Oracle EBS R12 with Database 19c - Administration

Oracle recently announced that Oracle Database 19c is certified with Oracle E-Business Suite Release 12.1 / 12.2.  You may now start planning your database upgrade from 11g or 12c to 19c. The minimum certified version of Oracle Database 19c for Oracle EBS is Oracle Database 19c (19.3). Oracle Database 19c offers customers the best performance, scalability, reliability, and security for all their operational and analytical workloads.

What's New For EBS With Oracle Database 19c:

There are several mandatory architecture updates when you upgrade your Oracle E-Business Suite database to Oracle Database 19c.  The following is a summary of the key required architectural changes:

Container Database (CDB) Architecture:

With the Database 19c certification EBS R12.1/R12.2 on-premises databases are now certified with the CDB architecture (multitenant architecture).

It is important for you to pay particular attention to the following points:

• A CDB with one PDB (single tenant) is currently the only certified deployment for Oracle E-Business Suite with Database 19c.
• A CDB with multiple PDBs (multitenant) is not currently certified for Oracle E-Business Suite.
• A non-CDB architecture is not planned to be certified or supported for EBS with Database 19c.

As part of the upgrade to Database 19c, you will convert your EBS database to the CDB architecture with a single pluggable database (PDB) i.e EBS database.

In EBS 19c database, it contains two environment files one for CDB and the other one for PDB and one Context file. The CONTEXT_FILE points to PDB database. when we source the PDB environment then only we can find the database CONTEXT_FILE.

All EBS specific tasks on the database side should be performed only on EBS instance, which is Pluggable database (PDB) in this case. Also, EBS specific database initialization parameters must be updated only in the pluggable database (EBS_PDB).

Database Tier Environment Sourcing:

1. To source EBS CDB -  <EBS CDB>_<hostname>.env 
2. To source EBS PDB -   <EBS PDB>_<hostname>.env 

1) How to source and connect the Oracle E-Business Suite CDB environment as SYSDBA?
Oracle E-Business Suite database CDB environment files are located in the database ORACLE_HOME. 

Run the following commands to connect the CDB dtabase

$ cd $ORACLE_HOME

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

2) How to start the CDB that hosts the Oracle E-Business suite PDB?

Run the following commands to start the CDB

$ source <CDB_NAME>.<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> startup; 

3) How to shutdown the CDB that hosts the Oracle E-Business suite PDB?

Run the following commands to shutdown CDB database

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> shutdown normal;

Two scripts introduced in EBS Database 19c to start and stop the CDB database and listener.

• adcdbctl.sh
• adcdblnctl.sh

4) How to source and connect the Oracle E-Business Suite PDB environment as SYSDBA?

Run the following commands to connect the EBS PDB database

$ source <CDB_NAME>_<NODE_NAME>.env

$ export ORACLE_PDB_SID=<PDB NAME>

$ sqlplus "/ as sysdba"

5) How to open the Oracle E-Business Suite PDB?

Source the CDB environment file, connect to the CDB as SYSDBA, then execute the SQL command to start the PDB:

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> alter pluggable database open read write services=all;

6) How to close the Oracle E-Business Suite PDB?

Source the CDB environment file, connect to the CDB as SYSDBA, then execute the SQL command to stop the PDB:

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> alter pluggable database <EBS PDB Name> close immediate; 

7) How to find Oracle E-Business Suite PDB information and status?

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> show pdbs;

Returns the values for the con_id, con_name, open mode, restricted values of all your PDBs.

SQL> select name, open_mode from v$pdbs; 

8) How to connect the Oracle E-Business Suite PDB as APPS user?

Run the following commands to connect PDB as APPS user

$ source <PDB_NAME>_<NODE_NAME>.env

$ sqlplus apps/apps@<PDB_NAME>

9) How to connect the Oracle E-Business Suite PDB as SYSTEM user?

Run the following commands to connect PDB as SYSTEM user

$ source <PDB_NAME>_<NODE_NAME>.env

$ sqlplus system/manager@<PDB_NAME>

10) How to access the Oracle E-Business Suite PDB while logged into the CDB?

Use the following command to set the environment, show the PDB name, and then connect to that PDB:

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> show pdbs;

