Refreshable PDB in Oracle Database 12.2



Refreshable PDB is a new features introduced in 12.2 . It builds upon hot clone capability in Oracle Database 12.2.

It basically means we can refresh the remote PDB from source PDB on regular intervals by just applying the incremental redo

This feature eliminates the need to re-create a cloned environment from scratch and also ensures that a cloned PDB can be periodically synchronized with source changes. This feature significantly reduces the time to provision a cloned copy of PDBs

The intended use case of Refreshable PDB is as a “golden master” database. That is, a database from which individual developers take clones – typically, and most efficiently snapshot clones (which can be created in seconds or minutes even for very large databases).

We can create a copy of a production PDB in the test Datacenter and Then use snaphot technology to create the environment for Development and testing.

How to create Refreshable PDB

To create a PDB for automatic refresh,
create pluggable database <PDB name> from Source_PDB@DB_Link refresh mode every 360; — (360 minutes)

The Refreshable PDB should then be opened read-only as follows:
alter pluggable database <PDB name> read only;

For manual refresh, the syntax is
create pluggable database Golden_Master_PDB from Prod_PDB@DBLink refresh mode manual;

Once the initial clone is complete, the database is available for use.

Important Points
1) As the clone becomes stale, we can refresh it. We do this by applying all the redo that’s accumulated since it was last refreshed. Even if the source database is enormous, the incremental redo will typically be much smaller. It’s therefore going to be a much, much quicker process than the initial hot clone. Therefore, it’s going to be much simpler to keep production clones refreshed with recent data copied from production.

2) Refreshable PDBs may be defined to be refreshed either automatically (on a specific schedule) or manually (refreshed on-demand). It is also possible to perform an on-demand refresh of a PDB defined to be refreshed automatically. A PDB defined to be automatically refreshed can be transitioned to being manually refreshed, and vice versa.

How to manual refresh the Refreshable PDB

1) Close the PDB

alter pluggable database <pdb name> close immediate;

Pluggable database altered.

2) Now connect to the pluggable database to be refreshed

alter pluggable database <pdb name> refresh;

You cannot refresh the PDB while login to another PDB

SQL> alter pluggable database <pdb name> refresh;
alter pluggable database pdb2 refresh
*
ERROR at line 1:
ORA-65118: operation affecting a pluggable database cannot be performed from
another pluggable database

3) Now open in read only mode

alter pluggable database <pdb name> open read only;

Nice things with Refreshable PDB

You cannot read -write open the Refreshable PDB by normal command.It will give error

SQL> alter pluggable database <pdb name> open;
alter pluggable database pdb2 open
*
ERROR at line 1:
ORA-65341: cannot open pluggable database in read/write mode

So then to how to convert into non-refreshable mode

How to Make a refreshable PDB non-refreshable.
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE <pdb name> REFRESH MODE NONE;
ALTER PLUGGABLE DATABASE OPEN;

Important Note, once the PDB is made non-refreshable, it can’t be made refreshable again.

More Stuff

The current refresh mode can be queried using the DBA_PDBS view.

COLUMN pdb_name FORMAT A30

SELECT pdb_id, pdb_name, refresh_mode, refresh_interval
FROM dba_pdbs
ORDER BY 1;

PDB_ID PDB_NAME REFRES REFRESH_INTERVAL


2 PDB$SEED NONE
3 TECH_DB NONE
4 TECH_DB_RE MANUAL

Related Articles

Local Undo in Oracle Database 12c R2(12.2)

Oracle Database Interactive Quick Reference Guide | 11g & 12c

Oracle database 12c:How to create Pluggable database in 12c database

Oracle Flex Cluster 12c

Very useful 10 new things in 12c database

Top Oracle 12c New Features for developers


Leave a Reply