Tuesday, October 15, 2024

Implementing Case-Insensitive Pluggable Database Name with Oracle E-Business Suite Release 12.2 on Oracle Database 19c Multitenant Architecture

This article describes how to configure case-insensitive pluggable database (PDB) names in the Oracle Database 19c Multitenant Architecture for use with Oracle E-Business Suite Release 12.2.

Note: This article assumes, it is for a database without RAC and DataGuard configured and a single-node application tier.

Pre-requisites

  1. Should be Oracle E-Business Suite 12.2.3 and later. For additional information, refer to the Prerequisites section in Doc ID 2782130.1.
  2. Create appsutil.zip and copy it to the database tier.
  3. If you use a pluggable database encrypted using transparent database encryption (TDE), refer to Oracle Support Knowledge Document 2301128.1, Impact Of Renaming PDB On TDE Configuration.
  4. If you have integrated Oracle E-Business Suite with Oracle Access Manager using Oracle E-Business Suite AccessGate, you must deregister Oracle E-Business Suite from Oracle Access Manager before performing the steps to change your existing PDB name to uppercase.

Steps to setup case-insensitive pluggable database (PDB) name

  1. Stop Application Tier Services and Update Directory Object Paths
    • On the application tier node, source the environment and shut down all the application tier services
      $ cd <EBS_ROOT>
      $ . ./EBSapps.env run
      $ cd $ADMIN_SCRIPTS_HOME
      $ ./adstpall.sh 
      
    • Take a full backup of the database and application tier using your preferred backup procedure.
    • Log in as the 'oracle' user on the database tier, and run the following command to identify the directory paths that are currently stored in the UTL_FILE_DIR parameter:
      $ . <PDB name>_<hostname>.env
      $ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl \
         -contextfile=$CONTEXT_FILE \
         -oraclehome=$ORACLE_HOME \
         -outdir=$ORACLE_HOME/appsutil/log \
         -mode=getUtlFileDir \
         -servicetype=<onpremise|opc>
      
      The script creates a text file named <PDB name>_utlfiledir.txt in the <ORACLE_HOME>/dbs directory.
    • Create the following directories on database node
      $ mkdir -p $ORACLE_HOME/../temp/<uppercase PDB name>
      $ mkdir -p $ORACLE_HOME/appsutil/outbound/<uppercase PDB name>_<hostname>
      
    • Update the directory paths in file <ORACLE_HOME>/dbs/<PDB name>_utlfiledir.txt to match the directory paths created in the above step.
    • Run the txkCfgUtlfileDir.pl script to update the directory object paths on the database node:
      $ . <PDB Name>_<hostname>.env
      $ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl \
         -contextfile=$CONTEXT_FILE \
         -oraclehome=$ORACLE_HOME \
         -outdir=$ORACLE_HOME/appsutil/log \
         -mode=setUtlFileDir \
         -servicetype=<onpremise|opc>
      
  2. Rename Pluggable Database and Disable Case-Sensitive PDB Names
    • On the database node, set the environment for the CDB:
      $ cd <19c_ORACLE_HOME>
      $ . ./<CDB name>_<hostname>.env
      
    • Connect to the CDB via SQL*Plus as SYSDBA, and close the pluggable database:
      $ sqlplus / as sysdba
      SQL> alter pluggable database "<PDB name>" close instances=all;
      
    • Open the pluggable database in RESTRICTED mode:
      SQL> alter pluggable database "<PDB name>" open restricted;
      
    • While still connected to the CDB$ROOT container, change the PDB to have a temporary name
      SQL> alter pluggable database "<PDB name>" rename global_name to TEMP<uppercase PDB name>;
    • Note: This step is required as the current global_name is case-insensitive, and if you attempt to use the same name but with a different case you will receive the ORA-65042: name is already used by an existing container error.
    • Set the _pdb_name_case_sensitive initialization parameter to FALSE.
      SQL> alter system set "_pdb_name_case_sensitive"=FALSE scope=spfile;
      
      If you are using a parameter file (PFILE) for example: <19c_ORACLE_HOME>/dbs/init<SID>.ora, use a file editor and change the _pdb_name_case_sensitive initialization parameter to FALSE within the PFILE and save the change.
    • Stop and restart the container database for the initialization parameter change to take effect:
      SQL> alter session set container = CDB$ROOT;
      SQL> shutdown;     
      SQL> startup;
      
    • Connect to the CDB via SQL*Plus as SYSDBA, and close the pluggable database:
      $ sqlplus / as sysdba
      SQL> alter pluggable database TEMP<uppercase PDB name> close;
      
    • Open the pluggable database in RESTRICTED mode:
      SQL> alter pluggable database TEMP<uppercase PDB name> open restricted;
    • Connect to the CDB via SQL*Plus as SYSDBA, and rename the temporary pluggable database back to the original name, but using uppercase:
      SQL> alter pluggable database "TEMP<uppercase PDB name>" rename global_name to "<uppercase PDB name>";
      
      Run the following command to verify the change:
      SQL> show pdbs
      
      You should see the pluggable database name in uppercase.
    • The renamed PDB will still be in RESTRICTED mode. Run the following commands to restart the PDB:
      SQL> alter pluggable database <uppercase PDB name> close;
      SQL> alter pluggable database <uppercase PDB name> open;
      
  3. Configure Oracle E-Business Suite Database Tier
    • Run AutoConfig for the Pluggable Database
      Log in as oracle and set the environment as follows:
      
      $ cd <19c_ORACLE_HOME>/appsutil
      $ . ./txkSetCfgCDB.env dboraclehome=<19c_ORACLE_HOME>
      $ export ORACLE_SID=<CDB_SID>
      $ export ORACLE_UNQNAME=<19c_ORACLE_UNQNAME>
      $ export TNS_ADMIN=<19c_ORACLE_HOME>/network/admin
      
    • As the oracle user, run the txkPostPDBCreationTasks.pl script, specifying parameter values applicable to your environment. This script will create a new database tier context file and run AutoConfig. You will need to provide the APPS password when prompted.
      $ perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl \
              -dboraclehome=<19c_ORACLE_HOME> \
              -outdir=<19c_ORACLE_HOME>/appsutil/log \
              -cdbname=<CDB name> \
              -dbuniquename=<CDB db_unique_name> \
              -cdbsid=<CDB Instance Name> \
              -pdbsid=<Uppercase PDB Name> \
              -appsuser=<apps user> \
              -israc=no \
              -dbport=<EBS DB port> \
              -servicetype=onpremise
      
  4. Configure Oracle E-Business Suite Application Tier
    • Source the environment for the run edition file system
      $ cd <EBS_ROOT>
      $ . ./EBSapps.env run
      
    • Create a new application tier context file by running the following command, and setting the APPLPTMP value to one of the values represented by the s_db_util_filedir database tier context file variable:
      $ perl $AD_TOP/patch/115/bin/txkAppsDBConfig.pl \
              contextfile=<path to existing run CONTEXT_FILE> \
              dbhost=<DB host|Scan Host> \
              dbdomain=<DB Domain|Scan domain> \
              dbport=<EBS DB port|Scan Port> \
              dbsid=<uppercase PDB name> \
              applptmp=<APPLPTMP>
      
    • As the case of the PDB name has now changed, the CONTEXT_NAME based on that value has also changed. Use a text editor such as vi to amend the Oracle HTTP Server (OHS) httpd.conf and ssl.conf configuration files under the $FMW_HOME on both the run and patch file systems, updating all relevant CONTEXT_NAME values with the new CONTEXT_NAME.
    • Specifying the new context file generated on the run edition file system, run AutoConfig to propagate the changes
      $ $AD_TOP/bin/adconfig.sh contextfile=<new run CONTEXT_FILE>
      
    • Source the new environment file of the run edition file system:
      $ cd <EBS_ROOT>
      $ . ./EBSapps.env run
      
      You can use the following command to confirm the environment variable FILE_EDITION points to the run edition file system:
      $ echo $FILE_EDITION
      
    • Run the following command to regenerate the configuration files on the patch file system and upload the patch context file to the database:
      $ perl $AD_TOP/patch/115/bin/txkPatchAppsConfig.pl patchcontextfile=<new patch CONTEXT_FILE>
      
    • Start the application tier services:
      $ cd <EBS_ROOT>
      $ . ./EBSapps.env run
      $ cd $ADMIN_SCRIPTS_HOME
      $ ./adstrtal.sh
      
  5. If you have implemented any integrations that connect an external node to Oracle E-Business Suite, you must now reconfigure those integrations with the updated DBC file.
Hope this article will be helpful.


Some Most Popular Articles