With the introduction of Oracle CDB-PDB architecture, DBA's had to get used to a whole way of thinking about databases. Databases became more flexible, more volatile in a sense that you can do many cool things with PDB's, easier upgrades, cloning etc...
With the release of Oracle 12.2 the cloning of PDB's got extended with a option to refresh a remote PDB with data from a local source PDB. The clone you created in a remote location can now be updated automatically with changes from its local partner (source) by applying redo that is retrieved over a database link.
This is called a Refreshable Clone PDB.
When thinking about these kind of options, DBA's may start to think of Dataguard, the Standby Database solution for Oracle Enterprise Editions.
The Refreshable Clone PDB is much of the same, but then free of any extra costs and very easy to setup and maintain. Even better, the Refreshable Clone PDB also works on Standard Edition, so an Enterprise Edition License is not needed here.
Of course the remote database server must be licensed correctly to run this database.
There are a number of advantages of having a Refreshable Clone PDB:
- When the Clone is on another server, you can use it as a Disaster Recovery solution. The RPO is then dependent on the refresh rate you specify when creating the remote clone.
RTO is depending on the amount of work needed, to change the role of the clone to a production status in case of a disaster. - When there is no disaster and when there is sufficient archivelog space on the source database, one can even open up the clone in a "read only" state for reporting purposes, thus unloading the source database. Keep in mind that the clone does not get updated when it is open and thus gets out of sync with its source. The redo can only be applied, when the clone is closed.
Opening a database for reporting like this, hugely increases RTO and RPO.
Some requirements before you can create a Refreshable Clone PDB:
- A CDB on the remote location must already exist having the same patch version and components as the source CDB
- A database link must be created from remote CDB, connecting to the local (source) CDB, using the SYSTEM account
- The SYSTEM account in the source CDB must have "CREATE PLUGGABLE DATABASE" rights. In Oracle 12.2, this was the case, however in 19c this right is by default only granted to SYS.
- The remote CDB must have the PDB_FILE_NAME_CONVERT parameter set in the spfile, if the directory structure of the clone does not match that of the source.
Example:
In this example we assume that we have two CDBs, a CDB1 as the source and a CDB2 as the remote location. The PDB in the source is called HR_PROD. It is up to you if you want to name the Clone PDB the same. For DR this may be convenient, as this eases a failover. When used for reporting the Clone PDB may get another name.
We assume that you have entered the correct connection string (named CDB1) in the tnsnames.ora on the remote server, pointing to the source server.
We start with creating the database link:
SQL> create database link TO_CDB1
connect to system identified by yourpassword
using 'CDB1';
Now we are creating the Refreshable Clone PDB:
SQL> alter system set
pdb_file_name_convert='/u01/oradata/hrprod','/u01/oradata/hrclone'
scope=both;
SQL> create pluggable database HR_CLONE from HR_PROD@TO_CDB1
refresh mode every 5 minutes;
That's it !
Checking the result shows:
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 HR_CLONE MOUNTED
Manual refresh:
SQL> alter pluggable database HR_CLONE refresh;
Change the refresh interval:
SQL> alter pluggable database HR_CLONE refresh mode manual;
or
SQL> alter pluggable database HR_CLONE refresh mode every 30 minutes;
Open the database:
SQL> alter pluggable database HR_CLONE open read only;
Thinking about this, it may be convenient to first put refresh mode to manual, before opening the database. This prevent you from getting lots of errors in the alert_log;
Switchover
Because a Refreshable Clone PDB is not a Dataguard solution, a switchover is something manual, to be performed by the DBA. With Dataguard, failovers can be automated with near zero loss of data.
When however doing a switchover correctly, the latest transactions are taken into account, also the switchover of a Cloned PDB must be possible with near zero dataloss.
We need to take into account that in the above database the refresh rate is set to 5 minutes. This means that every 5 minutes the source database is probed for changes. Whenever we do a switchover in between two such probes / refreshes, then we may loose changes applied in the source database during that interval.
First: Switchover can be done by the database by using an ALTER PLUGGABLE DATABASE .... SWITCHOVER command, but this feature is restricted to Enterprise Edition on Exadata and all editions on Oracle Cloud Services.
To overcome this for on-premise databases a hidden parameter can be set:
SQL> alter system set "_exadata_feature_on"=true scope=spfile;
A test using an on-premise Standard Edition runs into -yet- unresolvable errors, even when setting that parameter. It may be of the Standard Edition, but it may also be that the available documentation on the Internet is incomplete.
A manual switchover however is possible. Whenever we shutdown or loose the source database, we can disable the Refresh Mode on the Clone and open it in Read/Write mode to start using it.
This then looks like this:
SQL> alter pluggable database HR_CLONE refresh;
Pluggable database altered.
SQL> alter pluggable database HR_CLONE refresh mode none;
Pluggable database altered.
SQL> alter pluggable database HR_CLONE open;
Pluggable database altered.
After opening the PDB in Read/Write mode it can not be reverted to Refreshable Clone.
It has to be recreated.
