19c Upgrade for Oracle EBS 12.2.9

-

Upgrading an Oracle E-Business Suite Release 12 Database to Oracle Database 19c

 

Source version

Target version

Database

12.1.0.2.0

19.10.0.0.0

ORACLE_SID

VISR12

CDBà CDBVIS & PDBàVISR12

ORACLE_HOME

/u02/oracle/VISR12/12.1.0

/u02/oracle/VISR12/19.3.0

Oracle E-Business

12.2.9

Operating system

Red Hat Enterprise Linux Server release 7.8 (x86_64)

Vendor

Oracle Linux

Contributors

Gouri Sankar Dash, Hemantha Gorella, Kanhu Gopal Mahapatra

Documents referred:

Oracle support Doc number: Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c (Doc ID 2552181.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 (Doc ID 2525754.1)

Major Steps:

1.  

1.        ETCC DB & Apps Tier patching

2.        Interoperability patches (ADOP)

3.        Install 19c software only

4.        Update 19c home to latest patch set (19.10)

5.        Create CDB with a new name

6.        Store UTL file values in to 12c DB

7.        Upgrade DB from 12c to 19c

8.        Now 19c CDB & 19c Non-CDB both are in the same home

9.        Convert 19c non CDB to PDB

10.     Correct the UTLDIR values in application CONTEXT_FILE & run auto config.

Most time-consuming steps are

  • Pre-upgrade patching (Interoperability patches, actualize all) -> Can be taken care beforehand.
  • Upgrading 12c to 19c. 
  • 19c non CDB to PDB conversion
Important Information Regarding the Upgrade to Oracle Database 19c

⮚       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.

⮚       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 de-support in Oracle Database 19c of the UTL_FILE_DIR database initialization parameter.

⮚       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 (I have used 19.10 for this POC).

⮚       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.

⮚       When upgrading your Oracle E-Business Suite database, it is essential to measure your performance to ensure certain operations will continue to meet your requirements. The most reliable strategy is to upgrade the database in a test environment, and then conduct a benchmark test with a configuration, product mix, and user load that simulates your own current and expected workloads. These conditions can help verify performance before you upgrade your production-ready environment.


2 - Pre-upgrade steps

Run hcheck.sql

We got a single error in our DB with the health check sql. We have ignored it after reading the note ID HCKE-0023: Orphaned SEG$ Entry (Doc ID 1360934.1). “In most cases the SEG$ is detached from its dictionary entry.” **No Impact. **. If you have more errors, you need to correct them before moving further.



Now let’s work on missing DB & APPS Tier ETCC patches, download the latest etcc patch from Patch 17537119

2.1.1 ETCC DB/Apps missed list

Apply the ETCC DB & Apps patches as per your ENV. Below patches are for my current ENV. There was no missing for Apps Tier for me.

 

21322448

21626377

30758943

31404014

31692949

2.1.2 - Oracle E-Business Suite patches need to apply.

26521736 → 19c interoperability patch for Release 12.2

31088182

28732161

31349591

31800803 → it contains post steps

30433124

Note- These all patches are AD patches to be applied in application. Try to complete all these in a single patch cycle. After applying all these patches we need to create appsutll.zip directory & copy to 12c Oracle_Home. where you will get  txkSetCfgCDB.env config file &  txkOnPremPrePDBCreationTasks.pl. Also complete the below steps to save time in future.

On the current run file system: $ adop phase=prepare, $ adop phase=actualize_all, $ adop phase=finalize, finalize_mode=full, $ adop phase=cutover.

On the new run file system: $ adop phase=cleanup cleanup_mode=full

2.1.4 - Create the initialization parameter setup files

Run the following commands to create the $ORACLE_HOME/dbs/<ORACLE_SID>_initparam.sql and $ORACLE_HOME/dbs/<ORACLE_SID>_datatop.txt files.

 

On UNIX/Linux:

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>

$ export ORACLE_SID=<ORACLE_SID>

$ cd $ORACLE_HOME/appsutil/bin

$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=<ORACLE_HOME> \

-outdir=<ORACLE_HOME>/appsutil/log -appsuser=<apps user> \

-dbsid=<ORACLE_SID> -skipdbshutdown=yes

 

IMP : Don’t source any environment while executing below steps:
 [oracle@ebsr12 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u02/oracle/VISR12/12.1.0

 

Oracle Home being passed: /u02/oracle/VISR12/12.1.0

[oracle@ebsr12 appsutil]$ export ORACLE_SID=VISR12

[oracle@ebsr12 appsutil]$ cd bin

[oracle@ebsr12 bin]$ pwd

/u02/oracle/VISR12/12.1.0/appsutil/bin

[oracle@ebsr12 bin]$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/u02/oracle/VISR12/12.1.0 \

> -outdir=/u02/oracle/VISR12/12.1.0/appsutil/log -appsuser=apps \

> -dbsid=VISR12 -skipdbshutdown=yes

Enter the APPS Password:

After this step, the script will generate below 3 important files (VISR12_initparam.sql,VISR12_datatop.txt &VISR12_PDBDesc.xml)  , which we will need to use while PDB converion & parameter setup.

 

-rw-r--r-- 1 oracle dba    3408 Mar 13 12:16 VISR12_initparam.sql

-rw-r--r-- 1 oracle dba     421 Mar 13 12:16 VISR12_datatop.txt

-rw-r--r-- 1 oracle dba   61088 Mar 13 12:16 VISR12_PDBDesc.xml

 

2.2 - Database Installation

 

2.2.1 -Prepare to create the 19c Oracle home

 

Create a new home for 19c. (/u02/oracle/VISR12/19.3.0/) parallel to 12c Home.

⮚       Mkdir -p /u02/oracle/VISR12/19.3.0/

⮚       Unzip the 19.3 software into it. You will get all ORACLE_HOME files there with runInstaller.sh script.

                             - set the 19c envà Create this 19c.env file manually & update below variables into that. We need to use this file multiple times during our upgrade.

 

⮚       export  ORACLE_BASE=/u02/oracle/VISR12

⮚       export PATH=/u02/oracle/VISR12/19.3.0/perl/bin:/u02/oracle/VISR12/19.3.0/bin:/usr/bin:/usr/sbin:/u02/oracle/VISR12/19.3.0/appsutil/jre/bin:/bin:/usr/bin/X11:/usr/local/bin:/u02/oracle/VISR12/19.3.0/perl/bin:/u02/oracle/VISR12/19.3.0/bin:/usr/bin:/usr/sbin:/u02/oracle/VISR12/19.3.0/appsutil/jre/bin:/bin:/usr/bin/X11:/usr/local/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:.:.

⮚       export ORACLE_HOME=/u02/oracle/VISR12/19.3.0

⮚       export PERL5LIB=/u02/oracle/VISR12/19.3.0/perl/lib/5.14.1:/u02/oracle/VISR12/19.3.0/perl/lib/site_perl/5.14.1:/u02/oracle/VISR12/19.3.0/appsutil/perl

⮚       export LD_LIBRARY_PATH=/u02/oracle/VISR12/19.3.0/lib:/usr/X11R6/lib:/usr/openwin/lib:/u02/oracle/VISR12/19.3.0/lib:/usr/dt/lib:/u02/oracle/VISR12/19.3.0/ctx/lib

⮚       export ORA_NLS10=/u02/oracle/VISR12/19.3.0/nls/data/9idata

 

 

 2.2.2 -Install Oracle Database 19c

 















/u02/oracle/oraInventory/logs/InstallActions2021-03-13_01-12-22PM/installActions2021-03-13_01-12-22PM.log

Here our 19.3 base installation completed.

 

2.2.3 - Now we will apply the additional patches on top of 19.3.0 Oracle_Home & few additional steps before creating the CDB.

 

Apply the latest Release Update certified for your platform documented in Table 1.6.1 or Table 1.7 of Section 3.3 of My Oracle Support Knowledge Document 1594274.1,
32218454 - 19.10.0 Patch set
32067171
29867728
31405300
31424070

“Download the latest opatch for 19c version & apply it before applying any patch from this list. (6880880)”

 

 

2.2.4 - Create the nls/data/9idata directory

On the database server node, as the owner of the Oracle RDBMS file system and database instance, run the following command to create the $ORACLE_HOME/nls/data/9idata directory.

$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl (Do it in 19c Oracle Home). 

[oracle@ebsr12 old]$ pwd

/u02/oracle/VISR12/19.3.0/nls/data/old

[oracle@ebsr12 old]$ perl cr9idata.pl

Creating directory /u02/oracle/VISR12/19.3.0/nls/data/9idata ...

Copying files to /u02/oracle/VISR12/19.3.0/nls/data/9idata...

Copy finished.

Please reset environment variable ORA_NLS10 to /u02/oracle/VISR12/19.3.0/nls/data/9idata!

 








 


2.2.5 - Create appsutil.zip and copy it to the database tier (19c)

 We need to now copy the appsutil.zip from Application Tier to Database Tier

[appltest@ebn1 bin]$ cd $AD_TOP/bin

[appltest@ebn1 bin]$ perl admkappsutil.pl

 

Note - This steps is for creating a fresh appsutil.zip file & copy to 19c Oracle Home.

 

2.2.6 – Install JRE 8

 

To install JRE 8 on the appsutil directory, copy the $ORACLE_HOME/jdk/jre directory to $ORACLE_HOME/appsutil/jre. Run the following commands:

 

$ cd $ORACLE_HOME/appsutil

$ cp -r $ORACLE_HOME/jdk/jre .

$ cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/appsutil/jre/lib/ext

 

Note - Make sure the ORACLE_HOME is 19c ORACLE_HOME.

 

2.2.7 - Create the CDB

 

On the database server node:

 

Please find the screenshot for Database Configuration Assistant (DBCA) to create the container database (CDB).
















 Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 SQL> SELECT value AS db_charset FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

 DB_CHARSET

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

AL32UTF8


 








2.2.8 Run datapatch on the CDB

 

Use the following commands to load any necessary patches on the CDB.

On UNIX/Linux:

$ export ORACLE_SID=<CDB SID>

$ $ORACLE_HOME/OPatch/datapatch

 


2.2.9 - Create the CDB MGDSYS schema

 Use SQL*Plus to connect to the CDB as SYSDBA and run the $ORACLE_HOME/rdbms/admin/catmgd.sql script. This creates the new MGDSYS schema on the CDB.

 $ sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql



2.2.10 - Create the CDB TNS files

On the database server node, run the following perl script to generate the required TNS files. Note that this script does not create a listener.

 

On UNIX/Linux:

 

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>

$ cd $ORACLE_HOME/appsutil/bin

$ perl txkGenCDBTnsAdmin.pl -dboraclehome=<ORACLE_HOME> \

-cdbname=<CDB SID> -cdbsid=<CDB SID> -dbport=<Database port> \

-outdir=<ORACLE_HOME>/appsutil/log

 




2.2.12 Shutdown CDB database

 Use SQL*Plus to connect to the CDB as SYSDBA and use the following command to shut down the database:

$

sqlplus "/ as sysdba"

SQL> shutdown;

 

Pre-upgrade checks –

 /u02/oracle/VISR12/12.1.0/jdk/bin/java -jar /u02/oracle/VISR12/19.3.0/rdbms/admin/preupgrade.jar FILE TEXT DIR /home/oracle/pre-chk_upgrade

 

 

[oracle@ebsr12 dbs]$ /u02/oracle/VISR12/12.1.0/jdk/bin/java -jar /u02/oracle/VISR12/19.3.0/rdbms/admin/preupgrade.jar FILE TEXT DIR /home/oracle/pre-chk_upgrade

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

PREUPGRADE SUMMARY

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

  /home/oracle/pre-chk_upgrade/preupgrade.log

  /home/oracle/pre-chk_upgrade/preupgrade_fixups.sql

  /home/oracle/pre-chk_upgrade/postupgrade_fixups.sql

 

Execute fixup scripts as indicated below:

 

Before upgrade:

 

Log into the database and execute the preupgrade fixups

@/home/oracle/pre-chk_upgrade/preupgrade_fixups.sql

 

After the upgrade:

 

Log into the database and execute the postupgrade fixups

@/home/oracle/pre-chk_upgrade/postupgrade_fixups.sql

 

Preupgrade complete: 2021-03-14T14:37:51

 

 

SQL> @/home/oracle/pre-chk_upgrade/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

 

Auto-Generated by:       Oracle Preupgrade Script

                         Version: 19.0.0.0.0 Build: 1

Generated on:            2021-03-14 14:37:22

 

For Source Database:     VISR12

Source Database Version: 12.1.0.2.0

For Upgrade to Version:  19.0.0.0.0

 

Preup                             Preupgrade

Action                            Issue Is

Number  Preupgrade Check Name     Remedied    Further DBA Action

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

    1.  invalid_objects_exist     NO          Manual fixup recommended.

    2.  amd_exists                YES         None.

    3.  exclusive_mode_auth       NO          Manual fixup recommended.

    4.  case_insensitive_auth     NO          Manual fixup recommended.

    5.  streams_setup             NO          Manual fixup recommended.

    6.  mv_refresh                NO          Manual fixup recommended.

    7.  underscore_events         NO          Informational only.

                                              Further action is optional.

    8.  dictionary_stats          YES         None.

    9.  parameter_deprecated      NO          Informational only.

                                              Further action is optional.

   10.  rman_recovery_version     NO          Informational only.

                                              Further action is optional.

 

The fixup scripts have been run and resolved what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database is not ready

for upgrade.  To resolve the outstanding issues, start by reviewing

the preupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

 

PL/SQL procedure successfully completed.

 



2.3 - Database Upgrade

 

2.3.1 - Complete patching cycle and remove adop created editions (These were already completed in parallel to save time before hand.

 

On the current run file system:

$ adop phase=prepare

$ adop phase=actualize_all

$ adop phase=finalize finalize_mode=full

$ adop phase=cutover

On the new run file system:

$ adop phase=cleanup cleanup_mode=full

 

2.3.2 - Store the UTL_FILE_DIR parameter values

 Perform the instructions in Section 3.1.1, Migrating UTL_FILE_DIR Settings when Upgrading to Oracle Database 19c, in My Oracle Support Knowledge Document 2525754.1

 Source the Oracle E-Business Suite database environment of your 11g or 12c Oracle home.

 perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/u02/oracle/VISR12/12.1.0/appsutil/VISR12_ebsr12.xml \

-oraclehome=/u02/oracle/VISR12/12.1.0 -outdir=/home/oracle \

-upgradedhome=/u02/oracle/VISR12/19.3.0 -mode=getUtlFileDir -servicetype=onpremise

 

[oracle@ebsr12 ~]$  perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/u02/oracle/VISR12/12.1.0/appsutil/VISR12_ebsr12.xml -oraclehome=/u02/oracle/VISR12/12.1.0 -outdir=/home/oracle -upgradedhome=/u02/oracle/VISR12/19.3.0 -mode=getUtlFileDir -servicetype=onpremise

Enter the APPS Password:

Script Name    : txkCfgUtlfileDir.pl

Script Version : 120.0.12020000.15

Started        : Sun Mar 14 13:55:13 EDT 2021

Log File       : /home/oracle/TXK_UTIL_DIR_Sun_Mar_14_13_55_10_2021/txkCfgUtlfileDir.log

Context file: /u02/oracle/VISR12/12.1.0/appsutil/VISR12_ebsr12.xml exists.

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

Successfully generated the below file with UTL_FILE_DIR content:

/u02/oracle/VISR12/12.1.0/dbs/VISR12_utlfiledir.txt

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

Completed        : Sun Mar 14 13:55:14 EDT 2021

 

Successfully Completed the script

ERRORCODE = 0 ERRORCODE_END

[oracle@ebsr12 ~]$


This will create file VISR12_utlfiledir.txt under your 12C ORACLE_HOME. Which will contain your UTLFILE dir location info. We need to load these values into 12C DB before upgrading.



Then run again the setUtlFile mode to store the values into the DB. (Else you will get error in the post upgrade.)

 Source the Oracle E-Business Suite database environment of your 11g or 12c Oracle home.

perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/u02/oracle/VISR12/12.1.0/appsutil/VISR12_ebsr12.xml \

-oraclehome=/u02/oracle/VISR12/12.1.0 -outdir=/home/oracle \

-upgradedhome=/u02/oracle/VISR12/19.3.0 -mode=setUtlFileDir -servicetype=onpremise

 

#===========================================================================#

# For reference, original contents of UTL_FILE_DIR without any updates are  #

# retained in below file:                                                   #

#                                                                           #

# /home/oracle/TXK_UTIL_DIR_Sun_Mar_14_13_55_10_2021/VISR12_utlfiledir.txt

#############################################################################

/u02/oracle/VISR12/temp/VISR12

/u02/oracle/VISR12/temp/VISR12

/u02/oracle/VISR12/19.3.0/appsutil/outbound/VISR12_ebsr12

/u02/oracle/VISR12/temp/VISR12

2.3.3 - Shut down the application tier server processes

     ⮚       [applmgr@ebsr12 scripts]$ adstpall.sh 

2.3.5 - Shut down the database listener

⮚       [oracle@ebsr12 dbs]$ lsnrctl stop VIR12

2.3.6 - Prepare to upgrade

Ensure that the oratab file contains an entry for the database to be upgraded.

Modify the following initialization parameters before the upgrade:

⮚       Comment out all the deprecated initialization parameters. Any necessary parameter will be added back in after the upgrade.

⮚       Unset the olap_page_pool_size initialization parameter.

⮚       If you have an 11g source database, set the PGA_AGGREGATE_TARGET initialization parameter to at least 10G.

⮚       Set the SGA_TARGET initialization parameter to at least 2G.

⮚       Add the event EVENT='10946 trace name context forever, level 8454144'

Shut down and restart the database to enable the parameters.

 

2.3.7 - Upgrade the database instance

 







You can choose your own recover strategies here.









Now you will see you won’t be able to login to the oracle DB using your EBS env file. But you will see the DB is still up & running.

After this you won’t be able to login as apps because the upgrade script removes the sec_case_sensativity_logon automatically from init file & creates a spfile in 19c ORACLE_HOME/dbs.

You need to make sure you add that parameter back before moving to next step.

 





As the ORACLE_HOME has been changed, you need to export the ORACLE_HOME staying in the Oracle 12c DB connection putty.









Look carefully, it’s a 12C client connecting 19c DB.

Next,

Ensure that the trigger SYSTEM.EBS_LOGON is valid by connecting to the database using SQL*Plus as SYSDBA and running the following compile command:

 

SQL> alter trigger SYSTEM.EBS_LOGON compile;

 









 

2.3.9 - Perform patch post-install instructions

Connect to the database as SYSDBA and run the following scripts:

 

SQL> @?/rdbms/admin/dbmsxdbschmig.sql

SQL> @?/rdbms/admin/prvtxdbschmig.plb

 

SQL> @?/rdbms/admin/dbmsxdbschmig.sql

 

Session altered.

 

SQL> SET FEEDBACK 1

SQL> SET NUMWIDTH 10

SQL> SET LINESIZE 80

SQL> SET TRIMSPOOL ON

SQL> SET TAB OFF

SQL> SET PAGESIZE 100

SQL>

SQL> CREATE OR REPLACE PACKAGE sys.xdb_migrateschema IS

  2

 |

|

|

|

 

SQL> Rem    surman      03/08/13 - 16462837: Common start and end scripts

SQL> Rem    surman      03/08/13 - Created

SQL> Rem

SQL>

SQL> alter session set "_ORACLE_SCRIPT" = false;

 

Session altered.

 

SQL>

 

 

SQL> @?/rdbms/admin/prvtxdbschmig.plb

SQL> @@?/rdbms/admin/sqlsessstart.sql

SQL> Rem

SQL> Rem $Header: rdbms/admin/sqlsessstart.sql /main/3 2018/07/25 13:50:02 surman Exp $

SQL> Rem

 

 

SQL> Rem    MODIFIED   (MM/DD/YY)

SQL> Rem    surman      05/04/18 - 27464252: Update SQL_PHASE

SQL> Rem    surman      03/08/13 - 16462837: Common start and end scripts

SQL> Rem    surman      03/08/13 - Created

SQL> Rem

SQL>

SQL> alter session set "_ORACLE_SCRIPT" = false;

 

Session altered.

 

SQL>

 

Note - Execute postupgrade_fixups.sql

SQL> postupgrade_fixups.sql

SP2-0734: unknown command beginning "postupgrad..." - rest of line ignored.

SQL> @postupgrade_fixups.sql

 

Session altered.

 

 

PL/SQL procedure successfully completed.

 

 

PL/SQL procedure successfully completed.

 

 

PL/SQL procedure successfully completed.

 

 

Package created.

 

No errors.

 

Package body created.

 

 

PL/SQL procedure successfully completed.

 

No errors.

 

2.4 – Post Database Upgrade

2.4.1 - Run adgrants.sql

 

Copy $APPL_TOP/admin/adgrants.sql  from the administration server node to the database server node. Use SQL*Plus to connect to the database as SYSDBA, and run the script using the following command:




$ sqlplus "/ as sysdba" @adgrants.sql (or adgrants_nt.sql) <apps user>

 

SQL> @adgrants.sql

Connected.

 

PL/SQL procedure successfully completed.

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

--- adgrants.sql started at 2021-03-15 21:22:10 ---

 

Enter value for 1: apps

 

Creating PL/SQL profiler objects.

|

|

|

12 rows selected.

Grants given by this script have been written to the ad_zd_logs table.

You can run $AD_TOP/sql/ADZDSHOWLOG.sql to produce a report showing these grants.

 

Commit complete.

 

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.10.0.0.0

[oracle@ebsr12 ~]$

 

2.4.2 - Grant create procedure privilege on CTXSYS

 

Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node. Use SQL*Plus to connect to the database as apps and run the script using the following command:

 

$ sqlplus apps/<apps password> @adctxprv.sql <SYSTEM password> CTXSYS


2.4.3 - Compile invalid objects

 sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql

 

2.4.4 - Grant datastore access

 $ sqlplus "/ as sysdba"

SQL> grant text datastore access to public;

2.4.5 - Gather statistics for the SYS schema

 Copy $APPL_TOP/admin/adstats.sql from the administration server node to the database server node. Note that adstats.sql has to be run in restricted mode. Use SQL*Plus to connect to the database as SYSDBA and use the following commands to run adstats.sql in restricted mode:

 $ sqlplus "/ as sysdba"

SQL> alter system enable restricted session;

SQL> @adstats.sql

$ sqlplus "/ as sysdba"

SQL> alter system disable restricted session;

SQL> exit;



2.5 - Convert Database to Multitenant Architecture

 

Note:

·       At this point there are two databases that are associated with the 19c Oracle home, the CDB and the non-CDB database. Moreover, the non-CDB database will be migrated to the PDB database. Set the appropriate environment variables to connect to the appropriate database

 

·       Use the txkSetCfgCDB.env under $ORACLE_HOME/appsutil to set the env every time till migration completes.

·       Take a cold backup. of the 19c Non-CDB.


2.5.1 Create PDB Descriptor

 

We need to create the necessary PDB XML descriptor files needed using “txkOnPremPrePDBCreationTasks.pl”. This will also shutdown the database i.e. 19c Non-CDB Database which we have upgraded.

You should not open the database again till the entire PDB migration process is complete.

 

[oraVISR12@ebn1 dbs]$ cd $ORACLE_HOME/appsutil

[oraVISR12@ebn1 appsutil]$ pwd

/u02/oracle/VISR12/19.3.0/appsutil

[oraVISR12@ebn1 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u02/oracle/VISR12/19.3.0

Oracle Home being passed: /u02/oracle/VISR12/19.3.0

 

[oraVISR12@ebn1 appsutil]$ export ORACLE_SID=VISR12

[oraVISR12@ebn1 appsutil]$ cd $ORACLE_HOME/appsutil/bin

 

[oraVISR12@ebn1 bin]$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/u02/oracle/VISR12/19.3.0 \

-outdir=/u02/oracle/VISR12/19.3.0/appsutil/log -appsuser=apps -dbsid=VISR12






****Very IMP step: DON’T START 12C DB till migration completes. **************

 

2.5.3 Update CDB Initialization Parameters

 

On the database server node, copy the <source SID>_initparam.sql and <source SID>_datatop.txt files from the source $ORACLE_HOME/dbs directory to the new $ORACLE_HOME/dbs directory. Then use SQL*Plus to connect to the CDB as SYSDBA, and run the following commands to update the CDB initialization parameters:

 

On UNIX/Linux:

 

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>

$ export ORACLE_SID=<CDB SID>

$ sqlplus "/ as sysdba"

SQL> startup nomount;

SQL> @$ORACLE_HOME/dbs/<source SID>_initparam.sql

SQL> alter system set LOCAL_LISTENER="<hostname>:<port number>" scope=both;

SQL> shutdown;

SQL> startup;

 

[oraVISR12@ebn1 bin]$ cd $ORACLE_HOME/appsutil

[oraVISR12@ebn1 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u02/oracle/VISR12/19.3.0

Oracle Home being passed: /u02/oracle/VISR12/19.3.0

 

[oraVISR12@ebn1 appsutil]$ export ORACLE_SID=CDBVIS

[oraVISR12@ebn1 appsutil]$ sqlplus "/as sysdba"

SQL>

startup nomount;

SQL> @$ORACLE_HOME/dbs/VISR12_initparam.sql

SQL> alter system set LOCAL_LISTENER=ebsr12:1521" scope=both;

System altered.


2.5.4 - Check for PDB Violations

 

Note - Before we migrate to PDB, we need to check for any violations that will impact the Non-CDB to PDB conversion.

 

Use the following commands to run the txkChkPDBCompatability.pl script. This checks the PDB for any violations.

On UNIX/Linux:

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>

$ export ORACLE_SID=<CDB SID>

$ cd $ORACLE_HOME/appsutil/bin

$ perl txkChkPDBCompatability.pl -dboraclehome=<ORACLE_HOME> \

-outdir=<ORACLE_HOME>/appsutil/log -cdbsid=<CDB SID> \

-pdbsid=<source SID> -servicetype=onpremise

 

[oraVISR12@ebn1 appsutil]$ cd $ORACLE_HOME/appsutil

[oraVISR12@ebn1 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u02/oracle/VISR12/19.3.0

Oracle Home being passed: /u02/oracle/VISR12/19.3.0

 

[oraVISR12@ebn1 appsutil]$ export ORACLE_SID=CDBVIS

[oraVISR12@ebn1 appsutil]$ cd $ORACLE_HOME/appsutil/bin

[oraVISR12@ebn1 bin]$ perl txkChkPDBCompatability.pl -dboraclehome=/u02/oracle/VISR12/19.3.0 \

-outdir=/u02/oracle/VISR12/19.3.0/appsutil/log -cdbsid=CDBVIS -pdbsid=VISR12 -servicetype=onpremise

 




 

More details below



We got few warnings only no error. So, we are good to go to the PDB creation.

Else you need to resolve all the violation errors till you get cleared.

 


2.5.5 - Create the PDB

 

Load the environment variables by running the following commands.

On UNIX/Linux:

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>

$ cd $ORACLE_HOME/appsutil/bin

$ perl txkCreatePDB.pl -dboraclehome=<ORACLE_HOME> -outdir=<ORACLE_HOME>/appsutil/log \

-cdbsid=<CDB SID> -pdbsid=<source SID> -dbuniquename=<CDB SID> -servicetype=onpremise

 

Finally, we have reached the important step of converting the Non-CDB database to PDB.

Before running the PDB creation script “txkCreatePDB.pl” ensure that you have

• Compiled all Invalid Objects

• There is enough Disk Space

• Check SGA sizing

[oraVISR12@ebn1 bin]$ cd /u02/oracle/VISR12/19.3.0/appsutil

[oraVISR12@ebn1 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u02/oracle/VISR12/19.3.0

Oracle Home being passed: /u02/oracle/VISR12/19.3.0

 

[oraVISR12@ebn1 appsutil]$ cd $ORACLE_HOME/appsutil/bin

[oraVISR12@ebn1 bin]$ perl txkCreatePDB.pl -dboraclehome=/u02/oracle/VISR12/19.3.0 \

-outdir=/u02/oracle/VISR12/19.3.0/appsutil/log -cdbsid=CDBVIS -pdbsid=VISR12 \

-dbuniquename=CDBVIS -servicetype=onpremise

 

The PDB datafile location can be the same as the source datafile location. When prompted with "Enter the wallet credentials" and "Enter the secret key", enter the previously specified CDB wallet password and non-CDB secret password respectively.

 



 


 

Note: Here I have given the Datafile location as the same as of my source 12C Datafile location as I didn't have space. 

But if you want a different location you can change it.






 

And finally, 😊


 

 



 











2.5.6 - Run the post PDB script

Run the post PDB script. à Very imp (THIS has to be performed from the session where we have exported the PDB SID. Else it will fail.

Use the following commands to run the

txkPostPDBCreationTasks.pl script. This updates the PDB configuration.

On UNIX/Linux:

$ export ORACLE_SID=VISR12

 

Use the following commands to run the txkPostPDBCreationTasks.pl script. This updates the PDB configuration.

 

On UNIX/Linux:

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=<full path of ORACLE_HOME>

$ perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=<ORACLE_HOME> \

-outdir=<ORACLE_HOME>/appsutil/log -cdbsid=<CDB SID> -pdbsid=<PDB SID> \

-appsuser=apps -dbport=<TNS port number> -servicetype=onpremise

 

oracle@ebsr12 appsutil]$ . ./txkSetCfgCDB.env dboraclehome=/u02/oracle/VISR12/19.3.0/

Oracle Home being passed: /u02/oracle/VISR12/19.3.0/

[oracle@ebsr12 appsutil]$ perl /u02/oracle/VISR12/19.3.0/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=/u02/oracle/VISR12/19.3.0 \

> -outdir=/u02/oracle/VISR12/19.3.0/appsutil/log -cdbsid=CDBVIS -pdbsid=VISR12 \

> -appsuser=apps -dbport=1521 -servicetype=onpremise

Enter the APPS Password:

 

Enter the CDB SYSTEM Password:

 

Script Name    : txkPostPDBCreationTasks.pl

Script Version : 120.0.12020000.52

Started        : Tue Mar 16 13:59:20 EDT 2021

 

Log File       : /u02/oracle/VISR12/19.3.0/appsutil/log/TXK_POST_PDB_Tue_Mar_16_13_59_06_2021/txkPostPDBCreationTasks.log
















Here you will get 2 ENV files created in your 19c ORACLE_HOME. 1 for PDB & 1 for CDB.

While connecting to EBS database, you can source PDB ENV & connect as shown below.





Now check the DBA registry.




Few post steps like DB link recreate & Reconfigure external node integrations, we skipped as those are not relevant to us & conditional.

2.5.7- Modify initialization parameters  

 

Use the following sections in My Oracle Support Knowledge Document 396009.1, Database Initialization Parameter Settings for Oracle E-Business Suite Release 12, as a guideline in modifying your initialization parameters.

 

a)       Common Database Initialization Parameters for All Releases (You may need to verify this)

b)       Release-Specific Database Initialization Parameters for Oracle 19c. (You may need to verify this)

c)       Additional Database Initialization Parameters for Oracle E-Business Suite Release 12.2. (You may need to verify this)

d)       Temporary Tablespace Setup (You may need to verify this)

e)       Database Initialization Parameter Sizing (You may need to verify this)

f)        Modify the appsTier context file for app (We have done only this from /usr/tmp to correct value)

 

Previous value:

[applmgr@ebsr12 ~]$ grep APPLPTMP $CONTEXT_FILE

         <APPLPTMP oa_var="s_applptmp" osd="UNIX">/usr/tmp</APPLPTMP>

 

 

 

New value:

[applmgr@ebsr12 scripts]$ grep APPLPTMP $CONTEXT_FILE

         <APPLPTMP oa_var="s_applptmp" osd="UNIX">/u02/oracle/VISR12/temp/VISR12</APPLPTMP>

 

 

2.5.8 - Run AutoConfig on applications tier



 













2.5.14 - Restart application tier server processes & validate all.

 



Issues:

1.       Autoconfig failing with error, as we missed the setUTL part

+++++++++++++++++++++++++++++++++++++++++++++++++++

UTL_FILE_DIR:

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

PROGRAM : (/u02/oracle/VISR12/19.3.0/appsutil/bin/txkCfgUtlfileDir.pl)

TIME    : Tue Mar 16 20:16:24 2021

FUNCTION: main::getUtlFileDirParam [ Level 1 ]

ERRORMSG: Unable to read UTL_FILE_DIR parameter.

 

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)

 

 

