Workers Failing Scenarios In R12

Workers Failing Scenarios In R12

Scenario 1

ADJRIINITPASSWD.sql Fails With An ORA-01031 Error:

sqlplus -s APPS/***** @/oracle/prod/web/apps/apps_st/appl/ad/12.0.0/patch/115/sql/ADJRIINITPASSWD.sql
Connected.
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 101
ORA-06512: at "APPS.AD_JAR", line 44
ORA-06512: at line 2

Soution:

Issue the commands:

$ sqlplus apps/<appspwd>

SQL> create or replace context AD_JAR using AD_JAR;

Restart the failed worker from adctrl.

Reference Metalink Doc ID 1597168.1


Scenario 2

ORA-01031 occurs while Granting SELECT on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role

GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role

AD Worker error:
The following ORACLE error:

ORA-01031: insufficient privileges

occurred while executing the SQL statement:

GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role

Error occurred in file

/misprod6/misap/apps/apps_st/appl/ad/12.0.0/patch/115/sql/ademusr.sql

with arguments '&systempwd &un_fnd &pw_fnd &un_apps &pw_apps'.

CAUSE:

APPS does not have enough privilege to grant select on DBA_USERS_WITH_DEFPWD to another users.

Solution:

To implement the solution, please execute the following steps:

Log into the database node as applmgr user and run the following:

$ sqlplus "/ as sysdba"

GRANT select on DBA_USERS_WITH_DEFPWD to SYSTEM with grant option;

connect system/pwd

GRANT select on DBA_USERS_WITH_DEFPWD to em_oam_monitor_role;

Run "adctrl" to start the failed adworker.

Reference Metalink Doc ID 2093641.1


Scenario 3

Error Applying CPU JAN 2015 Patch 19873049 while Executing ADFIXUSER.sql PLS-00201: identifier 'SYS.AD_ZD_SYS' must be declared

ERROR sqlplus -s APPS/***** @$APPL_TOP/ad/12.0.0/patch/115/sql/ADFIXUSER.sql
Connected.
sys.ad_zd_sys.fix_sysuser;
*
ERROR at line 2:
ORA-06550: line 2, column 4:
PLS-00201: identifier 'SYS.AD_ZD_SYS' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored

Solution:

To implement the solution, please execute the following steps:

1. Run the adgrants.sql script as a user that can connect as SYSDBA to grant privileges to selected SYS objects and create PL/SQL profiler objects.
use the adgrants.sql provided by the patch

Usage:

Create $ORACLE_HOME/appsutil/admin on the database server.

Compare the version of adgrants.sql(UNIX) in $APPL_TOP/admin to that in patch directory. Or, compare the version of adgrants_nt.sql(Windows) in %APPL_TOP%\admin to that in patch directory.

Copy the higher version of adgrants.sql (UNIX) to $ORACLE_HOME/appsutil/admin. Or, copy higher version of adgrants_nt.sql (Windows) to %ORACLE_HOME%\appsutil\admin.

Set the environment to point to ORACLE_HOME on the database server.

Use SQL*Plus to run the script:

UNIX:
$ sqlplus /nolog
SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql <APPS schema name>

Windows:
C:\> sqlplus /nolog
SQL> @%ORACLE_HOME%\appsutil\admin\adgrants_nt.sql <APPS schema name>

Use adctrl to fix restart the failed job.

Reference Metalink Doc ID 1965199.1


Scenario 4

ORA-00060: deadlock detected while waiting for resource, ORA-06512: at "SYS.AD_ZD_SYS", line 39

ADFIXUSER.sql Deadlocks With ORA-00060

running ADFIXUSER.sql deadlocks with:

This can be reproduced with the following steps:

Run the EBS (12.1.3) script ADFIXUSER.sql which calls sys.ad_zd_sys.fix_sysuser;

sqlplus -s APPS/**** @/u01/applmgr/patch/apps/apps_st/appl/ad/12.0.0/patch/115/sql/ADFIXUSER.sql

The following error results:

begin
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.AD_ZD_SYS", line 39
ORA-06512: at line 2

The locks appear to be on these objects:

USERNAME TERMINAL KILL TABLE_LOCKED LOCK_MODE
------------ --------- ------------------------
SYS UNKNOWN 1149,7 SYS.WRI$_ADV_OBJSPACE_TR Row Exclusive
SYS UNKNOWN 1149,7 SYS.WRI$_ADV_OBJECTS Row Exclusive
SYS UNKNOWN 1149,7 SYS._NEXT_OBJECT Row Exclusive
SYS UNKNOWN 1149,7 SYS._NEXT_OBJECT Row Exclusive
SYS UNKNOWN 1149,7 SYS.ORA$BASE Share
SYS UNKNOWN 1149,7 HR.IRC_POSTING_CONTENTS Share

CAUSE:

The adgrants.sql (version 120.26.12010000.12) delivered with patches 20395533 and 20203366 has an error at line 488 from the apostrophe:

---Line 488 of adgrants.sql ------------
REm to DataPump's Metadata API.
---------------------------------------

Solution:

Edit the adgrants.sql and remove the apostrophe after DataPump on line 488 and save.

Re-test running adgrants.sql after the update.

Reference Metalink Doc ID 2028437.1