在ASM磁盘组中删除归档日志报ORA-15028

1 备份报错RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03009: failure of delete command on ORA_DISK_1 channel at 05/27/2022 09:41:13 ORA-15028: ASM file '+ARCH/cjdb/archivelog/2022_05_25/thread_1_seq_585427.1532.1105599629' not dropped; currently being accessed 2  使用lsof命令，查看归档日志是否被占用，经查询，数据库节点1占用了此归档日志。ASMCMD> lsof -G ARCHDB_Name  Instance_Name  Path                                                                     cjdb      cjdb1       +arch/cjdb/archivelog/2022_05_25/thread_1_seq_585427.1532.1105599629   3  在asm实例中，针对ORA-15028，生成trace文件，来分析根本原因。 SYS@+ASM1 > select pid, program from v$process where program like '%DIAG%';        PID PROGRAM ---------- ------------------------------------------------          6 oracle@dbssvra (DIAG) SYS@+ASM1 >select pid, spid, pname from v$process where pname like '%DIAG%';        PID SPID                     PNAME ---------- ------------------------ -----          6 129616                   DIAG SYS@+ASM1 >alter system set events '15028 trace name systemstate_global level 267'; System altered. 4 在asmcmd命令行中删除被占用的归档日志，让数据库自动生成trace文件ASMCMD> rm thread_1_seq_585427.1532.1105599629ORA-15032: not all alterations performedORA-15028: ASM file '+ARCH/cjdb/ARCHIVELOG/2022_05_25/thread_1_seq_585427.1532.1105599629' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute) 5 关闭ORA-15028的trace文件 SYS@+ASM1 >alter system set events '15028 trace name systemstate_global off'; System altered. 6 查看生成的trace文件-rw-r-----  1 grid oinstall      6645 May 27 10:22 +ASM1_diag_129616.trm-rw-r-----  1 grid oinstall    194058 May 27 10:22 +ASM1_diag_129616.trc-rw-r-----  1 grid oinstall  21385428 May 27 10:22 +ASM1_diag_129616_20220527102235.trc 7  经查看，在 +ASM1_diag_129616.trc文件中未找到有用的信息，但在系统生成的dump文件中（ +ASM1_diag_129616_20220527102235.trc）,有相关进程信息。为了快速找到相关信息，建议打开文件， 直接搜索thread_1_seq_585427.1532.1105599629，就会出现如下信息。  SO: 0xbcd63970, type: 132, owner: 0xba3f5248, flag: INIT/-/-/0x00 if: 0x3 c: 0x3             proc=0x1107dee20, name=ASM file, file=kff2.h LINE:898, pg=0            (kffil) netnm: cjdb1:cjdb, mapid: 20187978                    gnum: 8.1267240719, fnum: 1532.1105599629, flgs: 0x182                    family: 0.0, parent: 0.0                    opennm: +ARCH/cjdb/archivelog/2022_05_25/thread_1_seq_585427.1532.1105599629                    openflags: 0x80000000                    fullnm: +arch/cjdb/archivelog/2022_05_25/thread_1_seq_585427.1532.1105599629                    redun: 0x11, fdflg: 0x0, blksiz: 512, fsiz: 2806702                    ftype: 4, extsz: [4294967295,0,0]                    extents: 1371, start: 60, count: 1311, xp: 0x0                    base: 14302, lxcnt: 1                    enq: 0xfd0d1db0, cod: 0x0                    unlock[0].au: 0, unlock[0].disk: 0, unlock[0].flags 0x0                    unlock[1].au: 0, unlock[1].disk: 0, unlock[1].flags 0x0                    unlock[2].au: 0, unlock[2].disk: 0, unlock[2].flags 0x0                    unlock[3].au: 0, unlock[3].disk: 0, unlock[3].flags 0x0                    unlock[4].au: 0, unlock[4].disk: 0, unlock[4].flags 0x0                    unlock[5].au: 0, unlock[5].disk: 0, unlock[5].flags 0x0                    strpsz: 1048576, strpwdth: 1                    lnk: 0xbcd63ff0, 0xbcd63ff0                    aba: 0, 0                    client pid: 1180 osid: 174392 8  根据操作系统OSPID,查询数据库的sid和serial# SYS@cjdb1 >select SID,SERIAL# from v$session where PADDR in (select ADDR from  v$process where SPID=&b); Enter value for b: 174392 old   1: select SID,SERIAL# from v$session where PADDR in (select ADDR from  v$process where SPID=&b) new   1: select SID,SERIAL# from v$session where PADDR in (select ADDR from  v$process where SPID=174392)        SID    SERIAL# ---------- ----------       2813      40761 9 根据查询出的会话SID，来查看是哪个用户及其执行的SQL    SYS@cjdb1 >select b.SID,b.SERIAL#,b.USERNAME,b.SCHEMANAME,b.OSUSER,b.SQL_ID from v$session b where b.SID='2813';        SID    SERIAL# USERNAME         SCHEMANAME                     OSUSER                         SQL_ID ---------- ---------- ---------------- ------------------------------ ------------------------------ -------------       2813      40761 SJZT              SJZT                        Ruby                           2d1z203tkk3fu 10 根据SQL_ID，查看执行的SQL，经确定，认为是业务模块，使用数据库的logminer进程数据挖掘，导致的 归档日志不释放，咨询相关业务模块，经确认，可以杀相关会话。   SQL_ID  2d1z203tkk3fu, child number 0 ------------------------------------- SELECT SCN, TIMESTAMP, XID, OPERATION_CODE, ROLLBACK, SQL_REDO, ROW_ID, DATA_OBJ#, CSF, PXID, RBASQN, RBABLK, RBABYTE, TX_NAME  from V$LOGMNR_CONTENTS Plan hash value: 2156073882 --------------------------------------------------------------------------- | Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| --------------------------------------------------------------------------- |   0 | SELECT STATEMENT |                   |       |       |     1 (100)| |*  1 |  FIXED TABLE FULL| X$LOGMNR_CONTENTS |     1 |  2294 |     0   (0)| --------------------------------------------------------------------------- 11 杀相关会话，验证归档日志是否可以删除，经确认，可以进行删除。在对应的数据库节点，执行如下命令，杀相关会话alter system disconnect session '2813,40761' immediate;    等待2分钟后，在asmcmd命令行，执行如下命令进行删除，可以正常删除了。       ASMCMD> rm thread_1_seq_585427.1532.1105599629    ASMCMD> ls thread_1_seq_585427.1532.1105599629     ASMCMD-8002: entry 'thread_1_seq_585427.1532.1105599629' does not exist in directory '+ARCH/cjdb/ARCHIVELOG/2022_05_25/'   12 本文参考了Oracle官方文档为：《 Get ORA-15028 when delete datafile on ASM (Doc ID 2083351.1)》 和 《 RMAN Receives ORA-15028 When Trying To Delete Archivelog File From ASM Diskgroup (Doc ID 1466848.1)》