After database Export and Import for 19c database upgrade, three AZ tables for EBS iSetup becomes missing in 19c database:
SQL> select * from dba_tables where owner = 'AZ' and table_name in
('AZ_REQUESTS','AZ_REPORTER_DATA','AZ_DIFF_RESULTS');
no rows selected
Even iSetup is not used, Business user still wants to get them back. Oracle Doc ID 832459.1 (How To Cleanup Invalid Oracle iSetup (AZ) Tables And Recreate) provides steps on re-creating them.
$ appPWD='PWDappsXXX'
$ sysPWD='PWDsysXXX'
1. Make sure account XDB is OPEN
SQL> SELECT status FROM dba_registry WHERE comp_id = 'XDB';
STATUS
-----------
VALID
SQL> SELECT username, account_status FROM dba_users WHERE username = 'XDB';
USERNAME ACCOUNT_STATUS
----------------- ---------------
XDB LOCKED
SQL> conn system/$sysPWD
Connected.
SQL> ALTER USER xdb IDENTIFIED BY xdb ACCOUNT UNLOCK;
ALTER USER xdb IDENTIFIED BY xdb ACCOUNT UNLOCK
*
ERROR at line 1:
ORA-01031: insufficient privileges
Fix: sysdba needs to unlock XDB account on the root$ container
SQL> SELECT username, account_status FROM dba_users WHERE username = 'XDB';
USERNAME ACCOUNT_STATUS
----------------- ---------------
XDB OPEN
2. Get the script ready
SQL> show user
USER is "SYSTEM"
SQL> set pagesize 200
SQL> column OWNER format a15
SQL> column TYPE_NAME format a30
SQL> SELECT owner, type_name FROM all_types WHERE type_name LIKE 'AZ%';
OWNER TYPE_NAME
-------------- ------------------------------
APPS_NE AZ_RPTDATA_H
APPS_NE AZ_RPTDATA_V_COLL
APPS_NE AZ_RPTDATA_V_TAGS
APPS_NE AZ_RPTDIFF_H
APPS_NE AZ_RPTDIFF_V_COLL
APPS_NE AZ_RPTDIFF_V_TAGS
APPS_NE AZ_SELSETS_EXT
APPS_NE AZ_SELSETS_H
APPS_NE AZ_SELSETS_H_COLL
APPS_NE AZ_SELSETS_V652_T
APPS_NE AZ_SELSETS_V_COLL
11 rows selected.
3. Run "drop type" script as SYSTEM
SQL> show user
USER is "SYSTEM"
SQL> SELECT 'drop type ' || owner || '.' || type_name || ' force;' FROM all_types WHERE type_name LIKE 'AZ%';
Then run below lines to drop those types forcefully as SYSTEM:
drop type APPS_NE.AZ_RPTDATA_H force;
drop type APPS_NE.AZ_RPTDATA_V_COLL force;
drop type APPS_NE.AZ_RPTDATA_V_TAGS force;
drop type APPS_NE.AZ_RPTDIFF_H force;
drop type APPS_NE.AZ_RPTDIFF_V_COLL force;
drop type APPS_NE.AZ_RPTDIFF_V_TAGS force;
drop type APPS_NE.AZ_SELSETS_EXT force;
drop type APPS_NE.AZ_SELSETS_H force;
drop type APPS_NE.AZ_SELSETS_H_COLL force;
drop type APPS_NE.AZ_SELSETS_V652_T force;
drop type APPS_NE.AZ_SELSETS_V_COLL force;
4. Reset AZ password if it is lost. Then, login as AZ to drop three tables if they exist.
$ FNDCPASS apps/$appPWD 0 Y system/$sysPWD ORACLE AZ azPWD
$ sqlplus az/azPWD
SQL> DROP TABLE AZ_REQUESTS;
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> DROP TABLE AZ_REPORTER_DATA;
SQL> DROP TABLE AZ_DIFF_RESULTS;
5. Run 3 XDF scripts on OS level to create 3 tables and 6 indexes:
$AZ_TOP/patch/115/xdf/az_diff_results.xdf
$AZ_TOP/patch/115/xdf/az_reporter_data.xdf
$AZ_TOP/patch/115/xdf/az_requests.xdf
Use "tnsping $TWO_TASK" to get the database connection info to replace part of below lines.
$ cd $AZ_TOP/patch/115/xdf
$ adjava "-mx128m" "-nojit" "oracle.apps.fnd.odf2.FndXdfCmp" \
"az" "azPWD" "apps" "$appPWD" \
"thin" "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<database-host>)(PORT=<database-port>))(CONNECT_DATA=(SID=<database-SID>)))" \
"table" "az_diff_results.xdf" "$FND_TOP/patch/115/xdf/xsl"
$ adjava "-mx128m" "-nojit" "oracle.apps.fnd.odf2.FndXdfCmp" \
"az" "azPWD" "apps" "$appPWD" \
"thin" "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<database-host>)(PORT=<database-port>))(CONNECT_DATA=(SID=<database-SID>)))" \
"table" "az_reporter_data.xdf" "$FND_TOP/patch/115/xdf/xsl"
$ adjava "-mx128m" "-nojit" "oracle.apps.fnd.odf2.FndXdfCmp" \
"az" "azPWD" "apps" "$appPWD" \
"thin" "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<database-host>)(PORT=<database-port>))(CONNECT_DATA=(SID=<database-SID>)))" \
"table" "az_requests.xdf" "$FND_TOP/patch/115/xdf/xsl"
SQL> desc AZ_DIFF_RESULTS
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(240)
DISPLAY_NAME VARCHAR2(240)
REQUEST_ID NOT NULL NUMBER(15)
SOURCE NOT NULL VARCHAR2(240)
TYPE NUMBER(2)
ID NOT NULL NUMBER
PARENT_ID NUMBER
HASHCODE_DETAILS VARCHAR2(2000)
DEPTH NUMBER
IS_DIFFERENT VARCHAR2(1)
IS_TRANSFORMED VARCHAR2(1)
SHOW_ONLY_DIFF VARCHAR2(1)
PARAM1 VARCHAR2(240)
PARAM2 VARCHAR2(240)
PARAM3 VARCHAR2(240)
PARAM4 VARCHAR2(240)
PARAM5 VARCHAR2(240)
DETAIL_LOG_STATUS VARCHAR2(255)
DETAIL_LOG_MSG VARCHAR2(2000)
6. Run 3 scripts to restore the iSetup XMLDB Schemas
$ sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhDiffSchema.sql $sysPWD
... ...
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Connected.
declare
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qmtcolcb_nomatch], [ID],
[annotation], [], [], [], [], [], [], [], [], []
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 122
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 106
ORA-06512: at line 5
Fix: Database has been started with the oracle user having the wrong LIBPATH set. After 19c upgrade, some of the old 12c paths mixed in with new 19c. The fix requires a database bounce.
See Doc ID 1292089.1 (Primary Note for Oracle XML Database (XDB) Install / Deinstall)
$ sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhRptSchema.sql $sysPWD
$ sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhSelSetSchema.sql $sysPWD
... ...
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Connected.
declare
*
ERROR at line 1:
ORA-20101: Exception occurred in Selection Set Schema Registration procedure
:ORA-64479: element 'AZ_SELSETS_V' is missing 'SQLType' annotation; schema
cannot be registered by common users
ORA-06512: at line 92
Fix: Apply patch 31791566
adop phase=apply apply_mode=downtime patches=31791566 patchtop=/.../fs_ne/EBSapps/patch
7. Restore iSetup specific XMLTYPE columns
sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhAddDiffCol.sql az azPWD
sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhAddDriver.sql az azPWD
sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhAddRptCol.sql az azPWD
sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azhAddSelSet.sql az azPWD
8. For 12.2 only. All steps have to be done outside of a patching cycle (i.e. prepare has not been run)
SQL> show user
USER is "APPS"
SQL> set serveroutput on
SQL> exec ad_zd_table.patch('AZ','AZ_REQUESTS');
PL/SQL procedure successfully completed.
SQL> exec ad_zd_table.patch('AZ','AZ_REPORTER_DATA');
PL/SQL procedure successfully completed.
SQL> exec ad_zd_table.patch('AZ','AZ_DIFF_RESULTS');
PL/SQL procedure successfully completed.
SQL> desc AZ_DIFF_RESULTS
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(240)
DISPLAY_NAME VARCHAR2(240)
REQUEST_ID NOT NULL NUMBER(15)
SOURCE NOT NULL VARCHAR2(240)
TYPE NUMBER(2)
ID NOT NULL NUMBER
PARENT_ID NUMBER
HASHCODE_DETAILS VARCHAR2(2000)
DEPTH NUMBER
IS_DIFFERENT VARCHAR2(1)
IS_TRANSFORMED VARCHAR2(1)
SHOW_ONLY_DIFF VARCHAR2(1)
PARAM1 VARCHAR2(240)
PARAM2 VARCHAR2(240)
PARAM3 VARCHAR2(240)
PARAM4 VARCHAR2(240)
PARAM5 VARCHAR2(240)
DETAIL_LOG_STATUS VARCHAR2(255)
DETAIL_LOG_MSG VARCHAR2(2000)
ATTR_DIFF SYS.XMLTYPE(XMLSchema "http:
//isetup.oracle.com/2006/dif
fresultdata.xsd" Element "H"
)
SQL> SELECT owner, type_name FROM all_types WHERE type_name LIKE 'AZ%';
OWNER TYPE_NAME
--------------- ------------------------------
SYSTEM AZ_RPTDATA_H
SYSTEM AZ_RPTDATA_V_COLL
SYSTEM AZ_RPTDATA_V_TAGS
SYSTEM AZ_RPTDIFF_H
SYSTEM AZ_RPTDIFF_V_COLL
SYSTEM AZ_RPTDIFF_V_TAGS
SYSTEM AZ_SELSETS_EXT
SYSTEM AZ_SELSETS_H
SYSTEM AZ_SELSETS_H_COLL
SYSTEM AZ_SELSETS_V
SYSTEM AZ_SELSETS_V_COLL
11 rows selected.
9. Provide appropriate grants
$ sqlplus apps/$appPWD @$AZ_TOP/patch/115/sql/azR12grants.sql $sysPWD
... ...
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Connected.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Commit complete.
iSetup tables have column of data-type "SYS.XMLTYPE". If it becomes invalid, EBS may hit below message. That may require table re-creation also.
PLS-00905: object AZ.AZ_<xxxxx> is invalid
