Author: heemasatapathy

Heema is an Oracle Ace Pro, IEEE Senior Member, and experienced speaker at both local and international IT conferences. With two decades of hands-on experience as a senior DBA and Solution Architect, Heema is adept at database administration and eager to embrace new technologies. Known for being customer-focused, organized, and self-motivated, Heema has a strong track record in managing technical projects and communicating solutions effectively. His global IT experience spans India, Japan, Malaysia, Singapore, Switzerland, and the USA. He holds a Bachelor of Engineering degree with Honors.

adgrants before applying latest Oracle E-Business Suite Online Patching Readiness Report Patch 33993716 fails

Symptoms

SQL> @/mnt/install_images/oracle/EBS122Patches/OnlineEnablement/OPERR/ADC/33993716/admin/adgrants.sql APPS

======================

The following ERRORS and WARNINGS have been encountered during this adgrants session:

TIME_STAMP                 SESSION_ID LOG_MODULE                                                                            LOG_LEVEL

——————– ———- —————————————— ———-

LOG_MESSAGE

———————————————————————————————————————————————————————————————————————————————————-

2023/04/05 10:27:45      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-04042: procedure, function, package, or package body does not exist, SQL: grant EXECUTE on “DBMS_SCHEMA_COPY” to “APPS”

2023/04/05 10:27:45      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-04042: procedure, function, package, or package body does not exist, SQL: grant EXECUTE on “DBMS_SCHEMA_COPY” to “SYSTEM” with grant option

2023/04/05 10:27:46      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-00942: table or view does not exist, SQL: grant WRITE on “DIRECTORY FND_DIAG_DIR” to “APPS”

2023/04/05 10:27:46      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-00942: table or view does not exist, SQL: grant READ on “DIRECTORY FND_DIAG_DIR” to “APPS”

2023/04/05 10:27:46      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-04042: procedure, function, package, or package body does not exist, SQL: grant EXECUTE on “XDB_MIGRATESCHEMA” to “APPS”

2023/04/05 10:27:46      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-00942: table or view does not exist, SQL: grant SELECT on “XDB$MOVESCHEMATAB” to “APPS”

2023/04/05 10:27:46      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-00942: table or view does not exist, SQL: grant INSERT on “XDB$MOVESCHEMATAB” to “APPS”

2023/04/05 10:27:46      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-00942: table or view does not exist, SQL: grant UPDATE on “XDB$MOVESCHEMATAB” to “APPS”

2023/04/05 10:27:46      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-00942: table or view does not exist, SQL: grant DELETE on “XDB$MOVESCHEMATAB” to “APPS”

2023/04/05 10:27:46      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-00942: table or view does not exist, SQL: grant SELECT on “XDB$MOVESCHEMATAB” to “SYSTEM” with grant option

2023/04/05 10:27:46      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-00942: table or view does not exist, SQL: grant INSERT on “XDB$MOVESCHEMATAB” to “SYSTEM” with grant option

2023/04/05 10:27:46      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-00942: table or view does not exist, SQL: grant UPDATE on “XDB$MOVESCHEMATAB” to “SYSTEM” with grant option

2023/04/05 10:27:46      1764 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE                    WARNING

ORA-00942: table or view does not exist, SQL: grant DELETE on “XDB$MOVESCHEMATAB” to “SYSTEM” with grant option

13 rows selected.

Grants given by this script have been written to the ad_zd_logs table.

You can run $AD_TOP/sql/ADZDSHOWLOG.sql to produce a report showing these grants.

Commit complete.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

Version 19.18.0.0.0

rgp6db(oracle):/dodiapps/rgp6/rgp6db/19c/appsutil/admin

$

Fix:

conn to APPS schema on PDB

SQL> select status,object_type from all_objects where object_name like ‘AD_ZD_PREP’;

STATUS OBJECT_TYPE

VALID PACKAGE
INVALID PACKAGE BODY

SQL> alter package AD_ZD_PREP compile body;

Warning: Package Body altered with compilation errors.

SQL> sho errors
Errors for PACKAGE BODY AD_ZD_PREP:

LINE/COL ERROR

