Documents to be Referred
a) Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c (Doc ID 2552181.1)
b) Using Oracle 19c RAC Multitenant (Single PDB) with Oracle E-Business Suite Release 12.2 (Doc ID 2530665.1)
c) Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID 1594274.1)
Oracle E-Business Suite Applications DBA and Technology Stack Release Notes for R12.AD.C.Delta.12 and R12.TXK.C.Delta.12 (Doc ID 2649885.1)
Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Doc ID 1617461.1)
Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID 1594274.1)
Using UTL_FILE_DIR or Database Directories for PL/SQL File I/O in Oracle E-Business Suite Releases 12.1 and 12.2 (Doc ID 2525754.1)
19c Database with Oracle E-Business Suite R12 Known Issues & Solutions (Doc ID 2662860.1)
Oracle E-Business Suite Recommended Performance Patches (Doc ID 244040.1)
cd /u01/app/oracle/patch/19c_ebs/31281355
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
opatch apply
unzip p31219897_190000_SOLARIS64.zip
cd /u01/app/oracle/patch/19c_ebs/31219897
opatch prereq CheckConflictAgainstOHWithDetail -ph ./
opatch apply
unzip p29802382_198000DBRU_Generic.zip
cd /u01/app/oracle/patch/19c_ebs/29802382
opatch apply
unzip p29867728_198000DBRU_SOLARIS64.zip
cd /u01/app/oracle/patch/19c_ebs/29867728
opatch apply
/u01/app/oracle/patch/19c_ebs/30621255
opatch apply
/u01/app/oracle/patch/19c_ebs/31113249
opatch apply
unzip p31381618_198000DBRU_SOLARIS64.zip --- etcc bundle
cd /u01/app/oracle/patch/19c_ebs/etcc-bundle/Solaris_sparc/database/19.8.0.0.200714DBRU
unzip p31178103_198000DBRU_SOLARIS64.zip
cd /u01/app/oracle/patch/19c_ebs/etcc-bundle/Solaris_sparc/database/19.8.0.0.200714DBRU/31178103
opatch apply
cd /u01/app/oracle/patch/19c_ebs/31596956
APplication Server :
==========================
############Upgrade to latest AD and TXK#####################
Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Doc ID 1617461.1)
AD 11 Patch:
============
1. Start admin server
cd $ADMIN_SCRIPTS_HOME/
./adadminsrvctl.sh start
copy adgrants and execute
compare the version of adgrants.sql in %APPL_TOP%\admin
cd /u01/app/oracle/fs_ne/EBSapps/patch/26834480/admin
scp adgrants.sql oracle@rgdrdb01:/u01/app/oracle/product/12.1.0/dbhome_1/appsutil/admin
######## -- 35 minutes AD
============================================
{ echo apps; echo manager; echo welcome123; } | adop phase=apply patches=26834480 hotpatch=yes
--patchtop=/u01/upgrade/upg_patch/ADTXK11
---Not working ----> { echo apps; echo manager; echo welcome123; } | adop phase=apply patches=26834480_SQ hotpatch=yes
** 30 Min ****
{ echo apps; echo manager; echo welcome123; } | adop phase=apply patches=30258630,30213183,28280348 merge=yes hotpatch=yes workers=40
### 80 min (TXK)
========
{ echo $appspass; echo $systempass; echo $wlspass; }
{ echo apps; echo manager; echo welcome123; } |adop phase=apply patches=28840822,29965377,29781255 merge=yes workers=40 hotpatch=yes
update txk_tcc_results set DATABASE_NAME='UPG';
update TXK_TCC_RESULTS set node_name='RGDRDB01' where component_name='RDBMS';
update TXK_TCC_RESULTS set node_name='SPDRAPP' where component_name!='RDBMS';
set serveroutput on;
declare
l_msg varchar2(4000);
begin
ad_zd_adop.adop_database_validations(l_msg);
dbms_output.put_line(l_msg);
end;
/
Prerequsite patches : 9 hrs
=======================
{ echo apps; echo manager; echo welcome123; } | adop phase=prepare,apply,finalize,cutover,cleanup mtrestart=no patches=25452805,26052406,26521736,28371446,29965377,30433124,30601878,30713114,31088182,31096454,31138017,31151048,31167560,31349591,29914546
select BUG_NUMBER,CREATION_DATE,ARU_RELEASE_NAME,LANGUAGE from AD_BUGS where bug_number in ('25452805','26052406','26521736','28371446','29965377','30433124','30601878','30713114','31088182','31096454','31138017','31151048','31167560','31349591','29914546')
-- ('30258630','30213183','28280348','28840822','29965377','29781255')
Output: /u01/app/oracle/fs_ne/EBSapps/log/adop/31/20201011_092138/adzdshowstatus.out
===============================================================
Node Name Node Type Phase Status Started Finished Elapsed
--------------- ---------- --------------- --------------- -------------------- -------------------- ------------
spdrapp master PREPARE COMPLETED 2020/10/08 17:43:24 2020/10/08 20:08:06 2:24:42
APPLY COMPLETED 2020/10/08 20:09:00 2020/10/09 00:38:44 4:29:44
FINALIZE COMPLETED 2020/10/09 00:39:18 2020/10/09 00:51:18 0:12:00
CUTOVER COMPLETED 2020/10/09 00:52:12 2020/10/09 00:57:23 0:05:11
CLEANUP COMPLETED 2020/10/09 00:57:26 2020/10/09 02:47:03 1:49:37
File System Synchronization Type: Light
Run Autoconfig on DB Tier:
==========================
. /u01/app/oracle/EBSapps.env run
cd $ADMIN_SCRIPTS_HOME/
./adautocfg.sh
applprod@spdrapp:/u01/app/oracle/fs2/inst/apps/UPG_spdrapp/admin/scripts$ perl $AD_TOP/bin/admkappsutil.pl
scp -p /u01/app/oracle/fs2/inst/apps/UPG_spdrapp/admin/out/appsutil.zip oracle@rgdrdb01:/u01/app/oracle/product/12.1.0/dbhome_1
cd $COMMON_TOP/util/jdk
scp -rp jre oracle@rgdrdb01:/u01/app/oracle/product/12.1.0/dbhome_1/appsutil
./adconfig.sh contextfile=/u01/app/oracle/product/12.1.0/dbhome_1/appsutil/UPG_rgdrdb01.xml
on DB Home:
==============
cd /u01/app/oracle/product/12.1.0/dbhome_1/appsutil
. ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/12.1.0/dbhome_1
export ORACLE_SID=UPG
cd $ORACLE_HOME/appsutil/bin
perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/u01/app/oracle/product/12.1.0/dbhome_1 -outdir=/u01/app/oracle/product/12.1.0/dbhome_1appsutil/log -appsuser=apps -dbsid=UPG -skipdbshutdown=yes
Prepare Environment File:
=========================
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID=UPGCDB
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
export ORA_NLS10=/u01/app/oracle/product/19.3.0/dbhome_1/nls/data/9idata
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORA_NLS10=/u01/app/oracle/product/19.3.0/dbhome_1/nls/data/9idata
perl $ORACLE_HOME/nls/data/old/cr9idata.pl
select * from nls_database_parameters
where parameter='NLS_CHARACTERSET';
Create CDB --> DBCA - 1 hr 10 min
==================================
19c Home
-----------
$ORACLE_HOME/bin/dbca
Run datapatch - 2 min
=========================
export ORACLE_SID=upcdb
$ORACLE_HOME/OPatch/datapatch
Create MGDSYS Schema - 2min
===========================
sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql
Create CDB TNS Files
==========================
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.3.0/dbhome_1
cd $ORACLE_HOME/appsutil/bin
perl txkGenCDBTnsAdmin.pl -dboraclehome=/u01/app/oracle/product/19.3.0/dbhome_1 -cdbname=upgcdb \
-cdbsid=upgcdb -dbport=1536 -outdir=/u01/app/oracle/product/19.3.0/dbhome_1/appsutil/log
Shut down the CDB
===================
-bash-5.0$ echo $ORACLE_HOME
/u01/app/oracle/product/19.3.0/dbhome_1
sqlplus / as sysdba
SQL> shutdown;
Configure UTL_FILE_DIR
========================
/u01/app/oracle/product/12.1.0/dbhome_1
-bash-5.0$ . UPG_rgdrdb01.env
-bash-5.0$ echo $CONTEXT_FILE
/u01/app/oracle/product/12.1.0/dbhome_1/appsutil/UPG_rgdrdb01.xml
Query :
===========
perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/u01/app/oracle/product/12.1.0/dbhome_1/appsutil/UPG_rgdrdb01.xml -oraclehome=/u01/app/oracle/product/12.1.0/dbhome_1 -outdir=/tmp/txkCfgUtlfileDir -upgradedhome=/u01/app/oracle/product/19.3.0/dbhome_1 -mode=getUtlFileDir
Set the Path:
===============
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=setUtlFileDir [ -skipdirvalidation=Yes ]
mkdir -p /u01/app/oracle/product/19.3.0/temp/UPG
perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/u01/app/oracle/product/12.1.0/dbhome_1/appsutil/UPG_rgdrdb01.xml
-oraclehome=/u01/app/oracle/product/12.1.0/dbhome_1 -outdir=/tmp/txkCfgUtlfileDir -upgradedhome=/u01/app/oracle/product/19.3.0/dbhome_1 -mode=setUtlFileDir
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=setUtlFileDir [ -skipdirvalidation=Yes ]
Ignore the below:
==================
** WARNING: Incorrect value s_applptmp detected on Apps Tier nodes. Please check log for details
set serveroutput on
declare
f1 utl_file.file_type;
begin
f1 := utl_file.fopen('/usr/tmp','test.txt','w');
utl_file.fclose(f1);
end;
/
UPGRADE :
===========
1. check and add oratab entry
2.
alter system set db_recovery_file_dest_size=100G scope=both;
alter system set db_recovery_file_dest='+RECO' scope=both;
alter database flashback on;
CREATE RESTORE POINT BEF_UPGRADE_19c GUARANTEE FLASHBACK DATABASE;
alter system set event EVENT='10946 trace name context forever, level 8454144' scope=spfile;
select name,open_mode,log_mode, flashback_on from gv$database;
$ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
SQL> select name,open_mode,log_mode, flashback_on from gv$database;
NAME OPEN_MODE LOG_MODE FLASHBACK_ON
--------- -------------------- ------------ ------------------
UPG READ WRITE ARCHIVELOG YES
--10 min--
SQL> @/u01/app/oracle/cfgtoollogs/UPG/preupgrade/preupgrade_fixups.sql
remove below parameter from Init file:
*.local_listener='UPG_LOCAL'
Dont enable post recompile after upgrde .
Open VNC: Except recompile other steps will complete in 3 hrs
===========
cd /u01/app/oracle/product/19.3.0/dbhome_1/bin
./dbua -keepevents
recompile - 1 hr with 2800 objects invalid to 35 ( with AFM invalid )
Post Ugrade script: 15 min
=================
@/u01/app/oracle/cfgtoollogs/UPG/preupgrade/postupgrade_fixups.sql
alter trigger SYSTEM.EBS_LOGON compile;
alter system set compatible=’19.0.0' scope=spfile;
alter system set compatible='19.0.0' scope=spfile;
drop restore point BEF_UPGRADE_19C;
Bounce the DB
Perform patch post-install instructions
===========================
SQL>@?/rdbms/admin/dbmsxdbschmig.sql
SQL>@?/rdbms/admin/prvtxdbschmig.plb
applprod@spdrapp:~$ scp -p /u01/app/oracle/fs2/EBSapps/appl/admin/adgrants.sql oracle@rgdrdb01:/u01/app/oracle/product/19.3.0/dbhome_1/appsutil/dba
applprod@spdrapp:~$ scp -p /u01/app/oracle/fs2/EBSapps/appl/ad/12.0.0/patch/115/sql/adctxprv.sql oracle@rgdrdb01:/u01/app/oracle/product/19.3.0/dbhome_1/appsutil/dba
sqlplus "/ as sysdba" @adgrants.sql apps
sqlplus apps/apps @adctxprv.sql manager CTXSYS
sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
grant text datastore access to public;
applprod@spdrapp:~$ scp -p $APPL_TOP/admin/adstats.sql oracle@rgdrdb01:/u01/app/oracle/product/19.3.0/dbhome_1/appsutil/dba
SQL> alter system enable restricted session;
SQL> @adstats.sql >>> 10 min
Convert Database to Multitenant Architecture:
=================================================
cd /u01/app/oracle/product/19.3.0/dbhome_1/appsutil
. ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.3.0/dbhome_1
cd $ORACLE_HOME/appsutil/bin
export ORACLE_SID=TEST
perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/u01/app/oracle/product/19.3.0/dbhome_1 -outdir=$ORACLE_HOME/appsutil/log -appsuser=apps -dbsid=UPG
Syntax:
==========
perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=<ORACLE_HOME> \
-outdir=<ORACLE_HOME>/appsutil/log -appsuser=<apps user> -dbsid=<source SID>
Update the CDB initialization parameters
==========================================
cd /u01/app/oracle/product/12.1.0/dbhome_1/dbs/
cp UPG_initparam.sql UPG_datatop.txt $ORACLE_HOME/dbs
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.3.0/dbhome_1
$ export ORACLE_SID=upgcdb
$ sqlplus "/ as sysdba"
SQL> startup nomount;
SQL> SQL> @/u01/app/oracle/product/19.3.0/dbhome_1/dbs/UPG_initparam.sql
alter system set LOCAL_LISTENER="<hostname>:<port number>" scope=both;
SQL> alter system set LOCAL_LISTENER="rgdrdb01:1536" scope=both;
SQL> shutdown;
SQL> startup;
Check for PDB Violations
========================
perl txkChkPDBCompatability.pl -dboraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -cdbsid=upgcdb -pdbsid=UPG -servicetype=onpremise
select name, con_id, db_unique_name from v$database;
Create the PDB
================
export ORACLE_SID=upgcdb
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.3.0/dbhome_1
cd $ORACLE_HOME/appsutil/bin
perl txkCreatePDB.pl -dboraclehome=/u01/app/oracle/product/19.3.0/dbhome_1 -outdir=/u01/app/oracle/product/19.3.0/dbhome_1/appsutil/log -cdbsid=upgcdb -pdbsid=UPG -dbuniquename=upgcdb -servicetype=onpremise
***ORA-65139: Mismatch Between XML Metadata File And Data File. Database Not Open Read Only (Doc ID 1963139.1) ***
-- Set the ORACLE_SID variable to noncdb.
-- Connect to the noncdb instance.
sqlplus / as sysdba
-- If the instance is up, shut it down first.
shutdown immediate
-- Start up the database in mount exclusive mode.
startup mount exclusive
-- Open the database in read-only mode.
alter database open read only;
exec dbms_pdb.describe(pdb_descr_file=>'/u01/app/oracle/product/19.3.0/dbhome_1/dbs/UPG_PDBDesc.xml');
SQL> create pluggable database pdb2 using '/u02/oracle/noncdb.xml' copy file_name_convert=('+DATA','+NEWDATA');
1. check system and sys password complexity & verify.
Post DB script: (%% This Steps prepares the PDB for EBS %%%%%%%%)
===============
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=/u01/app/oracle/product/19.3.0/dbhome_1
perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=/u01/app/oracle/product/19.3.0/dbhome_1 \
-outdir=/u01/app/oracle/product/19.3.0/dbhome_1/appsutil/log -cdbsid=upgcdb -pdbsid=UPG \
-appsuser=apps -dbport=1536 -servicetype=onpremise
SQL> alter system set temp_undo_enabled=FALSE scope=both;
OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters.
EXIT STATUS: 255
*******FATAL ERROR*******
PROGRAM : (/u01/app/oracle/product/19.3.0/dbhome_1/appsutil/bin/txkPostPDBCreationTasks.pl)
TIME : Wed Oct 14 18:04:49 2020
FUNCTION: main::generateSystemOraPasswd [ Level 1 ]
ERRORMSG: Password file creation did not go through successfully.
*******FATAL ERROR*******
PROGRAM : (/u01/app/oracle/product/19.3.0/dbhome_1/appsutil/bin/txkPostPDBCreationTasks.pl)
TIME : Wed Oct 14 18:04:49 2020
FUNCTION: main::generateSystemOraPasswd [ Level 1 ]
ERRORMSG: Password file creation did not go through successfully.
-bash-5.0$
srvctl config database -d upgcdb
srvctl modify database -d upgcdb -p +DATA
STEPS in APPLICATIONS TIER:
===========================
on both the Patch and Run APPL_TOP
$TNS_ADMIN/tnsnames.ora
upgcdb=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=10.201.1.140)(PORT=1536))
(CONNECT_DATA=
(SERVICE_NAME= UPG)
(INSTANCE_NAME=upgcdb)
)
)
sqlplus sys@UPG
select sys_context('USERENV','CON_NAME') CON_NAME,
sys_context('USERENV','CON_ID') CON_ID,
sys_context('USERENV','DB_NAME') DB_NAME from DUAL;
show con_name
select value from v$parameter where name='utl_file_dir';
/u01/app/oracle/product/19.3.0/temp/UPG,/u01/app/oracle/product/19.3.0/dbhome_1/appsutil/outbound/UPG_rgdrdb01
applprod@spdrapp:~$ grep s_apps_jdbc_connect_descriptor $CONTEXT_FILE
<jdbc_url oa_var="s_apps_jdbc_connect_descriptor">jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=YES)(FAILOVER=YES)(ADDRESS=(PROTOCOL=tcp)(HOST=rgdrdb01.rg.com)(PORT=1536)))(CONNECT_DATA=(SERVICE_NAME=UPG)))</jdbc_url>
applprod@spdrapp:~$ grep s_dbport $CONTEXT_FILE
<dbport oa_var="s_dbport" oa_type="EXT_PORT" base="1521" step="1" range="-1" label="Database Port">1536</dbport>
applprod@spdrapp:~$ vi $CONTEXT_FILE
applprod@spdrapp:~$ grep s_apps_jdbc_connect_descriptor /u01/app/oracle/fs1/inst/apps/UPG_spdrapp/appl/admin/UPG_spdrapp.xml
<jdbc_url oa_var="s_apps_jdbc_connect_descriptor">jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=YES)(FAILOVER=YES)(ADDRESS=(PROTOCOL=tcp)(HOST=rgdrdb01.rg.com)(PORT=1536)))(CONNECT_DATA=(SERVICE_NAME=UPG)))</jdbc_url>
applprod@spdrapp:~$
alter system set service_names='','ebs_patch','UPG_ebs_patch' scope=both sid='*';
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string upgcdb
SQL>
SQL>
SQL> alter system set service_names='upgcdb','UPG_ebs_patch','ebs_UPG','upg','upgcdbXDB' scope=both;
System altered.
SQL> alter system register;
Steps:
=====
SQL> alter System set SERVICE_NAMES='upgcdb' SCOPE=BOTH;
System altered.
SQL> alter system register;
System altered.
-bash-5.0$ lsnrctl stop upgcdb
-bash-5.0$ lsnrctl stop
SQL> ALTER PLUGGABLE DATABASE UPG CLOSE IMMEDIATE;
Pluggable database altered.
COnnect PDB:
==============
-bash-5.0$ . upgcdb_rgdrdb01.env
-bash-5.0$ export ORACLE_PDB_SID=UPG
-bash-5.0$ sqlplus / as sysdba
OPEN PDB:
=========
$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> alter pluggable database <EBS PDB Name> open read write services=all;
CLOSE PDB:
==========
$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> alter pluggable database <EBS PDB Name> close immediate;
col message for a100
col NAME for a20
set lines 1000 pagesi 2000
select name, cause, message, status from PDB_PLUG_IN_VIOLATIONS;