Tuesday, July 10, 2012

Database frozen due to full archive log destination

Database froze, due to archive log destination being full and you can only connect as SYSDBA.
You can add one more archive log destination to a filesystem with enough free space:
SQL> alter system set log_archive_dest_1='LOCATION=/oratest/TESTDB/arch' scope=memory;
System altered.

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     33
Next log sequence to archive   35
Current log sequence           35

Database will try to put logs on both destinations, but if one of them is full, it will fail and will put it only in the second destination. This is not a problem.
This depends on  LOG_ARCHIVE_MIN_SUCCEED_DEST parameter [default=1], which defines the minimum number of destinations that must succeed in order for the online logfile to be available for reuse.
Use RMAN to backup and delete your archive logs and when the incident is resolved, you may undo the change:
SQL> alter system set log_archive_dest_1='' scope=memory;
System altered.

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     33
Next log sequence to archive   35
Current log sequence           35

Another way is to manually move some archive logs to a new destination.
Let's say our archive log destination is full.
Our current archive logs are:
./archivelog/2012_07_10:
total 20
-rw-r----- 1 oratest oinstall 1024 Jul 10 08:34 o1_mf_1_30_7zqhpr63_.arc
-rw-r----- 1 oratest oinstall 1024 Jul 10 08:34 o1_mf_1_31_7zqhps7b_.arc
-rw-r----- 1 oratest oinstall 1024 Jul 10 08:34 o1_mf_1_32_7zqhpt0t_.arc
-rw-r----- 1 oratest oinstall 1024 Jul 10 08:34 o1_mf_1_33_7zqhptqk_.arc
-rw-r----- 1 oratest oinstall 1536 Jul 10 08:34 o1_mf_1_34_7zqhpvkw_.arc

We may query V$ARCHIVED_LOG:
SELECT NAME, COMPLETION_TIME, DELETED, STATUS 
FROM V$ARCHIVED_LOG
ORDER BY COMPLETION_TIME;

NAMECOMPLETION_TIMEDELETEDSTATUS
............
10/7/2012 08:33:14YESD
/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_30_7zqhpr63_.arc10/7/2012 08:34:16NOA
/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_31_7zqhps7b_.arc10/7/2012 08:34:17NOA
/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_32_7zqhpt0t_.arc10/7/2012 08:34:18NOA
/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_33_7zqhptqk_.arc10/7/2012 08:34:18NOA
/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_34_7zqhpvkw_.arc10/7/2012 08:34:19NOA

Rows with NAME=NULL, DELETED=YES, STATUS=D [Deleted] are archive logs backed up and deleted by RMAN.
Those with a name, DELETED=NO, STATUS=A [Available] are still in your archive log destination.

We move one archive log to a new filesystem:
[oratest@testsrv 2012_07_10]$ mv o1_mf_1_30_7zqhpr63_.arc /oratest/TESTDB/arch/
[oratest@testsrv 2012_07_10]$ ll
total 16
-rw-r----- 1 oratest oinstall 1024 Jul 10 08:34 o1_mf_1_31_7zqhps7b_.arc
-rw-r----- 1 oratest oinstall 1024 Jul 10 08:34 o1_mf_1_32_7zqhpt0t_.arc
-rw-r----- 1 oratest oinstall 1024 Jul 10 08:34 o1_mf_1_33_7zqhptqk_.arc
-rw-r----- 1 oratest oinstall 1536 Jul 10 08:34 o1_mf_1_34_7zqhpvkw_.arc
[oratest@testsrv 2012_07_10]$ ll /oratest/TESTDB/arch/
total 4
-rw-r----- 1 oratest oinstall 1024 Jul 10 08:34 o1_mf_1_30_7zqhpr63_.arc

We try to backup  our archive logs using RMAN:
RMAN> RUN {
ALLOCATE CHANNEL ch01 TYPE DISK;
ALLOCATE CHANNEL ch02 TYPE DISK;
ALLOCATE CHANNEL ch03 TYPE DISK;
ALLOCATE CHANNEL ch04 TYPE DISK;
BACKUP INCREMENTAL LEVEL=0
    ARCHIVELOG ALL DELETE INPUT;
}

allocated channel: ch01
channel ch01: SID=50 device type=DISK

allocated channel: ch02
channel ch02: SID=69 device type=DISK

allocated channel: ch03
channel ch03: SID=83 device type=DISK

