RMAN-03009 ORA-15028

RMAN-03009: failure of delete command on c1 channel at 02/25/2015 04:00:05

ORA-15028: ASM file '+DATADG/xxxxdb/archivelog/2015_01_19/thread_1_seq_6009.4593.869409181' not dropped; currently being accessed

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
Checked for relevance on *** 02-Sep-2014 *** 

SYMPTOMS

6 node RAC - 11203  

While trying to delete obsolete archive logs via RMAN, the tool receives ORA-15028 when trying to delete archivelog file from ASM diskgroup

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of delete command on default channel at 06/08/2012 13:20:35
ORA-15028: ASM file '+DG_ARCH/myprod/archivelog/2012_06_04/thread_1_seq_72711.5178.785032231' not dropped; currently being accessed

 
* Impact on Business : Need to found out which instance is holding the lock on the archivelog file. 
   The lock prevents all obsoleted archivelog files from being deleted and if the archivelog diskgroup  fills up, the database will halt, affecting the user community.

 

CAUSE

 A cause was not found since the following data was not collected while the issue was present:

Run the following from one of the ASM and database instances, to collect cluster-wide systemstate and hanganalyze dumps:

For RAC (connect to one of the RAC instances and execute):
 $> sqlplus /nolog 
 SQL> connect / as sysdba (sysasm if connecting to an ASM instance)
 SQL> oradebug setmypid 
 SQL> oradebug unlimit 
 REM : The next line should give something like Hang Analysis in $ORACLE_BASE/diag/.../trace/$ORACLE_SID_diag_<pid>.trc. Upload this
 REM : Run the following two lines on one instance 2-3 times - 1 minute apart:
 SQL> oradebug -g all hanganalyze 3 
 SQL> oradebug -g all dump systemstate 266
 REM : The following line will print the location for the systemstate trace. Upload this
 SQL> oradebug tracefile_name
 REM : Also upload the instance alert log.

 
Collect instance views to help determine the source of the open file. From each ASM and database instance:

spool <ASM|db>_<#>_lock_session_process_details.html
 SET MARKUP HTML ON
 set echo on
 set pagesize 200
 alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
 select 'THIS REPORT WAS GENERATED AT: ==)> ' , sysdate " "  from dual;
 select 'HOSTNAME ASSOCIATED WITH THIS INSTANCE: ==)> ' , MACHINE " " from v$session where program like '%SMON%';
 select * from gv$lock ;
 select * from v$session;
 select sw.sid, l.inst_id, l.block from gv$lock l, gv$session_wait sw where l.sid = sw.sid and l.inst_id = sw.inst_id;
 select * from v$process;
 select * from v$version;
 show parameter all
 spool off
 exit

 
From each ASM instance:   Run all scripts from Note:470211.1

 

SOLUTION

 Use the following to quickly find out which database instance holds the lock and to identify for restart:

ASMCMD [+] > lsof -G DG_ARCH
DB_Name  Instance_Name  Path
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_1_seq_72711.5178.785032231
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_1_seq_72720.4818.785040307
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_1_seq_72727.4616.785046605
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_1_seq_72730.4479.785049261
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_1_seq_72742.4395.785059089
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_2_seq_70382.2308.785047531
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_2_seq_70385.1835.785050225
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_2_seq_70402.3091.785064485
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_2_seq_70408.1211.785069875
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_2_seq_70410.4439.785071661
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_3_seq_67973.4354.785051059
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_3_seq_67974.2051.785051959
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_3_seq_67995.1876.785069891
myprod     myprod1          +dg_arch/myprod/archivelog/2012_06_04/thread_6_seq_32158.4060.785046539
myprod     myprod1          +dg_arch/myprod/datafile/tbs_hways_master_data_med.1698.750777283