EBS steps when database is upgraded to 19c using Export/Import

In some special situations, database can not be upgraded to 19c by Database Upgrade procedure. For example, run hcheck as sysdba (Doc ID 136697.1). If get below error, you have to fix the problem before moving forward with database upgrade.

Problem Summary

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

HCKW-0027: System Tablespace does not have relative file number of 1  (Doc ID 2364065.1)

Problem Description

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

Running hcheck.sql

Returned warning:

 - SystemNotRfile1 ... 1201000200 > 902000000 03/04 16:03:08 WARN

Confirm file names caused the mess-up:

SQL> select RELATIVE_FNO fno, file_id, substr(file_name,1,50) fileName 

from dba_data_files where file_name like '%sys%';

FNO  File_ID  FILENAME

  16              1   /oradata/u10/EBSDEV/system01.dbf

    8              8   /oradata/u10/EBSDEV/system06.dbf

  87            87   /oradata/u10/EBSDEV/system07.dbf

etc … … 

SQL> select RELATIVE_FNO, file_id, substr(file_name,1,50) fileName 

from dba_data_files where file_id =16;

FNO  File_ID  FILENAME

  16           16   /oradata/u11/EBSDEV/APPS_TS_TX_IDX36.dbf

Steps on EBS apps side when database export & import are used to upgrade the database:

1. Make sure all pre-requisite patches were applied to apps side 

A list of EBS patches are needed before 19c database upgrade. See Doc ID 2552181.1 (Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle  Database 19c).

2. Run through patch cycle to complete adop cleanup

On the current run file system:

Run fs_clone first (if no pending ADOP session exists). It will screen out problems and help to fix potential issues (for example, fs_clone was not executed right after ssl cert renewal):

$ adop phase=fs_clone

If troubleshooting was performed, make sure nothing is running on PATCH (say, fs2) file system and log onto EBS site to do a sanity check.

$ ps -ef | grep fs2

Starting a new OS session on primary node:

$ adop phase=prepare

$ adop phase=actualize_all

$ adop phase=finalize finalize_mode=full

$ adop -status

... ...

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

Node Name  Node Type      Phase           Status          Started              Finished             Elapsed

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

node1    master    PREPARE       COMPLETED       2022/X/14 20:32:32  2022/X/14 20:38:05  0:05:33

                            APPLY             ACTIVE

                            FINALIZE       COMPLETED       2022/X/14 21:27:31  2022/X/14 21:34:38  0:07:07

                            CUTOVER      NOT STARTED

                            CLEANUP       NOT STARTED

node2    slave      PREPARE       COMPLETED       2022/X/14 20:32:31  2022/X/14 20:38:07  0:05:36

                            APPLY            NOT STARTED

                            FINALIZE      COMPLETED       2022/X/14 21:27:31  2022/X/14 21:34:38  0:07:07

                            CUTOVER     NOT STARTED

                            CLEANUP     NOT STARTED

node3    slave      PREPARE      COMPLETED       2022/X/14 20:32:31  2022/X/14 20:39:39  0:07:08

                           APPLY             NOT STARTED

                           FINALIZE      COMPLETED       2022/X/14 21:27:31  2022/X/14 21:34:38  0:07:07

                           CUTOVER      NOT STARTED

                           CLEANUP      NOT STARTED

May see concurrent job "Online Patching In Progress"

$ adop phase=cutover

On the new run file system:

$ adop phase=cleanup cleanup_mode=full 

Also run SQL scripts by apps:

@$AD_TOP/sql/ADZDSHOWOBJS.sql - to check which objects still need to be tidied up.

@$AD_TOP/sql/ADZDSHOWLOG.sql  - to make sure that it ran through properly

@$AD_TOP/sql/ADZDSHOWED.sql     - to get the list of Editions

Shutdown apps services

3. DBA upgrades database to 19c by using Export and Import

4. Modify apps files after DBA provides new TNS entry. 

Below is an example of the new TNS entry. 

EBSDEV=

        (DESCRIPTION=

                (ADDRESS=(PROTOCOL=tcp)(HOST=ebs_db1d.domain.com)(PORT=1560))

            (CONNECT_DATA=

                (SERVICE_NAME=ebs_EBSDEV)

                (INSTANCE_NAME=CEBSDEV)

            )

        )

