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

Multitenant : Switchover

Prerequisites
Create a Refreshable PDB
Switchover Roles
Switchover Roles (Switchback)
Considerations
Appendix

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.

Multitenant : PDB Refresh in Oracle Database 12c Release 2 (12.2)

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:

Hope this helps. Regards Tim...

Back to the Top.