The above issue came as we missed the setUtlFileDir script before upgrade.

UTLFILE location was not present in the DB side

.



Create DIR /u02/oracle/VISR12/temp/

[oracle@ebsr12 oracle]$ mkdir -p /u02/oracle/VISR12/temp/VISR12/

[oracle@ebsr12 oracle]$cd /u02/oracle/VISR12/temp

[oracle@ebsr12 oracle]$chmod -R 777 VISR12

 

Then rerun post PDB script. (This is harmless, can be run multiple times till you correct the error)

 

perl /u02/oracle/VISR12/19.3.0/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=/u02/oracle/VISR12/19.3.0 \

-outdir=/u02/oracle/VISR12/19.3.0/appsutil/log -cdbsid=CDBVIS -pdbsid=VISR12 \

-appsuser=apps -dbport=1521 -servicetype=onpremise

 

*****Make sure to export ORACLE_SID=VISR12, else you will see error ****

+++++++++++++++++++++++++++++++++++++++++++++++++++++

2.       Error:2

 

ERROR DESCRIPTION:

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

PROGRAM : (/u02/oracle/VISR12/19.3.0/appsutil/bin/txkCfgUtlfileDir.pl)

TIME    : Tue Mar 16 20:48:48 2021

FUNCTION: main::checkUtlFileDir [ Level 1 ]

