Oracle E-Business Suite Release 12.2 System Schema Migration

In This Document

• Section 1: Overview of the EBS System Schema
• Section 2: Requirements for Using the EBS System Schema
• Section 3: Migrating to the EBS System Schema
• Section 4: References

Section 1: Overview of the EBS System Schema

The Release 12.2 database architecture has been modernized by adoption of the Oracle E-Business Suite System Schema, EBS_SYSTEM. Prior to the introduction of the EBS_SYSTEM schema, Oracle E-Business Suite installed application objects in the Oracle Database SYS and SYSTEM schemas. Migration to the EBS System Schema obviates the need for any EBS-owned objects to reside in the SYS or SYSTEM schemas.

Key characteristics of the EBS System Schema include:

• Creation of the EBS_SYSTEM schema and associated grant management is performed as follows:
  
  1. Creation of the EBS_SYSTEM schema and is performed by SYS running the adgrants.sql script (supplying the APPS account as the parameter) before applying the AD-TXK Delta 13 (or later) RUPs.
  2. Grants required by the APPS account are run automatically by the AD-TXK Delta 13 (or later) RUP installation process.

• All EBS database objects that currently reside in the SYS or SYSTEM schemas are migrated to appropriate Oracle E-Business Suite schemas. Depending upon the EBS object type and function, the object is migrated to EBS_SYSTEM, APPS, or APPS_NE.
  
  
• All Oracle E-Business Suite administration actions (such as running adop, adadmin and other utilities) are now performed by EBS_SYSTEM.
  
  
• Access to the Oracle database SYS and SYSTEM and the Oracle database server operating system is no longer required for Oracle E-Business Suite system administrative functions.
  
  
• If any grants need to be fixed after the AD-TXK Delta 13 (or later) RUPs are applied, refer to My Oracle Support Knowledge Document 2758993.1, Managing Database Privileges in Oracle E-Business Suite Release 12.2 (Running adgrants.sql).
  

Key benefits of migrating to the EBS System Schema include support for the following features:

• Public Oracle Database APIs
• Least Privileges Model for database object access
• Separation of Duties for administrators
• Database service names for application tier database connections
• Oracle Database Unified Auditing
• Easier interoperability across Oracle Database releases

Diagram 1 - The modernized Oracle E-Business Suite database and its key features.

Each of these will be discussed in the subsections below. Be aware that numbering of the subsections is for ease of reference only, and is not intended to reflect any set order of importance.

1.1 Public Oracle Database APIs

As part of the Oracle E-Business Suite System Schema Migration, all Oracle E-Business Suite code is updated to map to public Oracle database dictionary objects and APIs. Utilizing public Oracle database APIs provides further capability to lock down EBS runtime accounts.

1.2 Least Privileges Model for Database Object Access

With the migration to the EBS_SYSTEM schema and usage of public Oracle Database APIs, runtime accounts may be constrained even further. As part of this feature, unnecessary privileges are revoked from Oracle E-Business Suite application accounts.

1.3 Separation of Duties for Administrators

Migration to the EBS_SYSTEM schema makes it possible to separate the role of the Oracle E-Business Suite system administrators from database administrators. All Oracle E-Business Suite administration actions (such as running adop, adadmin, and other utilities) will now prompt for the EBS_SYSTEM password instead of the SYSTEM password. Highly privileged operations that were previously run by the SYS or SYSTEM accounts are now run by EBS_SYSTEM.

Access to the Oracle database SYS and SYSTEM and the Oracle database server operating system is no longer required for Oracle E-Business Suite system administration functions. Database patching may be performed by the Oracle database administrator, and Oracle E-Business Suite patching may be performed by the Oracle E-Business Suite system administrator or applications database administrator (DBA).

The passwords for EBS_SYSTEM and SYSTEM must match until after the Completion Patch is successfully applied. Once the Completion Patch has been successfully applied, the password for EBS_SYSTEM should be changed to be different from the SYSTEM schema password.

1.4 Database Service Names for Application Tier Database Connections

As part of modernizing the Oracle E-Business Suite, connections from the Oracle E-Business Suite application tier to the Oracle E-Business Suite database are now performed using database service names.

1.5 Support for Oracle Database Unified Auditing

Once all requirements are met, Oracle E-Business Suite customers are now able to utilize Unified Auditing, the latest method for auditing an Oracle Database. With Unified Auditing, audit data is combined into a single audit trail.  A new schema, AUDSYS, is used for storing the Unified Audit Trail. Separation of duties is achieved with multiple database roles to audit configuration and view the audit data.

For additional information, refer to My Oracle Support Knowledge Document 2777404.1, Enabling Unified Auditing in Oracle E-Business Suite Release 12.2 with Oracle Database 19c or 12c.

1.6 Streamline Database Directory Objects

Following migration to EBS System Schema (EBS_SYSTEM), the APPS schema will no longer have the ability to create database directory objects. Database directory objects are now created by the EBS_SYSTEM user.

The following four standard new database directory objects are created with the privileges shown:

Object Name	Privileges
EBS_LOG	Read/Write
EBS_TEMP	Read/Write
EBS_INBOUND	Read
EBS_OUTBOUND	Write

By default, the database directory objects are mapped to a temporary directory in the $ORACLE_HOME on the database tier file system. If a large number of files are written to this directory, your $ORACLE_HOME database tier file system may reach capacity: it is therefore recommended that you instead map the database directory objects to a secure location in a separate mount point from your $ORACLE_HOMEdatabase tier file system. For more information, refer to the Oracle Database documentation for your specific database release.

1.7 Interoperabilty Across Oracle Database Releases

Oracle E-Business Suite uptake of database releases will be made easier by EBS only referencing public database views and APIs.

Section 2: Requirements for Using the EBS System Schema

