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

9 comments:

  1. i dont think we will be able to connect when archive is full.

    ReplyDelete
    Replies
    1. there is one more option we can connect in oem and we can try to increase db_recovery_file_dest size and we can initiate log backup then revert it back to original size.

      Delete
    2. agree. many cases its free space exist but not allocated.

      Delete
  2. is there any way that I can make sure if archive log is full processes don't get hanged? for eg: if backup failed for whatever reason and backup admin wasn't able to start it back up. is there anything we can automate on the archive log to ensure it wont fill up?

    ReplyDelete
    Replies
    1. You could schedule a cron job with a script, like the following.
      If the database's archive log destination exceeds a level (30%), then it informs you by email and runs the scripts that backups and deletes the archive logs.

      #!/bin/sh
      export RMAN_SCR_DIR=[The directory where the script which backups the archive logs resides]
      export ARCH_DEST=[The archive log destination directory]
      export RMAN_SCR1=[The script which backups the archive logs]
      export ORACLE_SID1=[Your database's SID]
      export ORACLE_BASE=[$ORACLE_BASE]
      export ORACLE_HOME=[[$ORACLE_HOME]
      export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
      export SRVR=[The server's name]
      export DBA_MAIL=[The comma sepearated email list, that will get informed the backup started, in double quotes]

      cat_count=`cat $RMAN_SCR_DIR/$RMAN_SCR1.out|grep RMAN-00569|wc -l`
      if [ "$cat_count" -gt 0 ]; then
      grep "RMAN-00569" $RMAN_SCR_DIR/$RMAN_SCR1.out|mailx -r $SRVR@ote.gr -s "$ORACLE_SID1 RMAN-00569 error" $DBA_MAIL
      fi
      export ORACLE_SID=$ORACLE_SID1
      active_backup1=`sqlplus -s / as sysdba << EOF1
      SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
      SELECT COUNT(1) FROM V\\$RMAN_BACKUP_JOB_DETAILS WHERE STATUS='RUNNING' AND INPUT_TYPE='ARCHIVELOG';
      EXIT;
      EOF1`
      pctfull=`df $ARCH_DEST |grep -v Mounted |grep -v dev|awk '{print $4}' |sed s/%//`
      crit_level=30
      if [ "$pctfull" -ge "$crit_level" ] && [ "$active_backup1" -eq 0 ]; then
      $RMAN_SCR_DIR/$RMAN_SCR1
      echo " "|mailx -r $SRVR@ote.gr -s "$SRVR: Automatic backup started at $pctfull%" $DBA_MAIL

      Delete
  3. What about physically deleting log files and run VALIDATE archivelog?

    ReplyDelete
    Replies
    1. If you delete them, then you have to take a new full online backup ASAP. You will not be able to recover your database using the old full online backup, at a point in time after the archived logs you deleted.

      Delete
  4. Sorry for the dumb question, but wouldn't the incremental backup be sufficient?

    Thanks

    ReplyDelete
    Replies
    1. Sometimes, you backup software may fail and stop backing up and delete the archived logs and, if you do not get notified in time, the filesystem will get filled.
      Also, an unusual amount of DMLs executed to your database, might fill it, since your sizing may not be adequate for it.

      Delete