“Server not open” / “Drop tablespace” catch-22 situation

oracleoracle-11gtablespaces

I am getting the "server not open" error in SQLPLUS, and the equivalent on Oracle SQL Developer, which says "Oracle Initialization or shutdown in progress".

Fair enough. The fix for this is as follows:

You check to see the status of your database: 

select status, database_status from v$instance;

and I see that it is mounted and active. Then I run the following command:

 alter database open;

Which generates the following error:

ORA-01157: cannot identify/lock data file 6 – see DBWR trace file
ORA-01110: data file 6: 'F:\XXXX_INDEX.DBF'

To resolve this issue, I referred to this resource.

Which is telling me to open the database by running the same alter database open command as above.

The issue is circular.

Best Answer

No, it is not circular, alter database open is the 3rd step.

If you have a backup of that datafile, then restore and recover it.

If you do not have a backup, then do what the post you referenced tells you. Since your database is already mounted, you can skip step 1.

  1. If the database is down, mount it.

STARTUP MOUNT;

  1. Offline drop the datafile.

ALTER DATABASE DATAFILE 'full_path_file_name' OFFLINE DROP;

  1. If the database is at mount, open it.

ALTER DATABASE OPEN;

...

Related Question