Oracle 19c Database Upgrade From 12.2.0.1.0 to 19.3.0.0 Using Manual Method

Oracle 19c Database Upgrade

DETAILS	SOURCE	TARGET
OS_VERSION	OEL 6.4	OEL 6.4
DB_VERSION	12.2.0.1	19.3.0.0
DB_NAME	PROD	PROD
ORACLE_HOME	/u01/app/oracle/product/12.2.0.1/db_1	/u01/app/oracle/product/19.0.0/dbhome_1

INSTALL THE 19C BINARIES

CREATE THE REQIRED DIRECTORIES & INSTALL THE 19C SOFTWARE FOR UPGRADE :

[root@doyen ~]# mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1
[root@doyen ~]# mkdir -p /u02/oradata
[root@doyen ~]# chown -R oracle:oinstall /u01 /u02
[root@doyen ~]# chmod -R 775 /u01 /u02

[oracle@doyen dbhome_1]$ unzip V982063-01.zip

[oracle@doyen dbhome_1]$ ./runInstaller
Launching Oracle Database Setup Wizard…

The response file for this session can be found at:
/u01/app/oracle/product/19.0.0/dbhome_1/install/response/db_2019-07-06_00-43-38AM.rsp

You can find the log of this install session at:
/u01/app/oraInventory/logs/InstallActions2019-07-06_00-43-38AM/installActions2019-07-06_00-43-38AM.log

PRE-STEPS : TAKE THE RMAN BACKUP IF THE UPGRADE PROCESS IS NOT SUCCESSFUL.

[oracle@doyen ~]$ . oraenv
ORACLE_SID = [dbs] ? prod
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@doyen ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 5 21:41:25 2019

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1124073472 bytes
Fixed Size 8619840 bytes
Variable Size 805308608 bytes
Database Buffers 301989888 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
SQL> !rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Feb 5 21:45:58 2019

connected to target database: prod (DBID=2299854335)

RMAN> backup database plus archivelog;

Starting backup at 14-FEB-20
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=1012859197
channel ORA_DISK_1: starting piece 1 at 14-FEB-20
channel ORA_DISK_1: finished piece 1 at 14-FEB-20
piece handle=/u01/app/oracle/fast_recovery_area/prod/prod/backupset/2019_07_05/o1_mf_annnn_TAG20190705T214638_gkyxz6wb_.bkp tag=TAG20190705T214638 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 14-FEB-20

Starting backup at 14-FEB-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prod/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prod/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prod/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-FEB-20
channel ORA_DISK_1: finished piece 1 at 14-FEB-20
piece handle=/u01/app/oracle/fast_recovery_area/prod/prod/backupset/2019_07_05/o1_mf_nnndf_TAG20190705T214646_gkyxzgpq_.bkp tag=TAG20190705T214646 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
Finished backup at 14-FEB-20

Starting backup at 14-FEB-20
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=2 STAMP=1012859253
channel ORA_DISK_1: starting piece 1 at 14-FEB-20
channel ORA_DISK_1: finished piece 1 at 14-FEB-20
piece handle=/u01/app/oracle/fast_recovery_area/prod/prod/backupset/2019_07_05/o1_mf_annnn_TAG20190705T214733_gkyy0xcd_.bkp tag=TAG20190705T214733 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-FEB-20

Starting Control File and SPFILE Autobackup at 14-FEB-20
piece handle=/u01/app/oracle/fast_recovery_area/prod/prod/autobackup/2019_07_05/o1_mf_s_1012859254_gkyy0ywv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-FEB-20

RMAN> list backup of database summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — — – ———– ————— ——- ——- ———- —
2 B F A DISK 14-FEB-20 1 1 NO TAG20190705T214646

RMAN>

RMAN> exit

Recovery Manager complete.

CLEAR THE RECYCLE BIN

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

RUN THE GATHER STATS :

SQL> exec dbms_stats.gather_database_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

RUN THE PRE-UPGRADE TOOL :

[oracle@doyen bin]$ /u01/app/oracle/product/12.2.0.1/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar

PREUPGRADE SUMMARY

/u01/app/oracle/cfgtoollogs/prod/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/prod/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/prod/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups

preupgrade_fixups.sql : fixes issues that an automated script can fix safely. This script must be run before the upgrade

@/u01/app/oracle/cfgtoollogs/prod/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

postupgrade_fixups.sql : fixes the upgrade issues that can be automatically fixed after the upgrade.
Once the upgraded finished without errors, we can run the postupgrade_fixups.sql.

@/u01/app/oracle/cfgtoollogs/prod/preupgrade/postupgrade_fixups.sql

EXECUTE THE PREUPGRADE SCRIPTS

SQL*Plus: Release 12.2.0.1.0 Production on Sat Feb 6 02:45:58 2019

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> @/u01/app/oracle/cfgtoollogs/prod/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: 2020-02-14 02:44:33

For Source Database: prod
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
—— ———————— ———- ——————————–
1. tablespaces_info NO Informational only.
Further action is optional.
2. 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.

CHECK THE TIMEZONE DATABASE VERSION
The New Database TimeZone Version is shown in pre-upgrade.log

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’ ORDER BY PROPERTY_NAME;

PROPERTY_NAME VALUE
—————————— ——–
DST_PRIMARY_TT_VERSION 26
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE

SHUTDOWN THE 12C DATABASE AND COPY THE PARAMETER FILE & PASSWORD FILE TO NEW ORACLE HOME 19C

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

[oracle@doyen dbs]$ cp spfileprod.ora /u01/app/oracle/product/19.0.0/dbhome_1/dbs/

[oracle@doyen dbs]$ cp orapwprod /u01/app/oracle/product/19.0.0/dbhome_1/dbs/

SET THE ENV FOR NEW 19C

[oracle@doyen ~]$ . prod.env

ORACLE_SID=prod; export ORACLE_SID
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1/; export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

START THE DATABASE IN UPGRADE MODE

[oracle@doyen ~]$ . prod.env
[oracle@doyen ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Sat Feb 6 03:44:11 2019
Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1124072640 bytes
Fixed Size 8895680 bytes
Variable Size 620756992 bytes
Database Buffers 486539264 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL>

CEHCK THE STATUS OF UPGRADE MODE DATABASE :

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
———————- ————
prod OPEN MIGRATE

RUN THE DBUPGRADE UTILITY

[oracle@doyen bin]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/bin
[oracle@doyen bin]$
[oracle@doyen 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.3.0.0.0DBRU_LINUX.X64_190417]

/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/upgrade20190706035847]

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

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

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

Number of Cpus = 1
Database Name = prod
DataBase Version = 12.2.0.1.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/prod/upgrade20190706035858/catupgrd_catcon_51674.lst]

catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/prod/upgrade20190706035858/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/prod/upgrade20190706035858/catupgrd_*.lst] files for spool files, if any

Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/prod/upgrade20190706035858]

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

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

——————————————————
Phases [0-107] End Time:[2020_02_14 04:36:12] ——————————————————

Grand Total Time: 2222s

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

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

Grand Total Upgrade Time: [0d:0h:37m:2s] [oracle@doyen bin]$

START THE DATABASE IN NORMAL MODE

