Multitenant pluggable database upgrade

Preamble

Whether you like it or not Multitenant is the way to go ! Starting with 19c the option has been made free for 3 pluggable databases (PDBs) per container database (CDB):

And in 21c the non-CDB architecture is desupported (not deprecated), this is clearly stated in upgrade guide.

For my testing I have created an empty pluggable database with my own admin account using:

SQL> CREATE pluggable DATABASE pdb1
     admin USER yjaquier IDENTIFIED BY "secure_password"
     roles = (dba)
     DEFAULT TABLESPACE users datafile '/u01/app/oracle/oradata/ORCL1/pdb1/users01.dbf' SIZE 100m
     file_name_convert = ('/u01/app/oracle/oradata/ORCL1/pdbseed','/u01/app/oracle/oradata/ORCL1/pdb1')
     path_prefix = '/u01/app/oracle/oradata/ORCL1/pdb1';
 
Pluggable DATABASE created.
 
SQL> ALTER pluggable DATABASE pdb1 OPEN;
 
Pluggable DATABASE altered.
 
SQL> !ls -l /u01/app/oracle/oradata/ORCL1/pdb1
total 850040
-rw-r----- 1 oracle dba 356524032 Jul 12 15:13 sysaux01.dbf
-rw-r----- 1 oracle dba 304095232 Jul 12 15:13 system01.dbf
-rw-r----- 1 oracle dba 104865792 Jul 12 15:13 temp01.dbf
-rw-r----- 1 oracle dba 104865792 Jul 12 15:13 undotbs01.dbf
-rw-r----- 1 oracle dba 104865792 Jul 12 15:13 users01.dbf
 
SQL> ALTER pluggable DATABASE pdb1 save state;
 
Pluggable DATABASE altered.
 
SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

In the local PDB account I create a test table to demonstrate figures copy:

SQL> CREATE TABLE test01(id NUMBER, descr VARCHAR2(50));
 
TABLE created.
 
SQL> ALTER USER yjaquier quota unlimited ON users;
 
USER altered.
 
SQL> INSERT INTO test01 VALUES (1, 'One');
 
1 ROW created.
 
SQL> INSERT INTO test01 VALUES (2, 'Two');
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.

For proxy and remote cloning I have also created a global PDB admin account:

SQL> CREATE USER c##pdbadmin IDENTIFIED BY "secure_password";
 
USER created.
 
SQL> GRANT CREATE SESSION, sysoper, CREATE pluggable DATABASE TO c##pdbadmin container=ALL;
 
GRANT succeeded.

Remark:
If your PDB$SEED tempfile has a strange name use alter tablespace temp add tempfile and alter tablespace temp drop tempfile to adjust it.

The purpose of those blog posts is to experiment rapid provisioning of production database copy and potential automated refresh of live production database. I will also experiment what is called application containers to provision instant fresh application installation for POC or training purposes.

This first part is about pluggable database upgrade as well as features that required special Oracle engineered system or special feature.

Testing has been done on Red Hat Enterprise Linux Server release 7.9 (Maipo) using Oracle Enterprise Edition 19c (19.15) and Oracle Enterprise Edition 21c (21.6) for the upgrade part.

Pluggable database upgrade from 19c (19.15) to 21c (21.6)

First things first let’s migrate a PDB from 19c to 21c. I will do it on my unique test machine but outside the physical copy it is exactly same process between different nodes (if operating system are different with different endian then it is more complex with a datafiles conversion). The now recommended method for database upgrade (whatever upgrade you do) is autoupgrade, it used to be DBUA but Mike and his team has done a hard job on this new tool. You can find tons of article about it on Mike’s blog (https://mikedietrichde.com/). The MOS Document 2485457.1 provide the latest release of this Java tool.

I start by creating a sample config file:

[oracle@server01 ~]$ java -jar autoupgrade.jar -create_sample_file config
Created sample configuration file /home/oracle/sample_config.cfg

That I have customized for my PDB1 pluggable database. The decision I have taken is to copy the datafiles from the non-OMF location to the new non-OMF location (datafiles copy):

[oracle@server01 ~]$ grep -v ^# autoupgrade.cfg
global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
upg2.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade/orcl1
upg2.sid=orcl1
upg2.source_home=/u01/app/oracle/product/19.0.0/dbhome_1
upg2.target_cdb=orcl2
upg2.target_home=/u01/app/oracle/product/21.0.0/dbhome_1
upg2.pdbs=pdb1
upg2.target_pdb_name.pdb1=upgpdb1
upg2.target_pdb_copy_option.pdb1=file_name_convert=('/u01/app/oracle/oradata/ORCL1/pdb1', '/u01/app/oracle/oradata/ORCL2/pdb1_upg')
upg2.run_utlrp=yes
upg2.timezone_upg=yes

Remark:
DO NOT PUT an ending slash at the end f your Oracle home or the tool will not be able to connect to your databases !!

Check if the upgrade is possible with:

[oracle@server01 ~]$ java -jar autoupgrade.jar -config autoupgrade.cfg -mode analyze
AutoUpgrade is not fully tested on OpenJDK 64-Bit Server VM, Oracle recommends to use Java HotSpot(TM)
AutoUpgrade 22.3.220503 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 PDB(s) will be analyzed
Type 'help' to list console commands
upg> Job 100 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]
 
Jobs finished                  [1]
Jobs failed                    [0]
 
Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log

