Manual Upgrade Scenarios for Multitenant Architecture Oracle Databases

Review these topics to understand the manual upgrade scenarios and procedures for Oracle Database deployed with multitenant architecture.

Starting with Oracle Database 12c, multitenant architecture enables Oracle Database deployments using multitenant container databases (CDB) that contain pluggable databases (PDBs). All Oracle Database releases earlier than Oracle Database 12c release 1 (12.1.0.1) use non-CDB architecture.

Caution:

You cannot downgrade a database after you have set the compatible initialization parameter to 12.1.0.2. Only if the compatibility is set to 12.1.0.1 will a downgrade be possible for a pluggable database (PDB), and there may still be restrictions on downgrading.

Oracle strongly recommends that you upgrade your source and target databases to the most recent bundle patch set update (BP, PSU) before starting an upgrade, and before starting a downgrade.

Topics:

See Also:

Oracle Database Concepts for an overview of multitenant architecture

Oracle Database Administrator's Guide for complete information about creating and configuring a CDB

About Oracle Multitenant Oracle Database Upgrades

You can upgrade Oracle Databases installed on multitenant architecture either 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.

The following sections provide a high-level summary of each upgrade technique.

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)

The procedure in this section provides steps for upgrading a CDB manually.

You must complete the following steps before using this procedure:

  • Install the software for Oracle Database 12c

  • Prepare the new Oracle home

  • Run the Pre-Upgrade Information Tool