2.1 Prerequisites

To use the EBS System Schema, you need to be on Oracle E-Business Suite Release 12.2.3 or later.

2.2 Mandatory Patches and Steps

You must have available the following release update packs (RUPs) and other patches, ready to apply below in Section 3, in the order shown here.

1. AD and TXK Delta 13 (or later) — Support for EBS System Schema Migration was introduced in the Oracle Applications DBA (AD) and Oracle E-Business Suite Technology Stack (TXK) Delta 13 release update packs. Earlier releases of AD and TXK included database objects owned by AD or TXK that resided in the Oracle Database SYS or SYSTEM schemas, and also included references to private Oracle database SYS or SYSTEM objects.
   
   With AD and TXK Delta 13 (or later), EBS database objects are migrated from SYS and SYSTEM into the EBS_SYSTEM, APPS, or APPS_NE schemas.  All relevant object references are updated to point to the migrated objects in the APPS or APPS_NE schemas.
   
   AD and TXK Delta 13 (or later) also update all code that previously referenced private Oracle Database SYS and SYSTEM objects, to reference Oracle Database approved public objects or public APIs instead.
   
   
2. EBS Release 12.2.11 (or later), or EBS Releases 12.2.3 to 12.2.10 plus EBS System Schema Migration Consolidated Patch — Oracle E-Business Suite Releases 12.2.3 to 12.2.10 include EBS-owned database objects that reside in the database SYS or SYSTEM schemas, or reference database SYS or SYSTEM objects. The EBS System Schema Migration Consolidated Patch (also referred to as the Consolidated Patch) migrates these objects to the appropriate EBS schemas. Note that EBS Release 12.2.11 (and later) include the Consolidated Patch.
   
   
   Note: Oracle E-Business Suite Release 12.2.11 (and later) do not install EBS objects in the SYS or SYSTEM schemas, and therefore do not require the EBS System Schema Migration Consolidated Patch.
   
   
3. Before proceeding with application of the final patch in Step 4, you must update any customizations that have been identified with mandatory compliance requirements. For guidance, refer to My Oracle Support Knowledge Document 1577661.1, Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2.
   
   
4. EBS System Schema Migration Completion Patch — Although it is possible to apply AD-TXK Delta 13 and the EBS System Schema Migration Consolidated Patch without applying the EBS System Schema Migration Completion Patch immediately afterwards, the migration is not finished until the Completion Patch has been applied. This patch drops AD, TXK, and EBS product code in the database SYS or SYSTEM schemas, and removes any final restricted references to database SYS or SYSTEM objects.
   
   Prior to applying the Completion Patch, you must apply the following prerequisite patches:
   
   • AD-TXK Delta 13 (or later)
   • Either the EBS Release 12.2.11 (or later) RUP or the EBS System Schema Migration Consolidated Patch
     
     
   The Completion Patch will fail to apply if you do not apply these patches first.
   
   Diagram 2 - The four major requirements for EBS System Schema Migration.
   
   
   Note: The principles shown in the diagram also apply to AD and TXK RUPs later than Delta 13.
   
   
   
   
   
   Note: Until the Oracle E-Business Suite System Schema Migration Completion Patch (also referred to as the Completion Patch) has been applied, the EBS_SYSTEM and Oracle Database SYSTEM schema passwords must be identical. This requirement is for backward compatibility, until all EBS code has been migrated from the SYS or SYSTEM schemas. Diagram 3 depicts when the EBS_SYSTEM and SYSTEM passwords must be identical.
   
   Diagram 3 - The need for the EBS_SYSTEM and SYSTEM passwords to match.
   
   
   Note: The principles shown in the diagram also apply to AD and TXK RUPs later than Delta 13.
   
   

   

   Note: After the Completion Patch has been applied, the EBS_SYSTEM and Oracle Database SYSTEM passwords can be different, and we recommend that they are. Diagram 4 depicts when it is recommended to update the EBS_SYSTEM and SYSTEM passwords to unique values.

   Diagram 4 - The need to update the EBS_SYSTEM and SYSTEM passwords to have unique (different) values.

   Note: The principles shown in the diagram also apply to AD and TXK RUPs later than Delta 13.

   

Section 3: Migrating to the EBS System Schema

Migration consists of several stages, as summarized below.

By performing the applicable steps in this section, you will:

1. Apply the latest AD-TXK RUP.
2. Either:
   • Apply the EBS Release 12.2.11 (or later) RUP.
     or:
   • If remaining on any EBS release from 12.2.3 to 12.2.10 inclusive, apply the EBS System Schema Migration Consolidated Patch.
     
3. Update any customizations reported with mandatory compliance requirements for the EBS System Schema migration.
4. Apply the EBS System Schema Migration Completion Patch.

3.1 Upgrade Oracle E-Business Suite to Release 12.2.3 or Later

If applicable, upgrade your Oracle E-Business Suite environment to at least Release 12.2.3. We highly recommend that you upgrade to the latest available EBS Release RUP. For more information, refer to My Oracle Support Knowledge Document 1320300.1, Oracle E-Business Suite Release Notes, Release 12.2.

Refer to whichever of these books is relevant to you:

• Oracle E-Business Suite Upgrade Guide Release 11i to 12.2
• Oracle E-Business Suite Upgrade Guide Release 12.0 and 12.1 to 12.2

3.2 Apply Latest AD-TXK RUPs

Apply the latest release update packs for AD and TXK for Release 12.2, by following the instructions in My Oracle Support Knowledge Document 1617461.1, Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2. AD and TXK introduced support for EBS System Schema Migration starting with R12.AD.C.Delta.13 and R12.TXK.C.Delta.13.

Note: Oracle strongly recommends that you always apply the latest version of the AD and TXK RUPs at the earliest opportunity.