To modify the TNS entries, edit the $TNS_ADMIN/tnsnames.ora files to specify the CDB instance name. 

$ cd $TNS_ADMIN

$ vi tnsnames.ora

replace every INSTANCE_NAME to CDBEDEV

replace every SERVICE_NAME to ebs_EBSDEV (if it is different from 12c)

$ sqlplus apps/appsPWD

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> 

$ tnsping ebsdev

... ...

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=ebsDB1d.domain.com)(PORT=1560)) (CONNECT_DATA= (SERVICE_NAME=ebs_EBSDEV) (INSTANCE_NAME=CDBEDEV)))

OK (0 msec)

5. Edit $CONTEXT_FILE on both RUN and PATCH file systems

Update the following values in the context file.

Variable Name                         Value

s_dbhost                                          New database hostname (if changed)

s_dbport                                         New database port (if changed)

s_dbGlnam                                      CDBEDEV    (Seems autoconfig does not keep it)

s_apps_jdbc_connect_descriptor NULL (<jdbc_url oa_var="s_apps_jdbc_connect_descriptor"/>)

s_dbport                                         New database port

s_applptmp                                 Defined in UTL_FILE_DIR (e.g. /path/to/EBSDEV/utl_dir)

6. Run AutoConfig on RUN and PATCH file systems in all nodes

SQL> select * from FND_OAM_CONTEXT_FILES;     

SQL> select * from fnd_nodes;         <== only one row for db node

$ ./adautocfg.sh

Notes: 

a). AutoConfig will fail on jtfictx.sh because jtfictx.sql requires AD_CTX_DDL, which is created in a later step. AutoConfig will be run again after AD_CTX_DDL has been created. 

b). After AutoConfig, s_apps_jdbc_connect_descriptor will get new value.

c). AutoConfig will overwrite variable s_dbGlnam ?

Table FND_OAM_CONTEXT_FILES might be cleaned during database upgrade. If CONTEXT FILE records for PATCH system are missing, run AutoConfig on PATCH file system as well to avoid ADOP session failure in the future.

To run AutoConfig on PATCH file system, disable a trigger first (and afterwards enable it).

SQL> conn system/systemPWD

Connected.

SQL> alter trigger ebs_logon disable;

Trigger altered.

SQL> select * from FND_OAM_CONTEXT_FILES;     <== entries for both RUN and PATCH

7. Create the CTX objects

Certain Oracle Text objects are not preserved by the import process. The consolidated export/import utility patch that were applied to the administration server node in Section 4 of Doc ID 2552181.1 contains a perl script, dpost_imp.pl, that will generate an AutoPatch driver file. Use this driver file to call the scripts that create these objects.

$ cd $AU_TOP/patch/115/bin

$ mkdir 6924477

$ perl dpost_imp.pl u6924477.drv 12      <== it creates file u6924477.drv

$ mv u6924477.drv 6924477                   <== repeat above steps on other node(s)

$ adop phase=apply hotpatch=yes patchtop=$AU_TOP/patch/115/bin patches=6924477 options=forceapply

8. DBAs run some reports, compile invalid objects and make necessary grants.

9. Run AutoConfig in 2nd time on all apps nodes and start all EBS services.

All shall work by now.

OPTIONAL actions: 

1. If WF agent does not start, the issue is with table AQ$_WF_CONTROL_P. Build WF queue on fly.

$ sqlplus applsys/PWD

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> set pages 10000

SQL> select instance_name from v$instance;

SQL> select count(*) from all_objects;

  COUNT(*)

----------

     62483

SQL> exec dbms_aqadm.drop_queue_table(queue_table=>'WF_CONTROL',force => TRUE);

PL/SQL procedure successfully completed.

SQL> select count(*) from all_objects;

  COUNT(*)

----------

     62446

SQL> select * from all_objects where object_name like'AQ$WF_CONTROL%';

no rows selected

SQL> select * from all_objects where object_name like'AQ$_WF_CONTROL%';

no rows selected

$ sqlplus apps/appsPWD

SQL> @/path/to/EBSPGRND/utl_dir/wfctqrec_new.sql APPLSYS appsPWD

