Saturday, June 10, 2017

ORA-01624 + ORA-00312 : While dropping Online Redo Logs

I had to resize my Online Redo Logs, but to do that I had to drop old Redo Logs after adding new ones


SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance testDB (thread 1)
ORA-00312: online log 1 thread 1:
'/opt/app/testDB/oraredo01/testDB/redo_1a.log'
ORA-00312: online log 1 thread 1:
'/opt/app/testDB/oraredo02/testDB/redo_1b.log'

I was not able to drop the old Redo Logs as all were showing up as Active, log switches didnt help

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1         31  536870912        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         2          1         29  536870912        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         3          1         30  536870912        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         4          1         32 1073741824        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         5          1         33 1073741824        512          2 NO
CURRENT             1.5060E+13 10-JUN-17   2.8147E+14

         6          1         28 1073741824        512          2 YES
ACTIVE              1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17


6 rows selected.


To mark them inactive, do a checkpoint on the database

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         1          1         31  536870912        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         2          1         29  536870912        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         3          1         30  536870912        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
         4          1         32 1073741824        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17

         5          1         33 1073741824        512          2 NO
CURRENT             1.5060E+13 10-JUN-17   2.8147E+14

         6          1         28 1073741824        512          2 YES
INACTIVE            1.5060E+13 10-JUN-17   1.5060E+13 10-JUN-17


6 rows selected.

Now we can drop the old redo logs

SQL> alter database drop logfile group 1;

Database altered.

No comments:

Post a Comment