Oracle Database 12c introduces multitenant architecture, which enables Oracle Database to function as a multitenant container database (CDB) with pluggable databases. You can upgrade the CDB using DBUA, and in the process, upgrade all the pluggable databases attached to the CDB at the same time. Or, after you install the software for Oracle Database 12c and prepare the new Oracle home, you can proceed with a manual, command-line upgrade.

  1. If you have not done so, run the Pre-Upgrade Information Tool. Review the Pre-Upgrade Information tool output and correct all issues noted in the output before proceeding.

  2. Back up the source database.

  3. If you have not done so, prepare the new Oracle home.

  4. Shut down the database. (The syntax is the same for a non-CDB and a CDB.)

    SQL> SHUTDOWN IMMEDIATE
    

    Note:

    To close a PDB, you can specify it from the CDB root: alter pluggable database PDBname close.

  5. If your operating system is Windows, then complete the following steps:

    1. Stop the OracleService SID Oracle service of the database you are upgrading, where SID is the instance name. For example, if your SID is ORCL, then enter the following at a command prompt:

      C:\> NET STOP OracleServiceORCL
    2. Delete the Oracle service at a command prompt using ORADIM.

      If your SID is ORCL, then enter the following command, substituting your SID for SID.

      C:\> ORADIM -DELETE -SID ORCL
      
    3. Create the service for the new release Oracle Database at a command prompt using the ORADIM command of the new Oracle Database release.

      For example:

      C:\> ORADIM -NEW -SID SID -SYSPWD PASSWORD -MAXUSERS USERS
           -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
      

      Most Oracle Database services log on to the system using the privileges of the Oracle Home User. The service runs with the privileges of this user. The ORADIM command prompts you for the password to this user account. You can specify other options using ORADIM.

      In this example, if your SID value is ORCL, your password (SYSPWD) value is TWxy5791, the maximum number of users (MAXUSERS) value is 10, and the Oracle home path is C:\ORACLE\PRODUCT\12.2.0\DB, then enter the following command:

      C:\> ORADIM -NEW -SID ORCL -SYSPWD TWxy5791 -MAXUSERS 10
      -STARTMODE AUTO -PFILE C:\ORACLE\PRODUCT\12.2.0\DB\DATABASE\INITORCL.ORA
      

      ORADIM writes a log file to the ORACLE_HOME\database directory. The log file contains the name of the PDB in the multitenant database.

  6. If your operating system is Linux or UNIX, then perform the following checks:

    1. Your ORACLE_SID is set correctly

    2. The oratab file points to the Oracle home for Oracle Database 12c

    3. The following environment variables point to the Oracle Database 12c directories:

      • ORACLE_HOME

      • PATH

    4. Any scripts that clients use to set $ORACLE_HOME environment variable must point to the new Oracle home.

    Note:

    If you are upgrading an Oracle Real Application Clusters database, then perform these checks on all nodes where the Oracle Real Application Clusters database has instances configured.

    See Also:

    Oracle Database and Oracle Clusterware installation guides for information about setting other important environment variables on your operating system

  7. Log in to the system as the owner of the Oracle home under the new Oracle Database release.

  8. Start SQL*Plus in the new Oracle home from the path Oracle_home/rdbms/admin directory.

    For example:

    $ cd $ORACLE_HOME/rdbms/admin
    $ pwd
    /u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin
    $ sqlplus
  9. Connect to the database that you want to upgrade using an account with SYSDBA privileges:

    SQL> CONNECT / AS SYSDBA
    
  10. Start the CDB in upgrade mode:

    SQL> startup upgrade
  11. Start the instance by issuing the following command in SQL*Plus:

    SQL> alter pluggable database all open upgrade;
    

    If errors appear listing desupported initialization parameters, then make a note of the desupported initialization parameters and continue with the upgrade. Remove the desupported initialization parameters the next time you shut down the database.

    Note:

    Starting up the database in UPGRADE mode does the following:

    • Starts up the database with a new version of the Oracle Database instance

    • Restricts logins to SYSDBA

    • Disables system triggers

    • Performs additional operations that prepare the database for upgrades

  12. Exit SQL*Plus before proceeding to the next step.

    For example:

    SQL> EXIT
  13. To upgrade an entire CDB, run the Parallel Upgrade Utility (catctl.pl) from the new Oracle home. The Parallel Upgrade Utility provides parallel upgrade options that reduce downtime. You can run the command by using the command-line script dbupgrade from the new Oracle home.

    For example:

    Linux:

    cd $ORACLE_HOME/bin
    ./dbupgrade 
    

    Windows:

    cd %ORACLE_HOME%\bin
    dbupgrade 
    

    Note:

    • Use the -d option to specify the directory that contains the files that you want to process. Use the -l option to specify the directory that you want to use for spool log files.

    • If you are upgrading an entire CDB, and there are errors in CDB$ROOT, then the upgrade aborts.

  14. To upgrade a subset of PDBs within a CDB, specify either an inclusion list, or an exclusion list.

    • This example for a Linux or UNIX system uses an inclusion list to upgrade PDB1 only:

      cd $ORACLE_HOME/bin
      ./dbupgrade -c 'PDB1' 
      
    • This example for a Windows system uses an exclusion list to upgrade everything in the CDB except PDB1:

      cd $ORACLE_HOME\bin
      dbupgrade -C "PDB1" 

    Note:

    You can upgrade an individual PDB by unplugging it from the earlier release CDB, and plugging it into a later release CDB.

    For Windows, when you run the dbupgrade command with the inclusion (-c) or the exclusion (-C) options, you must specify the option with quotes around the CDB root name and PDB seed name.

    For example:

    ... -C "CDB$ROOT PDB$SEED"

  15. For CDBs, log in to the CDB as SYSDBA and run the command alter pluggable database all open to make databases available for recompiling code. For example:

    $ sqlplus / as sysdba
    
    SQL> alter pluggable database all open;
  16. Run catcon.pl. This command starts utlrp.sql and recompiles any remaining stored PL/SQL and Java code.

    For example:

    $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
    

    Because you run the command using -b utlrp0, the log file utlrp0.log is generated with the recompile results.

  17. Run postupgrade_fixups.sql.

    For example:

    $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b postupgrade_fixups -d '''.''' postupgrade_fixups.sql
  18. Run utlu122s.sql. This command verifies that all issues are fixed.

    For example, in a CDB:

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

    Because you run the command using -b utlu122s, the log file utlu122s0.log is generated with the upgrade results. You can review the upg_summary.log file to review the upgrade report.

    To see information about the state of the database, run utlu122s.sql as many times as you want, at any time after you complete the upgrade. If the utlu122s.sql script returns errors, or if it shows components that are not marked as VALID, or if the SQL script you run is not from the most recent release, then refer to the troubleshooting section in this guide.

  19. Ensure that the time zone data files are current by using the DBMS_DST PL/SQL package to upgrade the time zone file. You can also update the time zone after the upgrade. If you update the time zone, then you must update the time zone in both CDB$ROOT and the PDBs.

  20. Exit SQL*Plus.

    For example:

    SQL> EXIT
  21. (Conditional) If you are upgrading an Oracle Real Application Clusters database, then use the following command syntax to upgrade the database configuration in Oracle Clusterware:

    $ srvctl upgrade database -db db-unique-name -oraclehome oraclehome

    In this example, db-unique-name is the assigned database name (not the instance name), and oraclehome is the Oracle home location in which the database is being upgraded. The SRVCTL utility supports long GNU-style options, in addition to the short CLI options used in earlier releases.

Your database is now upgraded. You are ready to complete post-upgrade procedures.

Note:

Oracle Warehouse Builder (OWB) components are not upgraded as part of the Oracle Database upgrade process. Oracle Warehouse Builder is not installed as part of Oracle Database 12c. Upgrade Oracle Warehouse Builder separately.

Caution:

If you retain the old Oracle software, then never start the upgraded database with the old software. Only start Oracle Database using the start command in the new Oracle Database home.

Before you remove the old Oracle environment, relocate any data files in that environment to the new Oracle Database environment.

See Also:

Oracle Database Administrator’s Guide for information about relocating data files

About Upgrading PDBs Using the Parallel Upgrade Utility with Priority Lists

In Oracle Database 12.2, you can upgrade PDBs using a priority list to upgrade a set of PDBs ahead of other PDBs, and you can modify that upgrade priority.

Priority lists enable you to group and upgrade PDBs according to their 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. You run 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.

Create the list using the following format. In this format example, the variable numeral is a numeric value, and pdbx is the name of a PDB.

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

The numeral represents the priority for the PDB.

PDB priorities are as follows:

  1. CDB$ROOT: Priority 1. Upgrading the container database first is a mandatory priority. You cannot change the priority for the container database upgrade. CDB$ROOT is always processed first.

  2. PDB$SEED: Priority 1. Upgrading the PDB seed database is a mandatory priority. You cannot change the priority for the PDB seed upgrade. PDB$SEED always upgraded after CDB$ROOT, and with the first batch of PDB upgrades.

  3. Priority List 1 PDBs: Priority 1 is the highest user-selected priority. These PDBs are upgraded second after CDB$ROOT, in the batch where the PDB$SEED PDB is upgraded.

  4. Priority List 2 PDBs: Priority 2 is the second-highest priority PDB set. These PDBs are upgraded after the Priority 1 PDBs.

  5. Priority List 3 PDBs: Priority 3 is the third-highest priority PDB set. These PDBS are upgraded after priority 2 PDBs.

  6. Priority List 4 PDBs: Priority 4 is the fourth-highest priority PDB set. These PDBS are upgraded after priority 3 PDBs.

  7. Priority List 5 PDBs: Priority 5 is the fifth-highest priority PDB set. These PDBS are upgraded after priority 4 PDBs.

  8. Priority List 6 PDBs: Priority 6 is the sixth-highest priority PDB set. These PDBS are upgraded after priority 7 PDBs.

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.

For example:

Number,Pdb
1,sales1,region2,receivables1
2,sales2
3,dss1,region3,region2,dss2,dss3

Use the following syntax to run the Parallel Upgrade utility using a priority list:

dbupgrade -L priority_list_name

For example, to run the Parallel Upgrade Utility on a Windows system using the Parallel Upgrade Utility batch command and a priority list named My122Upgrade, enter the following command:

C:>\u01\app\12.2.0.0\db_home1\rdbms\admin\dbupgrade -L My122Upgrade 

After you complete a 12.2 upgrade using a priority list to set upgrade priorities, these PDB priority states are maintained in the CDB for the PDBs. The next upgrade honors the priorities set for the PDBs in the 12.2 upgrade.

Use the following SQL command syntax to change PDB upgrade priority states, where PDBName is the name of the PDB whose upgrade priority you want to change, and PDBPriorityNumber is the new priority value you want to assign:

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

For example:

SQL> alter session set container = CDB$ROOT
SQL> alter pluggable database region2 upgrade priority 2

In this example, the PDB named region 2 that was set to upgrade priority 1 in the previous example is changed to upgrade priority 2.

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

Starting in Oracle Database 12c release 2 (12.2), you can use inclusion and exclusion lists with priority lists to control how your PDBs are upgraded.

Upgrade Processing and 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.

You can use inclusion lists and exclusion lists in the following ways:

  • On their own, to include or exclude a set of PDBs from an upgrade

  • In conjunction with priority lists to provide detailed specifications for the order in which PDBs are upgraded, and which PDBs are excluded from an upgrade.

    When inclusion lists are used with priority lists, the PDBs listed in inclusion lists are upgraded according to the priority value they are assigned in the priority lists. PDBs listed in inclusion lists but not listed in priority lists are upgraded after all PDBs in the priority lists are upgraded.

    When exclusion lists are used with priority lists, the PDBs listed in exclusion lists are not upgraded.

Note:

Create priority lists using a plain text editor, such as vi on Linux and UNIX, or Notepad on Windows.

In the examples in this topic, the cpu_count value is equal to 2.

Upgrade Priority using Default Processing

Default processing is the upgrade processing that is carried out if you do not designate how you want to upgrade PDBs in your CDBs using lists.

With default processing, CDB$ROOT is upgraded, and then PDB$SEED. Depending on the degree of parallelism you set, one or more PDBs may be updated in parallel with PDB$SEED. As upgrades complete, PDBs are upgraded as upgrade processors become available.