3.3 Apply Either EBS Release 12.2.11 (or later), or Consolidated Patch for Releases 12.2.3 to 12.2.10

If you are are on an Oracle E-Business Suite release from 12.2.3 to 12.2.10 inclusive, we recommend upgrading to Release 12.2.11 by applying the EBS 12.2.11 (or later) RUP. If you are not currently planning to upgrade to Release 12.2.11 (or later), you must apply the EBS System Schema Migration Consolidated Patch, Patch 31817501. For instructions, refer to My Oracle Support Knowledge Document 2774309.1, Applying the Oracle E-Business Suite Consolidated Patch for EBS System Schema Migration.

3.4 Remove Customization Dependencies on Database SYS or SYSTEM Objects

3.4.1 Mandatory Steps

Run the Oracle E-Business Suite System Schema Migration Compliance Checker report, ADSYSCC.sql, and remove or update any of your customizations that would give errors on running the EBS System Schema Migration Completion Patch. For detailed instructions, refer to My Oracle Support Knowledge Document 1577661.1, Developing and Deploying Customizations in Oracle E-Business Suite Release 12.

3.4.2 Optional Steps

Run the Oracle E-Business Suite System Schema Migration Compliance Checker report, ADSYSCC.sql, and remove or update any of your customizations that are identified with mandatory compliance requirements and would result in invalid database objects for your customizations.

3.5 Apply EBS System Schema Migration Completion Patch

Before the Oracle E-Business Suite System Schema Migration Completion Patch ("Completion Patch") can be applied, all EBS code must have been migrated from the SYS or SYSTEM schemas to the APPS or APPS_NE schemas. To finish the migration process, the EBS System Schema Migration Completion Patch will drop all EBS code that resides in the SYS or SYSTEM accounts.

Consequently, before you apply the Completion Patch, any custom code that points to EBS-defined objects in Oracle Database system accounts must be updated to point to the appropriate migrated EBS-defined objects in EBS-defined schemas. Failure to do so will result in invalid database objects for your custom code.

Apply the Oracle E-Business Suite System Schema Migration Completion Patch (Patch 32573930) by following the steps below.

Note: If you have previously applied the Completion Patch as per the steps in this section, do not re-perform the steps.

1. Source the run edition applications environment:
   
   UNIX:
$ <INSTALL_BASE>/EBSapps.env run
Windows:
C:\> <INSTALL_BASE>\EBSapps.cmd run
2. Before proceeding with application of the Completion Patch, perform a full adop cleanup by running the following command:
$ adop phase=cleanup cleanup_mode=full
3. Run the EBS System Schema Migration Compliance Checker, ADSYSCC.sql. When prompted, enter the APPS schema password.
   
   $ sqlplus <APPS schema name>@<TWO_TASK> @$AD_TOP/sql/ADSYSCC.sql
   This utility reports all violations of the EBS System Schema Migration Standards. Any EBS violations are fixed by both the EBS Release 12.2.11 (or later) RUP and the EBS System Schema Migration Consolidated Patch. If the report for your environment includes EBS violations after you have applied all mandatory patches (including the Completion Patch), you should log a service request with Oracle Support.

   You must fix any mandatory compliance violations listed in this report that are part of your custom code. For detailed instructions, refer to My Oracle Support Knowledge Document 1577661.1, Developing and Deploying Customizations in Oracle E-Business Suite Release 12.

Note: If you proceed with applying the Completion Patch when there are reported violations with custom code, you will have invalid database objects until your custom code is fixed.

If after applying the completion patch there are reported violations with EBS code, log a service request with Oracle Support to obtain assistance.


4. (Conditional) If you have integrated EBS with Oracle Database Vault, disable all existing Oracle Database Vault realms by navigating to the $FND_TOP/patch/115/sql directory and running the fnddbvstatus.sql script as follows:
   SQL> @fnddbvstatus.sql DISABLE
   For more information, refer to Appendix A, Management Scripts, in the following My Oracle Support knowledge documents:
   • Document 2131435.1, Integrating Oracle E-Business Suite Release 12.2 with Oracle Database Vault 12c
   • Document 2617770.1, Integrating Oracle E-Business Suite Release 12.2 with Oracle Database Vault 19c
     
     
5. Apply the Completion Patch by running the following adop command, as shown in this UNIX example:
$ adop phase=apply patches=32573930 apply_mode=hotpatch options=forceapply,nocopyportion,nogenerateportion
Note: You may encounter errors if you attempt to apply the Completion Patch and have specified different passwords for the EBS_SYSTEM and SYSTEM schemas.To apply the completion patch successfully, the SYSTEM and EBS_SYSTEM passwords must be the same.



6. Run the adsysmigclnup.sql cleanup script on the database tier by performing substeps a to d below. This will drop AD objects from the SYS schema and revoke the associated privileges.
   
   
   1. Create <ORACLE_HOME>/appsutil/admin on the database server.
      
      
   2. Copy <AD_TOP>/patch/115/sql/adsysmigclnup.sql to the database tier <ORACLE_HOME>/appsutil/admin directory using the appropriate command for your system:
      
      UNIX:
   $ cp $AD_TOP/patch/115/sql/adsysmigclnup.sql $ORACLE_HOME/appsutil/admin
   Windows:
   C:\> copy %AD_TOP%\patch\115\sql\adsysmigclnup.sql %ORACLE_HOME%\appsutil\admin
   3. Set the environment to point to the $ORACLE_HOME on the database server.
      
      
   Note: If the database is multitenant, source using the following steps as applicable for your environment.
   
   
   UNIX:
   $ source $ORACLE_HOME/<CDB_NAME>_<NODE_NAME>.env
