8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : Refreshable PDB Switchover in Oracle Database 18c
Oracle Database 12.2 introduced the concept of a refreshable clone PDB. Oracle 18c allows you to switch the roles of a source PDB and a refreshable PDB. This functionality is known as Refreshable PDB Switchover
- Prerequisites
- Create a Refreshable PDB
- Switchover Roles
- Switchover Roles (Switchback)
- Considerations
- Appendix
Related articles.
- Multitenant : Refreshable PDB Switchover
- Multitenant : All Articles
- Multitenant : PDB Refresh in Oracle Database 12c Release 2 (12.2)
Prerequisites
This feature is currently restricted to Enterprise Edition on Engineered Systems, like Exadata, but is available on all editions on Oracle Database Cloud Services, as described here. There is a workaround for testing by enabling the "_exadata_feature_on" initialisation parameter.
export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES sqlplus / as sysdba <<EOF alter system set "_exadata_feature_on"=true scope=spfile; shutdown immediate; startup; exit; EOF
It's assumed you understand the concepts of a refreshable PDB, as these will not be covered in this article. If you need to read up on those, check out this article.
Since we will be switching roles, we need to complete a few extra setup steps compared to a normal refreshable PDB.
- Both instances need to be in archivelog mode as we will be performing switchovers in both directions.
- There needs to be a database link in both directions, using the matching credentials.
- The documentation recommends the database link user should have some additional privileges compared to a regular refreshable PDB database link user.
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)
The commands to create these instances with all the necessary settings are covered in the appendix below.
Create a Refreshable PDB
Connect to the local database, then create a new PDB in the local database by cloning the remote PDB.
export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES sqlplus / as sysdba CREATE PLUGGABLE DATABASE pdb5_ro FROM pdb5@clone_link REFRESH MODE MANUAL; ALTER PLUGGABLE DATABASE pdb5_ro OPEN READ ONLY;
Switchover Roles
Check the refreshable PDB is open in read-only mode.
export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES sqlplus / as sysdba SELECT open_mode FROM v$pdbs WHERE name = 'PDB5_RO'; OPEN_MODE ---------- READ ONLY SQL>
Connect to the source database, switch the roles and open the PDB in read-only mode. We have to specify a valid refresh mode other than NONE. In this example we are using MANUAL.
export ORACLE_SID=cdb3 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES sqlplus / as sysdba ALTER SESSION SET CONTAINER = pdb5; ALTER PLUGGABLE DATABASE REFRESH MODE MANUAL FROM pdb5_ro@clone_link SWITCHOVER; ALTER PLUGGABLE DATABASE OPEN READ ONLY;
We can see the original source PDB is now a refreshable PDB.
SELECT status, refresh_mode FROM dba_pdbs WHERE pdb_name = 'PDB5'; STATUS REFRES ---------- ------ REFRESHING MANUAL SQL>
We can refresh this PDB in the usual way.
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE; ALTER PLUGGABLE DATABASE REFRESH; ALTER PLUGGABLE DATABASE OPEN READ ONLY;
Switchover Roles (Switchback)
We can perform another switchover to revert the PDBs to their original roles.
Connect to the new primary database, switch the roles and open the PDB in read-only mode.
export ORACLE_SID=cdb1 export ORAENV_ASK=NO . oraenv export ORAENV_ASK=YES sqlplus / as sysdba ALTER SESSION SET CONTAINER = pdb5_ro; ALTER PLUGGABLE DATABASE REFRESH MODE MANUAL FROM pdb5@clone_link SWITCHOVER; ALTER PLUGGABLE DATABASE OPEN READ ONLY;
We can see the PDB is once again refreshable.
SELECT status, refresh_mode FROM dba_pdbs WHERE pdb_name = 'PDB5_RO'; STATUS REFRES ---------- ------ REFRESHING MANUAL SQL>
Considerations
Remember, this is not Data Guard. There is a lag between the initiation and completion of the switchover, where transactions against the original primary database could be applied, and not synced with the read-only database before the roles are switched. As a result, you may lose those transactions. If this is a problem for you, you need to use Data Guard!
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, create database link and link user. 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'; alter system set "_exadata_feature_on"=true scope=spfile; create user c##remote_clone_user identified by remote_clone_user container=all; grant create session, resource, create any table, unlimited tablespace to c##remote_clone_user container=all; grant create pluggable database to c##remote_clone_user container=all; grant sysoper to c##remote_clone_user container=all; create database link clone_link connect to c##remote_clone_user identified by remote_clone_user using 'cdb3'; exit; EOF # Enable ARCHIVELOG mode. sqlplus / as sysdba <<EOF shutdown immediate; startup mount; alter database archivelog; alter database open; 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, create database link and link user. 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'; alter system set "_exadata_feature_on"=true scope=spfile; create user c##remote_clone_user identified by remote_clone_user container=all; grant create session, resource, create any table, unlimited tablespace to c##remote_clone_user container=all; grant create pluggable database to c##remote_clone_user container=all; grant sysoper to c##remote_clone_user container=all; create database link clone_link connect to c##remote_clone_user identified by remote_clone_user using 'cdb1'; 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:
- Switching Over a Refreshable Clone PDB
- Multitenant : Refreshable PDB Switchover
- Multitenant : All Articles
- Multitenant : PDB Refresh in Oracle Database 12c Release 2 (12.2)
Hope this helps. Regards Tim...