Introduction
Refreshable PDB is a new feature introduced in Oracle database from 12.2 onwards i.e 12.2/18c/19c . 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 Data-center and Then use snapshot 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.
Queries Related to Refreshable PDB
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) : How to configure local UNDO ,how to disable it, how it impacts cloning,flashback, relocation
Oracle Database Interactive Quick Reference Guide | 11g & 12c : Quick Reference Guide
How to create Pluggable database in 12c database :How to create Pluggable database in 12c database, Create a pluggable database by duplicating an existing one. ,Create a pluggable database using the seed container.
Oracle Flex Cluster 12c :Oracle Flex cluster 12c is a new Oracle Clusterware based High Availability clustering topology.it has hub nodes and leaf nodes
Very useful 10 new things in 12c database
Top Oracle 12c New Features for developers :This post has the compilation of the top Oracle 12c New Features for developers with brief description and usage notes to help
Recommended Courses
The following are some of the recommended courses you can buy if you want to get a step further
Given below are the links to some of the courses
Oracle DBA 11g/12c – Database Administration for Junior DBA : This course is good for the people who are starting as Junior DBA or aspire to be Oracle DBA. This will provide a good understanding of backup & recovery and General administration tasks
Oracle Database: Oracle 12C R2 RAC Administration : This course covers the installation, administration of Oracle RAC. A good course for Oracle DBA who want to upgrade his skills for Oracle RAC
Oracle Data Guard: Database Administration for Oracle 12C R2 : This course covers the installation, administration of Oracle Dataguard. A good course for Oracle DBA who want to upgrade his skills for Oracle Dataguard
