Oracle 12.2 Standby Database for Standard Edition Two

High Availability for Standard Edition Two

It’s a nasty discussion whenver it comes to Desaster Recovery or High Availability with Standard Edition Two. Even though RAC is available there is no real Desaster Recovery as Oracle does not allow to use Data Guard. So the only real alternative today is dbvisit Standby.

… and than Oracle released Multitenant Database!

Refreshable Pluggable Database

Starting with Release 12.2 there are some very useful enhancements to the Multitenant Database architecture. Especially the local undo management which we will cover in a different blog. But during the DOAG Database Conference in May 2017 I had some discussions with colleagues from other companies and one said: “Johannes, as you are an SE2 fan and have a deep knowledge on Multitenant have you already tried to use Refreshable Pluggable Database as a Standby Database solution for SE2?”

While I was testing that functionality I received a tweed about a blog from Franck Pachot, where he was describing “poor-man standby“. Thanks to Franck für this great blog it was really helpful.

A refreshable pluggable database is a copy of an exisiting PDB which will be refreshed automatically (time based) or on demand. For the refresh the PDB must be closed (like Data Guard physical Standby) but it’s allowed to open the Pluggable database read only. If the PDB is being opened read write the refresh mechanism is being destroyed and has to be recreated entirely.

But that has been the case some years ago for the Standby Database as well.

Automatic Pluggable Database Refresh

You should hopefully be familiar on how to set up a CDB and / or a PDB. With this my environment looks like follow: CDB “UDO” with the PDB “CELLO” on server “lindenberg” acts as the source and CDB “HERBERT” on server “groenemeyer” (without any PDB) is the target.

If you want to clone a PDB from an existing in a different CDB you need to use database links. In our case I would recommend to create database links and a dedicated common user in both databases as you might want to failover and use the same scenario in the other direction.

SQL> CREATE USER c##johannes IDENTIFIED BY manager CONTAINER=ALL;
SQL> GRANT connect, sysoper, dba TO c##johannes CONTAINER=ALL;

Source (UDO)

SQL> CREATE DATABASE LINK herbert CONNNECT TO c##johannes IDENTIFIED BY manager USING 'HERBERT';

Target (HERBERT)

SQL> CREATE DATABASE LINK udo CONNNECT TO c##johannes IDENTIFIED BY manager USING 'UDO';

With this I’m now able to create a new PDB on “HERBERT”. I’m using the same PDB name (CELLO) to clarify that it is a 100% copy:

SQL> CREATE PLUGGABLE DATABASE cello FROM cello@UDO
     file_name_convert=('UDO','HERBERT')
     REFRESH MODE EVERY 1 MINUTE;

That’s it!

With this command the PDB will be created and as long as I’m not opening it refreshed every minute. If you open the PDB read only the apply process will stop and restarted when the PDB is closed again.

There is surely a lag of experience with this feature like the real gap in case of a failover and the overhead on the source PDB of course. But it looks like a very interesting alternative and I would appreaciate if you share your experience with me.

Important Hint:

Please ensure that you do not create a second PDB on the target CDB as you will probably violate the license aggrement for Standard Edition Two.