Oracle 18c – Complete checklist for Manual Upgrade for Multitenant Architecture Oracle Databases from 12.1.x.x to 18.x.x.x

PURPOSE

This document is created for use as a guideline and checklist when upgrading 12cR1, 12cR2 multitenant database to higher version 18.x.x.x

SCOPE

Database Administrators, Support

DETAILS

Introduction

Multitenant architecture database can either be upgraded in parallel, or in sequence.

Starting with Oracle Database 12c release 1 (12.1), Oracle provides multitenant architecture, which enables the creation and administration of pluggable databases (PDBs) in a container database (CDB). You can upgrade multitenant architecture systems using either Oracle Database Upgrade Assistant (DBUA), or using the Parallel Upgrade Utility to carry out manual upgrades.

There are two techniques for upgrading Oracle Databases using the multitenant architecture:

• In parallel. With this technique, you carry out one upgrade operation that upgrades the CDB, and then upgrades the PDBs in parallel.
• Sequentially. With this technique, you install a new release CDB, prepare and unplug PDBs from the earlier release CDB, plug the PDBs into a later release CDB, and then complete the upgrade for each PDB.

Upgrading Oracle Multitenant In Parallel

With the In Parallel technique, you first upgrade CDB$ROOT using the Parallel Upgrade Utility (catctl.pl), using parameters to set the degree of parallel processing and availability:

The -n parameter defines how many parallel processes run the upgrade, up to 8.

The -M parameter determines if the CDB$ROOT stays in UPGRADE mode through the entire upgrade, or becomes available for access after the CDB upgrade is complete. If you do not run the upgrade with the -M parameter, then when the CDB$ROOT upgrade is complete, PDBs then become available for access as soon as each PDB completes its upgrade. If you run the upgrade with the -M parameter, then CDB$ROOT stays in UPGRADE mode, and PDBs do not become available until upgrade of all PDBs is complete.

Upgrading Oracle Multitenant In Sequence

With the In Sequence technique, you install the new release multitenant architecture CDB. Next, in the earlier release multitenant architecture CDB, you issue SQL commands to run preupgrade scripts to prepare one or more PDBs to upgrade, and shut them down. You then unplug PDBs, plug them into the new release multenant architecture CDB, and complete the upgrade sequentially for each PDB.

Manually Upgrading a Multitenant Container Oracle Database (CDB)

Step 1: Recommendations for source CDB / PDB database

• Make sure there are no INVALID objects/components in the source database.  Verify the database health by executing dbupgdiag.sql (refer Note 556610.1 to download this script).
  • If there are any INVALID objects are found execute $ORACLE_HOME/rdbms/admin/utlrp.sql multiple times to validate them

• Take a cold or hot backup of the source database
• Disable any custom triggers that would get executed before / after DDL. You can enable it once upgrade is completed.

Check the status of PDB. It should be in open with Read write mode

These commands will also make sure the Multitenant Option is enabled.

Example:

• Execute Preupgrade scripts before upgrade and review the preupgrade log for any issues.
• Check the status of all materialized views (MV), and refresh any materialized views that are not fresh.
• Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.

Step2: Requirements and recommendations for target ORACLE_HOME

• Verify the whether your operating system is certified for 18.1. Click here to launch certification portal
• Install 18.1.0.0, verify there are no installation related issues.
• Download and install latest RU / RUR if any
• Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target homeReview patch recommendations as given in the article “Patches to apply before upgrading Oracle GI and DB to 18c (Doc ID 2414935.1)”
  • Remove any _ (underscore) parameter, obsolete and deprecated parameters in pfile
  • Note min value of COMPATIBLE parameter to upgrade 18.1 is “11.2.0”, make sure you have COMPATIBLE parameter is set to 11.2.0 or greater
• Review patch recommendations as given in the article “Patches to apply before upgrading Oracle GI and DB to 18c (Doc ID 2414935.1)”
• Apply patch 29213893 on target ORACLE_HOME to avoid ORA-01422 error – refer: Database Upgrade to 12.2, 18c, 19c fails with ORA-01422, ORA-06512 for SYS.DBMS_STATS (Doc ID 2525596.1)

Step 3: Pre-Upgrade Steps

1. Open all PDBs

SQL> alter pluggable database all open;

2. Run the Pre-Upgrade Information Tool (preupgrade.jar), using the following syntax:

Use space-delimitation for lists. On Linux and UNIX, define the list by placing the list inside single quotes: ‘. On Windows systems, define the list by placing the list inside double quotes “.

Linux and UNIX:

Windows:

Note: You must use Java 1.5 or later to run the Pre-Upgrade Information too.
If no parameters are passed to the preupgrade.jar , it is run against all the container databases ( CDB and PDB’s ).

3. Review any generated fixup scripts and log files.

If ORACLE_BASE is defined, then the fixup files are placed in

Linux and UNIX:

Windows:

If ORACLE_BASE is not defined, then fixup files are placed in one of the following paths:

Linux and UNIX:

Windows:

4. Execute the preupgrade_fixups script, or individual PDB scripts.\

Note: fixup script for PDB$SEED is preupgrade_fixups_pdb_seed.sql

5. For Oracle RAC database set CLUSTER_DATABASE to false

6. Shut down the database in the old Oracle home.

For example, where db_unique_name is your database name:

7. Copy the PFILE or SPFILE from the old Oracle home to the new Oracle home

Step 4: Upgrade Steps

8. Connect with SQL*Plus:

Note: Make sure that you have set ORACLE_HOME, PATH which points to target version

9. Bring the CDB$ROOT instance into upgrade mode:

10. Bring all PDBs into upgrade mode:

11. Check the status of PDBs to confirm that they are ready to upgrade:

For all PDBs, ensure that the status is set to MIGRATE.
12. Exit from SQL*Plus, and change directory to the new Oracle home $ORACLE_HOME/rdbms/admin:

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

If you do not specify any parameters, then the Parallel Upgrade Utility runs the upgrade in parallel on the number of PDBs equivalent to the number of CPUs divided by 2. On a server with 64 CPUs, 64 divided by 2 equals 32 PDBs upgraded in parallel, carried out with two SQL processors for each PDB. CDB$ROOT remains in NORMAL mode for the duration of the upgrade.

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

Step 5: Post-Upgrade Steps

15. Open all PDBs, so that you can recompile the databases:

16. Exit from SQL*Plus, and change directory to the new Oracle home path $ORACLE_HOME/rdbms/admin:

17. Run the catcon.pl script and the postupgrade_fixups.sql script that is supplied with the new release Oracle Database.

The following example shows the command strings for running catcon.pl, using the -n parameter to specify one parallel processor for each PDB, using the -d parameter to specify the path where the preupgrade script that you want to run is located, using the -l parameter to specify the location where you want the scripts to place log files, and using the -b flag to specify the log file prefixes for the postupgrade_fixups.sql script:

18. Run postupgrade_fixups.sql.

19. Run utlu122s.sql to verify that there are no upgrade issues.

log file utlu122s0.log is generated.

20. For Oracle RAC environments set the initialization parameter value for CLUSTER_DATABASE to TRUE, and to start the Oracle RAC database

Upgrading PDBs Using the Parallel Upgrade Utility with Priority Lists

Oracle Database 12.2 and later releases, you can upgrade PDBs using a priority list to upgrade a set of PDBs ahead of other PDBs, and we can modify that upgrade priority.  This list enables to group and upgrade PDBs based on the priority.  A priority list is a text file with comma-delimited lists defining the order of upgrade priority, and the PDBs in each numeric priority group. We can execute the Parallel Upgrade Utility (dbupgrade, dbupgrade.cmd, or catctl.pl) using the -L option to run the upgrade using a priority list, and to call that list as the upgrade runs.

Format for creating priority list

Note:

• Create priority lists using a plain text editor, such as vi on Linux and UNIX, or Notepad on Windows.
• Here numeral represents the priority for the PDB.

Syntax to run the Parallel Upgrade utility using a priority list:

After upgrade is complete PDB priority states are maintained in the CDB.  If we upgrade the database to next release, it uses the same priority.  To change this we have to execute

PDB Upgrades Using Priority Lists, Inclusion Lists, and Exclusion Lists

The following terms designate types of upgrade list processing:

Example of Inclusion List

With following priority list

1,CDB$ROOT
1,PDB$SEED
1,CDB1_PDB2,CDB1_PDB4
2.CDB1_PDB5

The upgrade order is carried out in the following sequence:

CDB1_PDB2, CDB1_PDB4
CDB1_PDB5

The Parallel Upgrade Utility processes only the PDBs that are in the inclusion list, and in the order of the priority list.

Example of Exclusion List

With following priority list

1,CDB$ROOT
1,PDB$SEED
1,CDB1_PDB1,CDB1_PDB2
2,CDB1_PDB3,CDB1_PDB4
3,CDB1_PDB5

Because CDB$ROOT is excluded, the priority processing shifts. The upgrade order is carried out in the following sequence:

PDB$SEED, CDB_PDB1
CDB_PDB2, CDB_PDB3
CDB1_PDB4, CDB1_PDB5

Exclusion List using CATCTL_LISTONLY

With following priority list

1,CDB$ROOT
1,PDB$SEED
1,CDB1_PDB1,CDB1_PDB2
2,CDB1_PDB3
3,CDB1_PDB5

With the CATCTL_LISTONLY option, PDBs that are not in the priority list are excluded from the upgrade.  Specifying the keyword CATCTL_LISTONLY in the exclusion list turns the priority list into an inclusion priority list. Only PDBs in the list are processed.

Upgrading Pluggable Databases (PDBs) In Parallel

Using the In-Parallel technique, you can upgrade the CDB, and then immediately upgrade PDBs using parallel SQL processors.

Container databases (CDBs) can contain zero, one, or more pluggable databases (PDBs). By default, the Parallel Upgrade Utility (catctl.pl) updates the CDB and all of its PDBs in the same upgrade window. The Parallel Upgrade Utility uses the number of computer processing units (CPUs) to determine the maximum number of PDBs that are upgraded simultaneously. The number of PDBs that are upgraded in parallel is determined by dividing the parallel SQL process count (-n option) by the parallel PDB SQL process count (-N option).

Steps to upgrade PDBs in parallel

1. Take necessary backup
2. Execute pre-upgrade tool, fix up any issues found, make sure there are no INVALID objects/components
3. un the Parallel Upgrade Utility. In sequence, the following upgrades are carried out:

a. Cycle 1: CDB$ROOT is upgraded to the new Oracle release
b. Cycle 2 to Cycle x: PDB$SEED and PDBs are upgraded in parallel, with the number of cycles of upgrades as determined by the parameter settings you specify with -n.
4. Complete post-upgrade steps.

Upgrading Multitenant Container Databases In Parallel

Use this technique to upgrade CDB$ROOT, PDB$SEED, and all PDBS in the CDB in one upgrade operation.
Oracle recommends that you use this approach if you can schedule downtime, because it provides a direct procedure for upgrades and simplicity of maintenance. Using this procedure upgrades in parallel all the PDBs in the multitenant architecture container database, depending on your server’s available processors (CPUs).

1. Ensure that you have a proper backup strategy in place.
2. Open all PDBs.
For example:

3. Run the Pre-Upgrade Information Tool (preupgrade.jar), using the following syntax:

4. Review any generated fixup scripts and log files.

5. Run the preupgrade_fixups script, or individual PDB scripts.

6. For Oracle RAC databases, set the cluster database initialization parameter to false:

7. Shut down the database in the old Oracle home.

8. Copy the PFILE or SPFILE from the old Oracle home to the new Oracle home
9. Connect with SQL*Plus:

10. Bring the CDB$ROOT instance into upgrade mode:

11. Bring all PDBs into upgrade mode:

12. Check the status of PDBs to confirm that they are ready to upgrade:

For all PDBs, ensure that the status is set to MIGRATE.

13. Exit from SQL*Plus, and change directory to the new Oracle home $ORACLE_HOME/rdbms/admin:

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

15. Review the upg_summary.log to conf8irm that the upgrade was successful, and if necessary, review other logs.
16. Open all PDBs, so that you can recompile the databases:

17. Exit from SQL*Plus, and change directory to the new Oracle home path $ORACLE_HOME/rdbms/admin:

18. Run the catcon.pl script and the postupgrade_fixups.sql script that is supplied with the new release Oracle Database.

The following example shows the command strings for running catcon.pl, using the -n parameter to specify one parallel processor for each PDB, using the -d parameter to specify the path where the preupgrade script that you want to run is located, using the -l parameter to specify the location where you want the scripts to place log files, and using the -b flag to specify the log file prefixes for the postupgrade_fixups.sql script:

19. Run postupgrade_fixups.sql.

20. Run utlu122s.sql to verify that there are no upgrade issues.

When you use catcon.pl to run utlu122s.sql, the log file utlu122s0.log is generated.

21. For Oracle RAC environments only, enter the following commands to set the initialization parameter value for CLUSTER_DATABASE to TRUE, and to start the Oracle RAC database, where dbname is the name of the Oracle RAC database: