Senthil Rajendran's Blog

Wednesday, July 24, 2019

Oracle EBS 12.2 - ADOP patching on a downtime mode with [ERROR] Patch service is not exist or running

ADOP patching on a downtime mode with [ERROR] Patch service is not exist or running

Please note , if you get ADOP issues on PROD please read the logs and understand the problem before executing any commands.

When applying adop patches on a downtime mode you might see an error that the patch service is not existing or running. Please note NOT all patches are meant to be applied to a downtime mode so read the readme of the patch to understand the patching process. Exception cases breaking the rule book patches can be applied to a development environment but you have to know how to deal with post adop failure cases.

In this case it is clear that the patch service is not existing.

adop phase=apply patches=1234567 apply_mode=downtime

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:

Validating credentials.

Initializing.
Run Edition context : /p01/ebs/appl/fs1/inst/apps/ebs_app01/appl/admin/ebs_app01.xml
Patch edition context: /p01/ebs/appl/fs2/inst/apps/ebs_app01/appl/admin/ebs_app01.xml
Patch file system free space: 77.95 GB

Validating system setup.
Node registry is valid.
[ERROR] Patch service is not exist or running
[WARNING] ETCC: The following required database fixes have not been applied to node app01:
bla bla bla
Encountered the above errors when performing database validations.
Resolve the above errors and restart adop.

Fix : connect as APPS user and execute
SQL> exec ad_zd_prep.create_patch_service;

Run ADOP now and it should proceed further.

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.