The examples that follow use the following multitenant configuration of CDB and PDBs:

CDB$ROOT
PDB$SEED
CDB1_PDB1
CDB1_PDB2
CDB1_PDB3
CDB1_PDB4
CDB1_PDB5

In default processing, you specify no preference for which PDBs you want upgraded or excluded from upgrade. With default processing, CDB$ROOT is upgraded first, and PDB$SEED is updated in the first group of PDBs upgraded.

Example 3-29 Specifying Complete PDB Upgrade Priority

The following example of a priority list, where the priority setting for all PDBs is set by the list:


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

Here is another way of writing the same list, in which you group PDBs in priority order:

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

In the preceding example, the PDBs listed in priority 1 are CDB1_PDB1 and CDB1_PDB2. These PDBs are upgraded before CDB1_PDB3 and CDB1_PDB4.

Here is another way of writing the same list, using container ID values (CON_ID) to set the priority order:

1,CDB$ROOT
1,PDB$SEED
1,3,4
2,5,6
3,7

In the preceding example, the PDBs listed in priority 1 are CDB1_PDB1 (identified by CON_ID 3) and CDB1_PDB2 (identified by CON_ID 4). These PDBs are upgraded before CDB1_PDB3 (CON_ID 5) and CDB1_PDB4 (CON_ID 6).

When you use CON_IDs to specify priority, the first number specifies the priority of the group of PDBs. The second value or number specifies the PDBs (by CON_ID) number that are in that priority grouping. CDB$ROOT is always updated first, and PDB$SEED is always updated in the first upgrade priority group.

These examples all show a priority list upgrade with the following characteristics:

  • Exclusion processing: None

  • Inclusion processing: None

  • Default processing: None

The upgrade order is carried out in the following sequence:

  1. CDB$ROOT

  2. PDB$SEED, CDB1_PDB1

  3. CDB1_PDB2, CDB1_PDB3

  4. CDB1_PDB4, CDB1_PDB5

Example 3-30 Specifying a Priority Subset of PDBs, and Upgrading Other PDBs with Default Processing

The following example specifies a priority list called priority.lst, which specifies a subset of PDBs for upgrade:

catctl -L priority.lst catupgrd.sql
1,CDB$ROOT
1,PDB$SEED
1,CDB1_PDB1,CDB1_PDB2

This example shows a priority list upgrade with the following characteristics:

  • Exclusion processing: None

  • Inclusion processing: None

  • Default processing: CDB1_PDB3,CDB1_PDB4,CDB1_PDB5

The upgrade order is carried out in the following sequence:

  1. CDB$ROOT

  2. PDB$SEED, CDB1_PDB1

  3. CDB1_PDB2, CDB1_PDB3

  4. CDB1_PDB4, CDB1_PDB5

Example 3-31 Specifying a Priority Subset of PDBs, and Upgrading Other PDBs with an Inclusion List

The following example specifies a priority list called priority.lst, which specifies a priority subset of PDBs for upgrade:

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

This command refers to the following priority list:

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

This example shows a priority list upgrade with the following characteristics:

  • Exclusion processing: None

  • Inclusion processing: CDB1_PDB2 CDB1_PDB4 CDB1_PDB5

  • Default processing: None

The upgrade order is carried out in the following sequence:

  1. CDB1_PDB2, CDB1_PDB4

  2. 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 3-32 Specifying a Priority Subset of PDBs, and Excluding CDB$ROOT with an Exclusion List

The following example runs catctl using a priority list called priority.lst. Because this command runs with the -C option, it excludes CDB$ROOT from the upgrade:

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

This is the priority list:

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

The upgrades are processed using the priority list to specify upgrade priority.

  • Inclusion processing: None

  • Exclusion processing: CDB$ROOT

  • Priority processing: PDB$SEED, CDB1_PDB1, CDB1_PDB2, CDB1_PDB3, CDB1_PDB4, CDB1_PDB5

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

  1. PDB$SEED, CDB_PDB1

  2. CDB_PDB2, CDB_PDB3

  3. CDB1_PDB4, CDB1_PDB5

Example 3-33 Specifying an Exclusion List using CATCTL_LISTONLY

The following example specifies a priority list called priority.lst, which specifies a subset of PDBs for upgrade. With the CATCTL_LISTONLY option, PDBs that are not in the priority list are excluded from the upgrade:

catctl -L priority.lst -C 'CATCTL_LISTONLY' catupgrd.sql

Priority list:

1,CDB$ROOT
1,PDB$SEED
1,CDB1_PDB1,CDB1_PDB2
2,CDB1_PDB3
3,CDB1_PDB5
  • Exclusion processing: CATCTL_LISTONLY (Only process inclusion priority list)

  • Inclusion processing: None

  • Default processing: None

The upgrade order is carried out in the following sequence:

  1. CDB$ROOT

  2. PDB$SEED, CDB1_PDB1,CDB1_PDB2

  3. CDB1_PDB3, CDB1_PDB5

Note:

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. No default processing occurs in this scenario, so in this example, CDB1_PDB4 is not processed.

Example 3-34 Specifying a Priority List using CON_ID Values

The following example specifies a priority list called priority.lst, which specifies a subset of PDBs for upgrade:

catctl -L priority.lst -C 'CATCTL_LISTONLY' catupgrd.sql

The upgrade order is determined by the priority list priority number. In the list called by the -L parameter, priority.lst, the numbers following the upgrade priority number are the CON_ID values associated with PDB names:

1,3,4
2,5,CDB1_PDB4
3,7

In the preceding list example, note that you can use a mix of CON_ID numbers and PDB names.

The PDBs listed in priority 3 are CDB1_PDB1 (identified by CON_ID 3) and CDB1_PDB2 (identified by CON_ID 4). These PDBs are upgraded before CDB1_PDB3 (CON_ID 5), CDB1_PDB4, which is identified by name, and CDB1_PDB5 (CON_ID 7).

  • Exclusion processing: -C CATCTL_LISTONLY (Only process PDBs in the inclusion priority list)

  • Exclusion Processing: None

  • Inclusion processing: Specified in priority.lst

  • Default processing: CDB$ROOT, PDB$SEED

The upgrade order is determined by the priority list, which uses the CON_ID numbers associated with the PDB.

  1. CDB$ROOT

  2. PDB$SEED, CDB1_PDB1

  3. CDB1_PDB2, CDB1_PDB3

  4. CDB1_PDB4, CDB1_PDB5

Note:

This example demonstrates the use of CON_IDs to specify the PDBs, and omits CDB$ROOT and PDB$SEED from the priority list. CDB$ROOT and PDB$SEED are processed using default processing.

Upgrading Multitenant Architecture In Parallel

Use this technique to upgrade Oracle Database release 12c (12.1.0.1 and later releases) by upgrading container databases (CDBs), and then upgrading multiple pluggable databases (PDBs) in parallel.

Topics:

About 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). If you use the In Parallel upgrade technique, then you can use the upgrade automation features built into the Parallel Upgrade Utility (catctl.pl) to update the CDB and all of its PDBs in the same upgrade window. The In Parallel technique runs upgrade processes in parallel, based on the resource settings you provide. If you choose to use all of your available system resources for upgrade, then parallel processing is limited only by your system CPU power, and the number of cores.

For example, you can use the In-Parallel technique to patch all of your PDBs at the same time with one bundle patch or patch set update (BP or PSU). You can also upgrade the entire multitenant architecture to a later release, including CDB$ROOT, PDB$SEED, and all of the PDBs plugged into the CDB, using parallel processing to limit your downtime.

Note:

The advantage of this technique is simplicity and ease of maintenance that it provides. However, you must plan your upgrade window to accommodate a common downtime for all of the database services that the PDBs on the CDB are providing.

You can upgrade the entire multitenant architecture database in parallel using Parallel Upgrade Utility (catctl.pl), which you can start from the command line using the dbupgrade shell script. The following syntax shows the key parameters for in-parallel processing:

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

    The number of PDBs upgraded concurrently is controlled by the value you provide for the -n parameter.

    Starting in Oracle Database 12c, the default value for Oracle Multitenant databases is the number of CPUs on your system. A cpu_count value equal to 24 equates to a default value of 24 for -n. The maximum value is now unlimited. The minimum value is 4. The maximum PDB upgrades running concurrently is the value of -n divided by the value of -N.

  • -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 following is a high-level list of actions during the In Parallel PDB upgrade technique:

  1. Make sure that your backup strategy is complete.

  2. Copy and run the preupgrade scripts from the new Oracle Database release, and fix any issues that they uncover.

  3. Run the Parallel Upgrade Utility. In sequence, the following upgrades are carried out:

    1. Cycle 1: CDB$ROOT is upgraded to the new Oracle release

    2. Cycle 2: PDB$SEED...cycle x: PDBs are upgraded in parallel, with the number of cycles of upgrades as determined by the parameter settings you provide.

    If you do not specify -M, then PDBs become available to bring online as they complete upgrade.

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

Note:

When you upgrade the entire container using the In Parallel upgrade method, all the PDBs must be down. Perform the upgrade in a scheduled upgrade window so that you can bring all the PDBs down.

