Refresh PDB in Oracle Database 12c Release 2

This article will guide you through the steps to refresh pdb in oracle database 12c release 2. It is possible to refresh the contents of a remotely hot cloned PDB provided it is created as a refreshable PDB and has only ever been opened in read only mode. The read-only PDB can be used for reporting purposes, or as the source for other clones, to minimise the impact on a production system when multiple up-to-date clones are required on a regular basis. 

Prerequisites

In this context, the word "local" refers to the destination or target CDB that will house the cloned PDB. The word "remote" refers to the PDB that is the source of the clone.

The prerequisites for a PDB refresh are similar to those of a hot remote clone. In addition to the prerequisites for hot remote cloning, you must also consider the following.

• A refreshable PDB must be in a separate CDB to its source, so it must be a remote clone.
• You can change a refreshable PDB to a non-refreshable PDB, but not vice versa.
• If the source PDB is not available over a DB link, the archived redo logs can be read from a location specified by the optional REMOTE_RECOVERY_FILE_DEST parameter.
• New datafiles added to the source PDB are automatically created on the destination PDB. The PDB_FILE_NAME_CONVERT parameter must be specified to allow the conversion to take place.

I have two databases running on the same virtual machine as an example for this guide, but they could be running on separate physical or virtual servers.

• cdb1 : The local database that will eventually house the refreshable clone.
• cdb3 : The remote CDB, used for the source PDB (pdb5).

Create a Refreshable PDB

 Remember, you must have completed all the preparations for a hot remote clone described in the linked article before going forward.

Connect to the local database to initiate the clone.

export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

Create a new PDB in the local database by cloning the remote PDB. In this case we are using Oracle Managed Files (OMF), so we don't need to bother with FILE_NAME_CONVERTparameter for file name conversions. In this case we are using manual refresh mode.

CREATE PLUGGABLE DATABASE pdb5_ro FROM pdb5@clone_link
  REFRESH MODE MANUAL;

Pluggable database created.

SQL>

We can see the new PDB has been created, but it is in the MOUNTED state.

COLUMN name FORMAT A30

SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5_RO';

NAME                           OPEN_MODE
------------------------------ ----------
PDB5_RO                        MOUNTED

SQL>

The PDB is opened in read-only mode to complete the process.

ALTER PLUGGABLE DATABASE pdb5_ro OPEN READ ONLY;

SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5_RO';

NAME                           OPEN_MODE
------------------------------ ----------
PDB5_RO                        READ ONLY

SQL>

Alter the Source PDB

We want to prove the new PDB can be refreshed, so we will add a new tablespace, user and table owned by that user in the source database.

Connect to the source database.

export ORAENV_ASK=NO
export ORACLE_SID=cdb3
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

Make some changes to the source PDB.

ALTER SESSION SET CONTAINER=pdb5;

CREATE TABLESPACE test_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test IDENTIFIED BY test
  DEFAULT TABLESPACE test_ts
  QUOTA UNLIMITED ON test_ts;

GRANT CREATE SESSION, CREATE TABLE TO test;

CREATE TABLE test.t1 (
  id NUMBER
);

INSERT INTO test.t1 VALUES (1);
COMMIT;

Refresh the PDB

The source PDB now differs from the clone, so we should be able to easily see if the clone can be refreshed.

Connect to the target database.

export ORAENV_ASK=NO
export ORACLE_SID=cdb1
. oraenv
export ORAENV_ASK=YES

sqlplus / as sysdba

Switch to the refreshable PDB and check for the presence of the test table. It will not exist yet.

ALTER SESSION SET CONTAINER=pdb5_ro;

SELECT * FROM test.t1;
SELECT * FROM test.t1
                   *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>

The refresh operation can only take place from the refreshable PDB, not the root container.

ALTER SESSION SET CONTAINER=pdb5_ro;

ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER SESSION SET PDB_FILE_NAME_CONVERT = 'CDB3', 'CDB1';
ALTER PLUGGABLE DATABASE REFRESH;
ALTER PLUGGABLE DATABASE OPEN READ ONLY;

Check for the presence of the test table again. It will now exist.

SELECT * FROM test.t1;

        ID
----------
         1

1 row selected.

SQL>

Notice the tablespace as also been created in the refreshable PDB.

SELECT tablespace_name
FROM   dba_tablespaces
ORDER BY 1;

TABLESPACE_NAME
------------------------------
SYSAUX
SYSTEM
TEMP
TEST_TS
UNDOTBS1
USERS

6 rows selected.

SQL>

Refresh Modes

In the example above we created a refreshable PDB using the manual refresh mode. Alternatively we could allow it to refresh automatically. The possible variations during creation are shown below.

-- Manual refresh mode.
CREATE PLUGGABLE DATABASE pdb5_ro FROM pdb5@clone_link
  REFRESH MODE MANUAL;

-- Automatically refresh ever 60 minutes.
CREATE PLUGGABLE DATABASE pdb5_ro FROM pdb5@clone_link
  REFRESH MODE EVERY 60 MINUTES;

-- Non-refreshable PDB.
-- These two are functionally equivalent.
CREATE PLUGGABLE DATABASE pdb5_ro FROM pdb5@clone_link
  REFRESH MODE NONE;

CREATE PLUGGABLE DATABASE pdb5_ro FROM pdb5@clone_link;

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 PDB1                           NONE
         4 PDB5_RO                        MANUAL

3 rows selected.

SQL>

The refresh mode can be altered after the refreshable PDB is created, as shown below.

-- Alter the refresh interval.
ALTER PLUGGABLE DATABASE pdb5_ro REFRESH MODE EVERY 60 MINUTES;
ALTER PLUGGABLE DATABASE pdb5_ro REFRESH MODE EVERY 120 MINUTES;

-- Set an automatically refreshed PDB to manual mode.
ALTER PLUGGABLE DATABASE pdb5_ro REFRESH MODE MANUAL;

-- Make a refreshable PDB non-refreshable.
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE pdb5_ro REFRESH MODE NONE;
ALTER PLUGGABLE DATABASE OPEN;

Remember, once the PDB is made non-refreshable, it can't be made refreshable again.