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