Solution for ORA 01033 caused by ORA 01157 and ORA 01110

Solution for ORA 01033 caused by ORA 01157 and ORA 01110

Today I was asked to do some work with some data. Basically I have to import the raw data into something more usable, easy to manipulate and run queries. My first option as usual was to use Oracle.

I started my server and listener and try to connect my SQL Developer. I like this front end because is easy to use and whats more it is supported by Oracle which makes the integration easier. Anyways, I tried to connect it and I couldnt so my next try was to use command line and use sqlplus which is the default command interaction you have when you install Oracle DB Server. I executed:

sqlplus <username>/<password>

and I got an ORA-01033: ORACLE initialization or shutdown in progress. I had this issue before and I just used to wait or even re-start my computer but this time it did not work so I had to try another things. I tried running:

C:> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu May 5 17:22:38 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: PATHTOFILECMS2.DAT

This last command gave me the real reason it seems the server was looking for a DAT file that was no longer in the system which was true, I removed some files but it seems the Oracle DB server was still looking for them. So the solution was to tell the server to stop looking for these files. In order to do that I executed:

SQL> alter database datafile PATHTOFILECMS2.DAT offline drop;
Database altered.

Now that the file has been deleted we restart the server:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             385877416 bytes
Database Buffers          385875968 bytes
Redo Buffers                5259264 bytes
Database mounted.
Database opened.

And now everything should be working just fine. Oracle can give us errors when using it but sometimes they are not really the root cause of them this is something I hate about it. We need to be careful and find the real cause before start trying to solve the problem. In this case ORA-01033  was not the real issue, ORA-01157 and ORA-01110 were the root cause and usually because of these kind of issues we have to re-install or try crazy things when the solutions is something different, something simpler.

Hope this helps someone :)

download file now