$ export ORACLE_PDB_SID=<EBS_PDB_Name>
   Windows:
   C:\> %ORACLE_HOME%\<EBS_PDB_Name>_<NODE_NAME>.cmd
   4. Use SQL*Plus to run the cleanup script as follows.
      
      UNIX:
      $ sqlplus /nolog
SQL> @$ORACLE_HOME/appsutil/admin/adsysmigclnup.sql <APPS schema name>
      Windows:
      C:\> sqlplus /nolog
SQL> @%ORACLE_HOME%\appsutil\admin\adsysmigclnup.sql <APPS schema name>
      Note: If you find new invalid objects after performing the above steps, compile the invalid objects by running $ORACLE_HOME/rdbms/admin/utlrp.sql as the SYS user.
      
      
      Note: If you have Oracle Database Vault enabled, you may see an error message like the following in the report produced after the cleanup script runs:
      
      revoke READ on SYS.DUAL from "DVSYS" ERROR ORA-01031: insufficient privileges
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Such errors can be safely ignored.
      
      
   5. (Conditional) If you are using a multitenant database on UNIX and set ORACLE_PDB_SID in step c, you must unset it now:
      $ unset ORACLE_PDB_SID
7. After you have applied the Completion Patch, you should change the EBS_SYSTEM password and SYSTEM passwords by running substeps a to c below. We recommend that you set the two passwords to unique values (i.e. different from each other).
   
   
   1. On the application tier, source the environment using the appropriate command for your system:
      
      UNIX:
      $ . <INSTALL_BASE>/EBSapps.env run
      Windows:
      C:\> <INSTALL_BASE>\EBSapps.cmd run

      Still on the application tier, change the EBS_SYSTEM schema password to be different from that of the SYSTEM schema password, and provide the new password only to DBAs who specifically require access to the SYSTEM account:
      $ sqlplus EBS_SYSTEM/<EBS_SYSTEM PASSWORD>
SQL> alter user EBS_SYSTEM identified by <NEW PASSWORD>;
   2. On the database tier, set the environment using the appropriate command for your system:
      
      UNIX:
      $ . $ORACLE_HOME/<CDB SID>_<HOST>.env
      Windows:
      C:\> %ORACLE_HOME%\<CDB_SID>_<HOST>.cmd
   3. Still on the database tier, change the SYSTEM schema password, and provide the new password only to DBAs who specifically require access to the SYSTEM account.
   $ sqlplus SYSTEM/<SYSTEM PASSWORD>
SQL> alter user SYSTEM identified by <NEW PASSWORD>;
   Note: If your EBS instance is integrated with Oracle Database Vault, connect as the <DV_ACCTMGR> user instead of the SYSTEM user.
   
   
   
8. Validate adop by performing substeps (a) and (b) below.
   
   
   1. Source the run edition applications environment.
      
      UNIX:
      $ . <INSTALL_BASE>/EBSapps.env run
      Windows:
      C:\> <INSTALL_BASE>\EBSapps.cmd run

   2. Run the command:
      $ adop -validate

9. (Conditional) Integrate Oracle E-Business Suite with Oracle Database Vault using EBS_SYSTEM.
   
   If you have previously integrated EBS with Oracle Database Vault, follow subsection (a) below. If you have not previously performed such an integration but would like to do so now, follow subsection (b).
   
   
   1. Existing Integration with Oracle Database Vault
      
      
      1. Enable Oracle Database Vault by navigating to the $FND_TOP/patch/115/sql directory and running the following command:
         SQL> @fnddbvstatus.sql ENABLE
      2. Download and apply Patch 33267691:R12.AD.C.
         
         
      3. Follow Section 3: Integrate Oracle Database Vault with Oracle E-Business Suite Release 12.2 in My Oracle Support knowledge Document 2727580.1, Integrating Oracle E-Business Suite Release 12.2 with Oracle Database Vault using EBS_SYSTEM.
         
         
   2. New Integration with Oracle Database Vault
      
      Follow the instructions in My Oracle Support Knowledge Document 2727580.1, Integrating Oracle E-Business Suite Release 12.2 with Oracle Database Vault using EBS_SYSTEM. 
   3. 
      

 Oracle E-Business Suite Release 12.2 System Schema Migration (Doc ID 2755875.1)

19c Database with Oracle E-Business Suite R12 Known Issues & Solutions

 The purpose of this document is to present known issues and solutions for the 19c Database Upgrade with Oracle E-Business Suite (EBS).

Important Information Regarding the Upgrade to Oracle Database 19c

1. When upgrading your Oracle E-Business Suite to Oracle Database 19c, your database will be converted to a multitenant architecture, which will consist of a Container Database (CDB) with a single Pluggable Database. Only multitenant architecture database are supported for Oracle E-Business Suite with Oracle Database 19c.
2. During the upgrade, you will also perform steps to migrate directories defined for PL/SQL File I/O to database directory objects. This requirement is due to the desupport in Oracle Database 19c of the UTL_FILE_DIR database initialization parameter.
3. Oracle 19c Database Release Update Information for Oracle E-Business Suite:
   
   • Oracle Database Release Update 19.3 as well as Release Update 19.5 and later are certified.
   • Oracle recommends that you upgrade to the latest Oracle Database Release Update that is certified with Oracle E-Business Suite. Applying the latest Release Update will ensure that you have the security-related fixes and high-priority non-security fixes. See My Oracle Support Knowledge Document 2285040.1, Release Update Introduction and FAQ, for more details.
   • You can upgrade directly from an earlier Oracle Database version to Oracle Database Release Update 19.3 or Release Update 19.6 or later.
   • You cannot upgrade directly to Oracle Database Release Update 19.5. You must first upgrade from your earlier Oracle Database version to Oracle Database 19.3, and then apply Oracle Database Release Update 19.5.

 E-Business Suite 19c Database Upgrade Analyzer (Doc ID 2704990.1) 