467/3 PL/SQL: SQL Statement ignored
467/19 PL/SQL: ORA-00942: table or view does not exist
471/5 PL/SQL: SQL Statement ignored
471/21 PL/SQL: ORA-00942: table or view does not exist
491/5 PL/SQL: Statement ignored
491/5 PLS-00201: identifier ‘SYS.XDB_MIGRATESCHEMA’ must be declared
505/5 PL/SQL: SQL Statement ignored
505/21 PL/SQL: ORA-00942: table or view does not exist
SQL>

The package ‘xdb_migrateschema’ is missing. It is required for AD_ZD_PREP to compile in an E-Business Suite environment.

SQL> select owner, object_name from dba_objects where object_name like upper (‘xdb_migrateschema’);

no rows selected

SQL>

I. Confirm if the package ‘xdb_migrateschema’ exists using the following select statement:
select owner, object_name from dba_objects where object_name like upper (‘xdb_migrateschema’);

If it does NOT exist, then use the following scripts to create it and confirm results again using the above query:

. setsid
export ORACLE_PDB_SID=RGP6
conn / as sysdba

@?/rdbms/admin/dbmsxdbschmig.sql

@?/rdbms/admin/prvtxdbschmig.plb

II. Re-run adgrants.sql.

The following ERRORS and WARNINGS have been encountered during this adgrants session:

. setsid
export ORACLE_PDB_SID=RGP6
conn / as sysdba
SQL> sho con_name

CON_NAME

RGP6
SQL> @/mnt/install_images/oracle/EBS122Patches/OnlineEnablement/OPERR/ADC/33993716/admin/adgrants.sql APPS

TIME_STAMP SESSION_ID LOG_MODULE LOG_LEVEL

LOG_MESSAGE

2023/04/05 11:11:33 1015 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE WARNING
ORA-04042: procedure, function, package, or package body does not exist, SQL: grant EXECUTE on “DBMS_SCHEMA_COPY” to “APPS”

2023/04/05 11:11:33 1015 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE WARNING
ORA-04042: procedure, function, package, or package body does not exist, SQL: grant EXECUTE on “DBMS_SCHEMA_COPY” to “SYSTEM” with grant option

2023/04/05 11:11:34 1015 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE WARNING
ORA-00942: table or view does not exist, SQL: grant WRITE on “DIRECTORY FND_DIAG_DIR” to “APPS”

2023/04/05 11:11:34 1015 ad.plsql.ad_zd_sys.GIVE_PRIVILEGE WARNING
ORA-00942: table or view does not exist, SQL: grant READ on “DIRECTORY FND_DIAG_DIR” to “APPS”

Grants given by this script have been written to the ad_zd_logs table.
You can run $AD_TOP/sql/ADZDSHOWLOG.sql to produce a report showing these grants.

Commit complete.

After 28426427 from ad/txk 10 patches getting grant warning on DBMS_UPG_ objects (Doc ID 2507343.1)
The above warnings can be ignored when running adgrants.sql. If receiving these warnings system likely does not have dbvault enabled, which is not an EBS requirement.

III. Recompile the package AD_ZD_PREP and ensure it compiles successfully.

sqlplus apps
SQL> select status,object_type from all_objects where object_name like ‘AD_ZD_PREP’;

STATUS OBJECT_TYPE

VALID PACKAGE
INVALID PACKAGE BODY

SQL> alter package AD_ZD_PREP compile body;

Package body altered.

SQL> select status,object_type from all_objects where object_name like ‘AD_ZD_PREP’;

STATUS OBJECT_TYPE

VALID PACKAGE
VALID PACKAGE BODY

SQL>
IV. Continue with the upgrade and confirm online patching is successfully enabled.

Metalink Reference

12.2 E-Business Suite DBA Upgrade Script ADZDEXRPT.sql Fails On Invalid Object ‘AD_ZD_PREP’ Or Patch 13543062 Fails With Error: ORA-06508: PL/SQL: could not find APPS.AD_ZD_PREPAD_ZD_PREP Due To Missing Package ‘xdb_migrateschema’ (Doc ID 2066607.1)

After 28426427 from ad/txk 10 patches getting grant warning on DBMS_UPG_ objects (Doc ID 2507343.1)

Rel 12.2 driver fails at cnsca.odf

Start time for statement below is: Fri Mar 31 2023 14:45:10

