8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Multitenant : PDB Upgrades Using Priority Lists, Inclusion Lists and Exclusion Lists in Oracle Database 12c Release 2 (12.2)

From Oracle 12.2 onward it's possible adjust the priority in which PDBs are upgraded when running the 'catupgrd.sql' script with the parallel upgrade utility (catctl). It's also possible to combine them with inclusion and exclusion lists.

Priority Lists
Inclusion and Exclusion Lists

Priority Lists

Let's assume this is the list of PDBs in the system.

COLUMN pdb_name FORMAT A30

SELECT pdb_id,
       pdb_name
FROM   cdb_pdbs
ORDER BY 1;

    PDB_ID PDB_NAME
---------- ------------------------------
         2 PDB$SEED
         3 PDB1
         4 PDB2
         5 PDB3
         6 PDB4
         7 PDB5
         8 PDB6

SQL>

A priority list is just a file in a specific format, listing containers along with their priority from 1-N. The root and seed containers are always priority 1, but you can add extra pluggable databases to the priority 1 group also. There are three formats for the file. In each case the line begins with the priority number, then one or more PDBs listed by name or PDB_ID number. Any PDBs not listed in the file are upgraded once those in the priority list are complete. The following three formats all result in the same priority list.

1,CDB$ROOT
1,PDB$SEED
1,PDB1
1,PDB2
2,PDB3
2,PDB4
3,PDB5
3,PDB6

1,CDB$ROOT
1,PDB$SEED
1,PDB1,PDB2
2,PDB3,PDB4
3,PDB5,PDB6

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

Assuming a file called "/tmp/priority.lst" existed with the contents matching one of the above, we might call use it as part of an upgrade process as follows.

cd $ORACLE_HOME/rdbms/admin 
$ORACLE_HOME/perl/bin/perl catctl.pl -L /tmp/priority.lst -l /tmp catupgrd.sql

After the upgrade, the priority associated with each PDB is recorded and will be used automatically for future upgrades.

COLUMN pdb_name FORMAT A30

SELECT pdb_id,
       pdb_name,
       upgrade_priority
FROM   cdb_pdbs
ORDER BY 1;

    PDB_ID PDB_NAME                       UPGRADE_PRIORITY
---------- ------------------------------ ----------------
         2 PDB$SEED                                      1
         3 PDB1                                          1
         4 PDB2                                          1
         5 PDB3                                          2
         6 PDB4                                          2
         7 PDB5                                          3
         8 PDB6                                          3

SQL>

This priority can be altered as follows.

ALTER PLUGGABLE DATABASE pdb1 UPGRADE PRIORITY 1;
ALTER PLUGGABLE DATABASE pdb2 UPGRADE PRIORITY 2;
ALTER PLUGGABLE DATABASE pdb3 UPGRADE PRIORITY 2;
ALTER PLUGGABLE DATABASE pdb4 UPGRADE PRIORITY 3;
ALTER PLUGGABLE DATABASE pdb5 UPGRADE PRIORITY 3;
ALTER PLUGGABLE DATABASE pdb6 UPGRADE PRIORITY 3;

We can see this reflected in the output from the previous query.

COLUMN pdb_name FORMAT A30

SELECT pdb_id,
       pdb_name,
       upgrade_priority
FROM   cdb_pdbs
ORDER BY 1;

    PDB_ID PDB_NAME                       UPGRADE_PRIORITY
---------- ------------------------------ ----------------
         2 PDB$SEED                                      1
         3 PDB1                                          1
         4 PDB2                                          1
         5 PDB3                                          2
         6 PDB4                                          2
         7 PDB5                                          3
         8 PDB6                                          3

SQL>

Inclusion and Exclusion Lists

Since it was introduced in Oracle 12.1 the "catcon.pl" script has always allowed you to use inclusion and exclusion lists, as explained here.

From Oracle 12.2 onward the "catctl.pl" script can do something similar with respect to database upgrades. It can also be combined with the priority lists.

# Upgrade only root, seed and PDB1.
cd $ORACLE_HOME/rdbms/admin 
$ORACLE_HOME/perl/bin/perl catctl.pl -L /tmp/priority.lst -c "CDB$ROOT PDB$SEED PDB1" -l /tmp catupgrd.sql

# Upgrade all but PDB1 and PDB6.
cd $ORACLE_HOME/rdbms/admin 
$ORACLE_HOME/perl/bin/perl catctl.pl -L /tmp/priority.lst -C "PDB1 PDB6" -l /tmp catupgrd.sql

# Upgrade only those PDBs in the priority list file.
cd $ORACLE_HOME/rdbms/admin 
$ORACLE_HOME/perl/bin/perl catctl.pl -L /tmp/priority.lst -C 'CATCTL_LISTONLY' -l /tmp catupgrd.sql

For more information see:

Hope this helps. Regards Tim...

Back to the Top.