The E-Business Suite 19c Database Upgrade Analyzer is a self-service health-check script that reviews E-Business R12 19c Database Upgrade related data, analyzes current configurations and settings for the environment and provides feedback and recommendations on the progress of your 19c database upgrade and any potential missing or failed upgrade steps. Analyzers may also be used as data collection tools by Support Engineers. Your application data is not altered in any way when you run this analyzer.

----------------------------------------
NOTE: In the images below and/or the attached document, user details / company name / address / email / telephone number represent a fictitious sample (based upon made up data used in the Oracle Demo Vision instance). Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
----------------------------------------

NOTE: Make sure to review Doc ID 2567105.1 which contains commonly asked questions and answers about using Oracle E-Business Suite with the multitenant architecture.

 

QUESTIONS AND ANSWERS

 

Questions

Questions	Answer
Which listener.ora should be used to start database listener ?	$ORACLE_HOME/network/admin/listener.ora should be used. Use $ORACLE_HOME/appsutil/scripts/<pdbname>_<hostname>/adcdblnctl.sh script : Example  adcdblnctl.sh start <CDBNAME>
How to know which listener.ora is used ?	Run lsnrctl status CDBNAME command look for value of "Listener Parameter File" in the output. Example : Listener Parameter File   /u01/oracle/db/tech/19.3/network/admin/listener.ora

 

Problems

 

Database Issues

