Steps to Upgrade and Move 11g DB from VM to 19c DB System for Oracle E-Business Suite 12.1.3 on OCI Cloud

Steps to Upgrade 11g DB System to 19c DB System for Oracle E-Business Suite 12.1.3 on OCI Cloud

In this post I am going to share how to upgrade the 11g DB to 19c DB system on OCI for EBS 12.1.3 Application.

Source Oracle Database 11.2.0.4, OS Linux 7.9 VM
Target Oracle Database 19.23.0.0.0 DBCS

EBS Version 12.1.3

Steps:

Fetch the Current Details of Source Database:

Check TDE Encryption details on source 11g database.

select * from  v$encryption_wallet;

WRL_TYPE

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

WRL_PARAMETER

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

STATUS

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

file

/u01/app/oracle/product/11.2.0.4/dbhome_1/admin/PROD/wallet

CLOSED

Below steps required only if TDE is present.

Connect to 11g Database Server

Create Directory 

mkdir -p  /u01/app/oracle/product/11.2.0.4/dbhome_1/admin/PROD/wallet

Open the encryption wallet

on 11g server

sqlplus '/as sysdba'

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "anything";

System altered.

SQL>  select * from  v$encryption_wallet;

WRL_TYPE

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

WRL_PARAMETER

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

STATUS

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

file

/u01/app/oracle/product/11.2.0.4/dbhome_1/admin/PROD/wallet

OPEN

Validate the Encryption Wallet Key 

ls -ltr /u01/app/oracle/product/11.2.0.4/dbhome_1/admin/PROD/wallet

total 4

-rw-r--r-- 1 oracle oinstall 2576 Jul  5 21:43 ewallet.p12

Validate the encryption wallet key.

sqlplus '/as sysdba'

SQL> alter system set encryption wallet close identified by "anything";

System altered.

SQL>  select * from  v$encryption_wallet;

WRL_TYPE

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

WRL_PARAMETER

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

STATUS

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

file

/u01/app/oracle/product/11.2.0.4/dbhome_1/admin/PROD/wallet

CLOSED

SQL>  ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "anything";

System altered.

SQL> select * from  v$encryption_wallet;

WRL_TYPE

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

WRL_PARAMETER

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

STATUS

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

file

/u01/app/oracle/product/11.2.0.4/dbhome_1/admin/PROD/wallet

OPEN

Check the DB wallet certificate 

 cd /u01/app/oracle/product/11.2.0.4/dbhome_1/admin/PROD/wallet

orapki wallet display -wallet .

Oracle PKI Tool : Version 11.2.0.4.0 - Production

Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Requested Certificates:

Subject:        CN=oracle

User Certificates:

Oracle Secret Store entries:

ORACLE.SECURITY.DB.ENCRYPTION.AcvSpemcs0+Tvz/WHeItwskAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY

Trusted Certificates:

Check the NLS Character Set 

SQL> select * from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

PARAMETER

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

VALUE

-----------

NLS_CHARACTERSET

AL32UTF8

Check current db version

SQL> select banner from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 - Production

Check apps version

SQL> select release_name from apps.fnd_product_groups;

RELEASE_NAME

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

12.1.3

1) Run hcheck.sql

Follow My Oracle Support Knowledge Document 136697.1 hcheck.sql - Script to Check Data Dictionary for Known Problems, to run hcheck.sql, which looks for some known common Data Dictionary problems.

On DB server , source 11g environment 

 sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 3 22:44:43 2024

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool hcheck_3jun2024.out

SQL> @hcheck.sql

Fix any issues as required based on hcheck spool output

2) Apply EBS Patches as part for Pre-requisites.

Apply all these patches. If already applied any patches, ignore them.

• Patch 8796558
• Patch 9239090 - Oracle E-Business Suite 12.1.3.
• Patch 23569686 - R12.AD.B.delta.8.
• Patch 27135427 - R12.TXK.B.delta.4.
• Patch 30033914 - 19c interoperability patch for Release 12.1.3.
• Patch 27102203 - Prerequisite patch for 28613638.
• Patch 28613638
• Patch 28685719
• Patch 29178111
• Patch 29583055
• Patch 29905536
• Patch 30370150
• Patch 30601878
• Patch 30844256
• Patch 31209544
• Patch 31527189
• Patch 32141931
• Patch 31867474
• Patch 33798766

Enable Maintenance Mode:

Stop the Application First.

cd $ADMIN_SCRIPTS_HOME

adstpall.sh apps/apps

adadmin

Enable Maintainance Mode

or use to enable maintenance mode

sqlplus apps/appspassword

SQL>@$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE

Copy adgrants.sql from Patch 30033914: 19c interoperability patch for Release 12.1.3 on Database Node and run it:

cd $ORACLE_HOME/appsutil/admin

scp oracle@ApplicationIP:/app/preupgpatching/30033914/admin/adgrants.sql .

sqlplus /nolog @adgrants.sql APPS

cd /app/preupgpatching/30033914

adpatch

Apply all other prerequisite patches on EBS 12.1.3 mentioned above, skip if already applied.

cd /app/preupgpatching/28613638

adpatch

cd /app/preupgpatching/29178111

adpatch

cd /app/preupgpatching/29414243

adpatch

cd /app/preupgpatching/29583055

adpatch

cd /app/preupgpatching/30370150

adpatch

cd /app/preupgpatching/30601878

adpatch

cd /app/preupgpatching/31209544

adpatch

cd /app/preupgpatching/31406810

adpatch

cd /app/preupgpatching/31470349

adpatch

Note: 

I created default file to apply the patches non interactively.

adpatch defaultsfile=$APPL_TOP/admin/PROD/my_def.txt

adpatch defaultsfile=$APPL_TOP/admin/PROD/my_def.txt \

logfile=adpatch.log \

patchtop=/home/applprod/DB_UPGRADE/patches/30033914 \

driver=u30033914.drv \

workers=6 \

interactive=no

Create Appsutill.zip on APPS node:

perl $AD_TOP/bin/admkappsutil.pl

Copy latest appsutil from Apps Node to Database Node 11g and run autoconfig:

cd $ORACLE_HOME

cp -r appsutil appsutil_orig

unzip -o appsutil.zip

sh adautocfg.sh

Disable Maintenance Mode on Application Node:

adadmin

or use to disable maintenance mode