Caution:

  • Always create a backup of existing databases before starting any configuration change.

  • You cannot downgrade a database after you have set the compatible initialization parameter to 12.1.0.2. Only if the compatibility is set to 12.1.0.1 will a downgrade be possible for a pluggable database (PDB), and there may still be restrictions on downgrading.

  • Oracle strongly recommends that you upgrade your source and target databases to the most recent bundle patch or patch set update (BP or PSU) before starting an upgrade, and before starting a downgrade.

  1. Ensure that you have a proper backup strategy in place.
  2. (Conditional) For Oracle RAC environments only, enter the following commands to set the initialization parameter value for CLUSTER_DATABASE to FALSE:
    ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE;
    

    Restart the database after changing the CLUSTER_DATABASE parameter.

  3. Open all PDBs.

    For example:

    SQL> alter pluggable database all open;
    
  4. 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 ".

    For example, run the following command to run the Pre-Upgrade Information tool on PDBs PDB1 through PDB25, where you have set up an environment variable $ORACLE_HOME_12.1 for your Oracle Database Oracle home in /u01/app/ oracle/product/12.1.0/dbhome_1/, and you have set up an environment variable $ORACLE_HOME_12.2 for your new Oracle Database Oracle home in /u01/app/oracle/product/12.2.0/dbhome_1/:

    Linux and UNIX:

    java -jar $ORACLE_HOME_12.2/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_12.2%/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 tool. By default, the Java releases in Oracle Database releases that you can upgrade directly support the tool.

  5. Read 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

    On multitenant architecture Oracle Databases, the Pre-Upgrade Information Tool also creates a consolidated preupgrade_fixups.sql script. You can run the consolidated fixup script by using catcon.pl. The consolidated fixup script runs on every container that was open at the time that you ran the preupgrade.jar command.

  6. Run the preupgrade_fixups script, or individual PDB scripts. The preupgrade_fixups SQL scripts resolve some of the issues reported by the preupgrade script.

    On multitenant environment Oracle Database deployments, you can run preupgrade_fixupspdb-name.sql scripts on the source database, where pdb-name is the PDB name. If you generate fixup scripts for PDBs, then the PDB name is added to the fixup filename.

    In addition to the individual PDB fixup scripts, you can use catcon.pl to run the consolidated preupgrade_fixups.sql script. The consolidated script runs on every container that was open at the time that you ran preupgrade.jar.

    Note:

    Because $ is a reserved symbol on operating systems, the fixup script for PDB$SEED is preupgrade_fixups_pdb_seed.sql.

    Complete any other preupgrade tasks that the Pre-Upgrade Information Tool identifies.

  7. Switch to the new $ORACLE_HOME, and set any other needed environment variables.
  8. Connect with SQL*Plus:
    sqlplus / as sysdba
  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. Open all PDBs, so that you can recompile the databases:
    ALTER PLUGGABLE DATABASE ALL OPEN;
  15. Exit from SQL*Plus, and change directory to the new Oracle home path $ORACLE_HOME/rdbms/admin:
    SQL> EXIT
    cd $ORACLE_HOME/rdbms/admin
  16. 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
  17. Run the catcon.pl script:
    $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

    The catcon.pl script runs utlrp.sql from the $ORACLE_HOME/rdbms/admin directory.

    The script recompiles any remaining stored PL/SQL and Java code. Note the following conditions of this use case:

    • The -n parameter is set to 1, so the script runs each PDB recompilation in sequence.

    • Expect a time delay for the serial recompilation of PDBs to complete. Depending on the number of PDBs that you are upgrading, the recompilation can extend significantly beyond the time required for the upgrade scripts to complete.

  18. Run postupgrade_fixups.sql.

    Non- CDB:

    SQL> @rdbms/admin/postupgrade_fixups.sql

    CDB:

    $ORACLE_HOME/perl/bin /perl catcon.pl -n1 -e -b postupgradefixups -d '''.''' postupgradefixups.sql
  19. Run utlu122s.sql to verify that all issues have been fixed.

    Non-CDB:

    SQL> @rdbms/admin/utlu122s.sql
    

    CDB:

    $ORACLE_HOME/perl/bin /perl catcon.pl -n1 -e -b utlu122s -d '''.''' utlu122s.sql

    When you use catcon.pl to run utlu122s.sql, the log file utlu122s0.log is generated. The log file provides the upgrade results. You can also review the upgrade report, upg_summary.log.

    To see information about the state of the database, run utlu122s.sql as many times as you want, at any time after the upgrade is completed. If the utlu122s.sql script returns errors, or shows components that do not have the status VALID, or if the version listed for the component is not the most recent release, then perform troubleshooting.

  20. (Conditional) 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

    Your database is now upgraded.

    Note:

    Oracle Warehouse Builder (OWB) components are not upgraded as part of the Oracle Database upgrade process. Oracle Warehouse Builder is not installed as part of Oracle Database 12c.

    Caution:

    If you retain the old Oracle software, then never start the upgraded database with the old software. Only start Oracle Database using the start command in the new Oracle Database home.

    Before you remove the old Oracle environment, relocate any data files in that environment to the new Oracle Database environment.

    See Also:

    Oracle Database Administrator's Guide for information about relocating data files

Upgrading Multitenant Architecture Sequentially Using Unplug-Plug

Use this technique to upgrade Oracle Database release 12c (12.1.0.1 and later releases) by unplugging pluggable databases (PDBs) in earlier release container databases (CDBs), and plugging into later release CDBs

Topics:

About Upgrading Pluggable Databases (PDBs) Sequentially

Using the unplug-plug technique, you upgrade PDBs after you upgrade the CDB. Use this overview to understand PDB sequential upgrades.

Container databases (CDBs) can contain zero, one, or more pluggable databases (PDBs). You can upgrade one PDB without upgrading the whole CDB. For example, you can unplug a PDB from a release 12.1.0.2 CDB, plug it into a release 12.2.0.1 CDB, and then upgrade that PDB to release 12.2.0.1.

Starting with Oracle Database 12.2, you can use DBUA to upgrade PDBs, or upgrade PDBs manually. You can upgrade the CDB and all PDBs (an In Parallel manual upgrade), or you can upgrade the CDB, and then upgrade PDBs sequentially (a Sequential manual upgrade).

The following is a high-level list of the steps required for sequential PDB upgrades:

  1. Unplug the earlier release PDB from the earlier release CDB.

  2. Drop the PDB from the CDB.

  3. Plug the earlier release PDB into the later release CDB.

  4. Upgrade the earlier release PDB to a later release.

Starting in Oracle Database 12c release 2 (12.2), you can provide lists to the Parallel Upgrade Utility to upgrade PDBs:
  • Priority lists, to set the order in which PDBs are upgraded

  • Inclusion lists, which enable you to designate a set of PDBs to upgrade after the PDBs listed in the priority list are upgraded

  • Exclusion lists, which enable you to designate a set of PDBs that are not upgraded

Note:

A PDB cannot be recovered unless it is backed up. After upgrading using the method of creating a CDB and plugging in a PDB, be sure to back up the PDB.

Unplugging the Earlier Release PDB from the Earlier Release CDB

Unplugging the PDB is the first of three upgrade tasks.

  1. Run the Pre-Upgrade Information Tool on the PDB.

    For example, where the PDB named salespdb is running in the CDB in $ORACLE_HOME_12.1:

    $ORACLE_HOME_12.1/jdk/bin/java -jar 
    $ORACLE_HOME_12.2/rdbms/admin/preupgrade.jar dir /tmp -c salespdb
  2. Run preupgrade_fixups.sql on your source database.

    For example:

    CONNECT / AS SYSDBA
    SQL> ALTER SESSION SET CONTAINER=SALESPDB;
    
    SQL> @/tmp/preupgrade_fixups_salespdb.sql
  3. Close the PDB you want to unplug.

    For example, use the following command to close the PDB salespdb:

    SQL> alter pluggable database salespdb close;
    
  4. Follow all recommendations listed in preupgrade.log.

  5. Log back in to CDB$ROOT:
    CONNECT / AS SYSDBA
    SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
    
  6. Unplug the earlier release PDB using the following SQL command syntax, where pdb is the name of the PDB, and path is the location of the PDB XML file:

    alter pluggable database pdb unplug into 'path/pdb.xml';

    For example, where the pdb name is salespub and path is /home/oracle/salespdb.xml:

    SQL> alter pluggable database salespdb unplug into '/home/oracle/salespdb.xml';
    

    The following response displays when the command is completed:

    Pluggable database altered
  7. Drop the pluggable database SALESPDB, but keep data files.

    Oracle recommends that you drop SALESPDB after this procedure to clean up leftover information in the CDB views, and to help to avoid future issues. As a best practice guideline, back up your PDB in the destination CDB first, and then issue the DROP command on the source.

    Caution:

    After you drop the PDB from its original CDB, you cannot revert to it using previously taken backup, because the DROP command removes backup files.

    To drop the pluggable database, enter the following command:

    SQL> drop pluggable database salespdb keep datafiles; 
  8. Exit.

Plugging in the Earlier Release PDB to the Later Release CDB

