Upgrade Oracle Database from 12c to 19c using the RMAN backup : In this article we will discuss about how to Upgrade Oracle Database from 12c to 19c using the RMAN backup.
Enviroment:
Source:
=======
Hostname : primary.localdomain
Database Name : PROD
Source DB VERSION : 12.2.0.1.0
CDB : non-cdb, Single Instance
Source DB Home Path : /u01/app/oracle/product/12.2.0.1/db_1
Datafile Location : /u01/app/oracle/oradata/PROD/
Target:
=======
Hostname : node-1.localdomain
Database Name : PROD
Target DB VERSION : 19c (19.3.0.0.0)
CDB : non-cdb, Single Instance
Target DB Path : /u02/app/oracle/product/19.0.0/dbhome_1
Datafile Location : /u02/app/oracle/oradata/PROD/
Upgrade Method : Manual(using the RMAN backup)Steps of Oracle Database 19c Restore from 12c rman backup :
In Oracle 12c :
Create pfile from spfile of 12cR2 database:
SQL> create pfile='/home/oracle/initnewIDM.ora' from spfile;
File created.
SQL>Change the parameter compatible in the pfile from “12.2.0.1.0” to “19.3.0.0.0” :
from:
======
*.compatible='12.2.0.1.0'
To:
======
*.compatible='19.3.0.0.0'Check the Database registries :
SQL> set lines 400 pages 400
SQL> col comp_name for a60
SQL> select comp_name, status from dba_registry;
COMP_NAME STATUS
------------------------------------------------------------ --------------------------------------------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
OLAP Analytic Workspace VALID
Oracle Real Application Clusters OPTION OFF
Oracle XML Database VALID
Oracle Workspace Manager VALID
Oracle Text VALID
Oracle Multimedia VALID
Spatial VALID
Oracle OLAP API VALID
Oracle Label Security VALID
Oracle Database Vault VALID
15 rows selected.
SQL>Take full database RMAN backup, archives backup and controlfile backup :
[oracle@primary RMAN_BKP]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Tue Aug 10 10:31:07 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=489590169)
RMAN> run
RMAN> {
RMAN> allocate channel ch1 device type disk;
RMAN> crosscheck backup;
RMAN> crosscheck archivelog all;
RMAN> crosscheck archivelog all;
RMAN> backup as compressed backupset database format '/u02/RMAN_BKP/Fullback_%T_%U'
RMAN> plus archivelog format '/u02/RMAN_BKP/Archive_%T_%U';
RMAN> backup current controlfile format '/u02/RMAN_BKP/Controlback_%T_%U';
RMAN> release channel ch1;
RMAN> }
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=52 device type=DISK
specification does not match any backup in the repository
validation succeeded for archived log
archived log file name=/u01/archive/1_1_1077751836.dbf RECID=1 STAMP=1077754408
validation succeeded for archived log
archived log file name=/u01/archive/1_2_1077751836.dbf RECID=2 STAMP=1080210582
Crosschecked 2 objects
validation succeeded for archived log
archived log file name=/u01/archive/1_1_1077751836.dbf RECID=1 STAMP=1077754408
validation succeeded for archived log
archived log file name=/u01/archive/1_2_1077751836.dbf RECID=2 STAMP=1080210582
Crosschecked 2 objects
Starting backup at 10-AUG-21
current log archived
channel ch1: starting compressed archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=1077754408
input archived log thread=1 sequence=2 RECID=2 STAMP=1080210582
input archived log thread=1 sequence=3 RECID=3 STAMP=1080210702
channel ch1: starting piece 1 at 10-AUG-21
channel ch1: finished piece 1 at 10-AUG-21
piece handle=/u02/RMAN_BKP/Archive_20210810_01065d8f_1_1 tag=TAG20210810T103142 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:35
Finished backup at 10-AUG-21
Starting backup at 10-AUG-21
channel ch1: starting compressed full datafile backup set
channel ch1: 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 ch1: starting piece 1 at 10-AUG-21
channel ch1: finished piece 1 at 10-AUG-21
piece handle=/u02/RMAN_BKP/Fullback_20210810_02065d9j_1_1 tag=TAG20210810T103219 comment=NONE
channel ch1: backup set complete, elapsed time: 00:06:15
Finished backup at 10-AUG-21
Starting backup at 10-AUG-21
current log archived
channel ch1: starting compressed archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=4 STAMP=1080211115
channel ch1: starting piece 1 at 10-AUG-21
channel ch1: finished piece 1 at 10-AUG-21
piece handle=/u02/RMAN_BKP/Archive_20210810_03065dlc_1_1 tag=TAG20210810T103835 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-AUG-21
Starting backup at 10-AUG-21
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 10-AUG-21
channel ch1: finished piece 1 at 10-AUG-21
piece handle=/u02/RMAN_BKP/Controlback_20210810_04065dle_1_1 tag=TAG20210810T103837 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-AUG-21
Starting Control File and SPFILE Autobackup at 10-AUG-21
piece handle=/u01/app/oracle/fast_recovery_area/PROD/PROD/autobackup/2021_08_10/o1_mf_s_1080211122_jk42gv82_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 10-AUG-21
released channel: ch1
RMAN>
Recovery Manager complete.
[oracle@primary RMAN_BKP]$Copy backup files and pfile from Source(12c) to Target(19c) :
[oracle@primary RMAN_BKP]$ ls -ltrh
total 362M
-rw-r-----. 1 oracle oinstall 40M Aug 10 10:32 Archive_20210810_01065d8f_1_1
-rw-r-----. 1 oracle oinstall 312M Aug 10 10:38 Fullback_20210810_02065d9j_1_1
-rw-r-----. 1 oracle oinstall 45K Aug 10 10:38 Archive_20210810_03065dlc_1_1
-rw-r-----. 1 oracle oinstall 11M Aug 10 10:38 Controlback_20210810_04065dle_1_1
-rw-r--r--. 1 oracle oinstall 1.2K Aug 10 10:39 initPROD.ora
[oracle@primary RMAN_BKP]$
[oracle@primary RMAN_BKP]$ scp * oracle@192.168.0.112:/u02/backup/rman/
The authenticity of host '192.168.0.112 (192.168.0.112)' can't be established.
ECDSA key fingerprint is SHA256:KMocJJVCjmc/7q+zR4GCXMem7J1es/f1BZc80N2BUz8.
ECDSA key fingerprint is MD5:6b:e7:43:9f:06:0a:74:16:20:a3:05:b1:23:84:bb:60.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.112' (ECDSA) to the list of known hosts.
oracle@192.168.0.112's password:
Archive_20210810_01065d8f_1_1 100% 40MB 13.3MB/s 00:03
Archive_20210810_03065dlc_1_1 100% 45KB 8.7MB/s 00:00
Controlback_20210810_04065dle_1_1 100% 10MB 11.2MB/s 00:00
Fullback_20210810_02065d9j_1_1 100% 312MB 12.4MB/s 00:25
initPROD.ora 100% 1170 18.7KB/s 00:00
[oracle@primary RMAN_BKP]$In Oracle 19c :
Make required directories as per pfile :
[oracle@node-1 rman]$ mkdir -p /u02/app/oracle/admin/PROD/adump
[oracle@node-1 rman]$ mkdir -p /u02/app/oracle/oradata/PROD
[oracle@node-1 rman]$ mkdir -p /u02/app/oracle/fast_recovery_area/PROD
[oracle@node-1 rman]$ mkdir -p /u02/archiveStartup database in nomount stage using pfile :
SQL> startup nomount pfile='/u02/backup/rman/initPROD.ora';
ORACLE instance started.
Total System Global Area 733999368 bytes
Fixed Size 8900872 bytes
Variable Size 201326592 bytes
Database Buffers 520093696 bytes
Redo Buffers 3678208 bytes
SQL>Restore controlfile from controlfile backup and mount the database :
[oracle@node-1 rman]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 10 10:54:27 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (not mounted)
RMAN> restore controlfile from '/u02/backup/rman/Controlback_20210810_04065dle_1_1';
Starting restore at 10-AUG-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=427 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u02/app/oracle/oradata/PROD/control01.ctl
output file name=/u02/app/oracle/fast_recovery_area/PROD/control02.ctl
Finished restore at 10-AUG-21
RMAN>
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN>Rename redolog files if location is different :
RMAN> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/redo03.log
/u01/app/oracle/oradata/PROD/redo02.log
/u01/app/oracle/oradata/PROD/redo01.log
RMAN> alter database rename file '/u01/app/oracle/oradata/HFCIDMU/redo01.log' to '/u02/app/oracle/oradata/HFCIDMU/redo01.log';
Statement processed
RMAN> alter database rename file '/u01/app/oracle/oradata/HFCIDMU/redo02.log' to '/u02/app/oracle/oradata/HFCIDMU/redo02.log';
Statement processed
RMAN> alter database rename file '/u01/app/oracle/oradata/HFCIDMU/redo03.log' to '/u02/app/oracle/oradata/HFCIDMU/redo03.log';
Statement processed
RMAN> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/HFCIDMU/redo03.log
/u02/app/oracle/oradata/HFCIDMU/redo02.log
/u02/app/oracle/oradata/HFCIDMU/redo01.log
Crosscheck backupsets and Catalog the backup location :
RMAN> crosscheck backupset;
Starting implicit crosscheck backup at 10-AUG-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=433 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 10-AUG-21
Starting implicit crosscheck copy at 10-AUG-21
using channel ORA_DISK_1
Finished implicit crosscheck copy at 10-AUG-21
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u02/RMAN_BKP/Archive_20210810_01065d8f_1_1 RECID=1 STAMP=1080210703
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u02/RMAN_BKP/Fullback_20210810_02065d9j_1_1 RECID=2 STAMP=1080210741
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u02/RMAN_BKP/Archive_20210810_03065dlc_1_1 RECID=3 STAMP=1080211116
Crosschecked 3 objects
RMAN> delete expired backupset;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 EXPIRED DISK /u02/RMAN_BKP/Archive_20210810_01065d8f_1_1
2 2 1 1 EXPIRED DISK /u02/RMAN_BKP/Fullback_20210810_02065d9j_1_1
3 3 1 1 EXPIRED DISK /u02/RMAN_BKP/Archive_20210810_03065dlc_1_1
Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u02/RMAN_BKP/Archive_20210810_01065d8f_1_1 RECID=1 STAMP=1080210703
deleted backup piece
backup piece handle=/u02/RMAN_BKP/Fullback_20210810_02065d9j_1_1 RECID=2 STAMP=1080210741
deleted backup piece
backup piece handle=/u02/RMAN_BKP/Archive_20210810_03065dlc_1_1 RECID=3 STAMP=1080211116
Deleted 3 EXPIRED objects
RMAN>
RMAN> catalog start with '/u02/backup/rman/';
searching for all files that match the pattern /u02/backup/rman/
List of Files Unknown to the Database
=====================================
File Name: /u02/backup/rman/Archive_20210810_01065d8f_1_1
File Name: /u02/backup/rman/Archive_20210810_03065dlc_1_1
File Name: /u02/backup/rman/Fullback_20210810_02065d9j_1_1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u02/backup/rman/Archive_20210810_01065d8f_1_1
File Name: /u02/backup/rman/Archive_20210810_03065dlc_1_1
File Name: /u02/backup/rman/Fullback_20210810_02065d9j_1_1
RMAN>Restore and Recover the database :
In my case we restore the database in different location. Therefore, we use set new name command.
RMAN> run
2> {
3> allocate channel ch1 device type disk;
4> set newname for database to '/u01/app/oracle/oradata/PROD/%U';
5> set newname for tempfile '/u01/app/oracle/oradata/PROD/temp01.dbf' to '/u02/app/oracle/oradata/PROD/temp01.dbf';
6> restore database;
7> switch datafile all;
8> switch tempfile all;
9> release channel ch1;
10> }
released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=267 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 10-AUG-21
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/app/oracle/oradata/PROD/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/PROD/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/oradata/PROD/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u02/app/oracle/oradata/PROD/users01.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/rman/Fullback_20210810_02065d9j_1_1
channel ORA_DISK_1: piece handle=/u02/backup/rman/Fullback_20210810_02065d9j_1_1 tag=TAG20210810T103219
channel ORA_DISK_1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:01:55
Finished restore at 10-AUG-21
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=1080241137 file name=/u02/app/oracle/oradata/PROD/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=1080241137 file name=/u02/app/oracle/oradata/PROD/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=1080241137 file name=/u02/app/oracle/oradata/PROD/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=1080241137 file name=/u02/app/oracle/oradata/PROD/users01.dbf
renamed tempfile 1 to /u02/app/oracle/oradata/PROD/temp01.dbf in control file
released channel: ch1
RMAN>
RMAN> run
2> {
3> allocate channel ch1 device type disk;
4> recover database;
5> release channel ch1;
6> }
allocated channel: ch1
channel ch1: SID=267 device type=DISK
Starting recover at 10-AUG-21
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ch1: reading from backup piece /u02/backup/rman/Archive_20210810_03065dlc_1_1
channel ch1: piece handle=/u02/backup/rman/Archive_20210810_03065dlc_1_1 tag=TAG20210810T103835
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/archive/1_4_1077751836.dbf thread=1 sequence=4
unable to find archived log
archived log thread=1 sequence=4
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/10/2021 19:04:51
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 5 and starting SCN of 1525224
RMAN>Open the database :
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
ORA-01092: ORACLE instance terminated. Disconnection forced
RMAN-03002: failure of sql statement command at 08/10/2021 19:05:23
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 3087294
Session ID: 260 Serial number: 6155Startup the database in upgrade mode and start upgrade the database :
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 6459226200 bytes
Fixed Size 8910936 bytes
Variable Size 1459617792 bytes
Database Buffers 4949278720 bytes
Redo Buffers 41418752 bytes
Database mounted.
Database opened.
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
DOC>######################################################################
DOC>######################################################################
DOC> ERROR
DOC>
DOC>
DOC> As of 12.2, customers must use the parallel upgrade utility, catctl.pl,
DOC> to invoke catupgrd.sql when upgrading the database dictionary.
DOC> Running catupgrd.sql directly from SQL*Plus is no longer supported.
DOC>
DOC> For Example:
DOC>
DOC> cd $ORACLE_HOME/rdbms/admin
DOC> catctl
DOC>
DOC> or
DOC>
DOC> cd $ORACLE_HOME/rdbms/admin
DOC> $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
DOC>
DOC> Refer to the Oracle Database Upgrade Guide for more information.
DOC>
DOC>
DOC>######################################################################
DOC>######################################################################
DOC>#
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@node-1 ~]$
[oracle@node-1 ]$ cd $ORACLE_HOME/rdbms/admin
[oracle@node-1 admin]$
[oracle@node-1 admin]$
[oracle@node-1 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 2 catupgrd.sql
Argument list for [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 = 2
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]
/u02/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u02/app/oracle/product/19.0.0/dbhome_1]
/u02/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u02/app/oracle/product/19.0.0/dbhome_1]
catctlGetOraBaseLogDir = [/u02/app/oracle/product/19.0.0/dbhome_1]
Analyzing file /u02/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20210810110718]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20210810110718/catupgrd_catcon_3942.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20210810110718/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20210810110718/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 [/u02/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PROD/upgrade20210810110730/catupgrd_catcon_3942.lst]
catcon::set_log_file_base_path: catcon: See [/u02/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PROD/upgrade20210810110730/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u02/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PROD/upgrade20210810110730/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u02/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PROD/upgrade20210810110730]
Parallel SQL Process Count = 2
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:[2021_08_10 11:07:40]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [PROD] Files:1 Time: 117s
*************** Catalog Core SQL ***************
Serial Phase #:1 [PROD] Files:5 Time: 209s
Restart Phase #:2 [PROD] Files:1 Time: 2s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [PROD] Files:19 Time: 131s
Restart Phase #:4 [PROD] Files:1 Time: 1s
************* Catalog Final Scripts ************
Serial Phase #:5 [PROD] Files:7 Time: 72s
***************** Catproc Start ****************
Serial Phase #:6 [PROD] Files:1 Time: 57s
***************** Catproc Types ****************
Serial Phase #:7 [PROD] Files:2 Time: 56s
Restart Phase #:8 [PROD] Files:1 Time: 1s
**************** Catproc Tables ****************
Parallel Phase #:9 [PROD] Files:67 Time: 201s
Restart Phase #:10 [PROD] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [PROD] Files:1 Time: 453s
Restart Phase #:12 [PROD] Files:1 Time: 2s
************** Catproc Procedures **************
Parallel Phase #:13 [PROD] Files:94 Time: 64s
Restart Phase #:14 [PROD] Files:1 Time: 2s
Parallel Phase #:15 [PROD] Files:120 Time: 127s
Restart Phase #:16 [PROD] Files:1 Time: 2s
Serial Phase #:17 [PROD] Files:22 Time: 14s
Restart Phase #:18 [PROD] Files:1 Time: 1s
***************** Catproc Views ****************
Parallel Phase #:19 [PROD] Files:32 Time: 144s
Restart Phase #:20 [PROD] Files:1 Time: 1s
Serial Phase #:21 [PROD] Files:3 Time: 62s
Restart Phase #:22 [PROD] Files:1 Time: 1s
Parallel Phase #:23 [PROD] Files:25 Time: 1020s
Restart Phase #:24 [PROD] Files:1 Time: 2s
Parallel Phase #:25 [PROD] Files:12 Time: 431s
Restart Phase #:26 [PROD] Files:1 Time: 2s
Serial Phase #:27 [PROD] Files:1 Time: 0s
Serial Phase #:28 [PROD] Files:3 Time: 15s
Serial Phase #:29 [PROD] Files:1 Time: 0s
Restart Phase #:30 [PROD] Files:1 Time: 2s
*************** Catproc CDB Views **************
Serial Phase #:31 [PROD] Files:1 Time: 3s
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: 117s
Serial Phase #:36 [PROD] Files:1 Time: 0s
Restart Phase #:37 [PROD] Files:1 Time: 1s
Serial Phase #:38 [PROD] Files:6 Time: 28s
Restart Phase #:39 [PROD] Files:1 Time: 1s
*************** Catproc DataPump ***************
Serial Phase #:40 [PROD] Files:3 Time: 234s
Restart Phase #:41 [PROD] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [PROD] Files:13 Time: 530s
Restart Phase #:43 [PROD] Files:1 Time: 2s
Parallel Phase #:44 [PROD] Files:11 Time: 73s
Restart Phase #:45 [PROD] Files:1 Time: 1s
Parallel Phase #:46 [PROD] Files:3 Time: 13s
Restart Phase #:47 [PROD] Files:1 Time: 1s
************* Final Catproc scripts ************
Serial Phase #:48 [PROD] Files:1 Time: 45s
Restart Phase #:49 [PROD] Files:1 Time: 1s
************** Final RDBMS scripts *************
Serial Phase #:50 [PROD] Files:1 Time: 14s
************ Upgrade Component Start ***********
Serial Phase #:51 [PROD] Files:1 Time: 3s
Restart Phase #:52 [PROD] Files:1 Time: 1s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [PROD] Files:2 Time: 1314s
***************** Upgrading XDB ****************
Restart Phase #:54 [PROD] Files:1 Time: 1s
Serial Phase #:56 [PROD] Files:3 Time: 29s
Serial Phase #:57 [PROD] Files:3 Time: 14s
Parallel Phase #:58 [PROD] Files:10 Time: 14s
Parallel Phase #:59 [PROD] Files:25 Time: 28s
Serial Phase #:60 [PROD] Files:4 Time: 35s
Serial Phase #:61 [PROD] Files:1 Time: 0s
Serial Phase #:62 [PROD] Files:32 Time: 16s
Serial Phase #:63 [PROD] Files:1 Time: 0s
Parallel Phase #:64 [PROD] Files:6 Time: 18s
Serial Phase #:65 [PROD] Files:2 Time: 93s
Serial Phase #:66 [PROD] Files:3 Time: 131s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [PROD] Files:1 Time: 1s
Serial Phase #:69 [PROD] Files:1 Time: 6s
Parallel Phase #:70 [PROD] Files:2 Time: 166s
Restart Phase #:71 [PROD] Files:1 Time: 2s
Parallel Phase #:72 [PROD] Files:2 Time: 5s
Serial Phase #:73 [PROD] Files:2 Time: 7s
***************** Upgrading SDO ****************
Restart Phase #:74 [PROD] Files:1 Time: 1s
Serial Phase #:76 [PROD] Files:1 Time: 208s
Serial Phase #:77 [PROD] Files:2 Time: 10s
Restart Phase #:78 [PROD] Files:1 Time: 2s
Serial Phase #:79 [PROD] Files:1 Time: 220s
Restart Phase #:80 [PROD] Files:1 Time: 2s
Parallel Phase #:81 [PROD] Files:3 Time: 382s
Restart Phase #:82 [PROD] Files:1 Time: 2s
Serial Phase #:83 [PROD] Files:1 Time: 26s
Restart Phase #:84 [PROD] Files:1 Time: 1s
Serial Phase #:85 [PROD] Files:1 Time: 43s
Restart Phase #:86 [PROD] Files:1 Time: 1s
Parallel Phase #:87 [PROD] Files:4 Time: 807s
Restart Phase #:88 [PROD] Files:1 Time: 1s
Serial Phase #:89 [PROD] Files:1 Time: 5s
Restart Phase #:90 [PROD] Files:1 Time: 1s
Serial Phase #:91 [PROD] Files:2 Time: 46s
Restart Phase #:92 [PROD] Files:1 Time: 2s
Serial Phase #:93 [PROD] Files:1 Time: 3s
Restart Phase #:94 [PROD] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [PROD] Files:1 Time: 86s
Restart Phase #:96 [PROD] Files:1 Time: 1s
*********** Final Component scripts ***********
Serial Phase #:97 [PROD] Files:1 Time: 9s
************* Final Upgrade scripts ************
Serial Phase #:98 [PROD] Files:1 Time: 1460s
******************* Migration ******************
Serial Phase #:99 [PROD] Files:1 Time: 4s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [PROD] Files:1 Time: 3s
Serial Phase #:101 [PROD] Files:1 Time: 0s
Serial Phase #:102 [PROD] Files:1 Time: 61s
***************** Post Upgrade *****************
Serial Phase #:103 [PROD] Files:1 Time: 77s
**************** Summary report ****************
Serial Phase #:104 [PROD] Files:1 Time: 6s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [PROD] Files:1 Time: 3s
Serial Phase #:106 [PROD] Files:1 Time: 0s
Serial Phase #:107 [PROD] Files:1 Time: 28s
------------------------------------------------------
Phases [0-107] End Time:[2021_08_10 13:48:25]
------------------------------------------------------
Grand Total Time: 9666s
LOG FILES: (/u02/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PROD/upgrade20210810110730/catupgrd*.log)
Upgrade Summary Report Located in:
/u02/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PROD/upgrade20210810110730/upg_summary.log
Grand Total Upgrade Time: [0d:2h:41m:6s]Check the Database registries :
SQL> startup
ORACLE instance started.
Total System Global Area 733999368 bytes
Fixed Size 8900872 bytes
Variable Size 432013312 bytes
Database Buffers 289406976 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.
SQL>
SQL> set lines 400 pages 400
SQL> col comp_name for a60
SQL> select comp_name, status from dba_registry;
COMP_NAME STATUS
------------------------------------------------------------ --------------------------------------------
Oracle Database Catalog Views UPGRADED
Oracle Database Packages and Types UPGRADED
JServer JAVA Virtual Machine UPGRADED
Oracle XDK UPGRADED
Oracle Database Java Packages UPGRADED
OLAP Analytic Workspace UPGRADED
Oracle Real Application Clusters UPGRADED
Oracle XML Database UPGRADED
Oracle Workspace Manager UPGRADED
Oracle Text UPGRADED
Oracle Multimedia UPGRADED
Spatial UPGRADED
Oracle OLAP API UPGRADED
Oracle Label Security UPGRADED
Oracle Database Vault UPGRADED
15 rows selected.
SQL>Run utlrp.sql for validate dba_registry components and Check the Database registries again :
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2021-08-10 14:48:14
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 2021-08-10 15:20:01
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.
SQL>
SQL>
SQL> select comp_name, status from dba_registry;
COMP_NAME STATUS
------------------------------------------------------------ --------------------------------------------
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
OLAP Analytic Workspace VALID
Oracle Real Application Clusters OPTION OFF
Oracle XML Database VALID
Oracle Workspace Manager VALID
Oracle Text VALID
Oracle Multimedia VALID
Spatial VALID
Oracle OLAP API VALID
Oracle Label Security VALID
Oracle Database Vault VALID
15 rows selected.
SQL>I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.
Thanks for sharing Knowledge very Helpful
Timezone no need upgrade?