sqlplus apps/appspassword

SQL>@$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

Run preclone on Application Node and Take Backup:

cd $ADMIN_SCRIPTS_HOME

perl adpreclone.pl appsTier

Take the Application Backup.

Validate the patched once applied.

Select bug_number,creation_date from apps.ad_bugs where bug_number in ('28292585','8796558','9239090','23569686','27135427','30033914','27102203','28613638','28685719','29178111','29414243','29583055','29905536','30370150','30601878','31209544','31406810','31470349','30844256','31527189','32141931','31867474','33798766');

3) Apply Patch 6400501 - Not needed in my environment as it was already applied

4) Set case sensitive parameter to false at database level

Check SEC_CASE_SENSITIVE_LOGON parameter is database is set to false. If not set then, please set it to false.

SQL> show parameter case

NAME                                 TYPE        VALUE

------------------------------------ ----------- ----------------------sec_case_sensitive_logon             boolean     FALSE

5)  Create New Target Oracle Database 19c DB System on Oracle Cloud

11g DB Name: PROD

19c DB Name: PRODCDB

19c DB System hostname: funocidb

1. Login to the Oracle Cloud Infrastructure Console using your Oracle Cloud Infrastructure credentials.
   
   
2. Select the menu at the upper left corner of the page. Then under Oracle Database, select Oracle Base Database Service to display the Database Cloud Service console.
   
   
3. Click Create DB System to create a DB system with the values indicated. For more details on each of the fields refer to "To create a DB system".
   1. Compartment: Select the same compartment as the source DB System.
   2. Display Name: The name for the new DB System deployment.
   3. Availability Domain: The availability domain (within the region) in which the DB system will reside.
   4. Shape Type: Virtual Machine
   5. Shape: The shape to use to launch the DB system.
      • The recommendation for Oracle E-Business Suite is to use a minimum of VM.Standard2.x shape for the OCI DB System.
      • OCI DB System Shapes are available from the "VM Shapes."
      • Consider the OCPU, memory, local disk, and network bandwidth requirements when selecting a shape to the meet the system needs.
   6. Total Node Count: The number of nodes in the DB system. Enter the same number of nodes as the source system.
   7. Select Software Edition to the same as the source DB System.
   8. Storage Management Software: Choose "Oracle Grid Infrastructure".
   9. Available Storage Size (GB): Enter a valid value based on the source DB System size.
   10. Total Storage Size (GB): Total storage includes available storage plus recovery logs. This value will be selected for you based on the available storage size. For more details refer to "Storage Options for Virtual Machine DB Systems".
   11. Cluster Name: This is an optional name. This field is only present when "Total Node Count=2" (such as for a 2-Node DB System).
   12. SSH Public Key: Specify the same public key used to connect to the source DB System.
   13. License Type: The type of license you want to use for the DB System. Your choice affects metering for billing.
   14. Virtual Cloud Network: The VCN must match the source DB System.
   15. Client Subnet: The database tier subnet must match the source DB System.
   16. Hostname Prefix: The host name for a DB System must begin with an alphabetic character, be no longer than 16 characters, and can contain only alphanumeric characters and hyphens (-). Also, the host name must be unique within the subnet. Otherwise, the DB System will fail to provision.
   17. Host Domain Name: The domain name of the DB system, populated as <db_subnet_name>.<VCN_Name>.oraclevcn.com format. This cannot be changed.
   18. Host and Domain URL: Combines the host and domain names to display the fully qualified domain name (FQDN) for the database.
       
       
4. Continue with creation of the DB System by clicking Next.
1. Database Name: The name for the database.
2. Database Image, click Change Database Image
3. Select Oracle Provided Database Software Images,
4. Under Database Version, select the check box DISPLAY ALL AVAILABLE VERSIONS.
5. In the Database Version drop-down list, select 19.23.0.0
   
6. PDB name: (Required) The name of the default pluggable database (PDB). As part of an Oracle Database 19c deployment, a PDB name is mandatory, but the actual pluggable database created at this point will not be used by Oracle E-Business Suite. Therefore, enter a dummy value such as "DUMMYPDB." It will be removed later in this section.
7. Database Admin Password: The password for Oracle Database administrative users.
8. Automatic Backup: Select the check box if you want to enable automatic incremental backups for this database.
9. Click Show Advanced Options:
   1. Character Set: The character set for the database. This value must match the value of the on-premises Oracle E-Business Suite database.
   2. National Character Set: The national character set for the database. This value must match the value of the source Oracle E-Business Suite database.
10. Tags: Optionally, you can apply tags.
    
    
5. Click Create DB System.

Update the ssh Configurations

Login Root USER
sudo su -

vi /etc/ssh/sshd_config
Add following line:
AllowUsers oracle opc

vi /etc/ssh/sshd_config
modify (or add, if necessary) the settings shown

# To allow multi-threaded transfer of files.
MaxStartups 100
# To prevent client timeout.
ClientAliveInterval 100
ClientAliveCountMax 99999

Now run following with ROOT user
/sbin/service sshd restart

Login as Oracle User and Create Temporary environment file for 19c Database:

vi /home/oracle/19c.env

export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

export ORACLE_SID= PRODCDB

export PATH=$PATH:/u01/app/oracle/product/19.0.0.0/dbhome_1/bin

export LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0.0/dbhome_1/lib

export ORACLE_UNQNAME=PRODCDB_cl

export TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin

Create an nls directory using the following commands on all database nodes.

. /home/oracle/19c.env

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/nls/data/old/cr9idata.pl

Set the ORA_NLS10 environment variable in the cluster registry by running the following command:

$ srvctl setenv database -d $ORACLE_UNQNAME -t ORA_NLS10=$ORACLE_HOME/nls/data/9idata

Create a shared wallet location for the source 11g wallet files on the target DB System:

$ mkdir -p /opt/oracle/dcs/commonstore/wallets/tde/<source 11g $ORACLE_UNQNAME>

Note: The <source 11g $ORACLE_UNQNAME> directory name must be created with matching case, as its environment variable is used for the TDE ENCRYPTED_WALLET_LOCATION in the sqlnet.ora.

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

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

Set SGA and PGA to avoid memory potential pressure.

$ sqlplus "/ as sysdba"

SQL> show parameter sga_target;

SQL> show parameter pga_aggregate_target;

