Multitenant Part 3 : PDB Refresh

Multitenant Part 3 : PDB Refresh

The refreshable PDB at target will be in read only mode and 

The read-only PDB can be used for reporting purposes.

Later point of time, You can change a refreshable PDB to a non-refreshable PDB, but  remember not vice versa.

Now, let see how it works via this graphic,

From Prod CDB, we do clone of the PRODPDB1, once it done, at Dev site it should be in read only mode. 

Technology behind this refreshable PDB is, PDB hot clone only. Very first you need to do hot clone with refresh mode parameter in create pluggable command.

After that, whenever you need latest version of your production data, you just need to refresh it. And as additional step you can make new PDBs from your refreshable PDB

Step by Step to perform Refreshable PDB

Here I would like to share step by step commands to perform Refreshable PDB

Let’s define what could be our database name for Production and Development and CDB/PDB name.

Throughout our scenario I will follow below names to be performed.

Source Database Server (Production)

CDB Name : PRODCDB

PDB Name : PROPDB1

Target Database Server (Development)

CDB Name : DEVDCDB

PDB Name : DEVPDB1

Prerequisites of Scenario

You can follow same set of commands and replicate this in your environment.

These prerequisites are common for all our scenarios, like, hot clone, refreshable PDB, PDB relocation and snapshot carousel, other scenarios you can found in my blog or follow the link embedded above. 

Source Database Server

At source we need to configure database in archive log mode if not, it is mandatory to do it and local undo mode, so each PDB will have their own undo tablespace.

Database should be in Archive log mode and local undo mode.

Target Database Server

• Add tns entry of source database and create database link to connect to source database.
• There should be a common user in Source Database
• Grant privileges to common user i.e. create session and create pluggable database to all.

CREATE DATABASE LINK prod_link CONNECT TO c##clone_user IDENTIFIED BY  clone_user USING 'PROPDB1';

Once we are done with these prerequisites, only few commands need to run after that to complete the task.

Here, I am giving two example to create refreshable PDB 1) Manual 2) Automatic as below,

Example 1

In this example, database refresh mode is manual, it means that you can refresh database at your own peace whenever  you need. 

Create manual refreshable PDB

CREATE PLUGGABLE DATABASE PROPDB1_RF FROM PDB1@prod_link_pub REFRESH MODE MANUAL;

After creating refreshable PDB there may be some change happens to your main database and you want to refresh again to reflect it in your PDB so there are few steps to refresh it.

Manually Refresh your PROPDB1_RF from Source PROPDB1.

First, set your container to refreshable PDB

alter session set container=PROPDB1_RF;

Second, close your PDB

alter pluggable database PROPDB1_RF close immediate;

Third, refresh your current PDB

alter pluggable database PROPDB1_RF refresh;

Fourth, And finally, you can open it in read only mode.

alter pluggable database open read only;

Example 2

In this in second command refresh mode is every 10 min, so your refreshable PDB will be refresh automatically in every 10mins. 

CREATE PLUGGABLE DATABASE PROPDB1_RF FROM PDB1@prod_link REFRESH MODE EVERY 10 MINUTES;

Create clone from Refreshable PDB

Addition to that, you can create one or more PDBs from your refreshable PDB.

A refreshable clone PDB must be either close or read only mode. A refreshable PDB must be closed when a refresh is performed. 

In automatic refresh, If it is not closed when automatic refresh is attempted, then the refresh is deferred until the next scheduled refresh.

Command to create New PDB from Refreshable PDB

sql> create pluggable database PDB1CLONE from PROPDB1_RF;

sql> alter pluggable database PDB1CLONE open;

Refreshable PDB Switchover

In Oracle 12.2, Refreshable clone PDB was introduced.  In Oracle 18c, now it allows you to switch the roles of a source PDB and a refreshable PDB. This functionality is known as Refreshable PDB Switchover.

It means that you can make your refreshable PDB as primary one. And here is the command for the same.

alter pluggable database PROPDB1_RF refresh mode every 10 minutes from c##clone_user@prod_link_pub switchover;

I hope this article will help you to create refreshable PDB seamlessly.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert