8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
Related articles.
- Multitenant : PDB Refresh
- Multitenant : All Articles
- Multitenant : Hot Clone a Remote PDB or Non-CDB in Oracle Database 12c Release 2 (12.2)
- Multitenant : Local Undo Mode in Oracle Database 12c Release 2 (12.2)
- Multitenant : YouTube Playlist
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : Refreshable PDB Switchover in Oracle Database 18c
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.
- 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.
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.
- cdb1 : The local CDB, that will eventually house the refreshable clone (pdb5_ro).
- 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_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:
- Multitenant : PDB Refresh
- Multitenant : All Articles
- Multitenant : Hot Clone a Remote PDB or Non-CDB in Oracle Database 12c Release 2 (12.2)
- Multitenant : Local Undo Mode in Oracle Database 12c Release 2 (12.2)
- PDB Refresh
- Refreshing a PDB
- ALTER PLUGGABLE DATABASE
- Multitenant : YouTube Playlist
- Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
- Multitenant : Refreshable PDB Switchover in Oracle Database 18c
Hope this helps. Regards Tim...