Upgrade Oracle 11g To 19c Database BEST PRACTICE

In this article, we are going to learn to upgrade oracle 11g to 19c database. Here we are going to use a manual upgrade using SQL scripts.

Read: How to Add New Disk in ASM DiskGroup

Table of Contents

A list of that releases supports a direct upgrade

Here is the list which supports a direct upgrade to the new release:

11.2.0.4
12.1.0.1
12.1.0.2
12.2.0.1

Prerequisite Upgrade Oracle 11g to 19c Database

Follow the below steps carefully before upgrading the 11g database into the 19c database.

Step 1: Take full database backup before upgrade

You must take the full database RMAN backup before performing the upgrade.

$rman target/

RMAN>  run {
2> allocate channel c1 type disk;
3> backup incremental level 0 tag 'OCP_BEFORE_UPG' database format '/u02/backup/%d_%U';
4> backup tag 'OCP_CONTROL_FILE' current controlfile format '/u02/backup/%d_%T_%s_%p_CONTROL';
5> release channel c1;
6> }

Verify that the backup must be completed before the upgrade, and check using the below commands.

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

Step 2: Purge Recyclebin

Before upgrading, you must purge the recyclebin.

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

Step 3: Gather stats

Gather database stats to finish up-gradation soon.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

Step 4: Run the preupgrade tool

Before upgrading, we run the preupgrade tool. This tool is available in oracle 19c home.

$/u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /u01/preupgrade/

==================
PREUPGRADE SUMMARY
==================
/u01/preupgrade/preupgrade.log
/u01/preupgrade/preupgrade_fixups.sql
/u01/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups
@/u01/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2022-03-21T21:11:41

The above tool gives us two .SQL files

Run preupgrade_fixups.sql

SQL> @/u01/preupgrade/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: 2022-03-21 21:19:24

For Source Database: OCP19C
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. parameter_min_val NO Manual fixup recommended.
2. em_present NO Manual fixup recommended.
3. amd_exists NO Manual fixup recommended.
4. apex_manual_upgrade NO Manual fixup recommended.
5. dictionary_stats YES None.
6. trgowner_no_admndbtrg YES None.
7. pre_fixed_objects YES None.
8. tablespaces_info NO Informational only.
Further action is optional.
9. exf_rul_exists NO Informational only.
Further action is optional.
10. rman_recovery_version NO Informational only.
Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

Step 5: Check the timezone

Check the timezone version if the target database is lower than the source DB timezone version.

SQL> SELECT version FROM v$timezone_file;

VERSION
-------
14

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4

PROPERTY_NAME            VALUE
------------------------ -----
DST_PRIMARY_TT_VERSION   14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE        NONE

How to Upgrade Oracle 11g to 19c Database

Here are the steps to Upgrade Oracle 11g to 19c Database.

Step 1. Shutdown oracle 11g DB

Let’s shutdown the oracle 11g database.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 2: Copy the Parameter and Password file

Copy the parameter file and password file from oracle 11g home to 19c home.

$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileOCP19C.ora /u01/app/oracle/product/19.0.0/dbhome_1
$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwOCP19C /u01/app/oracle/product/19.0.0/dbhome_1

Step 3: Startup upgrade

Now set environment variables of oracle 19c version and startup the database in upgrade mode.

$ export ORACLE_SID=OCP19C
$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
$ export PATH=/u01/app/oracle/product/19.0.0/dbhome_1:$PATH
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 21 21:54:54 2022

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1543500136 bytes
Fixed Size 8896872 bytes
Variable Size 999524196 bytes
Database Buffers 477202480 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.

Step 4: Start DBUPGRADE utility

It’s time to start dbupgrade utility from /u01/app/oracle/product/19.0.0/dbhome_1/bin

[oracle@19c bin]$ export ORACLE_SID=OCP19C

[oracle@19c bin]$ ./dbupgrade

Argument list for [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0

catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.2.0.0.0_LINUX.X64_190204]

/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1]
/u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20220321214707]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20220321214707/catupgrd_catcon_9390.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20220321214707/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20220321214707/catupgrd_*.lst] files for spool files, if any

Number of Cpus = 1
Database Name = OCP19C
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/OCP19C/upgrade20220321214709/catupgrd_catcon_9390.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/OCP19C/upgrade20220321214709/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/OCP19C/upgrade20220321214709/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/OCP19C/upgrade20220321214709]

Components in [OCP19C]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS RAC WK]
Parallel SQL Process Count = 4

------------------------------------------------------
Phases [0-107] Start Time:[2022_03_21 21:47:10]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [OCP19C] Files:1 [oracle@19c bin]$ export ORACLE_SID=OCP19C
[oracle@19c bin]$ ./dbupgrade

Argument list for [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0

catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.2.0.0.0_LINUX.X64_190204]

/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1]
/u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1]

Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql

Log file directory = [/tmp/cfgtoollogs/upgrade20220321214707]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20220321214707/catupgrd_catcon_9390.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20220321214707/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20220321214707/catupgrd_*.lst] files for spool files, if any

Number of Cpus = 1
Database Name = OCP19C
DataBase Version = 11.2.0.4.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/OCP19C/upgrade20220321214709/catupgrd_catcon_9390.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/OCP19C/upgrade20220321214709/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/OCP19C/upgrade20220321214709/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/OCP19C/upgrade20220321214709]

Components in [OCP19C]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ]
Not Installed [DV MGW ODM OLS RAC WK]
Parallel SQL Process Count = 4

------------------------------------------------------
Phases [0-107] Start Time:[2022_03_21 21:47:10]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [OCP19C] Files:1
Time: 68s
*************** Catalog Core SQL ***************
Serial Phase #:1 [OCP19C] Files:5 Time: 48s
Restart Phase #:2 [OCP19C] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [OCP19C] Files:19 Time: 25s
Restart Phase #:4 [OCP19C] Files:1 Time: 1s
************* Catalog Final Scripts ************
Serial Phase #:5 [OCP19C] Files:7 Time: 24s
***************** Catproc Start ****************
Serial Phase #:6 [OCP19C] Files:1 Time: 12s
***************** Catproc Types ****************
Serial Phase #:7 [OCP19C] Files:2 Time: 10s
Restart Phase #:8 [OCP19C] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [OCP19C] Files:67 Time: 42s
Restart Phase #:10 [OCP19C] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [OCP19C] Files:1 Time: 63s
Restart Phase #:12 [OCP19C] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [OCP19C] Files:94 Time: 12s
Restart Phase #:14 [OCP19C] Files:1 Time: 1s
Parallel Phase #:15 [OCP19C] Files:120 Time: 21s
Restart Phase #:16 [OCP19C] Files:1 Time: 0s
Serial Phase #:17 [OCP19C] Files:22 Time: 3s
Restart Phase #:18 [OCP19C] Files:1 Time: 1s
***************** Catproc Views ****************
Parallel Phase #:19 [OCP19C] Files:32 Time: 28s
Restart Phase #:20 [OCP19C] Files:1 Time: 1s
Serial Phase #:21 [OCP19C] Files:3 Time: 11s
Restart Phase #:22 [OCP19C] Files:1 Time: 0s
Parallel Phase #:23 [OCP19C] Files:25 Time: 197s
Restart Phase #:24 [OCP19C] Files:1 Time: 1s
Parallel Phase #:25 [OCP19C] Files:12 Time: 106s
Restart Phase #:26 [OCP19C] Files:1 Time: 0s
Serial Phase #:27 [OCP19C] Files:1 Time: 0s
Serial Phase #:28 [OCP19C] Files:3 Time: 4s
Serial Phase #:29 [OCP19C] Files:1 Time: 0s
Restart Phase #:30 [OCP19C] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [OCP19C] Files:1 Time: 1s
Restart Phase #:32 [OCP19C] Files:1 Time: 1s
Serial Phase #:34 [OCP19C] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [OCP19C] Files:293 Time: 22s
Serial Phase #:36 [OCP19C] Files:1 Time: 0s
Restart Phase #:37 [OCP19C] Files:1 Time: 0s
Serial Phase #:38 [OCP19C] Files:6 Time: 5s
Restart Phase #:39 [OCP19C] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [OCP19C] Files:3 Time: 51s
Restart Phase #:41 [OCP19C] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [OCP19C] Files:13 Time: 117s
Restart Phase #:43 [OCP19C] Files:1 Time: 1s
Parallel Phase #:44 [OCP19C] Files:11 Time: 17s
Restart Phase #:45 [OCP19C] Files:1 Time: 0s
Parallel Phase #:46 [OCP19C] Files:3 Time: 2s
Restart Phase #:47 [OCP19C] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [OCP19C] Files:1 Time: 9s
Restart Phase #:49 [OCP19C] Files:1 Time: 1s
************** Final RDBMS scripts *************
Serial Phase #:50 [OCP19C] Files:1 Time: 20s
************ Upgrade Component Start ***********
Serial Phase #:51 [OCP19C] Files:1 Time: 1s
Restart Phase #:52 [OCP19C] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [OCP19C] Files:2 Time: 587s
***************** Upgrading XDB ****************
Restart Phase #:54 [OCP19C] Files:1 Time: 1s
Serial Phase #:56 [OCP19C] Files:3 Time: 28s
Serial Phase #:57 [OCP19C] Files:3 Time: 4s
Parallel Phase #:58 [OCP19C] Files:10 Time: 3s
Parallel Phase #:59 [OCP19C] Files:25 Time: 4s
Serial Phase #:60 [OCP19C] Files:4 Time: 7s
Serial Phase #:61 [OCP19C] Files:1 Time: 0s
Serial Phase #:62 [OCP19C] Files:32 Time: 4s
Serial Phase #:63 [OCP19C] Files:1 Time: 0s
Parallel Phase #:64 [OCP19C] Files:6 Time: 6s
Serial Phase #:65 [OCP19C] Files:2 Time: 22s
Serial Phase #:66 [OCP19C] Files:3 Time: 80s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [OCP19C] Files:1 Time: 1s
Serial Phase #:69 [OCP19C] Files:1 Time: 1s
Parallel Phase #:70 [OCP19C] Files:2 Time: 56s
Restart Phase #:71 [OCP19C] Files:1 Time: 0s
Parallel Phase #:72 [OCP19C] Files:2 Time: 1s
Serial Phase #:73 [OCP19C] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:74 [OCP19C] Files:1 Time: 0s
Serial Phase #:76 [OCP19C] Files:1 Time: 85s
Serial Phase #:77 [OCP19C] Files:2 Time: 2s
Restart Phase #:78 [OCP19C] Files:1 Time: 1s
Serial Phase #:79 [OCP19C] Files:1 Time: 19s
Restart Phase #:80 [OCP19C] Files:1 Time: 0s
Parallel Phase #:81 [OCP19C] Files:3 Time: 82s
Restart Phase #:82 [OCP19C] Files:1 Time: 1s
Serial Phase #:83 [OCP19C] Files:1 Time: 5s
Restart Phase #:84 [OCP19C] Files:1 Time: 0s
Serial Phase #:85 [OCP19C] Files:1 Time: 11s
Restart Phase #:86 [OCP19C] Files:1 Time: 0s
Parallel Phase #:87 [OCP19C] Files:4 Time: 142s
Restart Phase #:88 [OCP19C] Files:1 Time: 1s
Serial Phase #:89 [OCP19C] Files:1 Time: 1s
Restart Phase #:90 [OCP19C] Files:1 Time: 0s
Serial Phase #:91 [OCP19C] Files:2 Time: 9s
Restart Phase #:92 [OCP19C] Files:1 Time: 0s
Serial Phase #:93 [OCP19C] Files:1 Time: 1s
Restart Phase #:94 [OCP19C] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [OCP19C] Files:1 Time: 31s
Restart Phase #:96 [OCP19C] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:97 [OCP19C] Files:1 Time: 2s
************* Final Upgrade scripts ************
Serial Phase #:98 [OCP19C] Files:1 Time: 24s
******************* OCPration ******************
Serial Phase #:99 [OCP19C] Files:1 Time: 48s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [OCP19C] Files:1 Time: 1s
Serial Phase #:101 [OCP19C] Files:1 Time: 0s
Serial Phase #:102 [OCP19C] Files:1 Time: 44s
***************** Post Upgrade *****************
Serial Phase #:103 [OCP19C] Files:1 Time: 38s
**************** Summary report ****************
Serial Phase #:104 [OCP19C] Files:1 Time: 2s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [OCP19C] Files:1 Time: 0s
Serial Phase #:106 [OCP19C] Files:1 Time: 0s
Serial Phase #:107 [OCP19C] Files:1 Time: 32s

