Open in app

Sign in

Write

Sign in

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

Mohsin Ali
10 min readFeb 15, 2024

--

This is step by step document on how to migrate and upgrade Oracle Database 11g on Oracle Base Database Service (Oracle Enterprise Database Service) to a new DB System running Oracle Database 19c Multitenant Architecture with Oracle E-Business Suite (EBS) Release 12.1.3

Prerequisites:

Apply following patches on EBS 12.1.3 using adpatch.

1.  Patch 8796558 WMS SUPPORT FOR THE NEW RFID MODEL
2.  Patch 9239090 Oracle E-Business Suite 12.1.3.
3.  Patch 23569686 R12.AD.B.delta.8.
4.  Patch 27135427 R12.TXK.B.delta.4.
5.  Patch 30033914 19c interoperability patch for Release 12.1.3.
6.  Patch 27102203 Prerequisite patch for 28613638
7.  Patch 28613638 CDB-PDB19C: TRACKING BUG FOR EBS 12.1.3 CLONING CHANGES FOR DATABASE 19C
8.  Patch 28685719 APACHE FAILS TO START AFTER APPLYING 27135427
9.  Patch 29178111 CDB-PDB19C: TRACKING BUG FOR EBS 12.1.3 AUTOCONFIG CHANGES FOR DATABASE 19C
10. Patch 29583055 19C DB DE-SUPPORTING UTL_FILE
11. Patch 29905536 GRANT DBMS_SERVICE MISSING IN ADGRANTS.SQL - EBS 12.1.3
12. Patch 30370150 APPSST19C : ORA-01720: GRANT OPTION DOES NOT EXIST FOR 'SYS._BASE_USER'
13. Patch 30601878 TCH19C :: MERGE OF RECENT TXK FIXES REQUIRED FOR 19C
14. Patch 30844256 ENHANCEMENTS TO CDB/PDB CONFIGURATION SCRIPTS FOR OCI FLOWS
15. Patch 31209544 CONSOLIDATED PATCH FOR EBS 12.1 - 19C SUPPORT
16. Patch 31527189 TXKCFGUTLFILEDIR.PL FAILED FOR SETUTLFILEDIR ON 12CDB_PDB_EBS1213
17. Patch 32141931 EVENT_PARAM_VALUE WRONGLY INSTANTIATED IN S21X19C_INITPARAM.SQL
18. Patch 31867474 TXKCREATEPDB.PL SCRIPT HAS INCORRECT SYNTAX FOR CREATE PLUGGABLE DATABASE
19. Patch 33798766 TXKCREATEPDB.PL FAILED ON 19C EXADATA CLOUD SERVICES

Enable Maintainance Mode:

Stop the Application First.

. /app/UPG/apps/apps_st/appl/APPSUPG_upg-inst.env
cd $ADMIN_SCRIPTS_HOME
adstpall.sh apps/apps

. /app/UPG/apps/apps_st/appl/APPSUPG_upg-inst.env
adadmin
Enable Maintainance Mode 5>1

Copy adgrants.sql from Patch 23569686: R12.AD.B.delta.8 on Database Node and run it:

cd $ORACLE_HOME/appsutil
mkdir admin
cd admin
scp oracle@ApplicationIP:/app/preupgpatching/23569686/admin/adgrants.sql .
sqlplus /nolog @adgrants.sql APPS

Apply Patch 23569686: R12.AD.B.delta.8:

. /app/UPG/apps/apps_st/appl/APPSUPG_upg-inst.env
cd /app/preupgpatching/23569686
adpatch

adadmin
Compile APPS Schema 3>1

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

Apply Patch 30033914: 19c interoperability patch for Release 12.1.3 on APPS node:

. /app/UPG/apps/apps_st/appl/APPSUPG_upg-inst.env
cd /app/preupgpatching/30033914
adpatch

An error occurred while relinking application programs.
Continue as if it were successful [No] : Yes

Apply all prerequisite patches on EBS 12.1.3 mentioned above:

Apply all other prerequisite patches on EBS 12.1.3 mentioned above, skip the 
pathes which are already applied:

. /app/UPG/apps/apps_st/appl/APPSUPG_upg-inst.env
cd /app/preupgpatching/27102203
adpatch

cd /app/preupgpatching/28613638
adpatch

cd /app/preupgpatching/29178111
adpatch

cd /app/preupgpatching/29583055
adpatch

cd /app/preupgpatching/29905536
adpatch

cd /app/preupgpatching/30370150
adpatch

cd /app/preupgpatching/30601878
adpatch

cd /app/preupgpatching/30844256
adpatch

cd /app/preupgpatching/31209544
adpatch

cd /app/preupgpatching/31527189
adpatch

cd /app/preupgpatching/32141931
adpatch

cd /app/preupgpatching/31867474
adpatch

cd /app/preupgpatching/33798766
adpatch

Create Appsutill.zip on APPS node:

. /app/UPG/apps/apps_st/appl/APPSUPG_upg-inst.env
perl $AD_TOP/bin/admkappsutil.pl

Starting the generation of appsutil.zip
Log file located at /app/UPG/inst/apps/UPG_upg-inst/admin/log/MakeAppsUtil_05261001.log
output located at /app/UPG/inst/apps/UPG_upg-inst/admin/out/appsutil.zip
MakeAppsUtil completed successfully.

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

cd $ORACLE_HOME
scp oracle@ApplicationIP:/app/UPG/inst/apps/UPG_upg-inst/admin/out/appsutil.zip .

cp -r appsutil appsutil_orig
unzip -o appsutil.zip

cd /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/scripts/UPG_upgdb
sh adautocfg.sh

Disable Maintainance Mode on Application Node:

. /app/UPG/apps/apps_st/appl/APPSUPG_upg-inst.env 
adadmin
5 > 2

Run preclone on Application Node and Take Backup:

cd $ADMIN_SCRIPTS_HOME
perl adpreclone.pl appsTier

Take the Application Backup.

Create New Target Oracle Database 19c DB System:

Follow section 2 of Oracle Document: ID:2758990.1

Environment:

11g DB Name: UPG
19c DB Name: UPGCDB
19c DB System hostname: upg19c

Once 19c DB System is provisioned follow the below steps:

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

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=UPGCDB
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=UPGCDB_hfv_lhr
export TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin

Create an NLS directory:

. /home/oracle/19c.env
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/nls/data/old/cr9idata.pl

Create a shared wallet location for the source 11g wallet on 19c DB System:

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

Create MGDSYS schema in the CDB:

. /home/oracle/19c.env
sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql

Close and drop the Pluggable Database:

alter pluggable database UPGCDB_PDB1 close instances=ALL;
drop pluggable database UPGCDB_PDB1 including datafiles;

Stop the CDB:

echo $ORACLE_UNQNAME
UPGCDB_hfv_lhr

srvctl stop database -d UPGCDB_hfv_lhr

Enable OLAP option for 19c DB System:

cd $ORACLE_HOME/rdbms/lib
chopt enable olap

Start the CDB:

echo $ORACLE_UNQNAME  
UPGCDB_hfv_lhr  
  
srvctl start database -d UPGCDB_hfv_lhr

Install required OLAP schema object:

sqlplus / as sysdba 
@?/olap/admin/olap.sql SYSAUX TEMP;

Upgrade the time zone 19c DB System:

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

Shutdown the CDB:

echo $ORACLE_UNQNAME  
srvctl stop database -d UPGCDB_hfv_lhr

Copy Following scripts from Application node to 19c DB System:

On Application Node:

scp /app/UPG/inst/apps/UPG_upg-inst/admin/out/appsutil.zip oracle@19cDBSystem:/u01/app/oracle/product/19.0.0.0/dbhome_1/
scp /app/UPG/apps/apps_st/appl/admin/adgrants.sql oracle@19cDBSystem:/u01/app/oracle/product/19.0.0.0/dbhome_1/admin
scp /app/UPG/apps/apps_st/appl/admin/adstats.sql oracle@19cDBSystem:/u01/app/oracle/product/19.0.0.0/dbhome_1/admin
scp /app/UPG/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adctxprv.sql oracle@19cDBSystem:/u01/app/oracle/product/19.0.0.0/dbhome_1/admin

Prepare for 11g Database Upgrade:

Perform the following on Source 11g DB System:

Create a <DB_NAME>_initparam.sql file in the $ORACLE_HOME/dbs directory       
. /u01/app/oracle/product/11.2.0.4/dbhome_1/UPG_upgdb.env       
       
perl $ORACLE_HOME/appsutil/bin/txkGenDBInitParam.pl -dbcontextfile=$CONTEXT_FILE -dboraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log

Transfer output file to 19c DB System:
scp /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UPG_initparam.sql oracle@19cDBSystem:/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/

Store the UTL_FILE_DIR parameter values on 11g DB System:

. /u01/app/oracle/product/11.2.0.4/dbhome_1/UPG_upgdb.env
cd $ORACLE_HOME/appsutil
cp UPG_upgdb.xml UPG_upgdb.xml_11g


perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/UPG_upgdb.xml -oraclehome=/u01/app/oracle/product/11.2.0.4/dbhome_1 -outdir=/tmp/txkCfgUtlfileDir -upgradedhome=/u01/app/oracle/product/19.0.0.0/dbhome_1 -mode=getUtlFileDir
Enter the APPS Password:
Successfully generated the below file with UTL_FILE_DIR content:
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UPG_utlfiledir.txt
Successfully Completed the script
ERRORCODE = 0 ERRORCODE_END

Transfer output file to 19c DB System:
scp /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/UPG_utlfiledir.txt oracle@19cDBSystem:/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs

Perform the following 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/UPG_upg19c

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/UPG_upg19c
/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/UPG/apps/apps_st/appl/APPSUPG_upg-inst.env

cp /app/UPG/inst/apps/UPG_upg-inst/appl/admin/UPG_upg-inst.xml /app/UPG/inst/apps/UPG_upg-inst/appl/admin/UPG_upg-inst.xml_bkp
vi $CONTEXT_FILE
Press escape

s_applptmp
search
change path to  /u01/app/oracle/product/19.0.0.0/temp/UPG

s_appltmp
search
change path to  /u01/app/oracle/product/19.0.0.0/temp/UPG

escape
: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

Run Autoconfig on Apps Node:

cd $ADMIN_SCRIPTS_HOME/
sh adautocfg.sh
AutoConfig completed successfully.

Perform the following on Source 11g DB System:

Run autoconfig on 11g
. /u01/app/oracle/product/11.2.0.4/dbhome_1/UPG_upgdb.env

cd /u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/scripts/UPG_upgdb
sh adautocfg.sh

AutoConfig completed successfully

Make the Following Directory Structure on 11g DB System like Target 19c DB System.
So, We can complete UTL_FILE Configuration:

mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/outbound/upg19c
chown -R oracle:oinstall /u01/app/oracle/product/19.0.0.0/dbhome_1/

perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/u01/app/oracle/product/11.2.0.4/dbhome_1/appsutil/UPG_upgdb.xml \
-oraclehome=/u01/app/oracle/product/11.2.0.4/dbhome_1 -outdir=/u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/outbound/UPG_upg19c \
-upgradedhome=/u01/app/oracle/product/19.0.0.0/dbhome_1 -mode=setUtlFileDir -servicetype=onpremise -skipdirvalidation=Yes

Drop SYS.ENABLED$INDEXES:

. /u01/app/oracle/product/11.2.0.4/dbhome_1/UPG_upgdb.env

sqlplus "/ as sysdba"
drop table SYS.ENABLED$INDEXES;

Remove the MGDSYS schema:

. /u01/app/oracle/product/11.2.0.4/dbhome_1/UPG_upgdb.env
sqlplus "/ as sysdba" @?/md/admin/catnomgdidcode.sql

Copy the pre-upgrade tool from the target Oracle Database 19c VM DB System $ORACLE_HOME/rdbms/admin/preupgrade.jar on 11g DB System:

Run on 11g DB System
scp oracle@19cSBSystemIP:/u01/app/oracle/product/19.0.0.0/dbhome_1/rdbms/admin/preupgrade.jar .

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

java -jar /tmp/preupgrade.jar FILE TEXT

This command creates the following files:
/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/UPG_q9z_lhr/preupgrade/preupgrade.log
/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/UPG_q9z_lhr/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/UPG_q9z_lhr/preupgrade/postupgrade_fixups.sql

DROP OLAP on 11g DB System:

. /u01/app/oracle/product/11.2.0.4/dbhome_1/UPG_upgdb.env
sqlplus "/ as sysdba"

SET SERVEROUTPUT ON;
@/u01/app/oracle/product/11.2.0.4/dbhome_1/olap/admin/catnoamd.sql

spool dropolapobj.sql
select 'drop '||object_type||' ' || owner || '.' ||object_name||';'
from dba_objects
where status='INVALID' AND OWNER = 'OLAPSYS';

spool off
exit

sqlplus / as sysdba
@dropolapobj.sql
sqlplus / as sysdba
SET SERVEROUTPUT ON;

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;

RUN PREUPGRADE Fixups script on 11g DB SYSTEM:

. /u01/app/oracle/product/11.2.0.4/dbhome_1/UPG_upgdb.env

sqlplus "/ as sysdba"
@/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/UPG_q9z_lhr/preupgrade/preupgrade_fixups.sql

Copy the postupgrade_fixups.sql to the <19c ORACLE_HOME>/admin:

scp /u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/UPG_q9z_lhr/preupgrade/postupgrade_fixups.sql oracle@19cDBSystemIP:/u01/app/oracle/product/19.0.0.0/dbhome_1/admin/

Copy 11g Wallet Files to 19c:

cd /opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME

cd /u01/app/oracle/product/11.2.0.4/dbhome_1/admin/UPG_q9z_lhr/wallet
scp *wallet* oracle@19cDBSystemIP:/opt/oracle/dcs/commonstore/wallets/UPG_q9z_lhr/tde

TAKE Cold RMAN BACKUP of 11g DB:

run
{
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
allocate channel d5 device type disk;
allocate channel d6 device type disk;
backup as compressed backupset full database tag PROD filesperset 10
format '/mnt/OFDBFS/PRODBackup/L0_DB_%T_%d_%s_%p.bkp';
backup format '/mnt/OFDBFS/PRODBackup/L0_ARC_%T_%d_%s_%p.bkp' archivelog all;
backup format '/mnt/OFDBFS/PRODBackup/CF_Level_0_%T_%d.bkp' tag CF current controlfile;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
}
exit;

Prepare Target 19c VM DB System for Restore:

On 19c DB System:
export ORACLE_SID=<11g non-CDB SID>
export ORACLE_UNQNAME=<11g $ORACLE_UNQNAME>
export ORACLE_HOME=<target 19c ORACLE_HOME>

export ORACLE_SID=UPG
export ORACLE_UNQNAME=UPG_q9z_lhr
export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
mkdir -p $ORACLE_HOME/<11g non-CDB name>/adump
mkdir -p $ORACLE_HOME/UPG/adump

Login Grid User and Perform following on ASM:

sudo su - grid
asmcmd
mkdir +DATA/<CDB db_unique_name>/<11g non-CDB name>
mkdir +DATA/<CDB db_unique_name>/<11g non-CDB name>/DATAFILE
mkdir +RECO/<CDB db_unique_name>/<11g non-CDB name>
mkdir +RECO/<CDB db_unique_name>/<11g non-CDB name>/ONLINELOG

su - grid
asmcmd
mkdir +DATA/UPGCDB_hfv_lhr/UPG
mkdir +DATA/UPGCDB_hfv_lhr/UPG/DATAFILE
mkdir +RECO/UPGCDB_hfv_lhr/UPG
mkdir +RECO/UPGCDB_hfv_lhr/UPG/ONLINELOG

Create a temporary initialization parameter file to restore 11g Backup on 19c DB System:

Create a temporary initialization parameter file <19c $ORACLE_HOME/dbs/init<11g non-CDB SID>.ora> with the following parameters:
cd $ORACLE_HOME/dbs
vi initUPG.ora

audit_file_dest='/u01/app/oracle/product/19.0.0.0/dbhome_1/UPG/adump' # Dir created in Step 2.
audit_trail ='DB'
compatible=11.2.0.4
control_files = '+DATA/UPGCDB_hfv_lhr/UPG/control01.ctl','+DATA/UPGCDB_hfv_lhr/UPG/control02.ctl'
# As a best practice, create the control files on separate physical devices.
db_name='UPG'
db_block_size=8192
db_files=512
db_domain=''
db_recovery_file_dest='+RECO'
db_recovery_file_dest_size=200G
diagnostic_dest='/u01/app/oracle/product/19.0.0.0/dbhome_1/UPG'
job_queue_processes=2 # Set to number of CPUs.
open_cursors=300
processes=600
pga_aggregate_target=10G
remote_login_passwordfile='EXCLUSIVE'
sec_case_sensitive_logon=FALSE       #TRUE  you can use true or False based on your choice
sga_target = 2G
undo_tablespace='APPS_UNDOTS1' # Must match undo tablespace in the source database.
EVENT='10946 trace name context forever, level 8454144'
db_file_name_convert='+DATA/UPG_q9z_lhr/datafile/','+DATA/UPGCDB_hfv_lhr/UPG/DATAFILE'
log_file_name_convert='+RECO/UPG_q9z_lhr/onlinelog/','+RECO/UPGCDB_hfv_lhr/UPG/ONLINELOG'

Restore Database on 19c DB System:

export ORACLE_SID=<11g non-CDB SID>
export ORACLE_UNQNAME=<11g $ORACLE_UNQNAME>
export ORACLE_HOME=<target 19c ORACLE_HOME>

export ORACLE_SID=UPG
export ORACLE_UNQNAME=UPG_q9z_lhr
export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

sqlplus / as sysdba
startup nomount pfile=$ORACLE_HOME/dbs/initUPG.ora
rman target /

SET DECRYPTION IDENTIFIED BY "OffTst#2F_1M";
SET DBID=1169325294;

RESTORE CONTROLFILE FROM '/mnt/OFDBFS/rman_bk/CF_Level_0_20230607_UPG.bkp';
alter database mount;
run
{
catalog start with '/mnt/OFDBFS/rman_bk' noprompt;
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
restore database;
recover database;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
If recovery error come then use below command to recover until SCN
recover database until SCN <SCN NUMBER>;
recover database until SCN 123456789;

Run the following commands to first disable block change tracking:

sqlplus / as sysdba
alter database disable block change tracking;
alter database enable block change tracking using file '+DATA';

alter database open resetlogs UPGRADE;

Upgrade Restored Oracle 11g Database to Oracle Database 19c:

export ORACLE_SID=<11g non-CDB SID>
export ORACLE_UNQNAME=<11g $ORACLE_UNQNAME>
export ORACLE_HOME=<target 19c ORACLE_HOME>

export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export ORACLE_SID=UPG
export ORACLE_UNQNAME=UPG_q9z_lhr
export PATH=$ORACLE_HOME/bin:$PATH
cd $ORACLE_HOME/bin
nohup ./dbupgrade &


tail -f $ORACLE_HOME/bin/nohup.out

Post-Upgrade Steps:

export ORACLE_SID=<11g non-CDB SID>
export ORACLE_UNQNAME=<11g $ORACLE_UNQNAME>
export ORACLE_HOME=<target 19c ORACLE_HOME>

export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export ORACLE_SID=UPG
export ORACLE_UNQNAME=UPG_q9z_lhr
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba
SQL> startup

Run the postupgrade_fixups.sql created in the pre-upgrade steps previously and copied to the target VM DB System:

sqlplus / as sysdba
SQL> @?/admin/postupgrade_fixups.sql
sqlplus / as sysdba
SQL> @?/rdbms/admin/utlusts.sql TEXT

Upgrade the time zone in the upgraded database:

cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
SQL> @utltz_countstar.sql


SQL> spool utltz_upg_check.log
SQL> @utltz_upg_check.sql
SQL> spool off


SQL> spool utltz_upg_apply.log
SQL> @utltz_upg_apply.sql
SQL> spool off
sqlplus / as sysdba
SQL> @?/rdbms/admin/catuppst.sql

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

For status of invalid objects
select count(*) from obj$ where status in (4,5,6);

Copy the JRE directory from 19c $ORACLE_HOME/jdk:

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

Change sqlnet.ora:

vi $ORACLE_HOME/network/admin/sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

wq!

Run the adgrants.sql script:

sqlplus / as sysdba @$ORACLE_HOME/admin/adgrants.sql apps

Grant CREATE PROCEDURE privilege to CTXSYS:

sqlplus apps/apps @$ORACLE_HOME/admin/adctxprv.sql manager CTXSYS

Compile invalid objects:

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

Install the Object Label Security (OLS) component:

sqlplus "/ as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/catols.sql

Install the Database Vault (DV) component:

sqlplus "/ as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/catmac.sql SYSTEM TEMP1

Grant DATASTORE ACCESS to PUBLIC:

sqlplus "/ as sysdba"
SQL> grant text datastore access to public;

Gather statistics for the SYS schema:

sqlplus "/ as sysdba"
SQL> alter system enable restricted session;
SQL> @?/admin/adstats.sql
sqlplus "/ as sysdba"
SQL> alter system disable restricted session;
SQL> exit;

Export the Encryption Key:

export ORACLE_SID=<11g non-CDB SID>
export ORACLE_UNQNAME=<11g $ORACLE_UNQNAME>
export ORACLE_HOME=<target 19c ORACLE_HOME>

export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export ORACLE_SID=UPG
export ORACLE_UNQNAME=UPG_q9z_lhr
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus '/ as sysdba'
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 "As33L#2Secret" to '/u01/app/oracle/product/19.0.0.0/dbhome_1/tdepdb.exp' FORCE KEYSTORE IDENTIFIED BY "OffTst#2F_1M";

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=<11g $ORACLE_UNQNAME>

export ORACLE_SID=UPG
export ORACLE_UNQNAME=UPG_q9z_lhr

sqlplus '/ as sysdba'

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:

export ORACLE_SID=<non-CDB_SID>
export ORACLE_UNQNAME=<11g $ORACLE_UNQNAME>

export ORACLE_SID=UPG
export ORACLE_UNQNAME=UPG_q9z_lhr

sqlplus '/ as sysdba'

shutdown immediate;
startup mount;
alter database open read only;

Create a descriptor file $ORACLE_HOME/dbs/YPG_PDBDesc.xml for the non-CDB that is to be plugged in:

sqlplus '/ as sysdba'

BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/UPG_PDBDesc.xml');
end;
/

Shut down the upgraded non-CDB database:
sqlplus '/ as sysdba'
shutdown immediate;

Set the CDB environment, mount the primary CDB instance and run <DB_NAME>_initparam.sql manually with sysdba privileges as per the following code:

$ export ORACLE_HOME=<19c Oracle Home>
$ export ORACLE_SID=<CDB_SID>
$ export ORACLE_UNQNAME=<19c $ORACLE_UNQNAME>
$ export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1
export ORACLE_SID=UPGCDB
export ORACLE_UNQNAME=UPGCDB_hfv_lhr
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba

startup nomount;
@$ORACLE_HOME/dbs/UPG_initparam.sql
shutdown;
srvctl start database -d <CDB_UNQNAME>

srvctl start database -d UPGCDB_hfv_lhr

Run txkChkPDBCompatability.pl:

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 $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0.0/dbhome_1

export ORACLE_SID=UPGCDB
export ORACLE_UNQNAME=UPGCDB_hfv_lhr
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=UPGCDB -pdbsid=UPG

Plug in the non-CDB database as a PDB:

alter system set local_listener =<node_name>:<Grid Listener Port> scope=both;
alter system set local_listener ='upg19c:1521' scope=both;
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.0.0.0/dbhome_1
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 \
-pdbdatadir=+DATA/<CDB db_unique_name>/<11g non-CDB name>/DATAFILE \
-servicetype=dbsystem \
-istdeenabled=yes \
-promptsecretkey=yes \
-secretkeyfile=<export key file with full path created in Section 5.3 above> \
-keystoreloc=<19c CDB WALLET_LOCATION>
export ORACLE_SID=UPGCDB
export ORACLE_UNQNAME=UPGCDB_hfv_lhr
export TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin



perl $ORACLE_HOME/appsutil/bin/txkCreatePDB.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 \
-dbuniquename=UPGCDB_hfv_lhr \
-cdbsid=UPGCDB \
-pdbsid=UPG \
-noncdbdatadir=+DATA/UPG/DATAFILE \
-pdbdatadir=+DATA/UPG/DATAFILE \
-servicetype=dbsystem \
-istdeenabled=yes \
-promptsecretkey=yes \
-secretkeyfile=/u01/app/oracle/product/19.0.0.0/dbhome_1/tdepdb.exp \
-keystoreloc=/opt/oracle/dcs/commonstore/wallets/UPGCDB_hfv_lhr

Enter the CDB wallet password:  OffTst#2F_1M
Enter the secret key:  As33L#2Secret

Login in GRID USER:

su - grid
vi /u01/app/19.0.0.0/grid/network/admin/listener.ora
add following line
USE_SID_AS_SERVICE_LISTENER=ON
wq!

lsnrctl reload

Login Oracle USER:

$ 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
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=UPGCDB
export ORACLE_UNQNAME=UPGCDB_hfv_lhr
export TNS_ADMIN=/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
$ 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/UPG
mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/outbound/UPG_upg19c
mkdir -p /u01/app/oracle/product/19.0.0.0/dbhome_1/appsutil/admin

Run txkPostPDBCreationTasks.pl:

cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/jdk/jre/lib/ext
cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/appsutil/jre/lib/ext
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
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=UPGCDB \
-dbuniquename=UPGCDB_hfv_lhr \
-cdbsid=UPGCDB \
-pdbsid=UPG \
-appsuser=apps \
-israc=no \
-dbport=1521 \
-servicetype=dbsystem

Database Upgrade is Complete.

Now re-clone Applcation with 19c DB:

cd /app/UPG/apps/apps_st/comn/clone/bin/
perl adcfgclone.pl appsTier

Start the Application.

Oracle Database
Oracle Oci
Database Upgrade
Oracle
Oracle Cloud

--

--

Mohsin Ali

Written by Mohsin Ali

10 Followers

Oracle Application Database Consultant Linkedin: https://www.linkedin.com/in/mohsinalirana/

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams