Tuesday, June 04, 2013

ORA-01623, ORA-01624 and ORA-01567

This was tested on an Oracle 11.2.0.2.7 database. The output below shows that this database had 3 online redo log groups. You can only drop a redo log group if its STATUS is INACTIVE so when I tried to drop the CURRENT redo log group, I got an error:
 
SQL> l
  1* select group#, status from v$log
SQL> /
 
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE
 
SQL> l
  1* alter database drop logfile group 2
SQL> /
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01623: log 2 is current log for instance ORCL
(thread 1) - cannot drop
ORA-00312: online log 2 thread 1:
'/oracle/app/oracle/product/11.2.0.2.7/software_downloa
d/ORCL/redo02.log'
 
SQL>
 
I switched to a new redo log group so the previous CURRENT redo log group changed to ACTIVE but I still could not drop it:
 
SQL> alter system switch logfile
  2  /
 
System altered.
 
SQL> select group#, status from v$log
  2  /
 
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 ACTIVE
         3 CURRENT
 
SQL> alter database drop logfile group 2
  2  /
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance
ORCL (thread 1)
ORA-00312: online log 2 thread 1:
'/oracle/app/oracle/product/11.2.0.2.7/software_downloa
d/ORCL/redo02.log'
 
SQL>
 
Eventually it changed to INACTIVE then I was able to drop it:
 
SQL> select group#, status from v$log
  2  /
 
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT
 
SQL> alter database drop logfile group 2
  2  /
 
Database altered.
 
SQL> select group#, status from v$log
  2  /
 
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         3 CURRENT
 
SQL>
 
You need a minimum of 2 online redo log groups at all times so that one can be archived while the other is in use. When I tried to drop another redo log group, I got a different error again:
 
SQL> select group#, status from v$log
  2  /
 
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         3 CURRENT
 
SQL> alter database drop logfile group 1
  2  /
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01567: dropping log 1 would leave less than 2 log
files for instance ORCL (thread 1)
ORA-00312: online log 1 thread 1:
'/oracle/app/oracle/product/11.2.0.2.7/software_downloa
d/ORCL/redo01.log'
 
SQL>
 
I got the idea for this example from the book advertised below, which I have just started reading. If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at international_dba@yahoo.co.uk.

No comments: