8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Multitenant : PDB Refresh in Oracle Database 12c Release 2 (12.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 : Refresh

Related articles.

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, 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.

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.

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_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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.