Minimal Downtime Migration and Upgrade using Refreshable PDB Clones

Introduction

Oracle Multitenant architecture makes it possible to effortlessly move PDBs from one CDB to another, even though the source and target CDBs were of different release versions, e.g. 18c and 19c, and even of different editions like moving from SE to EE. In the previous blog we discussed how patching can be done with minimal downtime by creating refreshable PDBs across CDBs with different patch levels. Similarly, in this blog post we are going to create the refreshable PDB across CDBs with different release versions, keeping them in sync until “last minute”, and finally upgrade the PDB on the target CDB to match the CDB’s release version.

The Environment

  • Source: EE CDB version 18c and Release Update (RU) 18.11
  • Target: EE CDB version 19c and Release Update (RU) 19.10

0. Prepare the Source CDB

Prepare the C##SYSOPER user In the source CDB:

SQL> grant create session, sysoper to C##SYSOPER identified by SysOperPw__1811 container=all;

Grant succeeded.

1. Create the Refreshable PDB on the Target CDB

Create a database link on the target CDB (19.10) pointing to the source CDB (18.11):

SQL> create database link DBLINK_TO_18_11 connect to C##SYSOPER identified by SysOperPw__1811 using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 130.61.117.238)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CDBROOT_fra1sr.sub012345.vcnfra2.oraclevcn.com)))';

Database link created.

Create the refreshable PDB clone with automatic refresh every one minute. You can choose the same PDB name on target, but for butter demonstration, I’ll choose PDB002 on target instead of PDB001:

SQL> create pluggable database PDB002 from PDB001@DBLINK_TO_18_11 keystore identified by "TDE_Wallet_Password" refresh mode every 1 minutes;

Pluggable database created.

SQL> select name, open_mode, restricted from v$pdbs where name='PDB002';

NAME                 OPEN_MODE            RESTRICTED
-------------------- -------------------- --------------------
PDB002               MOUNTED

SQL> select pdb_name, status, refresh_mode, refresh_interval from dba_pdbs where pdb_name='PDB002';

PDB_NAME             STATUS     REFRESH_MODE         REFRESH_INTERVAL
-------------------- ---------- -------------------- ----------------
PDB002               REFRESHING AUTO                                1

One minute is the minimal time that can be specified for automatic refresh.

The PDB is now in MOUNTED mode, REFRESHING state, and will be refreshed with changes from the source every one minute.

2. Close the Source PDB

Stop your application and close the source PDB to prevent any further changes:

SQL> alter pluggable database PDB001 close immediate;

Pluggable database altered.

The downtime begins.

3. Refresh the Target PDB

Now bring your target PDB up to date by executing a manual refresh to get the last changes from the source PDB:

SQL> alter pluggable database PDB002 refresh;

Pluggable database altered.

4. Upgrade the PDB

Open the target PDB for upgrade:

SQL> alter pluggable database PDB002 refresh mode none;

Pluggable database altered.

SQL> alter pluggable database PDB002 open upgrade;

Pluggable database altered.

SQL> select name, open_mode, restricted from v$pdbs where name='PDB002';

NAME                 OPEN_MODE            RESTRICTED
-------------------- -------------------- --------------------
PDB002               MIGRATE              YES

Upgrade the PDB:

$ORACLE_HOME/bin/dbupgrade -c "PDB002" -l /tmp

Grand Total Upgrade Time:    [0d:0h:21m:53s]

After the upgrades succeeds, the PDB is left in MOUNTED mode. Open the PDB:

SQL> alter pluggable database PDB002 open;

Pluggable database altered.

5. Post Upgrade Tasks

Recompile invalid objects using the utlrp.sql script:

SQL> alter session set container=PDB002;

Session altered.

SQL> @?/rdbms/admin/utlrp.sql

PL/SQL procedure successfully completed.

Run the postupgrade_fixups.sql script:

SQL> @/u01/app/oracle/cfgtoollogs/$ORACLE_SID/preupgrade/postupgrade_fixups.sql

Downtime ends. Check the DBA_REGISTRY_SQLPATCH view:

SQL> alter session set container=PDB002;

Session altered.

SQL> select patch_id, patch_type, status, description, action_time from dba_registry_sqlpatch order by action_time desc;

The PDB is now at release 19c and patch level 19.10.

In case something went wrong, you can just open your source PDB again and use it for production.

6. Additional Tasks

Re-key the new created PDB so that it has its own TDE master encryption key:

SQL> alter session set container=PDB002;

Session altered.

SQL> administer key management set key using tag 'new own key' force keystore identified by "TDE_Wallet_Password" with backup;

keystore altered.

Gather statistics:

SQL> exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'schema_name', estimate_percent=>dbms_stats.auto_sample_size, cascade=>true, degree=>4);

PL/SQL procedure successfully completed.

Conclusion

Refreshable PDB Clones provide flexibility to upgrade individual PDBs one by one with minimal downtime. As the source PDB remains open, you can test this scenario first without impacting your business continuity. The source PDB remains available for a fallback scenario if needed. The Source PDB could also be of different edition, e.g. 18.11 SE, while the target being of version 19.10 EE. You can use the same procedure to migrate a PDB from one platform to another, e.g. from on-premises to the cloud, to Oracle Cloud of course.

Further Reading

Would you like to get notified when the next post is published?