Connected.                                               (see below Notes)

PL/SQL procedure successfully completed.

Commit complete.

$ sqlplus applsys/PWD

SQL> select count(*) from all_objects;

  COUNT(*)

----------

     62474

SQL> column object_name format a25

SQL> column object_type format a10

SQL> select object_name, object_type from all_objects 

where object_name like 'AQ$_WF_CONTROL%';

OBJECT_NAME                      OBJECT_TYP

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

AQ$_WF_CONTROL_E          QUEUE

AQ$_WF_CONTROL_F           VIEW

AQ$_WF_CONTROL_G          TABLE

AQ$_WF_CONTROL_H          TABLE

AQ$_WF_CONTROL_I           TABLE

AQ$_WF_CONTROL_L          TABLE

AQ$_WF_CONTROL_N          SEQUENCE

AQ$_WF_CONTROL_S          TABLE

AQ$_WF_CONTROL_T          TABLE

AQ$_WF_CONTROL_V          EVALUATION

                                                       CONTEXT

10 rows selected.

SQL> select object_name, object_type from all_objects 

where object_name like 'AQ$WF_CONTROL%';

OBJECT_NAME               OBJECT_TYP

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

AQ$WF_CONTROL               VIEW

AQ$WF_CONTROL_R          VIEW

AQ$WF_CONTROL_S           VIEW

Some database objects are created on "fly" after EBS services are started:

$ cd $ADMIN_SCRIPTS_HOME

$ ./adstrtal.sh apps/appsPWD

SQL> select object_name, object_type from all_objects 

where object_name like 'AQ$_WF_CONTROL%';

OBJECT_NAME               OBJECT_TYP

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

AQ$_WF_CONTROL_D          TABLE

AQ$_WF_CONTROL_E          QUEUE

AQ$_WF_CONTROL_F           VIEW

AQ$_WF_CONTROL_G          TABLE

AQ$_WF_CONTROL_H          TABLE

AQ$_WF_CONTROL_I           TABLE

AQ$_WF_CONTROL_L          TABLE

AQ$_WF_CONTROL_N          SEQUENCE

AQ$_WF_CONTROL_P          TABLE

AQ$_WF_CONTROL_S          TABLE

AQ$_WF_CONTROL_T          TABLE

AQ$_WF_CONTROL_V          EVALUATION

                                                       CONTEXT

12 rows selected.

SQL> select object_name, object_type from all_objects 

where object_name like 'AQ$WF_CONTROL%';

OBJECT_NAME                     OBJECT_TYP

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

AQ$WF_CONTROL               VIEW

AQ$WF_CONTROL_R          VIEW

AQ$WF_CONTROL_S           VIEW

SQL> select count(*) from all_objects;

  COUNT(*)

----------

     62487

Three Concurrent Managers shall be started and in running:

   . Workflow Mailer Service

   . Workflow Agent Listener Service

   . Workflow Document Web Services Service

But "Workflow Notification Mailer" may still show Down status due to a wrong SMTP server.

Notes: If running wfctqrec.sql gets error, delete some rows and save it as wfctqrec_new.sql for execution.

$ ls -altr wfctqrec*.sql

-rwxr-xr-x 1 user group 3734 Apr 12  2021 wfctqrec.sql

-rwxr-xr-x 1 user group 2865 Jul 14 17:16 wfctqrec_new.sql

$ diff wfctqrec.sql wfctqrec_new.sql

<   BEGIN

<     dbms_aqadm.stop_queue( queue_name => 'WF_CONTROL', wait => FALSE);

<   EXCEPTION

<     WHEN no_queue THEN

<       null;

<     WHEN others THEN

<       RAISE_APPLICATION_ERROR(-20000, 'Oracle Error = '

<                                     ||to_char(sqlcode)||' - '||sqlerrm);

<   END;

<

<   BEGIN

<     dbms_aqadm.drop_queue(queue_name=> 'WF_CONTROL');

<   EXCEPTION

<     WHEN no_queue THEN

<       null;

<     WHEN others THEN

<       RAISE_APPLICATION_ERROR(-20000, 'Oracle Error = '

<                                     ||to_char(sqlcode)||' - '||sqlerrm);

<   END;

<

<   BEGIN

<     dbms_aqadm.drop_queue_table( queue_table => 'WF_CONTROL', force => TRUE);

<   EXCEPTION

<     WHEN queue_table_not_exists THEN

<       null;

<     WHEN others THEN

<       RAISE_APPLICATION_ERROR(-20000, 'Oracle Error = '

<                                     ||to_char(sqlcode)||' - '||sqlerrm);

<   END;

<

2. After 19c upgrade, three AZ tables for EBS iSetup becomes missing in 19c database.  Follow 

Oracle Doc ID 832459.1 (How To Cleanup Invalid Oracle iSetup (AZ) Tables And Recreate) to re-create them

3. In R12.2, when below line was used to hold concurrent jobs for a period of time for database upgrade completion and then from GUI forms to cancel the HOLD, some concurrent jobs got "No Manager" status.

SQL> update  apps.fnd_concurrent_requests  set hold_flag = 'Y' 

where status_code in ('Q','I') and hold_flag = 'N';

The problem may be column EDITION_NAME of table fnd_concurrent_requests was populated with invalid data. The fix is to find the current edition and run an update to the column.  

SQL> select edition_name from all_editions;

EDITION_NAME

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

ORA$BASE

SQL> update fnd_concurrent_requests

set edition_name='ORA$BASE'

where request_id in (228633x, 228633x, 228632x, 228632x, 228624x, 228624x)

;

How to capture a HEAP dump for oacore process in EBS Weblogic

When users could not log onto EBS site (with error code 502 for URL /OA_JAVA/oracle/apps/fnd/jar/fndewt.jar), we saw oacore processes used very high CPU and WLS Admin site show oacore1 status is unknown (blank). We had to shut oacore1 down and then start it up in WLS Admin Console.

After checking all EBS logs, Oracle Support did not find anything that might cause the problem and so asked us to capture a HEAP dump when the issue occurs. 

Use the following steps to customize the managed server configuration (of each OACORE) via the WebLogic Server Administration Console.

1. Log on to the WebLogic Server Administration Console.

2. Click on the 'Servers' link. This link takes you to a page containing a summary of the WebLogic Administration Server and all managed servers.

3. Click on the managed server whose configuration needs to be updated. A page containing various tabs for the settings of the managed server appears (e.g. oacore_server1).

4. Navigate to Configuration Tab > Server Start Tab > You can see the Arguments Section.

5. Click on Lock and Edit button in the 'Change Center' panel.

6. Update the Heap setting to append

-XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/u02/app/temp/HEAP_trace

(add above line to the end of the long string with a single space in between. No service bounce is needed. /u02/app/temp/HEAP_trace can be changed to a different location.)

7. Click the 'Save' button to save the configuration changes.

8. Once the customizations are complete and saved, click the 'Activate Changes' button in the 'Change Center' panel to activate the changes.

9. Next time Out of Memory error occurs (or service bounce), a *.hprof dump file will be generated in /u02/app/temp/HEAP_trace.  Compressed it by gzip and upload it to Oracle SR.

$ grep hprof $EBS_DOMAIN_HOME/servers/oacore_server1/logs/oacore_server1.out00033

Dumping heap to /u02/app/temp/HEAP_trace/java_pid21982.hprof

More details in the log file:

java.lang.OutOfMemoryError: Java heap space

Dumping heap to /u02/app/temp/HEAP_trace/java_pid21982.hprof ...

Heap dump file created [5475772387 bytes in 33.025 secs]

java.lang.OutOfMemoryError: Java heap space

        at org.apache.xmlbeans.impl.store.Locale.weakCur(Locale.java:2711)

        ... ...

R12.2 Autoconfig Fails On Patch file system with error ORA-12514

When I cloned a R12.2 instance, "perl adcfgclone.pl appsTier dualfs" gave an error at the final stage:

... ...

AutoConfig has completed with errors for .patch

Do you want to startup the Application Services for EBSDEV? (y/n) [n] : n

Before fixing the error, I did not have problem in setting up the instance and starting all apps services in RUN file system. The website was available to the user while I did troubleshooting in the PATCH file system. 