allocated channel: ch04
channel ch04: SID=100 device type=DISK

Starting backup at 10-JUL-12
current log archived
released channel: ch01
released channel: ch02
released channel: ch03
released channel: ch04
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 07/10/2012 09:42:30
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_30_7zqhpr63_.arc
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

We got an error and nothing was backed up and deleted. The moved file is missing.
We need to use RMAN CROSSCHECK command:
RMAN> CROSSCHECK ARCHIVELOG ALL;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
validation failed for archived log
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_30_7zqhpr63_.arc RECID=15 STAMP=788258056
validation succeeded for archived log
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_31_7zqhps7b_.arc RECID=16 STAMP=788258057
validation succeeded for archived log
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_32_7zqhpt0t_.arc RECID=17 STAMP=788258058
validation succeeded for archived log
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_33_7zqhptqk_.arc RECID=18 STAMP=788258058
validation succeeded for archived log
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_34_7zqhpvkw_.arc RECID=19 STAMP=788258059
validation succeeded for archived log
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_35_7zqmppbw_.arc RECID=20 STAMP=788262150
Crosschecked 6 objects

Querying V$ARCHIVED_LOG we can see that moved log's STATUS changed to X [Expired]:
NAMECOMPLETION_TIMEDELETEDSTATUS
............
/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_30_7zqhpr63_.arc10/7/2012 08:34:16NOX
............

We retry to backup the archive logs:
RMAN> RUN {
ALLOCATE CHANNEL ch01 TYPE DISK;
ALLOCATE CHANNEL ch02 TYPE DISK;
ALLOCATE CHANNEL ch03 TYPE DISK;
ALLOCATE CHANNEL ch04 TYPE DISK;
BACKUP INCREMENTAL LEVEL=0
    ARCHIVELOG ALL DELETE INPUT;
}

released channel: ORA_DISK_1
allocated channel: ch01
channel ch01: SID=50 device type=DISK

allocated channel: ch02
channel ch02: SID=69 device type=DISK

allocated channel: ch03
channel ch03: SID=83 device type=DISK

allocated channel: ch04
channel ch04: SID=100 device type=DISK

Starting backup at 10-JUL-12
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=31 RECID=16 STAMP=788258057
input archived log thread=1 sequence=32 RECID=17 STAMP=788258058
channel ch01: starting piece 1 at 10-JUL-12
channel ch02: starting compressed archived log backup set
channel ch02: specifying archived log(s) in backup set
input archived log thread=1 sequence=33 RECID=18 STAMP=788258058
input archived log thread=1 sequence=34 RECID=19 STAMP=788258059
channel ch02: starting piece 1 at 10-JUL-12
channel ch03: starting compressed archived log backup set
channel ch03: specifying archived log(s) in backup set
input archived log thread=1 sequence=35 RECID=20 STAMP=788262150
channel ch03: starting piece 1 at 10-JUL-12
channel ch04: starting compressed archived log backup set
channel ch04: specifying archived log(s) in backup set
input archived log thread=1 sequence=36 RECID=21 STAMP=788262734
channel ch04: starting piece 1 at 10-JUL-12
channel ch01: finished piece 1 at 10-JUL-12
piece handle=/oratest/TESTDB/fast_recovery_area/TESTDB/backupset/2012_07_10/o1_mf_annnn_TAG20120710T095214_7zqn8ync_.bkp tag=TAG20120710T095214 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:00
channel ch01: deleting archived log(s)
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_31_7zqhps7b_.arc RECID=16 STAMP=788258057
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_32_7zqhpt0t_.arc RECID=17 STAMP=788258058
channel ch02: finished piece 1 at 10-JUL-12
piece handle=/oratest/TESTDB/fast_recovery_area/TESTDB/backupset/2012_07_10/o1_mf_annnn_TAG20120710T095214_7zqn8ynp_.bkp tag=TAG20120710T095214 comment=NONE
channel ch02: backup set complete, elapsed time: 00:00:00
channel ch02: deleting archived log(s)
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_33_7zqhptqk_.arc RECID=18 STAMP=788258058
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_34_7zqhpvkw_.arc RECID=19 STAMP=788258059
channel ch04: finished piece 1 at 10-JUL-12
piece handle=/oratest/TESTDB/fast_recovery_area/TESTDB/backupset/2012_07_10/o1_mf_annnn_TAG20120710T095214_7zqn8yor_.bkp tag=TAG20120710T095214 comment=NONE
channel ch04: backup set complete, elapsed time: 00:00:00
channel ch04: deleting archived log(s)
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_36_7zqn8yf3_.arc RECID=21 STAMP=788262734
channel ch03: finished piece 1 at 10-JUL-12
piece handle=/oratest/TESTDB/fast_recovery_area/TESTDB/backupset/2012_07_10/o1_mf_annnn_TAG20120710T095214_7zqn8yof_.bkp tag=TAG20120710T095214 comment=NONE
channel ch03: backup set complete, elapsed time: 00:00:00
channel ch03: deleting archived log(s)
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_35_7zqmppbw_.arc RECID=20 STAMP=788262150
Finished backup at 10-JUL-12
released channel: ch01
released channel: ch02
released channel: ch03
released channel: ch04