SQL> alter system set sga_target=3G scope=SPFILE;

SQL> alter system set pga_aggregate_target=1G scope=SPFILE;

During the 19c service creation, we created a default pluggable database called "DUMMYPDB". As this PDB is not required,delete it using the OCI Console:

1. Log in to the Oracle Cloud Infrastructure Console using your Oracle Cloud Infrastructure credentials.
2. Open the navigation menu. Select Oracle Database, then select Oracle Base Database.
3. Select your Compartment. A list of DB systems is displayed.
4. In the list of DB systems, find the DB system containing the PDB you want to delete. Click the DB system name to display details about it.
5. In the list of databases, find the database containing the PDB you want to delete. Click the database name to display details about it.
6. In the Resources section of the page, click Pluggable Databases.
7. In the list of PDBs, find the PDB you want to delete. Click the "DUMMYPDB" name to display details about it.
8. From the PDB details page, click More actions, and then click Delete.
9. In the Delete PDB dialog, click Delete PDB to confirm.

or  Use below command after connecting to the CDB

alter pluggable database DUMMYPDB close instances=ALL;

drop pluggable database DUMMYPDB including datafiles;

Stop the CDB:

echo $ORACLE_UNQNAME

PRODCDB_cl

srvctl stop database -d PRODCDB_cl

Enable OLAP option for 19c DB System:

cd $ORACLE_HOME/rdbms/lib

chopt enable olap

Start the CDB:

echo $ORACLE_UNQNAME  

PRODCDB_cl  

  

srvctl start database -d PRODCDB_cl

Install required OLAP schema object:

sqlplus / as sysdba 

@?/olap/admin/olap.sql SYSAUX TEMP;

Only need if we applied the DST patches.

Upgrade the time zone 19c DB System(Not Needed in my Environment) :

cd $ORACLE_HOME/rdbms/admin  

sqlplus / as sysdba  

@utltz_countstar.sql  

  

spool utltz_upg_check.log  

@utltz_upg_check.sql  

spool off  

  

spool utltz_upg_apply.log  

@utltz_upg_apply.sql  

spool off

Run datapatch on the CDB:

. /home/oracle/19c.env  

$ORACLE_HOME/OPatch/datapatch

Copy the orai18n.jar fileRun the following command to copy the orai18n.jar file to the $ORACLE_HOME/jdk/jre/lib/ext directory.

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

Shutdown the CDB:

echo $ORACLE_UNQNAME  

srvctl stop database -d PRODCDB_cl

Copy the appsutil.zip directory from the application node created in previous steps after applying application patches to the DBCS 19c home and unzip.

unzip -o appsutil.zip

6) Prepare 11g Database for Upgrade

Shut down the applications server processes on each application tier server node:

$ adstpall.sh

On the database tier server node, source the Oracle Database 11g non-CDB environment file:

$. $ORACLE_HOME/<SID>_<hostname>.env

Create an initialization parameter setup file containing the non-default, customized initialization parameters from the source database along with essential Oracle E-Business Suite specific Oracle Database 19c parameters.

Run the following command to create a <DB_NAME>_initparam.sql file in the 

$ cd $ORACLE_HOME/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/11.2.0.4/dbhome_1

$ export ORACLE_SID=PROD

$ cd $ORACLE_HOME/appsutil/bin

$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/u01/app/oracle/product/11.2.0.4/dbhome_1 \

-outdir=/u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log -appsuser=apps \

-dbsid=PROD -skipdbshutdown=yes

Copy the $ORACLE_HOME/dbs/<DB_NAME>_initparam.sql file to <19c ORACLE_HOME>/dbs directory on the target Oracle 19c DB System.

Use scp command or any other method which is comfortable.

7) Prepare 19c Home for 11g Upgrade to 19c before migration on DBCS

Install the 19c Pre-requisites on same server where 11g DB home is already present.

yum install -y oracle-database-preinstall-19c

yum install java

Take gold copy from DBCS and use to 19c setup on premises. I will not need to apply any patches as it is already applied to DBCS Home.

Use below post on to create a gold image and use to build a 19c home.

Creating a Gold Image of Oracle Database Home and using it for Cloning Oracle Home

Once 19c is ready on-premise

Create an temporary environment file.

cat 19cdb.env
export ORACLE_HOME=/u01/app/oracle/product/19.0.0
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin
export ORACLE_BASE=/u01/app/oracle
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PERL5LIB=$ORACLE_HOME/perl/lib/5.28.1:$ORACLE_HOME/perl/lib/site_perl/5.28.1

Check db patches on 19c

[oracle@fundb OPatch]$ ./opatch lspatches
36195566;JDK BUNDLE PATCH 19.0.0.0.240416
36459041;MERGE ON DATABASE RU 19.23.0.0.0 OF 34697081 36294695
36199232;OJVM RELEASE UPDATE: 19.23.0.0.240416 (36199232)
36260537;DSTV43 UPDATE - TZDATA2024A - NEED OJVM FIX
36240578;OCW RELEASE UPDATE 19.23.0.0.0 (36240578)
36233263;Database Release Update : 19.23.0.0.240416 (36233263)
OPatch succeeded.

8) UTL_FILE_DIR Setup

Store the UTL_FILE_DIR parameter values on 11g DB System

On 11g DB run the below commands

Run the txkCfgUtlfileDir.pl script in getUtlFileDir mode using the following command:

Source 11g home

$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=<DB Context File> \

-oraclehome=<11g/12c ORACLE_HOME> -outdir=<Output/Log location> \

-upgradedhome=<19c ORACLE_HOME> -mode=getUtlFileDir -servicetype=onpremise|opc

[oracle@fundb 19.0.0]$ pwd

/u01/app/oracle/product/19.0.0

[oracle@fundb 19.0.0]$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/PROD_fundb.xml \

-oraclehome=/u01/app/oracle/product/11.2.0.4/dbhome_1 -outdir=/u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log \

-upgradedhome=/u01/app/oracle/product/19.0.0 -mode=getUtlFileDir -servicetype=onpremise

Enter the APPS Password:

*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS

*** Log File = /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log/TXK_UTIL_DIR_Sat_Jul_6_12_24_50_2024/txkCfgUtlfileDir.log

Program :  started @ Sat Jul  6 12:24:56 2024

*** Log File = /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log/TXK_UTIL_DIR_Sat_Jul_6_12_24_50_2024/txkCfgUtlfileDir.log

Script Name    : txkCfgUtlfileDir.pl

Script Version : 120.0.12010000.10

Started        : Sat Jul  6 12:24:56 +08 2024

Log File       : /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/log/TXK_UTIL_DIR_Sat_Jul_6_12_24_50_2024/txkCfgUtlfileDir.log

Context file: /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/PROD_fundb.xml exists.

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

Successfully generated the below file with UTL_FILE_DIR content:

/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/PROD_utlfiledir.txt

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

Completed        : Sat Jul  6 12:24:56 +08 2024

Successfully Completed the script

ERRORCODE = 0 ERRORCODE_END

Transfer output file to 19c DB Home:

cp /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/DEV_utlfiledir.txt /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs

Create appsutil and url directories on 19c DB System:

cd $ORACLE_HOME/

unzip -o appsutil.zip

mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/outbound/<11gSID>_<19c DB SYSTEM Hostname>

mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/outbound/DEV_funocidb

vi /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/UPG_utlfiledir.txt

/u01/app/oracle/product/19.0.0.0/temp/UPG

/u01/app/oracle/product/19.0.0.0/temp/UPG

/u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/outbound/DEV_funocidb

/u01/app/oracle/product/19.0.0.0/temp/UPG

wq!

mkdir -p /u01/app/oracle/product/19.0.0.0/temp/UPG

chown -R oracle:oinstall /u01/app/oracle/product/19.0.0.0/temp

Perform the Following on APPS NODE

. /app/DEV/apps/apps_st/appl/APPSDEV_funapps.env

cp /app/DEV/inst/apps/DEV_funapps/appl/admin/DEV_funapps.xml /app/DEV/inst/apps/DEV_funapps/appl/admin/DEV_funapps.xml_bkp

vi $CONTEXT_FILE

Press escape

s_applptmp

search

change path to  /u01/app/oracle/product/19.0.0.0/temp/DEV

s_appltmp

search

change path to  /u01/app/oracle/product/19.0.0.0/temp/DEV

escape

:wq!

mkdir -p /u01/app/oracle/product/19.0.0.0/temp/DEV

chown -R oracle:oinstall /u01/app/oracle/product/19.0.0.0/temp

Run Autoconfig on Apps Node:

cd $ADMIN_SCRIPTS_HOME/

sh adautocfg.sh

AutoConfig completed successfully.

Drop SYS.ENABLED$INDEXES on 11g

 

If the SYS.ENABLED$INDEXES table exists, use SQL*Plus to connect to the database as SYSDBA and run the following command to drop it:

$ sqlplus "/ as sysdba"

SQL> drop table SYS.ENABLED$INDEXES;

SQL> exit

As sysdba run below on 11g

SQL> PURGE DBA_RECYCLEBIN;

SQL> alter system set local_listener='';

System altered.

SQL> alter system set  olap_page_pool_size=0 scope=spfile;

System altered.

SQL> alter system set event='10946 trace name context forever, level 8454144' scope=spfile;

System altered.

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

alter system set pga_aggregate_target=10G;

System altered.

Set the SGA_TARGET initialization parameter to at least 2G.

Remove the MGDSYS schema 11g

Use SQL*Plus to connect to the database as SYSDBA and run the $ORACLE_HOME/md/admin/catnomgdidcode.sql script. This drops the MGDSYS schema.

$ sqlplus "/ as sysdba" @?/md/admin/catnomgdidcode.sql

Copy the pre-upgrade tool from the target Oracle Database 19c DB System $ORACLE_HOME/rdbms/admin/preupgrade.jar to a temporary location on this source Oracle Database 11g DB System.

Run the pre-upgrade tool on the Source Oracle Database11g DB System as follows:

$ java -jar <temp location>/preupgrade.jar FILE TEXT

$ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/rdbms/admin/preupgrade.jar TERMINAL TEXT

This command creates the following files:

    <ORACLE_HOME>/cfgtoollogs/<SID>/preupgrade/preupgrade.log 

    <ORACLE_HOME>/cfgtoollogs/<SID>/preupgrade/preupgrade_fixups.sql

    <ORACLE_HOME>/cfgtoollogs/<SID>/preupgrade/postupgrade_fixups.sql

Execute the preupgrade_fixups.sql and implement all the recommendations before continuing with the upgrade. This includes setting initialization parameters, manually upgrading or removing some components and removing underscore parameters that have been deprecated in Oracle Database 19c.

$ sqlplus "/ as sysdba"

SQL> @<ORACLE_HOME>/cfgtoollogs/<SID>/preupgrade/preupgrade_fixups.sql

Remove hidden parameter from 11g

SQL> alter system reset "_system_trig_enabled";

System altered.

SQL>  alter system reset "_sort_elimination_cost_ratio";

System altered.

SQL> alter system reset "_b_tree_bitmap_plans";

System altered.

SQL> alter system reset "_fast_full_scan_enabled";

System altered.

SQL> alter system reset "_like_with_bind_as_equality";

System altered.

SQL> alter system reset "_optimizer_autostats_job";

System altered.

SQL> alter system reset "_trace_files_public";

System altered.

Gather stale data dictionary statistics prior to database upgrade in off-peak time using:

        EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

Remove OLAP Catalog

 $ sqlplus "/ as sysdba"

SQL> @<ORACLE_HOME>/olap/admin/catnoamd.sql

Copy the  post upgrade script     <ORACLE_HOME>/cfgtoollogs/<SID>/preupgrade/postupgrade_fixups.sql file on 19c as it will needed after upgrade.

9) Upgrade 11g to 19c Non Container Database

Validate entry in /etc/oratab

Entry in etc/oratab file

PROD:/u01/app/oracle/product/11.2.0.4/dbhome_1:N

From 19c Home Launch the Database upgrade Assistant. Remember the 11g NON CDB will be upgraded to 19c NON CDB Database

$ORACLE_HOME/bin/dbua -keepEvents

Post Upgrade Tasks

Run the post upgrade fix up script.

Now we will use 19c db home

[oracle@fundb ~]$ . oraenv

ORACLE_SID = [oracle] ? PROD

The Oracle base remains unchanged with value /u01/app/oracle

Execute the script connecting to sysdba.

SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/PROD/preupgrade/postupgrade_fixups.sql

Session altered.

