Oracle E-Business Suite Database Upgrade (19c)
In this post we will upgrade the Oracle database from version 12.1 to 19c and apply the latest database Patch Set Update (PSU).
Table of Contents
If you are interested in the steps it takes to install an Oracle E-Business Suite 12.2 with Oracle Database 12.2 take a look here. Since 12.2 is out of support, we need to upgrade the database to 19c or 23ai. Since we cannot upgrade directly to 23ai we need to upgrade to 19c first.
Downloading the software
The Oracle Database 19c software can be downloaded from edelivery.oracle.com from the same place as we downloaded the Oracle E-Business Suite software (see here). The latest hcheck.sql script can be downloaded from Doc ID 136697.1.
Database Upgrade preparation
The primary documentation for this database Upgrade can be found in Doc ID 2580629.1 on the Oracle support portal.
Running hcheck.sql
We will run hcheck.sql to check the database for common Data Dictionary errors.
# run hcheck.sql
mkdir -p ~/hcheck; cd ~/hcheck
. /d01/oracle/VIS/12.1.0/VIS_lin1.env;
sqlplus -S / as sysdba <<EOF
spool hcheck.log
@/sw/ora_ebs/12214/hcheck.sql
spool off
exit
EOF
In my case I had problems with 5 database objects (1 x HCKE-0002 and 4 x HCKE-0023):
Output
HCheck Version 04AUG23 on 03-MAR-2025 07:55:42
----------------------------------------------
Catalog Version 12.1.0.2.0 (1201000200)
db_name: VIS
Is CDB?: NO
Catalog Fixed
Procedure Name Version Vs Release Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj ... 1201000200 <= *All Rel* 03/03 07:55:42 PASS
.- MissingOIDOnObjCol ... 1201000200 <= *All Rel* 03/03 07:55:42 FAIL
HCKE-0002: Object type column with missing OID$ (Doc ID 1360268.1)
OBJ#=525262 Name=CSR.CSR_RULE_WINDOWS_B IntCol#=3=SYS_NC_ROWINFO$
TabProp=2107603
.- SourceNotInObj ... 1201000200 <= *All Rel* 03/03 07:55:43 PASS
.- OversizedFiles ... 1201000200 <= *All Rel* 03/03 07:55:55 PASS
.- PoorDefaultStorage ... 1201000200 <= *All Rel* 03/03 07:55:55 PASS
.- PoorStorage ... 1201000200 <= *All Rel* 03/03 07:55:55 PASS
.- TabPartCountMismatch ... 1201000200 <= *All Rel* 03/03 07:55:55 PASS
.- OrphanedTabComPart ... 1201000200 <= *All Rel* 03/03 07:55:56 PASS
.- MissingSum$ ... 1201000200 <= *All Rel* 03/03 07:55:56 PASS
.- MissingDir$ ... 1201000200 <= *All Rel* 03/03 07:55:56 PASS
.- DuplicateDataobj ... 1201000200 <= *All Rel* 03/03 07:55:57 PASS
.- ObjSynMissing ... 1201000200 <= *All Rel* 03/03 07:55:58 PASS
.- ObjSeqMissing ... 1201000200 <= *All Rel* 03/03 07:55:58 PASS
.- OrphanedUndo ... 1201000200 <= *All Rel* 03/03 07:55:59 PASS
.- OrphanedIndex ... 1201000200 <= *All Rel* 03/03 07:55:59 PASS
.- OrphanedIndexPartition ... 1201000200 <= *All Rel* 03/03 07:55:59 PASS
.- OrphanedIndexSubPartition ... 1201000200 <= *All Rel* 03/03 07:55:59 PASS
.- OrphanedTable ... 1201000200 <= *All Rel* 03/03 07:55:59 PASS
.- OrphanedTablePartition ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- OrphanedTableSubPartition ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- MissingPartCol ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- OrphanedSeg$ ... 1201000200 <= *All Rel* 03/03 07:56:00 FAIL
HCKE-0023: Orphaned SEG$ Entry (Doc ID 1360934.1)
ORPHAN SEG$: SegType=INDEX TS=15 RFILE/BLOCK=61/137666
ORPHAN SEG$: SegType=INDEX TS=15 RFILE/BLOCK=61/137650
ORPHAN SEG$: SegType=LOB TS=15 RFILE/BLOCK=61/137642
ORPHAN SEG$: SegType=LOB TS=15 RFILE/BLOCK=61/137658
.- OrphanedIndPartObj# ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- DuplicateBlockUse ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- FetUet ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- Uet0Check ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- SeglessUET ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- BadInd$ ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- BadTab$ ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- BadIcolDepCnt ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- ObjIndDobj ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- TrgAfterUpgrade ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- ObjType0 ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- BadOwner ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- StmtAuditOnCommit ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- BadPublicObjects ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- BadSegFreelist ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- BadDepends ... 1201000200 <= *All Rel* 03/03 07:56:00 PASS
.- CheckDual ... 1201000200 <= *All Rel* 03/03 07:56:01 PASS
.- ObjectNames ... 1201000200 <= *All Rel* 03/03 07:56:01 PASS
.- BadCboHiLo ... 1201000200 <= 1202000000 03/03 07:56:02 PASS
.- ChkIotTs ... 1201000200 <= *All Rel* 03/03 07:56:02 PASS
.- NoSegmentIndex ... 1201000200 <= *All Rel* 03/03 07:56:02 PASS
.- BadNextObject ... 1201000200 <= *All Rel* 03/03 07:56:02 PASS
.- DroppedROTS ... 1201000200 <= *All Rel* 03/03 07:56:02 PASS
.- FilBlkZero ... 1201000200 <= *All Rel* 03/03 07:56:02 PASS
.- DbmsSchemaCopy ... 1201000200 <= *All Rel* 03/03 07:56:02 PASS
.- OrphanedIdnseqObj ... 1201000200 > 1201000000 03/03 07:56:02 PASS
.- OrphanedIdnseqSeq ... 1201000200 > 1201000000 03/03 07:56:02 PASS
.- OrphanedObjError ... 1201000200 > 1102000000 03/03 07:56:02 PASS
.- ObjNotLob ... 1201000200 <= *All Rel* 03/03 07:56:02 PASS
.- MaxControlfSeq ... 1201000200 <= *All Rel* 03/03 07:56:02 PASS
.- SegNotInDeferredStg ... 1201000200 > 1102000000 03/03 07:56:02 PASS
.- SystemNotRfile1 ... 1201000200 > 902000000 03/03 07:56:02 PASS
.- DictOwnNonDefaultSYSTEM ... 1201000200 <= *All Rel* 03/03 07:56:03 PASS
.- OrphanTrigger ... 1201000200 <= *All Rel* 03/03 07:56:03 PASS
.- ObjNotTrigger ... 1201000200 <= *All Rel* 03/03 07:56:03 PASS
---------------------------------------
03-MAR-2025 07:56:03 Elapsed: 21 secs
---------------------------------------
Found 5 potential problem(s) and 0 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not
PL/SQL procedure successfully completed.
Statement processed.
Complete output is in trace file:
/d01/oracle/VIS/12.1.0/admin/VIS_lin1/diag/rdbms/vis/VIS/trace/VIS_ora_13510_HCHECK.trcRegarding Oracle Support (HCKE-0002, HCKE-0023) these issues can be ignored. After checking the patches we can continue with the upgrade:
Install the Oracle 19c database software and CDB creation
# Create the initialization parameter setup files
. /d01/oracle/VIS/12.1.0/VIS_lin1.env
cd /d01/oracle/VIS/12.1.0/appsutil
. ./txkSetCfgCDB.env dboraclehome=/d01/oracle/VIS/12.1.0
export ORACLE_SID=VIS
cd /d01/oracle/VIS/12.1.0/appsutil/bin
echo apps|perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/d01/oracle/VIS/12.1.0 \
-outdir=/d01/oracle/VIS/12.1.0/appsutil/log -appsuser=apps \
-dbsid=VIS -skipdbshutdown=yes
# unzip database software
unzip -q /sw/ora_ebs/12214/V982063-01.zip -d /d01/oracle/VIS/19.3.0
# install the 19.3 database software
cat > ~oracle/rsp_db.rsp << EOF
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=dba
INVENTORY_LOCATION=/oracle/oraInventory
ORACLE_BASE=/d01/oracle/VIS
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=dba
oracle.install.db.OSDGDBA_GROUP=dba
oracle.install.db.OSKMDBA_GROUP=dba
oracle.install.db.OSRACDBA_GROUP=dba
oracle.install.db.rootconfig.executeRootScript=true
oracle.install.db.rootconfig.configMethod=ROOT
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.ConfigureAsContainerDB=false
oracle.install.db.config.starterdb.memoryOption=false
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.managementOption=DEFAULT
oracle.install.db.config.starterdb.omsPort=0
oracle.install.db.config.starterdb.enableRecovery=false
oracle.install.db.config.starterdb.password.ALL=change_on_install
EOF
export ORACLE_HOME=/d01/oracle/VIS/19.3.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PERL5LIB=/d01/oracle/VIS/19.3.0/perl/lib/5.38.2:/d01/oracle/VIS/19.3.0/perl/lib/site_perl/5.38.2/:/d01/oracle/VIS/19.3.0/appsutil/perl
export CV_ASSUME_DISTID=OL7
echo changeme|/d01/oracle/VIS/19.3.0/runInstaller -silent -responseFile /home/oracle/rsp_db.rsp
# update opatch
mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.old
unzip -oq /sw/ora_ebs/12214patches/p6880880_122010_Linux-x86-64.zip -d $ORACLE_HOME
unzip -oq /sw/ora_ebs/12214patches/p37260974_190000_Linux-x86-64.zip -d ~/dbfixes
unzip -oq /sw/ora_ebs/12214patches/p37102264_190000_Linux-x86-64.zip -d ~/dbfixes
unzip -oq /sw/ora_ebs/12214patches/p33538523_1926000DBRU_Linux-x86-64.zip -d ~/dbfixes
unzip -oq /sw/ora_ebs/12214patches/p36749043_1926000DBRU_Linux-x86-64.zip -d ~/dbfixes
unzip -oq /sw/ora_ebs/12214patches/p37327930_1926000DBRU_Linux-x86-64.zip -d ~/dbfixes
# applying patches
unzip -oq /sw/ora_ebs/12214patches/p37260974_190000_Linux-x86-64.zip -d ~/dbfixes
cd ~/dbfixes; $ORACLE_HOME/OPatch/opatch apply 37260974 -silent
echo changeme|su - root -c 'chown root /d01/oracle/VIS/19.3.0/bin/extjob; chmod 4750 /d01/oracle/VIS/19.3.0/bin/extjob'
$ORACLE_HOME/OPatch/opatch napply -id 37102264,33538523,36749043 -silent
cd ~/dbfixes/zip-bundle/LINUX_X86-64/database/19.26.0.0.250121DBRU/
for i in *zip; do unzip -qo $i; done
$ORACLE_HOME/OPatch/opatch napply -id 33538523,36749043 -silent; cd
# Create nls/data/9idata directory
perl $ORACLE_HOME/nls/data/old/cr9idata.pl
# Create appsutil.zip and copy it to the database tier
. /d01/oracle/VIS/EBSapps.env run
perl /d01/oracle/VIS/fs1/EBSapps/appl/ad/12.0.0/bin/admkappsutil.pl
unzip -qo /d01/oracle/VIS/fs1/inst/apps/VIS_lin1/admin/out/appsutil.zip -d /d01/oracle/VIS/19.3.0
# Copy the orai18n.jar file
cp /d01/oracle/VIS/19.3.0/jlib/orai18n.jar /d01/oracle/VIS/19.3.0/jdk/jre/lib/ext
# Install JRE 8
cd /d01/oracle/VIS/19.3.0/appsutil
cp -r /d01/oracle/VIS/19.3.0/jdk/jre .
cp /d01/oracle/VIS/19.3.0/jlib/orai18n.jar /d01/oracle/VIS/19.3.0/appsutil/jre/lib/ext
# Create the CDB
mkdir /d01/oracle/VIS/19.3.0/VISCDB
cat > ~oracle/rsp_dbca.rsp << EOF
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=VISCDB.fritz.box
sid=VISCDB
databaseConfigType=SI
policyManaged=false
createServerPool=false
force=false
createAsContainerDatabase=true
numberOfPDBs=0
useLocalUndoForPDBs=true
templateName=/d01/oracle/VIS/19.3.0/assistants/dbca/templates/General_Purpose.dbc
emConfiguration=DBEXPRESS
emExpressPort=5500
runCVUChecks=FALSE
omsPort=0
dvConfiguration=false
olsConfiguration=false
datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
datafileDestination={ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/
storageType=FS
characterSet=AL32UTF8
nationalCharacterSet=UTF8
registerWithDirService=false
skipListenerRegistration=true
variables=ORACLE_BASE_HOME=/d01/oracle/VIS/19.3.0,DB_UNIQUE_NAME=VISCDB,ORACLE_BASE=/d01/oracle/VIS,PDB_NAME=,DB_NAME=VISCDB,ORACLE_HOME=/d01/oracle/VIS/19.3.0,SID=VISCDB
initParams=undo_tablespace=UNDOTBS1,sga_target=2GB,db_block_size=8192BYTES,nls_language=AMERICAN,dispatchers=(PROTOCOL=TCP) (SERVICE=VISCDBXDB),diagnostic_dest=/d01/oracle/VIS/19.3.0/VISCDB,control_files=("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control02.ctl"),remote_login_passwordfile=EXCLUSIVE,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=640,pga_aggregate_target=1GB,nls_territory=AMERICA,open_cursors=300,db_domain=fritz.box,compatible=19.0.0,db_name=VISCDB,audit_trail=db
sampleSchema=false
memoryPercentage=40
databaseType=MULTIPURPOSE
automaticMemoryManagement=false
totalMemory=0
sysPassword=change_on_install
systemPassword=manager
EOF
export ORACLE_HOME=/d01/oracle/VIS/19.3.0
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PERL5LIB=/d01/oracle/VIS/19.3.0/perl/lib/5.38.2:/d01/oracle/VIS/19.3.0/perl/lib/site_perl/5.38.2/:/d01/oracle/VIS/19.3.0/appsutil/perl
dbca -silent -createDatabase -responseFile ~/rsp_dbca.rsp
# Run datapatch on CDB
export ORACLE_SID=VISCDB; unset TWO_TASK
$ORACLE_HOME/OPatch/datapatch
# Create the CDB MGDSYS schema
echo '@?/rdbms/admin/catmgd.sql'|sqlplus -S / as sysdba
# Create the CDB TNS files
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=/d01/oracle/VIS/19.3.0
cd $ORACLE_HOME/appsutil/bin
perl txkGenCDBTnsAdmin.pl -dboraclehome=/d01/oracle/VIS/19.3.0 \
-cdbname=VISCDB -cdbsid=VISCDB -dbport=1521 \
-outdir=/d01/oracle/VIS/19.3.0/appsutil/log
# Set parameters and shutdown the CDB
echo 'shutdown immediate'|sqlplus -S / as sysdba
Now we are ready to perform the actual database upgrade involving downtime for the end users.
Performing the actual database upgrade
# Store the UTL_FILE_DIR parameter values
. /d01/oracle/VIS/12.1.0/VIS_lin1.env
echo apps|perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/d01/oracle/VIS/12.1.0/appsutil/VIS_lin1.xml \
-oraclehome=/d01/oracle/VIS/12.1.0 -outdir=/home/oracle \
-upgradedhome=/d01/oracle/VIS/19.3.0 -mode=getUtlFileDir -servicetype=onpremise
mkdir -p /d01/oracle/VIS/temp/VIS
mkdir -p /d01/oracle/VIS/19.3.0/appsutil/outbound/VIS_lin1
. /d01/oracle/VIS/12.1.0/VIS_lin1.env
(echo apps; echo manager)|perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/d01/oracle/VIS/12.1.0/appsutil/VIS_lin1.xml -oraclehome=/d01/oracle/VIS/12.1.0 -outdir=/home/oracle -upgradedhome=/d01/oracle/VIS/19.3.0 -mode=setUtlFileDir -servicetype=onpremise
# stop Application Tier
. /d01/oracle/VIS/fs1/EBSapps/appl/VIS_lin1.env
(echo apps; echo apps; echo welcome1)|/d01/oracle/VIS/fs1/inst/apps/VIS_lin1/admin/scripts/adstpall.sh
. /d01/oracle/VIS/12.1.0/VIS_lin1.env
lsnrctl stop
echo 'VIS:/d01/oracle/VIS/12.1.0:N' >> /etc/oratab
sed -i s/^sec_case_sensitive_logon.*// /d01/oracle/VIS/12.1.0/dbs/initVIS.ora
sed -i s/^olap_page_pool_size.*// /d01/oracle/VIS/12.1.0/dbs/initVIS.ora
sed -i s/'^processes.*'/'processes = 300 # Max. no. of users x 2'/ /d01/oracle/VIS/12.1.0/dbs/initVIS.ora
sed -i s/'^sessions.*'/'sessions = 600 # 2 X processes'/ /d01/oracle/VIS/12.1.0/dbs/initVIS.ora
sed -i s/'^o7_dictionary_accessibility.*'/'#o7_dictionary_accessibility = FALSE #MP'/ /d01/oracle/VIS/12.1.0/dbs/initVIS.ora
sed -i s/'^optimizer_adaptive_features.*'/'#optimizer_adaptive_features = FALSE #MP'/ /d01/oracle/VIS/12.1.0/dbs/initVIS.ora
sed -i s/'^utl_file_dir.*'/'#utl_file_dir = \/usr\/tmp,\/usr\/tmp,\/d01\/oracle\/VIS\/12.1.0\/appsutil\/outbound\/VIS_lin1,\/usr\/tmp'/ /d01/oracle/VIS/12.1.0/dbs/initVIS.ora
echo "EVENT='10946 trace name context forever, level 8454144'" >>/d01/oracle/VIS/12.1.0/dbs/initVIS.ora
sqlplus -S / as sysdba <<EOF
shutdown immediate
startup
@?/olap/admin/catnoamd.sql
exec dbms_stats.gather_dictionary_stats()
EOF
# Upgrade the database instance
# fix dbua check failures
. /d01/oracle/VIS/12.1.0/VIS_lin1.env
cat <<EOF >~/drop.sql
set pause off
set heading off
set pagesize 0
set feedback off
set verify off
spool /home/oracle/dropsys.sql
select 'DROP ' || object_type || ' SYSTEM."' || object_name || '";'
from dba_objects
where object_name not in ('AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','AQ$_SCHEDULES','PRODUCT_USER_PROFILE','SQLPLUS_PRODUCT_PROFILE','PRODUCT_PRIVS','HELP','HELP_TOPIC_SEQ') and object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';
spool off
exit
EOF
sqlplus -S / as sysdba @/home/oracle/drop.sql
echo '@/home/oracle/dropsys.sql'|sqlplus -S system/manager
echo '@?/rdbms/admin/utlrp'|sqlplus -S / as sysdba
# Case insensitive password version
sqlplus / as sysdba <<EOF
alter user DCM identified by change_on_install;
alter user REPADMIN identified by change_on_install;
alter user IP identified by change_on_install;
alter user CTXTEST identified by change_on_install;
alter user CSDUMMY identified by change_on_install;
alter user TRACESVR identified by change_on_install;
alter user EDGE identified by change_on_install;
alter user AD_MONITOR identified by change_on_install;
alter user EM_MONITOR identified by change_on_install;
exit
EOF
# remove Streams
echo 'exec dbms_streams_adm.remove_streams_configuration()'|sqlplus -S / as sysdba
sqlplus / as sysdba <<EOF
truncate table sys.apply\$_source_obj ;
truncate table apply\$_source_schema;
EOF
export ORACLE_HOME=/d01/oracle/VIS/19.3.0
export ORACLE_SID=VIS
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PERL5LIB=/d01/oracle/VIS/19.3.0/perl/lib/5.38.2:/d01/oracle/VIS/19.3.0/perl/lib/site_perl/5.38.2/:/d01/oracle/VIS/19.3.0/appsutil/perl
export CV_ASSUME_DISTID=OL7
lsnrctl start
dbua -silent -sid VIS -keepEvents -upgradeTimezone true -recompile_invalid_objects true -upgrade_parallelism 4 -emConfiguration DBEXPRESS -listeners LISTENER -performFixUp true
sqlplus -S / as sysdba <<EOF
alter system set compatible='19.0.0' scope=spfile;
alter user apps identified by apps;
shutdown immediate
startup
EOF
$ORACLE_HOME/OPatch/datapatch
After the database has been upgraded to Oracle 19c these steps need to be done:
# Run adgrants.sql
mkdir -p /d01/oracle/VIS/19.3.0/appsutil/admin
cp /d01/oracle/VIS/fs_ne/EBSapps/patch/36989014/admin/adgrants.sql /d01/oracle/VIS/19.3.0/appsutil/admin
echo '@/d01/oracle/VIS/19.3.0/appsutil/admin/adgrants.sql apps'|sqlplus -S /nolog
# Grant create procedure privilege on CTXSYS
cp /d01/oracle/VIS/fs1/EBSapps/appl/ad/12.0.0/patch/115/sql/adctxprv.sql /d01/oracle/VIS/19.3.0/appsutil/admin
sqlplus -S apps/apps @/d01/oracle/VIS/19.3.0/appsutil/admin/adctxprv.sql manager CTXSYS
# Compile invalid objects
echo '@$ORACLE_HOME/rdbms/admin/utlrp.sql'|sqlplus -S / as sysdba
# Grant datastore access
echo 'grant text datastore access to public;'|sqlplus -S / as sysdba
# Validate Workflow ruleset
cp /d01/oracle/VIS/fs1/EBSapps/appl/fnd/12.0.0/patch/115/sql/wfaqupfix.sql /d01/oracle/VIS/19.3.0/appsutil/admin
sqlplus apps/apps @/d01/oracle/VIS/19.3.0/appsutil/admin/wfaqupfix.sql applsys apps
# Gather statistics for SYS schema
cp /d01/oracle/VIS/fs1/EBSapps/appl/admin/adstats.sql /d01/oracle/VIS/19.3.0/appsutil/admin
sqlplus -S / as sysdba <<EOF
alter system enable restricted session;
@/d01/oracle/VIS/19.3.0/appsutil/admin/adstats.sql
EOF
sqlplus -S / as sysdba <<EOF
alter system disable restricted session;
EOF
In the next step we will convert the database to a pluggable database (VIS) in the container database VISCDB:
# Create the PDB descriptor
sed -i s/'^alias tal.*'/"alias tal\='tail -200f \/d01\/oracle\/VIS\/19.3.0\/VISCDB\/diag\/rdbms\/viscdb\/VISCDB\/trace\/alert_VISCDB.log'"/ ~/.bash_profile
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=/d01/oracle/VIS/19.3.0
export ORACLE_SID=VIS
cd $ORACLE_HOME/appsutil/bin
echo apps|perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/d01/oracle/VIS/19.3.0 \
-outdir=/d01/oracle/VIS/19.3.0/appsutil/log -appsuser=apps -dbsid=VIS
# Update the CDB initialization parameters
cp /d01/oracle/VIS/12.1.0/dbs/VIS_initparam.sql /d01/oracle/VIS/19.3.0/dbs
cp /d01/oracle/VIS/12.1.0/dbs/VIS_datatop.txt /d01/oracle/VIS/19.3.0/dbs
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=/d01/oracle/VIS/19.3.0
export ORACLE_SID=VISCDB
sqlplus -S / as sysdba <<EOF
startup nomount;
@?/dbs/VIS_initparam.sql
alter system set LOCAL_LISTENER="lin1.fritz.box:1521" scope=both;
alter system set processes=300 scope=spfile;
alter system set sessions=600 scope=spfile;
shutdown;
startup;
EOF
# Check for PDB violations
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=/d01/oracle/VIS/19.3.0
export ORACLE_SID=VISCDB
cd $ORACLE_HOME/appsutil/bin
perl txkChkPDBCompatability.pl -dboraclehome=/d01/oracle/VIS/19.3.0 \
-outdir=/d01/oracle/VIS/19.3.0/appsutil/log -cdbsid=VISCDB \
-pdbsid=VIS -servicetype=onpremise
# Create the PDB
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=/d01/oracle/VIS/19.3.0
cd $ORACLE_HOME/appsutil/bin
(/d01/oracle/VIS/data; echo /d01/oracle/VIS/data)|perl txkCreatePDB.pl -dboraclehome=/d01/oracle/VIS/19.3.0 -outdir=/d01/oracle/VIS/19.3.0/appsutil/log \
-cdbsid=VISCDB -pdbsid=VIS -dbuniquename=VIS.fritz.box -servicetype=onpremise
# Run the post PDB script
exit
su - oracle
cd /d01/oracle/VIS/19.3.0/appsutil
. ./txkSetCfgCDB.env dboraclehome=/d01/oracle/VIS/19.3.0
(echo apps; echo manager)|perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=/d01/oracle/VIS/19.3.0 \
-outdir=/d01/oracle/VIS/19.3.0/appsutil/log -cdbsid=VISCDB -pdbsid=VIS \
-appsuser=apps -dbport=1521 -servicetype=onpremise
# Modify initialization parameters
export ORACLE_SID=VISCDB
sqlplus -S / as sysdba <<EOF
alter system reset core_dump_dest;
alter system reset "_pdb_name_case_sensitive";
alter system set event="10946 trace name context forever, level 8454144" scope=spfile;
alter system set optimizer_secure_view_merging=false;
alter system set shared_pool_size=600m scope=spfile;
alter system set shared_pool_reserved_size=60m scope=spfile;
alter system set filesystemio_options = 'SETALL' scope=spfile;
alter system set sec_case_sensitive_logon = false;
alter session set container=VIS;
alter system set temp_undo_enabled=false;
alter session set container=cdb\$root;
shutdown immediate
startup
EOF
# Run AutoConfig on applications tier
cat <<EOF >>/d01/oracle/VIS/fs1/inst/apps/VIS_lin1/ora/10.1.2/network/admin/tnsnames.ora
VISCDB_LIN1.fritz.box=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=lin1.fritz.box)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ebs_VIS)(INSTANCE_NAME=VISCDB)
)
)
EOF
sed -i s/'<jdbc_url oa_var=\"s_apps_jdbc_connect_descriptor.*'/' <jdbc_url oa_var=\"s_apps_jdbc_connect_descriptor\"><\/jdbc_url>'/ /d01/oracle/VIS/fs1/inst/apps/VIS_lin1/appl/admin/VIS_lin1.xml
sed -i s/'<APPLPTMP oa_var=\"s_applptmp\".*'/' <APPLPTMP oa_var=\"s_applptmp\" osd=\"UNIX\">\/d01\/oracle\/VIS\/temp\/VIS<\/APPLPTMP>'/ /d01/oracle/VIS/fs1/inst/apps/VIS_lin1/appl/admin/VIS_lin1.xml
. /d01/oracle/VIS/EBSapps.env run
echo apps|$INST_TOP/admin/scripts/adautocfg.sh
exit
su - oracle
. /d01/oracle/VIS/EBSapps.env run
# Restart applications tier server processes
(echo apps; echo apps; echo welcome1)|/d01/oracle/VIS/fs1/inst/apps/VIS_lin1/admin/scripts/adstrtal.sh
The database of the Oracle E-Business Suite system is now on Oracle 19c with the latest Patch Set Update (PSU) applied and the Application is running again 🙂
Leave a Reply