ERRORMSG: Directory object does not exist for path : /usr/tmp

)

ERRORCODE = 1 ERRORCODE_END

.end std out.

 

Solution:

***********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.

  

SQL> sho parameter list

 NAME                                 TYPE        VALUE

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

forward_listener                     string

listener_networks                    string

local_listener                       string      ebsr12:1521

remote_listener                      string

 

SQL>  alter system set local_listener='';

 System altered.

 

SQL> sho parameter list

 NAME                                 TYPE        VALUE

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

forward_listener                     string

listener_networks                    string

local_listener                       string

remote_listener                      string


SQL> alter system register;

 


Comments

Post a Comment

Popular posts from this blog

R12.2 Log file locations

-
Few Logfile location on EBS R12.2.X Admin server   -   $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/AdminServer/logs/AdminServer.log oacore logfile  -  $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1.log oacore out file  -  $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1.out oacore diagnostic log -  $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oacore_server1/logs/oacore_server1-diagnostic.log oafm logfile -  $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1.log oafm outfile -  $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1.out oafm diagnostic log  - $FMW_HOME/user_projects/domains/EBS_domain_$TWO_TASK/servers/oafm_server1/logs/oafm_server1-diagnostic.log form server log  - $FMW_HOME/user_projects/domain...
Read more

Cloning

-
Apps 11i Multinode to single node To see if your environment is ready for the Cloning with Rapid Clone, please check the Metalink Note   230672.1. NOTE: The initial system has ONE APPL_TOP on one node (apps.localdomain) and the DB tier on another node (db.localdomain).     These easy to follow steps will allow you to move or clone Oracle applications from one location to another. This can be essential when moving from a multi-node system to single-node system. It may seem complex, but by approaching such a task one stage at a time it will become much easier under understand this technical process. 1.   Maintain Snapshot information (on the APPS node)     adadmin -> 2. Maintain Applications Files menu -> 5. Maintain snapshot information -> 2. Update current view snapshot 2. Prepare the source system database tier for cloning       cd <RDBMS ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME...
Read more

Oracle Kye-vault Installation (Version 21.4 ) ( Chapter -I )

-
Image
Interesting story behind this: I have received a customer requirement for a DBA position, and the main criteria was: the DBA must know oracle key vault, for their security solution project. I have received around 25/30 resumes where everyone has written, they know Oracle key-vault. While interviewing, someone saying it's a security file 😆, someone saying it's a parameter that exists since 12c on-words 😀, others saying it's a tool like oracle wallet & TDE 😟. Everyone disappointed me, although it's just a matter of little more detail reading of  (https://docs.oracle.com/en/database/oracle/key-vault/21.4/).   So thought of writing this blog and share it across for quick understanding.  Note: I have done this installation/deployment of  appliance image in my VM ware server. Created a 300Gigs vm with 8 GB of RAM & 2 vCPUs, and deployed it. Very simple.  Oracle Kye-vault is a security solution soft appliance from Oracle . It comes as an ISO image from...
Read more