Solution to the problem that oracle cannot log on to sqlplus after the tablespace file is deleted by mistake, oraclesqlplus

Solution to the problem that oracle cannot log on to sqlplus after the tablespace file is deleted by mistake, oraclesqlplus

Background: Today I learned how to create a tablespace in the oracle database. So I rushed to create two tablespaces named/u01/test/my_01.dbf and/u01/test/my_02.dbf. Software: CentOS-6.7-x86_64, linux. x64_11gR2_database. The CREATE Command is as follows:

Create tablespace my_01 logging datafile '/u01/test/my_01.dbf' size 128 M

Alter tablespace my_01 add datafile '/u01/test/my_01.dbf' size 128 M

Two tablespace files are generated under/u01/test. After all, it was created by myself. Then, I thought these two tablespace files occupied space, so I moved them to trash on the centos graphic interface. The effect is as follows:

When I use the sqlplus command again, the following error is found:

When we see '/u01/test/my_01.dbf', I thought: it is related to my deletion of tablespace files... Now that there is a problem, you have to go online to find information. After all, the rp broke out and found the cause: After oracle is started, the user logs on to load the original configuration information in the solution, an error is reported because files are not found during the loading process. Thanks to this Netizen's dedication to https://www.doc88.com/p-634723326544.html

Solution Process:

[Oracle @ jie ~] $ Sqlplus/nolog

SQL * Plus: Release 11.2.0.1.0 Production on Mon Feb 29 16:05:31 2016

Copyright (c) 1982,200 9, Oracle. All rights reserved.

SQL> conn/as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 507512904 bytes

Database Buffers 318767104 bytes

Redo Buffers 2433024 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6-see DBWR trace file -- error message

ORA-01110: data file 6: '/u01/test/my_01.dbf'

SQL> alter database datafile 6 offline drop; -- here 6 is based on the data file 6 in the preceding error message

Database altered.

SQL> alter database open resetlogs;

Alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> alter database open;

Alter database open

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 7-see DBWR trace file

ORA-01110: data file 7: '/u01/test/my_02.dbf'

(Because I deleted two tablespace files by mistake, so there is another 7 to process. Repeat the above steps)

SQL> alter database datafile 7 offline drop;

Database altered.

SQL> alter database open resetlogs;

Alter database open resetlogs

*

ERROR at line 1:

ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> alter database open;

Database altered.

SQL> shutdown normal

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 507512904 bytes

Database Buffers 318767104 bytes

Redo Buffers 2433024 bytes

Database mounted.

SQL> conn scott

Enter password:

ERROR:

ORA-01033: ORACLE initialization or shutdown in progress

Process ID: 0

Session ID: 0 Serial number: 0

Warning: You are no longer connected to ORACLE.

(Restart oracle !)

SQL> quit

[Oracle @ jie ~] $ Su-oracle

Password:

[Oracle @ jie ~] $ Sqlplus/nolog

SQL * Plus: Release 11.2.0.1.0 Production on Mon Feb 29 16:44:51 2016

Copyright (c) 1982,200 9, Oracle. All rights reserved.

SQL> conn/as sysdba

Connected.

SQL> startup

ORA-01081: cannot start already-running ORACLE-shut it down first

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 507512904 bytes

Database Buffers 318767104 bytes

Redo Buffers 2433024 bytes

Database mounted.

Database opened.

SQL> conn scott

Enter password:

Connected.

Now, the problem is solved !!