Ash Blogs: EBS DB 19c Upgrade

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

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

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

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> 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;

Ash Blogs

EBS DB 19c Upgrade

No comments:

Post a Comment

Java Upgrade

Labels