Senthil Rajendran's Blog: Oracle EBS 12.2 - adop phase=cleanup fails with "library cache pin" waits

Tuesday, July 16, 2019

Oracle EBS 12.2 - adop phase=cleanup fails with "library cache pin" waits

adop phase=cleanup fails with "library cache pin" waits

When running cleanup action in adop , it hung for ever.

Lets dig deeper to see the problem

SQL> SELECT s.sid,
s.username,
s.program,
s.module from v$session s where module like '%AD_ZD%';

2313 APPS perl@pwercd01vn074 (TNS V1-V3) AD_ZD

SQL> select event from v$session where sid=2313 ;

EVENT
----------------------------------------------------------------
library cache pin

SQL>

SQL> select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
2 3 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
4 11, 'PACKAGE BODY', 12, 'TRIGGER',
5 13, 'TYPE', 14, 'TYPE BODY',
6 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, ' 7 DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PR 8 OFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.userna 9 me
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'
10 order by lock_mode_held desc

/

OBJECT_TYPE OBJECT_NAME LOCK_MODE_HELD LOCK_MODE_REQUESTED SID SERIAL# USERNAME
------------------ -------------------- -------------- ------------------- ---------- ---------- ------------------------------
PACKAGE DBMS_SYS_SQL 2 0 3822 60962 APPS
PACKAGE DBMS_SYS_SQL 2 0 2313 27404 APPS
PACKAGE DBMS_SYS_SQL 2 0 2313 27404 APPS
PACKAGE DBMS_SYS_SQL 2 0 3822 60962 APPS
PACKAGE DBMS_SYS_SQL 0 2 3821 14545
PACKAGE DBMS_SYS_SQL 0 2 3821 14545
PACKAGE DBMS_SYS_SQL 0 3 2313 27404 APPS

PACKAGE DBMS_SYS_SQL 0 3 2313 27404 APPS

SQL> select

2 distinct

ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,ses.ksuseunm machine,

ob.kglnaown obj_owner, ob.kglnaobj obj_name

3 4 5 ,pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req

, w.state, w.event, w.wait_Time, w.seconds_in_Wait

-- lk.kglnaobj, lk.user_name, lk.kgllksnm,

6 7 8 --,lk.kgllkhdl,lk.kglhdpar

--,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,

9 10 --,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl

from

x$kglpn pn, x$kglob ob,x$ksuse ses

, v$session_wait w

11 12 13 14 where pn.kglpnhdl in

15 (select kglpnhdl from x$kglpn where kglpnreq >0 )

and ob.kglhdadr = pn.kglpnhdl

and pn.kglpnuse = ses.addr

and w.sid = ses.indx

order by seconds_in_wait desc

SID SERIAL# USERNAME MACHINE OBJ_OWNER OBJ_NAME PIN_CNT PIN_MODE PIN_REQ STATE EVENT WAIT_TIME SECONDS_IN_WAIT

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

2313 27404 APPS apprnrcod05 SYS DBMS_SYS_SQL 0 0 3 WAITING library cache pin 0 701

2313 27404 APPS apprnrcod05 SYS DBMS_SYS_SQL 2 2 0 WAITING library cache pin 0 701

803 46104 APPS orarnrcod05 SYS DBMS_SYS_SQL 2 2 0 WAITED SHORT TIME control file sequential read -1 24

Other errors on the adop logs , you can also use scanlogs to verify the errors.

[ERROR] [CLEANUP 1:1 ddl_id=69120] ORA-04020: deadlock detected while trying to lock object SYS.DBMS_SYS_SQL SQL: begin sys.ad_grants.cleanup; end;

Reference

Adop Cleanup Issue: "[ERROR] [CLEANUP] ORA-04020: deadlock detected " (Doc ID 2424333.1)

Fix :

SQL> select count(1)

from dba_tab_privs

where table_name='DBMS_SYS_SQL'

and privilege='EXECUTE'

and grantee='APPS' 2 3 4 5 ;

COUNT(1)

----------

SQL> exec sys.ad_grants.cleanup;

PL/SQL procedure successfully completed.