重庆思庄Oracle、Redhat认证学习论坛

标题: Get ORA-15028 when delete datafile on ASM [打印本页]

作者: 刘泽宇 时间: 2024-9-15 13:19
标题: Get ORA-15028 when delete datafile on ASM
现象：
On : 11.1.0.7 version, STORAGE

When attempting to delete unused datafile in asmcmd,
the following error occurs.

ERROR
-----------------------
ASMCMD> rm -rf TBS_ODS_201505.1978.877567729
ORA-15032: not all alterations performed
ORA-15028: ASM file '+DATADG/<database>/datafile/TBS_ODS_201505.1978.877567729' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)

原因：
Since lsof command in ASMCMD is only available in 11.2 and above, asmcmd of 11.1.0.7 do not support it.

So we can track this issue on ASM instance by using errorstack:

alter system set events '15028 trace name errorstack level 3';
Note: To turn off this event after debug:

alter system set events '15028 trace name context off';
From errorstack trace of ORA-15028, we can see process 19453 (with sid 115, serial#42340) have a LIBRARY OBJECT LOCK on datafile TBS_ODS_201505.1978.877567729, so rm datafile failed:

   SO: 0x68b0cf60, type: 70, owner: 0x70ff2b88, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x70e5d4e0, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8003, pg=0<===LIBRARY OBJECT LOCK in ASM.
   LIBRARY OBJECT LOCK: 0x68b0cf60 handle=0x68b0c5f8 mod=N<===handle=0x68b0c5f8, this library object lock.
   pnc=(nil) pns=(nil) cbb=1 rpr=1 exc=1 ilh=(nil) ctx=(nil)
   use=0x70ff2b88 ses=0x70ff2b88 cnt=1 flg=CNB/[0001] spn=0x5657de49
   LIBRARY HANDLE:0x68b0c5f8 bid=103749 hid=437b9545 lmd=N pmd=0 sta=VALD
   name=alter diskgroup /*ASMCMD*/ "DATADG" drop file '+<DISKGROUP>/<database>/datafile/TBS_ODS_201505.1978.877567729'<===The datafile
   hash=0c46526f95b34fc7af8ef83f437b9545 idn=0
   tim=11-27-2015 12:38:32    kkkk-dddd-llll=0000-0001-0001
   exc=1 ivc=0 ldc=2 slc=1 lct=1 pct=1
   cbb=1 rpr=1 kdp=1 kep=1 bus=0 hus=0 rfc=0 dbg=0
   dmtx=0x68b0c6a0(0, 0, 0) mtx=0x68b0c710(0, 13, 0)
   nsp=CRSR(00) typ=CRSR(00) llm=0 flg=RON/KGHP/TIM/PN0/DBN/[10012840]
   lwt=0x68b0c680[0x68b0c680,0x68b0c680]
   pwt=0x68b0c660[0x68b0c660,0x68b0c660]
   ref=0x68b0c690[0x68b0c690,0x68b0c690]
      LIBRARY OBJECT: 0x681b7790
      flg=EXS[0001] pfl=[0000] ssta=VALD
      CHILDREN: size=16
      child# table reference handle
      ------ -------- --------- --------
         0 681b7640  681b71c0 68b0c408
      DATA BLOCKS:
      data#    heap  pointer status pins change whr
      ----- -------- -------- --------- ---- ------ ---
         0 68b0c540 681b7870 681b7710 I/P/A/-/- 0 NONE 00
...
SO: 0x70ff2b88, type: 4, owner: 0x70e5d4e0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x70e5d4e0, name=session, file=ksu.h LINE:10719, pg=0
(session) sid: 115 ser: 42340 trans: 0x6eeeaa40, creator: 0x70e5d4e0<===session sid115,serial#42340
            flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
            flags2: (0x48009) -/DDLT2/INC
            DID: , short-term DID:
            txn branch: (nil)
            oct: 193, prv: 0, sql: 0x68b0c5f8, psql: 0x68b1a470, user: 0/SYS<====The LIBRARY OBJECT LOCK handle is sql:0x68b0c5f8, and executed by this session object 0x70ff2b88。
ksuxds FALSE at location: 0
service name: SYS$USERS
client details:
   O/S info: user: oracle, term: pts/3, ospid: 19453<===ospid is 19453
   machine: bidw1 program: perl@bidw1 (TNS V1-V3)<====it is a perl process
   application name: perl@bidw1 (TNS V1-V3), hash value=611414084
Current Wait Stack:
   Not in wait; last wait ended 0.487780 sec ago
Wait State:
   auto_close=0 flags=0x21 boundary=(nil)/-1
Session Wait History:
   0: waited for 'SQL*Net message from client'<===And the session is on idle wait.
      driver id=62657100, #bytes=1, =0
      wait_id=26745 seq_num=26746 snap_id=1
      wait times: snap=0.000175 sec, exc=0.000175 sec, total=0.000175 sec
      wait times: max=infinite
      wait counts: calls=0 os=0
      occurred after 0.000011 sec of elapsed time

From this process state dump in errorstack trace, we can see this datafile is locked by session (sid115,serial#42340), so it cannot be deleted.

An alternative way is to find it by using systemstate dump. In systemstate dump trace, search the file name, and then we can find the process id behind it:

SO: 0xbad98e58, type: 151, owner: 0xbaba35f0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0xc07697d0, name=ASM file, file=kff2.h LINE:969, pg=0 conuid=0
(kffil) netnm: <DATABASE>2:<DATABASE>, mapid: 8 ts: 03/24/2016 04:20:52
gnum: 2.4043856141, fnum: 268.847044817, flgs: 0x182
family: 0.0, parent: 0.0
opennm: +<DISKGROUP>/<DATABASE>/TEMPFILE/temp.268.847044817
openflags: 0x80100000
fullnm: +<DISKGROUP>/<DATABASE>/TEMPFILE/temp.268.847044817<===
redun: 0x11, fdflg: 0x0, blksiz: 8192, fsiz: 211713
ftype: 6, extsz: [4294967295,0,0]
extents: 1655, start: 0, count: 60, xp: 0x0
base: 0, lxcnt: 0
enq: 0xbad9d8f8, 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
mapsz: 1655
lnk: 0x0, 0x0
aba: 0, 0
client pid: 19 osid: 5925 <<<os pid

处理方法：
1. Release the LIBRARY OBJECT LOCK on the datafile TBS_ODS_201505.1978.877567729,
1) Kill process 19453 on node bidw1:

#ps -ef|grep 19453
#kill -9 19453
Or
2) Kill it's session from ASM instance:

export ORACLE_SID=+ASM1
sqlplus / as sysdba
alter system kill session '115,42340';

2. Retry delete datafile on asmcmd:

ASMCMD> rm -rf TBS_ODS_201505.1978.877567729

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)