CREATE UNIQUE INDEX CN.”CN_SCA_RULE_ATTRIB_ALL_TL_U2″ ON

 CN.CN_SCA_RULE_ATTRIBUTES_ALL_TL (SCA_RULE_ATTRIBUTE_ID, ORG_ID, LANGUAGE)

 LOGGING STORAGE (FREELIST GROUPS 4 FREELISTS 4 )  PCTFREE 10 INITRANS 11

 MAXTRANS 225 COMPUTE STATISTICS   TABLESPACE APPS_TS_SEED

Statement executed.

AD Worker error:

The index cannot be created as the table has duplicate keys.

Solution:

During Upgrade to 12.2.0, Script CNSCA.ODF Fails – Create Index CN_SCA_RULE_ATTRIB_ALL_TL_U2 – The index cannot be created as the table has duplicate keys. (Doc ID 1674721.1)

SQL> SELECT SCA_RULE_ATTRIBUTE_ID, ORG_ID, LANGUAGE, count() FROM CN.CN_SCA_RULE_ATTRIBUTES_ALL_TL WHERE ORG_ID IS NULL GROUP BY SCA_RULE_ATTRIBUTE_ID, ORG_ID, LANGUAGE HAVING count()>1 2 3 4 5 ;

SCA_RULE_ATTRIBUTE_ID ORG_ID LANG COUNT(*)

            -1020            PTB          14
            -1019            PTB          14
            -1009            PTB          14
            -1003            PTB          14
            -1021            PTB          14
            -1018            PTB          14
            -1014            PTB          14
            -1008            PTB          14
            -1001            PTB          14
            -1000            PTB          14
            -1016            PTB          14

SCA_RULE_ATTRIBUTE_ID ORG_ID LANG COUNT(*)

            -1015            PTB          14
            -1011            PTB          14
            -1002            PTB          14
            -1023            PTB          14
            -1013            PTB          14
            -1012            PTB          14
            -1007            PTB          14
            -1006            PTB          14
            -1005            PTB          14
            -1017            PTB          14
            -1022            PTB          14

SCA_RULE_ATTRIBUTE_ID ORG_ID LANG COUNT(*)

            -1010            PTB          14
            -1004            PTB          14

24 rows selected.

SQL> SELECT SCA_RULE_ATTRIBUTE_ID, ORG_ID, LANGUAGE, count() FROM CN.CN_SCA_RULE_ATTRIBUTES_ALL_TL WHERE ORG_ID IS NOT NULL GROUP BY SCA_RULE_ATTRIBUTE_ID, ORG_ID, LANGUAGE HAVING count()>1 2 3 4 5
6 ;

no rows selected

SQL> CREATE table cn_sca_rule_attrib_all_tl_bkp AS
SELECT * FROM cn_sca_rule_attributes_all_tl; 2

Table created.

SQL> DELETE FROM cn_sca_rule_attributes_all_tl WHERE org_id IS NULL;

336 rows deleted.

SQL> commit;

Commit complete.

SQL>

Metalink Reference:

During Upgrade to 12.2.0, Script CNSCA.ODF Fails – Create Index CN_SCA_RULE_ATTRIB_ALL_TL_U2 – The index cannot be created as the table has duplicate keys. (Doc ID 1674721.1)

ADZDMVREFRESHNEEDED.sql before kicking off Rel 12.2 Driver

To reduce the overall upgrade time, you can optionally run the ADZDMVREFRESHNEEDED.SQL script before starting with the Release 12.2.0 upgrade.

  1. Validated the version of all ADZDMVREFRESHNEEDED.sql present.

$ ls -ltr /dodiapps/rgp6/apps/apps_st/appl/ad/12.0.0/patch/115/sql/ADZDMVREFRESHNEEDED.sql
-rwxr-xr-x 1 oracle dba 3007 Apr 28 18:01 /dodiapps/rgp6/apps/apps_st/appl/ad/12.0.0/patch/115/sql/ADZDMVREFRESHNEEDED.sql
rgp6app(oracle):/dodiapps/rgp6/apps/apps_st/appl/ad/12.0.0/patch/115/sql

