Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Friday, August 31, 2012

ORA-16038: log sequence# cannot be archived

After the 3 node - 2node RAC Apps database clone one of our DBA forgot to disable the unused thread#3.

i.e Our Prod was 3-node RAC hence three threads for redologs where as our QA system is two node RAC and the 3rd thread should be disabled ( No third node to capture the logs for thread#3) if not during the RMAN backup you will get the below errors.



Starting backup at 31-AUG-12
ORACLE error from target database:
ORA-16038: log 3 sequence# 171 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '+DBNAME_FRA_01/DBNAME/onlinelog/group_3.4609.790630963'
ORA-00312: online log 3 thread 1: '+DBNAME_FRA_01/DBNAME/onlinelog/group_3.4547.790630981'

Fix:

Login into the DB:

SQL>  select inst_id,open_mode from gv$database;

   INST_ID OPEN_MODE
---------- --------------------
         1 READ WRITE
         2 READ WRITE


SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 INACTIVE
         3          1 INACTIVE
         4          2 UNUSED
         5          2 UNUSED
         6          2 UNUSED
         7          3 UNUSED
         8          3 UNUSED
         9          3 UNUSED
        10          3 UNUSED

10 rows selected.

SQL> alter database disable thread 3;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

SQL> alter database drop logfile group 8;

Database altered.

SQL> alter database drop logfile group 9;

Database altered.

SQL> alter database drop logfile group 10;

Database altered.

SQL> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 INACTIVE
         3          1 INACTIVE
         4          2 UNUSED
         5          2 UNUSED
         6          2 UNUSED

6 rows selected.

Now restart the RMAN archivelog backup:


Starting backup at 31-AUG-12
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=790830466
input archived log thread=1 sequence=2 RECID=2 STAMP=790851323
input archived log thread=1 sequence=3 RECID=3 STAMP=790893321



No comments: