Upgrade 11g DB System to 19c DB System for Oracle E-Business Suite 12.1.3 on OCI
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.