Oracle Refreshable Pluggable Database

With the release of version 12.2, Oracle has introduced the Refreshable Pluggable Database (PDB) option. With this option it is possible to make a physical copy of a Pluggable Database in another CDB (Container) and update it over a database link. This option is available in all versions of the Oracle software, so both in Oracle EE and SE2, and both on-prem and in the Cloud. It should not be seen as a replacement for DataGuard so when EE licenses are available, DBA.nl will prefer to use DataGuard. For that reason, this option will mainly be of interest in environments licensed through SE2 licenses.

Setup

• Step 1, is to create a Pluggable database in CDB1 on server 1.
• In step 2, a copy of the PDB is made in CDB2 on server 2 through a database link. By specifying the Refresh Mode, this database is updated every 2 minutes based on the available redo information from the source PDB.

From Oracle 19c it is also possible to place multiple PDBs (Max 3) in a CDB without the Multitenant option. It is also possible to cross-create and update PDBs Refreshable PDBs using the same principle. See below:

The refresh mode indicates that the standby database is refreshed every x minutes (in this example every 2 minutes) and therefore in principle lags a maximum of 2 minutes. The Refreshable PDB can also be temporarily opened as read-only and if you close it, the refresh will continue.

CDB> alter pluggable database PDBORA2 open read only;
CDB> alter pluggable database close;

A Refreshable PDB is updated based on redo information. Unlike DataGuard, a Refreshable PDB uses a temporary archivelog (on the source) that is created on the primary database in the FRA. When the redo stream from this archivelog is applied to the Refreshable PDB, this temporary archivelog is removed again. A directory foreign_archivelog is created in the FRA in which the temporary archivelog is created. This is also reflected in the alert logs of both databases:

— CDB1 => Primary
2020-03-26T16:22:24.030666 +01:00
Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/ CDB1 /foreign_archivelog/RED/2020_03_26/o1_mf_ 1 _ 13 _1315987322_.arc

— CDB2 => Stand-by
RED(3):Media Recovery Start
2020-03-26T16:22:23.500067+01:00
RED(3): Serial Media Recovery started
RED(3):max_pdb is 3
2020-03-26T16:22:23.551406+01:00
RED(3):Media Recovery Log /u01/app/oracle/fast_recovery_area/ CDB1 /foreign_archivelog/RED/2020_03_26/o1_mf_ 1 _ 13 _1315987322_.arc
2020-03-26T16:22:23.860669+01:00
RED(3):Incomplete Recovery applied until change 1123625 time 03/26/2020 16:22:22
2020-03-26T16:22:23.866060+01:00
RED(3):Media Recovery Complete (CDB2)
RED(3):Completed: alter pluggable database refresh

On both the primary and the standby, no files remain permanently that need to be acted upon, as is the case with, for example, a scripted standby.

Activate Standby
When the source database is no longer available, the standby database can be activated so that it can be used. The RPO (Recovery Point Objective = the maximum data loss) depends on the interval at which the Refreshable PDB is configured and when the source database was lost. The RTO (Recovery Time Objective = how long it takes for the database to be available again) depends on:

• The amount of redo generation in the source database.
• The frequency of the refresh.

In the worst case, the source database will be lost just before the next refresh when a lot of redo was generated in the source PDB. In that case, the standby PDB must first be updated until the last moment before it is opened in read-write mode. A condition for this is that the Container of the source PDB is available. Although the source PDB is not available, the standby PDB can still be updated based on the available online redologs (CDB level) and any archive logs before opening this read-write. The example below assumes two Containers (CDB1 and CDB2). A DB-Link has been created from both Containers to the other CDB.

— Create PDB RED in CDB1
CDB1> CREATE PLUGGABLE DATABASE RED
ADMIN USER PDBADM IDENTIFIED BY COMPLEXPASSWORD
CREATE_FILE_DEST=’/u01/app/oracle/oradata’;
CDB1> alter pluggable database RED open read write;

— Create Refreshable PDB RED in CDB2
CDB2> create pluggable database RED from RED@CDB1
CREATE_FILE_DEST=’/u01/app/oracle/oradata’
refresh mode every 2 minutes;

Setting the refresh mode to x minutes will create a scheduler_job in the CDB that initiates the refresh every x minutes. Such as:

CDB2> select job_action from dba_scheduler_jobs where job_name = ‘RED_2733630503_REFRESH’;
declare
cur integer := sys.dbms_sql.open_cursor(security_level => 2);
get started
sys.dbms_sql.parse( c †> cur, statement †> ‘alter pluggable database refresh’, language_flag => sys.dbms_sql.native, container => ‘RED’);
sys.dbms_sql.close_cursor(c=> cur);
end;

The database parameter job_queue_processes parameter must therefore be set to a value higher than 0 otherwise it will not refresh much.

Crash Primary PDB
In the example below, the primary PDB crash is initiated.

Create some test rows first.

— Create HR demo schedule in CDB1
CDB1/RED> @?/demo/schema/human_resources/hr_main.sql
— Check after 2 minutes if HR is also in the RED PDB in CDB2.
CDB2> alter pluggable database RED open read only;
CDB2> alter session set container=RED;
CDB2/RED> select count(*) from dba_objects where owner=’HR’;
COUNT(*)
———-
34

— Close the RED PDB in CDB2 so that it can be refreshed again.
CDB2> alter pluggable database RED close;

— In primary RED PDB
CDB1/RED> exec for i in 1..150 loop update hr.employees set hire_date=sysdate, salary=salary+1; dbms_lock.sleep(1); commit; end loop
CDB1/RED> select max(to_char(hire_date,’DD-MM-YYYY HH24:MI:SS’)) from hr.employees;

26-03-2020 14:29:01

CDB1/RED> shutdown abort
Pluggable Database closed.

Now that the primary PDB has crashed, the Refreshable PDB will have to be opened in read-write mode. A manual refresh will first be performed for this. This is only possible when the CDB1 is available for redo.

— manual refresh
CDB2> alter pluggable database RED refresh;
— Disable auto refresh
CDB2> alter pluggable database RED refresh mode none;
— Open PDB read-write
CDB2> alter pluggable database RED open read write;
CDB2> alter session set container=RED;
— Check whether the last record has actually been applied to the Refreshable PDB.
CDB2/RED> select max(to_char(hire_date,’DD-MM-YYYY HH24:MI:SS’)) from hr.employees;

26-03-2020 14:29:01

Crash Primary CDB
In the example below, the primary CDB crash is initiated. If the source CDB is not available, it means that the standby PDB cannot be updated before opening it read-write. In that case, the changes up to and including the last refresh will be included. So there is talk of data loss at that time. In the example below, the primary database is the RED PDB in CDB2. The RED database in CDB1 is on standby and refreshed every minute.

— Primary (CDB2/RED)
CDB2> alter session set container=RED;
Session altered.
— Updating Employees table.
CDB2/RED> exec for i in 1..150 loop update hr.employees set hire_date=sysdate, salary=salary+1; dbms_lock.sleep(1); commit; end loop
PL/SQL procedure successfully completed.
— Check last record
CDB2/RED> select max(to_char(hire_date,’DD-MM-YYYY HH24:MI:SS’)) from hr.employees;
MAX(TO_CHAR(HIRE_DA

---

 

27-03-2020 11:50:16

— Crash of CDB
CDB2> shutdown abortion

It is not visible from the CDB1 that the source database is not available. However, it is visible that the LAST_REFRESH_SCN no longer increases. This could be used as a control mechanism by writing it to an auxiliary table at a fixed interval. An alert can then easily be linked to this.

In the alert log of the standby database it is clearly visible that the Refresh failed.

2020-03-27T11:50:33.898050+01:00

RED(4):alter pluggable database refresh

***********************************************************************

Fatal NI connect error 12514, connecting to:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.16.196)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=CDB2)(CID=(PROGRAM=oracle@kvm1)(HOST= kvm1)(USER=oracle))))

TNS-12564: TNS:connection refused

ns secondary err code: 0

nt main err code: 0

nt secondary err code: 0

nt OS Err Code: 0

***********************************************************************

This means that manual refresh also fails.

CDB1> alter pluggable database RED refresh;

alter pluggable database RED refresh

*

ERROR at line 1:

ORA-17627: ORA-12514: TNS:listener does not currently know of service_name

Opening the Refreshable PDB in read-write mode fails.

CDB1> alter pluggable database RED open read write;

alter pluggable database RED open read write

*

ERROR at line 1:

ORA-65341: cannot open pluggable database in read/write mode

If the CDB is not available, a number of steps must be performed in order to open this read-write.

CDB1> alter pluggable database RED open read only;
CDB1> alter session set container=RED;
CDB1/RED> exec dbms_pdb.describe(‘/tmp/RED.xml’);
CDB1/RED> alter pluggable database RED close immediate;
CDB1> alter session set container=cdb$root;
CDB1> drop pluggable database RED keep data files;
CDB1> create pluggable database RED using ‘/tmp/RED.xml’ NOCOPY;
CDB1> alter pluggable database RED open;
CDB1> alter session set container=RED;

In terms of lead time, it is not much, but looking at the steps that must be taken when the CDB is available, this is a bit more complex. But what about data loss in this case?

CDB1/RED> select max(to_char(hire_date,’DD-MM-YYYY HH24:MI:SS’)) from hr.employees;
MAX(TO_CHAR(HIRE_DA

---

 

27-03-2020 11:49:32

Source for Crash:

27-03-2020 11:50:16

In this case, the data loss (RPO) is less than a minute and the RTO is less than 5 minutes.

When the crashed CDB is available again, the Refreshable PDB will have to be rebuilt. ATTENTION because the old source PDB will be opened read write by default which is a risk because that would mean that there are two active databases. It is therefore not possible to use an HA Service because this is too great a risk that changes will be made on two databases.

Switchover option

From Oracle 18c an option has been added, namely the switchover option. This option is currently only available in the Cloud or on Engineered Systems as part of the Multitenant option. With this option it becomes possible to switch the PDB’s roles. For large databases this isn’t really an option once you switch the former source database will be recreated. The reason for this is the fact that when a PDB has been opened read-write, it cannot be started as a Refreshable PDB. This is therefore a less good option for large databases, because then no standby is available during the time that the standby is refreshed.

Conclusion

Setting up and updating Refreshable PDBs is simple and works well. The only requirements are:

• A container on a second server.
• Network connectivity (SQL*NET) between both containers.
• Fast_Recovery_Area.
• Job_Queue_Processes> 0

It is important that both servers are licensed.

Due to the limitations when the source CDB is lost, this option is primarily suitable as a Disaster-Recovery solution where a number of manual actions must be performed at database level and from within the application to ensure that the correct database is connected.
The ability to open the Refreshable PDB read-only makes it a good solution for reporting purposes, for example. Because it is not easy to switch roles, this is a disadvantage because in that case the Refreshable PDB has to be rebuilt.
This method can be used very well for, for example, migrations to a new server or migrations to a CDB with a higher patch level. In that case, after opening the Refreshable PDB in Read-Write, it will have to be provided with the Patches that are installed in the CDB by means of Datapatch.