Once completed, Check the component version and fix any issues.

Timezone

 SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID

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

timezlrg_43.dat              43          0

Component Version

SQL> set line 200;

set pagesize 9999;

col COMP_ID format a15;

col COMP_NAME format a40;

col STATUS format a15;

select COMP_ID,COMP_NAME,STATUS,version from dba_registry;

COMP_ID         COMP_NAME                      STATUS          VERSION

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

CATALOG         Oracle Database Catalog Views  VALID           19.0.0.0.0

CATPROC         Oracle Database Packages and T VALID           19.0.0.0.0

                ypes

JAVAVM          JServer JAVA Virtual Machine   VALID           19.0.0.0.0

CATJAVA         Oracle Database Java Packages  VALID           19.0.0.0.0

XML             Oracle XDK                     VALID           19.0.0.0.0

RAC             Oracle Real Application Cluste OPTION OFF      19.0.0.0.0

                rs

APS             OLAP Analytic Workspace        VALID           19.0.0.0.0

ORDIM           Oracle Multimedia              VALID           19.0.0.0.0

SDO             Spatial                        INVALID         19.0.0.0.0

CONTEXT         Oracle Text                    VALID           19.0.0.0.0

XOQ             Oracle OLAP API                VALID           19.0.0.0.0

APEX            Oracle Application Express     INVALID         3.2.1.00.12

XDB             Oracle XML Database            VALID           19.0.0.0.0

13 rows selected.

I need to fix the spatial and Oracle Application Express.

Connect to database As sysdba 

SQL> alter system set SEC_CASE_SENSITIVE_LOGON=false;

System altered.

SQL> alter system set compatible='19.0.0' scope=spfile;

System altered.

Run Datapatch

  cd /u01/app/oracle/product/19.0.0/OPatch

 ./datapatch

SQL Patching tool version 19.23.0.0.0 Production on Sat Sep 21 08:55:34 2024

Copyright (c) 2012, 2024, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_795_2024_09_21_08_55_34/sqlpatch_invocation.log

Connecting to database...OK

Gathering database info...done

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of interim SQL patches:

Interim patch 36199232 (OJVM RELEASE UPDATE: 19.23.0.0.240416 (36199232)):

  Binary registry: Installed

  SQL registry: Applied successfully on 21-SEP-24 05.07.40.296503 AM

Current state of release update SQL patches:

  Binary registry:

    19.23.0.0.0 Release_Update 240406004238: Installed

  SQL registry:

    Applied 19.23.0.0.0 Release_Update 240406004238 successfully on 21-SEP-24 05.07.40.274434 AM

Adding patches to installation queue and performing prereq checks...done

Installation queue:

  No interim patches need to be rolled back

  No release update patches need to be installed

  No interim patches need to be applied

SQL Patching tool complete on Sat Sep 21 08:55:48 2024

Restart Database

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 3.4360E+10 bytes

Fixed Size                 13693168 bytes

Variable Size            2348810240 bytes

Database Buffers         3.1944E+10 bytes

Redo Buffers               53411840 bytes

Database mounted.

Database opened.

Copy the encryption key to 19c

[oracle@fundb ~]$ mkdir -p /u01/app/oracle/admin/PROD/wallet/

[oracle@fundb ~]$ cp /u01/app/oracle/product/11.2.0.4/dbhome_1/admin/PROD/wallet/ewallet.p12  /u01/app/oracle/admin/PROD/wallet/

sqlplus '/as sysdba'

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "anything";

Fixing Spatial Component

Connect as SYSDBA and execute below

@$ORACLE_HOME/md/admin/sdopatch.sql

set serveroutput on
exec sys.VALIDATE_SDO();

Restart the database

Fixing Apex Component

cd /u01/app/oracle/product/19.0.0/apex

Connect as SYSDBA and execute below

 

drop user APEX_050100 cascade;

@apexins.sql SYSAUX SYSAUX TEMP /i/

Validate the Components now

 set line 200;

set pagesize 9999;

col COMP_ID format a15;

col COMP_NAME format a40;

col STATUS format a15;

select COMP_ID,COMP_NAME,STATUS,version from dba_registry;

COMP_ID         COMP_NAME                                STATUS          VERSION

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

CATALOG         Oracle Database Catalog Views            VALID           19.0.0.0.0

CATPROC         Oracle Database Packages and Types       VALID           19.0.0.0.0

JAVAVM          JServer JAVA Virtual Machine             VALID           19.0.0.0.0

CATJAVA         Oracle Database Java Packages            VALID           19.0.0.0.0

XML             Oracle XDK                               VALID           19.0.0.0.0

RAC             Oracle Real Application Clusters         OPTION OFF      19.0.0.0.0

APS             OLAP Analytic Workspace                  VALID           19.0.0.0.0

ORDIM           Oracle Multimedia                        VALID           19.0.0.0.0

SDO             Spatial                                  UPGRADED        19.0.0.0.0

CONTEXT         Oracle Text                              VALID           19.0.0.0.0

XOQ             Oracle OLAP API                          VALID           19.0.0.0.0

XDB             Oracle XML Database                      VALID           19.0.0.0.0

APEX            Oracle Application Express               VALID           18.2.0.00.12

13 rows selected.

Compile Invalid Objects

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

10) Moving the NON-CDB 19c DB to DBCS 19c Home

Copy the wallet file to DBCS 19c location(/opt/oracle/dcs/commonstore/wallets/tde/PROD/) created earlier 

11g file

/u01/app/oracle/product/11.2.0.4/dbhome_1/admin/PROD/wallet/ewallet.p12

On DBCS 19c

cp /tmp/ewallet.p12 /opt/oracle/dcs/commonstore/wallets/tde/PROD/

I will move the data from Source 19c NON CDB to DBCS 19c using active duplicate. 

On OCI DBCS system as oracle user add below in the listener.ora file

Shut down listener running from grid home.

[oracle@ebsdboci admin]$ vi listener.ora

PROD_CL =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.1)(PORT = 1522))

    )

  )

SID_LIST_PROD_CL =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME= /u01/app/oracle/product/19.0.0.0/dbhome_1)

      (SID_NAME = PROD)

    )

  )

Start listener

[oracle@ebsdboci admin]$ lsnrctl start PROD_CL

Onpremises VM