Plugging the PDB from the earlier release PDB to the later release CDB is the second of three upgrade tasks.

This procedure example shows how to plug in a PDB when you are using Oracle-Managed Files. Refer to Oracle Database Administrator’s Guide for additional information about plugging in PDBs.

  1. Connect to the later release CDB.

  2. Plug in the earlier release PDB using the following SQL command, where pdb is the name of the PDB, and path is the path where the PDB XML file is located:

    create pluggable database pdb using 'path/pdb.xml';

    For example:

    SQL> create pluggable database salespdb using '/home/oracle/salespdb.xml';
    

    The following response displays when the command is completed:

    Pluggable database created.

Note:

When you plug in an earlier release PDB, the PDB is in restricted mode. You can only open the PDB for upgrade.

Upgrading the Earlier Release PDB to the Later Release

In the third of three PDB upgrade steps, you upgrade the earlier-release PDB to the release level of the CDB.

  1. If needed, switch to the PDB that you want to upgrade. For example, enter the following command to switch to the PDB salespdb:

    SQL> alter session set container=salespdb;
  2. Open the PDB in UPGRADE mode.

    SQL> alter pluggable database open upgrade;
    
  3. Upgrade the PDB using the Parallel Upgrade Utility command (catctl.pl, or the shell utility dbupgrade).

    When you upgrade a PDB, you use the commands you normally use with the Parallel Upgrade Utility. However, you also add the option -c PDBname to specify which PDB you are upgrading. Capitalize the name of your PDB as shown in the following example using the PDB named salespdb:

    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d  \
    $ORACLE_HOME/rdbms/admin -c 'SALESPDB' -l $ORACLE_BASE catupgrd.sql
  4. Review results.

    The default file path for the logs is in the path Oracle_base/cfgtoollogs/dbname/upgradedatetime, where Oracle_base is the Oracle base path, dbname is the database name, and upgradedatetime is the date and time for the upgrade. The date and time strings are in the character string format YYYYMMDDHHMMSC, in which YYYY designates the year, MM designates the month, DD designates the day, HH designates the hour, MM designates the minute, and SC designates the second.

    For example:

    $ORACLE_BASE/cfgtoollogs/salespdb/upgrade20160815120001/upg_summary.log
    
  5. Log in to SQL*Plus, and open the PDB to execute post-upgrade fixups, and to recompile the INVALID objects in the database:

    SQL> STARTUP;
    SQL> ALTER SESSION SET CONTAINER=SALESPDB;
    
  6. Use the utility catcon.pl to run the script postupgrade_fixups.sql:

    $ORACLE_HOME/perl/bin/perl catcon.pl –c 'SALESPDB' -n 1 -e -b postfixups -d '''.''' /tmp/cfgtoollogs/SALESPDB/preupgrade/postupgrade_fixups.sql
  7. Use the utility catcon.pl to run utlrp.sql from the $ORACLE_HOME/rdbms/admin directory:

    $ORACLE_HOME/perl/bin/perl catcon.pl –c 'SALESPDB'-n 1 -e -b comp -d '''.''' utlrp.sql

    The script recompiles INVALID objects in the database.

Use Inclusion or Exclusion Lists for PDB Upgrades

If you want to upgrade a subset of earlier release PDBs, then use inclusion or exclusion lists to avoid reupgrading the CDB or PDBs that are at the new release level.

Oracle recommends that you record the containers that you upgrade, and use inclusion or exclusion lists to exclude these containers from successive bulk upgrades. Excluding upgraded containers from successive bulk upgrades ensures that the upgrade only runs on PDBs that require the upgrade. Avoiding reupgrades minimizes the overall upgrade time, and avoids unnecessary unavailability.

For example: If you have installed Oracle Database 12c release 12.2.0.1, then the containers CDB$ROOT, PDB$SEED, and any other PDBs created when the CDB was created, are part of the new release multitenant architecture. If you upgraded a CDB, and at the same time upgraded a set of PDBs to release 12.2.0.1, then you do not need to upgrade either the CDB containers or the upgraded PDBs again.

In either case, when you plug in earlier release PDBs and then upgrade them, upgrade the PDBs with either an exclusion list, or an inclusion list:
  • Use an inclusion list to specify the only the set of PDBs that you want to upgrade

  • Use an exclusion list to exclude the CDB and PDB containers that are already upgraded

If you do not use an inclusion list or an exclusion list to limit the upgrade scope, then the Parallel Upgrade Utility (catctl.pl) attempts to upgrade the entire CDB, not just the PDBs that require the upgrade. During that upgrade process, your system undergoes needless downtime. The inclusion list and the exclusion list options are mutually exclusive.