Friday, 2 June 2017

Multitenant : PDB Refresh in Oracle Database 12c Release 2

From Oracle Database 12.2 onward 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.

Multitenant, Oracle PDB, Oracle Database 12c

  • Prerequisites


The prerequisites for a PDB refresh are similar to those of a hot remote clone, so you should be confident with that before continuing. You can read about it in this article.


In addition to the prerequisites for hot remote cloning, we must also consider the following.
  1. A refreshable PDB must be in a separate CDB to its source, so it must be a remote clone.
  2. You can change a refreshable PDB to a non-refreshable PDB, but not vice versa.
  3. 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.
  4. 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.
In the examples below I have two databases running on the same virtual machine, but they could be running on separate physical or virtual servers.
  1. cdb1 : The local database that will eventually house the refreshable clone.
  2. cdb3 : The remote CDB, used for the source PDB (pdb5).

  • Create a Refreshable PDB


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_CONVERT parameter 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 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.

  • Appendix


These tests were performed on a free trial of the Oracle Database Cloud Service, where the CDB1 instance and PDB1 pluggable database were created as part of the service creation. The additional instance was built on the same virtual machine using the commands below. I've included the DBCA commands to create and delete the CDB1 instance for completeness. They were not actually used.

# Empty local container (cdb1).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword OraPasswd1 \
 -systemPassword OraPasswd1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb1 \
 -pdbAdminPassword OraPasswd1 \
 -databaseType MULTIPURPOSE \
 -automaticMemoryManagement false \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u01/app/oracle/oradata/" \
 -redoLogFileSize 50 \
 -initParams encrypt_new_tablespaces=DDL \
 -emConfiguration NONE \
 -ignorePreReqs

# Remote container (cdb3) with PDB (pdb5).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb3 -sid cdb3 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword OraPasswd1 \
 -systemPassword OraPasswd1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb5 \
 -pdbAdminPassword OraPasswd1 \
 -databaseType MULTIPURPOSE \
 -automaticMemoryManagement false \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u01/app/oracle/oradata/" \
 -redoLogFileSize 50 \
 -initParams encrypt_new_tablespaces=DDL \
 -emConfiguration NONE \
 -ignorePreReqs


# Delete the instances.
#dbca -silent -deleteDatabase -sourceDB cdb1 -sysDBAUserName sys -sysDBAPassword OraPasswd1
dbca -silent -deleteDatabase -sourceDB cdb3 -sysDBAUserName sys -sysDBAPassword OraPasswd1

As explained earlier, in all cases Oracle Managed Files (OMF) was used so no file name conversions were needed. Also, the source databases were switched to archivelog mode.

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

sqlplus / as sysdba <<EOF

ALTER SYSTEM SET db_create_file_dest = '/u01/app/oracle/oradata';

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER PLUGGABLE DATABASE pdb5 OPEN;
ALTER PLUGGABLE DATABASE pdb5 SAVE STATE;

EXIT;
EOF