Sudipta Bhaskar
May 8, 202126 min
This post is about upgrading multitenant RAC Database from 12c to 19c.
I will attempt this in silent mode.
Following are the details of my current RAC.
Following are the main steps of upgrade.
Install Oracle 19.3 db home binary.
Executing preupgrade jar
Performing the pre upgrade actions
Backing up the database / Create a guaranteed restore point
Upgrade the database
Perform the post upgrade actions
Let's first create the folder structure /dboracle/app/oracle/product/19.0/dbhome_1 in both the servers.
mkdir -p /dboracle/app/oracle/product/19.0/dbhome_1
chown -R oracle:oinstall /dboracle/app/oracle/product/19.0/dbhome_1
I have downloaded the 19.3 db home binaries (LINUX.X64_193000_db_home.zip).
I have coped it to /dboracle/dbsoftware in first node and unzipped it.
Let's install the 19.3 db home binaries. I want to do this in silent mode with the help of a response file.
Apply the 19.8.0.0.200714 RU (Patch No 31305339)
[oracle@OEL7N1 dbsoftware]$ ./runInstaller -waitforcompletion -ignorePrereqFailure -applyRU /dboracle/software/31305339
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.
Preparing the home to patch...
Applying the patch /dboracle/software/31305339...
OPatch command failed while applying the patch. For details look at the logs from /dboracle/dbsoftware/cfgtoollogs/opatchauto/.
It failed because opatch version is not meeting the requirement.
Copied latest Opatch and applied patch again.
[oracle@OEL7N1 dbsoftware]$ ./runInstaller -waitforcompletion -ignorePrereqFailure -applyRU /dboracle/software/31305339
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.
Preparing the home to patch...
Applying the patch /dboracle/software/31305339...
Successfully applied the patch.
The log can be found at: /u01/app/oraInventory/logs/InstallActions2021-05-06_03-38-51PM/installerPatchActions_2021-05-06_03-38-51PM.log
No X11 DISPLAY variable was set, but this program performed an operation which requires it.
[oracle@OEL7N1 dbsoftware]$
Prerequisites :
[oracle@OEL7N1 dbsoftware]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba),54331(asmdba),54332(asmadmin)
[oracle@OEL7N1 dbsoftware]$ pwd
/dboracle/dbsoftware
[oracle@OEL7N1 dbsoftware]$ ./runInstaller -silent -executePrereqs -responseFile /home/oracle/db.rsp
Launching Oracle Database Setup Wizard...
Prerequisite checks executed successfully.
Installation :
[oracle@OEL7N1 dbsoftware]$ ./runInstaller -waitforcompletion -ignorePrereqFailure -silent -responseFile /home/oracle/db.rsp
Launching Oracle Database Setup Wizard...
[FATAL] [INS-35954] The installer has detected that the Oracle home location provided in the response file is not correct.
CAUSE: The Database Oracle home is the location from where the installer is executed. It has been detected that the value set in the response file is different (/dboracle/app/oracle/product/19.0/dbhome_1). Value expected is: /dboracle/dbsoftware
ACTION: It is not required to specify ORACLE_HOME in the response file for Database installation. Alternatively, set it to the location of the installer (/dboracle/dbsoftware).
Reason : We need to copy all the binaries to the oracle home location and then run runInstaller
[oracle@OEL7N1 dbhome_1]$ ./runInstaller -waitforcompletion -ignorePrereqFailure -silent -responseFile /home/oracle/db.rsp
Launching Oracle Database Setup Wizard...
[WARNING] [INS-13013] Target environment does not meet some mandatory requirements.
CAUSE: Some of the mandatory prerequisites are not met. See logs for details. /u01/app/oraInventory/logs/InstallActions2021-05-06_04-30-13PM/installActions2021-05-06_04-30-13PM.log
ACTION: Identify the list of failed prerequisite checks from the log: /u01/app/oraInventory/logs/InstallActions2021-05-06_04-30-13PM/installActions2021-05-06_04-30-13PM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
The response file for this session can be found at:
/dboracle/app/oracle/product/19.0/dbhome_1/install/response/db_2021-05-06_04-30-13PM.rsp
You can find the log of this install session at:
/u01/app/oraInventory/logs/InstallActions2021-05-06_04-30-13PM/installActions2021-05-06_04-30-13PM.log
As a root user, execute the following script(s):
1. /dboracle/app/oracle/product/19.0/dbhome_1/root.sh
Execute /dboracle/app/oracle/product/19.0/dbhome_1/root.sh on the following nodes:
[OEL7N1, OEL7N2]
Successfully Setup Software with warning(s).
Run the mentioned script in both the nodes. I am showing example of running in node1.
[root@OEL7N1 ~]# /dboracle/app/oracle/product/19.0/dbhome_1/root.sh
Check /dboracle/app/oracle/product/19.0/dbhome_1/install/root_OEL7N1.localdomain_2021-05-06_16-56-05-046986658.log for the output of root script
[root@OEL7N1 ~]# cat /dboracle/app/oracle/product/19.0/dbhome_1/install/root_OEL7N1.localdomain_2021-05-06_16-56-05-046986658.log
Performing root user operation.
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /dboracle/app/oracle/product/19.0/dbhome_1
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Let's go to the next part of Executing preupgrade jar.
preupgrade.jar file is located at $ORACLE_HOME/rdbms/admin
[oracle@OEL7N1 admin]$ pwd
/dboracle/app/oracle/product/19.0/dbhome_1/rdbms/admin
[oracle@OEL7N1 admin]$ ls -ltr preupgrade.jar
-rw-r--r-- 1 oracle oinstall 726130 May 6 16:28 preupgrade.jar
Let's run the preupgrade from 12c home.
[oracle@OEL7N1 admin]$ /dboracle/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /dboracle/app/oracle/product/19.0/dbhome_1/rdbms/admin/preupgrade.jar DIR /dboracle/upgrade
==================
PREUPGRADE SUMMARY
==================
/dboracle/upgrade/preupgrade.log
/dboracle/upgrade/preupgrade_fixups.sql
/dboracle/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 /dboracle/upgrade/ -b preup_orcl /dboracle/upgrade/preupgrade_fixups.sql
2. Review logs under /dboracle/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 /dboracle/upgrade/ -b postup_orcl /dboracle/upgrade/postupgrade_fixups.sql
2. Review logs under /dboracle/upgrade/
Preupgrade complete: 2021-05-06T17:36:58
Let's check the logs and take action.
[root@OEL7N1 upgrade]# cat preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-05-06T17:33:24
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL
Container Name: CDB$ROOT
Container ID: 1
Version: 12.2.0.1.0
DB Patch Level: DATABASE APR 2021 RELEASE UPDATE 12.2.0.1.210420
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: NOARCHIVELOG
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] VALID
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. Run 12.2.0.1.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
4 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
2. (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.
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 890 MB 892 MB
SYSTEM 830 MB 942 MB
UNDOTBS1 75 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 1 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 ORCL container CDB$ROOT
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/dboracle/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. Recreate directory objects to remove any symbolic links from directory
paths. To identify paths that contain symbolic links before upgrading,
use OS commands like UNIX file or WINDOWS dir. After upgrading, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify directory objects
with symbolic links in the path.
Found 2 user directory objects to be checked: DBMS_OPTIM_ADMINDIR,
DBMS_OPTIM_LOGDIR.
Starting in Release 18c, symbolic links are not allowed in directory
object paths 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 ORCL container CDB$ROOT
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/dboracle/upgrade/postupgrade_fixups.sql
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-05-06T17:34:55
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL
Container Name: PDB$SEED
Container ID: 2
Version: 12.2.0.1.0
DB Patch Level: DATABASE APR 2021 RELEASE UPDATE 12.2.0.1.210420
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: NOARCHIVELOG
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] VALID
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 260 MB 371 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 ORCL container PDB$SEED
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/dboracle/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. Recreate directory objects to remove any symbolic links from directory
paths. To identify paths that contain symbolic links before upgrading,
use OS commands like UNIX file or WINDOWS dir. After upgrading, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify directory objects
with symbolic links in the path.
Found 2 user directory objects to be checked: DBMS_OPTIM_ADMINDIR,
DBMS_OPTIM_LOGDIR.
Starting in Release 18c, symbolic links are not allowed in directory
object paths 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 ORCL container PDB$SEED
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/dboracle/upgrade/postupgrade_fixups.sql
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-05-06T17:36:29
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL
Container Name: PDBORCL
Container ID: 3
Version: 12.2.0.1.0
DB Patch Level: DATABASE APR 2021 RELEASE UPDATE 12.2.0.1.210420
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: NOARCHIVELOG
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] VALID
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.
INFORMATION ONLY
================
2. 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 370 MB 500 MB
SYSTEM 270 MB 380 MB
TEMP 132 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 ORCL container PDBORCL
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/dboracle/upgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
3. 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.
4. Recreate directory objects to remove any symbolic links from directory
paths. To identify paths that contain symbolic links before upgrading,
use OS commands like UNIX file or WINDOWS dir. After upgrading, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql to identify directory objects
with symbolic links in the path.
Found 2 user directory objects to be checked: DBMS_OPTIM_ADMINDIR,
DBMS_OPTIM_LOGDIR.
Starting in Release 18c, symbolic links are not allowed in directory
object paths used with BFILE data types, the UTL_FILE package, or
external tables.
5. (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.
6. 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 ORCL container PDBORCL
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following from within the container
SQL>@/dboracle/upgrade/postupgrade_fixups.sql
I have run utlrp to compile the invalid objects and the following script.
[oracle@OEL7N1 admin]$ $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /dboracle/upgrade/ -b preup_orcl /dboracle/upgrade/preupgrade_fixups.sql
catcon: ALL catcon-related output will be written to [/dboracle/upgrade//preup_orcl_catcon_20480.lst]
catcon: See [/dboracle/upgrade//preup_orcl*.log] files for output generated by scripts
catcon: See [/dboracle/upgrade//preup_orcl_*.lst] files for spool files, if any
catcon.pl: completed successfully
Let's go the next step.of backing up the database and creating a guranteed restore point.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 20
Next log sequence to archive 21
Current log sequence 21
SQL> create restore point before_upgrade_19c guarantee flashback database;
Restore point created.
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
NAME GUA TIME
---------------------- --- -----------------------------------
BEFORE_UPGRADE_19C YES 07-MAY-21 06.44.00.000000000 AM
Some more checks before upgrade
Check the timezone version.
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
26
Disable scheduler jobs.
set pagesize 2000
set lines 2000
set long 99999
select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;
OWNER JOB_NAME ENABL STATE
-------------------- ------------------------------ ----- ---------------
SYS PURGE_LOG TRUE SCHEDULED
SYS FILE_WATCHER FALSE DISABLED
SYS PMO_DEFERRED_GIDX_MAINT_JOB TRUE SCHEDULED
SYS CLEANUP_NON_EXIST_OBJ TRUE SCHEDULED
SYS CLEANUP_ONLINE_IND_BUILD TRUE SCHEDULED
SYS CLEANUP_TAB_IOT_PMO TRUE SCHEDULED
SYS FILE_SIZE_UPD TRUE SCHEDULED
SYS ORA$AUTOTASK_CLEAN TRUE SCHEDULED
SYS HM_CREATE_OFFLINE_DICTIONARY FALSE DISABLED
SYS DRA_REEVALUATE_OPEN_FAILURES TRUE SCHEDULED
SYS ORA$PREPLUGIN_BACKUP_JOB FALSE DISABLED
SYS BSLN_MAINTAIN_STATS_JOB TRUE SCHEDULED
SYS FGR$AUTOPURGE_JOB FALSE DISABLED
SYS RSE$CLEAN_RECOVERABLE_SCRIPT TRUE SCHEDULED
SYS SM$CLEAN_AUTO_SPLIT_MERGE TRUE SCHEDULED
SYS LOAD_OPATCH_INVENTORY FALSE DISABLED
SYS XMLDB_NFS_CLEANUP_JOB FALSE DISABLED
SYS CLEANUP_TRANSIENT_TYPE TRUE SCHEDULED
SYS CLEANUP_TRANSIENT_PKG TRUE SCHEDULED
SYS CLEANUP_ONLINE_PMO TRUE SCHEDULED
SYS LOAD_OPATCH_INVENTORY_2 FALSE DISABLED
SYS LOAD_OPATCH_INVENTORY_1 FALSE DISABLED
ORACLE_OCM MGMT_CONFIG_JOB TRUE SCHEDULED
ORACLE_OCM MGMT_STATS_CONFIG_JOB TRUE SCHEDULED
24 rows selected.
Verify system and sys default tablespace.(Both should be system tablespace)
SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM'); 2 3
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS SYSTEM
SYSTEM SYSTEM
Check whether there are any externally authenticated SSL users
SQL> SELECT name FROM sys.user$
2 WHERE ext_username IS NOT NULL
AND password = 'GLOBAL'; 3
no rows selected
Review and Remove any unnecessary hidden parameters.
SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;
no rows selected
Upgrade Process :
Stop database from 12.2 oracle home and remove the database from oracle restart.
[oracle@OEL7N1 ~]$ srvctl config
orcl
[oracle@OEL7N1 ~]$ srvctl status database -d orcl -v
Instance orcl1 is running on node oel7n1 with online services pdborcl_srv. Instance status: Open.
Instance orcl2 is running on node oel7n2 with online services pdborcl_srv. Instance status: Open.
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl stop database -d orcl
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl status database -d orcl -v
Instance orcl1 is not running on node oel7n1
Instance orcl2 is not running on node oel7n2
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl remove database -d orcl -f
Let's set 19c environment.
[oracle@OEL7N1 ~]$ echo $ORACLE_HOME
/dboracle/app/oracle/product/19.0/dbhome_1
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ echo $PATH
/dboracle/app/oracle/product/19.0/dbhome_1:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/dboracle/app/oracle/product/12.2.0/dbhome_1/bin
Need to copy spfile and pwd file in dbs location of 19c home.
[oracle@OEL7N1 ~]$ . oraenv
ORACLE_SID = [orcl1] ? +ASM1
The Oracle base has been set to /u01/app/grid
[oracle@OEL7N1 ~]$ asmcmd cp +DATA/ORCL/PARAMETERFILE/spfile.272.1070782447 /dboracle/app/oracle/product/19.0/dbhome_1/dbs/spfileorcl1.ora
copying +DATA/ORCL/PARAMETERFILE/spfile.272.1070782447 -> /dboracle/app/oracle/product/19.0/dbhome_1/dbs/spfileorcl1.ora
[oracle@OEL7N1 ~]$ asmcmd cp +DATA/ORCL/PASSWORD/pwdorcl.256.1070782021 /dboracle/app/oracle/product/19.0/dbhome_1/dbs/orapworcl1
copying +DATA/ORCL/PASSWORD/pwdorcl.256.1070782021 -> /dboracle/app/oracle/product/19.0/dbhome_1/dbs/orapworcl1
[oracle@OEL7N1 ~]$ export ORACLE_SID=orcl1
[oracle@OEL7N1 ~]$ export ORACLE_HOME=/dboracle/app/oracle/product/19.0/dbhome_1
[oracle@OEL7N1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@OEL7N1 ~]$ which sqlplus
/dboracle/app/oracle/product/19.0/dbhome_1/bin/sqlplus
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 7 13:13:27 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1577055360 bytes
Fixed Size 9135232 bytes
Variable Size 553648128 bytes
Database Buffers 1006632960 bytes
Redo Buffers 7639040 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-39701: database must be mounted EXCLUSIVE for UPGRADE or DOWNGRADE
Process ID: 32539
Session ID: 176 Serial number: 31358
SQL> alter system set cluster_database=false scope=spfile sid='*';
ERROR:
ORA-03114: not connected to ORACLE
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
sql
[oracle@OEL7N1 ~]$ sql
SQLcl: Release 19.1 Production on Fri May 07 13:23:18 2021
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Username? (''?) / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1577055360 bytes
Fixed Size 9135232 bytes
Variable Size 553648128 bytes
Database Buffers 1006632960 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> alter system set cluster_database=false scope=spfile sid='*';
System altered.
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
[oracle@OEL7N1 ~]$ echo $ORACLE_HOME
/dboracle/app/oracle/product/19.0/dbhome_1
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 7 13:25:30 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1577055360 bytes
Fixed Size 9135232 bytes
Variable Size 553648128 bytes
Database Buffers 1006632960 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL>
SQL> alter pluggable database all open upgrade;
Pluggable database altered.
SQL> set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,log_mode,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,v$instance;SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE LOG_MODE DB_VERSION LOGINS DB UP TIME
--------- ------------------------------ ---------------- ---------- ------------ ----------------- ---------- -----------------------------
ORCL OEL7N1.localdomain PRIMARY READ WRITE ARCHIVELOG 19.0.0.0.0 RESTRICTED 07-MAY-2021 13:25:46
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MIGRATE YES
3 PDBORCL MIGRATE YES
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE CDB VERSION STATUS
--------- ---------- --- ----------------- ------------
ORCL READ WRITE YES 19.0.0.0.0 OPEN MIGRATE
Now, Actual Upgrade.
[oracle@OEL7N1 bin]$ pwd
/dboracle/app/oracle/product/19.0/dbhome_1/bin
[oracle@OEL7N1 bin]$ ./dbupgrade
=== Took a long time. Pasting last few lines of log
catcon::sureunlink: verify that the file really no longer exists
catcon::sureunlink: confirmed that /dboracle/app/oracle/product/19.0/dbhome_1/cfgtoollogs/orcl/upgrade20210507133748/catupgrdpdborcl_catcon_kill_sess_16618_ALL.sql no longer exists after 1 attempts
catcon::catconWrapUp: done
Time: 7355s For CDB$ROOT
Time: 14641s For PDB(s)
Grand Total Time: 21996s
LOG FILES: (/dboracle/app/oracle/product/19.0/dbhome_1/cfgtoollogs/orcl/upgrade20210507133748/catupgrdcdbroot*.log)
Upgrade Summary Report Located in:
/dboracle/app/oracle/product/19.0/dbhome_1/cfgtoollogs/orcl/upgrade20210507133748/upg_summary.log
Grand Total Upgrade Time: [0d:6h:6m:36s]
Let's check the upgrade summary
[oracle@OEL7N1 upgrade20210507133748]$ cat upg_summary.log
Oracle Database Release 19 Post-Upgrade Status Tool 05-07-2021 15:38:3
Container Database: ORCL
[CON_ID: 1 => CDB$ROOT]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.8.0.0.0 00:41:45
JServer JAVA Virtual Machine UPGRADED 19.8.0.0.0 00:06:03
Oracle XDK UPGRADED 19.8.0.0.0 00:02:25
Oracle Database Java Packages UPGRADED 19.8.0.0.0 00:00:29
OLAP Analytic Workspace UPGRADED 19.8.0.0.0 00:00:54
Oracle Label Security UPGRADED 19.8.0.0.0 00:00:34
Oracle Database Vault UPGRADED 19.8.0.0.0 00:01:43
Oracle Text UPGRADED 19.8.0.0.0 00:02:51
Oracle Workspace Manager UPGRADED 19.8.0.0.0 00:03:54
Oracle Real Application Clusters UPGRADED 19.8.0.0.0 00:00:02
Oracle XML Database UPGRADED 19.8.0.0.0 00:05:30
Oracle Multimedia UPGRADED 19.8.0.0.0 00:03:09
Spatial LOADING 19.8.0.0.0 00:19:03
Oracle OLAP API INVALID 19.8.0.0.0 00:00:36
Datapatch 00:25:59
Final Actions 00:26:26
Post Upgrade 00:00:38
Total Upgrade Time: 01:57:48 [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 05-07-2021 17:55:5
Container Database: ORCL
[CON_ID: 2 => PDB$SEED]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.8.0.0.0 01:00:24
JServer JAVA Virtual Machine VALID 19.8.0.0.0 00:03:58
Oracle XDK VALID 19.8.0.0.0 00:02:04
Oracle Database Java Packages VALID 19.8.0.0.0 00:00:25
OLAP Analytic Workspace VALID 19.8.0.0.0 00:01:03
Oracle Label Security VALID 19.8.0.0.0 00:00:18
Oracle Database Vault VALID 19.8.0.0.0 00:04:50
Oracle Text VALID 19.8.0.0.0 00:01:01
Oracle Workspace Manager VALID 19.8.0.0.0 00:01:06
Oracle Real Application Clusters VALID 19.8.0.0.0 00:00:00
Oracle XML Database VALID 19.8.0.0.0 00:04:27
Oracle Multimedia VALID 19.8.0.0.0 00:01:19
Spatial VALID 19.8.0.0.0 00:13:23
Oracle OLAP API VALID 19.8.0.0.0 00:00:34
Datapatch 00:22:18
Final Actions 00:22:41
Post Upgrade 00:02:15
Post Compile 00:11:36
Total Upgrade Time: 02:12:56 [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.
Oracle Database Release 19 Post-Upgrade Status Tool 05-07-2021 19:44:1
Container Database: ORCL
[CON_ID: 3 => PDBORCL]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.8.0.0.0 00:43:31
JServer JAVA Virtual Machine UPGRADED 19.8.0.0.0 00:04:51
Oracle XDK UPGRADED 19.8.0.0.0 00:03:41
Oracle Database Java Packages UPGRADED 19.8.0.0.0 00:00:36
OLAP Analytic Workspace UPGRADED 19.8.0.0.0 00:01:02
Oracle Label Security UPGRADED 19.8.0.0.0 00:00:30
Oracle Database Vault UPGRADED 19.8.0.0.0 00:05:33
Oracle Text UPGRADED 19.8.0.0.0 00:00:39
Oracle Workspace Manager UPGRADED 19.8.0.0.0 00:00:57
Oracle Real Application Clusters UPGRADED 19.8.0.0.0 00:00:02
Oracle XML Database UPGRADED 19.8.0.0.0 00:04:35
Oracle Multimedia UPGRADED 19.8.0.0.0 00:01:17
Spatial LOADING 19.8.0.0.0 00:14:41
Oracle OLAP API VALID 19.8.0.0.0 00:00:45
Datapatch 00:18:56
Final Actions 00:19:19
Post Upgrade 00:02:22
Total Upgrade Time: 01:45:50 [CON_ID: 3 => PDBORCL]
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: 02:12:56 [CON_ID: 2 => PDB$SEED * ]
Total Upgrade Time: 01:57:48 [CON_ID: 1 => CDB$ROOT]
Total Upgrade Time: 01:45:50 [CON_ID: 3 => PDBORCL]
Grand Total Upgrade Time: [0d:6h:6m:36s]
[oracle@OEL7N1 upgrade20210507133748]$
Let's check the version and component state.
SQL> select BANNER from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> col COMP_ID for a10
SQL> col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;SQL> SQL> SQL> SQL>
COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 UPGRADED
CATPROC Oracle Database Packages and Types 19.0.0.0.0 UPGRADED
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 UPGRADED
XML Oracle XDK 19.0.0.0.0 UPGRADED
CATJAVA Oracle Database Java Packages 19.0.0.0.0 UPGRADED
APS OLAP Analytic Workspace 19.0.0.0.0 UPGRADED
RAC Oracle Real Application Clusters 19.0.0.0.0 UPGRADED
XDB Oracle XML Database 19.0.0.0.0 UPGRADED
OWM Oracle Workspace Manager 19.0.0.0.0 UPGRADED
CONTEXT Oracle Text 19.0.0.0.0 UPGRADED
ORDIM Oracle Multimedia 19.0.0.0.0 UPGRADED
SDO Spatial 19.0.0.0.0 LOADING
XOQ Oracle OLAP API 19.0.0.0.0 INVALID
OLS Oracle Label Security 19.0.0.0.0 UPGRADED
DV Oracle Database Vault 19.0.0.0.0 UPGRADED
15 rows selected.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
SQL> set serveroutput on
SQL> exec sys.VALIDATE_SDO();
PL/SQL procedure successfully completed.
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry where COMP_NAME='Spatial';
COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
SDO Spatial 19.0.0.0.0 VALID
Run the following from both cdb and pdb
@$ORACLE_HOME/rdbms/admin/catuppst.sql
Let's run the post upgrade script and utlrp.
$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /dboracle/upgrade/ -b postup_orcl /dboracle/upgrade/postupgrade_fixups.sql
@?/rdbms/admin/utlrp
After running utlrp, pdb components are fine.
COMP_ID COMP_NAME VERSION STATUS
------------------------------ ---------------------------------------- --------------- -----------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 19.0.0.0.0 VALID
XDB Oracle XML Database 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
SDO Spatial 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
OLS Oracle Label Security 19.0.0.0.0 VALID
DV Oracle Database Vault 19.0.0.0.0 VALID
15 rows selected.
SQL> show con_name
CON_NAME
------------------------------
PDBORCL
All components are fine in cdb also.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
------------------------------ ---------------------------------------- --------------- --------------------------------------------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Types 19.0.0.0.0 VALID
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 19.0.0.0.0 VALID
XDB Oracle XML Database 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
SDO Spatial 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
OLS Oracle Label Security 19.0.0.0.0 VALID
DV Oracle Database Vault 19.0.0.0.0 VALID
15 rows selected.
All components are in valid state in pdb seed also.
Let's fix the timezone now.
Download DST for timezone upgrade scripts from Doc ID 1585343.1
sudip@DESKTOP-RDIUEUO MINGW64 /c/Softwares
$ scp DBMS_DST_scriptsV1.9.zip oracle@192.168.126.11:/dboracle/software
oracle@192.168.126.11's password:
DBMS_DST_scriptsV1.9.zip 100% 16KB 1.0MB/s 00:00
[oracle@OEL7N1 software]$ cd timezone
[oracle@OEL7N1 timezone]$ ls -ltr
total 16
-rw-r--r-- 1 oracle oinstall 16095 May 8 05:09 DBMS_DST_scriptsV1.9.zip
[oracle@OEL7N1 timezone]$ unzip *
Archive: DBMS_DST_scriptsV1.9.zip
creating: DBMS_DST_scriptsV1.9/
inflating: DBMS_DST_scriptsV1.9/countstarTSTZ.sql
inflating: DBMS_DST_scriptsV1.9/countstatsTSTZ.sql
inflating: DBMS_DST_scriptsV1.9/upg_tzv_apply.sql
inflating: DBMS_DST_scriptsV1.9/upg_tzv_check.sql
[oracle@OEL7N1 timezone]$ ls -ltr
total 16
drwxr-xr-x 2 oracle oinstall 107 Mar 14 2018 DBMS_DST_scriptsV1.9
-rw-r--r-- 1 oracle oinstall 16095 May 8 05:09 DBMS_DST_scriptsV1.9.zip
[oracle@OEL7N1 timezone]$ cd DBMS_DST_scriptsV1.9/
[oracle@OEL7N1 DBMS_DST_scriptsV1.9]$ ls -ltr
total 68
-rw-r--r-- 1 oracle oinstall 31010 Aug 22 2014 upg_tzv_check.sql
-rw-r--r-- 1 oracle oinstall 19502 Aug 22 2014 upg_tzv_apply.sql
-rw-r--r-- 1 oracle oinstall 6294 Jan 8 2015 countstarTSTZ.sql
-rw-r--r-- 1 oracle oinstall 7213 Mar 17 2018 countstatsTSTZ.sql
Let's run it.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> @countstatsTSTZ.sql
.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
For SYS tables first...
Note: empty tables are not listed.
Stat date - Owner.Tablename.Columnname - num_rows
02/05/2021 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 4
02/05/2021 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 4
02/05/2021 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 4
02/05/2021 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3
02/05/2021 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3
02/05/2021 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3
02/05/2021 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1
02/05/2021 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1
02/05/2021 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1
02/05/2021 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME - 1
02/05/2021 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME - 1
02/05/2021 - SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME - 1
07/05/2021 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME - 1
07/05/2021 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME - 1
07/05/2021 - SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME - 1
02/05/2021 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 3
02/05/2021 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 3
02/05/2021 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 3
02/05/2021 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1
02/05/2021 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1
02/05/2021 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1
02/05/2021 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1
02/05/2021 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1
02/05/2021 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1
07/05/2021 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1
07/05/2021 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1
07/05/2021 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1
02/05/2021 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME - 4
02/05/2021 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME - 4
02/05/2021 - SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME - 4
07/05/2021 - SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ - 2
08/05/2021 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1
08/05/2021 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1
08/05/2021 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1
08/05/2021 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1
08/05/2021 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1
07/05/2021 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7
07/05/2021 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7
08/05/2021 - SYS.KET$_CLIENT_TASKS.CURR_WIN_START - 3
08/05/2021 - SYS.KET$_CLIENT_TASKS.LG_DATE - 3
08/05/2021 - SYS.KET$_CLIENT_TASKS.LT_DATE - 3
08/05/2021 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 45
08/05/2021 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 45
07/05/2021 - SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP - 23674
07/05/2021 - SYS.OPTSTAT_USER_PREFS$.CHGTIME - 72
02/05/2021 - SYS.RADM_FPTM$.TSWTZ_COL - 1
02/05/2021 - SYS.REG$.NTFN_GROUPING_START_TIME - 2
02/05/2021 - SYS.REG$.REG_TIME - 2
07/05/2021 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 167
08/05/2021 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11
08/05/2021 - SYS.SCHEDULER$_JOB.END_DATE - 24
08/05/2021 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 24
08/05/2021 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 24
08/05/2021 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 24
08/05/2021 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 24
08/05/2021 - SYS.SCHEDULER$_JOB.START_DATE - 24
07/05/2021 - SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 82
07/05/2021 - SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 82
07/05/2021 - SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 82
02/05/2021 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 4
02/05/2021 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 4
08/05/2021 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9
08/05/2021 - SYS.SCHEDULER$_WINDOW.END_DATE - 9
08/05/2021 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9
08/05/2021 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9
08/05/2021 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9
08/05/2021 - SYS.SCHEDULER$_WINDOW.START_DATE - 9
08/05/2021 - SYS.SCHEDULER$_WINDOW_DETAILS.LOG_DATE - 4
08/05/2021 - SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE - 4
08/05/2021 - SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE - 4
08/05/2021 - SYS.STATS_TARGET$.END_TIME - 1056
08/05/2021 - SYS.STATS_TARGET$.START_TIME - 1056
02/05/2021 - SYS.TAB_STATS$.SPARE6 - 1137
07/05/2021 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 176
07/05/2021 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 176
07/05/2021 - SYS.WRI$_ALERT_OUTSTANDING.CREATION_TIME - 1
07/05/2021 - SYS.WRI$_ALERT_OUTSTANDING.TIME_SUGGESTED - 1
08/05/2021 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SAVTIME - 143392
08/05/2021 - SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SPARE6 - 143392
07/05/2021 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME - 52520
07/05/2021 - SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 - 52520
08/05/2021 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 2874
08/05/2021 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 2874
08/05/2021 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 351
08/05/2021 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 351
08/05/2021 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 351
08/05/2021 - SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME - 7369
08/05/2021 - SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 - 7369
08/05/2021 - SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME - 7369
08/05/2021 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 3890
08/05/2021 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 3890
08/05/2021 - SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ - 21
07/05/2021 - SYS.WRM$_PDB_INSTANCE.OPEN_TIME_TZ - 40
07/05/2021 - SYS.WRM$_PDB_INSTANCE.STARTUP_TIME_TZ - 40
07/05/2021 - SYS.WRM$_PDB_IN_SNAP.OPEN_TIME_TZ - 157
08/05/2021 - SYS.WRM$_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ - 111
08/05/2021 - SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ - 111
26/01/2017 - SYS.XS$PRIN.END_DATE - 15
26/01/2017 - SYS.XS$PRIN.START_DATE - 15
Total numrow of SYS TSTZ columns is : 457198
There are in total 162 non-SYS TSTZ columns.
.
For non-SYS tables ...
Note: empty tables are not listed.
Stat date - Owner.Tablename.Columnname - num_rows
07/05/2021 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME - 1
07/05/2021 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME - 1
07/05/2021 - GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME - 1
02/05/2021 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME - 1
02/05/2021 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME - 1
02/05/2021 - WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME - 1
07/05/2021 - WMSYS.WM$WORKSPACES_TABLE$.CREATETIME - 1
07/05/2021 - WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE - 1
Total numrow of non-SYS TSTZ columns is : 8
There are in total 20 non-SYS TSTZ columns.
Total Minutes elapsed : 0
SQL>
SQL> exec dbms_scheduler.purge_log;
PL/SQL procedure successfully completed.
SQL> @upg_tzv_check.sql
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: 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 upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL> @upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
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.
ORACLE instance started.
Total System Global Area 1577055360 bytes
Fixed Size 9135232 bytes
Variable Size 654311424 bytes
Database Buffers 905969664 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.
ORACLE instance started.
Total System Global Area 1577055360 bytes
Fixed Size 9135232 bytes
Variable Size 654311424 bytes
Database Buffers 905969664 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 sqlplus session.
INFO: Do not use it for timezone related selects.
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
##########
1 row selected.
SQL> col version for 99999
SQL> SELECT version FROM v$timezone_file;
VERSION
-------
32
1 row selected.
SQL>
SQL> @?/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool 05-08-2021 06:24:0
Container Database: ORCL
[CON_ID: 1 => CDB$ROOT]
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.8.0.0.0 00:41:45
JServer JAVA Virtual Machine VALID 19.8.0.0.0 00:06:03
Oracle XDK VALID 19.8.0.0.0 00:02:25
Oracle Database Java Packages VALID 19.8.0.0.0 00:00:29
OLAP Analytic Workspace VALID 19.8.0.0.0 00:00:54
Oracle Label Security VALID 19.8.0.0.0 00:00:34
Oracle Database Vault VALID 19.8.0.0.0 00:01:43
Oracle Text VALID 19.8.0.0.0 00:02:51
Oracle Workspace Manager VALID 19.8.0.0.0 00:03:54
Oracle Real Application Clusters VALID 19.8.0.0.0 00:00:02
Oracle XML Database VALID 19.8.0.0.0 00:05:30
Oracle Multimedia VALID 19.8.0.0.0 00:03:09
Spatial VALID 19.8.0.0.0 00:19:03
Oracle OLAP API VALID 19.8.0.0.0 00:00:36
Datapatch 00:25:59
Final Actions 00:26:26
Post Upgrade 00:00:00
Post Compile 00:00:18
Total Upgrade Time: 01:57:29 [CON_ID: 1 => CDB$ROOT]
Database time zone version is 32. It meets current release needs.
Let's start both the instances and add the database in oracle restart.
[oracle@OEL7N1 ~]$ srvctl add database -d orcl -oraclehome /dboracle/app/oracle/product/19.0/dbhome_1 -dbtype RAC -spfile +DATA/ORCL/PARAMETERFILE/spfile.307.1071991167 -pwfile +DATA/ORCL/PASSWORD/pwdorcl.256.1070782021
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl status database -d orcl
Database is not running.
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl add instance -i orcl1 -n OEL7N1 -d orcl
[oracle@OEL7N1 ~]$ srvctl add instance -i orcl2 -n OEL7N2 -d orcl
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl status database -d orcl
Instance orcl1 is not running on node oel7n1
Instance orcl2 is not running on node oel7n2
[oracle@OEL7N1 ~]$ srvctl status database -d orcl
Instance orcl1 is not running on node oel7n1
Instance orcl2 is not running on node oel7n2
[oracle@OEL7N1 ~]$ srvctl start database -d orcl
PRCR-1079 : Failed to start resource ora.orcl.db
CRS-5017: The resource action "ora.orcl.db start" encountered the following error:
ORA-01102: cannot mount database in EXCLUSIVE mode
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/oel7n1/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.orcl.db' on 'oel7n1' failed
CRS-2632: There are no more servers to try to place resource 'ora.orcl.db' on that would satisfy its placement policy
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl stop database -d orcl
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl remove database -d orcl
Remove the database orcl? (y/[n]) y
Note : Started database using old spfile in dbs location which we used for upgrade. Modified the cluster_database parameter to true.
[oracle@OEL7N1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat May 8 07:17:03 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1577055360 bytes
Fixed Size 9135232 bytes
Variable Size 654311424 bytes
Database Buffers 905969664 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show parameter cluster_database
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
SQL>
SQL> create spfile='+DATA' from pfile;
File created.
SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl add database -d orcl -oraclehome /dboracle/app/oracle/product/19.0/dbhome_1 -dbtype RAC -spfile +DATA/ORCL/PARAMETERFILE/spfile.307.1071991167 -pwfile +DATA/ORCL/PASSWORD/pwdorcl.256.1070782021
[oracle@OEL7N1 ~]$ srvctl add instance -i orcl1 -n OEL7N1 -d orcl
[oracle@OEL7N1 ~]$ srvctl add instance -i orcl2 -n OEL7N2 -d orcl
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl status database -d orcl
Instance orcl1 is not running on node oel7n1
Instance orcl2 is not running on node oel7n2
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl start database -d orcl
[oracle@OEL7N1 ~]$
[oracle@OEL7N1 ~]$ srvctl status database -d orcl
Instance orcl1 is running on node oel7n1
Instance orcl2 is running on node oel7n2
We need to enable the disabled scheduler jobs.
INST_ID INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION_FULL STARTUP_T STATUS LOGINS
-------- --------------- ---------------- ------------------------------ ----------------- --------- ------------ ---------
2 2 orcl2 OEL7N2.localdomain 19.8.0.0.0 08-MAY-21 OPEN ALLOWED
1 1 orcl1 OEL7N1.localdomain 19.8.0.0.0 08-MAY-21 OPEN ALLOWED
Last step : Add a service for pdb.
[oracle@OEL7N2 ~]$ srvctl add service -d orcl -s pdborcl_srv -preferred orcl1,orcl2 -tafpolicy BASIC -clbgoal SHORT -pdb PDBORCL
[oracle@OEL7N2 ~]$ srvctl start service -d orcl -s pdborcl_srv
[oracle@OEL7N2 ~]$ srvctl status service -d orcl
Service pdborcl_srv is running on instance(s) orcl1,orcl2
[oracle@OEL7N2 ~]$ srvctl status database -d orcl
Instance orcl1 is running on node oel7n1
Instance orcl2 is running on node oel7n2
[oracle@OEL7N2 ~]$ srvctl status database -d orcl -v
Instance orcl1 is running on node oel7n1 with online services pdborcl_srv. Instance status: Open.
Instance orcl2 is running on node oel7n2 with online services pdborcl_srv. Instance status: Open.
Upgrade is now complete.
Long post. Hope it is of some help in your DBA Journey.