The archive log destination is now cleared:
/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10
[oratest@testsrv 2012_07_10]$ ll
total 0

All entries in V$ARCHIVED_LOG now have NAME=NULL, DELETED=YES, STATUS=D, except the log we moved.

Now we return that log in the archive log destination:
[oratest@testsrv 2012_07_10]$ mv /oratest/TESTDB/arch/o1_mf_1_30_7zqhpr63_.arc .
[oratest@testsrv 2012_07_10]$ ll
total 4
-rw-r----- 1 oratest oinstall 1024 Jul 10 08:34 o1_mf_1_30_7zqhpr63_.arc

We CROSSCHECK once more:
RMAN> CROSSCHECK ARCHIVELOG ALL;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
validation succeeded for archived log
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_30_7zqhpr63_.arc RECID=15 STAMP=788258056
Crosschecked 1 objects

Querying V$ARCHIVED_LOG, we see that log is available again:
NAMECOMPLETION_TIMEDELETEDSTATUS
............
/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_30_7zqhpr63_.arc10/7/2012 08:34:16NOA
............

We take one more backup to delete it:
RMAN> RUN {
ALLOCATE CHANNEL ch01 TYPE DISK;
ALLOCATE CHANNEL ch02 TYPE DISK;
ALLOCATE CHANNEL ch03 TYPE DISK;
ALLOCATE CHANNEL ch04 TYPE DISK;
BACKUP INCREMENTAL LEVEL=0
    ARCHIVELOG ALL DELETE INPUT;
}

released channel: ORA_DISK_1
allocated channel: ch01
channel ch01: SID=50 device type=DISK

allocated channel: ch02
channel ch02: SID=69 device type=DISK

allocated channel: ch03
channel ch03: SID=83 device type=DISK

allocated channel: ch04
channel ch04: SID=100 device type=DISK

Starting backup at 10-JUL-12
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=15 STAMP=788258056
channel ch01: starting piece 1 at 10-JUL-12
channel ch02: starting compressed archived log backup set
channel ch02: specifying archived log(s) in backup set
input archived log thread=1 sequence=37 RECID=22 STAMP=788263417
channel ch02: starting piece 1 at 10-JUL-12
channel ch01: finished piece 1 at 10-JUL-12
piece handle=/oratest/TESTDB/fast_recovery_area/TESTDB/backupset/2012_07_10/o1_mf_annnn_TAG20120710T100337_7zqny9l8_.bkp tag=TAG20120710T100337 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
channel ch01: deleting archived log(s)
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_30_7zqhpr63_.arc RECID=15 STAMP=788258056
channel ch02: finished piece 1 at 10-JUL-12
piece handle=/optim2/oratest/TESTDB/fast_recovery_area/TESTDB/backupset/2012_07_10/o1_mf_annnn_TAG20120710T100337_7zqny9lj_.bkp tag=TAG20120710T100337 comment=NONE
channel ch02: backup set complete, elapsed time: 00:00:01
channel ch02: deleting archived log(s)
archived log file name=/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10/o1_mf_1_37_7zqny9fo_.arc RECID=22 STAMP=788263417
Finished backup at 10-JUL-12
released channel: ch01
released channel: ch02
released channel: ch03
released channel: ch04

[oratest@testsrv 2012_07_10]$ pwd
/oratest/TESTDB/fast_recovery_area/TESTDB/archivelog/2012_07_10
[oratest@testsrv 2012_07_10]$ ll
total 0

No comments:

Post a Comment