在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 ARCH

DB_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.1105599629

ORA-15032: not all alterations performed

ORA-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)》