Minimal Downtime Patching using Refreshable PDB Clones

Introduction

Oracle Multitenant allows you to clone PDBs from one CDB to another easily, even though the source and target CDBs were of different patch levels, e.g. 19.9 and 19.10, and even different editions like moving from SE to EE. The only thing you’ll need is to run datapatch afterwards. Unplug and Plug the PDB will require more downtime needed to move the data files from source to target. Conventional PDB cloning would require stopping the application for the duration needed for the clone operation to complete, which also could take a while for large and busy databases.

Using PDB Refreshable Clones allows you to propagate the changes from source to target manually or automatically and keep the target up to date until “last minute” where you stop your application and perform the last refresh. PDB Refresh was first introduced in Oracle Database Release 12.2 and is available for SE and EE on-premises and in the Cloud. Have a look at the really cool Features and Licensing page. I know, “cool” and “licensing” in one sentence? I’ll make an exception this time.

The source and target CDBs could even be of different release versions, e,g. 18c and 19c. In this case we will need to upgrade the PDB afterwards instead of running datapatch. This will be the topic of the next blog.

The Environment

  • Source: EE CDB version 19c and Release Update (RU) 19.9
  • 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__199 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 (19.9):

SQL> create database link DBLINK_TO_19_9 connect to C##SYSOPER identified by SysOperPw__199 using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 130.61.33.156)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CDBROOT_fra1rr.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_19_9 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. The target PDB can be opened in READ ONLY mode if you want. To get the new changes from the source, it must be closed again.

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.

Open the target PDB:

SQL> alter pluggable database PDB002 refresh mode none;

Pluggable database altered.

SQL> alter pluggable database PDB002 open;

Warning: PDB altered with errors.

OH! Let’s check what is going on:

select type, cause, message from PDB_PLUG_IN_VIOLATIONS where name='PDB002' and status != 'RESOLVED';

This is ok, as we didn’t run datapatch yet.

4. Run Datapatch on the Target DB

Open the PDB in READ WRITE mode.

Run the utlrp.sql script before you invoke datapatch to clean up invalid objects:

SQL> alter session set container=PDB002;

Session altered.

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

PL/SQL procedure successfully completed.

Now run datapatch:

$ORACLE_HOME/OPatch/datapatch -verbose -pdbs PDB002

Validating logfiles...done
Patch 31465389 rollback (pdb PDB002): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31465389/23823398/31465389_rollback_CDBROOT_PDB002_2021Feb02_21_34_32.log (no errors)
Patch 32218454 apply (pdb PDB002): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/32218454/24018797/32218454_apply_CDBROOT_PDB002_2021Feb02_21_34_33.log (no errors)
Patch 31465389 apply (pdb PDB002): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31465389/23988611/31465389_apply_CDBROOT_PDB002_2021Feb02_21_35_28.log (no errors)
SQL Patching tool complete on Wed Feb  2 21:36:28 2021

To get rid of the errors in PDB_PLUG_IN_VIOLATIONS view, close and open the target PDB again:

SQL> alter pluggable database PDB002 close immediate;

Pluggable database altered.

SQL> alter pluggable database PDB002 open;

Pluggable database altered.

Great! Now the PDB opens without any issues. Check the PDB_PLUG_IN_VIOLATIONS view:

SQL> select type, cause, message from PDB_PLUG_IN_VIOLATIONS where name='PDB002' and status != 'RESOLVED';

As you don’t necessarily need to encrypt SYSTEM and SYSAUX tablespaces, you could ignore these warnings.

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

5. 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 allow you to reduce the downtime for patching down to very few minutes giving you the flexibility to patch 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. 19.9 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?