Monday, December 26, 2016

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

While shutting down my TEST database process was hanged. Then I had to use shutdown abort. But when I wanted to start database it did not open.


 SQL> select name from v$database;  
 NAME  
 ---------  
 TEST

SQL> shut abort;  
 ORACLE instance shut down.  
 SQL> startup mount  
 ORACLE instance started.  
 Total System Global Area 6597406720 bytes  
 Fixed Size                2265664 bytes  
 Variable Size           3204451776 bytes  
 Database Buffers       3372220416 bytes  
 Redo Buffers            18468864 bytes  
 Database mounted.  
 SQL> alter database open;  
 alter database open  
 *  
 ERROR at line 1:  
 ORA-03113: end-of-file on communication channel  
 Process ID: 6552  
 Session ID: 191 Serial number: 3 

 What`s wrong? 

 SQL> alter database open resetlogs;  
 ERROR:   
 ORA-03114: not connected to ORACLE  

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

And instance got down

$ ps -ef | grep smon
oracle    6586  6246  0 17:50 pts/2    00:00:00 grep smon

 $ sqlplus "/as sysdba"  
 SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 26 17:50:35 2016  
 Copyright (c) 1982, 2013, Oracle. All rights reserved.  
 Connected to an idle instance.  
 SQL> startup mount  
 ORACLE instance started.  
 Total System Global Area 6597406720 bytes  
 Fixed Size                2265664 bytes  
 Variable Size           3204451776 bytes  
 Database Buffers       3372220416 bytes  
 Redo Buffers            18468864 bytes  
 Database mounted.  

Tried to reset logs

SQL> alter database open resetlogs;  
 alter database open resetlogs  
 *  
 ERROR at line 1:  
 ORA-01139: RESETLOGS option only valid after an incomplete database recovery 

Unsuccessful...
Let`s recover database 

SQL> recover database until cancel;  
 Media recovery complete.  

Now, try to open with resetlogs

 SQL> alter database open resetlogs;  
 Database altered. 


Done, now it works

4 comments:

  1. Excellent and very cool idea and the subject at the top of magnificence and I am happy to this post..Interesting post! Thanks for writing it. What's wrong with this kind of post exactly? It follows your previous guideline for post length as well as clarity..

    Best Dental Clinic In Chennai

    ReplyDelete
  2. This information is impressive; I am inspired with your post writing style & how continuously you describe this topic.


    Pawn Shop

    Pawn Loans

    Pawn Shops

    Pawn Loan

    Pawn Shop near me

    ReplyDelete
  3. Thanks that's called the "Perfect and Quick Fix".

    ReplyDelete
    Replies
    1. You are welcome Ram. Nice to hear that.

      Delete