Copy the tnsnames.ora ,sqlnet.ora and listener.ora from 11g home to 19c home on-prem server.

if static listener registration is not present then add and start listener

PROD =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.7)(PORT = 1521))

    )

  )

SID_LIST_PROD =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/dbhome_1)

      (SID_NAME = PROD)

    )

  )

Add the below entries on tnsnames.ora on both sides

SOURCE=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.7)(PORT = 1521)))(CONNECT_DATA =(ORACLE_SID = PROD)))

TARGET_CL=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.1)(PORT =1522)))(CONNECT_DATA =(ORACLE_SID = PROD)))

Validate tnsping is wokring on both tns entries.

On 19c on premises create password file and copy to targte DBCS ORACLE_HOME/dbsid

orapwd  file=orapwPROD password=oracle123

In case we are changing SID On target DBCS 

copy the password file as orapwPROD to orapwNEWSID

create parameter file

vi initPROD.ora

Add below entry

DB_NAME=PROD

DB_UNIQUE_NAME=PROD_CL

DB_BLOCK_SIZE=8192

export ORACLE_SID=PROD

export ORACLE_UNQNAME=PROD_CL

Copy wallet from source to target.

cp ewallet.p12 /opt/oracle/dcs/commonstore/wallets/PROD_CL/tde

Startup nomount

SQL> startup nomount

ORACLE instance started.

Total System Global Area  436206376 bytes

Fixed Size                  8940328 bytes

Variable Size             285212672 bytes

Database Buffers          134217728 bytes

Redo Buffers                7835648 bytes

SQL>  ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "anything";

System altered.

SQL> administer key management create LOCAL auto_login keystore from keystore '/opt/oracle/dcs/commonstore/wallets/PROD_CL/tde/' identified by anything;  --> .sso file will be created.

SQL> select * from  v$encryption_wallet;

WRL_TYPE

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

WRL_PARAMETER

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

STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC

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

    CON_ID

----------

FILE

/opt/oracle/dcs/commonstore/wallets/PROD_CL/tde/

OPEN                           PASSWORD             SINGLE    NONE     NO

         0

On DBCS 

Create the ASM directory structure

$ sudo su - grid

$ asmcmd

ASMCMD> mkdir +DATA/PRODCDB_DC/PROD

ASMCMD> mkdir +DATA/PRODCDB_DC/PROD/DATAFILE

ASMCMD> mkdir +RECO/PRODCDB_DC/PROD

ASMCMD> mkdir +RECO/PRODCDB_DC/PROD/ONLINELOG

vi rman_duplicate.cmd

 

set encryption off;

RUN {

  ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE DISK;

  ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE DISK;

  ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE DISK;

  ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE DISK;

  ALLOCATE AUXILIARY CHANNEL c5 DEVICE TYPE DISK;

  ALLOCATE AUXILIARY CHANNEL c6 DEVICE TYPE DISK;

  ALLOCATE AUXILIARY CHANNEL c7 DEVICE TYPE DISK;

  ALLOCATE AUXILIARY CHANNEL c8 DEVICE TYPE DISK;

  DUPLICATE TARGET DATABASE TO PROD

  FROM ACTIVE DATABASE

spfile

  SET CONTROL_FILES='+DATA','+RECO'

  set db_unique_name='PROD'

  set db_file_name_convert='/u02/PROD/db/data','+DATA/PRODCDB_DC/PROD/datafile','/u03/PROD/db/data','+DATA/PRODCDB_DC/PROD/datafile','/u04/PROD/db/data','+DATA/PRODCDB_DC/PROD/datafile'

  set log_file_name_convert='/u03/PROD/db/data','+RECO/PRODCDB_DC/PROD/onlinelog','/u02/PROD/db/data','+RECO/PRODCDB_DC/PROD/onlinelog'

  set diagnostic_dest='/u01/app/oracle'

  set log_archive_dest_1='LOCATION=/u01/app/oracle'

  NOFILENAMECHECK;

}

export TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin

Validate connectivity

sqlplus /nolog

connect sys/oracle123@SOURCE AS SYSDBA

connect sys/oracle123@TARGET_CL AS SYSDBA

Run RMAN command to duplicate.

rman target sys/oracle123@SOURCE auxiliary sys/oracle123@TARGET_CL cmdfile=rman_duplicate.cmd 

Validate Database after duplicate finishes

SQL> select name from v$database;

NAME

---------

PROD

11) Move the datafiles to OMF format for 19c NON CDB which we moved to DBCS.

If we miss this step then PDB conversion will fail.

Identify all the 19c NON_CDB  datafile file_id and file_name details as follows:

SQL> select file_id, file_name from dba_data_files;

Generate the MOVE DATAFILE commands by running the following SQL:

SQL>

set line 140

set pagesize 2000

set heading off

spool datafile_loc.sql