$ adident Header /mnt/install_images/oracle/EBS122Patches/ADTXK14/33600809/ad/patch/115/sql/ADZDMVREFRESHNEEDED.sql
/mnt/install_images/oracle/EBS122Patches/ADTXK14/33600809/ad/patch/115/sql/ADZDMVREFRESHNEEDED.sql:
$Header ADZDMVREFRESHNEEDED.sql 120.0.12020000.2 2016/06/30 08:38:31 mkumandu noship $
rgp6app(oracle):/mnt/install_images/oracle/EBS122Patches

$ adident Header /mnt/install_images/oracle/EBS122Patches/OnlineEnablement/OPERR/ADB/31026891/ad/patch/115/sql/ADZDMVREFRESHNEEDED.sql
/mnt/install_images/oracle/EBS122Patches/OnlineEnablement/OPERR/ADB/31026891/ad/patch/115/sql/ADZDMVREFRESHNEEDED.sql:
$Header ADZDMVREFRESHNEEDED.sql 120.0.12010000.3 2016/08/01 10:21:30 sstomar noship $
rgp6app(oracle):/mnt/install_images/oracle/EBS122Patches

$ adident Header /dodiapps/rgp6/apps/apps_st/appl/ad/12.0.0/patch/115/sql/ADZDMVREFRESHNEEDED.sql
/dodiapps/rgp6/apps/apps_st/appl/ad/12.0.0/patch/115/sql/ADZDMVREFRESHNEEDED.sql:
$Header ADZDMVREFRESHNEEDED.sql 120.0.12010000.3 2016/08/01 10:21:30 sstomar noship $
rgp6app(oracle):/mnt/install_images/oracle/EBS122Patches
$

$adident Header /mnt/install_images/oracle/EBS122Patches/OnlineEnablement/OPERR/ADC/33993716/ad/patch/115/sql/ADZDMVREFRESHNEEDED.sql
/mnt/install_images/oracle/EBS122Patches/OnlineEnablement/OPERR/ADC/33993716/ad/patch/115/sql/ADZDMVREFRESHNEEDED.sql:
$Header ADZDMVREFRESHNEEDED.sql 120.0.12020000.2 2016/06/30 08:38:31 mkumandu noship $
rgp6app(oracle):/mnt/install_images/oracle/EBS122Patches
$

  1. Execute the file under Online Enablement Readiness Report Patch 31026891 as suggested by Oracle SR analyst

sqlplus apps
@/mnt/install_images/oracle/EBS122Patches/OnlineEnablement/OPERR/ADB/31026891/ad/patch/115/sql/ADZDMVREFRESHNEEDED.sql
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
rgp6app(oracle):/mnt/install_images/oracle/EBS122Patches
$

Rel 12.2 Driver fails at pydccore.sql

ATTENTION: All workers either have failed or are waiting:

                  FAILED: file pydccore.sql on worker  3.

ATTENTION: Please fix the above failed worker(s) so the manager can continue.

Start time for file is: Fri Mar 31 2023 14:07:52

sqlplus -s APPS/***** @/dodiapps/rgp6/rgp6apps/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adsqlwrapper.sql ‘/dodiapps/rgp6/rgp6apps/fs1/EBSapps/appl/pay/12.0.0/patch/115/sql/pydccore.sql &un_pay &pw_pay &index_tspace’

Connected.

PL/SQL procedure successfully completed.

declare

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ORA-06512: at “SYS.DBMS_SQL”, line 1134

ORA-06512: at line 563

FIX: Checked blocking sessions in the database and found NONE. Restarted Failed Worker using adctrl option 2 Tell worker to restart a failed job and it completed  

Rapid Install Rel 12.2 binaries using Option “Upgrade to Oracle E-Business Suite Release 12.2.0”

Symptom

During Rapid Install Rel 12.2 using Option “Upgrade to Oracle E-Business Suite Release 12.2.0” and at Validate System Configuration Screen, DB service_name check fails.

Fix

Conditional Action: For Oracle Database 11g Release 2 (11.2.0.4) and Oracle Database 12c Release 1 (12.1.0.2) only, the database init. ora parameter service_names must have an entry called ebs_patch.

Conditional Action: This note applies only if you are upgrading from Oracle E-Business Suite Release 12.1.3 with a multitenant architecture. For a multitenant architecture, you can ignore the failure related to ‘DB service_names check’.