DBA Hub

📋Steps in this guide1/2

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

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Priority Lists

Let's assume this is the list of PDBs in the system. 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 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. 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. After the upgrade, the priority associated with each PDB is recorded and will be used automatically for future upgrades. This priority can be altered as follows. We can see this reflected in the output from the previous query.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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>

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

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

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>

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;

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>
2

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. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
# 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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!