SELECT 'ALTER DATABASE MOVE DATAFILE ' || file_id || ' TO ''' || '+DATA' || ''';' FROM dba_data_files;

spool off;

The target location for each datafile will be determined by Oracle Managed Files (OMF)

Run all the ALTER DATABASE MOVE DATAFILE commands generated in the previous step

sqlplus '/as sysdba'

@datafile_loc.sql

12) Additional steps to be performed on NON container 19c DB

export ORACLE_SID=PROD

export ORACLE_UNQNAME=PROD_CL

Add the following entry to the Oracle 19c non-CDB $ORACLE_HOME/network/admin/sqlnet.ora file on each of the Oracle RAC nodes:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

Copy latest adgrants.sql script and run on 19c non container database using the following command:

Take the script from application server.

$ sqlplus / as sysdba @adgrants.sql apps

Grant CREATE PROCEDURE privilege to CTXSYS by running adctxprv.sql. Connect to the database as APPS and run the script using the following command:

Take the script from application server.

$ sqlplus apps/[APPS password] @$ORACLE_HOME/admin/adctxprv.sql [SYSTEM password] CTXSYS

sqlplus apps/apps @adctxprv.sql oracle123 CTXSYS

Connect to the database as SYSDBA and run the $ORACLE_HOME/rdbms/admin/utlrp.sql script to compile invalid objects:

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

Connect to the database as SYSDBA and install the Object Label Security (OLS) component by running the catols.sql script as follows:

$ sqlplus "/ as sysdba"

SQL> @$ORACLE_HOME/rdbms/admin/catols.sql

Run the catmac.sql script to install the Database Vault (DV) component:

$ sqlplus "/ as sysdba"

SQL> @$ORACLE_HOME/rdbms/admin/catmac.sql SYSTEM <TEMP tablespace>

@$ORACLE_HOME/rdbms/admin/catmac.sql SYSTEM TEMP1

Run the following to grant DATASTORE ACCESS to PUBLIC:

$ sqlplus "/ as sysdba"

SQL> grant text datastore access to public;

Run adstats.sql to gather statistics for the SYS schema:

Take the script from application server.

$ sqlplus "/ as sysdba"

SQL> alter system enable restricted session;

SQL> @adstats.sql

$ sqlplus "/ as sysdba"

SQL> alter system disable restricted session;

SQL> exit;

Export the Encryption Key

On the upgraded non-CDB 19c database, connect via SQL*Plus as SYSDBA:

$ sqlplus '/ as sysdba'

To export the non-CDB wallet keys, run the following SQL:

SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "<secret key or password>" to '<Export file name, for example -/tmp/tdepdb.exp>' FORCE KEYSTORE IDENTIFIED BY "<source 11g wallet password>";

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "anything" to '/tmp/tdepdb.exp' FORCE KEYSTORE IDENTIFIED BY "anything";

13) Plug in Oracle Database 19c Non-CDB Database as a PDB

Ensure that TEMP table space contains at least 5GB available space:

$ export ORACLE_SID=<non-CDB_SID>

$ export ORACLE_UNQNAME=<non-CDB_SID uniquename>

$ sqlplus '/ as sysdba'

alter system set compatible='19.0.0.0' scope=spfile; --> based on value in CDB database.

SQL>

col TABLESPACE_NAME format a16

col TABLESPACE_SIZE format 999,999,999,999

col FREE_SPACE format 999,999,999,999

select TABLESPACE_NAME, TABLESPACE_SIZE, FREE_SPACE

from DBA_TEMP_FREE_SPACE;

Shut down and restart the non-CDB database in READ ONLY mode:

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database open read only;

Note: It is important the $ORACLE_UNQNAME is set correctly to the <$ORACLE_UNQNAME> with the correct case as this environment variable is part of the TDE ENCRYPTED_WALLET_LOCATION in the sqlnet.ora

Create a descriptor file $ORACLE_HOME/dbs/<PDB name>_PDBDesc.xml for the non-CDB that is to be plugged in.

SQL> BEGIN

DBMS_PDB.DESCRIBE(pdb_descr_file => '<19c Oracle Home>/dbs/<PDB name>_PDBDesc.xml');

end;

/

Note: The <PDB name> is the non-CDB database name. The non-CDB database must be open as READ ONLY when creating the PDB descriptor file.

Shut down the upgraded non-CDB database:

SQL> shutdown immediate;

The non-CDB database is now marked for plugging in as a PDB and it should not be opened as a normal database.

Update the initialization parameters on the Oracle 19c Container Database.

Set the CDB environment, mount the primary CDB instance and run <DB_NAME>_initparam.sql manually with sysdba privileges as per the following code. This file was created in 11g home and being used copied to DBCS $ORACLE_HOME/dbs

$ export ORACLE_HOME=<19c Oracle Home>

$ export ORACLE_SID=<CDB_SID>

$ export ORACLE_UNQNAME=<19c $ORACLE_UNQNAME>

$ export PATH=$ORACLE_HOME/bin:$PATH

$ sqlplus / as sysdba

SQL> startup nomount;

SQL> @$ORACLE_HOME/dbs/<DB_NAME>_initparam.sql

SQL> shutdown;

Restart the primary instance of the CDB for the initialization parameters to take effect.

Non-RAC:

$ srvctl start database -d PROD_CL

Run txkChkPDBCompatability.pl using the following commands. 

This will perform prerequisite checks prior to plugging in the database as a PDB, and report any error conditions you need to fix before you proceed to the plugin phase:

$ cd <19c Oracle Home>/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=<19c Oracle Home>

$ export ORACLE_SID=<19c CDB SID>

$ export ORACLE_UNQNAME=<19c $ORACLE_UNQNAME>

$ export TNS_ADMIN=<19c Oracle Home>/network/admin

$ perl $ORACLE_HOME/appsutil/bin/txkChkPDBCompatability.pl \

-dboraclehome=<19c Oracle Home> \

-outdir=<19c Oracle Home>/appsutil/log \

-cdbsid=<19c CDB SID> \

-pdbsid=<EBS Database name to be plugged in>

$ cd /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil

$ . ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0.0/dbhome_1

$ export ORACLE_SID=PRODCDB

$ export ORACLE_UNQNAME=PRODCDB_DC

$ export TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin

$ perl $ORACLE_HOME/appsutil/bin/txkChkPDBCompatability.pl \

-dboraclehome=/u01/app/oracle/product/19.0.0.0/dbhome_1 \

-outdir=/u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/log \

-cdbsid=PRODCDB \

-pdbsid=PROD

Set Local listener parameter in CDB 

alter system set local_listener =<node_name1>:<Grid Listener Port> ;

alter system set local_listener ='10.1.2.1:1521'

Plug the database into CDB

 mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/log

Run the script txkCreatePDB.pl 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

$ perl $ORACLE_HOME/appsutil/bin/txkCreatePDB.pl \

-dboraclehome=<19c Oracle Home> \

-outdir=<19c Oracle Home>/appsutil/log \

-dbuniquename=<CDB db_unique_name> \

-cdbsid=<CDB SID of the current RAC Node> \

-pdbsid=<EBS Database name to be plugged in> \

-noncdbdatadir=+DATA/<CDB db_unique_name>/<11g non-CDB name>/DATAFILE,+DATA/<CDB db_unique_name>/<11g non-CDB name>/TEMPFILE \

-pdbdatadir=+DATA/<CDB db_unique_name>/<11g non-CDB name>/DATAFILE,+DATA/<CDB db_unique_name>/<11g non-CDB name>/TEMPFILE \

-servicetype=dbsystem \

-istdeenabled=yes \

-promptsecretkey=yes \

-secretkeyfile=<export key file with full path created in Section 5.3 above> \

-keystoreloc=<19c CDB WALLET_LOCATION>

When prompted, enter the Oracle Database 19c CDB wallet password and the secret key password created earlier.

Enter the CDB wallet password: --> When we created. the DBCS same password should be by default if we have not changed.

Enter the secret key: 11g wallet password

If any issues are there then fix and rerun

14) Implement Autoconfig in Pluggable Database

If you are using the Grid listener, set the USE_SID_AS_SERVICE_<LISTENER_NAME>=ON parameter in the $GRID_HOME/network/admin/listener.ora file and restart the listener :

USE_SID_AS_SERVICE_<LISTENER_NAME>=ON

Set the environment for the CDB:

 

cd /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil

 . ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0.0/dbhome_1

 export ORACLE_SID=PRODCDB

 export ORACLE_UNQNAME=PRODCDB_DC

 export TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin

Add the following entry to the Oracle 19c $ORACLE_HOME/network/admin/sqlnet.ora file:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

As part of the migration from the UTL_FILE_DIR parameter, we're now using database objects for PL/SQL File I/O. All directories specified by these database objects must exist on the 19c Oracle DB System file system. Create them using the commands based on the following example:

$ mkdir -p <19c Oracle Home>/temp/<PDB NAME>

$ mkdir -p <19c ORACLE_HOME>/appsutil/outbound/<context name>

mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1/temp/PROD

mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/outbound/PROD_ebsdb

Run the following script to implement AutoConfig. 

When prompted, provide the APPS user password and SYSTEM user password for the CDB.

$ 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=<PDB Name> \

-appsuser=<apps user> \

-israc=<yes/no> \

-virtualhostname=<VIP Hostname> \

-dbport=<EBS DB port> \

-scanhostname=<Scan name> \

-scanport=<Scan Port> \

-servicetype=dbsystem

The scan name can be obtained by running the following command from the database Oracle Home:

$ srvctl config scan

perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl \

-dboraclehome=/u01/app/oracle/product/19.0.0.0/dbhome_1 \

-outdir=/u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/log \

-cdbname=PRODCDB \

-dbuniquename=PRODCDB_DC \

-cdbsid=PRODCDB \

-pdbsid=PROD \

-appsuser=apps \

-israc=no \

-dbport=1521 \

-servicetype=dbsystem

Run Autoconfig on Database Node and validate if it is successful

cd /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/scripts/PROD_ebsdb

sh adautocfg.sh

Modify the initialization parameters for the PDB(based on requirement)

alter system set pga_aggregate_target=12G;

connect to PDB and run below

 ALTER SYSTEM SET LOCAL_LISTENER='' scope=both;

Enable Archive mode in database

$ sqlplus / as sysdba

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> select log_mode from v$database;

LOG_MODE

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

ARCHIVELOG

SQL> alter database open;

Generate the MOVE DATAFILE commands by running the following SQL:

SQL>

set line 140

set pagesize 2000

set heading off

spool datafile_loc.sql

SELECT 'ALTER DATABASE MOVE DATAFILE ' || file_id || ' TO ''' || '+DATA' || ''';' FROM dba_data_files;

spool off;

This will move all the datafiles under CDB structure.

Setup utl dir paths

Connect to DB server

mkdir -p /u01/app/oracle/product/temp

vi  /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/PROD_utlfiledir.txt

/u01/app/oracle/product/temp

/u01/app/oracle/product/19.0.0.0/dbhome_1/temp/PROD

/u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/outbound/PROD_ebsdb

Source PDB environment file

perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \

-oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=setUtlFileDir -servicetype=opc

perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE \

-oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=syncUtlFileDir -servicetype=opc

15) Setup Application tier  to connect to DBCS 19c Database

As the user of the applications server node modify the $TNS_ADMIN/tnsnames.ora file to specify the CDB instance name. The following shows the format of the new TNS entry.

<TWO_TASK>==> Application two task value can be fetched from echo $TWO_TASK

<TWO_TASK> =

 (DESCRIPTION =

  (ADDRESS = (PROTOCOL=tcp)(HOST=<hostname>.<domain>)(PORT=<port number>))

  (CONNECT_DATA = (SERVICE_NAME=ebs_<PDB SID>)(INSTANCE_NAME=<CDB SID>))

 )

PROD =

 (DESCRIPTION =

  (ADDRESS = (PROTOCOL=tcp)(HOST=ebsdb.lab)(PORT=1521))

  (CONNECT_DATA = (SERVICE_NAME=ebs_PROD)(INSTANCE_NAME=PRODCDB))

 )

Update the following values in the context file of every Applications tier server node.

Variable Name Value

s_dbport New database port

s_dbhost        New Host Name

s_dbdomain      New Domain

s_db_serv_sid,s_dbSidLower,s_dbSid       New SID

s_apps_jdbc_connect_descriptor NULL (blank entry)

s_applptmp Directory (not /usr/tmp) defined in UTL_FILE_DIR(/u01/app/oracle/product/temp)

To identify the allowable directories for s_applptmp use, connect to the Oracle E-Business Suite database instance as the apps user and run the following query:

SQL> select value from apps.v$parameter2 where name='utl_file_dir';

mkdir -p /u01/app/oracle/product/temp on apps tier

Run AdConfig 

 $AD_TOP/bin/adconfig.sh contextfile=$CONTEXT_FILE

Compile all forms on application using ADADMIN

Now my DB is upgraded to 19c and moved to DB system on OCI with EBS 12.1 application.

Additional Post-Upgrade Tasks

Identify and encrypt all non-encrypted tablespaces in the CDB and PDB.

Connect to the CDB as SYSDBA and run the following query to identify non-encrypted tablespaces in the CDB:

$ sqlplus "/ as sysdba"

SQL> select TABLESPACE_NAME, CONTENTS, ENCRYPTED from DBA_TABLESPACES where upper(encrypted)='NO' order by 1;

Connect to the PDB database and run the same query to identify non-encrypted tablespaces in the PDB:

SQL> alter session set container = "<PDB Name>";

SQL> select TABLESPACE_NAME, CONTENTS, ENCRYPTED from DBA_TABLESPACES where upper(encrypted)='NO' order by 1;

You must now encrypt all non-encrypted tablespaces (other than TEMPORARY tablespaces) in the CDB and PDB identified in the queries that you ran in steps a and b above. For each tablespace, run a command like the following:

SQL> alter tablespace <tablespace_name> encryption online encrypt;

Reference: Oracle Document: ID:2758990.1

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment