Skip to content

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

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus “/ as sysdba”

SQL> alter session set container=<PDB_Name>

SQL> @utlrp.sql

  • 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:

 

SQL> select name, open_mode,cdb,i.con_id, version from V$database,v$instance i;

NAME OPEN_MODE CDB CON_ID VERSION
——— ——————– — ———- —————–
DB18C READ WRITE YES 0 18.0.0.0.0

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

CON_ID NAME OPEN_MODE

———- —————————— ———-

2 PDB$SEED READ ONLY

3 PDB1 READ WRITE

  • 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:

java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar [TERMINAL|FILE|DIR outputdir] [TEXT|XML] [-c InclusionListOfPDBs] [-C ExclusionListOfPDBs]

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:

java -jar $ORACLE_HOME_18.1/rdbms/admin/preupgrade.jar -c ‘pdb1 pdb2 pdb3 pdb4 pdb5 pdb6 pdb7 pdb8 pdb9 pdb10 pdb11 pdb12 pdb13 pdb14 pdb15 pdb16 pdb17 pdb18 pdb19 pdb20 pdb21 pdb22 pdb23 pdb24 pdb25’

Windows:

java -jar %ORACLE_HOME_18.1%/rdbms/admin/preupgrade.jar -c “pdb1 pdb2 pdb3 pdb4 pdb5 pdb6 pdb7 pdb8 pdb9 pdb10 pdb11 pdb12 pdb13 pdb14 pdb15 pdb16 pdb17 pdb18 pdb19 pdb20 pdb21 pdb22 pdb23 pdb24 pdb25”

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:

$ORACLE_BASE/cfgtoollogs/db_unique_name/preupgrade

Windows:

%ORACLE_BASE%\cfgtoollogs\db_unique_name\preupgrade

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

Linux and UNIX:

$ORACLE_HOME/cfgtoollogs/db_unique_name/preupgrade

Windows:

%ORACLE_HOME\cfgtoollogs\db_unique_name\preupgrade

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

ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;

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

For example, where db_unique_name is your database name:

$ srvctl stop database -d db_unique_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:

sqlplus / as sysdba

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

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

STARTUP UPGRADE

10. Bring all PDBs into upgrade mode:

ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;

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

SHOW PDBS

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:

SQL> EXIT
$ ORACLE_HOME/bin

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

dbupgrade -d $ORACLE_HOME/rdbms/admin

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:

ALTER PLUGGABLE DATABASE ALL OPEN;

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

SQL> EXIT
cd $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:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d $ORACLE_HOME/cfgtoollogs/cdbupgr/preupgrade -l /home/oracle/upgrdDBA -b postupgrade_fixups postupgrade_fixups.sql

18. Run postupgrade_fixups.sql.

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b postupgradefixups -d ”’.”’ postupgradefixups.sql

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

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlu122s -d ”’.”’ utlu122s.sql

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

ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
srvctl start database -db db_unique_name

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

Number, Pdb
numeral,pdb1,pdb2,pdb3
numeral,pdb4
numeral,pdb5,pdb6,pdb7,pdb8
.
.
.

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.
When you run the Parallel Upgrade Utility, the following processing rules apply:

CDB$ROOT and PDB$SEED are always processed first, even if they are not present in the priority list.
All PDBs that are in priority lists are processed in order of priority
Any PDBs that are not listed in priority lists are processed after the PDBs named in the priority list.

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

dbupgrade -L priority_list_name

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

SQL> alter session set container = CDB$ROOT
SQL> alter pluggable database PDBName upgrade priorityPDBPriorityNumber

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

The following terms designate types of upgrade list processing:

    Priority lists: Comma-delimited lists that designate the upgrade priority of PDBs in the list.
Inclusion lists: Comma-delimited lists that designate PDBs that you want to upgrade. PDBs in these lists are upgraded after the PDBs listed in priority lists.
Exclusion lists: Comma-delimited lists that designate PDBs that you do not want to be upgraded.

 

Example of Inclusion List

With following priority list

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

catctl -L priority.lst -c ‘CDB1_PDB2 CDB1_PDB4 CDB1_PDB5’ catupgrd.sql

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

catctl -L priority.lst -C ‘CDB$ROOT’ catupgrd.sql

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

catctl -L priority.lst -C ‘CATCTL_LISTONLY’ catupgrd.sql

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).

dbupgrade [-M] -n [-N]

-M Specifies if CDB$ROOT is kept in upgrade mode, or if it becomes available when it completes upgrade:

If you run the Parallel Upgrade Utility with the -M parameter, then the upgrade places CDB$ROOT and all of its PDBs in upgrade mode, which can reduce total upgrade time. However, you cannot bring up any of the PDBs until the CDB and all of its PDBs are upgraded.

If you do not run the Parallel Upgrade Utility with the -M parameter, then CDB$ROOT is upgraded and restarted in normal mode, and the normal background processes are started. After a successful upgrade, only CDB$ROOT is opened in read/write mode. All the PDBs remain in MOUNT mode. As each PDB is upgraded, you can bring each PDB online while other PDBs are still being upgraded.

-n Specifies the number of in-parallel PDB upgrade processors.

If you do not specify a value for -n, then the default for -n is the CPU_COUNT value.

If you do specify a value for -n, then that value is used to determine the number of parallel SQL processes. The maximum value is unlimited. The minimum value is 4.

-N Specifies the number of SQL processors to use when upgrading PDBs. The maximum value is 8. The minimum value is 1. If you do not specify a value for -N, then the default value is 2.

The maximum PDB upgrades running concurrently is the value of -n divided by the value of -N.

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:

SQL> alter pluggable database all open;

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

java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar [TERMINAL|FILE|DIR outputdir] [TEXT|XML] [-c InclusionListOfPDBs] [-C ExclusionListOfPDBs]

Linux and UNIX:

java -jar $ORACLE_HOME_18.1/rdbms/admin/preupgrade.jar -c ‘pdb1 pdb2 pdb3 pdb4 pdb5 pdb6 pdb7 pdb8 pdb9 pdb10 pdb11 pdb12 pdb13 pdb14 pdb15 pdb16 pdb17 pdb18 pdb19 pdb20 pdb21 pdb22 pdb23 pdb24 pdb25’

Windows:

java -jar %ORACLE_HOME_18.1%/rdbms/admin/preupgrade.jar -c “pdb1 pdb2 pdb3 pdb4 pdb5 pdb6 pdb7 pdb8 pdb9 pdb10 pdb11 pdb12 pdb13 pdb14 pdb15 pdb16 pdb17 pdb18 pdb19 pdb20 pdb21 pdb22 pdb23 pdb24 pdb25”

4. Review any generated fixup scripts and log files.

By default, if ORACLE_BASE is defined, then the fixup files are placed in one of the following paths:

Linux and UNIX:

$ORACLE_BASE/cfgtoollogs/db_unique_name/preupgrade

Windows

%ORACLE_BASE%\cfgtoollogs\db_unique_name\preupgrade

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

Linux and UNIX:

$ORACLE_HOME/cfgtoollogs/db_unique_name/preupgrade

Windows:

%ORACLE_HOME\cfgtoollogs\db_unique_name\preupgrade

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

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

For example;

ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile;

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:

sqlplus / as sysdba

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

STARTUP UPGRADE

11. Bring all PDBs into upgrade mode:

ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;

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

SHOW PDBS

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:

SQL> EXIT
$ ORACLE_HOME/bin

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

dbupgrade -d $ORACLE_HOME/rdbms/admin

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:

ALTER PLUGGABLE DATABASE ALL OPEN;

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

SQL> EXIT
cd $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:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d \
$ORACLE_HOME/cfgtoollogs/cdbupgr/preupgrade -l /home/oracle/upgrdDBA -b \
postupgrade_fixups postupgrade_fixups.sql

19. Run postupgrade_fixups.sql.

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b postupgradefixups -d ”’.”’ postupgradefixups.sql

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

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlu122s -d ”’.”’ utlu122s.sql

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:

ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
srvctl start database -db db_unique_name
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: