Thursday, July 21, 2011

ORA-01623: log %s is current log for thread %s - cannot drop

In the process of refreshing a 9i Dev database I had to change the file locations of a few datafiles and the logfiles. Though the usage of DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT would have made the job simpler but i just did not feel like using them. So, the controlfile was
re-created, recovery was invoked and thats when the issues started showing up one by one - detailed below.




SQL> startup nomount
ORACLE instance started.

Total System Global Area  395387088 bytes
Fixed Size                   454864 bytes
Variable Size             285212672 bytes
Database Buffers          109051904 bytes
Redo Buffers                 667648 bytes
SQL> @C:\Oracle\admin\devel\scripts\crea_ctrl_20_oct_2010.sql

Control file created.

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            c:\oracle\ora92\RDBMS
Oldest online log sequence     0
Current log sequence           0

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 505595471 generated at 10/19/2010 06:44:30 
needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC04700.001
ORA-00280: change 505595471 for thread 1 is in sequence #4700


Specify log: {=suggested | filename | AUTO | CANCEL}
H:\prod_hot_bkup\1_4700.DBF
ORA-00279: change 505596642 generated at 10/19/2010 07:04:31 
needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC04701.001
ORA-00280: change 505596642 for thread 1 is in sequence #4701
ORA-00278: log file 'H:\prod_hot_bkup\1_4700.DBF' no longer 
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
H:\prod_hot_bkup\1_4701.DBF
ORA-00279: change 505596644 generated at 10/19/2010 07:04:31 
needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC04702.001
ORA-00280: change 505596644 for thread 1 is in sequence #4702
ORA-00278: log file 'H:\prod_hot_bkup\1_4701.DBF' no longer 
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
H:\prod_hot_bkup\1_4702.DBF
ORA-00279: change 505596648 generated at 10/19/2010 07:04:37 
needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC04703.001
ORA-00280: change 505596648 for thread 1 is in sequence #4703
ORA-00278: log file 'H:\prod_hot_bkup\1_4702.DBF' no longer 
needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.



Issue #1
--------

The database engine is unable to locate the logfile, because the directory path cannot be found. So i try to drop the logfile groups to create new ones on the available directory paths.



SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'I:\ORACLE\ORADATA\devel\REDO01A.LOG'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files in thread 1
ORA-00312: online log 2 thread 1: 'D:\ORACLE\ORADATA\devel\REDO02A.LOG'
ORA-00312: online log 2 thread 1: 'H:\ORACLE\ORADATA\devel\REDO02B.LOG'

SQL> alter database add logfile group 1 
  2> ('E:\ORACLE\ORADATA\devel\REDO01A.LOG') size 200M;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 
  2> ('E:\ORACLE\ORADATA\devel\REDO02A.LOG') size 200M;

Database altered.



Issue #2
--------

One cannot drop an online redo logfile and cannot switch a logfile in mount state either to drop it.



SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for thread 1 - cannot drop
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\devel\REDO03A.LOG'
ORA-00312: online log 3 thread 1: 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'


SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open


SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED



Querying V$ views for the logfiles gives an insight of the state of the logfile groups. Then the locations of the logfile groups are changed but the STALE contents do not get cleared neither does it allow to drop one of the members of a logfile groups. As a next step to resolve the issue, the contents of the current online logfile group are cleared so the file is clean from corruption.




    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS          
---------- ---------- ---------- ---------- ---------- --- ---------------
         1          1          0  209715200          1 YES UNUSED         
         2          1          0  209715200          1 YES UNUSED         
         3          1          0  209715200          2 YES INVALIDATED    

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- -------------------------------------------
         3 STALE   ONLINE  D:\ORACLE\ORADATA\devel\REDO03A.LOG
         3 STALE   ONLINE  H:\ORACLE\ORADATA\devel\REDO03B.LOG
         2         ONLINE  E:\ORACLE\ORADATA\devel\REDO02A.LOG
         1         ONLINE  E:\ORACLE\ORADATA\devel\REDO01A.LOG

SQL> alter database rename file 'D:\ORACLE\ORADATA\devel\REDO03A.LOG' to 
  2> 'E:\ORACLE\ORADATA\devel\REDO03A.LOG';

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ---------------------------------------
         3 STALE   ONLINE  E:\ORACLE\ORADATA\devel\REDO03A.LOG
         3 STALE   ONLINE  H:\ORACLE\ORADATA\devel\REDO03B.LOG
         2         ONLINE  E:\ORACLE\ORADATA\devel\REDO02A.LOG
         1         ONLINE  E:\ORACLE\ORADATA\devel\REDO01A.LOG

SQL> alter database drop logfile member 'H:\ORACLE\ORADATA\devel\REDO03B.LOG';
alter database drop logfile member 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 3
ORA-01517: log member: 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'


SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- --------------------------------------
         3         ONLINE  E:\ORACLE\ORADATA\devel\REDO03A.LOG
         3         ONLINE  H:\ORACLE\ORADATA\devel\REDO03B.LOG
         2         ONLINE  E:\ORACLE\ORADATA\devel\REDO02A.LOG
         1         ONLINE  E:\ORACLE\ORADATA\devel\REDO01A.LOG





Yet, the third logfile group cannot be dropped because it is the current logfile and the database needs to be opened to switch the logfile.



SQL> alter database drop logfile member 'H:\ORACLE\ORADATA\devel\REDO03B.LOG';
alter database drop logfile member 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: 'E:\ORACLE\ORADATA\devel\REDO03A.LOG'
ORA-00312: online log 3 thread 1: 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced




Issue #3
--------

The ORACLE instance terminated, reason being the mismatch in the UNDO tablespace name. After the correction was made, the database would fail to open yet again. The database would not proceed further with either of the 'alter database open' and 'alter database open resetlogs' commands.

The sequence number had been reset to '1' when the last time the database was opened with a 'resetlogs' but the database could not come up to ensure a complete recovery as evident from the below results. So the database would require some more recovering but this time it starts afresh from sequence '1'



ALERT LOG
---------

Wed Oct 20 12:51:30 2010
Errors in file c:\oracle\admin\devel\udump\devel_ora_7580.trc:
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type

devel_ora_7580.trc
------------------
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type


SQL> startup mount;
ORACLE instance started.

Total System Global Area  395387088 bytes
Fixed Size                   454864 bytes
Variable Size             285212672 bytes
Database Buffers          109051904 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ------------------------------------------
         3         ONLINE  E:\ORACLE\ORADATA\devel\REDO03A.LOG
         3         ONLINE  H:\ORACLE\ORADATA\devel\REDO03B.LOG
         2         ONLINE  E:\ORACLE\ORADATA\devel\REDO02A.LOG
         1         ONLINE  E:\ORACLE\ORADATA\devel\REDO01A.LOG

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


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\devel\SYSTEM01.DBF'

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 505596650 generated at 10/20/2010 12:51:29 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00001.001
ORA-00280: change 505596650 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\devel\SYSTEM01.DBF'


ORA-01112: media recovery not started


SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done



The database recovery was started again and this time the online redo log that was created from the last incomplete recovery was applied to achieve a successful recovery and the database was opened with a 'resetlogs' keyword to reset the sequence number one more time.




SQL> recover database  using backup controlfile;
ORA-00279: change 505596650 generated at 10/20/2010 12:51:29 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00001.001
ORA-00280: change 505596650 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
H:\oracle\oradata\devel\REDO03B.LOG
Log applied.
Media recovery complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

1 comment:

Anonymous said...

very complete !
Great, thank you !