Issue	Solution
ORA-65064: system ts doesn't exist An ORA-65064 might have been raised when we attempted to plug in a legacy database into a CDB and the legacy database was created under a pre-V8 database release and then moved to later releases using the migrate utility	Cause: When you plug that database into a Multitenant environment you will see an ORA-65064 and the tracefile will have a message that reads:     kpdbcPlugVerifyFiles: ORA-65064: system ts doesn't exist Solution: The Multitenant architecture has a fundamental assumption that any plugged in database will have a file from the SYSTEM tablespace with a relative file number of 1. You may be seeing this issue if the database was created under v7 or earlier, was upgraded to later releases using the migrate utility and an ORA-65064 is raised during the plug-in operation and where the tracefile indicates that we could not find the system tablespace. Checking the XML file generated using dbms_pdb.describe() should also show that there is no SYSTEM tablespace with a relative file number (<rfn>..</rfn>) of 1. WORKAROUND: Use exp/imp or datapump to rebuild the database first and then perform the plug in operation.
ORA-7445 [qkacco] when running a Complex Query with Nested Joins ORA-07445: exception encountered: core dump [qkacco()+91] [SIGSEGV] [ADDR:0x0] [PC:0x289204B] [Address not mapped to object] []	Solution: ORA-7445 [qkacco] when Running a Complex Query with Nested Joins (Doc ID 2664281.1) Do not set optimizer_adaptive_plans=false at database level as a workaround. Instead use SQL-Profile, hints , "alter session" commands, etc  to change setting at session level for problematic SQL. Wait for database patch . Apply patch for database Bug 30621255
ORA-01034 while running txkPostPDBCreationTasks.pl script Starting Context Generation Tool at Tue May 19 13:24:17 CDT 2020 Using GenerateContext version 120.20.12010000.16 Connecting to the cgdev2 database instance... Connection parameters values: Database server hostname ==> databasehostname.xx.yy.com Database listener port ==> 1521 Database SID ==> PDBNAME Database schema name ==> apps Could not Connect to the Database : ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory Additional information: 4376 Additional information: 226357863	Database listener.ora parameter should have following setting USE_SID_AS_SERVICE_<CDBNAME>=ON Without this setting GenerateContext.class file fails to connect to database because it uses JDBC connect string hostname:port:SID notation. But this is changes in 19c and it is hostname:port/SID. Notice / instead of :  . Setting USE_SID_AS_SERVICE_<CDBNAME>=ON solves this issue
Invalid APPS database user credentials errors when running txkPostPDBCreationTasks.pl Post PDB script failing with following errors : *******FATAL ERROR******* PROGRAM : (.../appsutil/bin/txkPostPDBCreationTasks.pl) TIME : Sat May 16 16:47:09 2020 FUNCTION: main::validateAppsSchemaCredentials [ Level 1 ] ERRORMSG: Invalid APPS database user credentials. *******FATAL ERROR******* PROGRAM : (/.../appsutil/bin/txkPostPDBCreationTasks.pl) TIME : Sat May 16 16:47:09 2020 FUNCTION: main::validateAppsSchemaCredentials [ Level 1 ] ERRORMSG: Invalid APPS database user credentials.	Database parameter instance_name was set to PDB_NAME . It should set to CDB_NAME.
PDB services are not registering with database listener	Make sure the database parameter local_listener is not set at PDB level . This value should be inherited from CDB. After the change run "alter system register;" command so that services will be registered with the listener.
High "row cache mutex" waits are seen in AWR reports due to SQLs against v$session/v$process Example SQLs : 0u11juyazvjf5 SELECT PR.SPID , SE.SID , SE.PROGRAM , SE.MODULE FROM V$SESSION SE , V$PROCESS PR WHERE SE.SID = (SELECT SID FROM V$MYSTAT WHERE ROWNUM = 1) AND SE.PADDR = PR.ADDR 0y6qmpuvm4w9u select sid from v$session, v$process where audsid= sys_context('userenv', 'sessionid') and addr=paddr 1wr32ytknh1u9   SELECT P.PID, S.PROCESS, P.SERIAL#, P.SPID FROM V$PROCESS P, V$SESSION S WHERE S.AUDSID = USERENV('SESSIONID') AND S.PADDR = P.ADDR 0y7y17bcappxk   SELECT P.PID, P.SERIAL#, P.SPID FROM V$PROCESS P, V$SESSION S WHERE S.AUDSID = USERENV('SESSIONID') AND S.PADDR = P.ADDR	Solution is to apply database Patch 31903523 . Until then contention can be reduced as follows : - Disable EBS auditing : "Sign-On:Audit Level" . - IF MWA is used , then apply MWA patch 17288009 to reduce # of executions of SQL 0y6qmpuvm4w9u - Turn off SQL tracing
Running ADZDDBCC.sql in 19c database is taking time for Section 26.	Before running report set parameter _px_cdb_view_enabled to false . alter session set "_px_cdb_view_enabled"=false ;
Concurrent programs are failing with ora-20100: Temporary file creation for FND_FILE failed errors. This can be reproduced as follows in SQL-Plus : SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST'); BEGIN FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST'); END; * ERROR at line 1: 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 write permissions on the database server node and is listed under utl_file_dir para ORA-06512: at "APPS.FND_FILE", line 319 ORA-06512: at "APPS.FND_FILE", line 364 ORA-06512: at "APPS.FND_FILE", line 421 ORA-06512: at line 1	There are three reasons for that failure : 1> utl_file_dir parameter is not set . Run following SQL to check select value from apps.v$parameter where name = 'utl_file_dir';   2> Event 10946 should be set as explained in Note 2525754.1 alter system set events '10946 trace name context forever, level 8454144'; alter system set event='10946 trace name context forever, level 8454144' scope=spfile; 3> First value from above SQL should match to value of $APPLPTMP environment parameter
Running Materialized View refresh programs are running longer and locks are seen in the database	This is caused by RDBMS Bug 30402386 and explained in Note 2716307.1 Apply database patch. Until then set following hidden parameter as a temporary workaround : ALTER SYSTEM SET “_mv_refresh_shrink_log”=FALSE;
Database alert.log reports parsing errors for FND_SESSION_MANAGEMENT package 2021-03-09T05:07:08.548176-08:00 mypdb(3):WARNING: too many parse errors, count=621 SQL hash=0x8a3bb1a5 mypdb(3):PARSE ERROR: ospid=24007, error=6550 for statement: 2021-03-09T05:07:08.549382-08:00 mypdb(3):declare PRAGMA AUTONOMOUS_TRANSACTION;begin fnd_signon.audit_client_ip(:1,:2); commit;exception when others then rollback;end; mypdb(3):Additional information: hd=0x61fa9b68 phd=0x6daec960 flg=0x100476 cisid=65 sid=65 ciuid=65 uid=65 sqlid=bb9xkdf53rcd5 mypdb(3):...Current username=APPS mypdb(3):...Application: e::bes:oracle.apps.icx.security.session.created Action: / mypdb(3):WARNING: too many parse errors, count=336 SQL hash=0xe2823bd1 mypdb(3):PARSE ERROR: ospid=16624, error=6550 for statement: 2021-03-08T04:16:36.045025-08:00 mypdb(3):BEGIN :1 := FND_SESSION_MANAGEMENT.getHttpOnly(:2); END; mypdb(3):Additional information: hd=0xc12e4490 phd=0x84b82418 flg=0x100476 cisid=65 sid=65 ciuid=65 uid=65 sqlid=7kcmdtbj84fyj mypdb(3):...Current username=APPS mypdb(3):...Application: e::bes:oracle.apps.icx.security.session.created Action: /	Errors are safe to ignore since they will not be reported to end users. Apply E-Business Suite Patch 32429191:R12.FND.C and Patch 33429261:R12.FND.C .
txkCreatePDB.pl Completes with PDB Plugged in with "Open No Master Key" For an existing 19c container database, Plugin a EBS PDB in container database, Using txkCreatePDB.pl the wallet status for newly created PDB shows "OPEN_NO_MASTER_KEY" and the PDB is in Restricted Mode  =================== SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID -------------------------------------------------------------------------------------------------------------- -------------------- --------- --------- FILE <Location> OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE UNDEFINED 0 This Applies to EBS 12.1.3 & All 12.2 Versions which supports 19c Database including  OCI/On-Premises/Exadata and any other platform.	1. This can be caused due to passing a wrong value in secret key while plugging the PDB using txkCreatePDB.pl. 2. The Script should stop and fail without plugging the PDB when a wrong argument is been passed for secret key during the execution of txkCreatePDB.pl, hence there is an Enhancement request raised with the Product Development for the same "Enh 33566579" 3. To overcome this Situation, please apply the workaround given in the following Note  Manually Re-import the Secret Key in E-Business Suite Database On Premises & Cloud (Doc ID 2826133.1)
Bug 33405295 - DBMS_STATS.IMPORT_DATABASE_STATS FAILING WITH ERROR OPERATION NOT ALLOWED FROM WITHIN A PLUGGABLE DATABASE	Please follow the below action plan to solve the import stats error issue :    Gather schema stats on the source, as below: sqlplus apps/<apps> 1. exec fnd_stats.gather_schema_statistics(schemaname=>'ALL',estimate_percent=>dbms_stats.auto_sample_size,options=>'GATHER AUTO'); 2. Backed up Statistics into FND_STATTAB, following Doc ID 1581549.1 - Section 7.10.2.1 - Exporting Statistics - (using the FNDSTATS SQL provided within the note). 3. Then follow Doc ID: 2615617.1, as below: Section 5.9 - Export the Applications database instance Section 6.4 - Import the Applications database instance Section 7.1 - Reset Advanced Queues Section 7.2 - Run adgrants.sql Section 7.3 - Gather statistics and fixed objects statistics Section 7.4 - Run post PDB script 4. Upgraded FND_STATTAB Statistics Table: sqlplus system/<system_pw>@MOSCOW exec dbms_stats.upgrade_stat_table('APPLSYS','FND_STATTAB'); 5. Restored Statistics from FND_STATTAB following 1581549.1 - Section 7.10.2.2 Importing Statistics - (using the FNDSTATS SQL provided within the note).
Bug 33427856 - Gathering statistics for indexes on large tables in 19c may be time-consuming (Doc ID 33427856.8)	Apply Patch 33427856 or use workarounds until then Massive Performance Degradation On Statistics For Large Partitioned Tables After Upgrade To 19c (Doc ID 2611336.1)

 

OCI Database Issues

Issue	Solution
Failure in opening the PDB, after renaming it during refresh Core Dump during DBMS_SERVICE.START_SERVICE using case sensitive name for PDB DB System Platform: ExaCS DB Version: 19.10	Solution: Apply Patch 31143870
ORA-65092: system privilege granted with a different scope to 'OEM_MONITOR' Failure in Datapatch with error: ERROR at line 1: ORA-65092: system privilege granted with a different scope to 'OEM_MONITOR' ORA-06512: at line 6 ORA-06512: at line 2 DB System Platform: VM DB System DB Version: 19.8	Solution: Apply Patch 31796208 and rerun datapatch

 

Forms Issues

Issue	Solution
ORA-1017 when opening Forms and ORA-1017 Invalid username/password; logon denied FRM-41352 failed to create a new session	Cause: Misconfiguration with the listener and registration of services: Solution: 1. In 19c database service_name parameter should be set to container name      Connect to the CDB and run the following commands:         alter System set SERVICE_NAMES='<CDBNAME>' SCOPE=BOTH;         alter system register; 2. Verify that the PDB database is registered with the  listener       Connect to PDB and check value of local_listener parameter. It should be not be set at PDB level and should be same as CDB value. 3. PDB should have at a minimum of these services in 12.1 and 12.2.   select name from v$active_services; 12.1:   ebs_<PDB NAME>  <PDB NAME> 12.2:   ebs_<PDB NAME>   <PDB NAME>   <PDB_NAME>_ebs_patch 4. Listener should be started using CDB environment file       cd $ORACLE_HOME         .CDB_HOSTNAME.env          lsnrcrt stop CDBNAME        lsnrctl start CDBNAME 5. You should see something similar to the following: $ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-APR-2020 16:32:48 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias VISCDB Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 22-APR-2020 21:27:31 Uptime 6 days 19 hr. 5 min. 17 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/oracle/db/tech/19.3/network/admin/listener.ora Listener Log File /u01/oracle/db/tech/19.3/admin/PDB_HOSTNAME/diag/tnslsnr/HOSTNAME/cdb/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=HOSTNAME.<HOSTNAME>)(PORT=1521))) Services Summary... Service "2809223196ec2af8e053a740d20a4db6" has 1 instance(s). Instance "CDB", status READY, has 1 handler(s) for this service... Service "PDB" has 2 instance(s). -----> PDB service. Started when PDB is started Instance "PDB", status UNKNOWN, has 1 handler(s) for this service... Instance "CDB", status READY, has 1 handler(s) for this service... Service "PDB_ebs_patch" has 1 instance(s). ----> Created during post PDB steps , needed for ADOP patching Instance "CDB", status READY, has 1 handler(s) for this service... Service "CDB" has 1 instance(s). ---> CDB service , not used by EBS but needed for connections to CDB Instance "CDB", status READY, has 1 handler(s) for this service... Service "CDBXDB" has 1 instance(s). --> optional XDB service Instance "CDB", status READY, has 1 handler(s) for this service... Service "ebs_PDB" has 1 instance(s). ---> Created during post PDB steps , needed for EBS Instance "CDB", status READY, has 1 handler(s) for this service... The command completed successfully
APP-FND-00706: Routine Detected a data error	Cause: Cursor leaking in the database. The following file version has the bug code: $Header fdfdds.lc 120.23.12020000.20 2019/04/25 09:45:45 <USER> ship $ Solution: After Upgrading To 12.2.9 Forms Fail: "Error APP-FND-00706: Routine Detected A Data Error" (Doc ID 2629037.1)
ORA-04025: maximum allowed library object lock allocated for SELECT NLS_CHARSET_NAME  Following errors are seen in oacore log files during load testing oacore.default_group.2:292946577:-1:-1:GUEST(6):266001490:Thread[AJPRequestHandler-HTTPThreadGroup-6,5,HTTPThreadGroup]:1587728106:172.22.180.117:6160782:0:1341:ERROR:[fnd.common.ErrorStack]:java.sql.SQLException: ORA-20001: Oracle error -4025: ORA-04025: maximum allowed library object lock allocated for SELECT NLS_CHARSET_NAME(:B1 ) FROM SYS.DUAL ORA-06512: at "SYS.STANDARD", line 270 ORA-06512: at "APPS.FND_GLOBAL", line 1219  has been detected in fnd_global.set_nls. ORA-06512: at "APPS.APP_EXCEPTION", line 72 ORA-06512: at "APPS.FND_GLOBAL", line 280 ORA-06512: at "APPS.FND_GLOBAL", line 1584 ORA-06512: at "APPS.FND_GLOBAL", line 2017 ORA-06512: at "APPS.FND_GLOBAL", line 2652 ORA-06512: at "APPS.FND_GLOBAL", line 2804 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 999 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1304 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 433 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 329 ORA-06512: at line 1     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)     at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)     at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)     at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)     at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)     at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)	Apply database <Patch 31820859> : ORA-4025 DUE TO 65535 ACTIVE LOCKS LIMIT REACHED ON SELECT NLS_CHARSET_ID
FRM-40735: ON-ERROR Trigger raised unhandled exception ORA-01001	Solution: Using Oracle 19c RAC Multitenant (Single PDB) with Oracle E-Business Suite Release 12.1 (Doc ID 2530680.1)
ORA-65011: when opening forms after upgrading EBS Apps database 19c  Similar to to ORA-1017 errors in 12.2	Cause: Issue is related with service names            E-Business Suite 12.1 database should only have one service name defined Solution: service_names --> parameter should point to CDB only connect to PDB and run following SQL select name from v$active_services; this should return PDB name . If nothing is returned, then shutdown EBS services,, shutdown DB listener, then close PDB , bounce CDB, start DB listener, start EBS services and re-check. Service should be automatically started when PDB is opened