The log file for cloning RUN file system does not have any error. But log file $INST_TOP/admin/log/clone/patch/RCloneApplyAppstier_10281250.log from cloning PATCH file system has a lot of errors beginning with: 

ERROR DESCRIPTION:

(*******FATAL ERROR*******

PROGRAM : ($FND_TOP/patch/115/bin/txkSetUnicodeEncodingSecFilter.pl)

TIME    : Fri Oct 28 13:01:35 2022

FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]

MESSAGES:

Invalid Connect string - cannot connect to database

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When I ran AutoConfig in PATCH environment, it failed and gave similar error in the log:

*******FATAL ERROR*******

PROGRAM : ($FND_TOP/patch/115/bin/txkUpdateEBSDomain.pl)

TIME    : Wed Nov  2 11:44:02 2022

FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]

MESSAGES:

Invalid Connect string - cannot connect to database

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

I tried "sh adadminsrvctl.sh start forcepatchfs" in PATCH environment and it worked, which is required by ADOP later, even AutoConfig failed on PATCH.

I compared tnsnames.ora and listener.ora in both RUN and PATCH file systems and did not see any difference. After I read Doc ID 2378192.1 (12.2 Autoconfig Fails On PatchFS with error "ORA-12514: TNS:listener does not currently know of service requested"),  I made a change to database parameter SERVICE_NAMES:

SQL> show user

USER is "SYSTEM"

SQL> show parameter service_name

NAME                           TYPE        VALUE

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

service_names              string      EBSDEV

SQL> alter system set service_names='EBSDEV,EBSDEV_ebs_patch';

System altered.

SQL> show parameter service_name

NAME                          TYPE        VALUE

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

service_names              string      EBSDEV,EBSDEV_ebs_patch

After that, I ran AutoConfig in PATCH file system again, and got the same error. So, that was not the fix.

Then,  I found database parameter LOCAL_LISTENER might be incorrect. After I make below change (and maybe plus the change to service_names),  AutoConfig worked successfully in PATCH file system !

SQL> show parameter local_listener

NAME                          TYPE        VALUE

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

local_listener                string

SQL> alter system set local_listener='EBSDEV_LOCAL';

System altered.

SQL> show parameter local_listener

NAME                          TYPE        VALUE

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

local_listener                string      EBSDEV_LOCAL

After AutoConfig worked in PATCH, I ran "adop phase=fs_clone" (successfully) in RUN file system to over-write the PATCH files. Then I added 2nd node to the instance without issue.

UTL_FILE_DIR in 19c database

Starting in Oracle Database 18c, initialization parameter UTL_FILE_DIR is deprecated and no longer supported. It can not be altered at DB level "alter system set utl_file_dir='/path/to/utl_dir;". Instead, specify the name of a directory object. 

UTL_FILE_DIR is no longer listed in V$SYSTEM_PARAMETER and related views:

SQL> SELECT value FROM v$system_parameter WHERE name='utl_file_dir' ;

no rows selected

SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE

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

utl_file_dir                         unknown

But, view v$parameter has it:

SQL> column name format a15

SQL> column value format a55

SQL> SELECT value FROM v$parameter WHERE name='utl_file_dir' ;

VALUE

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

/path/to/utl_dir,/home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV

SQL> SELECT value FROM apps.v$parameter WHERE name='utl_file_dir' ;

VALUE

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

/path/to/utl_dir,/home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV

SQL> select name, value from apps.v$parameter2 where name = 'utl_file_dir';

NAME            VALUE

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

utl_file_dir    /path/to/utl_dir

utl_file_dir    /home/u01/app/oracle/product/19.0.0.0/temp/EBSDEV

Doc ID 2525754.1 (Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2) states:

With Oracle Database 19c, you can specify the directories that you want Oracle E-Business Suite to use for PL/SQL file I/O in the supplemental UTL_FILE_DIR parameter within the apps.v$parameter and apps.v$parameter2 views. Each directory specified in that parameter must also have a corresponding directory object defined within the database. When you upgrade your Oracle E-Business Suite database from Oracle Database 11g or Oracle Database 12c to Oracle Database 19c, you use a script called txkCfgUtlfileDir.pl to migrate the directory paths formerly specified in the UTL_FILE_DIR database initialization parameter to the new supplemental UTL_FILE_DIR parameter and to create the corresponding directory objects. After the upgrade, you also use the same script to add, modify, or delete directory paths in the supplemental UTL_FILE_DIR parameter.

