Thursday, July 3, 2014

ORA-15012: ASM file '+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411' does not exist


Full Backup of oralin database is Failing with the below errors,

##########################
## Error
##########################

ORA-15012: ASM file '+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411' does not exist

### Full Error

released channel: oralin_incr
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 07/01/2014 03:57:41
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file +RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411
ORA-17503: ksfdopn:2 Failed to open file +RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411
ORA-15012: ASM file '+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411' does not exist

##########################
#  Error Occurred
##########################

Error occured while a scheduled FULL backup is run in 11.2.0.3.0 version

##########################
## Command Executed
##########################

Full Database backup command

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check Archive Log Availability
#########################################

set lines 200
col name for a80
col thread# for 9999
col creator for a8
col completion_time for a20

Select name,creator,thread#,sequence#,applied,status,to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') "completion_time",resetlogs_id,archived,backup_count
from v$archived_log where sequence#>=3614 order by sequence# desc;

NAME                                                                             CREATOR  THREAD#  SEQUENCE# APPLIED   S completion_time      RESETLOGS_ID ARC BACKUP_COUNT
-------------------------------------------------------------------------------- -------- ------- ---------- --------- - -------------------- ------------ --- ------------
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3620.41615.851000431             ARCH           1       3620 NO        A 23-JUN-2014 13:00:40    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3619.11237.851000269             ARCH           1       3619 NO        A 23-JUN-2014 12:57:58    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3618.31003.851000109             ARCH           1       3618 NO        A 23-JUN-2014 12:55:17    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3617.25650.850999947             ARCH           1       3617 NO        A 23-JUN-2014 12:52:37    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3616.26704.850999771             ARCH           1       3616 NO        A 23-JUN-2014 12:49:40    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3615.42478.850999603             ARCH           1       3615 NO        A 23-JUN-2014 12:46:52    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411             ARCH           1       3614 NO        A 23-JUN-2014 12:43:40    806656703 YES            0

=====================================================================================================================
Sequence 3614 Shows as Available
=====================================================================================================================

#########################################
# 2) Check the Archive log Physically
#########################################

ASMCMD [+] > cd +RECO/oralin/archivelog/2014_06_23/
ASMCMD-08002: entry '2014_06_23' does not exist in directory '+RECO/oralin/archivelog/'
ASMCMD [+] > cd +RECO/oralin/archivelog
ASMCMD [+RECO/oralin/archivelog] > ls
2014_06_27/
2014_06_28/
2014_06_29/
2014_06_30/
2014_07_01/
ASMCMD [+RECO/oralin/archivelog] >

=====================================================================================================================
From Above output we can see that there is no archivelogs file present physically, but it still shows as available in v$archived_log
=====================================================================================================================

#########################################
# 3) Reason for Failure
#########################################

Error is occurring because there is no archivelog files physically available, but as per control file, the archive logs are still available.

So RMAN tries to backup the archivelogs and when it finds out its not available, backup fails.

=====================================================================================================================

##########################
## Solution
##########################

Looks like archive log files might have got deleted after it got shipped to standby due to wrong configuration.

From the V$archived_log column, backup_count we can see that its not backed up ever. So we cant restore the archive logs. In this case, we have a data loss.

We have to find out how the archive log got deleted. The database which i have faced is not that much important, so i am proceeding with the workaround.

=====================================================================================================================
Crosscheck all the archive logs using RMAN
=====================================================================================================================

Run below command in RMAN,

crosscheck archivelog all;

#### Now check the archive log status.

NAME                                                                             CREATOR  THREAD#  SEQUENCE# APPLIED   S completion_time      RESETLOGS_ID ARC BACKUP_COUNT
-------------------------------------------------------------------------------- -------- ------- ---------- --------- - -------------------- ------------ --- ------------
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3620.41615.851000431             ARCH           1       3620 NO        X 23-JUN-2014 13:00:40    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3619.11237.851000269             ARCH           1       3619 NO        X 23-JUN-2014 12:57:58    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3618.31003.851000109             ARCH           1       3618 NO        X 23-JUN-2014 12:55:17    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3617.25650.850999947             ARCH           1       3617 NO        X 23-JUN-2014 12:52:37    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3616.26704.850999771             ARCH           1       3616 NO        X 23-JUN-2014 12:49:40    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3615.42478.850999603             ARCH           1       3615 NO        X 23-JUN-2014 12:46:52    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411             ARCH           1       3614 NO        X 23-JUN-2014 12:43:40    806656703 YES            0

Now we can see that the unavailable archivelogs are marked as EXPIRED.

Tried running the backup now and its successful.  ( Archive logs which are marked EXPIRED are skipped )

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================



No comments: