This post is to detail the required steps to upgrade Oracle E-Business Suite database from 12c to 19c for EBS Release 12.1.3.

HIGH LEVEL STEPS

· Software Requirements

· Pre-Upgrade Tasks

· 19c Database Installation

· Upgrade the Database to 19c

· After the Database Upgrade

· Convert Database to Multitenant Architecture

Environment Details :

Source Database : 12.1.0.2 (Non CDB)

Target Database : 19.10.0.0 (CDB with one PDB)

EBS Version : 12.1.3

OS : Solaris SPARC 64-Bit

1. 19C DATABASE UPGRADE

Packages Required

The following packages (or later versions) must be installed for oracle database 19c :

pkg://solaris/system/library/openmp

pkg://solaris/compress/unzip

pkg://solaris/developer/assembler

pkg://solaris/developer/build/make

pkg://solaris/system/dtrace

pkg://solaris/system/header

pkg://solaris/system/library

pkg://solaris/system/linker

pkg://solaris/system/xopen/xcu4 (If not already installed as part of standard Oracle Solaris 11 installation)

pkg://solaris/x11/diagnostic/x11-info-clients

pkg://solaris/system/kernel/oracka (Only for Oracle Real Application Clusters installations)

Execute the below as root user to install the recommended packages :

root@oracleebs:~#pkg install oracle-database-preinstall-19c

Execute the below as ora19c user to verify the Package information :

ora19c@oracleebs:~$ pkg list |grep <Package name> (Ex: pkg list|grep assembler )

Kernel Parameters

Below are the Minimum recommended values for oracle database 19c.

Resource Control Minimum Value

project.max-sem-ids 100

process.max-sem-nsems 256

project.max-shm-memory 8 GB

project.max-shm-ids 100 128

tcp_smallest_anon_port 9000

tcp_largest_anon_port 65500

udp_smallest_anon_port 9000

udp_largest_anon_port 65500

Execute the below command to check kernel Parameters :

root@oracleebs:~# prctl -n <Parameter>-i project default

(Ex : prctl -n project.max-sem-ids -i project default )

root@oracleebs:~# prctl $$(It returns all the kernel parameters)

root@oracleebs:~#ipadm show-prop -p smallest_anon_port,largest_anon_port udp

root@oracleebs:~#ipadm show-prop -p smallest_anon_port,largest_anon_port tcp

1.2 Pre-upgrade Tasks

Run Health Check Script

Run the below Health Check Script to Check for Known Problems in Oracle 12c which looks for some known common Data Dictionary problems.

$ su - ora19c

ora19c@oracleebs:~$ . ./TEST_oracleebs.env

ora19c@oracleebs:~$ cd /u03/oracle/scripts

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL> spool hcheck.log

SQL> @/u03/oracle/scripts/hcheck

HCheck Version 07MAY18 on 14-MAY-2021 10:32:08

----------------------------------------------

Catalog Version 12.1.0.2.0 (1201000200)

db_name: TEST

Is CDB?: NO

Catalog Fixed

Procedure Name Version Vs Release Timestamp

Result

------------------------------ ... ---------- -- ---------- --------------

------

.- LobNotInObj ... 1201000200 <= *All Rel* 05/14 10:32:08 PASS

.- MissingOIDOnObjCol ... 1201000200 <= *All Rel* 05/14 10:32:08 PASS

.- SourceNotInObj ... 1201000200 <= *All Rel* 05/14 10:32:08 FAIL

HCKE-0003: SOURCE$ for OBJ# not in OBJ$ (Doc ID 1360233.1)

SOURCE$ has 405 rows for 23 OBJ# values not in OBJ$

.- BadDepends ... 1201000200 <= *All Rel* 05/14 10:34:47 WARN

HCKW-0016: Dependency$ p_timestamp mismatch for VALID objects (Doc ID

1361045.1)

[E] - P_OBJ#=24419 D_OBJ#=24543

[E] - P_OBJ#=24420 D_OBJ#=24544

[E] - P_OBJ#=24421 D_OBJ#=24545

[E] - P_OBJ#=24422 D_OBJ#=24546

[E] - P_OBJ#=24423 D_OBJ#=24547

[E] - P_OBJ#=24429 D_OBJ#=24553

[E] - P_OBJ#=24463 D_OBJ#=24587

[E] - P_OBJ#=24464 D_OBJ#=24588

SQL> spool off;

Fix For Health Check Violation

Check the health check log for any voilation in the source Database :

ora19c@oracleebs:~$ vi /u03/oracle/scripts/hcheck.log

Violation :HCKW-0016: Dependency$ p_timestamp mismatch for VALID objects

Run this query to find the objects with timestamp issue :

set pagesize 10000

column d_name format a20

column p_name format a20

SELECT

do.obj# d_obj,

do.name d_name,

do.type# d_type,

po.obj# p_obj,

po.name p_name,

to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",

to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME",

decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X

FROM sys.obj$ do, sys.dependency$ d, sys.obj$ po

WHERE P_OBJ#=po.obj#(+)

AND D_OBJ#=do.obj#

AND do.status=1 /*dependent is valid*/

AND po.status=1 /*parent is valid*/

AND po.stime!=p_timestamp /*parent timestamp not match*/

ORDER BY 2,1;

- Execute the script above and Compile the Objects returned in the above query.

Once Compiled, Run the Health Check Script again and timestamp mismatch error should not appear this time.

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL> spool hcheck_after_fix.log

SQL> set time on

SQL> set timi on

SQL> @/u03/oracle/scripts/hcheck

HCheck Version 07MAY18 on 14-MAY-2021 11:43:06

----------------------------------------------

Catalog Version 12.1.0.2.0 (1201000200)

db_name: TEST

Is CDB?: NO

Catalog Fixed

Procedure Name Version Vs Release Timestamp

Result

------------------------------ ... ---------- -- ---------- --------------

------

.- LobNotInObj ... 1201000200 <= *All Rel* 05/14 11:43:06 PASS

.- MissingOIDOnObjCol ... 1201000200 <= *All Rel* 05/14 11:43:06 PASS

.- SourceNotInObj ... 1201000200 <= *All Rel* 05/14 11:43:06 FAIL

HCKE-0003: SOURCE$ for OBJ# not in OBJ$ (Doc ID 1360233.1)

SOURCE$ has 405 rows for 23 OBJ# values not in OBJ$

.- ObjNotTrigger ... 1201000200 <= *All Rel* 05/14 11:44:36 PASS

---------------------------------------

14-MAY-2021 11:44:36 Elapsed: 90 secs

---------------------------------------

Found 1 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.

Elapsed: 00:01:30.39

Statement processed.

SQL> spool off

Note : We can safely ignore “HCKE-0003: SOURCE$ for OBJ# not in OBJ$ (Doc ID 1360233.1)SOURCE$ has 405 rows for 23 OBJ# values not in OBJ$”.

Collect Below Pre-checks :

Collect the below from the source Database :

Collect DB Links

Collect Invalid Objects

Collect Indexes

Purge Recycle Bin

The recycle bin must be completely empty before database upgrade.

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL> PURGE DBA_RECYCLEBIN;

TEST Recyclebin purged.

Enable Flashback

Enable Flashback in the source Database to create Guaranteed Restore Point. In case of Upgrade failure, We can rollback the changes to this guranteed restore point and restart the upgrade.

DB must be in archivelog mode to enable Flashback. Follow the below to Enable archivelog mode in the Source DB if it is in noarchivelog mode :

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL> shutdown;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter system set db_recovery_file_dest_size=500G scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='/u01/oracle/TEST/archivelog' scope=both;

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;

System altered.

SQL> alter database flashback on;

Database altered.

SQL> select name,open_mode,flashback_on from v$database;

NAME OPEN_MODE FLASHBACK_ON

--------- -------------------- ------------------

TEST READ WRITE YES

SQL> alter database open;

Database altered.

Create Guarantee Restore Point

Run the query below to create Guranteed Restore Point in Source Database before upgrade :

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL> CREATE RESTORE POINT b4_ebs_prereq_patching GUARANTEE FLASHBACK DATABASE;

Run the below query to check created Restore Points in the database :

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE

FROM V$RESTORE_POINT

WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME

--------------------------------------------------------------------------------

SCN

----------

TIME

---------------------------------------------------------------------------

DATABASE_INCARNATION# GUA STORAGE_SIZE

--------------------- --- ------------

B4_EBS_PREREQ_PATCHING

3.4928E+11

14-MAY-21 12.18.04.000000000 PM

4 YES 1073741824

Backup Database

Backup Full database plus archive logs and control file.

Backup Application

Shutdown the Application Services and Backup Application Codesets

applTEST@oracleebs:~$ cd $ADMIN_SCRIPTS_HOME

applTEST@oracleebs:~$ ./adstpall.sh apps/<apps_pwd>

applTEST@oracleebs:~$ cd /u02/oracle/TEST

applTEST@oracleebs:~$tar cEvf - apps/ inst/ |gzip -c > /u03/oracle/TEST_apps_bkp/TEST_apps_b4_19c_upg.tar.gz

Backup 12c Oracle Home

- Shutdown all the services (db and lisetner) running out of 12c oracle home.

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL> shut immediate;

ora19c@oracleebs:~$lsnrctl stop TEST

ora19c@oracleebs:~$ cd /u02/oracle/TEST/db/tech_st/product/12.1.0

ora19c@oracleebs:~$tar cEvf - dbhome_1/ |gzip -c >/u03/oracle/TEST_OH_BKP/12c_oh_bkp_b4_upgrade.tar.gz

Startup DB and Listener

$ su - ora19c

ora19c@oracleebs:~$ . ./TEST_oracleebs.env

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL> startup;

ora19c@oracleebs:~$lsnrctl start TEST

Enable Maintenance Mode

Run adadmin and select option ‘5 and 1’ to Enable maintenance mode.

$ su – applTEST

$ . ./TEST_oracleebs.env

applTEST@oracleebs:~$ adadmin

5. Change Maintenance Mode

1. Enable Maintenance Mode

Or Execute the below to enable Maintenance mode :

$ sqlplus apps/app5TEST @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE

Apply 12.1.3 Patches

19c DB upgrade Patches :

Below are the List of Patches tobe applied on Application Tier :

8796558

9239090

23569686

27135427

30033914

27102203

28613638

28685719

29178111

29583055

29905536

30370150

30601878

31209544

31406810

applTEST@oracleebs:~$ cd $ADMIN_SCRIPTS_TOP

applTEST@oracleebs:~$ ./adstpall.sh apps/<pwd>

1. Patch 28613638 : (TRACKING BUG FOR EBS 12.1.3 CLONING CHANGES FOR DATABASE 19C)

Pre-requisites :

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

Patch 27102203 : R12.OAM.B

Apply the patch :

applTEST@oracleebs:~$ cd /u03/oracle/patches/12_1_3_patches/28613638

applTEST@oracleebs:~$ time adpatch

Post Patching Steps :

Note : Don’t run the post patching steps now, Execute at once when all the patches have been applied to the Application Tier.

2. Patch 29178111 (TRACKING BUG FOR EBS 12.1.3 AUTOCONFIG CHANGES FOR DATABASE 19C)

Pre-requisites :

Patch 27135427 : R12.TXK.B.delta.4

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/12_1_3_patches/29178111

ora19c@oracleebs:~$ time adpatch

3. Patch 29583055 (19C DB DE-SUPPORTING UTL_FILE)

Pre-requisites : NONE

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/12_1_3_patches/29583055

ora19c@oracleebs:~$ time adpatch

Post Patching Steps : NONE

4. Patch 29905536 (GRANT DBMS_SERVICE MISSING IN ADGRANTS.SQL - EBS 12.1.3)

Pre-requisites :

Patch 7461070 : R12.AD.B.1

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

Patch 8919491 : R12.ATG_PF.B.delta.3

Run adgrants.sql as ora19c user before applying this patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/12_1_3_patches/29905536/admin/

ora19c@oracleebs:~$cp adgrants.sql $ORACLE_HOME/rdbms/admin

ora19c@oracleebs:~$ sqlplus /nolog

SQL> conn / as sysdba

SQL> @$ORACLE_HOME/rdbms/admin/adgrants.sql APPS

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/12_1_3_patches/29905536

ora19c@oracleebs:~$ time adpatch

workers : 64

Post Patching Steps : NONE

5. Patch 30033914 (INTEROPERABILITY PATCH FOR EBS 1213 ON RDBMS 19C (19.3+) )

Pre-requisites :

Patch 7461070 : R12.AD.B.1

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

Patch 8919491 : R12.ATG_PF.B.delta.3

Patch 9245674: R12.SCM_PF.B.3

Run adgrants.sql as ora19c user before applying this patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/12_1_3_patches/30033914/admin/

ora19c@oracleebs:~$ sqlplus /nolog

SQL> conn / as sysdba

SQL>@adgrants.sql APPS

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/12_1_3_patches/30033914

ora19c@oracleebs:~$ time adpatch

workers : 64

Post Patching Steps : NONE

6. Patch 30370150 (ORA-01720: GRANT OPTION DOES NOT EXIST FOR 'SYS._BASE_USER')

Pre-requisites :

Patch 7461070 : R12.AD.B.1

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

Patch 8919491 : R12.ATG_PF.B.delta.3

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/12_1_3_patches/30370150

ora19c@oracleebs:~$ time adpatch

workers : 64

Post Patching Steps : NONE

7. Patch 30601878 (TCH19C :: MERGE OF RECENT TXK FIXES REQUIRED FOR 19C)

Pre-requisites :

Patch 27135427 : R12.TXK.B.delta.4

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/12_1_3_patches/30601878

ora19c@oracleebs:~$ time adpatch

Post Patching Steps :

8. Patch 31209544 (CONSOLIDATED PATCH FOR EBS 12.1 - 19C SUPPORT)

Pre-requisites :

Patch 27135427 : R12.TXK.B.delta.4

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/12_1_3_patches/31209544

ora19c@oracleebs:~$ time adpatch

Post Patching Steps :

9. Patch 31406810 (PERF ISSUE ON 19C WHEN RUNNING AUTOCONFIG ON DB TIER)

Pre-requisites :

Patch 27135427 : R12.TXK.B.delta.4

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/12_1_3_patches/31406810

ora19c@oracleebs:~$ time adpatch

Post Patching Steps :

Disable Maintenance mode

Run adadmin and select option ‘5 and 2’ to Disable maintenance mode.

5. Change Maintenance Mode

2. Disable Maintenance Mode

Execute the below to disable Maintenance mode :

$ sqlplus apps/app5TEST @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

Patch Post Install Tasks

Perform the below patch post installation steps for patches :29178111, 28613638, 30601878 and 31209544.

- Run autoconfig on Apps tier

applTEST@oracleebs:~$cd $ADMIN_SCRIPTS_HOME

applTEST@oracleebs:~$ ./adautocfg.sh

- Generate appsutil.zip

Execute the below perl script to create appsutil.zip under $APPL_TOP/admin/out

applTEST@oracleebs:~$perl $AD_TOP/bin/admkappsutil.pl

Starting the generation of appsutil.zip

Log file located at /u02/oracle/TEST/inst/apps/TEST_oracleebs/admin/log/MakeAppsUtil_03112035.log

Output located at /u02/oracle/TEST/inst/apps/TEST_oracleebs/admin/out/appsutil.zip

MakeAppsUtil completed successfully.

- Copy appsutil.zip to DB oracle home and unzip it

applTEST@oracleebs:~$ cp /u02/oracle/TEST/inst/apps/TEST_oracleebs/admin/out/appsutil.zip /tmp

As ora19c User,

ora19c@oracleebs:~$ cp /tmp/appsutil.zip $ORACLE_HOME

ora19c@oracleebs:~$ cd $ORACLE_HOME

ora19c@oracleebs:~$ mv appsutil appsutil_bkp_b4_upg

ora19c@oracleebs:~$unzip -o appsutil.zip

- Run Autoconfig in DB Tier.

ora19c@oracleebs:~$ cd $ORACLE_HOME/appsutil/scripts/TEST_oracleebs

ora19c@oracleebs:~$ ./adautocfg.sh

10.1.2 Oracle Home Patch

Apply the 10.1.0.5 version of Patch 6400501 to the iAS 10.1.2 Applications tier Oracle home.

Run Autoconfig on Application Tier

applTEST@oracleebs:~$cd $ADMIN_SCRIPTS_HOME

applTEST@oracleebs:~$./adautocfg.sh

Maintain Snapshot Information

RUN ADADMIN and Choose options below to ‘Update Current View Snapshot’

2. Maintain Applications Files menu

4. Maintain snapshot information

2. Update current view snapshot

1. Update Complete APPL_TOP

Time Taken : 30 Min

Create init Parmater file

This is to be executed in 12c oracle home.

ora19c@oracleebs:~$cd /u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1/appsutil

$./txkSetCfgCDB.env dboraclehome=/u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1

Oracle Home being passed: /u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1

ora19c@oracleebs:~$ export ORACLE_SID=TEST

Fix for FATAL ERROR :

*******FATAL ERROR*******

PROGRAM : (txkOnPremPrePDBCreationTasks.pl)

TIME : Thu Mar 11 22:22:09 2021

FUNCTION: main::getDBHostDetails [ Level 1 ]

ERRORMSG: Unable to evaluate DB host and domain.

If Script txkOnPremPrePDBCreationTasks.pl returns the above error perform the fix below :

ora19c@oracleebs:~$ vi txkOnPremPrePDBCreationTasks.pl

Search for word ‘nslookup’ (Approximately at line 1459 )

Comment the line below :

#$temp_hostname = `nslookup \`hostname\` | awk \'BEGIN {rv=1;} /^Name:/ {print \$2; exit rv=0;} END {exit rv;}\'`;

Hardcode the below in txkOnPremPrePDBCreationTasks.pl script.

$temp_hostname = "hostname.domainname";

EX : $temp_hostname = "oracleebs.ebs.com";

$perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1 -outdir=/u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1/appsutil/log -appsuser=apps -dbsid=TEST -skipdbshutdown=yes

Enter the APPS schema username: ? apps

Enter the APPS Password:

Above commands create the below files :

$ORACLE_HOME/dbs/TEST_initparam.sql and TEST_datatop.txt files

Validate The Application

Start the Application services and validate the application after EBS Patching.

applTEST@oracleebs:~$ cd $ADMIN_SCRIPTS_HOME

applTEST@oracleebs:~$ ./adstrtall.sh apps

1.3 19c Database Installation

Prepare to create the 19c Oracle home

Create the Stage for 19c :

ora19c@oracleebs:~$ mkdir -p /u02/oracle/TEST/19.0.0/dbhome_1

ora19c@oracleebs:~$ cd /u02/oracle/TEST/19.0.0/dbhome_1

ora19c@oracleebs:~$ unzip -q /u03/oracle/Stage_19_3/SOLARIS.SPARC64_193000_db_home.zip

Note : The 19c Oracle home must be installed on the database server node in a different directory from the current Oracle home.

Install Oracle Database 19c

ora19c@oracleebs:~$export ORACLE_HOME=/u02/oracle/TEST/19.0.0/dbhome_1

ora19c@oracleebs:~$ export JAVA_OPTIONS='-Dsun.java2d.xrender=false'

ora19c@oracleebs:~$ cd $ORACLE_HOME

ora19c@oracleebs:~$ ./runInstaller

- Select the option to Install software Only and click Next

- In the Select Configuration Option screen, select Create and configure a single instance database and click Next.

- Choose the Enterprise Edition installation type and click Next.

- Enter ORACLE_BASE location and click Next

- Uncheck the button “Automatically run configuration scripts” to run root.sh Manually and click Next

Run root.sh Manually when the runInstaller prompts for root.sh

root@oracleebs:~# /u02/oracle/TEST/19.0.0/dbhome_1/root.sh

Set the below Env Variables after installation :

export ORACLE_HOME=/u02/oracle/TEST/19.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PERL5LIB=$ORACLE_HOME/perl/lib/site_perl/5.28.1

export PATH=$PATH:$ORACLE_HOME/OPatch

export PATH=$PATH:/usr/ccs/bin

Apply additional 19c Oracle Database patches

Apply the below opatches to 19c Oracle Home :

Patch 32218454 (OPatch utility version 12.2.0.1.23 or later to apply this patch)

Patch 32067171 (OPatch utility version 12.2.0.1.23 or later to apply this patch)

Patch 29867728 (Prerequisite Patch 32218454 should be applied before this patch)

Patch 31405300 (Prerequisite Patch 32218454 should be applied before this patch)

Patch 31424070 (Prerequisite Patch 32218454 should be applied before this patch)

OPatch Upgrade :

Copy latest OPatch (12.2.0.1.23) to 19c Oracle Home and unzip it to upgrade OPatch.

ora19c@oracleebs:~$ cd /u02/oracle/TEST/19.0.0/dbhome_1/

ora19c@oracleebs:~$ mv OPatch OPatch_old

ora19c@oracleebs:~$ cd /u03/oracle/patches/DB_oracle_home_patches/

ora19c@oracleebs:~$ cp p6880880_122010_SOLARIS64.zip /u02/oracle/TEST/19.0.0/dbhome_1/

ora19c@oracleebs:~$ cd /u02/oracle/TEST/19.0.0/dbhome_1/

ora19c@oracleebs:~$ unzip p6880880_122010_SOLARIS64.zip

ora19c@oracleebs:~$ cd OPatch

ora19c@oracleebs:~$ opatch version

OPatch Version: 12.2.0.1.24

OPatch succeeded.

ora19c@oracleebs:~$export PATH=$PATH:/usr/ccs/bin

ora19c@oracleebs:~$export PATH=$PATH:$ORACLE_HOME/OPatch

Note : Check /usr/ccs/bin is included PATH variable before Patching.

1. Patch 32218454 (Database Release Update 19.10.0.0.210119)

Pre-requisites :

OPatch utility version 12.2.0.1.23 or later to apply this patch

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/DB_oracle_home_patches/32218454

ora19c@oracleebs:~$ opatch apply

Post Patching Steps :

ora19c@oracleebs:~$ sqlplus /nolog

SQL>Connect / as sysdba

SQL>startup

SQL>quit

ora19c@oracleebs:~$ cd $ORACLE_HOME/OPatch

ora19c@oracleebs:~$ ./datapatch –verbose

Note : Don’t Execute the post patching steps now for any of the Patch. Post patching tasks should be executed after Database Upgrade. Post Patching steps covered later part of this Guide.

2. Patch 31405300 ( ORACLE DATABASE Patch for Bug# 31405300 for Solaris-64 Platforms)

Pre-requisites :

Prerequisite Patch 32218454 should be applied before this patch.

OPatch utility version 12.2.0.1.23 or later to apply this patch

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/DB_oracle_home_patches/31405300

ora19c@oracleebs:~$time opatch apply

Post Patching Steps : NONE

3. Patch 31424070 (ORACLE DATABASE Patch for Bug# 31424070 for Generic Platforms)

Pre-requisites :

Prerequisite Patch 32218454 should be applied before this patch

OPatch utility version 12.2.0.1.23 or later to apply this patch

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/DB_oracle_home_patches/31424070

ora19c@oracleebs:~$time opatch apply

Post Patching Steps :

ora19c@oracleebs:~$ cd $ORACLE_HOME/OPatch

ora19c@oracleebs:~$ datapatch

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL> ?/rdbms/admin/utlrp.sql

4. Patch 29867728 (OPTIMIZER Patch for Bug# 29867728 for Solaris-64 )

Pre-requisites :

Prerequisite Patch 32218454 should be applied before this patch

OPatch utility version 12.2.0.1.23 or later to apply this patch

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/DB_oracle_home_patches/29867728

ora19c@oracleebs:~$ time opatch apply

Post Patching Steps : NONE

5. Patch 32067171 (Oracle JavaVM Component Release Update 19.10.0.0.210119)

Pre-requisites :

Use the OPatch utility version 12.2.0.1.19 or later to apply this patch

Apply the patch :

ora19c@oracleebs:~$ cd /u03/oracle/patches/DB_oracle_home_patches/32067171

ora19c@oracleebs:~$time opatch apply

Post Patching Steps :

ora19c@oracleebs:~$ sqlplus /nolog

SQL> connect / as sysdba

ora19c@oracleebs:~$ startup upgrade

ora19c@oracleebs:~$ quit

ora19c@oracleebs:~$ cd $ORACLE_HOME/OPatch

ora19c@oracleebs:~$./datapatch -verbose

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL> shutdown

SQL> startup

SQL> ?/rdbms/admin/utlrp.sql

Create nls/data/9idata directory

Execute the below perl script :

ora19c@oracleebs:~$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl

Creating directory /u02/oracle/TEST/19.0.0/dbhome_1/nls/data/9idata ...

Copying files to /u02/oracle/TEST/19.0.0/dbhome_1/nls/data/9idata...

Copy finished.

Please reset environment variable ORA_NLS10 to /u02/oracle/TEST/19.0.0/dbhome_1/nls/data/9idata!

Create appsutil.zip and copy it to the database tier

Execute the below perl script to create appsutil.zip under $APPL_TOP/admin/out

applTEST@oracleebs:~$perl $AD_TOP/bin/admkappsutil.pl

Starting the generation of appsutil.zip

Log file located at /u02/oracle/TEST/inst/apps/TEST_oracleebs/admin/log/MakeAppsUtil_03121253.log

output located at /u02/oracle/TEST/inst/apps/TEST_oracleebs/admin/out/appsutil.zip

MakeAppsUtil completed successfully.

- Copy appsutil.zip to 19c DB oracle home and unzip it

applTEST@oracleebs:~$cp /u02/oracle/TEST/inst/apps/TEST_oracleebs/admin/out/appsutil.zip /tmp

As ora19c user,

ora19c@oracleebs:~$cp /tmp/appsutil.zip $ORACLE_HOME

ora19c@oracleebs:~$ cd $ORACLE_HOME

ora19c@oracleebs:~$ unzip appsutil.zip

Copy the orai18n.jar file (JRE Installation)

Run the following command in 19c to copy the orai18n.jar file to the $ORACLE_HOME/jdk/jre/lib/ext

ora19c@oracleebs:~$ cp $ORACLE_HOME/jlib/orai18n.jar $ORACLE_HOME/jdk/jre/lib/ext

ora19c@oracleebs:…u02/oracle/TEST/19.0.0/dbhome_1$ ls -ltr $ORACLE_HOME/jdk/jre/lib/ext

total 18994

-rw-r--r-- 1 ora19c TEST 69303 Jan 18 17:39 zipfs.jar

-rw-r--r-- 1 ora19c TEST 76298 Jan 18 17:39 ucrypto.jar

-rw-r--r-- 1 ora19c TEST 283206 Jan 18 17:39 sunpkcs11.jar

-rw-r--r-- 1 ora19c TEST 285868 Jan 18 17:39 sunjce_provider.jar

-rw-r--r-- 1 ora19c TEST 43191 Jan 18 17:39 sunec.jar

-rw-r--r-- 1 ora19c TEST 2024156 Jan 18 17:39 nashorn.jar

-rw-r--r-- 1 ora19c TEST 815 Jan 18 17:39 meta-index

-rw-r--r-- 1 ora19c TEST 1179462 Jan 18 17:39 localedata.jar

-rw-r--r-- 1 ora19c TEST 44516 Jan 18 17:39 jaccess.jar

-rw-r--r-- 1 ora19c TEST 8286 Jan 18 17:39 dnsns.jar

-rw-r--r-- 1 ora19c TEST 3860563 Jan 18 17:39 cldrdata.jar

-rw-r--r-- 1 ora19c TEST 1661488 May 17 16:25 orai18n.jar

Create the CDB

On the database server node, export the variables below:

export ORACLE_HOME=/u02/oracle/TEST/19.0.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PERL5LIB=$ORACLE_HOME/perl/lib/site_perl/5.28.1

ora19c@oracleebs:~$cd $ORACLE_HOME/bin

ora19c@oracleebs:~$./dbca

- Run DBCA to create the container database (CDB).

- Click Create a Database and click Next

- Select Advanced configuration and click Next

- Select General Purpose or Transaction Processing and click Next

- Enter the values below and click next

Global Database Name : CDBTEST

CDB SID : CDBTEST

- Check “Create as containser database” and “Create an empty container Database”

- Enter DATA_TOP locaiton for CDB and click Next

- Uncheck the “FRA” and “Archiving” and click next

- Uncheck “Create new listener” and click Next

- Uncheck “Database Vault” and “Label Security”, Click Next

- Click Memory and Set the SGA and PGA to 2G and 1G respectively.

- Click Sizing and set the processes to 1500.

- Click “”Character Sets” and select “AL32UTF8” as Database Characterset

- Click Connection Mode and select “Dedicated Server Mode”

- Uncheck “Configure Enterprise Manager” and click Next

- Check “Create Database” and click “Customize Storage Locations” to set redo log file size.

- Click Each Redo log file and set 1G and click Next

- Click Finish to create CDB.

Run datapatch on CDB

Note : Datapatch also should have been installed by DBCA in the previous step.

ora19c@oracleebs:~$ export ORACLE_SID=CDBTEST

ora19c@oracleebs:~$ export ORACLE_HOME=/u02/oracle/TEST/19.0.0/dbhome_1

ora19c@oracleebs:~$time $ORACLE_HOME/OPatch/datapatch

Create the CDB MGDSYS schema

Use SQL*Plus to connect to the CDB as SYSDBA and run ORACLE_HOME/rdbms/admin/catmgd.sql script. This creates the new MGDSYS schema on the CDB.

ora19c@oracleebs:~$ sqlplus "/ as sysdba" @?/rdbms/admin/catmgd.sql

Create the CDB TNS files

To be executed in 19c oracle home.

On the database server node, run the following perl script to generate the required TNS files. Note that this script does not create a listener.

ora19c@oracleebs:~$ cd$ORACLE_HOME/appsutil

ora19c@oracleebs:~$ . ./txkSetCfgCDB.env dboraclehome=/u02/oracle/TEST/19.0.0/dbhome_1

ora19c@oracleebs:~$ cd $ORACLE_HOME/appsutil/bin

Perform the fix below for error “Unable to evaluate DB host and domain.”

ora19c@oracleebs:~$vi txkGenCDBTnsAdmin.pl

Search for word ‘nslookup’

Comment the line below :

#$temp_hostname = `nslookup \`hostname\` | awk \'BEGIN {rv=1;} /^Name:/ {print \$2; exit rv=0;} END {exit rv;}\'`;

Hardcode the below in txkGenCDBTnsAdmin.plscript.

$temp_hostname = "hostname.domainname";

EX : $temp_hostname = "oracleebs.ebs.com";

ora19c@oracleebs:~$time perl txkGenCDBTnsAdmin.pl -dboraclehome=/u02/oracle/TEST/19.0.0/dbhome_1 -cdbname=CDBTEST-cdbsid=CDBTEST -dbport=1524 -outdir=$ORACLE_HOME/appsutil/log

Shut down the CDB

Connect to CDB and issue the below to shutdown the CDB

ora19c@oracleebs:~$ sqlplus "/ as sysdba"

SQL> shutdown immediate;

1.4 Upgrade the Database to 19c

Store the UTL_FILE_DIR parameter values

To retrieve the directory path values from the source UTL_FILE_DIR database initialization parameter:

Source 12c Oracle home env file :

ora19c@oracleebs:~$cd /u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1

ora19c@oracleebs:~$. ./TEST_oracleebs.env

ora19c@oracleebs:…ech_st/product/12.1.0/dbhome_1$ echo $CONTEXT_FILE

/u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1/appsutil/TEST_oracleebs.xml

Run the txkCfgUtlfileDir.pl script in getUtlFileDir mode using the following command:

ora19c@oracleebs:~$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl –contextfile=$CONTEXT_FILE-oraclehome=/u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1-outdir=/usr/tmp/TEST-upgradedhome=/u02/oracle/TEST/19.0.0/dbhome_1-mode=getUtlFileDir -servicetype=onpremise

Enter the full path of Context File: ? /u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1/appsutil/TEST_oracleebs.xml

Enter the APPS Password:

Note : Above script retrieves the directory paths stored in the UTL_FILE_DIR database init file, modifies them to prepare them for use in 19c, and creates a text file named TEST_utlfiledir.txt in the $ORACLE_HOME/dbs directory with the list of modified directory paths.

- If the list of directories in the TEST_utlfiledir.txt file still includes any symbolic links, edit the file to replace the symbolic links with physical directory paths

To store the directory path values in the database:

Create the following directory path:

ora19c@oracleebs:~$mkdir -p /u02/oracle/TEST/19.0.0/temp/TEST

vi $ORACLE_HOME/dbs/TEST_utlfiledir.txt

Comment the exisiting directory

#/usr/tmp/TEST

Update the below Directory Path :

/u02/oracle/TEST/19.0.0/temp/TEST

Note : Make sure same Directory value set in $APPLPTMP in application context file.

applTEST@oracleebs:~$ vi $CONTEXT_FILE

Update the below :

<APPLPTMP oa_var="s_applptmp" osd="UNIX">/u02/oracle/TEST/19.0.0/temp/TEST</APPLPTMP>

applTEST@oracleebs:~$ cd $ADMIN_SCRIPTS_HOME

applTEST@oracleebs:…apps/TEST_oracleebs/admin/scripts$ ./adautocfg.sh

Note - Source 12.1.3 apps env file and check $APPLPTMP returns the updated value.

applTEST@oracleebs:…apps/TEST_oracleebs/admin/scripts$ cd $APPL_TOP

applTEST@oracleebs:…2/oracle/TEST/apps/apps_st/appl$ . ./APPSTEST_oracleebs.env

applTEST@oracleebs:…2/oracle/TEST/apps/apps_st/appl$ echo $APPLPTMP

/u02/oracle/TEST/19.0.0/temp/TEST

Source 12c Oracle home env file :

ora19c@oracleebs:~$cd$HOME

ora19c@oracleebs:~$. ./TEST_oracleebs.env

Run the txkCfgUtlfileDir.pl script in setUtlFileDir mode using the following command:

ora19c@oracleebs:~$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=/u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1/appsutil/TEST_oracleebs.xml -oraclehome=/u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1 -outdir=/u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1/appsutil/log -upgradedhome=/u02/oracle/TEST/19.0.0/dbhome_1 -mode=setUtlFileDir -servicetype=onpremise

Enter the APPS Password:

Enter the SYSTEM Password:

Shut down the application tier server processes

applTEST@oracleebs:~$cd $ADMIN_SCRIPTS_HOME

applTEST@oracleebs:~$./adstpall.sh

Create Gurantted Restore Point

Create a Restore Point before executing dbua :

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL>CREATE RESTORE POINT bef_19c_upgrade GUARANTEE FLASHBACK DATABASE;

Drop SYS.ENABLED$INDEXES

Execute the below to drop SYS.ENABLED$INDEXES table,

ora19c@oracleebs:~$ sqlplus "/ as sysdba"

SQL> drop table sys.enabled$indexes;

ERROR at line 1:

ORA-00942: table or view does not exist

Shut down the database listener

Shut down the 12c database listener.

ora19c@oracleebs:~$ lsnrctl stop TEST

Note: Verify that you do not have the LOCAL_LISTENER initialization parameter set.

Unset the LOCAL_LISTENER in the source database :

ora19c@oracleebs:~$sqlplus / as sysdba

SQL>ALTER SYSTEM SET LOCAL_LISTENER='' ;

SQL>show parameter local_listener;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

local_listener string

Run preupgrade utility

One of the first step that should be taken prior to an Oracle Database Upgrade is determining any issues the upgrade may present to your database.

Source 12c DB env file and Execute the below :

$ cd /u03/oracle/scripts

ora19c@oracleebs:~$time $ORACLE_HOME/jdk/bin/java -jar /u02/oracle/TEST/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE TEXT DIR $HOME

==================

PREUPGRADE SUMMARY

==================

/export/home/ora19c/preupgrade.log

/export/home/ora19c/preupgrade_fixups.sql

/export/home/ora19c/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups

@/export/home/ora19c/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@/export/home/ora19c/postupgrade_fixups.sql

SQL> @/export/home/ora19c/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script

Version: 19.0.0.0.0 Build: 1

Generated on: 2021-05-18 12:09:33

For Source Database: TEST

Source Database Version: 12.1.0.2.0

For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further TEST Action

------ ------------------------ ---------- --------------------------------

1. purge_recyclebin YES None.

2. parameter_obsolete NO Manual fixup recommended.

3. tablespaces NO Manual fixup recommended.

4. invalid_objects_exist NO Manual fixup recommended.

5. exclusive_mode_auth NO Manual fixup recommended.

6. olap_page_pool_size NO Manual fixup recommended.

7. case_insensitive_auth NO Manual fixup recommended.

8. mv_refresh NO Manual fixup recommended.

9. hidden_params NO Informational only.

Further action is optional.

10. dictionary_stats YES None.

11. trgowner_no_admndbtrg YES None.

12. parameter_deprecated NO Informational only.

Further action is optional.

13. rman_recovery_version NO Informational only.

Further action is optional.

Manual Fixups Tasks

Peform the below manual Pre-upgrade tasks before running the 19c Upgrade.

1. Remove the below parameters from init file :

*.o7_dictionary_accessibility=FALSE#MP

utl_file_dir

*.olap_page_pool_size=4194304

_system_trig_enabled

_sort_elimination_cost_ratio

_b_tree_bitmap_plans

_fast_full_scan_enabled

_like_with_bind_as_equality

_optimizer_autostats_job

_trace_files_public

2. Ensure there is adequate space in APPS_TS_TX_DATA tablespace for the upgrade.

Execute the below Query and Make sure Max size is greater than used Size :

SQL> SELECT TABLESPACE_NAME,SUM(bytes)/1024/1024/1024 "USED GB",SUM(maxbytes)/1024/1024/1024 "MAX GB"FROM TEST_data_filesWHERE tablespace_name ='APPS_TS_TX_DATA'GROUP BYTABLESPACE_NAME ;

Execute the below Query and make sure there is Enough space in APPS_TS_TX_DATA :

SQL> select tablespace_name, sum(bytes)/(1024*1024*1024) "Free GB"from TEST_free_space WHERE tablespace_name='APPS_TS_TX_DATA' group by tablespace_name;

3. Update ALLOWED_LOGON_VERSION_SERVER:

Perform the steps below to fix Preupgrade Action number 2 :

Add the parameter below to allow the users who have assigned with 10G password version.

Add the parameter below in sqlnet.ora :

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

4. Refresh MaterializedView :

Perform the steps below to fix Preupgrade Action number 4 :

Execute the below Query to find Materialized Views to be refreshed :

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL>alter session set nls_date_Format = 'hh24:mi:ss dd.mm.yyyy';

SQL>select mview_name, last_refresh_date, last_refresh_end_time from all_mviews

where last_refresh_date IS NULL;

Execute the below Query to Refresh MVIEW :

SQL>DECLARE

v_number_of_failures NUMBER(12) := 0;

BEGIN

DBMS_MVIEW.REFRESH_ALL_MVIEWS(v_number_of_failures,'C','', TRUE, FALSE);

END;

Note : After performing Performing Preupgrade Manual Tasksx, Re-Run the preupgrade.jar script and make sure none of the tasks have ‘Manual Fixup recommended’ in TEST Action Column. We can safely ignore preupgradecaction remedy for invalid objects.

Prepare to upgrade

Make sure the following initialization parameters before the upgrade:

- Comment out all the deprecated initalization parameters.

- Unset the olap_page_pool_size initialization parameter.

- Set the PGA_AGGREGATE_TARGET initialization parameter to at least 10G.

- Set the SGA_TARGET initialization parameter to at least 2G.

- Add the EventParameter in pfile: event='10946 trace name context forever, level 8454144'

Ensure that the oratab file contains an entry for the database to be upgraded :

ora19c@oracleebs:~$cat /var/opt/oracle/oratab

TEST:/u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1:N

The above 12c oracle home entry should present in oratab.

Note: The preupgrade.jar log file provides instructions to remove the OLAP catalog (amd_exists). It does not actually remove the OLAP catalog. Run the $ORACLE_HOME/olap/admin/catnoamd.sql script manually to remove the OLAP catalog.

$ cd $HOME

ora19c@oracleebs:~$ cp preupgrade.log preupgrade.log_pre

ora19c@oracleebs:~$ cp preupgrade_fixups.sql preupgrade_fixups.sql_pre

ora19c@oracleebs:~$ cp postupgrade_fixups.sql postupgrade_fixups.sql_pre

ora19c@oracleebs:~$ time $ORACLE_HOME/jdk/bin/java -jar /u02/oracle/TEST/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE TEXT DIR $HOME

Note : Only the below were remaining before the upgrade.

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further TEST Action

------ ------------------------ ---------- --------------------------------

1. invalid_objects_exist NO Manual fixup recommended.

2. exclusive_mode_auth NO Manual fixup recommended.

3. mv_refresh NO Manual fixup recommended.

4. underscore_events NO Informational only.

Further action is optional.

5. rman_recovery_version NO Informational only.

Further action is optional.

Apply Extra Fixes for 19c Upgrade

Below errors were occurred during the 19c upgrade. So Apply the Extra Fixes on Source Database before running DBUA. We are applying these Extra fixes for the errors which are already identified in the test upgrade. Double check whether these fixes are really applicable to your environment before you apply them.

-Source 12c env and apply these fixes.

$ su - ora19c

ora19c@oracleebs:~$ . ./TEST_oracleebs.env

1. Change Default TEMP Tablespace :

Error : ORA-00959: tablespace 'TEMP1' does not exist

SQL> select file_name,tablespace_name from TEST_temp_files;

FILE_NAME

--------------------------------------------------------------------------------

TABLESPACE_NAME

------------------------------

/u02/oracle/TEST/data/TEST/datafile/temp03.dbf

TEMP3

SQL> CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '/u02/oracle/TEST/data/TEST/datafile/temp01.dbf' size 2000M reuse extent management localuniform size 128K;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

Database altered.

2. DROP Index "IDX$$_27FE500AC" :

Below Error encountered while 19c Upgrade, Apply the fix below before DBUA :

ORA-30556: either functional or bitmap join index is defined on the column to be modified

Important Note Find out the problematic index and drop it before DBUA. In my case it was "IDX$$_27FE500AC". you may have to done your own research to find the index. Also apply the extra fix only if you encounter with above said error.

ora19c@oracleebs:~$ cd /u03/oracle/scripts/

Connect as sysdba and execute the below to Backup the index before dropping it

ora19c@oracleebs:~$sqlplus / as sysdba

Get the Index Metadata before Dropping it.

SQL> @get_ddl_index.sql applsys AQ$_FND_CP_GSM_OPP_AQTBL_S

Drop The Index :

SQL>DROP INDEX "APPLSYS"."IDX$$_27FE500AC";

Create Gurantted Restore Point

Create a Restore Point before executing dbua :

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL>CREATE RESTORE POINT aft_preupg_fix_bef_dbua GUARANTEE FLASHBACK DATABASE;

Upgrade the database instance

Invoke DBUA using the following command. The keepEvents parameter allows events in the preupgraded database to be enabled during the upgrade.

ora19c@oracleebs:~$cd /u02/oracle/TEST/19.0.0/dbhome_1/bin

ora19c@oracleebs:~$ dbua -keepEvents

- Disregard warnings related to Network ACLs. AutoConfig manages all the Oracle E-Business Suite Network ACLs.

- When upgrading all statistics tables, note that Oracle E-Business Suite has only one statistics table (APPLSYS.FND_STATTAB) that needs to be upgraded

- Select TEST as source Database and click Next

- We can safely Ignore the below warnings since they are taken care in preupgrade tasks and click next

- Check ‘Enable Parallel Upgrade’, ‘Recompile Invalids’ and ‘Upgrade Timezone Data’ and click next

- Check own Backup and Restore Strategy and click Next

- Uncheck the CDB and click next

- Uncheck Enterprise Manager and click next

- Click Finish to upgrade the Database.

Make sure the below after the upgrade :

- The OLAP Catalog Component (AMD) may have "OPTION OFF" or "REMOVED" status in the TEST_registry table. Oracle E-Business Suite has no dependencies on AMD and so this is acceptable.

- Run utlrp and compile invalid objects

ora19c@oracleebs:~$ export ORACLE_SID=TEST

ora19c@oracleebs:~$ export ORACLE_HOME=/u02/oracle/TEST/19.0.0/dbhome_1

- Check the component Status after the upgrade.

SQL> col comp_id format a20

SQL> col COMP_NAME format a30

SQL> set lines 100

SQL> set pages 100

SQL>select comp_id,comp_name,status from TEST_registry;

Fix to update Compatibility Parameter

Error : ORA-38880: Cannot advance compatibility from 12.1.0.0.0 to 19.0.0.0.0 due to

guaranteed restore points

Execute the query below to check available Guaranteed restore points in the database :

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM v$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

Modify initialization parameters after the upgrade

- If you previously had the SEC_CASE_SENSITIVE_LOGON initialization parameter set to FALSE, re-enable the parameter.

- Set COMPATIBLE to 19.0.0.

- Revert PGA_AGGREGATE_TARGETback to the original value.

Perform patch post-install instructions in 19c

We can perform Patch post installation instructions since the db is upgraded.

Note: Several patches may require datapatch to be run. This only needs to be run once after the last patch has been applied.

ora19c@oracleebs:~$sqlplus /nolog

SQL>Connect / as sysdba

SQL> shut immediate;

SQL>startup upgrade

SQL>quit

ora19c@oracleebs:~$cd $ORACLE_HOME/OPatch

ora19c@oracleebs:~$ time ./datapatch -verbose

ora19c@oracleebs:…cle/TEST/19.0.0/dbhome_1/OPatch$

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL> shutdown immediate

SQL> startup

1.5 After the 19c Database Upgrade

Run adgrants.sql

Copy $APPL_TOP/admin/adgrants.sql DB node and execute the below script :

ora19c@oracleebs:~$ su - applTEST

applTEST@oracleebs:~$ cd$APPL_TOP/admin

applTEST@oracleebs:~$cp adgrants.sql /tmp

su – ora19c

ora19c@oracleebs:~$cp /tmp/adgrants.sql$ORACLE_HOME/rdbms/admin/

ora19c@oracleebs:~$ cd$ORACLE_HOME/rdbms/admin

sqlplus "/ as sysdba" @adgrants.sql APPS

Note: We can safely Ignore ORA-00942 errors while running adgrants.sql if occurs.

Compile invalid objects

ora19c@oracleebs:~$ sqlplus / as sysdba

SQL> select count(*) from TEST_objects where status='INVALID';

ora19c@oracleebs:~$sqlplus apps

SQL>exec sys.utl_recomp.recomp_parallel(16);

Run Post Upgrade Fix-up Script

Run the postupgrade scripts that the Pre-Upgrade Information Tool generates to complete fixups of upgraded database.

ora19c@oracleebs:~$ sqlplus / as sysdba@/export/home/ora19c/postupgrade_fixups.sql

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further TEST Action

------ ------------------------ ---------- --------------------------------

6. depend_usr_tables YES None.

7. old_time_zones_exist YES None.

8. dir_symlinks NO Manual fixup recommended.

9. post_dictionary YES None.

10. post_fixed_objects NO Informational only.

Further action is optional.

Recreate Dirsymlinks

Grant datastore access

Use SQL*Plus to connect to the database as SYSDBA and run the following command:

ora19c@oracleebs:~$ sqlplus "/ as sysdba"

SQL> grant text datastore access to public;

Grant succeeded.

Recreate Index IDX$$_27FE500AC

Connect to the database as SYSDBA and run the following command to recreate the dropped Index :

ora19c@oracleebs:~$ sqlplus "/ as sysdba"

SQL>CREATE INDEX "APPLSYS"."IDX$$_27FE500AC" ON "APPLSYS"."AQ$_FND_CP_GSM_OPP_AQTBL_S" ("QUEUE_NAME", BITAND("SUBSCRIBER_TYPE",1), "NAME")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING

STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "APPS_TS_TX_IDX" ;

Index created.

Gather statistics for SYS schema

Copy $APPL_TOP/admin/adstats.sql to DB node. Note that adstats.sql has to be run in restricted mode. Check the degree of parallelism is set to 30 in adstats.sql script.

As applTEST user,

applTEST@oracleebs:~$ cp$APPL_TOP/admin/adstats.sql /tmp

As ora19c user,

ora19c@oracleebs:~$ cp /tmp/adstats.sql /u02/oracle/TEST/19.0.0/dbhome_1/rdbms/admin/

ora19c@oracleebs:~$ sqlplus "/ as sysdba"

SQL> alter system enable restricted session;

SQL> set timi on

SQL> @?/rdbms/admin/adstats.sql

ora19c@oracleebs:~$ sqlplus "/ as sysdba"

SQL> alter system disable restricted session;

1.6 Convert 19c Database to Multitenant Architecture

Create the PDB descriptor

Source the 19c ENV file :

ora19c@oracleebs:~$ cd $ORACLE_HOME/appsutil

ora19c@oracleebs:~$ . ./txkSetCfgCDB.env dboraclehome=/u02/oracle/TEST/19.0.0/dbhome_1

ora19c@oracleebs:~$ export ORACLE_SID=TEST

ora19c@oracleebs:~$ vi txkOnPremPrePDBCreationTasks.pl

Search for word ‘nslookup’

Comment the line below :

#$temp_hostname = `nslookup \`hostname\` | awk \'BEGIN {rv=1;} /^Name:/ {print \$2; exit rv=0;} END {exit rv;}\'`;

Hardcode the below in txkOnPremPrePDBCreationTasks.pl script.

$temp_hostname = "hostname.domainname";

EX :$temp_hostname = "oracleebs.ebs.com";

ora19c@oracleebs:~$ perl txkOnPremPrePDBCreationTasks.pl -dboraclehome=/u02/oracle/TEST/19.0.0/dbhome_1-outdir=$ORACLE_HOME/appsutil/log -appsuser=apps -dbsid=TEST

Note: The txkOnPremPrePDBCreationTasks.pl script shuts down the non-CDB database. Do not manually bring up the non-CDB database.

Update the CDB initialization parameters

Copy TEST_initparam.sql andTEST_datatop.txt from 12c oracle home to 19c.

ora19c@oracleebs:~$ cd $ORACLE_HOME/appsutil

ora19c@oracleebs:~$ . ./txkSetCfgCDB.env dboraclehome=/u02/oracle/TEST/19.0.0/dbhome_1

ora19c@oracleebs:~$ export ORACLE_SID=CDBTEST

ora19c@oracleebs:~$ cd /u02/oracle/TEST/db/tech_st/product/12.1.0/dbhome_1/dbs

ora19c@oracleebs:~$cp TEST_initparam.sql TEST_datatop.txt /u02/oracle/TEST/19.0.0/dbhome_1/dbs

$ cd /u02/oracle/TEST/19.0.0/dbhome_1/dbs

ora19c@oracleebs:~$ sqlplus "/ as sysdba"

SQL> startup nomount;

SQL> @$ORACLE_HOME/dbs/TEST_initparam.sql

SQL> alter system set LOCAL_LISTENER="oracleebs:1524" scope=both;

SQL> alter system register;

Note :

- Ignore any ORA-25138 errors that occur when there are initialization parameters in the source database that are obsolete in Oracle Database 19c.

- Note: During this process, setting some parameters using ALTER SYSTEM SET may fail as they can only be set when the database is open. For any such parameters, identify the appropriate ALTER SYSTEM SET commands from the TEST_initparam.sql script and run them manually after starting or opening the database. You will need to restart the database again for those parameters to take effect.

Check for PDB violations

Use the following commands to run the txkChkPDBCompatability.pl script. This checks the PDB for any violations.

ora19c@oracleebs:~$ cd $ORACLE_HOME/appsutil

ora19c@oracleebs:~$ . ./txkSetCfgCDB.env dboraclehome=/u02/oracle/TEST/19.0.0/dbhome_1

ora19c@oracleebs:~$ export ORACLE_SID=CDBTEST

ora19c@oracleebs:~$ cd $ORACLE_HOME/appsutil/bin

ora19c@oracleebs:~$ perl txkChkPDBCompatability.pl -dboraclehome=$ORACLE_HOME-outdir=$ORACLE_HOME/appsutil/log -cdbsid=CDBTEST-pdbsid=TEST -servicetype=onpremise

$ vi /u02/oracle/TEST/19.0.0/dbhome_1/appsutil/log/TXK_CHK_PDB_COMPTABILITY_Wed_May_19_18_42_24_2021/check_pdb_plugin_violations.out

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 19 18:42:25 2021

Version 19.10.0.0.0

Connected.

NAME CAUSE TYPE STATUS

---------- -------------------- ---------- ----------

MESSAGE

------------------------------------------------------------------------------------------------------------------------------------------------------

PDB$SEED SQL Patch ERROR RESOLVED

Interim patch 31424070/23966480 (APPSST19C XTTS PDB - TABLE IMPORT/CREATION FAILED WITH ORA-39083 ORA-14334): Installed in the CDB but not in the PDB

PDB$SEED SQL Patch ERROR RESOLVED

Interim patch 32067171/24008962 (OJVM RELEASE UPDATE: 19.10.0.0.210119 (32067171)): Installed in the CDB but not in the PDB

PDB$SEED SQL Patch ERROR RESOLVED

'19.10.0.0.0 Release_Update 2101122017' is installed in the CDB but no release updates are installed in the PDB

TEST Non-CDB to PDB WARNING PENDING

PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run.

TEST OPTION WARNING PENDING

Database option DV mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.

TEST OPTION WARNING PENDING

Database option OLS mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0.

TEST Parameter WARNING PENDING

CDB parameter pga_aggregate_target mismatch: Previous 10G Current 1500M

8 rows selected.

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.10.0.0.0

Note – Ignored the above violations as per the instruction below.

****************************************************************************************

* *

* IMPORTANT NOTE: RESOLVE ALL VIOLATIONS BEFORE PROCEEDING TO CREATE PDB *

* *

* - All the errors except SQL PATCH ERRORS should be resolved *

* *

* - All the warnings except CHARACTER SET WARNINGS can be ignored *

* *

* - Review and set the INIT PARAMETERs to match the EBS requirements *

* *

****************************************************************************************

Create the PDB

ora19c@oracleebs:~$ cd $ORACLE_HOME/appsutil

ora19c@oracleebs:~$ . ./txkSetCfgCDB.env dboraclehome=/u02/oracle/TEST/19.0.0/dbhome_1

Oracle Home being passed: /u02/oracle/TEST/19.0.0/dbhome_1

ora19c@oracleebs:~$ cd $ORACLE_HOME/appsutil/bin

ora19c@oracleebs:~$ vi txkCreatePDB.pl

Search for word ‘nslookup’

Comment the line below :

#$temp_hostname = `nslookup \`hostname\` | awk \'BEGIN {rv=1;} /^Name:/ {print \$2; exit rv=0;} END {exit rv;}\'`;

Hardcode the below in txkOnPremPrePDBCreationTasks.pl script.

$temp_hostname = "hostname.domainname";

EX : $temp_hostname = "oracleebs.ebs.com";

ora19c@oracleebs:~$time perl txkCreatePDB.pl -dboraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -cdbsid=CDBTEST -pdbsid=TEST -dbuniquename=CDBTEST -servicetype=onpremise

Enter the non-CDB data top [/u02/oracle/TEST/data/TEST/datafile]: Press Enter

Enter the corresponding PDB data top [/u02/oracle/TEST/data/TEST/datafile]: Press Enter

Run the post PDB script

Use the following commands to run the txkPostPDBCreationTasks.pl script. This updates the PDB configuration.

ora19c@oracleebs:~$ cd $ORACLE_HOME/appsutil

ora19c@oracleebs:~$ . ./txkSetCfgCDB.env dboraclehome=/u02/oracle/TEST/19.0.0/dbhome_1

SQL> alter system set service_names='CDBTEST','TEST' scope=both;

System altered.

SQL> alter system register;

System altered.

$ cd $ORACLE_HOME/bin

$ ./lsnrctl start CDBTEST

ora19c@oracleebs:~$ timeperl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=/u02/oracle/TEST/19.0.0/dbhome_1 -outdir=/u02/oracle/TEST/19.0.0/dbhome_1/appsutil/log -cdbsid=CDBTEST -pdbsid=TEST-appsuser=apps –dbport=1524 -servicetype=onpremise

ora19c@oracleebs:…e/TEST/19.0.0/dbhome_1/appsutil$ time perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=/u02/ooracle/TEST/19.0.0/dbhome_1 -outdir=/u02/oracle/DB/19.0.0/dbhome_1/appsutil/log -cdbsid=CDBTEST -pdbsid=TEST -appsuser=apps -dbport=15524 -servicetype=onpremis

ora19c@oracleebs:…1/appsutil/scripts/TEST_oracleebs$ ./adautocfg.sh

Modify initialization parameters

Refer the below recommended comman Database initilization parameters and parameters specific to 19c and make sure they are present in pfile.

Common Database Initialization Parameters For All Releases

db_name = TEST

CONTROL_FILES = '/u02/oracle/TEST/db/data/CDB/CDBTEST/controlfile/cntrl01.ctl','/u02/oracle/TEST/db/data/CDB/CDBTEST/controlfile/cntrl02.ctl', '/u02/oracle/TEST/db/data/CDB/CDBTEST/controlfile/cntrl03.ctl'

db_block_size = 8192 #MP

_system_trig_enabled = TRUE #MP

nls_language = american

nls_territory = america

nls_numeric_characters = ".,"

nls_comp = binary #MP

nls_sort = binary #MP

nls_date_format = DD-MON-RR #MP

nls_length_semantics = BYTE #MP

diagnostic_dest = /u02/oracle/TEST

max_dump_file_size = 20480 #Limit default trace file size to 20 MB.

undo_management=AUTO #MP

undo_tablespace=APPS_UNDOTS1

_trace_files_public = FALSE

processes = 300 # Max. no. of users.

sessions = 600 # 2 x no. of processes.

db_files = 512 # Max. no. of database files.

dml_locks = 10000 # Database locks.

cursor_sharing = EXACT #MP

open_cursors = 600

session_cached_cursors = 500

sga_target = 2G #MP

db_block_checking = FALSE

db_block_checksum = TRUE

log_checkpoint_timeout = 1200 # Checkpoint at least every 20 mins.

log_checkpoint_interval = 100000

log_buffer = 10485760

log_checkpoints_to_alert = TRUE

shared_pool_size = 600M

shared_pool_reserved_size = 60M

aq_tm_processes = 1

job_queue_processes = 2

LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST’

_sort_elimination_cost_ratio =5 #MP

_like_with_bind_as_equality = TRUE #MP

_fast_full_scan_enabled = FALSE #MP

_b_tree_bitmap_plans = FALSE #MP

optimizer_secure_view_merging = FALSE #MP

_optimizer_autostats_job = FALSE #MP Turn off automatic statistics.

parallel_max_servers = 8 # Max. value should be 2 x no. of CPUs.

parallel_min_servers = 0

parallel_degree_policy = MANUAL #MP

parallel_force_local=TRUE #MP

pga_aggregate_target = 1G

workarea_size_policy = AUTO #MP

filesystemio_options = SETALL

sec_case_sensitive_logon = FALSE

_disable_actualization_for_grant = TRUE #MP

Release-Specific Database Initialization Parameters for Oracle 19c

compatible = 19.0.0 #MP

optimizer_adaptive_plans=TRUE #MP

optimizer_adaptive_statistics=FALSE #MP

pga_aggregate_limit =0 #MP

temp_undo_enabled=FALSE

_pdb_name_case_sensitive=TRUE #MP

event='10946 trace name context forever, level 8454144' #MP

_optimizer_gather_stats_on_conventional_dml = FALSE #MP

_optimizer_use_stats_on_conventional_dml = FALSE #MP

optimizer_real_time_statistics = FALSE #MP

Parameter Removal List for Oracle Database 19c

Remove the below parameters from init file if they are present in pfile.

_kks_use_mutex_pin

_shared_pool_reserved_min_alloc

_sqlexec_progression_cost

exafusion_enabled

exclude_seed_cdb_view

global_context_pool_size

max_enabled_roles

o7_dictionary_accessibility

olap_page_pool_size

optimizer_adaptive_features

parallel_automatic_tuning

parallel_degree_level

parallel_io_cap_enabled

parallel_server

parallel_server_instances

plsql_compiler_flags

plsql_native_library_dir

plsql_native_library_subdir_count

plsql_optimize_level

standby_archive_dest

timed_statistics

use_indirect_data_buffers

utl_file_dir

Update utl_file_dir

$ cd /u02/oracle/TEST/19.0.0/dbhome_1

$ . ./TEST_oracleebs.env

$ perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE -oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=syncUtlFileDir -servicetype=onpremise

$ cd /u02/oracle/TEST/19.0.0/temp/TEST

Run AutoConfig on applications tier

As the user of the applications server node, modify the$TNS_ADMIN/tnsnames.ora file to specify the CDB instance name. The following shows the format of the new TNS entry.

$ su - applTEST

applTEST@oracleebs:~$cd $TNS_ADMIN

$ cp tnsnames.ora tnsnames.ora_orig

applTEST@oracleebs:~$ vi tnsnames.ora

Comment the below :

#TEST = (DESCRIPTION=

# (ADDRESS=(PROTOCOL=tcp)(HOST=oracleebs)(PORT=1524))

# (CONNECT_DATA=(SID=TEST))

# )

Append the below :

TEST=

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=oracleebs.ebs.com)(PORT=1524))

(CONNECT_DATA=

(SERVICE_NAME=ebs_TEST)

(INSTANCE_NAME=CDBTEST)

)

Update the following values in the context file of every Applications tier server node.

Variable Name Value

s_dbport 1524

s_apps_jdbc_connect_descriptor NULL

s_applptmp /u02/oracle/TEST/19.0.0/temp/TEST

To identify the allowable directories for s_applptmp use, the following query:

SQL> select value from v$parameter where name='utl_file_dir';

Run AutoConfig on the APPL_TOP using the following command.

applTEST@oracleebs:~$ sh $INST_TOP/admin/scripts/adautocfg.sh

Note : Source env file again after running autoconfig to load the new variables.

Check Usable Indexes

Run the below script in the upgraded database and compare it with the eariler exported results.

ora19c@oracleebs:~$ sqlplus as sysdba

SQL> @/u03/oracle/scripts/indexes.sql

Restart applications tier server processes

Source the Env file again and Start the application Services.

$ su - applTEST

applTEST@oracleebs:~$ cd$ADMIN_SCRIPTS_HOME

applTEST@oracleebs:~$ ./adstrtal.sh apps/<pwd>

- Start the services and validate the Appliation.

Search This Blog

Wednesday, June 23, 2021

Step by Step EBS 12c Database (Non-CDB) Upgrade to 19c (CDB)

1. 19C DATABASE UPGRADE

Packages Required

Kernel Parameters

1.2 Pre-upgrade Tasks

Run Health Check Script

Fix For Health Check Violation

Collect Below Pre-checks :

Purge Recycle Bin

Enable Flashback

Create Guarantee Restore Point

Backup Database

Backup Application

Backup 12c Oracle Home

Startup DB and Listener

Enable Maintenance Mode

Apply 12.1.3 Patches

Disable Maintenance mode

Patch Post Install Tasks

10.1.2 Oracle Home Patch

Run Autoconfig on Application Tier

Maintain Snapshot Information

Create init Parmater file

Validate The Application

1.3 19c Database Installation

Prepare to create the 19c Oracle home

Install Oracle Database 19c

Apply additional 19c Oracle Database patches

OPatch Upgrade :

Create nls/data/9idata directory

Create appsutil.zip and copy it to the database tier

Copy the orai18n.jar file (JRE Installation)

Create the CDB

Run datapatch on CDB

Create the CDB MGDSYS schema

Create the CDB TNS files

Shut down the CDB

1.4 Upgrade the Database to 19c

Store the UTL_FILE_DIR parameter values

To store the directory path values in the database:

Source 12c Oracle home env file :

Shut down the application tier server processes

Create Gurantted Restore Point

Drop SYS.ENABLED$INDEXES

Shut down the database listener

Run preupgrade utility

Manual Fixups Tasks

Prepare to upgrade

Apply Extra Fixes for 19c Upgrade

Create Gurantted Restore Point

Upgrade the database instance

Fix to update Compatibility Parameter

Modify initialization parameters after the upgrade

Perform patch post-install instructions in 19c

1.5 After the 19c Database Upgrade

Run adgrants.sql

Compile invalid objects

Run Post Upgrade Fix-up Script

Recreate Dirsymlinks

Grant datastore access

Recreate Index IDX$$_27FE500AC

Gather statistics for SYS schema

1.6 Convert 19c Database to Multitenant Architecture

Create the PDB descriptor

Update the CDB initialization parameters

Check for PDB violations

Create the PDB

Modify initialization parameters

Common Database Initialization Parameters For All Releases

Release-Specific Database Initialization Parameters for Oracle 19c

Parameter Removal List for Oracle Database 19c

Update utl_file_dir

Run AutoConfig on applications tier

Check Usable Indexes

Restart applications tier server processes

You cannot complete this task because one of the following events caused a loss of page data: A system failure has occurred "Front end is locked after 12.2 upgrade"