[Oracle]資料庫無法登入 ORA-01033 oracle initialization or shutdown in progress

客戶的資料庫主機Crash，廠商協助把主機Ghost回來
雖然在服務裡把Oracle Database打開，卻仍然無法登入
呈現 ORA-01033 oracle initialization or shutdown in progress
經查應該是Improper shut down of Database
造成Problem with the Database CTL , DBF or ORA files(system01.dbf) error

Service.msc開啟服務無錯誤訊息，使用指令重啟資料庫(mount and restart DB again)
cmd.exe 下輸入
set ORACLE_SID=<YOUR SID>
sqlplus nolog
SQL> connect / as sysdba
Connect.
SQL> shutdown abort
ORACLE Instance shut down.
SQL> startup nomount
ORACLE Instance Started.
SQL> alter database mount;
SQL> alter database open;
但是在alter database open;會出現error
ORA-01122: database file 1 failed verification check
ORA-01110: database file 1: 'xxx\SYSTEM01.DBF'
ORA-01207: file is more recent than control file – old control file
查到建議的處理方法是
 1.alter database backup controlfile to trace;
 2.startup nomount;
 3.recover database using backup controlfile;
 4.alter database open;

參考到的處理方式是
CREATE CONTROLFILE REUSE DATABASE "DBNAME" NORESETLOGS NOARCHIVELOG ==depends what is your db log mode
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

不過會跳錯誤訊息
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'%ORACLE_HOME%\DATABASE\DBS1%ORACLE_SID%.ORA'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

可能是我沒有先SET ORACLE_HOME跟SET ORACLE_SID
造成失敗，不過大抵上方向是對的




ORA-01033 oracle initialization or shutdown in progress
Problem

ORA-01033 oracle initialization or shutdown  while accessing database.

Possible Causes 

1. Database might be starting or closing.
2. Problem with the Database CTL , DBF or ORA files. These files could have been moved or gone corrupt.
3. Database not mounted properly.
4. Problem with the DB service.
5. Improper shut down of Database.

Possible resolutions 

Trial 1  ( If Database might be starting or closing )

Wait for sometime and try again. If this doesn't recover , try restarting your system and then restarting DB service. 

Trial 2 ( if Database not mounted properly or Improper shutdown of DB )

mount and restart DB again. 

SQL> connect / as sysdba
Connect.
SQL> shutdown abort
ORACLE Instance shut down.
SQL> startup nomountORACLE Instance Started.
SQL> alter database mount;
SQL> alter database open;

Trial 3 ( if Problem is with the DB service )

go to services.msc and restart the respective DB service.

Trial 4 (Problem with the Database CTL , DBF or ORA files )

Try to recover the files if you have changed or moved these files.


If nothing work better delete the service and DB and recreate it.  
( Make sure that you have required Data backup before doing so, if you need that later)

Step 1 - go to service.msc and stop the respective service.

Step 2 - Delete it by going to command prompt and using 

sc delete <service name >  

Step 3 - Recreate Database.