SQL> alter session set container="PDBNAME";

11) Where to look for PDB errors if encounter a problem?

Source the environment and then review any plugin violations, as shown in the following example:

$ source <CDB_NAME>_<NODE_NAME>.env

$ sqlplus "/ as sysdba"

SQL> select name, cause, message, status from PDB_PLUG_IN_VIOLATIONS;

12) How to run AutoConfig on the database tier 

You should always source the Oracle E-Business Suite PDB environment before running AutoConfig. You can do so by running the following commands:

$ cd $ORACLE_HOME

$ source <PDB_NAME>_<NODE_NAME>.env 

Then run AutoConfig script

13) How to set up the environment to run an adgrants.sql?

Execute following steps before running Oracle E-Business Suite programs such as adgrants.sql.

$ source <CDB_NAME>_<NODE_NAME>.env

$ export ORACLE_PDB_SID=<PDB_NAME>

$ sqlplus "/ as sysdba"

14) How to run the EBS Technology Codelevel Checker (ETCC) against Oracle E-Business Suite PDB?

- The EBS Technology Codelevel Checker (ETCC) utility provides two scripts to help ensure you have the necessary database and application tier patches installed on your Oracle E-Business Suite Release 12.2 instance.

- ETCC extracts environment-related information from the context file (using the location defined in $CONTEXT_FILE), so you need to source the Oracle E-Business Suite PDB environment before you run the database checker script.

$ source <EBS PDB Name>_<NODE_NAME>.env

$ ./checkDBpatch.sh

15) How to list the OPatch inventory for a multitenant?

You can list the OPatch inventory of a multitenant database in the same way as for non-CDB. 

$ source <CDB_NAME>_<NODE_NAME>.env

$ export PATH=$PATH:$ORACLE_HOME/OPatch

$ opatch lsinventory –detail

16) How to set up the environment to install a database patch in an Oracle E-Business Suite multitenant database?

Source the CDB environment and add the OPatch directory to the path, as shown in the following example:

$ source <CDB_NAME>_<NODE_NAME>.env

$ export PATH=$PATH:$ORACLE_HOME/OPatch

17) Update Database Initialization Parameter at PDB level

• By default, initialization parameters at the PDB level inherit the value from the initialization parameters at the CDB level.
• There are parameters that can be changed at the PDB level and override what is being inherited from the CDB.
• To identify which parameters can be modified for a PDB, the ISPDB_MODIFIABLE column in the V$SYSTEM_PARAMETER view must be TRUE.  If the ISPDB_MODIFIABLE is TRUE then the parameter inherits the value from the CDB until it is changed with an ALTER SYSTEM SET command at the PDB.
• The initialization parameter set at the PDB level aren't stored in the spfile. They are stored in a data dictionary table in the root of the CDB (SYS.PDB_SPFILE$).
• When a PDB is moved around (through an unplug/plug), the initialization parameters set at the PDB level are part of the metadata stored in the XML file used for the unplug/plug.
• To reset a database parameter at PDB level

source <EBS CDB>_<hostname>.env

export ORACLE_PDB_SID=<EBS PDB SID>

sqlplus "/as sysdba"

Example: alter system set "_disable_actualization_for_grant"=TRUE; 

To check what all DB parameters can be set at PDB leve, following query can be used: 

SELECT NAME,ISPDB_MODIFIABLE FROM V$SYSTEM_PARAMETER WHERE UPPER(NAME) IN ('PROCESSES','SESSIONS','OPEN_CURSORS','_DISABLE_ACTUALIZATION_FOR_GRANT') ORDER BY 2,1;

Check value of DB parameters at CDB and PDB level:

SELECT CON_ID,NAME,DISPLAY_VALUE FROM V$SYSTEM_PARAMETER WHERE UPPER(NAME) IN ('<db parameter name>') ORDER BY 1,2,3;

UTL_FILE_DIR:

UTL_FILE_DIR is desupported with 19c database. UTL_FILE_DIR is a database pfile parameter. Which is used for EBS custom code or products requres access for read write file Operations on file system. 

Go through the article for more details - UTL_FILE_DIR Desupported with Oracle Database 19c

Hope this article will help you.

Best Regards,