SQL> show parameter event

NAME                 TYPE        VALUE

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

event                     string      10946 trace name context forever, level 8454144

xml_db_events     string      enable

SQL> show user

apps

SQL> set serveroutput on

SQL> exec FND_FILE.PUT_LINE(fnd_file.output, 'Hello output!');

SQL> exec FND_FILE.close;

This shall create a .tmp file in $APPLPTMP.

FND_FILE uses the UTL_FILE package, which can only write to directories specified in init.ora (before 19c database)). APPLPTMP is a special directory used only for PLSQL temporary files. When a concurrent program uses the FND_FILE package, the concurrent manager uses APPLPTMP as the directory to which it writes temporary files. Thus, APPLPTMP must be one of the directories listed in init.ora in order for FND_FILE to work

We had a concurrent program calling a PL/SQL procedure owned by a custom schema xxdoc failing with errors:

ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.

Directory &FILE_DIR is invalid, contact your system administrator.

Action: Check that the directory is a valid directory with wri

The procedure in custom schema called by a concurrent program is similar to:

CREATE OR REPLACE PROCEDURE xxdoc.proc_test1(errbuf out varchar2, retcode out number) as

BEGIN

    FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Test CP from custom procedure');

END;

CREATE OR REPLACE SYNONYM proc_test1 for xxdoc.proc_test1;   (by APPS)

The fix: Grant permission on directory EBS_DB_DIR_UTIL to XXDOC

Check a directory by " select * from dba_directories where directory_name = 'EBS_DB_DIR_UTIL'; " 

SQL> sho pdbs    (for SYSDBA. Or, select * from v$pdbs; )

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

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

         2   PDB$SEED                      READ ONLY     NO

         3   EBSDEV                          READ WRITE   NO

SQL> alter session set container=EBSDEV;

Session altered.

SQL> sho pdbs

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

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

         3   EBSDEV                           READ WRITE   NO

SQL> set lines 200 pages 1234

SQL> col directory_name for a35

SQL> col directory_path for a60

SQL> col owner for a20

SQL> select * from dba_directories where directory_name like '%UTIL%';

OWNER  DIRECTORY_NAME  DIRECTORY_PATH  ORIGIN_CON_ID

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

SYS       EBS_DB_DIR_UTIL      /path/to/utl_dir             3

SQL> GRANT READ,WRITE on directory EBS_DB_DIR_UTIL to XXDOC;

Grant succeeded.

SQL> col grantee for a12

SQL> col owner for a15

SQL> col table_name for a35

SQL> col grantor for a20

SQL> col privilege for a20

SQL> select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE 

from dba_tab_privs 

where table_name like '%UTIL%' and TYPE in ('DIRECTORY') 

order by owner,table_name,GRANTEE,privilege;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE 

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

APPS       SYS  EBS_DB_DIR_UTIL SYSTEM  READ 

APPS       SYS  EBS_DB_DIR_UTIL SYSTEM  WRITE 

SYSTEM SYS  EBS_DB_DIR_UTIL SYS          EXECUTE 

SYSTEM SYS  EBS_DB_DIR_UTIL SYS          READ 

SYSTEM SYS  EBS_DB_DIR_UTIL SYS          WRITE 

XXDOC  SYS  EBS_DB_DIR_UTIL SYS          READ 

XXDOC  SYS  EBS_DB_DIR_UTIL SYS          WRITE 

NOTES: 19c database has UTL directories. Seems all of them point to the folder on db host.

SQL> select GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE from dba_tab_privs 

where table_name like '%UTL%' and TYPE in ('DIRECTORY') 

order by owner,table_name,GRANTEE,privilege;

REFERENCES:

- Concurrent Requests Fail With Error ORA-20100 (Doc ID 2723960.1)

- Creating a Custom Application in Oracle EBS Release 12.1.3 and above (Doc ID 1577707.1)

- Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)

  [ Section 2.2.1: Granting Privileges to Editioned Objects

    Section 1.1.4: Tools and Scripts for Edition-Based Development ]

Re-create three AZ tables of iSetup

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