EBS_SYSTEM Schema Migration

Prerequisite: EBS Version 12.2.3+, Database 12.1.0.2 or 19c

MOS Notes:

• Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Doc ID 1617461.1) (AD/TXK Delta 13)
• Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID 1594274.1)
• FAQ: Oracle E-Business Suite and System Schema Migration (Doc ID 2758999.1)
• Oracle E-Business Suite Release 12.2 System Schema Migration (Doc ID 2755875.1)
• Applying the Oracle E-Business Suite Consolidated Patch for EBS System Schema Migration (Doc ID 2774309.1)
• Managing Database Privileges in Oracle E-Business Suite Release 12.2 (Running adgrants.sql) (Doc ID 2758993.1)
• Enabling Unified Auditing in Oracle E-Business Suite Release 12.2 with Oracle Database 19c or 12c (Doc ID 2777404.1) — Beyond the scope of this post

By migrating to the EBS_SYSTEM schema, E-Business Suite gains some new features:

• Utilizes public Oracle Database APIs
• Least Privileges model for database object access
• Separation of duties for database administrators
• Database service names for application tier database connections
• Certification with Oracle Database Unified Auditing
• Simplified integration with Oracle Database Vault
• Easier interoperability across Oracle Database releases

AD/TXK Delta 13

The first step in the migration is applying AD and TXK Delta 13. This step has some extra steps because it creates and enables the EBS_SYSTEM schema.

1. Install the technology stack patches (Note 1594274.1)
   • Do not apply the April 2022 19c RU (not certified with EBS yet anyway)
     • Issues have been reported with some users having difficulty applying to pluggables.  Make sure this is resolved before moving forward.
   • Get the latest ETCC patch
     • from your staging area run the patch checker on the apps and database tiers
     • download etcc bundle patches as well as the current database and weblogic quarterly patches
   • Install the database patches
     • run checkDBpatch.sh and repeat the process until no new patches are identified
   • Start a patching cycle
     • adop phase=prepare
   • Install the application technology patches
     • run checkMTpatch.sh and repeat the process until no new patches are identified
2.  Download the patches from 1617461.1 from section 3.2 step 4 and section 4
3. Copy the latest adgrants.sql to the database server (as this time, it is in patch 33441060)
   • On the database tier
     • Run it as sys on the database server, this will create the EBS_SYSTEM user
     • ALTER USER EBS_SYSTEM ACCOUNT UNLOCK;
     • ALTER USER EBS_SYSTEM IDENTIFIED BY same_password_as_system;
       • This is critical until the schema migration is complete the passwords much match
   • On the apps tier
     • perl $PATCH_TOP/32394134/ad/bin/adValidateEbssystemSchema.pl
4. Complete the patches as described in section 3.2

This has started the schema migration.  The utilities will now be using EBS_SYSTEM instead of SYSTEM on the apps_tier, but we are still linked to the SYSTEM schema (and the passwords must still be the same).

Consolidation Patch

We now need to apply the EBS_SYSTEM Schema Migration Consolidation patch (Note 2774309.1)

• Included in 12.2.11
• Patch 31817501 for 12.2.3-12.2.10

While you may proceed to the consolidation patch directly from the AD and TXK patches, you must complete this patching cycle before continuing to the next step.

The completion patch requires that you have done a full cleanup, so go ahead and do that now

adop phase=cleanup cleanup_mode=full

You may have mandatory post steps based upon your installed products.  For example, Enterprise Command Center must be at least 12.2 V7.

New Directories

The patch has created some new directories which were defaulted to /usr/tmp.  You should change them to a location visible to the apps tier if the applications DBAs will not have access to the database tier (In a non-RAC environment, I prefer to use a file system on the database tier that is NFS mounted on the apps tier for this as well as the value in APPLPTMP).

EBS_OUTBOUND
EBS_INBOUND
EBS_TEMP
EBS_LOG
EBS_OUT

For example,
CREATE OR REPLACE DIRECTORY EBS_OUT AS ‘/oracle_ebs/out’;

Custom Code

Just as when we were getting ready to enable online patching, Oracle has provided a script to help identify custom code that references objects that have been moved to EBS_SYSTEM.

Run
sqlplus apps @$AD_TOP/sql/ADSYSCC

Results will automatically be spooled to adsyscc.lst in the current directory.

Fix any custom objects identified in the report.

Completion Patch

At this point (full cleanup has been done, custom objects have been fixed), we are ready to apply patch 32573930

adop phase=apply patches=32573930 apply_mode=hotpatch options=forceapply,nocopyportion,nogenerationportion

Copy $AD_TOP/patch/115/sql/adsysmigclnup.sql to the database tier.

On the database tier:

sqlplus /nolog
  @adsysmigclnup APPS

Change the system and ebs_system passwords to have different values and make sure only the appropriate people know each password.

On the apps tier

adop -validate

At this point, you have completed the schema migration.  Note this is a summary from the MOS notes.  Be sure to read the actual notes to be sure that none of the steps I have skipped apply to your installation.