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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

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

在ASM磁碟組中刪除歸檔日誌報ORA-15028

相關文章