Skip to content

Oracle 19c – Complete checklist for Manual Upgrade for upgrading Oracle 12.x, 18c Container database (CDB) to Oracle 19c (19.x)

PURPOSE

The purpose of this article is to perform upgrade of 12c, 18c container databases (CDB with one or more pluggable databases) using Manual upgrade method to Oracle 19c release.

SCOPE

DBA, Support

DETAILS

About Manual Upgrade

Upgrade Path / Compatibility Matrix for 19c Oracle Container Database.

Minimum version of the database that can be directly upgraded to Oracle 19c.

Source Database Target Database
11.2.0.4 19c
12.1.0.2 19c
12.2.0.1 19c
18.1 19c

 

Manual Upgrade can be used to upgrade Oracle 12.1.0.2 or higher container (CDB) databases to 19c release.

Source Database Target Database
12.1.0.2 or higher 19c

 

Step 1: Requirements and Recommendations for source CDB / PDB database.

  • Ensure that all database components/objects provided by Oracle are VALID in the source container database and all the pluggable databases prior to starting the upgrade.

Doc Id 556610.1 – Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

dbupgdiag.sql script is a set of sql statements intended to provide a user friendly output to diagnose the status of the database either before (or) after upgrade.

The script will create a output file called db_upg_diag_<sid>_<timestamp>.log

  • Ensure that you do not have duplicate objects in the SYS and SYSTEM schema.
  • Before you start an upgrade process, Oracle strongly recommends to apply the latest PSU or Proactive Bundle Patch / RU or RUR Patch. Refer Doc Id – 2118136.2 to download the patches.
  • Make sure to have a valid backup of source database prior to upgrade.
  • Disable any custom triggers that would get executed before / after DDL statements. Re-enable them after the upgrade.
  • Check the database server upgrade/downgrade compatibility matrix before upgrading the database.
  • For Oracle RAC, if you upgrade a cluster database using manual method, then you must set CLUSTER_DATABASE to FALSE before performing the upgrade and set it back to TRUE after the upgrade.
  • Execute the preupgrade steps ( preupgrade.jar ), it is recommended to execute pre-upgrade utility and follow the recommendation like gather stats, purge recylebin etc.
  • Examine and follow the recommendation given in the preupgrade log file.
  • Materialized views in source database should be stopped before upgrade.

Doc ID 1406586.1 – How to Handle Materialized Views When You Upgrade or Clone a Database

  • Disable scheduled database custom jobs / cron jobs.

 

Step 2: Requirements and Recommendations for Target database

  • Verify the certification of Oracle 19c with your Platform/Operating system before downloading and installing Oracle 19c. Please check the certification information on My Oracle Support.
  • Ensure that newly installed Oracle Home doesn’t have relinking errors.
  • Install the latest available Proactive Bundle Patch or RU from Metalink. The patches can be downloaded from below document:

Doc ID 2118136.2 – Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases

  • Review patch recommendations as given in the below article :

Doc ID 2539751.1 – Patches to apply before upgrading Oracle GI and DB to 19c

 

Step 3 Preupgrade

Step 3.1 Preupgrade script execution

$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]

FILE|TERMINAL – Use this option to direct script output to a file. Use TERMINAL to direct output to the terminal. If it is not specified then default is FILE.

TEXT – Use this option to specify log should be in Text format. Use TEXT to specify text output. Use XML to specify XML output. If you do not specify an output type, then the default is text.

DIR – Logs will be created under <output_dir>. Directs the output to a specific directory. If you do not specify an output directory with the DIR option, then the output is directed to one of the default locations: If you define ORACLE_BASE environment variable then the generated scripts and log files will be created under $ORACLE_BASE/cfgtoollogs/<dbname>/preupgrade/ location else it will create under $ORACLE_HOME/cfgtoollogs/db_name/preupgrade/.

please make sure all the pluggable databases are open before running below preupgrade step:

source Oracle Home : /refresh/app/oracle/product/12.2.0.1.0

target Oracle Home : /refresh/app/oracle/product/19.0.0.0

 

For example,

$ export ORACLE_HOME=/refresh/app/oracle/product/12.2.0.1.0
$ export PATH=/refresh/app/oracle/product/12.2.0.1.0/bin:$PATH
$ export ORACLE_SID=cdb12201
$ /refresh/app/oracle/product/12.2.0.1.0/jdk/bin/java -jar /refresh/app/oracle/product/19.0.0.0/rdbms/admin/preupgrade.jar FILE TEXT
PREUPGRADE SUMMARY
==================
/refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade.log
/refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_fixups.sql
/refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/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 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/ -b preup_cdb12201 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_fixups.sql

2. Review logs under /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/

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 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/ -b postup_cdb12201 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/postupgrade_fixups.sql

2. Review logs under /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/.

The preupgrade.jar has generated fixup scripts for CDB$ROOT,PDB$SEED and pluggable databases. you may find below scripts in /u01/app/oracle/cfgtoollogs/cdb12102/preupgrade/*.sql.
[oracle@celvpvm14867 preupgrade]$ ls -ltr /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/*upgrade*
-rw-rw-r– 1 oracle oracle 7884 May 31 07:33 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_driver.sql
-rw-rw-r– 1 oracle oracle 455876 May 31 07:33 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_package.sql
-rw-rw-r– 1 oracle oracle 100166 May 31 07:33 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_messages.properties
-rw-rw-r– 1 oracle oracle 9855 May 31 07:33 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_fixups_CDB_ROOT.sql
-rw-rw-r– 1 oracle oracle 9259 May 31 07:33 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/postupgrade_fixups_CDB_ROOT.sql
-rw-rw-r– 1 oracle oracle 7780 May 31 07:33 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_CDB_ROOT.log
-rw-rw-r– 1 oracle oracle 8005 May 31 07:33 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_fixups_PDB_SEED.sql
-rw-rw-r– 1 oracle oracle 9258 May 31 07:33 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/postupgrade_fixups_PDB_SEED.sql
-rw-rw-r– 1 oracle oracle 7317 May 31 07:33 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_PDB_SEED.log
-rw-rw-r– 1 oracle oracle 7093 May 31 07:33 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_fixups_PDB12201.sql
-rw-rw-r– 1 oracle oracle 9258 May 31 07:33 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/postupgrade_fixups_PDB12201.sql
-rw-rw-r– 1 oracle oracle 6578 May 31 07:33 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_PDB12201.log
-rw-rw-r– 1 oracle oracle 7077 May 31 07:34 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_fixups_PDB1.sql
-rw-rw-r– 1 oracle oracle 9242 May 31 07:34 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/postupgrade_fixups_PDB1.sql
-rw-rw-r– 1 oracle oracle 6570 May 31 07:34 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_PDB1.log
-rw-rw-r– 1 oracle oracle 7077 May 31 07:34 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_fixups_PDB2.sql
-rw-rw-r– 1 oracle oracle 9242 May 31 07:34 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/postupgrade_fixups_PDB2.sql
-rw-rw-r– 1 oracle oracle 6570 May 31 07:34 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_PDB2.log
-rw-rw-r– 1 oracle oracle 32106 May 31 07:34 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_fixups.sql
-rw-rw-r– 1 oracle oracle 33554 May 31 07:34 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/postupgrade_fixups.sql
-rw-rw-r– 1 oracle oracle 34815 May 31 07:34 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade.log

 

 

Examine the preupgrade.log file and follow the recommendation. Execute the preupgrade_fixups_<pdb_name>.sql against all or respective pluggable database.

The below command will run the preupgrade_fixups.sql against all the pluggable databases.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 2 -e -b preupgrade_fixups /refresh/app/oracle/cfgtoollogs/cdb12201/preupgradepreupgrade_fixups.sql

To run the preupgrade fixups script against a particular pluggable database, below command can be used:

 $ORACLE_HOME/perl/bin/perl catcon.pl -c ‘CDB$ROOT’ -n 2 -e -b /preupgrade_fixups_cdbroot /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_fixups_CDB_ROOT.sql

Step 3.2 Check for Invalid Objects / Components.

Verify the below queries against the database and all the pluggable databases.

set pagesize 500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status=’INVALID’ order by owner,object_type;

select owner,object_type,count(*) from dba_objects where status=’INVALID’ group by owner,object_type order by owner,object_type ;

If you find invalid objects and/or database components then try to VALIDATE the invalid objects and/or database components by executing the following steps:

Run $ORACLE_HOME/rdbms/admin/utlrp.sql to validate the invalid objects in the database. You can execute the utlrp.sql scripts multiple times to validate the invalid objects.

From source Oracle Home, execute the utlrp.sql against the container and all the pluggable databases.

cd $ORACLE_HOME/rdbms/admin/
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d ”’.”’ utlrp.sql

$ sqlplus “/ as sysdba”
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

 

Step 3.3 Backing Up Oracle Database For Upgrade.

It is recommended to backup Oracle database after you run the Pre-Upgrade Information Tool. Take backup or create a guaranteed restore point or both. Test your backup. Ensure there is a proper fallback plan in case of any issues.

Step 3.4 Verify SYS and SYSTEM default tablespace.

Ensure the users SYS and SYSTEM have ‘SYSTEM’ as their default tablespace.

You must have sufficient space in the ‘SYSTEM’ tablespace or set extents to unlimited.

SQL> alter session set container=<PDB_Name>;

SQL> SELECT username, default_tablespace

FROM dba_users

WHERE username in (‘SYS’,’SYSTEM’);

If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace for user SYS and SYSTEM to SYSTEM by using the command below:

SQL> alter user SYS default tablespace SYSTEM;

SQL> alter user SYSTEM default tablespace SYSTEM;

 

 

Step 3.5 Gathering Optimizer Statistics to Decrease Database Upgrade Downtime

Oracle strongly recommends that you use this procedure to gather statistics before performing Oracle Database upgrades.Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, enter the following command:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b gatherstats — –x”exec dbms_stats.gather_dictionary_stats”

Above command gather dictionary statistics for all PDBs in a container database.

you can use the below command to gather statistics against a particular PDB.

 

For pdb_1 pluggable database :

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -c ‘PDB_1’ -b pdb_1_gatherstats — –x”exec dbms_stats.gather_dictionary_stats”

for cdb$root container:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -c ‘CDB$ROOT’ -b root_gatherstats — –x”exec dbms_stats.gather_dictionary_stats”

 

 

Step 3.6 Verifying Materialized View Refreshes are Complete Before Upgrade

Use this procedure to query the system to determine if there are any materialized view refreshes still in progress. Before upgrading Oracle Database, you must wait until all materialized views have
completed refreshing.

 $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -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″

It will verify against all the pluggable databases or below query can be used to verify against particular PDB:

 SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

Doc ID 1406586.1 – How to Handle Materialized Views When You Upgrade or Clone a Database

 

Step 3.7 Check of TIMESTAMP WITH TIMEZONE Datatype

The time zone files that are supplied with Oracle Database 19c release is version 32.

Case 1 Timezone version of source database is lower or equal 32.

If the source database is using a timezone file lower than version 32 then there is no DST patch to apply in source oracle home or target 12cR2 home.

Case 2 Timezone version of source database is higher than 32.

If the source database uses a Timezone version higher than 32 then BEFORE the upgrade you MUST patch the target 19c Oracle Home with a timezone data file of the SAME version as the one used in the source release database.

Step 3.8 Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading

 

Execute below query to check for the status of the backup:

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

Ensure that no files require media recovery:

SQL> SELECT * FROM v$recover_file;

Step 3.9 Purging Recycle Bin before upgrade

below command can be used to purge from all the pdbs:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b purge_recyclebin — –x”PURGE DBA_RECYCLEBIN”

or

SQL> PURGE DBA_RECYCLEBIN;

Step 3.10 Resolve Outstanding Distributed Transactions Before Upgrading

To resolve outstanding distributed transactions:

1. Issue the following statement:

SQL> Select * from dba_2pc_pending;

2. If the query in the previous step returns any rows, then issue the following statements:

SQL> select local_tran_id FROM dba_2pc_pending;

SQL> execute dbms_transaction.purge_lost_db_entry(”);

SQL> commit;

 

Step 3.11 Starting with Oracle Database 12c release 2 (12.2), you can upgrade the database without disabling Oracle Database Vault.

If your target Oracle Database release is 12.2 or later, then you can upgrade without disabling Oracle Database Vault.
If you have Oracle Database Vault enabled in your source Oracle Database release, then you can upgrade Oracle Database to Oracle Database 18c and later releases without first disabling Oracle Database Vault. After the upgrade, if your source Oracle Database release is Oracle Database 12c release 1 (12.1) or later, then Oracle Database Vault is enabled with the same enforcement settings that you had in place before the upgrade.

For example, if your source database is Oracle Database release 12.1, and Oracle Database Vault was disabled in that release, then it remains disabled after you upgrade.

If your source Oracle Database release 12.1 database had Oracle Database Vault enabled before the upgrade, then Oracle Database Vault is enabled after the upgrade.

If you manually disable Oracle Database Vault before the upgrade, then you must enable Oracle Database Vault manually after the upgrade

Step 3.12 Back Up Files to Preserve Downgrade and Recovery Options.

Oracle Data Guard Broker Configuration File and Downgrades

With upgrades to Oracle Database 19c and later releases, you must back up the Data Guard broker configuration file to preserve the capability to downgrade to an earlier release.

In releases before Oracle Database 19c, Oracle Database settings that are mapped to Oracle Data Guard broker properties are maintained in the Oracle Data Guard broker configuration file, and can be modified using the DGMGRL command-line interface. However, starting with Oracle Database 19c, these database settings are no longer stored in the broker configuration file. As a result of this change, although you can continue to modify these properties using DGMGRL, the values that you modify are no longer stored in the Oracle Data Guard broker configuration file. Instead, the DGMGRL commands directly modify the Oracle Database initialization parameters or database settings to which these Oracle Data Guard Broker properties are mapped.

Because of this change to the way that property settings are managed, if you use Oracle Data Guard broker, then Oracle recommends that you export your earlier release Oracle Data Guard broker configuration file to a secure backup location before you start the upgrade. If you do not back up the Oracle Data Guard broker configuration file before the upgrade, then after the upgrade, you cannot downgrade to an earlier release and retain the property options you previously selected for Oracle Data Guard

 

Step 3.13 Schema-Only Accounts and Upgrading EXPIRED Password Accounts

Before starting your upgrade, determine if you want to use password authenticate to default Oracle Database accounts where their passwords are in EXPIRED status, and their account is in LOCKED status

During upgrades to Oracle Database 19c, default Oracle accounts that have not had their passwords reset before upgrade (and are set to EXPIRED status), and that are also set to LOCKED status, are set to NO AUTHENTICATION after the upgrade is complete.

Because of this new feature, default accounts that are changed to schema-only accounts become unavailable for password authentication. The benefit of this feature is that administrators no longer have to periodically rotate the passwords for these Oracle Database-provided schemas. This feature also reduces the security risk of attackers using default passwords to hack into these accounts.

If you want to prevent these Oracle accounts from being set to schema-only accounts during the upgrade, then you must either set a valid strong password for the account before you start the upgrade, or set a valid strong password for these accounts after upgrade, or unlock the accounts before you log in to the upgraded Oracle Database.

After the upgrade, an administrator can also enable password authentication for schema-only accounts. However, for better security, Oracle recommends that you keep these accounts as schema only accounts.

Step 3.14 Copying Transparent Encryption Oracle Wallets

If Oracle wallet used with Transparent Data Encryption (TDE), then copy the sqlnet.ora and wallet file to the new Oracle home. You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.

1. Log in as an authorized user.
2. Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.

Open the Oracle wallet in mount.

For example:

SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN

Step  3.15 Understanding Password Case Sensitivity and SEC_CASE_SENSITIVE_LOGON parameter

Starting with Oracle Database 12c release 2 (12.2), the default password-based authentication protocol configuration excludes the use of the case-insensitive 10G password version. By default, the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12, which is an Exclusive Mode.

For greater security, Oracle recommends that you leave case-sensitive password-based authentication enabled. This setting is the default. However, you can temporarily disable case-sensitive authentication during the upgrade to new Oracle Database releases. After the upgrade, you can then decide if you want to enable the case-sensitive password-based authentication feature as part of your implementation plan to manage your password versions.

Before upgrading, Oracle recommends that you determine if this change to the default password-based authentication protocol configuration affects you. Perform the following checks:

  • Identify if you have accounts that use only 10G case-insensitive password authentication versions.
  • Identify if you have Oracle Database 11g release 2 (11.2.0.3) database or earlier clients that have not applied critical patch update CPUOct2012, or a later patch update, and have any account that does not have the case-insensitive 10G password version.
  • Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE. Setting this parameter to FALSE prevents the use of the case-sensitive password versions (the 11G and 12C password versions) for authentication.

Step 3.16 Dependencies on Network Utility Packages

After executing preupgrade script, verify the preupgrade log

WARNING: –> Database contains schemas with objects dependent on network packages.
…. Refer to the Database Upgrade Guide for instructions to configure Network ACLs.
…. USER WKSYS has dependent objects.
…. USER SYSMAN has dependent objects.
…. USER FLOWS_010600 has dependent objects.

Execute the following query to view DBA_DEPENDIENCIES

SQL> SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN

(‘UTL_TCP’,’UTL_SMTP’,’UTL_MAIL’,’UTL_HTTP’,’UTL_INADDR’,’DBMS_LDAP’) AND owner NOT IN (‘SYS’,’PUBLIC’,’ORDPLUGINS’);

To ensure that the new access controls are part of your upgrade testing, prepare a post-upgrade script to make the scripts available in your database environment. After the upgrade, grant specific required privileges. Access is based on the usage in the original database.

Step 3.17 Running Upgrades with Read-Only Tablespaces.

Use the Parallel Upgrade Utility with the -T option to take schema-based tablespaces offline during upgrade. Oracle Database can read file headers created in earlier releases, so you are not required to do anything to them during the upgrade. The file headers of READ ONLY tablespaces are updated when they are changed to READ WRITE. If the upgrade suffers a catastrophic error, so that the upgrade is unable to bring the tablespaces back online, then review the upgrade log files. The log files contain the actual SQL statements required to make the tablespaces available. To bring the tablespaces back online, you must run the SQL statements in the log files for the database, or run the log files for each PDB.

Step 3.18 Disable all batch and cron jobs.

For jobs initiated by Oracle then packages DBMS_JOB, DBMS_SCHEDULER can be used.

For cron jobs (external jobs controlled at the OS level) then this is a task for your Unix administrator.

See also:

Note 404238.1 : How to Disable an Entry from DBMS_SCHEDULER

Note 1335741.1 : How To Stop A Running Job Using DBMS_JOB

Note 67695.1 : PROCEDURE DBMS_JOB.BROKEN Specification

 

Step 3.19 Deprecated and Desupported Parameters

Remove desupported initialization parameters and adjust deprecated initialization parameters. In new releases, some parameters are desupported, and other parameters are deprecated. Remove all desupported parameters from any parameter file that starts the new Oracle Database instance. Desupported parameters can cause errors in new Oracle Database releases.

The Pre-Upgrade Information Tool displays any deprecated parameters and desupported parameters it finds in the Deprecated Parameters and Desupported Parameters sections, respectively.

 

Step 3.20 Review and Remove any unnecessary hidden/underscore parameters

Please review and remove any unnecessary hidden/underscore parameters prior to upgrading. It is strongly recommended that these be removed before upgrade unless your application vendors and/or Oracle Support state differently. Changes will need to be made in the init.ora or spfile.To view existing hidden parameters execute the following command while connected AS SYSDBA

SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE ‘\_%’ ESCAPE ‘\’ order by name;

 

Step 4 Preparing the New Oracle Home for Upgrading

  • Copy configuration files from the Oracle home of the database being upgraded to the new release Oracle Database Oracle home.
  • If you have a password file that resides within the old environment Oracle home, then move or copy the password file to the new Oracle home. It is recommended to recreate the password file freshly to leverage the new features (if any) provided by orapwd utility.
  • Remove desupported initialization parameters and adjust deprecated initialization parameters. In new releases, some parameters are desupported, and other parameters are deprecated. Remove all desupported parameters from any parameter file that starts the new Oracle Database instance. Desupported parameters can cause errors in new Oracle Database releases. Also, alter any parameter whose syntax has changed in the new release.
  • If you are upgrading a cluster database, then you need to change CLUSTER_DATABASE=FALSE before starting upgrade

Step 5 Upgrade step

Start the instance by issuing the following command in SQL*Plus:

Startup CDB in ugprade mode

 

SQL> startup upgrade pfile=<pfile location>

Start all PDB in upgrade mode

SQL> alter pluggable database all open upgrade;

Exit SQL*Plus before proceeding to the next step.

SQL> exit;

 

Start the upgrade using the Parallel Upgrade Utility (catctl.pl, using the shell command dbupgrade), where -d specifies the location of the directory:

cd $ORACLE_HOME/rdbms/admin

dbupgrade -d $ORACLE_HOME/rdbms/admin

catupgrade logs will be generated separately for Seed database and each PDB.

Note: The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process. Check the log file for “BEGIN catuppst.sql” to verify that catuppst.sql ran during the upgrade process.

Review the upg_summary.log to confirm that the upgrade was successful, and if necessary, review other logs.

Step 6 Post-Upgrade steps

Recompile the invalid objects by executing the utlrp.sql against the container and all the pluggable databases.

cd $ORACLE_HOME/rdbms/admin/
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d ”’.”’ utlrp.sql

Modify the corresponding entry in /etc/oratab file to point to the Oracle 19c ORACLE_HOME.

then execute the postupgrade_fixups.sql script generated. Below is the command:

$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/ -b postup_cdb12201 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/postupgrade_fixups.sql

Execute Post-Upgrade Status Tool, utlusts.sql and review the same. The script verifies that all issues are fixed.

SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql

For Oracle RAC environment, set the initialization parameter value for CLUSTER_DATABASE to TRUE, and Run srvctl command for Oracle Database 19c to upgrade the database. For example:

ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
$ORACLE_HOME/bin/srvctl upgrade database -db name -o ORACLE_HOME

 

Recovery Catalog Upgrade

If you use a version of the recovery catalog schema that is older than that required by the RMAN client, then you must upgrade it. You can upgrade the Recovery catalog by executing the UPGRADE CATALOG command

Please refer to Oracle documentation under “Upgrading the Recovery Catalog” for complete information and steps
Upgrade the Time Zone File Version After Upgrading Oracle Database

If the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade, then use the DBMS_DST PL/SQL package to update the RDBMS DST (timezone) version.

For latest DST patch refer to Note 412160.1.

The following scripts get delivered with Oracle Database 18c onward

$ORACLE_HOME/rdbms/admin/utltz_countstats.sql
Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info. No restart required.

$ORACLE_HOME/rdbms/admin/utltz_countstar.sql
Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of utlz_upg_check.sql and utlz_upg_apply.sql scripts.

$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
Time zone upgrade check script

?/rdbms/admin/utltz_upg_apply.sql
Time zone apply script. Warning: This script will restart the database and adjust time zone data.

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: