Step by Step Manual Upgrade Container (CDB-PDB) Database from 12c to 19c in Multitenant Architecture
In a recent post, we demonstrated Step by Step Manual Upgrade Oracle Database from 12c to 19c, click here to read about it. In this article, we are going to demonstrate Step by Step Manual Upgrade Container (CDB-PDB) Database from 12c to 19c in Multitenant Architecture on Linux. Follow the below steps to perform a manual upgrade to 19c in Multitenant Architecture.
List of target upgrade versions from supported source version along with the certificate, data source support.oracle.com.
Direct Upgrade List:
| Source DB Version | Target DB Version |
| 18.1 | 19c |
| 12.2.0.2 | 19c |
| 12.1.0.2 | 19c |
| 11.2.0.4 | 19c |
Note: Manual Upgrade can be used to upgrade Oracle 12.1.0.2 or higher container (CDB) databases to 19c release.
Indirect Upgrade List:
| Source DB Version | Intermediate DB Version | Target DB Version |
| 12.1.0.1 | 12.1.0.2/12.2.0.1 | 19c |
| 11.2.0.1/11.2.0.2/11.2.0.3 | 11.2.0.4 | 19c |
| 11.1.0.6/11.1.0.7 | 11.2.0.4 | 19c |
| 10.2.0.2/10.2.0.3/10.2.0.4/10.2.0.5 | 11.2.0.4/12.1.0.2 | 19c |
| 10.1.0.5 | 11.2.0.4/12.1.0.2 | 19c |
| 9.2.0.8 or earlier | 11.2.0.4 | 19c |
19c Certificate.
Below are the environmental details of this demonstration.
| COMPONENTS | SOURCE | TARGET |
| Database Name & Type | LABCDB05, Standalone (CDB, PDBs) | LABCDB05, Standalone (CDB, PDBs) |
| Database Version | 12.2.0.1 | 19.3.0.0 |
| Oracle Home | /u01/app/oracle/product/12201/db_1 | /u01/app/oracle/product/1930/db_1 |
| DB Server Type | Linux, OEL 7.9 | Linux, OEL 7.9 |
| Time Version | 26 | 32 |
We will complete a manual upgrade from 12c to 19c (Container) for multitenant architecture (CDB, PDBs) in the below three-part followed by detailed steps.
1. Pre-Checks / Pre-Steps
2. Manual Upgrade using command line Steps
3. Post upgrade Steps
Pre-Checks / Pre-Steps
1.1: Install 19c Binary: Install Oracle 19c binary if it’s not already installed on the DB server. Click here to get 19c binary installation steps and follow the same.
1.2: Validate CDB & PDB: Validate the status of CDB & PDBs.
@CDB$ROOT> show con_name
CON_NAME
------------------------------
CDB$ROOT
@CDB$ROOT> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DBSGURU01_PDB READ WRITE NO
1.3.1: Execute the pre-upgrade command: Execute the preupgrade tool from the source Oracle Home (12c) and make sure all PDBs are accessible.
$SOURCE_12C_ORACLE_HOME/jdk/bin/java -jar $TARGET_19C_ORACLE_HOME/rdbms/admin/preupgrade.jar FILE TEXT DIR output_dir
[oracle@DBsGuruN2 ~]$ /u01/app/oracle/product/12201/db_1/jdk/bin/java -jar /u01/app/oracle/product/1930/db_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/bkp/labcdb05_upgrade
==================
PREUPGRADE SUMMARY
==================
/home/oracle/bkp/labcdb05_upgrade/preupgrade.log
/home/oracle/bkp/labcdb05_upgrade/preupgrade_fixups.sql
/home/oracle/bkp/labcdb05_upgrade/postupgrade_fixups.sql
Execute fixup scripts across the entire CDB:
Before upgrade:
1. Execute preupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade/ -b preup_labcdb05 /home/oracle/bkp/labcdb05_upgrade/preupgrade_fixups.sql
2. Review logs under /home/oracle/bkp/labcdb05_upgrade/
After the upgrade:
1. Execute postupgrade fixups with the below command
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade/ -b postup_labcdb05 /home/oracle/bkp/labcdb05_upgrade/postupgrade_fixups.sql
2. Review logs under /home/oracle/bkp/labcdb05_upgrade/
Preupgrade complete: 2022-03-02T17:24:50
[oracle@DBsGuruN2 ~]$
1.3.2: Review the Log: Switch to the directory to review generated files and log by preupgrade tool as mentioned in preupgrade.jar.
[oracle@DBsGuruN2 ~]$ cd /home/oracle/bkp/labcdb05_upgrade
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt
total 964
drwxr-xr-x. 3 oracle oinstall 21 Mar 2 17:24 oracle
-rw-r--r--. 1 oracle oinstall 15085 Mar 2 17:24 dbms_registry_extended.sql
-rw-r--r--. 1 oracle oinstall 7884 Mar 2 17:24 preupgrade_driver.sql
-rw-r--r--. 1 oracle oinstall 455876 Mar 2 17:24 preupgrade_package.sql
-rw-r--r--. 1 oracle oinstall 14016 Mar 2 17:24 parameters.properties
drwxr-xr-x. 3 oracle oinstall 24 Mar 2 17:24 upgrade
-rw-r--r--. 1 oracle oinstall 100166 Mar 2 17:24 preupgrade_messages.properties
-rw-r--r--. 1 oracle oinstall 41134 Mar 2 17:24 components.properties
-rw-r--r--. 1 oracle oinstall 2 Mar 2 17:24 checksBuffer.tmp
-rw-r--r--. 1 oracle oinstall 12981 Mar 2 17:24 dir_preUpgrade_catCon0.log
-rw-r--r--. 1 oracle oinstall 7429 Mar 2 17:24 dir_preUpgrade_catCon1.log
-rw-r--r--. 1 oracle oinstall 60530 Mar 2 17:24 dir_preUpgrade_catCon_catcon_7326.lst
-rw-r--r--. 1 oracle oinstall 8817 Mar 2 17:24 preupgrade_fixups_CDB_ROOT.sql
-rw-r--r--. 1 oracle oinstall 9258 Mar 2 17:24 postupgrade_fixups_CDB_ROOT.sql
-rw-r--r--. 1 oracle oinstall 7557 Mar 2 17:24 preupgrade_CDB_ROOT.log
-rw-r--r--. 1 oracle oinstall 6957 Mar 2 17:24 preupgrade_fixups_PDB_SEED.sql
-rw-r--r--. 1 oracle oinstall 9258 Mar 2 17:24 postupgrade_fixups_PDB_SEED.sql
-rw-r--r--. 1 oracle oinstall 6977 Mar 2 17:24 preupgrade_fixups_DBSGURU01_PDB.sql
-rw-r--r--. 1 oracle oinstall 9278 Mar 2 17:24 postupgrade_fixups_DBSGURU01_PDB.sql
-rw-r--r--. 1 oracle oinstall 6722 Mar 2 17:24 preupgrade_PDB_SEED.log
-rw-r--r--. 1 oracle oinstall 6738 Mar 2 17:24 preupgrade_DBSGURU01_PDB.log
-rw-r--r--. 1 oracle oinstall 15995 Mar 2 17:24 execPreUpgrade_catCon0.log
-rw-r--r--. 1 oracle oinstall 9294 Mar 2 17:24 execPreUpgrade_catCon1.log
-rw-r--r--. 1 oracle oinstall 61199 Mar 2 17:24 execPreUpgrade_catCon_catcon_7432.lst
-rw-r--r--. 1 oracle oinstall 19264 Mar 2 17:24 preupgrade_fixups.sql
-rw-r--r--. 1 oracle oinstall 21433 Mar 2 17:24 postupgrade_fixups.sql
-rw-r--r--. 1 oracle oinstall 21017 Mar 2 17:24 preupgrade.log
[oracle@DBsGuruN2 labcdb05_upgrade]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ cat preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2022-03-02T17:24:37
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: LABCDB05
Container Name: CDB$ROOT
Container ID: 1
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] OPTION OFF
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
2. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
3. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 470 MB 500 MB
SYSTEM 800 MB 913 MB
TEMP 33 MB 150 MB
UNDOTBS1 70 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
4. No action needed.
Using default parallel upgrade options, this CDB with 2 PDBs will first
upgrade the CDB$ROOT, and then upgrade at most 2 PDBs at a time using 2
parallel processes per PDB.
The number of PDBs upgraded in parallel and the number of parallel
processes per PDB can be adjusted as described in Database Upgrade Guide.
5. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database LABCDB05 container CDB$ROOT
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/home/oracle/bkp/labcdb05_upgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
6. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 26 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
7. To identify directory objects with symbolic links in the path name, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
Recreate any directory objects listed, using path names that contain no
symbolic links.
Some directory object path names may currently contain symbolic links.
Starting in Release 18c, symbolic links are not allowed in directory
object path names used with BFILE data types, the UTL_FILE package, or
external tables.
8. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
9. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database LABCDB05 container CDB$ROOT
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/home/oracle/bkp/labcdb05_upgrade/postupgrade_fixups.sql
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2022-03-02T17:24:46
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: LABCDB05
Container Name: PDB$SEED
Container ID: 2
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: ARCHIVELOG
Readonly: TRUE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] OPTION OFF
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
2. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
3. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 330 MB 500 MB
SYSTEM 250 MB 360 MB
TEMP 64 MB 150 MB
UNDOTBS1 100 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database LABCDB05 container PDB$SEED
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/home/oracle/bkp/labcdb05_upgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
4. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 26 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
5. To identify directory objects with symbolic links in the path name, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
Recreate any directory objects listed, using path names that contain no
symbolic links.
Some directory object path names may currently contain symbolic links.
Starting in Release 18c, symbolic links are not allowed in directory
object path names used with BFILE data types, the UTL_FILE package, or
external tables.
6. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
7. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database LABCDB05 container PDB$SEED
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/home/oracle/bkp/labcdb05_upgrade/postupgrade_fixups.sql
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2022-03-02T17:24:46
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: LABCDB05
Container Name: DBSGURU01_PDB
Container ID: 3
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] OPTION OFF
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
2. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
3. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 350 MB 500 MB
SYSTEM 250 MB 361 MB
TEMP 64 MB 150 MB
UNDOTBS1 100 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database LABCDB05 container DBSGURU01_PDB
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/home/oracle/bkp/labcdb05_upgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
4. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 26 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
5. To identify directory objects with symbolic links in the path name, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
Recreate any directory objects listed, using path names that contain no
symbolic links.
Some directory object path names may currently contain symbolic links.
Starting in Release 18c, symbolic links are not allowed in directory
object path names used with BFILE data types, the UTL_FILE package, or
external tables.
6. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
7. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database LABCDB05 container DBSGURU01_PDB
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/home/oracle/bkp/labcdb05_upgrade/postupgrade_fixups.sql
NOTE: 1. Review log preupgrade.log and act if any action requires under “REQUIRED ACTIONS” especially for tablespaces SYSTEM, SYAUX, UNDO, TEMP, and optionally “RECOMMENDED ACTIONS“.
2. After taking action to fix issues as required, must execute again preupgrade tool and make sure no action require in preupgrade.log.
3. preupgrade_fixups.sql command will be executed before the upgrade, here we will be doing also manual execution of various SQLs and other commands which are the surety of success along with the smooth upgrade.
4. Since this is a container database so we have options to execute preupgrade fixup in the individual container/pluggable database, follow the below
preupgrade_fixups.sql ==> For all container i.e CDB$ROOT and all PLUGABBLE databases.
preupgrade_fixups_CDB_ROOT.sql ==> For ROOT container i.e CDB$ROOT.
preupgrade_fixups_<PDB_NAME>.sql ==> For individual PLUGABBLE database.
1.3.3: Execute preupgrade_fixups.sql: Execute preupgrade fixup command as stated above. We are going to execute the below command (preupgrade_fixups.sql) against all databases (CDB &PDBs).
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade/ -n 2 -e -b preup_labcdb05 /home/oracle/bkp/labcdb05_upgrade/preupgrade_fixups.sql
-b base name (e.g. catcon_test) for log and spool file names
-n Maximum number of parallel SQL processes to use when upgrading the database. Multitenant database defaults to the total number of CPUs on your system. Traditional database defaults to 4.
-l Directory to use for spool log files.
-e sets echo on while running sqlplus scripts
-I do not issue set Errorlogging Identifier (ostensibly because the caller already did it and does not want us to override it)
-d directory containing the file to be run
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade/ -n 2 -e -b preup_labcdb05 /home/oracle/bkp/labcdb05_upgrade/preupgrade_fixups.sql
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade//preup_labcdb05_catcon_8536.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade//preup_labcdb05*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade//preup_labcdb05_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt preup_labcdb05*
-rw-r--r--. 1 oracle oinstall 439 Mar 2 17:38 preup_labcdb05_catcon_8536.lst
-rw-r--r--. 1 oracle oinstall 8408 Mar 2 17:41 preup_labcdb050.log
-rw-r--r--. 1 oracle oinstall 4355 Mar 2 17:41 preup_labcdb051.log
[oracle@DBsGuruN2 labcdb05_upgrade]$
1.4: Refresh Materialized Views: Refresh materialized views if exist any and wait for the completion of materialized views.
--in ROOT Container DB:
@CDB$ROOT> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
@CDB$ROOT> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end; 2 3 4 5
6 /
PL/SQL procedure successfully completed.
CDB$ROOT> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
no rows selected
--In PLUGGABLE DB
@DBSGURU01_PDB> SHOW CON_NAME
CON_NAME
------------------------------
DBSGURU01_PDB
DBSGURU01_PDB> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end; 2 3 4 5
6 /
PL/SQL procedure successfully completed.
DBSGURU01_PDB> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
no rows selected
NOTE: Alternate option to execute the command using catcon.pl and from here onwards we will be using the same option so that all commands will be executed in all databases (CDB, PDBs) and must review each log file after execution of the command.
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -e -b mview_refresh -- --x"SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8"
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/mview_refresh_catcon_12351.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/mview_refresh*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/mview_refresh_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt mview_refresh*
-rw-r--r--. 1 oracle oinstall 435 Mar 2 18:13 mview_refresh_catcon_12351.lst
-rw-r--r--. 1 oracle oinstall 5711 Mar 2 18:13 mview_refresh0.log
1.5: Manually gather statistics: Execute the below commands to gather statistics which will reduce the total time of upgrade.
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -b gatherstats_dict -- --x"exec dbms_stats.gather_dictionary_stats"
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/gatherstats_dict_catcon_14429.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/gatherstats_dict*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/gatherstats_dict_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -b gatherstats_fix_obj -- --x"exec dbms_stats.gather_fixed_objects_stats;"
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/gatherstats_fix_obj_catcon_15403.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/gatherstats_fix_obj*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/gatherstats_fix_obj_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -b gatherstats_system -- --x"exec dbms_stats.gather_schema_stats ('SYSTEM');"
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/gatherstats_system_catcon_16079.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/gatherstats_system*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/gatherstats_system_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -b gatherstats_sys -- --x"exec dbms_stats.gather_schema_stats ('SYS');"
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/gatherstats_sys_catcon_16215.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/gatherstats_sys*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/gatherstats_sys_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt gatherstats*
-rw-r--r--. 1 oracle oinstall 441 Mar 2 18:42 gatherstats_dict_catcon_14429.lst
-rw-r--r--. 1 oracle oinstall 5088 Mar 2 18:43 gatherstats_dict0.log
-rw-r--r--. 1 oracle oinstall 447 Mar 2 18:49 gatherstats_fix_obj_catcon_15403.lst
-rw-r--r--. 1 oracle oinstall 5136 Mar 2 18:50 gatherstats_fix_obj0.log
-rw-r--r--. 1 oracle oinstall 445 Mar 2 18:51 gatherstats_system_catcon_16079.lst
-rw-r--r--. 1 oracle oinstall 5184 Mar 2 18:51 gatherstats_system0.log
-rw-r--r--. 1 oracle oinstall 439 Mar 2 18:51 gatherstats_sys_catcon_16215.lst
-rw-r--r--. 1 oracle oinstall 5148 Mar 2 18:54 gatherstats_sys0.log
[oracle@DBsGuruN2 labcdb05_upgrade]$
1.6: Active Backup Validation: Validate database for active backup or if any datafiles are in recovery mode.
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -b recover_file -- --x"SELECT * FROM V\$RECOVER_FILE;"
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/recover_file_catcon_4372.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/recover_file*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/recover_file_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -b backup_status -- --x"SELECT * FROM V\$BACKUP WHERE STATUS != 'NOT ACTIVE';"
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/backup_status_catcon_4565.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/backup_status*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/backup_status_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt recover_file*
-rw-r--r--. 1 oracle oinstall 433 Mar 3 15:23 recover_file_catcon_4372.lst
-rw-r--r--. 1 oracle oinstall 4895 Mar 3 15:23 recover_file0.log
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt backup_status*
-rw-r--r--. 1 oracle oinstall 435 Mar 3 15:25 backup_status_catcon_4565.lst
-rw-r--r--. 1 oracle oinstall 5171 Mar 3 15:25 backup_status0.log
[oracle@DBsGuruN2 labcdb05_upgrade]$
1.7: Default Tablespace for SYS & SYSTEM: Validate users SYS and SYSTEM have assigned default tablespace SYSTEM.
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -b system_tablespace -- --x"SELECT * FROM DBA_USERS WHERE USERNAME IN ('SYS','SYSTEM') AND DEFAULT_TABLESPACE!='SYSTEM';"
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/system_tablespace_catcon_7292.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/system_tablespace*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/system_tablespace_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt system_tablespace*
-rw-r--r--. 1 oracle oinstall 443 Mar 3 16:00 system_tablespace_catcon_7292.lst
-rw-r--r--. 1 oracle oinstall 5650 Mar 3 16:01 system_tablespace0.log
In case the assigned default tablespace for user SYS & SYSTEM is other than SYSTEM then must modify the default tablespace to SYSTEM. Login to particular CDB/PDB as require and execute below command.
SQL> ALTER USER SYS DEFAULT TABLESPACE SYSTEM;
SQL> ALTER USER SYSTEM DEFAULT TABLESPACE SYSTEM;
1.8: Validate 2phase pending transactions: Validate any Pending 2phase Transactions active.
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -b 2pc_pending -- --x"SELECT * FROM DBA_2PC_PENDING;"
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/2pc_pending_catcon_7584.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/2pc_pending*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/2pc_pending_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt 2pc_pending*
-rw-r--r--. 1 oracle oinstall 431 Mar 3 16:03 2pc_pending_catcon_7584.lst
-rw-r--r--. 1 oracle oinstall 4905 Mar 3 16:03 2pc_pending0.log
[oracle@DBsGuruN2 labcdb05_upgrade]$ view 2pc_pending0.log
[oracle@DBsGuruN2 labcdb05_upgrade]$
In case the above command returns any selected rows with data, then run the below following commands logging to particular CDB/PDB.
SQL> SELECT LOCAL_TRAN_ID FROM DBA_2PC_PENDING;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
1.9: Invalid Objects compile: Execute the below command to validate invalid objects and Recompile them if found any invalid objects, especially for database default users.
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -b utlrp -d '''.''' $ORACLE_HOME/rdbms/admin/utlrp.sql
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/utlrp_catcon_8654.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/utlrp*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt utlrp*
-rw-r--r--. 1 oracle oinstall 419 Mar 3 16:16 utlrp_catcon_8654.lst
-rw-r--r--. 1 oracle oinstall 14239 Mar 3 16:17 utlrp0.log
[oracle@DBsGuruN2 labcdb05_upgrade]$
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -b invalid -- --x"SELECT OWNER, COUNT(*) FROM DBA_OBJECTS WHERE STATUS <> 'VALID' GROUP BY OWNER;"
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/invalid_catcon_9222.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/invalid*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/invalid_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt invalid*
-rw-r--r--. 1 oracle oinstall 423 Mar 3 16:23 invalid_catcon_9222.lst
-rw-r--r--. 1 oracle oinstall 5483 Mar 3 16:23 invalid0.log
[oracle@DBsGuruN2 labcdb05_upgrade]$ view invalid0.log
1.10: Validate value of parameter sec_case_sensitive_logon: If the value is FALSE of this parameter then change it to TRUE.
@CDB$ROOT> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
@CDB$ROOT> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
@CDB$ROOT> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=TRUE SCOPE=BOTH;
System altered.
@CDB$ROOT> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
1.11: Timezone: Validate the timezone.
@CDB$ROOT> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
@CDB$ROOT> SET LINES 333
@CDB$ROOT> COL PROPERTY_NAME FOR A25
@CDB$ROOT> COL PROPERTY_VALUE FOR A21
@CDB$ROOT> SELECT VERSION FROM V$TIMEZONE_FILE;
VERSION
----------
26
@CDB$ROOT> SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME ='DST_PRIMARY_TT_VERSION';
PROPERTY_NAME PROPERTY_VALUE
------------------------- ---------------------
DST_PRIMARY_TT_VERSION 26
1.12: Component’s version along with status: Validate components status and make sure none of should be INVALID.
@CDB$ROOT> SHOW CON_NAME
CON_NAME
------------------------------
CDB$ROOT
@CDB$ROOT> SET LINES 333 PAGES 111
@CDB$ROOT> COL COMP_NAME FORM A55
@CDB$ROOT> SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;
@CDB$ROOT> SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;
COMP_NAME VERSION STATUS
------------------------------------------------------- ------------------------------ --------------------------------------------
Oracle Database Catalog Views 12.2.0.1.0 VALID
Oracle Database Packages and Types 12.2.0.1.0 VALID
JServer JAVA Virtual Machine 12.2.0.1.0 VALID
Oracle XDK 12.2.0.1.0 VALID
Oracle Database Java Packages 12.2.0.1.0 VALID
OLAP Analytic Workspace 12.2.0.1.0 VALID
Oracle Real Application Clusters 12.2.0.1.0 OPTION OFF
Oracle XML Database 12.2.0.1.0 VALID
Oracle Workspace Manager 12.2.0.1.0 VALID
Oracle Text 12.2.0.1.0 VALID
Oracle Multimedia 12.2.0.1.0 VALID
Spatial 12.2.0.1.0 VALID
Oracle OLAP API 12.2.0.1.0 VALID
Oracle Label Security 12.2.0.1.0 VALID
Oracle Database Vault 12.2.0.1.0 VALID
15 rows selected.
1.13: Upgrade component APEX: If component APEX is installed in 12c then it’s recommended to upgrade it prior to upgrading the database to 19c following doc ID 1088970.1.
@CDB$ROOT> SELECT COMP_ID, VERSION, STATUS FROM DBA_REGISTRY WHERE COMP_ID='APEX';
no rows selected
1.14: Empty Database Recyclebin: Purge deleted objects from recyclebin.
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -b recyclebin_purge -- --x"PURGE DBA_RECYCLEBIN;"
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/recyclebin_purge_catcon_13390.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/recyclebin_purge*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/recyclebin_purge_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -b recyclebin_validate -- --x"SELECT COUNT(*) FROM DBA_RECYCLEBIN;"
catcon: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/recyclebin_validate_catcon_13589.lst]
catcon: See [/home/oracle/bkp/labcdb05_upgrade/recyclebin_validate*.log] files for output generated by scripts
catcon: See [/home/oracle/bkp/labcdb05_upgrade/recyclebin_validate_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt recyclebin*
-rw-r--r--. 1 oracle oinstall 441 Mar 3 17:13 recyclebin_purge_catcon_13390.lst
-rw-r--r--. 1 oracle oinstall 4820 Mar 3 17:13 recyclebin_purge0.log
-rw-r--r--. 1 oracle oinstall 447 Mar 3 17:14 recyclebin_validate_catcon_13589.lst
-rw-r--r--. 1 oracle oinstall 5030 Mar 3 17:14 recyclebin_validate0.log
[oracle@DBsGuruN2 labcdb05_upgrade]$
1.15: Execute dbupgdiag.sql: This is recommended to execute dbupgdiag.sql which captured detailed information of installed COMPONENTS, INVALID OBJECTS along with much more detailed information of database and also must fix if found any action prior to upgrade to 19c. Click here to download dbupgdiag.sql.
@CDB$ROOT> @/home/oracle/bkp/labcdb05_upgrade/dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /home/oracle/bkp/labcdb05_upgrade/
03_Mar_2022_0528 .log
labcdb05_
*** Start of LogFile ***
Oracle Database Upgrade Diagnostic Utility 03-03-2022 17:28:24
===============
Hostname
===============
DBsGuruN2.labdomain
===============
Database Name
===============
LABCDB05
===============
Database Uptime
===============
15:04 03-MAR-22
=================
Database Wordsize
=================
This is a 64-bit database
================
Software Version
================
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
=============
Compatibility
=============
Compatibility is set as 12.2.0
================
Archive Log Mode
================
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6
================
Auditing Check
================
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/labcdb05
/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_sga_queue_size integer 1048576
================
Cluster Check
================
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
DOC>################################################################
DOC>
DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC> upgrading the database
DOC>
DOC>################################################################
DOC>#
===========================================
Tablespace and the owner of the aud$ table ( IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$)
===========================================
OWNER TABLESPACE_NAME
------------ ------------------------------
SYS SYSTEM
============================================================================
count of records in the sys.aud$ table where dbid is null- Standard Auditing
============================================================================
0
============================================================================================
count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
============================================================================================
select count(*) from system.aud$ where dbid is null
*
ERROR at line 1:
ORA-00942: table or view does not exist
=============================================================================
count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
=============================================================================
0
==========================================
Oracle Label Security is installed or not
==========================================
Oracle Label Security is installed
================
Number of AQ Records in Message Queue Tables
================
SYS - ALERT_QT - 0
SYS - AQ$_MEM_MC - 0
SYS - AQ_EVENT_TABLE - 0
SYS - AQ_PROP_TABLE - 0
SYS - KUPC$DATAPUMP_QUETAB - 0
SYS - ORA$PREPLUGIN_BACKUP_QTB - 0
SYS - SCHEDULER$_EVENT_QTAB - 0
SYS - SCHEDULER$_REMDB_JOBQTAB - 0
SYS - SCHEDULER_FILEWATCHER_QT - 0
SYS - SYS$SERVICE_METRICS_TAB - 0
WMSYS - WM$EVENT_QUEUE_TABLE - 0
================
Time Zone version
================
26
================
Local Listener
================
LISTENER_LABCDB05
================
Default and Temporary Tablespaces By User
================
USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE
---------------------------- ---------------------- ----------------------
SYS TEMP SYSTEM
SYSTEM TEMP SYSTEM
XS$NULL TEMP SYSTEM
OJVMSYS TEMP SYSTEM
LBACSYS TEMP SYSTEM
OUTLN TEMP SYSTEM
SYS$UMF TEMP SYSTEM
DBSNMP TEMP SYSAUX
APPQOSSYS TEMP SYSAUX
DBSFWUSER TEMP SYSAUX
GGSYS TEMP SYSAUX
ANONYMOUS TEMP SYSAUX
CTXSYS TEMP SYSAUX
SI_INFORMTN_SCHEMA TEMP SYSAUX
DVSYS TEMP SYSAUX
DVF TEMP SYSAUX
GSMADMIN_INTERNAL TEMP SYSAUX
ORDPLUGINS TEMP SYSAUX
MDSYS TEMP SYSAUX
OLAPSYS TEMP SYSAUX
ORDDATA TEMP SYSAUX
XDB TEMP SYSAUX
WMSYS TEMP SYSAUX
ORDSYS TEMP SYSAUX
GSMCATUSER TEMP USERS
MDDATA TEMP USERS
SYSBACKUP TEMP USERS
REMOTE_SCHEDULER_AGENT TEMP USERS
GSMUSER TEMP USERS
SYSRAC TEMP USERS
AUDSYS TEMP USERS
DIP TEMP USERS
SYSKM TEMP USERS
ORACLE_OCM TEMP USERS
SYSDG TEMP USERS
SPATIAL_CSW_ADMIN_USR TEMP USERS
================
Component Status
================
Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
APS OLAP Analytic Workspace VALID 12.2.0.1.0
CATALOG Oracle Database Catalog Views VALID 12.2.0.1.0
CATJAVA Oracle Database Java Packages VALID 12.2.0.1.0
CATPROC Oracle Database Packages and Types VALID 12.2.0.1.0
CONTEXT Oracle Text VALID 12.2.0.1.0
DV Oracle Database Vault VALID 12.2.0.1.0
JAVAVM JServer JAVA Virtual Machine VALID 12.2.0.1.0
OLS Oracle Label Security VALID 12.2.0.1.0
ORDIM Oracle Multimedia VALID 12.2.0.1.0
OWM Oracle Workspace Manager VALID 12.2.0.1.0
RAC Oracle Real Application Clusters OPTION OFF12.2.0.1.0
SDO Spatial VALID 12.2.0.1.0
XDB Oracle XML Database VALID 12.2.0.1.0
XML Oracle XDK VALID 12.2.0.1.0
XOQ Oracle OLAP API VALID 12.2.0.1.0
======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
======================================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
================================
List of Invalid Database Objects
================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
======================================================
Count of Invalids by Schema
======================================================
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================
DOC>###########################################################################
DOC>
DOC> Result referencing the string 'B023' ==> Database was created as 32-bit
DOC> Result referencing the string 'B047' ==> Database was created as 64-bit
DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0
DOC> (64-bit) , For known issue refer below articles
DOC>
DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC> Upgrading Or Patching Databases To 10.2.0.3
DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC>
DOC>###########################################################################
DOC>#
Metadata Initial DB Creation Info
-------- -----------------------------------
B047 Database was created as 64-bit
===================================================
Number of Duplicate Objects Owned by SYS and SYSTEM
===================================================
Counting duplicate objects ....
COUNT(1)
----------
0
=========================================
Duplicate Objects Owned by SYS and SYSTEM
=========================================
Querying duplicate objects ....
DOC>
DOC>################################################################################
DOC>Below are expected and required duplicates objects and OMITTED in the report .
DOC>
DOC>Without replication installed:
DOC>INDEX AQ$_SCHEDULES_PRIMARY
DOC>TABLE AQ$_SCHEDULES
DOC>
DOC>If replication is installed by running catrep.sql:
DOC>INDEX AQ$_SCHEDULES_PRIMARY
DOC>PACKAGE DBMS_REPCAT_AUTH
DOC>PACKAGE BODY DBMS_REPCAT_AUTH
DOC>TABLE AQ$_SCHEDULES
DOC>
DOC>If any objects found please follow below article.
DOC>Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
DOC>Read the Exceptions carefully before taking actions.
DOC>
DOC>################################################################################
DOC>#
========================
Password protected roles
========================
DOC>
DOC>################################################################################
DOC>
DOC> In version 11.2 password protected roles are no longer enabled by default so if
DOC> an application relies on such roles being enabled by default and no action is
DOC> performed to allow the user to enter the password with the set role command, it
DOC> is recommended to remove the password from those roles (to allow for existing
DOC> privileges to remain available). For more information see:
DOC>
DOC> Note 745407.1 : What Roles Can Be Set as Default for a User?
DOC>
DOC>################################################################################
DOC>#
Querying for password protected roles ....
================
JVM Verification
================
================================================
Checking Existence of Java-Based Users and Roles
================================================
DOC>
DOC>################################################################################
DOC>
DOC> There should not be any Java Based users for database version 9.0.1 and above.
DOC> If any users found, it is faulty JVM.
DOC>
DOC>################################################################################
DOC>#
User Existence
---------------------------
No Java Based Users
DOC>
DOC>###############################################################
DOC>
DOC> Healthy JVM Should contain Six Roles. For 12.2 Seven Roles
DOC> If there are more or less than six role, JVM is inconsistent.
DOC>
DOC>###############################################################
DOC>#
Role
------------------------------
There are 7 JAVA related roles
Roles
ROLE
------------------------------
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
DBJAVASCRIPT
JAVA_ADMIN
JAVA_DEPLOY
=========================================
List of Invalid Java Objects owned by SYS
=========================================
There are no SYS owned invalid JAVA objects
DOC>
DOC>#################################################################
DOC>
DOC> Check the status of the main JVM interface packages DBMS_JAVA
DOC> and INITJVMAUX and make sure it is VALID.
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>#################################################################
DOC>#
no rows selected
DOC>
DOC>#################################################################
DOC>
DOC> If the JAVAVM component is not installed in the database (for
DOC> example, after creating the database with custom scripts), the
DOC> next query will report the following error:
DOC>
DOC> select dbms_java.longname('foo') "JAVAVM TESTING" from dual
DOC> *
DOC> ERROR at line 1:
DOC> ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
DOC>
DOC> If the JAVAVM component is installed, the query should succeed
DOC> with 'foo' as result.
DOC>
DOC>#################################################################
DOC>#
JAVAVM TESTING
---------------
foo
===================================
Oracle Multimedia/InterMedia status
===================================
.
Oracle Multimedia/interMedia is installed and listed with the following version: 12.2.0.1.0 and status: VALID
.
Checking for installed Database Schemas...
ORDSYS user exists.
ORDPLUGINS user exists.
MDSYS user exists.
SI_INFORMTN_SCHEMA user exists.
.
Checking for Prerequisite Components...
JAVAVM installed and listed as valid
XDK installed and listed as valid
XDB installed and listed as valid
Validating Oracle Multimedia/interMedia...(no output if component status is valid)
PL/SQL procedure successfully completed.
*** End of LogFile ***
Upload db_upg_diag_labcdb05_03_Mar_2022_0528.log from "/home/oracle/bkp/labcdb05_upgrade/" directory
1.16: Full Database Backup: Take the database a full backup before upgrade.
[oracle@DBsGuruN2 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Mar 3 17:33:54 2022
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: LABCDB05 (DBID=2824718562)
RMAN> run
{
ALLOCATE CHANNEL D1 DEVICE TYPE DISK FORMAT '/home/oracle/bkp/labcdb05_%U';
ALLOCATE CHANNEL D2 DEVICE TYPE DISK FORMAT '/home/oracle/bkp/labcdb05_%U';
ALLOCATE CHANNEL D3 DEVICE TYPE DISK FORMAT '/home/oracle/bkp/labcdb05_%U';
BACKUP tag 'UPGRADE_DB' FORCE AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
BACKUP CURRENT CONTROLFILE TAG 'UPGRADE_CTL' FORMAT '/home/oracle/bkp/labcdb05ctl_%u';
BACKUP SPFILE TAG 'UPGRADE_SPFILE' FORMAT '/home/oracle/bkp/labcdb05sf_%U';
RELEASE CHANNEL D1;
RELEASE CHANNEL D2;
RELEASE CHANNEL D3;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
using target database control file instead of recovery catalog
allocated channel: D1
channel D1: SID=273 device type=DISK
allocated channel: D2
channel D2: SID=405 device type=DISK
allocated channel: D3
channel D3: SID=14 device type=DISK
Starting backup at 03-MAR-22
current log archived
channel D1: starting compressed archived log backup set
channel D1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=1098294044
input archived log thread=1 sequence=4 RECID=2 STAMP=1098298230
channel D1: starting piece 1 at 03-MAR-22
channel D2: starting compressed archived log backup set
channel D2: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=3 STAMP=1098298422
input archived log thread=1 sequence=6 RECID=4 STAMP=1098380055
channel D2: starting piece 1 at 03-MAR-22
channel D1: finished piece 1 at 03-MAR-22
piece handle=/home/oracle/bkp/labcdb05_010nfson_1_1 tag=UPGRADE_DB comment=NONE
channel D1: backup set complete, elapsed time: 00:00:15
channel D2: finished piece 1 at 03-MAR-22
piece handle=/home/oracle/bkp/labcdb05_020nfson_1_1 tag=UPGRADE_DB comment=NONE
channel D2: backup set complete, elapsed time: 00:00:15
Finished backup at 03-MAR-22
Starting backup at 03-MAR-22
channel D1: starting compressed full datafile backup set
channel D1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/labcdb05/system01.dbf
input datafile file number=00007 name=/oradata/labcdb05/users01.dbf
channel D1: starting piece 1 at 03-MAR-22
channel D2: starting compressed full datafile backup set
channel D2: specifying datafile(s) in backup set
input datafile file number=00003 name=/oradata/labcdb05/sysaux01.dbf
input datafile file number=00004 name=/oradata/labcdb05/undotbs01.dbf
channel D2: starting piece 1 at 03-MAR-22
channel D3: starting compressed full datafile backup set
channel D3: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/labcdb05/dbsguru01_pdb/sysaux01.dbf
input datafile file number=00012 name=/oradata/labcdb05/dbsguru01_pdb/users01.dbf
channel D3: starting piece 1 at 03-MAR-22
channel D3: finished piece 1 at 03-MAR-22
piece handle=/home/oracle/bkp/labcdb05_050nfsp7_1_1 tag=UPGRADE_DB comment=NONE
channel D3: backup set complete, elapsed time: 00:00:25
channel D3: starting compressed full datafile backup set
channel D3: specifying datafile(s) in backup set
input datafile file number=00006 name=/oradata/labcdb05/pdbseed/sysaux01.dbf
channel D3: starting piece 1 at 03-MAR-22
channel D1: finished piece 1 at 03-MAR-22
piece handle=/home/oracle/bkp/labcdb05_030nfsp7_1_1 tag=UPGRADE_DB comment=NONE
channel D1: backup set complete, elapsed time: 00:00:56
channel D1: starting compressed full datafile backup set
channel D1: specifying datafile(s) in backup set
input datafile file number=00009 name=/oradata/labcdb05/dbsguru01_pdb/system01.dbf
input datafile file number=00011 name=/oradata/labcdb05/dbsguru01_pdb/undotbs01.dbf
channel D1: starting piece 1 at 03-MAR-22
channel D2: finished piece 1 at 03-MAR-22
piece handle=/home/oracle/bkp/labcdb05_040nfsp7_1_1 tag=UPGRADE_DB comment=NONE
channel D2: backup set complete, elapsed time: 00:00:57
channel D2: starting compressed full datafile backup set
channel D2: specifying datafile(s) in backup set
input datafile file number=00005 name=/oradata/labcdb05/pdbseed/system01.dbf
channel D2: starting piece 1 at 03-MAR-22
channel D3: finished piece 1 at 03-MAR-22
piece handle=/home/oracle/bkp/labcdb05_060nfsq4_1_1 tag=UPGRADE_DB comment=NONE
channel D3: backup set complete, elapsed time: 00:00:26
channel D3: starting compressed full datafile backup set
channel D3: specifying datafile(s) in backup set
input datafile file number=00008 name=/oradata/labcdb05/pdbseed/undotbs01.dbf
channel D3: starting piece 1 at 03-MAR-22
channel D1: finished piece 1 at 03-MAR-22
piece handle=/home/oracle/bkp/labcdb05_070nfsr0_1_1 tag=UPGRADE_DB comment=NONE
channel D1: backup set complete, elapsed time: 00:00:28
channel D2: finished piece 1 at 03-MAR-22
piece handle=/home/oracle/bkp/labcdb05_080nfsr0_1_1 tag=UPGRADE_DB comment=NONE
channel D2: backup set complete, elapsed time: 00:00:28
channel D3: finished piece 1 at 03-MAR-22
piece handle=/home/oracle/bkp/labcdb05_090nfsr1_1_1 tag=UPGRADE_DB comment=NONE
channel D3: backup set complete, elapsed time: 00:00:25
Finished backup at 03-MAR-22
Starting backup at 03-MAR-22
current log archived
channel D1: starting compressed archived log backup set
channel D1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=5 STAMP=1098380157
channel D1: starting piece 1 at 03-MAR-22
channel D1: finished piece 1 at 03-MAR-22
piece handle=/home/oracle/bkp/labcdb05_0a0nfsrt_1_1 tag=UPGRADE_DB comment=NONE
channel D1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-MAR-22
Starting backup at 03-MAR-22
channel D1: starting full datafile backup set
channel D1: specifying datafile(s) in backup set
including current control file in backup set
channel D1: starting piece 1 at 03-MAR-22
channel D1: finished piece 1 at 03-MAR-22
piece handle=/home/oracle/bkp/labcdb05ctl_0b0nfsru tag=UPGRADE_CTL comment=NONE
channel D1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-MAR-22
Starting backup at 03-MAR-22
channel D1: starting full datafile backup set
channel D1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel D1: starting piece 1 at 03-MAR-22
channel D1: finished piece 1 at 03-MAR-22
piece handle=/home/oracle/bkp/labcdb05sf_0c0nfss0_1_1 tag=UPGRADE_SPFILE comment=NONE
channel D1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-MAR-22
Starting Control File and SPFILE Autobackup at 03-MAR-22
piece handle=/home/oracle/FRA/LABCDB05/autobackup/2022_03_03/o1_mf_s_1098380161_k21csb53_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 03-MAR-22
released channel: D1
released channel: D2
released channel: D3
RMAN>
[oracle@DBsGuruN2 labcdb05_upgrade]$ cd /home/oracle/bkp/
[oracle@DBsGuruN2 bkp]$ ls -lrt labcdb05_0* labcdb05ctl* labcdb05sf*
-rw-r-----. 1 oracle oinstall 91290624 Mar 3 17:34 labcdb05_020nfson_1_1
-rw-r-----. 1 oracle oinstall 118156800 Mar 3 17:34 labcdb05_010nfson_1_1
-rw-r-----. 1 oracle oinstall 111345664 Mar 3 17:34 labcdb05_050nfsp7_1_1
-rw-r-----. 1 oracle oinstall 215719936 Mar 3 17:35 labcdb05_030nfsp7_1_1
-rw-r-----. 1 oracle oinstall 116899840 Mar 3 17:35 labcdb05_040nfsp7_1_1
-rw-r-----. 1 oracle oinstall 112001024 Mar 3 17:35 labcdb05_060nfsq4_1_1
-rw-r-----. 1 oracle oinstall 1114112 Mar 3 17:35 labcdb05_090nfsr1_1_1
-rw-r-----. 1 oracle oinstall 55787520 Mar 3 17:35 labcdb05_080nfsr0_1_1
-rw-r-----. 1 oracle oinstall 56311808 Mar 3 17:35 labcdb05_070nfsr0_1_1
-rw-r-----. 1 oracle oinstall 1337344 Mar 3 17:35 labcdb05_0a0nfsrt_1_1
-rw-r-----. 1 oracle oinstall 18792448 Mar 3 17:35 labcdb05ctl_0b0nfsru
-rw-r-----. 1 oracle oinstall 114688 Mar 3 17:36 labcdb05sf_0c0nfss0_1_1
[oracle@DBsGuruN2 bkp]$
1.17: Review Hidden Parameters: Review hidden/underscore parameters and remove unwanted hidden parameters along with also recommended to create a PFILE prior to upgrading the database.
@CDB$ROOT> SELECT NAME, VALUE FROM SYS.V$PARAMETER WHERE NAME LIKE '\_%' ESCAPE '\' ORDER BY NAME;
no rows selected
@CDB$ROOT> CREATE PFILE FROM SPFILE;
File created.
1.18: Create Guaranteed Restore Point: You can also create a guaranteed restore point, to utilize this feature, your DB should be in archive log mode along with flashback ON with ample free space in FRA and this is a completely optional step depending on your requirements & criticality.
@CDB$ROOT> CREATE RESTORE POINT PRE_UPGRADE_LABCDB05 GUARANTEE FLASHBACK DATABASE;
Restore point created.
@CDB$ROOT> COL NAME FOR A25
@CDB$ROOT> COL GUARANTEE_FLASHBACK_DATABASE FOR A31
@CDB$ROOT> SELECT NAME,GUARANTEE_FLASHBACK_DATABASE,TIME FROM V$RESTORE_POINT;
NAME GUARANTEE_FLASHBACK_DATABASE TIME
------------------------- ------------------------------- ---------------------------------------------------------------------------
PRE_UPGRADE_LABCDB05 YES 03-MAR-22 05.44.59.000000000 PM
1 row selected.
1.19: Change the parameter CLUSTER_DATABASE to FALSE: To change the value of CLUSTER_DATABASE to FALSE is applicable only to the RAC database.
@CDB$ROOT> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
System altered.
1.20: Others:
1.20.1: Disable cronjobs/scheduler jobs/Triggers if any.
1.20.2: Blackout database in OEM. Click here to get steps for Target Blackouts in OEM 13c.
1.20.3: Stop all dependent applications.
1.21: Stop the database: Shutdown the database.
@CDB$ROOT> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
@CDB$ROOT> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@DBsGuruN2 ~]$
--For RAC:
[oracle@DBsGuruN2 ~]$ srvctl stop database -db <DB NAME>
1.22: Copy SPFILE & PASSWORD FILE: Copy SPFILE & PASSWORD FILE from 12c to 19c ORACLE HOME.
[oracle@DBsGuruN2 ~]$ cd $ORACLE_HOME/dbs
[oracle@DBsGuruN2 dbs]$ ls -lrt spfilelabcdb05.ora orapwlabcdb05
-rw-r-----. 1 oracle oinstall 3584 Mar 2 13:27 orapwlabcdb05
-rw-r-----. 1 oracle oinstall 3584 Mar 3 17:48 spfilelabcdb05.ora
[oracle@DBsGuruN2 dbs]$ cp orapwlabcdb05 spfilelabcdb05.ora /u01/app/oracle/product/1930/db_1/dbs/
[oracle@DBsGuruN2 dbs]$ cd /u01/app/oracle/product/1930/db_1/dbs/
[oracle@DBsGuruN2 dbs]$ ls -lrt spfilelabcdb05.ora orapwlabcdb05
-rw-r-----. 1 oracle oinstall 3584 Mar 3 17:51 spfilelabcdb05.ora
-rw-r-----. 1 oracle oinstall 3584 Mar 3 17:51 orapwlabcdb05
[oracle@DBsGuruN2 dbs]$
Manual Upgrade using command Steps
2.1: Startup in Upgrade Mode: Start the database in upgrade mode from 19c ORACLE_HOME.
[oracle@DBsGuruN2 dbs]$ export ORACLE_HOME=/u01/app/oracle/product/1930/db_1
[oracle@DBsGuruN2 dbs]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@DBsGuruN2 dbs]$ export ORACLE_SID=labcdb05
[oracle@DBsGuruN2 dbs]$ which sqlplus
/u01/app/oracle/product/1930/db_1/bin/sqlplus
[oracle@DBsGuruN2 dbs]$ sqlplus -v
SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@DBsGuruN2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 3 17:53:29 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> STARTUP UPGRADE
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1660941720 bytes
Fixed Size 9135512 bytes
Variable Size 1107296256 bytes
Database Buffers 536870912 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
Pluggable database altered.
SQL> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
3 DBSGURU01_PDB MIGRATE YES
SQL> EXIT
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
2.2: Execute dbupgrade command-line tool: To manually upgrade the 19c database in multitenant architecture, we can execute any one of the commands dbupgrade or catctl.pl.
$ORACLE_HOME/bin/dbupgrade -n 7 -l /home/oracle/bkp/labcdb05_upgrade/log
OR
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -n 7 -l /home/oracle/bkp/labdb03_upgrade/log catupgrd.sql
-n Maximum number of parallel SQL processes to use when upgrading the database. Multitenant database defaults to the total number of CPUs on your system. Traditional database defaults to 4.
-l Directory to use for spool log files.
Here we are upgrading using dbupgrade command-line tool and it took approx 1.5 hours to complete the upgrade and executed in the background using nohup. Follow the below:
oracle@DBsGuruN2 dbs]$ cd $ORACLE_HOME/bin/
[oracle@DBsGuruN2 bin]$ ls -lrt dbupgrade
-rwxr-x---. 1 oracle oinstall 3136 Apr 17 2019 dbupgrade
[oracle@DBsGuruN2 bin]$ nohup ./dbupgrade -l /home/oracle/bkp/labcdb05_upgrade/log &
[1] 17799
[oracle@DBsGuruN2 bin]$ nohup: ignoring input and appending output to ‘nohup.out’
[oracle@DBsGuruN2 bin]$ jobs -l
[1]+ 17799 Running nohup ./dbupgrade -l /home/oracle/bkp/labcdb05_upgrade/log &
[oracle@DBsGuruN2 bin]$ ls -lrt nohup.out
-rw-------. 1 oracle oinstall 12159 Mar 3 18:03 nohup.out
[oracle@DBsGuruN2 bin]$ tail -333f nohup.out
Argument list for [/u01/app/oracle/product/1930/db_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 = /home/oracle/bkp/labcdb05_upgrade/log
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/1930/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/1930/db_1]
/u01/app/oracle/product/1930/db_1/bin/orabasehome = [/u01/app/oracle/product/1930/db_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/1930/db_1]
Analyzing file /u01/app/oracle/product/1930/db_1/rdbms/admin/catupgrd.sql
Log file directory = [/home/oracle/bkp/labcdb05_upgrade/log]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/log/catupgrd_catcon_17804.lst]
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/log/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/log/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 4
Database Name = labcdb05
DataBase Version = 12.2.0.1.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/log/catupgrdcdbroot_catcon_17804.lst]
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/log/catupgrdcdbroot*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/log/catupgrdcdbroot_*.lst] files for spool files, if any
Log file directory = [/home/oracle/bkp/labcdb05_upgrade/log]
Parallel SQL Process Count (PDB) = 2
Parallel SQL Process Count (CDB$ROOT) = 4
Concurrent PDB Upgrades = 2
Generated PDB Inclusion:[PDB$SEED DBSGURU01_PDB]
Components in [CDB$ROOT]
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:[2022_03_03 18:03:32]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [CDB$ROOT] Files:1 Time: 57s
*************** Catalog Core SQL ***************
Serial Phase #:1 [CDB$ROOT] Files:5 Time: 54s
Restart Phase #:2 [CDB$ROOT] Files:1 Time: 4s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [CDB$ROOT] Files:19 Time: 27s
Restart Phase #:4 [CDB$ROOT] Files:1 Time: 3s
************* Catalog Final Scripts ************
Serial Phase #:5 [CDB$ROOT] Files:7 Time: 24s
***************** Catproc Start ****************
Serial Phase #:6 [CDB$ROOT] Files:1 Time: 19s
***************** Catproc Types ****************
Serial Phase #:7 [CDB$ROOT] Files:2 Time: 18s
Restart Phase #:8 [CDB$ROOT] Files:1 Time: 3s
**************** Catproc Tables ****************
Parallel Phase #:9 [CDB$ROOT] Files:67 Time: 39s
Restart Phase #:10 [CDB$ROOT] Files:1 Time: 3s
************* Catproc Package Specs ************
Serial Phase #:11 [CDB$ROOT] Files:1 Time: 89s
Restart Phase #:12 [CDB$ROOT] Files:1 Time: 3s
************** Catproc Procedures **************
Parallel Phase #:13 [CDB$ROOT] Files:94 Time: 21s
Restart Phase #:14 [CDB$ROOT] Files:1 Time: 4s
Parallel Phase #:15 [CDB$ROOT] Files:120 Time: 29s
Restart Phase #:16 [CDB$ROOT] Files:1 Time: 3s
Serial Phase #:17 [CDB$ROOT] Files:22 Time: 9s
Restart Phase #:18 [CDB$ROOT] Files:1 Time: 4s
***************** Catproc Views ****************
Parallel Phase #:19 [CDB$ROOT] Files:32 Time: 30s
Restart Phase #:20 [CDB$ROOT] Files:1 Time: 5s
Serial Phase #:21 [CDB$ROOT] Files:3 Time: 18s
Restart Phase #:22 [CDB$ROOT] Files:1 Time: 4s
Parallel Phase #:23 [CDB$ROOT] Files:25 Time: 110s
Restart Phase #:24 [CDB$ROOT] Files:1 Time: 3s
Parallel Phase #:25 [CDB$ROOT] Files:12 Time: 64s
Restart Phase #:26 [CDB$ROOT] Files:1 Time: 4s
Serial Phase #:27 [CDB$ROOT] Files:1 Time: 0s
Serial Phase #:28 [CDB$ROOT] Files:3 Time: 11s
Serial Phase #:29 [CDB$ROOT] Files:1 Time: 0s
Restart Phase #:30 [CDB$ROOT] Files:1 Time: 3s
*************** Catproc CDB Views **************
Serial Phase #:31 [CDB$ROOT] Files:1 Time: 6s
Restart Phase #:32 [CDB$ROOT] Files:1 Time: 5s
Serial Phase #:34 [CDB$ROOT] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [CDB$ROOT] Files:293 Time: 42s
Serial Phase #:36 [CDB$ROOT] Files:1 Time: 0s
Restart Phase #:37 [CDB$ROOT] Files:1 Time: 2s
Serial Phase #:38 [CDB$ROOT] Files:6 Time: 10s
Restart Phase #:39 [CDB$ROOT] Files:1 Time: 4s
*************** Catproc DataPump ***************
Serial Phase #:40 [CDB$ROOT] Files:3 Time: 44s
Restart Phase #:41 [CDB$ROOT] Files:1 Time: 4s
****************** Catproc SQL *****************
Parallel Phase #:42 [CDB$ROOT] Files:13 Time: 63s
Restart Phase #:43 [CDB$ROOT] Files:1 Time: 4s
Parallel Phase #:44 [CDB$ROOT] Files:11 Time: 13s
Restart Phase #:45 [CDB$ROOT] Files:1 Time: 3s
Parallel Phase #:46 [CDB$ROOT] Files:3 Time: 9s
Restart Phase #:47 [CDB$ROOT] Files:1 Time: 3s
************* Final Catproc scripts ************
Serial Phase #:48 [CDB$ROOT] Files:1 Time: 14s
Restart Phase #:49 [CDB$ROOT] Files:1 Time: 4s
************** Final RDBMS scripts *************
Serial Phase #:50 [CDB$ROOT] Files:1 Time: 9s
************ Upgrade Component Start ***********
Serial Phase #:51 [CDB$ROOT] Files:1 Time: 6s
Restart Phase #:52 [CDB$ROOT] Files:1 Time: 4s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [CDB$ROOT] Files:2 Time: 344s
***************** Upgrading XDB ****************
Restart Phase #:54 [CDB$ROOT] Files:1 Time: 2s
Serial Phase #:56 [CDB$ROOT] Files:3 Time: 15s
Serial Phase #:57 [CDB$ROOT] Files:3 Time: 12s
Parallel Phase #:58 [CDB$ROOT] Files:10 Time: 13s
Parallel Phase #:59 [CDB$ROOT] Files:25 Time: 15s
Serial Phase #:60 [CDB$ROOT] Files:4 Time: 16s
Serial Phase #:61 [CDB$ROOT] Files:1 Time: 0s
Serial Phase #:62 [CDB$ROOT] Files:32 Time: 11s
Serial Phase #:63 [CDB$ROOT] Files:1 Time: 0s
Parallel Phase #:64 [CDB$ROOT] Files:6 Time: 14s
Serial Phase #:65 [CDB$ROOT] Files:2 Time: 22s
Serial Phase #:66 [CDB$ROOT] Files:3 Time: 28s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [CDB$ROOT] Files:1 Time: 4s
Serial Phase #:69 [CDB$ROOT] Files:1 Time: 9s
Parallel Phase #:70 [CDB$ROOT] Files:2 Time: 34s
Restart Phase #:71 [CDB$ROOT] Files:1 Time: 3s
Parallel Phase #:72 [CDB$ROOT] Files:2 Time: 10s
Serial Phase #:73 [CDB$ROOT] Files:2 Time: 9s
***************** Upgrading SDO ****************
Restart Phase #:74 [CDB$ROOT] Files:1 Time: 4s
Serial Phase #:76 [CDB$ROOT] Files:1 Time: 50s
Serial Phase #:77 [CDB$ROOT] Files:2 Time: 10s
Restart Phase #:78 [CDB$ROOT] Files:1 Time: 4s
Serial Phase #:79 [CDB$ROOT] Files:1 Time: 39s
Restart Phase #:80 [CDB$ROOT] Files:1 Time: 3s
Parallel Phase #:81 [CDB$ROOT] Files:3 Time: 80s
Restart Phase #:82 [CDB$ROOT] Files:1 Time: 4s
Serial Phase #:83 [CDB$ROOT] Files:1 Time: 12s
Restart Phase #:84 [CDB$ROOT] Files:1 Time: 4s
Serial Phase #:85 [CDB$ROOT] Files:1 Time: 17s
Restart Phase #:86 [CDB$ROOT] Files:1 Time: 3s
Parallel Phase #:87 [CDB$ROOT] Files:4 Time: 88s
Restart Phase #:88 [CDB$ROOT] Files:1 Time: 3s
Serial Phase #:89 [CDB$ROOT] Files:1 Time: 9s
Restart Phase #:90 [CDB$ROOT] Files:1 Time: 4s
Serial Phase #:91 [CDB$ROOT] Files:2 Time: 14s
Restart Phase #:92 [CDB$ROOT] Files:1 Time: 4s
Serial Phase #:93 [CDB$ROOT] Files:1 Time: 7s
Restart Phase #:94 [CDB$ROOT] Files:1 Time: 3s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [CDB$ROOT] Files:1 Time: 21s
Restart Phase #:96 [CDB$ROOT] Files:1 Time: 2s
*********** Final Component scripts ***********
Serial Phase #:97 [CDB$ROOT] Files:1 Time: 9s
************* Final Upgrade scripts ************
Serial Phase #:98 [CDB$ROOT] Files:1 Time: 241s
******************* Migration ******************
Serial Phase #:99 [CDB$ROOT] Files:1 Time: 7s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [CDB$ROOT] Files:1 Time: 20s
Serial Phase #:101 [CDB$ROOT] Files:1 Time: 6s
Serial Phase #:102 [CDB$ROOT] Files:1 Time: 79s
***************** Post Upgrade *****************
Serial Phase #:103 [CDB$ROOT] Files:1 Time: 34s
**************** Summary report ****************
Serial Phase #:104 [CDB$ROOT] Files:1 Time: 8s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [CDB$ROOT] Files:1 Time: 10s
Serial Phase #:106 [CDB$ROOT] Files:1 Time: 6s
Serial Phase #:107 [CDB$ROOT] Files:1 Time: 57s
------------------------------------------------------
Phases [0-107] End Time:[2022_03_03 18:42:30]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[NONE]
------------------------------------------------------
Start processing of PDBs (PDB$SEED)
[/u01/app/oracle/product/1930/db_1/perl/bin/perl /u01/app/oracle/product/1930/db_1/rdbms/admin/catctl.pl -l /home/oracle/bkp/labcdb05_upgrade/log -I -i pdb_seed -n 2 -c 'PDB$SEED' /u01/app/oracle/product/1930/db_1/rdbms/admin/catupgrd.sql]
Start processing of PDBs (DBSGURU01_PDB)
[/u01/app/oracle/product/1930/db_1/perl/bin/perl /u01/app/oracle/product/1930/db_1/rdbms/admin/catctl.pl -l /home/oracle/bkp/labcdb05_upgrade/log -I -i dbsguru01_pdb -n 2 -c 'DBSGURU01_PDB' /u01/app/oracle/product/1930/db_1/rdbms/admin/catupgrd.sql]
Argument list for [/u01/app/oracle/product/1930/db_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = PDB$SEED
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = pdb_seed
Child Process I = 1
Log Dir l = /home/oracle/bkp/labcdb05_upgrade/log
Priority List Name L = 0
Argument list for [/u01/app/oracle/product/1930/db_1/rdbms/admin/catctl.pl]
Upgrade Mode active M = 0
For Oracle internal use only A = 0
SQL Process Count n = 2
Run in c = DBSGURU01_PDB
SQL PDB Process Count N = 0
Do not run in C = 0
Open Mode Normal o = 0
Input Directory d = 0
Start Phase p = 0
Echo OFF e = 1
End Phase P = 0
Simulate E = 0
Reverse Order r = 0
Forced cleanup F = 0
AutoUpgrade Resume R = 0
Log Id i = dbsguru01_pdb
Script s = 0
Child Process I = 1
Serial Run S = 0
Log Dir l = /home/oracle/bkp/labcdb05_upgrade/log
RO User Tablespaces T = 0
Priority List Name L = 0
Display Phases y = 0
Upgrade Mode active M = 0
Debug catcon.pm z = 0
SQL Process Count n = 2
Debug catctl.pl Z = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
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/1930/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/1930/db_1]
/u01/app/oracle/product/1930/db_1/bin/orabasehome = [/u01/app/oracle/product/1930/db_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/1930/db_1]
Analyzing file /u01/app/oracle/product/1930/db_1/rdbms/admin/catupgrd.sql
Log file directory = [/home/oracle/bkp/labcdb05_upgrade/log]
/u01/app/oracle/product/1930/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/1930/db_1]
/u01/app/oracle/product/1930/db_1/bin/orabasehome = [/u01/app/oracle/product/1930/db_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/1930/db_1]
Analyzing file /u01/app/oracle/product/1930/db_1/rdbms/admin/catupgrd.sql
Log file directory = [/home/oracle/bkp/labcdb05_upgrade/log]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/log/catupgrdpdb_seed_catcon_26588.lst]
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/log/catupgrdpdb_seed*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/log/catupgrdpdb_seed_*.lst] files for spool files, if any
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/log/catupgrddbsguru01_pdb_catcon_26590.lst]
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/log/catupgrddbsguru01_pdb*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/log/catupgrddbsguru01_pdb_*.lst] files for spool files, if any
Number of Cpus = 4
Number of Cpus = 4
Database Name = labcdb05
Database Name = labcdb05
DataBase Version = 19.0.0.0.0
DataBase Version = 19.0.0.0.0
PDB$SEED Open Mode = [MIGRATE]
DBSGURU01_PDB Open Mode = [MIGRATE]
Generated PDB Inclusion:[DBSGURU01_PDB]
Generated PDB Inclusion:[PDB$SEED]
CDB$ROOT Open Mode = [OPEN]
CDB$ROOT Open Mode = [OPEN]
Components in [PDB$SEED]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM RAC WK]
Components in [DBSGURU01_PDB]
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:[2022_03_03 18:43:14]
Container Lists Inclusion:[PDB$SEED] Exclusion:[NONE]
------------------------------------------------------
------------------------------------------------------
Phases [0-107] Start Time:[2022_03_03 18:43:14]
Container Lists Inclusion:[DBSGURU01_PDB] Exclusion:[NONE]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [DBSGURU01_PDB] Files:1 *********** Executing Change Scripts ***********
Serial Phase #:0 [PDB$SEED] Files:1 Time: 76s
*************** Catalog Core SQL ***************
Serial Phase #:1 [PDB$SEED] Files:5 Time: 76s
*************** Catalog Core SQL ***************
Serial Phase #:1 [DBSGURU01_PDB] Files:5 Time: 65s
Restart Phase #:2 [PDB$SEED] Files:1 Time: 65s
Restart Phase #:2 [DBSGURU01_PDB] Files:1 Time: 2s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [PDB$SEED] Files:19 Time: 2s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [DBSGURU01_PDB] Files:19 Time: 29s
Restart Phase #:4 [DBSGURU01_PDB] Files:1 Time: 29s
Restart Phase #:4 [PDB$SEED] Files:1 Time: 2s
************* Catalog Final Scripts ************
Serial Phase #:5 [PDB$SEED] Files:7 Time: 2s
************* Catalog Final Scripts ************
Serial Phase #:5 [DBSGURU01_PDB] Files:7 Time: 26s
***************** Catproc Start ****************
Serial Phase #:6 [DBSGURU01_PDB] Files:1 Time: 26s
***************** Catproc Start ****************
Serial Phase #:6 [PDB$SEED] Files:1 Time: 22s
***************** Catproc Types ****************
Serial Phase #:7 [PDB$SEED] Files:2 Time: 22s
***************** Catproc Types ****************
Serial Phase #:7 [DBSGURU01_PDB] Files:2 Time: 22s
Restart Phase #:8 [PDB$SEED] Files:1 Time: 22s
Restart Phase #:8 [DBSGURU01_PDB] Files:1 Time: 2s
**************** Catproc Tables ****************
Parallel Phase #:9 [PDB$SEED] Files:67 Time: 2s
**************** Catproc Tables ****************
Parallel Phase #:9 [DBSGURU01_PDB] Files:67 Time: 62s
Restart Phase #:10 [DBSGURU01_PDB] Files:1 Time: 62s
Restart Phase #:10 [PDB$SEED] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [PDB$SEED] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [DBSGURU01_PDB] Files:1 Time: 107s
Restart Phase #:12 [PDB$SEED] Files:1 Time: 107s
Restart Phase #:12 [DBSGURU01_PDB] Files:1 Time: 2s
Time: 2s
************** Catproc Procedures **************
************** Catproc Procedures **************
Parallel Phase #:13 [PDB$SEED] Files:94 Parallel Phase #:13 [DBSGURU01_PDB] Files:94 Time: 20s
Restart Phase #:14 [PDB$SEED] Files:1 Time: 20s
Restart Phase #:14 [DBSGURU01_PDB] Files:1 Time: 2s
Parallel Phase #:15 [DBSGURU01_PDB] Files:120 Time: 2s
Parallel Phase #:15 [PDB$SEED] Files:120 Time: 30s
Restart Phase #:16 [DBSGURU01_PDB] Files:1 Time: 30s
Restart Phase #:16 [PDB$SEED] Files:1 Time: 2s
Serial Phase #:17 [PDB$SEED] Files:22 Time: 2s
Serial Phase #:17 [DBSGURU01_PDB] Files:22 Time: 10s
Restart Phase #:18 [DBSGURU01_PDB] Files:1 Time: 10s
Restart Phase #:18 [PDB$SEED] Files:1 Time: 2s
***************** Catproc Views ****************
Parallel Phase #:19 [PDB$SEED] Files:32 Time: 2s
***************** Catproc Views ****************
Parallel Phase #:19 [DBSGURU01_PDB] Files:32 Time: 40s
Restart Phase #:20 [DBSGURU01_PDB] Files:1 Time: 40s
Restart Phase #:20 [PDB$SEED] Files:1 Time: 1s
Serial Phase #:21 [DBSGURU01_PDB] Files:3 Time: 1s
Serial Phase #:21 [PDB$SEED] Files:3 Time: 22s
Restart Phase #:22 [PDB$SEED] Files:1 Time: 22s
Restart Phase #:22 [DBSGURU01_PDB] Files:1 Time: 1s
Parallel Phase #:23 [DBSGURU01_PDB] Files:25 Time: 2s
Parallel Phase #:23 [PDB$SEED] Files:25 Time: 272s
Restart Phase #:24 [DBSGURU01_PDB] Files:1 Time: 271s
Restart Phase #:24 [PDB$SEED] Files:1 Time: 2s
Parallel Phase #:25 [DBSGURU01_PDB] Files:12 Time: 2s
Parallel Phase #:25 [PDB$SEED] Files:12 Time: 87s
Restart Phase #:26 [DBSGURU01_PDB] Files:1 Time: 87s
Restart Phase #:26 [PDB$SEED] Files:1 Time: 1s
Serial Phase #:27 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:28 [PDB$SEED] Files:3 Time: 1s
Serial Phase #:27 [DBSGURU01_PDB] Files:1 Time: 0s
Serial Phase #:28 [DBSGURU01_PDB] Files:3 Time: 26s
Serial Phase #:29 [PDB$SEED] Files:1 Time: 0s
Restart Phase #:30 [PDB$SEED] Files:1 Time: 26s
Serial Phase #:29 [DBSGURU01_PDB] Files:1 Time: 0s
Restart Phase #:30 [DBSGURU01_PDB] Files:1 Time: 2s
*************** Catproc CDB Views **************
Serial Phase #:31 [PDB$SEED] Files:1 Time: 2s
*************** Catproc CDB Views **************
Serial Phase #:31 [DBSGURU01_PDB] Files:1 Time: 6s
Restart Phase #:32 [PDB$SEED] Files:1 Time: 6s
Restart Phase #:32 [DBSGURU01_PDB] Files:1 Time: 2s
Serial Phase #:34 [PDB$SEED] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [PDB$SEED] Files:293 Time: 2s
Serial Phase #:34 [DBSGURU01_PDB] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [DBSGURU01_PDB] Files:293 Time: 111s
Serial Phase #:36 [DBSGURU01_PDB] Files:1 Time: 0s
Restart Phase #:37 [DBSGURU01_PDB] Files:1 Time: 111s
Serial Phase #:36 [PDB$SEED] Files:1 Time: 0s
Restart Phase #:37 [PDB$SEED] Files:1 Time: 2s
Serial Phase #:38 [PDB$SEED] Files:6 Time: 2s
Serial Phase #:38 [DBSGURU01_PDB] Files:6 Time: 11s
Restart Phase #:39 [DBSGURU01_PDB] Files:1 Time: 11s
Restart Phase #:39 [PDB$SEED] Files:1 Time: 1s
*************** Catproc DataPump ***************
Serial Phase #:40 [PDB$SEED] Files:3 Time: 1s
*************** Catproc DataPump ***************
Serial Phase #:40 [DBSGURU01_PDB] Files:3 Time: 53s
Restart Phase #:41 [PDB$SEED] Files:1 Time: 53s
Restart Phase #:41 [DBSGURU01_PDB] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [PDB$SEED] Files:13 Time: 2s
****************** Catproc SQL *****************
Parallel Phase #:42 [DBSGURU01_PDB] Files:13 Time: 85s
Restart Phase #:43 [PDB$SEED] Files:1 Time: 0s
Parallel Phase #:44 [PDB$SEED] Files:11 Time: 86s
Restart Phase #:43 [DBSGURU01_PDB] Files:1 Time: 0s
Parallel Phase #:44 [DBSGURU01_PDB] Files:11 Time: 14s
Restart Phase #:45 [PDB$SEED] Files:1 Time: 12s
Restart Phase #:45 [DBSGURU01_PDB] Files:1 Time: 1s
Parallel Phase #:46 [PDB$SEED] Files:3 Time: 1s
Parallel Phase #:46 [DBSGURU01_PDB] Files:3 Time: 8s
Restart Phase #:47 [PDB$SEED] Files:1 Time: 9s
Restart Phase #:47 [DBSGURU01_PDB] Files:1 Time: 2s
************* Final Catproc scripts ************
Serial Phase #:48 [PDB$SEED] Files:1 Time: 1s
************* Final Catproc scripts ************
Serial Phase #:48 [DBSGURU01_PDB] Files:1 Time: 27s
Restart Phase #:49 [PDB$SEED] Files:1 Time: 27s
Restart Phase #:49 [DBSGURU01_PDB] Files:1 Time: 2s
************** Final RDBMS scripts *************
Serial Phase #:50 [DBSGURU01_PDB] Files:1 Time: 2s
************** Final RDBMS scripts *************
Serial Phase #:50 [PDB$SEED] Files:1 Time: 10s
************ Upgrade Component Start ***********
Serial Phase #:51 [PDB$SEED] Files:1 Time: 11s
************ Upgrade Component Start ***********
Serial Phase #:51 [DBSGURU01_PDB] Files:1 Time: 7s
Restart Phase #:52 [PDB$SEED] Files:1 Time: 6s
Restart Phase #:52 [DBSGURU01_PDB] Files:1 Time: 1s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [PDB$SEED] Files:2 Time: 2s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [DBSGURU01_PDB] Files:2 Time: 384s
***************** Upgrading XDB ****************
Time: 383s
Restart Phase #:54 [PDB$SEED] Files:1 ***************** Upgrading XDB ****************
Restart Phase #:54 [DBSGURU01_PDB] Files:1 Time: 2s
Serial Phase #:56 [DBSGURU01_PDB] Files:3 Time: 2s
Serial Phase #:56 [PDB$SEED] Files:3 Time: 19s
Serial Phase #:57 [DBSGURU01_PDB] Files:3 Time: 19s
Serial Phase #:57 [PDB$SEED] Files:3 Time: 12s
Parallel Phase #:58 [PDB$SEED] Files:10 Time: 12s
Parallel Phase #:58 [DBSGURU01_PDB] Files:10 Time: 14s
Parallel Phase #:59 [PDB$SEED] Files:25 Time: 14s
Parallel Phase #:59 [DBSGURU01_PDB] Files:25 Time: 16s
Serial Phase #:60 [PDB$SEED] Files:4 Time: 16s
Serial Phase #:60 [DBSGURU01_PDB] Files:4 Time: 21s
Serial Phase #:61 [DBSGURU01_PDB] Files:1 Time: 0s
Serial Phase #:62 [DBSGURU01_PDB] Files:32 Time: 21s
Serial Phase #:61 [PDB$SEED] Files:1 Time: 0s
Serial Phase #:62 [PDB$SEED] Files:32 Time: 28s
Serial Phase #:63 [PDB$SEED] Files:1 Time: 0s
Parallel Phase #:64 [PDB$SEED] Files:6 Time: 28s
Serial Phase #:63 [DBSGURU01_PDB] Files:1 Time: 0s
Parallel Phase #:64 [DBSGURU01_PDB] Files:6 Time: 15s
Serial Phase #:65 [PDB$SEED] Files:2 Time: 15s
Serial Phase #:65 [DBSGURU01_PDB] Files:2 Time: 31s
Serial Phase #:66 [DBSGURU01_PDB] Files:3 Time: 31s
Serial Phase #:66 [PDB$SEED] Files:3 Time: 42s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [PDB$SEED] Files:1 Time: 42s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [DBSGURU01_PDB] Files:1 Time: 2s
Serial Phase #:69 [DBSGURU01_PDB] Files:1 Time: 2s
Serial Phase #:69 [PDB$SEED] Files:1 Time: 9s
Parallel Phase #:70 [PDB$SEED] Files:2 Time: 9s
Parallel Phase #:70 [DBSGURU01_PDB] Files:2 Time: 30s
Restart Phase #:71 [PDB$SEED] Files:1 Time: 30s
Restart Phase #:71 [DBSGURU01_PDB] Files:1 Time: 2s
Parallel Phase #:72 [DBSGURU01_PDB] Files:2 Time: 2s
Parallel Phase #:72 [PDB$SEED] Files:2 Time: 10s
Serial Phase #:73 [DBSGURU01_PDB] Files:2 Time: 10s
Serial Phase #:73 [PDB$SEED] Files:2 Time: 9s
***************** Upgrading SDO ****************
Restart Phase #:74 [DBSGURU01_PDB] Files:1 Time: 9s
***************** Upgrading SDO ****************
Restart Phase #:74 [PDB$SEED] Files:1 Time: 1s
Serial Phase #:76 [PDB$SEED] Files:1 Time: 1s
Serial Phase #:76 [DBSGURU01_PDB] Files:1 Time: 93s
Serial Phase #:77 [DBSGURU01_PDB] Files:2 Time: 94s
Serial Phase #:77 [PDB$SEED] Files:2 Time: 10s
Restart Phase #:78 [DBSGURU01_PDB] Files:1 Time: 10s
Restart Phase #:78 [PDB$SEED] Files:1 Time: 2s
Serial Phase #:79 [DBSGURU01_PDB] Files:1 Time: 2s
Serial Phase #:79 [PDB$SEED] Files:1 Time: 16s
Restart Phase #:80 [DBSGURU01_PDB] Files:1 Time: 15s
Restart Phase #:80 [PDB$SEED] Files:1 Time: 1s
Parallel Phase #:81 [PDB$SEED] Files:3 Time: 1s
Parallel Phase #:81 [DBSGURU01_PDB] Files:3 Time: 102s
Restart Phase #:82 [DBSGURU01_PDB] Files:1 Time: 103s
Restart Phase #:82 [PDB$SEED] Files:1 Time: 2s
Serial Phase #:83 [DBSGURU01_PDB] Files:1 Time: 2s
Serial Phase #:83 [PDB$SEED] Files:1 Time: 15s
Restart Phase #:84 [PDB$SEED] Files:1 Time: 16s
Restart Phase #:84 [DBSGURU01_PDB] Files:1 Time: 2s
Serial Phase #:85 [PDB$SEED] Files:1 Time: 2s
Serial Phase #:85 [DBSGURU01_PDB] Files:1 Time: 23s
Restart Phase #:86 [PDB$SEED] Files:1 Time: 23s
Restart Phase #:86 [DBSGURU01_PDB] Files:1 Time: 0s
Parallel Phase #:87 [DBSGURU01_PDB] Files:4 Time: 1s
Parallel Phase #:87 [PDB$SEED] Files:4 Time: 182s
Restart Phase #:88 [DBSGURU01_PDB] Files:1 Time: 181s
Restart Phase #:88 [PDB$SEED] Files:1 Time: 2s
Serial Phase #:89 [PDB$SEED] Files:1 Time: 2s
Serial Phase #:89 [DBSGURU01_PDB] Files:1 Time: 8s
Restart Phase #:90 [PDB$SEED] Files:1 Time: 9s
Restart Phase #:90 [DBSGURU01_PDB] Files:1 Time: 0s
Serial Phase #:91 [DBSGURU01_PDB] Files:2 Time: 2s
Serial Phase #:91 [PDB$SEED] Files:2 Time: 24s
Restart Phase #:92 [DBSGURU01_PDB] Files:1 Time: 23s
Restart Phase #:92 [PDB$SEED] Files:1 Time: 2s
Serial Phase #:93 [PDB$SEED] Files:1 Time: 2s
Serial Phase #:93 [DBSGURU01_PDB] Files:1 Time: 6s
Restart Phase #:94 [DBSGURU01_PDB] Files:1 Time: 6s
Restart Phase #:94 [PDB$SEED] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [PDB$SEED] Files:1 Time: 2s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [DBSGURU01_PDB] Files:1 Time: 32s
Restart Phase #:96 [DBSGURU01_PDB] Files:1 Time: 33s
Restart Phase #:96 [PDB$SEED] Files:1 Time: 2s
*********** Final Component scripts ***********
Serial Phase #:97 [PDB$SEED] Files:1 Time: 2s
*********** Final Component scripts ***********
Serial Phase #:97 [DBSGURU01_PDB] Files:1 Time: 8s
************* Final Upgrade scripts ************
Serial Phase #:98 [DBSGURU01_PDB] Files:1 Time: 8s
************* Final Upgrade scripts ************
Serial Phase #:98 [PDB$SEED] Files:1 Time: 247s
******************* Migration ******************
Time: 247s
Serial Phase #:99 [PDB$SEED] Files:1 ******************* Migration ******************
Serial Phase #:99 [DBSGURU01_PDB] Files:1 Time: 7s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [DBSGURU01_PDB] Files:1 Time: 7s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [PDB$SEED] Files:1 Time: 6s
Serial Phase #:101 [PDB$SEED] Files:1 Time: 6s
Serial Phase #:101 [DBSGURU01_PDB] Files:1 Time: 13s
Serial Phase #:102 [PDB$SEED] Files:1 Time: 13s
Serial Phase #:102 [DBSGURU01_PDB] Files:1 Time: 18s
***************** Post Upgrade *****************
Serial Phase #:103 [DBSGURU01_PDB] Files:1 Time: 18s
***************** Post Upgrade *****************
Serial Phase #:103 [PDB$SEED] Files:1 Time: 60s
**************** Summary report ****************
Serial Phase #:104 [DBSGURU01_PDB] Files:1 Time: 9s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [DBSGURU01_PDB] Files:1 Time: 6s
Serial Phase #:106 [DBSGURU01_PDB] Files:1 Time: 8s
Serial Phase #:107 [DBSGURU01_PDB] Files:1 Time: 0s
------------------------------------------------------
Phases [0-107] End Time:[2022_03_03 19:30:34]
Container Lists Inclusion:[DBSGURU01_PDB] Exclusion:[NONE]
------------------------------------------------------
Grand Total Time: 2841s [DBSGURU01_PDB]
LOG FILES: (/home/oracle/bkp/labcdb05_upgrade/log/catupgrddbsguru01_pdb*.log)
Upgrade Summary Report Located in:
/home/oracle/bkp/labcdb05_upgrade/log/upg_summary.log
Time: 359s
**************** Summary report ****************
Serial Phase #:104 [PDB$SEED] Files:1 Time: 7s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [PDB$SEED] Files:1 Time: 6s
Serial Phase #:106 [PDB$SEED] Files:1 Time: 8s
Serial Phase #:107 [PDB$SEED] Files:1 Time: 0s
------------------------------------------------------
Phases [0-107] End Time:[2022_03_03 19:35:31]
Container Lists Inclusion:[PDB$SEED] Exclusion:[NONE]
------------------------------------------------------
Grand Total Time: 3147s [PDB$SEED]
LOG FILES: (/home/oracle/bkp/labcdb05_upgrade/log/catupgrdpdb_seed*.log)
Upgrade Summary Report Located in:
/home/oracle/bkp/labcdb05_upgrade/log/upg_summary.log
Time: 2337s For CDB$ROOT
Time: 3196s For PDB(s)
Grand Total Time: 5533s
LOG FILES: (/home/oracle/bkp/labcdb05_upgrade/log/catupgrdcdbroot*.log)
Upgrade Summary Report Located in:
/home/oracle/bkp/labcdb05_upgrade/log/upg_summary.log
Grand Total Upgrade Time: [0d:1h:32m:13s]
3. Post upgrade Steps
3.1: Review Upgrade Logs: Go to upgrade log location and review log files.
[oracle@DBsGuruN2 bin]$ cd /home/oracle/bkp/labcdb05_upgrade/log/
[oracle@DBsGuruN2 log]$ ls -lrt
total 213180
-rw-------. 1 oracle oinstall 480 Mar 3 18:03 catupgrdcdbroot_catcon_17804.lst
-rw-r--r--. 1 oracle oinstall 0 Mar 3 18:34 catupgrdcdbroot_datapatch_upgrade.err
-rw-r--r--. 1 oracle oinstall 1622 Mar 3 18:38 catupgrdcdbroot_datapatch_upgrade.log
-rw-------. 1 oracle oinstall 8591299 Mar 3 18:42 catupgrdcdbroot1.log
-rw-------. 1 oracle oinstall 7539292 Mar 3 18:42 catupgrdcdbroot2.log
-rw-------. 1 oracle oinstall 7547379 Mar 3 18:42 catupgrdcdbroot3.log
-rw-------. 1 oracle oinstall 482 Mar 3 18:42 catupgrdpdb_seed_catcon_26588.lst
-rw-------. 1 oracle oinstall 492 Mar 3 18:42 catupgrddbsguru01_pdb_catcon_26590.lst
-rw-r--r--. 1 oracle oinstall 0 Mar 3 19:24 catupgrdpdb_seed_datapatch_upgrade.err
-rw-r--r--. 1 oracle oinstall 0 Mar 3 19:24 catupgrddbsguru01_pdb_datapatch_upgrade.err
-rw-r--r--. 1 oracle oinstall 1621 Mar 3 19:28 catupgrdpdb_seed_datapatch_upgrade.log
-rw-r--r--. 1 oracle oinstall 1642 Mar 3 19:28 catupgrddbsguru01_pdb_datapatch_upgrade.log
-rw-r--r--. 1 oracle oinstall 11432 Mar 3 19:30 catupgrddbsguru01_pdb_stderr.log
-rw-------. 1 oracle oinstall 56954199 Mar 3 19:30 catupgrddbsguru01_pdb0.log
-rw-------. 1 oracle oinstall 15431097 Mar 3 19:30 catupgrddbsguru01_pdb1.log
-rw-r--r--. 1 oracle oinstall 10926 Mar 3 19:35 catupgrdpdb_seed_stderr.log
-rw-------. 1 oracle oinstall 57052454 Mar 3 19:35 catupgrdpdb_seed0.log
-rw-------. 1 oracle oinstall 15319774 Mar 3 19:35 catupgrdpdb_seed1.log
-rw-r--r--. 1 oracle oinstall 39056 Mar 3 19:35 catupgrdcdbroot_stderr.log
-rw-r--r--. 1 oracle oinstall 5646 Mar 3 19:35 upg_summary.log
-rw-------. 1 oracle oinstall 49533675 Mar 3 19:35 catupgrdcdbroot0.log
[oracle@DBsGuruN2 log]$ cat upg_summary.log
Oracle Database Release 19 Post-Upgrade Status Tool 03-03-2022 18:41:1
Container Database: LABCDB05
[CON_ID: 1 => CDB$ROOT]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.3.0.0.0 00:14:58
JServer JAVA Virtual Machine UPGRADED 19.3.0.0.0 00:02:04
Oracle XDK UPGRADED 19.3.0.0.0 00:00:35
Oracle Database Java Packages UPGRADED 19.3.0.0.0 00:00:10
OLAP Analytic Workspace UPGRADED 19.3.0.0.0 00:00:35
Oracle Label Security UPGRADED 19.3.0.0.0 00:00:14
Oracle Database Vault UPGRADED 19.3.0.0.0 00:00:28
Oracle Text UPGRADED 19.3.0.0.0 00:00:49
Oracle Workspace Manager UPGRADED 19.3.0.0.0 00:00:37
Oracle Real Application Clusters UPGRADED 19.3.0.0.0 00:00:00
Oracle XML Database UPGRADED 19.3.0.0.0 00:02:16
Oracle Multimedia UPGRADED 19.3.0.0.0 00:00:55
Spatial UPGRADED 19.3.0.0.0 00:05:35
Oracle OLAP API UPGRADED 19.3.0.0.0 00:00:13
Datapatch 00:03:34
Final Actions 00:04:07
Post Upgrade 00:00:24
Total Upgrade Time: 00:35:30 [CON_ID: 1 => CDB$ROOT]
Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.
Oracle Database Release 19 Post-Upgrade Status Tool 03-03-2022 19:30:1
Container Database: LABCDB05
[CON_ID: 3 => DBSGURU01_PDB]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.3.0.0.0 00:21:04
JServer JAVA Virtual Machine UPGRADED 19.3.0.0.0 00:01:38
Oracle XDK UPGRADED 19.3.0.0.0 00:00:31
Oracle Database Java Packages UPGRADED 19.3.0.0.0 00:00:08
OLAP Analytic Workspace UPGRADED 19.3.0.0.0 00:00:45
Oracle Label Security UPGRADED 19.3.0.0.0 00:00:17
Oracle Database Vault UPGRADED 19.3.0.0.0 00:01:25
Oracle Text UPGRADED 19.3.0.0.0 00:00:41
Oracle Workspace Manager UPGRADED 19.3.0.0.0 00:00:43
Oracle Real Application Clusters UPGRADED 19.3.0.0.0 00:00:01
Oracle XML Database UPGRADED 19.3.0.0.0 00:03:09
Oracle Multimedia UPGRADED 19.3.0.0.0 00:00:51
Spatial UPGRADED 19.3.0.0.0 00:07:54
Oracle OLAP API UPGRADED 19.3.0.0.0 00:00:25
Datapatch 00:03:53
Final Actions 00:04:13
Post Upgrade 00:00:49
Total Upgrade Time: 00:45:52 [CON_ID: 3 => DBSGURU01_PDB]
Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.
Oracle Database Release 19 Post-Upgrade Status Tool 03-03-2022 19:35:1
Container Database: LABCDB05
[CON_ID: 2 => PDB$SEED]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.3.0.0.0 00:21:02
JServer JAVA Virtual Machine VALID 19.3.0.0.0 00:01:39
Oracle XDK VALID 19.3.0.0.0 00:00:31
Oracle Database Java Packages VALID 19.3.0.0.0 00:00:08
OLAP Analytic Workspace VALID 19.3.0.0.0 00:00:44
Oracle Label Security VALID 19.3.0.0.0 00:00:18
Oracle Database Vault VALID 19.3.0.0.0 00:01:25
Oracle Text VALID 19.3.0.0.0 00:00:41
Oracle Workspace Manager VALID 19.3.0.0.0 00:00:44
Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:01
Oracle XML Database VALID 19.3.0.0.0 00:03:09
Oracle Multimedia VALID 19.3.0.0.0 00:00:51
Spatial VALID 19.3.0.0.0 00:07:54
Oracle OLAP API VALID 19.3.0.0.0 00:00:25
Datapatch 00:03:52
Final Actions 00:04:13
Post Upgrade 00:00:49
Post Compile 00:04:49
Total Upgrade Time: 00:50:41 [CON_ID: 2 => PDB$SEED * ]
Asterisks denotes compilation time has been included during the upgrade process.
Database time zone version is 26. It is older than current release time
zone version 32. Time zone upgrade is needed using the DBMS_DST package.
Upgrade Times Sorted In Descending Order
Total Upgrade Time: 00:50:41 [CON_ID: 2 => PDB$SEED * ]
Total Upgrade Time: 00:45:52 [CON_ID: 3 => DBSGURU01_PDB]
Total Upgrade Time: 00:35:30 [CON_ID: 1 => CDB$ROOT]
Grand Total Upgrade Time: [0d:1h:32m:13s]
NOTE: If don’t pass the parameter for log location in dbupgrade command then below is the default location for the upgrade log.
$ORACLE_HOME/cfgtoollogs/<DB NAME>/upgrade/
3.2: Update Oratab: Update new ORACLE_HOME for 19c in the file /etc/oratab.
[oracle@DBsGuruN2 ~]$ vi /etc/oratab
labcdb05:/u01/app/oracle/product/1930/db_1:N
~
:wq
[oracle@DBsGuruN2 ~]$ cat /etc/oratab | grep -i labcdb05
labcdb05:/u01/app/oracle/product/1930/db_1:N
3.3: Start the Database: Set the environment and start the database.
[oracle@DBsGuruN2 ~]$ . oraenv
ORACLE_SID = [labdb01] ? labcdb05
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@DBsGuruN2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 18 11:36:42 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
@CDB$ROOT> STARTUP
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1660941720 bytes
Fixed Size 9135512 bytes
Variable Size 956301312 bytes
Database Buffers 687865856 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
@CDB$ROOT> SELECT NAME,OPEN_MODE,STATUS,VERSION FROM V$DATABASE, V$INSTANCE;
NAME OPEN_MODE STATUS VERSION
--------- -------------------- ------------ -----------------
LABCDB05 READ WRITE OPEN 19.0.0.0.0
@CDB$ROOT> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DBSGURU01_PDB READ WRITE NO
3.4: Execute utlrp.sql and validate Objects Status: Execute utlrp.sql and validate invalid objects in the database, should not be any invalid objects after the upgrade. After completion of the script must review logs and validate the object’s status in DB.
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -e -b post_utlrp -d '''.''' $ORACLE_HOME/rdbms/admin/utlrp.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/post_utlrp_catcon_4067.lst]
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/post_utlrp*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/post_utlrp_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt post_utlrp*
-rw-------. 1 oracle oinstall 462 Mar 4 10:38 post_utlrp_catcon_4067.lst
-rw-------. 1 oracle oinstall 55153 Mar 4 10:42 post_utlrp0.log
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade -n 1 -e -b post_invalid -- --x"SELECT OWNER, COUNT(*) FROM DBA_OBJECTS WHERE STATUS <> 'VALID' GROUP BY OWNER;"
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/post_invalid_catcon_5218.lst]
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/post_invalid*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/post_invalid_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt post_invalid*
-rw-------. 1 oracle oinstall 466 Mar 4 10:52 post_invalid_catcon_5218.lst
-rw-------. 1 oracle oinstall 5759 Mar 4 10:52 post_invalid0.log
3.5: Execute postupgrade_fixups.sql: Execute postupgrade_fixups.sql which was generated in step 1.1.
[oracle@DBsGuruN2 ~]$ $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /home/oracle/bkp/labcdb05_upgrade/ -n 1 -e -b post_fixup_labcdb05 /home/oracle/bkp/labcdb05_upgrade/postupgrade_fixups.sql
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/bkp/labcdb05_upgrade/post_fixup_labcdb05_catcon_5734.lst]
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/post_fixup_labcdb05*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/home/oracle/bkp/labcdb05_upgrade/post_fixup_labcdb05_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@DBsGuruN2 ~]$
[oracle@DBsGuruN2 labcdb05_upgrade]$ ls -lrt post_fixup*
-rw-------. 1 oracle oinstall 480 Mar 4 10:58 post_fixup_labcdb05_catcon_5734.lst
-rw-------. 1 oracle oinstall 13534 Mar 4 11:03 post_fixup_labcdb050.log
[oracle@DBsGuruN2 labcdb05_upgrade]$ cat post_fixup_labcdb050.log
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 4 10:58:35 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
SQL> Connected.
SQL> 2
Session altered.
SQL> 2
Session altered.
SQL>
ALTER SYSTEM KILL SESSION '140,30605' force timeout 0 -- process 5813
/
SQL> SQL> SQL> 2
Session altered.
SQL> SQL> 2
Session altered.
SQL> SQL>
SQL> SQL> 2
Session altered.
SQL> SQL>
NOW_CONNECTED_TO
--------------------------------------------------------------------------------
==== Current Container = CDB$ROOT Id = 1 ====
SQL>
NOW_CONNECTED_TO
--------------------------------------------------------------------------------
==== Current Container = CDB$ROOT Id = 1 ====
=========Trim Data========
SQL> SQL>
SQL> SQL> 2
Session altered.
SQL> SQL> ========== PROCESS ENDED ==========
SQL> ========== Process Terminated by catcon ==========
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@DBsGuruN2 labcdb05_upgrade]$
3.6: Upgrade Timezone: Require to upgrade DB timezone file version to 32. Prior to the upgrade, it was 26, refer to the post-upgrade tool log.
Execute utltz_upg_check.sql to validate the current RDBMS DST version and the newest RDBMS DST version.
@CDB$ROOT> @$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
Session altered.
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: This database is a Multitenant database.
INFO: Current container is CDB$ROOT .
INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database
INFO: will NOT update the RDBMS DST version of PDB databases in this CDB.
WARNING: There are 1 open PDBs .
WARNING: They will be closed when running utltz_upg_apply.sql .
INFO: Database RDBMS DST version is DSTv26 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Session altered.
Execute utltz_upg_apply.sql to upgrade DST version to 32.
@CDB$ROOT> @$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql
Session altered.
INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
INFO: This database is a Multitenant database.
INFO: Current container is CDB$ROOT .
INFO: Updating the RDBMS DST version of the CDB / CDB$ROOT database
INFO: will NOT update the RDBMS DST version of PDB databases in this CDB.
WARNING: There are 1 open PDBs .
WARNING: They will be closed when CDB$ROOT is restarted
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1660941720 bytes
Fixed Size 9135512 bytes
Variable Size 1342177280 bytes
Database Buffers 301989888 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1660941720 bytes
Fixed Size 9135512 bytes
Variable Size 1342177280 bytes
Database Buffers 301989888 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
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
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
Session altered.
@CDB$ROOT> COL PROPERTY_NAME FOR A25
@CDB$ROOT> COL PROPERTY_VALUE FOR A21
@CDB$ROOT> SELECT VERSION FROM V$TIMEZONE_FILE;
VERSION
----------
32
1 row selected.
3.7: Post upgrade validation tool: Execute utlusts.sql to validate upgrade status and review the log file.
@CDB$ROOT> @$ORACLE_HOME/rdbms/admin/utlusts.sql
Enter value for 1:
Oracle Database Release 19 Post-Upgrade Status Tool 03-04-2022 11:32:4
Container Database: LABCDB05
[CON_ID: 1 => CDB$ROOT]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.3.0.0.0 00:14:58
JServer JAVA Virtual Machine VALID 19.3.0.0.0 00:02:04
Oracle XDK VALID 19.3.0.0.0 00:00:35
Oracle Database Java Packages VALID 19.3.0.0.0 00:00:10
OLAP Analytic Workspace VALID 19.3.0.0.0 00:00:35
Oracle Label Security VALID 19.3.0.0.0 00:00:14
Oracle Database Vault VALID 19.3.0.0.0 00:00:28
Oracle Text VALID 19.3.0.0.0 00:00:49
Oracle Workspace Manager VALID 19.3.0.0.0 00:00:37
Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00
Oracle XML Database VALID 19.3.0.0.0 00:02:16
Oracle Multimedia VALID 19.3.0.0.0 00:00:55
Spatial VALID 19.3.0.0.0 00:05:35
Oracle OLAP API VALID 19.3.0.0.0 00:00:13
Datapatch 00:03:34
Final Actions 00:04:07
Post Upgrade 00:00:24
Post Compile 00:00:11
Total Upgrade Time: 00:35:41 [CON_ID: 1 => CDB$ROOT]
Database time zone version is 32. It meets current release needs.
3.8: Drop Guaranteed Restore Point: Drop restore point only after successful validation along with green signal by dependent applications/checkout.
@CDB$ROOT> COL NAME FOR A25
@CDB$ROOT> COL GUARANTEE_FLASHBACK_DATABASE FOR A31
@CDB$ROOT> SET LINES 333
@CDB$ROOT> SELECT NAME,GUARANTEE_FLASHBACK_DATABASE,TIME FROM V$RESTORE_POINT;
NAME GUARANTEE_FLASHBACK_DATABASE TIME
------------------------- ------------------------------- ---------------------------------------------------------------------------
PRE_UPGRADE_LABCDB05 YES 03-MAR-22 05.44.59.000000000 PM
@CDB$ROOT> DROP RESTORE POINT PRE_UPGRADE_LABCDB05;
Restore point dropped.
@CDB$ROOT> SELECT NAME,GUARANTEE_FLASHBACK_DATABASE,TIME FROM V$RESTORE_POINT;
no rows selected
3.9: Set the parameter Compatible: Change the COMPATIBLE parameter value to 19.0.0 to enable to use of all features of the upgraded version. This step is a very crucial step in terms of downgrading the database to the previous version or restoring guaranteed restore point. Make sure before the set a new value of COMPATIBLE parameter no major impact to DB in terms of performance, if possible test application for few days with an existing value of this parameter especially for the lower environment (DEV/TEST/UAT) upgrades so you have enough confidence to change it when you perform PROD databases upgrade.
@CDB$ROOT> SHOW PARAMETER COMPATIBLE PARAMETER;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
noncdb_compatible boolean FALSE
@CDB$ROOT> ALTER SYSTEM SET COMPATIBLE='19.0.0' SCOPE=SPFILE;
System altered.
@CDB$ROOT> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
@CDB$ROOT> STARTUP
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1660941720 bytes
Fixed Size 9135512 bytes
Variable Size 956301312 bytes
Database Buffers 687865856 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
@CDB$ROOT> SHOW PARAMETER COMPATIBLE PARAMETER;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
@CDB$ROOT>
@CDB$ROOT> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 DBSGURU01_PDB READ WRITE NO
3.10: RAC DB Action: Follow the below steps for the RAC database only.
3.10.1: Set cluster_database to TRUE.
@CDB$ROOT> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@DBsGuruN2 admin]$ srvctl start database -db <DB NAME>
3.10.2: Upgrade the Oracle Clusterware keys for the database.
[oracle@DBsGuruN2 admin]$ srvctl upgrade database -db <DB NAME> -o <ORACLE_HOME>
3.11: Listener Status: Validate the services for the database.
[oracle@DBsGuruN2 admin]$ lsnrctl status | grep -i 'labcdb\|dbsguru'
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DBsGuruN2.labdomain)(PORT=1521)))
Listener Log File /u01/app/oracle/diag/tnslsnr/DBsGuruN2/listener/alert/log.xml
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DBsGuruN2.labdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DBsGuruN2.labdomain)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/labcdb05/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Instance "labcdb05", status READY, has 1 handler(s) for this service...
Instance "labcdb05", status READY, has 1 handler(s) for this service...
Service "dbsguru01_pdb" has 1 instance(s).
Instance "labcdb05", status READY, has 1 handler(s) for this service...
Service "labcdb05" has 1 instance(s).
Instance "labcdb05", status READY, has 1 handler(s) for this service...
Service "labcdb05XDB" has 1 instance(s).
Instance "labcdb05", status READY, has 1 handler(s) for this service...
[oracle@DBsGuruN2 ~]$
3.12: Others:
3.12.1: Enable cronjobs/scheduler jobs/Triggers if any.
3.12.2: Delete blackout database in OEM. Click here to get steps for Target Blackouts in OEM 13c.
3.12.3: Start all dependent applications.
This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.
Related Articles
- Oracle Critical Database Patch ID for October 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for July 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for April 2024 along with enabled Download Link
- Step by Step Manual Upgrade Container (CDB-PDB) Database from 12c to 19c in Multitenant Architecture
- Steps to Upgrade Grid Infra – Standalone (GI) and Oracle Database from 12.2 to 19.14