Review the log file for potential actions to perform before the upgrade and to understand what will be done while and after the upgrade automatically by the tool.

I upgrade on the same server so (refer to documentation if upgrading to a different server):

[oracle@server01 ~]$ java -jar autoupgrade.jar -config autoupgrade.cfg -mode deploy
AutoUpgrade is not fully tested on OpenJDK 64-Bit Server VM, Oracle recommends to use Java HotSpot(TM)
AutoUpgrade 22.3.220503 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 PDB(s) will be processed
Type 'help' to list console commands
upg> lsj
+----+-------+---------+---------+-------+----------+-------+----------------------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|                     MESSAGE|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
| 102|  orcl1|PRECHECKS|EXECUTING|RUNNING|  17:21:16| 0s ago|Loading database information|
+----+-------+---------+---------+-------+----------+-------+----------------------------+
Total jobs 1
 
upg> lsj
+----+-------+-----+---------+-------+----------+-------+-------------------+
|Job#|DB_NAME|STAGE|OPERATION| STATUS|START_TIME|UPDATED|            MESSAGE|
+----+-------+-----+---------+-------+----------+-------+-------------------+
| 102|  orcl1|DRAIN|EXECUTING|RUNNING|  17:21:16| 0s ago|Compatibility check|
+----+-------+-----+---------+-------+----------+-------+-------------------+
Total jobs 1
 
upg> lsj
+----+-------+---------+---------+-------+----------+-------+------------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|           MESSAGE|
+----+-------+---------+---------+-------+----------+-------+------------------+
| 102|  orcl1|DBUPGRADE|EXECUTING|RUNNING|  17:21:16|60s ago|0%Upgraded UPGPDB1|
+----+-------+---------+---------+-------+----------+-------+------------------+
Total jobs 1
 
upg> lsj
+----+-------+---------+---------+-------+----------+-------+-------------------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|            MESSAGE|
+----+-------+---------+---------+-------+----------+-------+-------------------+
| 102|  orcl1|DBUPGRADE|EXECUTING|RUNNING|  17:21:16|14s ago|52%Upgraded UPGPDB1|
+----+-------+---------+---------+-------+----------+-------+-------------------+
Total jobs 1
 
upg> lsj
+----+-------+----------+---------+-------+----------+-------+-------+
|Job#|DB_NAME|     STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE|
+----+-------+----------+---------+-------+----------+-------+-------+
| 102|  orcl1|POSTFIXUPS|EXECUTING|RUNNING|  17:21:16|26s ago|       |
+----+-------+----------+---------+-------+----------+-------+-------+
Total jobs 1
 
upg> Job 102 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]
 
Jobs finished                  [1]
Jobs failed                    [0]
Jobs restored                  [0]
Jobs pending                   [0]
 
 
 
Please check the summary report at:
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html
/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log

Check all the logs to ensure you have no error to resolve…

And magically on my 21c CDB instance (the one on my 19c CDB has gone):

SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 UPGPDB1                        READ WRITE NO

Cleaning:

SQL> DROP pluggable DATABASE upgpdb1 including datafiles;
DROP pluggable DATABASE upgpdb1 including datafiles
*
ERROR AT line 1:
ORA-65025: Pluggable DATABASE UPGPDB1 IS NOT closed ON ALL instances.
 
 
SQL> ALTER pluggable DATABASE upgpdb1 CLOSE IMMEDIATE;
 
Pluggable DATABASE altered.
 
SQL> DROP pluggable DATABASE upgpdb1 including datafiles;
 
Pluggable DATABASE dropped.

Creating a split mirror clone pluggable database

This feature is inherited from ASM and has been extended directly at PDB level. But you need to rely on ASM, which is not my case and will be the case for really few customers not running RAC:

SQL> ALTER pluggable DATABASE prepare mirror copy pdb1_mirror01;
ALTER pluggable DATABASE prepare mirror copy pdb1_mirror01
*
ERROR AT line 1:
ORA-15120: ASM FILE name '/u01/app/oracle/oradata/ORCL2/pdb1/system01.dbf' does
NOT BEGIN WITH the ASM prefix character

Cloning a pluggable database from a pluggable snapshot

A PDB snapshot is a point-in-time copy of a PDB and a PDB snapshot carousel is a collection of PDB snapshots. I start by taking a snapshot of one of my PDB:

SQL> show pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
 
SQL> ALTER SESSION SET container=pdb1;
 
SESSION altered.
 
SQL> ALTER pluggable DATABASE snapshot pdb1_snap01;
ALTER pluggable DATABASE snapshot pdb1_snap01
*
ERROR AT line 1:
ORA-12754: Feature PDB SNAPSHOT CAROUSEL IS disabled due TO missing capability
.

As we can see on the extract from the licensing guide the feature to create a pluggable database from snapshot and the associated snapshot carousel feature is not available on Enterprise Edition (EE column). You need to be on Engineered Systems (EE-ES column) or in the Cloud to be able to use them.

References

More information on AutoUpgrade’s TARGET_PDB_COPY_OPTION

IT World

Multitenant pluggable database upgrade – part 1

Table of contents

Preamble

Pluggable database upgrade from 19c (19.15) to 21c (21.6)

Creating a split mirror clone pluggable database

Cloning a pluggable database from a pluggable snapshot

References

About Post Author

Yannick Jaquier

Related Posts:

Leave a Reply Cancel Reply