------------------------------------------------------
Phases [0-107] End Time:[2022_03_21 22:25:49]
------------------------------------------------------

Grand Total Time: 2320s

LOG FILES: (/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/OCP19C/upgrade20220321214709/catupgrd*.log)

Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/OCP19C/upgrade20220321214709/upg_summary.log

Grand Total Upgrade Time: [0d:0h:38m:40s]

Step 5: Startup database

After the upgrade starts the database normally.

SQL> startup
ORACLE instance started.

Total System Global Area 1543500136 bytes
Fixed Size                  8896872 bytes
Variable Size             939524096 bytes
Database Buffers          587202560 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

Step 6: Execute catuppst.sql

Execute the catuppst.sql from /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin location.

SQL> @catuppst.sql

Session altered.


Session altered.


Session altered.


PL/SQL procedure successfully completed.


Session altered.


System altered.


PL/SQL procedure successfully completed.


Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART 2022-03-21 23:05:38
DBUA_TIMESTAMP DBRESTART FINISHED 2022-03-21 23:05:38
DBUA_TIMESTAMP DBRESTART NONE 2022-03-21 23:05:38

TIMESTAMP
--------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST STARTED 2022-03-21 23:05:38


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2022-03-21 23:05:38
DBUA_TIMESTAMP POSTUP_BGN FINISHED 2022-03-21 23:05:38
DBUA_TIMESTAMP POSTUP_BGN NONE 2022-03-21 23:05:38

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2022-03-21 23:05:38
DBUA_TIMESTAMP CATREQ_BGN FINISHED 2022-03-21 23:05:38
DBUA_TIMESTAMP CATREQ_BGN NONE 2022-03-21 23:05:38

PL/SQL procedure successfully completed.

catrequtlmg: b_StatEvt = TRUE
catrequtlmg: b_SelProps = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlOCP = FALSE

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2022-03-21 23:05:39
DBUA_TIMESTAMP CATREQ_END FINISHED 2022-03-21 23:05:39
DBUA_TIMESTAMP CATREQ_END NONE 2022-03-21 23:05:39


catuppst: Dropping library DBMS_DDL_INTERNAL_LIB

PL/SQL procedure successfully completed.

catuppst: Dropping view _CURRENT_EDITION_OBJ_OCP

PL/SQL procedure successfully completed.

catuppst: Dropping view _ACTUAL_EDITION_OBJ_OCP

PL/SQL procedure successfully completed.

catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_OCP

PL/SQL procedure successfully completed.

catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_OCP

PL/SQL procedure successfully completed.

catuppst: Dropping table OBJ$OCP
catuppst: Dropping table USER$OCP
catuppst: Dropping table COL$OCP
catuppst: Dropping table CLU$OCP
catuppst: Dropping table CON$OCP
catuppst: Dropping table BOOTSTRAP$OCP
catuppst: Dropping table TAB$OCP
catuppst: Dropping table TS$OCP
catuppst: Dropping table IND$OCP
catuppst: Dropping table ICOL$OCP
catuppst: Dropping table LOB$OCP
catuppst: Dropping table COLTYPE$OCP
catuppst: Dropping table SUBCOLTYPE$OCP
catuppst: Dropping table NTAB$OCP
catuppst: Dropping table REFCON$OCP
catuppst: Dropping table OPQTYPE$OCP
catuppst: Dropping table ICOLDEP$OCP
catuppst: Dropping table VIEWTRCOL$OCP
catuppst: Dropping table ATTRCOL$OCP
catuppst: Dropping table TYPE_MISC$OCP
catuppst: Dropping table LIBRARY$OCP
catuppst: Dropping table ASSEMBLY$OCP
catuppst: Dropping table TSQ$OCP
catuppst: Dropping table FET$OCP

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2022-03-21 23:05:39
DBUA_TIMESTAMP POSTUP_END FINISHED 2022-03-21 23:05:39
DBUA_TIMESTAMP POSTUP_END NONE 2022-03-21 23:05:39

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2022-03-21 23:05:39
DBUA_TIMESTAMP CATUPPST FINISHED 2022-03-21 23:05:39
DBUA_TIMESTAMP CATUPPST NONE 2022-03-21 23:05:39

Session altered.

Step 7: Compile invalid objects

Using the below script, after Upgrade Oracle 11g to 19c Database compiles invalid objects.

SQL> @utlrp.sql

Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2022-03-21 23:07:30

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2022-03-21 23:18:13

DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
0

DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

Check invalid objects details

Using the below query you can check the details of the invalid object. It must be returned as “0“, Upgrade Oracle 11g to 19c Database almost finished.

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

COUNT(*)
----------
0

Step 8: Update COMPATIBLE parameter

Check and update the compatible parameter using the below command. This is required after upgrading Oracle 11g to 19c Database.

SQL> show parameter compatible;

NAME              TYPE    VALUE
----------------- -------- ---------
compatible        string  11.2.0.4.0
noncdb_compatible boolean FALSE


SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;

System altered.

Step 9. Restart the Database

Take stop the database and start normally then check the compatible parameters again after Upgrade Oracle 11g to 19c Database.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 2516580184 bytes
Fixed Size 8660824 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

SQL> show parameter compatible;

NAME              TYPE     VALUE
----------------- -------- ---------
compatible        string   19.0.0.0.0
noncdb_compatible boolean  FALSE

Step 10: Compile fixed objects stats

You must be compile fixed objects stats after upgrading Oracle 11g to 19c Database.

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Post upgradation steps

You must be performed post upgradation steps carefully.

Step 1: Upgrade the timezone version

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1543500136 bytes
Fixed Size 8896872 bytes
Variable Size 1040187392 bytes
Database Buffers 486539264 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.

SQL> SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/

l_tz_version=32
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

Step 2: Shutdown and startup the database

Shutdown the database and startup normally.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area 1543500136 bytes
Fixed Size 8896872 bytes
Variable Size 1040187392 bytes
Database Buffers 486539264 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.


SQL> SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/

Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

Check timezone

SQL> SELECT * FROM v$timezone_file;

FILENAME             VERSION    CON_ID
-------------------- ---------- ----------
timezlrg_32.dat      32         0

SQL>COLUMN property_name FORMAT A30
SQL>COLUMN property_value FORMAT A20

SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;

PROPERTY_NAME              PROPERTY_VALUE
-------------------------- ---------------
DST_PRIMARY_TT_VERSION     32
DST_SECONDARY_TT_VERSION   0
DST_UPGRADE_STATE          NONE

Step 3: Run postupgrade

The last step is just to run postupgrade_fixups.sql

SQL> @/u01/preupgrade/postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.
Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2022-03-21 21:12:30

For Source Database: ocp19C
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0

Preup                        Preupgrade
Action                       Issue Is 
Number Preupgrade Check Name Remedied      Further DBA Action
------ ------------------------ ---------- ----------------------------
11. old_time_zones_exist YES None.
12. dir_symlinks YES None.
13. post_dictionary YES None.
14. post_fixed_objects NO Informational only.
Further action is optional.
15. upg_by_std_upgrd NO Informational only.
Further action is optional.

The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.

PL/SQL procedure successfully completed.

Session altered.

Step 4: Check database name and version

Our database 11g to 19 upgradation is completed now.

SQL> select name,version,open_mode from v$database,v$instance;

NAME      VERSION           OPEN_MODE
--------- ----------------- ----------
OCP19C    19.0.0.0.0        READ WRITE

You have successfully upgraded Oracle 11g to 19c Database, if you learn something from this article please write your fillings in the comment box.

Upgrade Oracle 11g to 19c Database

A list of that releases supports a direct upgrade

Prerequisite Upgrade Oracle 11g to 19c Database

Step 1: Take full database backup before upgrade

Step 2: Purge Recyclebin

Step 3: Gather stats

Step 4: Run the preupgrade tool

Run preupgrade_fixups.sql

Step 5: Check the timezone

How to Upgrade Oracle 11g to 19c Database

Step 1. Shutdown oracle 11g DB

Step 2: Copy the Parameter and Password file

Step 3: Startup upgrade

Step 4: Start DBUPGRADE utility

Step 5: Startup database

Step 6: Execute catuppst.sql

Step 7: Compile invalid objects

Check invalid objects details

Step 8: Update COMPATIBLE parameter

Step 9. Restart the Database

Step 10: Compile fixed objects stats

Post upgradation steps

Step 1: Upgrade the timezone version

Step 2: Shutdown and startup the database

Check timezone

Step 3: Run postupgrade

Step 4: Check database name and version

Related

1 thought on “Upgrade Oracle 11g to 19c Database”

Leave a Comment Cancel reply

A list of that releases supports a direct upgrade

Prerequisite Upgrade Oracle 11g to 19c Database

Step 1: Take full database backup before upgrade

Step 2: Purge Recyclebin

Step 3: Gather stats

Step 4: Run the preupgrade tool

Run preupgrade_fixups.sql

Step 5: Check the timezone

How to Upgrade Oracle 11g to 19c Database

Step 1. Shutdown oracle 11g DB

Step 2: Copy the Parameter and Password file

Step 3: Startup upgrade

Step 4: Start DBUPGRADE utility

Step 5: Startup database

Step 6: Execute catuppst.sql

Step 7: Compile invalid objects

Check invalid objects details

Step 8: Update COMPATIBLE parameter

Step 9. Restart the Database

Step 10: Compile fixed objects stats

Post upgradation steps

Step 1: Upgrade the timezone version

Step 2: Shutdown and startup the database

Check timezone

Step 3: Run postupgrade

Step 4: Check database name and version

Share this:

Related

1 thought on “Upgrade Oracle 11g to 19c Database”

Leave a Comment Cancel reply