Login Issues

Issue	Solution
Intermittent login issues after 19c upgrade. The end users will see the following login issues - AppsLocalLogin.jsp will have a copyright date of 2014 - See login page not with style sheets - The system has encountered an error - Uncaught exception	Apply database Patch 31820859

 

Upgrade Issues

Issue	Solution
Running noncdb_to_pdb.sql fails with ORA-01219 : DATABASE OR PLUGGABLE DATABASE NOT OPEN: QUERIES ALLOWED ON FIXED TABLES OR VIEWS ONLY. When a non-CDB, having configured WALLET_ROOT and TDE_CONFIGURATION is cloned or described/plugged as a PDB, opening the PDB (the PDB created from non-CDB) fails with ORA-01219 : DATABASE OR PLUGGABLE DATABASE NOT OPEN: QUERIES ALLOWED ON FIXED TABLES OR VIEWS ONLY	Cause: Following Bug has been identified in EBS Database TDE configuration. This issue occurs while performing database upgrade to 19c. BUG 31494095 - NONCDB_TO_PDB.SQL FAILS WITH ORA-01219: DATABASE OR PLUGGABLE DATABASE NOT OPEN: QUERIES ALLOWED ON FIXED TABLES OR VIEWS ONLY Solution: Patch 31494095
Autoconfig Run Fails With Error 'tackTrace: java.lang.NullPointerException at oracle.apps.ad.context.GenerateContext.getPDBCDB' Due To Missing 'SELECT_CATALOG_ROLE' In Adgrants.sql When upgrading the database from 11.2.0.4 to 19c with EBS version R12 using Doc ID 2580629.1, Autoconfig failed with the following error: "Autoconfig could not create context file."	R12 E-Business Suite Upgrade To 19c Autoconfig Run Fails With Error 'tackTrace: java.lang.NullPointerException at oracle.apps.ad.context.GenerateContext.getPDBCDB' Due To Missing 'SELECT_CATALOG_ROLE' In Adgrants.sql (Doc ID 2672303.1)
Autoconfig is running slower after 19c upgrade.	Slow SQL was SELECT UO.object_name, aa.object_name procedure_name, aa.ARGUMENT_NAME FROM user_objects uo, all_arguments aa WHERE uo.object_name = 'FND_GSM_UTIL' AND aa.object_id = uo.object_id AND aa.object_name = 'UPLOAD_CONTEXT_FILE' AND object_type = 'PACKAGE' ORDER BY 1, 2, 3 SQL is modified to select from user_arguments instead of all_arguments. Fix is in Patch 31138017:R12.TXK.C Refer to 12.2 E-Business Technology Stack Autoconfig Performance Issue After Upgrade to 19c Database (Doc ID 2684666.1) for more details.
Running Autoconfig errors after 19c upgrade on application tier - Unable to read UTL_FILE_DIR parameter ERROR DESCRIPTION: *******FATAL ERROR******* PROGRAM : (/fs2/EBSapps/appl/fnd/12.0.0/patch/115/bin/txkCfgUtlfileDir.pl) TIME : FUNCTION: main::getUtlFileDirParam [ Level 1 ] ERRORMSG: Unable to read UTL_FILE_DIR parameter. ERRORCODE = 1 ERRORCODE_END .end std out. The following select also fails to return results: select value from v$parameter where name='utl_file_dir';	R12 E-Business Suite Technology Stack Post 19c Upgrade, Running Autoconfig Script txkCfgUtlfileDir.pl Fails With Fatal Error In FUNCTION: main::getUtlFileDirParam ERRORMSG: Unable to read UTL_FILE_DIR parameter (Doc ID 2685022.1)
Unable to call FND_LDAP_WRAPPER.CREATE USER  - ORA-06508: PL/SQL: could not find program unit being called If you upgraded to 19c prior to June, you may need to apply Patch 31088182. While creating a user in EBS 12.2.4, and after migrating to DB19c, you can run into this problem: Unable to call FND_LDAP_WRAPPER.CREATE USER due to the following reason:    ORA-06508: PL/SQL: could not find program unit being called.    (USER_NAME=<USER>)

References :

NOTE:2567105.1 - FAQ: Oracle E-Business Suite and the Oracle Multitenant Architecture

NOTE:2826133.1 - Manually Re-import the Secret Key in E-Business Suite Database On Premises & Cloud
NOTE:2655839.2 - Information Center: Oracle Database 19c with Oracle E-Business Suite 12.2 and 12.1
NOTE:2580629.1 - Interoperability Notes: Oracle E-Business Suite Release 12.1 with Oracle Database 19c
NOTE:2683670.1 - Previous Oracle Database 19c Release Update Database Patch Lists for Oracle E-Business Suite
NOTE:2716307.1 - Refresh Collection Snapshots Running Long After DB Upgrade
NOTE:1594274.1 - Oracle E-Business Suite Release 12.2: Consolidated List of Oracle Database Patches and Bug Fixes
NOTE: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

NOTE:2611336.1 - Massive Performance Degradation On Statistics For Large Partitioned Tables After Upgrade To 19c 

19c Database with Oracle E-Business Suite R12 Known Issues & Solutions (Doc ID 2662860.1)