Skip to main content

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

Comments

  1. UnknownFebruary 17, 2017 at 1:22 AM

    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. VenkatOctober 2, 2017 at 9:55 AM

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

    ReplyDelete
  3. UnknownMarch 13, 2018 at 10:06 PM

    When Database open and error showing
    ORA-03113: end-of-file on communication channel then run given below script...........

    alter system set "_system_trig_enabled" = FALSE;
    alter trigger sys.cdc_alter_ctable_before DISABLE;
    alter trigger sys.cdc_create_ctable_after DISABLE;
    alter trigger sys.cdc_create_ctable_before DISABLE;
    alter trigger sys.cdc_drop_ctable_before DISABLE;

    create undo tablespace UNDOTBS2
    datafile 'E:\oracle\oradata\ORCL\undotbs01.dbf' size 200m;

    alter system set undo_tablespace=UNDOTBS2 scope=spfile;

    drop tablespace UNDOTBS including contents;

    alter trigger sys.cdc_alter_ctable_before ENABLE;
    alter trigger sys.cdc_create_ctable_after ENABLE;
    alter trigger sys.cdc_create_ctable_before ENABLE;
    alter trigger sys.cdc_drop_ctable_before ENABLE;
    alter system set "_system_trig_enabled" = TRUE;
    alter system set undo_management=AUTO scope=spfile;

    SHUTDOWN IMMEDIATE
    STARTUP

    ReplyDelete
  4. UnknownMarch 13, 2018 at 10:10 PM

    1. RECOVER DATABASE UNTIL CANCEL
    2. ALTER DATABASE OPEN RESETLOGS;
    ------ AFTER ERROR ORA-03113: end-of-file on communication channel
    3. EXIT SQL...
    4. run services.msc stop all oracle services...
    5.. restart system

    ReplyDelete
  5. UnknownMarch 14, 2018 at 1:32 AM

    This comment has been removed by the author.

    ReplyDelete
  6. Jalilur RahmanApril 5, 2019 at 9:58 PM

    Very Useful Article

    ReplyDelete
  7. jorge alfredo mayorga cardenas (JoAlMaCa)May 13, 2019 at 4:50 PM

    SQL> recover database until cancel;
    ORA-00283: recovery session canceled due to errors
    ORA-16433: The database or pluggable database must be opened in read/write
    mode.

    ReplyDelete
  8. AnonymousJuly 29, 2023 at 9:25 AM

    Excellent post.
    Today gold rate in chennai 916
    Silver rate today
    Diamond rate today
    Platinum rate today

    ReplyDelete

Post a Comment

Popular posts from this blog

Fix: ORA-13639: The current operation was interrupted because it timed out.

Sometimes SQL Tuning Advisor interrupts cause time limit took more than defined. You have to analyze it or increase value. Here you can see increasing of value. Example result of sql select                  execution_name, advisor_name,                  to_char(execution_start,'dd-mon-yy hh:mi:ss') execution_start,                  to_char(execution_end,'dd-mon-yy hh:mi:ss') execution_end, status,error_message from dba_advisor_executions where task_name = 'SYS_AUTO_SQL_TUNING_TASK' order by execution_start; Check value of TIME_LIMIT`s parameter : SQL> column parameter_value for A35 SQL> select parameter_name, parameter_value from dba_advisor_parameters where task_name = 'SYS_AUTO_SQL_TUNING_TASK' and parameter_name in ('TIME_LIMIT', 'DEFAULT_EXECUTION_TYP...
Post a Comment
Read more

How to fix ORA-26040: Data block was loaded using the NOLOGGING option

Today I faced with new ORA error. After solving I want to share this experience with yours. So, today 5`th datafile of my database was corrupted (/u01/app/oracle/oradata/ulfet_db/example01.dbf). After recover via RMAN I saw strange error. RMAN> recover datafile 5 block 443; Starting recover at 24-MAR-13 using channel ORA_DISK_1 channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00005 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp tag=TAG20130324T223233 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03 starting media recovery media recovery complete, elapsed ti...
2 comments
Read more