[oracle@doyen ~]$ . prod.env
[oracle@doyen ~]$ export ORACLE_SID=prod
[oracle@doyen ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Sat Feb 6 04:39:17 2019
Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1124072640 bytes
Fixed Size 8895680 bytes
Variable Size 855638016 bytes
Database Buffers 251658240 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL>

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
——— ——————–
prod READ WRITE

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

Session altered.

PL/SQL procedure successfully completed.

Session altered.

System altered.

PL/SQL procedure successfully completed.

Session altered.

TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP DBRESTART 2019-07-06 04:50:33
DBUA_TIMESTAMP DBRESTART FINISHED 2019-07-06 04:50:33
DBUA_TIMESTAMP DBRESTART NONE 2019-07-06 04:50:33

TIMESTAMP
——————————————————————————–
DBUA_TIMESTAMP CATUPPST STARTED 2019-07-06 04:50:33

TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP POSTUP_BGN 2019-07-06 04:50:33
DBUA_TIMESTAMP POSTUP_BGN FINISHED 2019-07-06 04:50:33
DBUA_TIMESTAMP POSTUP_BGN NONE 2019-07-06 04:50:33

TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP CATREQ_BGN 2019-07-06 04:50:33
DBUA_TIMESTAMP CATREQ_BGN FINISHED 2019-07-06 04:50:33
DBUA_TIMESTAMP CATREQ_BGN NONE 2019-07-06 04:50:33

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP CATREQ_END 2019-07-06 04:50:33
DBUA_TIMESTAMP CATREQ_END FINISHED 2019-07-06 04:50:33
DBUA_TIMESTAMP CATREQ_END NONE 2019-07-06 04:50:33

catuppst: Dropping library DBMS_DDL_INTERNAL_LIB

PL/SQL procedure successfully completed.

catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG

PL/SQL procedure successfully completed.

catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP POSTUP_END 2019-07-06 04:50:33
DBUA_TIMESTAMP POSTUP_END FINISHED 2019-07-06 04:50:33
DBUA_TIMESTAMP POSTUP_END NONE 2019-07-06 04:50:33

TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP CATUPPST 2019-07-06 04:50:33
DBUA_TIMESTAMP CATUPPST FINISHED 2019-07-06 04:50:33
DBUA_TIMESTAMP CATUPPST NONE 2019-07-06 04:50:33

Session altered.

EXECUTE UTLRP SCRIPTS TO COMPLIE INVALID OBJECTS

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

Session altered.

TIMESTAMP
——————————————————————————–
COMP_TIMESTAMP UTLRP_BGN 2019-07-06 04:51:45

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 2019-07-06 04:57:33

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.

SET COMPATIBLE PARAMETER

SQL> show parameter compatible;

NAME TYPE VALUE
———————————— ———– ——————————
compatible string 12.2.0
noncdb_compatible boolean FALSE

SQL> ALTER SYSTEM SET COMPATIBLE = ‘19.0.0’ SCOPE=SPFILE;

System altered.

SHUTDOWN AND START THE DATABASE
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1124072640 bytes
Fixed Size 8895680 bytes
Variable Size 889192448 bytes
Database Buffers 218103808 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.

CHECK THE COMPATIBLE
SQL> show parameter compatible;

NAME TYPE VALUE
———————————— ———– ——————————
compatible string 19.0.0
noncdb_compatible boolean FALSE

COMPILE FIXED OBJECTS STATS

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

EDIT THE ORATAB ENTRY TO CHANGE THE NEW VERSION FOR DATABASE

[oracle@doyen admin]$ vi /etc/oratab

# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
#prod:/u01/app/oracle/product/12.2.0.1/db_1:N

prod:/u01/app/oracle/product/19.0.0/dbhome_1:N

START THE DATABASE IN UPGRADE MODE TO UPGRADE THE TIMEZONE VERSION

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1124072640 bytes
Fixed Size 8895680 bytes
Variable Size 889192448 bytes
Database Buffers 218103808 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_tz_version PLS_INTEGER;
3 BEGIN
4 l_tz_version := DBMS_DST.get_latest_timezone_version;
5 DBMS_OUTPUT.put_line(‘l_tz_version=’ || l_tz_version);
6 DBMS_DST.begin_upgrade(l_tz_version);
7 END;
8 /
l_tz_version=32
An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

RESTART THE DATABASE

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

Total System Global Area 1124072640 bytes
Fixed Size 8895680 bytes
Variable Size 889192448 bytes
Database Buffers 218103808 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.

UPGRADE TIMEZONE FILE:
Upgrade the database file zone file.

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;SQL> 2 3 4 5 6 7 8
9 /
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
Table list: “MDSYS”.”SDO_DIAG_MESSAGES_TABLE”
Number of failures: 0
Table list: “DVSYS”.”SIMULATION_LOG$”
Number of failures: 0
Table list: “DVSYS”.”AUDIT_TRAIL$”
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 THE TIMEZONE VERSION

SQL> SELECT * FROM v$timezone_file;

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

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

RUN THE POST_UPGRADE_FIXUPS

SQL> @/u01/app/oracle/cfgtoollogs/prod/preupgrade/postupgrade_fixups.sql

Session altered.

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: 2019-07-06 02:44:35

For Source Database: prod
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
—— ———————— ———- ——————————–
3. old_time_zones_exist YES None.
4. dir_symlinks YES None.
5. post_dictionary YES None.
6. post_fixed_objects 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.

CHECK THE COMPONENTS STATUS

SQL> select comp_name,status,substr(version,1,10) as version from dba_registry;

COMP_NAME STATUS VERSION
—————————— ——- ———–
Oracle Database Catalog Views VALID 19.0.0.0.0
Oracle Database Packages and T VALID 19.0.0.0.0
ypes

JServer JAVA Virtual Machine VALID 19.0.0.0.0
Oracle XDK VALID 19.0.0.0.0
Oracle Database Java Packages VALID 19.0.0.0.0
OLAP Analytic Workspace VALID 19.0.0.0.0
Oracle Real Application Cluste OPTION 19.0.0.0.0
rs OFF

COMP_NAME STATUS VERSION
—————————— ——- ———–
Oracle XML Database VALID 19.0.0.0.0
Oracle Workspace Manager VALID 19.0.0.0.0
Oracle Text VALID 19.0.0.0.0
Oracle Multimedia VALID 19.0.0.0.0
Spatial VALID 19.0.0.0.0
Oracle OLAP API VALID 19.0.0.0.0
Oracle Label Security VALID 19.0.0.0.0
Oracle Database Vault VALID 19.0.0.0.0

15 rows selected.

SQL>

CHECK THE DATABASE VERSION

SQL> select instance_name,status,version from v$instance;

INSTANCE_NAME STATUS VERSION
—————- ———— —————–
prod OPEN 19.0.0.0.0

Sathish Lakshmanan