8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | 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 we 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>


-- 18c only
SELECT last_refresh_scn
FROM   dba_pdbs
WHERE  pdb_name = 'PDB5_RO';

LAST_REFRESH_SCN
----------------
         1555382

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>

In 12.2 the refresh operation can only take place from the refreshable PDB, not the root container. This restriction was removed in later releases, but the documentation still says you must be connected to the PDB. The example below shows both possible approaches, but only the second will work in 12.2.

-- Post 12.2 : From root container.
CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb5_ro CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE pdb5_ro REFRESH;
ALTER PLUGGABLE DATABASE pdb5_ro OPEN READ ONLY;

-- 12.2 : From refreshable PDB.
ALTER SESSION SET CONTAINER=pdb5_ro;

ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE REFRESH;
ALTER PLUGGABLE DATABASE OPEN READ ONLY;


-- 18c only
SELECT last_refresh_scn
FROM   dba_pdbs
WHERE  pdb_name = 'PDB5_RO';

LAST_REFRESH_SCN
----------------
         1557699

SQL>

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

ALTER SESSION SET CONTAINER=pdb5_ro;

SELECT * FROM test.t1;

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

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

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
---------- ------------------------------ ------ ----------------
         3 PDB5_RO                        MANUAL

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.

The auto-refresh is only possible if the PDB is closed. This sounds odd, but the auto-refresh is meant to be used when the refreshable PDB is a source of a chain of clones. As such, you would open it read-only, use it as the source of a new clone, then close it again, so it can stay up to date. If you want the PDB to be open read-only most of the time, it's better to define your own job that closes, refreshes and opens the PDB again.

Appendix

The two container database instances were built using the following commands. The commands to remove the instances are included, so you can clean up when you are finished.

# Create FRA location.
mkdir -p /u01/app/oracle/fast_recovery_area

# Empty container (cdb1).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb1 -sid cdb1 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 0 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

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

# Set required parameters and database link.
sqlplus / as sysdba <<EOF
alter system set db_create_file_dest = '/u02/oradata';
alter system set remote_recovery_file_dest = '/u01/app/oracle/fast_recovery_area';

create database link clone_link
  connect to c##remote_clone_user identified by remote_clone_user using 'cdb3';

exit;
EOF



# Container (cdb3) with PDB (pdb5).
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb3 -sid cdb3 -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword SysPassword1 \
 -systemPassword SysPassword1 \
 -createAsContainerDatabase true \
 -numberOfPDBs 1 \
 -pdbName pdb5 \
 -pdbAdminPassword SysPassword1 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/u02/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

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

# Set required parameters and create user for DB link.
sqlplus / as sysdba <<EOF
alter pluggable database pdb5 save state;
alter system set db_create_file_dest = '/u02/oradata';
alter system set db_recovery_file_dest_size = 10G;
alter system set db_recovery_file_dest = '/u01/app/oracle/fast_recovery_area';

create user c##remote_clone_user identified by remote_clone_user container=all;
grant create session, create pluggable database to c##remote_clone_user container=all;

exit;
EOF

# Enable ARCHIVELOG mode.
sqlplus / as sysdba <<EOF
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
exit;
EOF


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

For more information see:

Hope this helps. Regards Tim...

Back to the Top.