Friday, 16 March 2018

ORA-01033: ORACLE initialization or shutdown in progress

Issue: ORA-01033: ORACLE initialization or shutdown in progress.

Steps to replicate the issue: I have created a tablespace and then deleted the folder where the tablespace was created. After deletion of the folder, I tried to open the SCHEMA, but it started giving the "ORA-01033: ORACLE initialization or shutdown in progress" error as shown below.
Solution: For the solution of  the above error follow the steps as shown below:
  • Open DOS command prompt
  • sqlplus /nolog
  • connect sys/manager as sysdba
  • shutdown immediate
  • startup
Thus, we will get the below error message:

ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: 'C:\DATABASE\HR_DEV\HRDEV01.DF'
The above error is the real cause of ORA-01033. Once we solve the above error, then ORA-01033 will also get solved. To solve ORA-01157 follow the steps as shown below:
  • If the database is down, mount it. 
        STARTUP MOUNT; 
  • Offline drop the datafile. 
        ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV01.DF' OFFLINE DROP;
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV02.DF' OFFLINE DROP;
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV03.DF' OFFLINE DROP;
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV04.DF' OFFLINE DROP;
  • If the database is at mount, open it. 
         ALTER DATABASE OPEN; 
  • Drop the user tablespace. 
         DROP TABLESPACE HR_DEV INCLUDING CONTENTS; 

Thus, the complete scripts executed in DOS command prompt is shown below:
Now we can connect to the database again and hence the solution to our requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.