Asymmetric dataguard with multitenant (part 3/3)

In the first two parts of this series we learned that the DBA has a great margin to control PDB behavior in a physical dataguard, allowing much more flexible configurations than we could initially consider. But we have also learned that the most important thing is not the “how”, but what goal we want to achieve with that architecture. That is to say, before performing an operation with a PDB in a Primary CDB, we must first think about what our objective is and what plan we are going to follow. The main question being, do we need this PDB to be available on standby database?

If I need it, have I made the necessary preparations beforehand to make it happen proactively? E.g. Have I copied the datafiles before the plug? Have I made a bridging strategy for the hot clone?

In case I don’t need it, have I made sure that it is excluded so as not to generate unnecessary downtime on the dataguard? E.g. I have a listing of ENABLED_PDBS_ON_STANDBY or have I done the operation with standbys=none?

Having a plan brings us closer to success. But if for whatever reason we have forgotten to follow the required steps, it is also equally important to understand what solutions we can apply to defuse any problems. What can we consider in case we have to solve any unexpected operational problems?. This is the approach I would take a priori:

So lets say that I plugged-in a PDB in the primary CDB without copying the datafiles to the standby site and without excluding that PDB from the dataguard synchronization. The first thing you’d probably want to do is to make sure that the standby apply process is rolling again as soon as possible. This action can be fast:

-- Feedback disabled, encryption clauses removed for clarity, and usual common commands and outputs removed

-- stop the apply services through broker- unlike me, try to use a sysdg user for this
$ dgmgrl sys/xxxxx@CDBA_g2r_fra
edit database CDBA_d7d_fra set state='APPLY-OFF';
exit

-- mount standby db in case it was open read-only
$ srvctl stop database –d CDBA_d7d_fra –o immediate
$ srvctl start database –d CDBA_d7d_fra –o mount

--disable recovery for this failed plug-in pdb
sqlplus / as sysdba
SQL> alter session set container=FAILEDPDB;
SQL> alter pluggable database disable recovery;
SQL> exit

--resume the recovery
$ dgmgrl sys/xxxxx@CDBA_g2r_fra
edit database CDBA_d7d_fra set state='APPLY-ON';
exit

Now the apply process is running back again and you can decide what to do with this PDB at the standby site:

A.- Retry the Plug-In operation on the standby site

So my fault was that I forgot to copy the datafiles on the standy site. Or maybe they were on the wrong location. Oh I wish I could go back in time and have one more chance to make things right… and thats flashback!. So provided that our standby database has flashback-on or a guaranteed restore point created before the plug-in operation replay, I can rewind the standby CDB, copy the datafiles to the right location, and let the recovery retry the plug-in command execution. Sounds complicated?. See how the former procedures changes with this additional action:

-- Feedback disabled, encryption clauses removed for clarity, and usual common commands removed

-- stop the apply services through broker
$ dgmgrl sys/xxxxx@CDBA_g2r_fra
edit database CDBA_d7d_fra set state='APPLY-OFF';
exit

-- mount standby db in case it was open read-only
$ srvctl stop database –d CDBA_d7d_fra –o immediate
$ srvctl start database –d CDBA_d7d_fra –o mount

--disable recovery for this failed plug-in pdb
sqlplus / as sysdba
SQL> alter session set container=FAILEDPDB;
SQL> alter pluggable database disable recovery;
SQL> alter session set container=cdb$root;
SQL> flashback database to restore point pre_plugin_standby;


--resume the recovery
$ dgmgrl sys/xxxxx@CDBA_g2r_fra
edit database CDBA_d7d_fra set state='APPLY-ON';
exit

And you are good to go. Is this solution perfect? No, as you need to take the whole standby CDB out of business in the meantime. Will it take too much time? It depends on your particular environment, but rewinding the standby database 5 minutes back can be affordable in many cases. Needless to say you need to take care of your protection mode so the primary is not lagged due to this temporary downtime and consider this maintenance in your RTO-related SLAs.

Ok, but I can I use ENABLED_PDBS_ON_STANDBY to disable recovery for that PDB. Can it be modified online?

Well, If you forgot to copy the datafiles to the standby – following the former example -, it is most probable that you are actually not working with “ENABLED_PDBS_ON_STANDBY” parameter, as you must explicitly add which PDBs are or will be (PDBs in the list may or may not exist yet) part of the standby recovery. And this is why I prefer to use this parameter* approach, as it forces you to be conscious about the operation you are about to implement. But, in any case, if you have added this PDB to the list and did not copied the necessary datafiles to the standby, you can indeed modify this parameter on the fly and still use the former procedure.

*A little side note about ENABLED_PDBS_ON_STANDBY: As you can see in the reference manual, you can even use wildcards within this parameter. Be cautious with this, as if you define a too broad wildcard it can accidentally match a name you do not want to match, losing the whole point of it.

B.- Re-introduce the PDB later on in a more convenient moment

So you have temporarily disabled the recovery for that failed PDB, and now your DG is back in sync. But you’d still like to have the PDB included in the main standby recovery process. In that case you can follow this great Oracle support note , where you will find a step-by-step guide to restore and reintroduce a singular PDB into a standby database. It’s leverages the “restore…from service” approach so no need to create static services for this; it is reasonably simple.

Ok Fran. I think I have a clear idea on how an asymmetric dataguard behaves in PDB terms and I know how to plan a successful maintenance. I even know how to reintroduce a PDB into the standby recovery after a primary hot-clone.

Now… do we have any alternative in 19c to perform a hot-clone in the primary CDB, and to have that PDB cloned in the standby as well, without the need of any additional maintenance?

In 19c we can achieve something similar being creative. Lets say we have the following prerequisites:

We need to clone a production PDB
No downtime allowed for that PDB
We have more than twice the size of that PDB in the form of free space in this environment.
We’d like to avoid stopping the recovery services on the standby to have that hot-cloned PDB synced there.

Now, lets see if the following architecture does the trick:

In a first stage, we will be doing a hot-clone (standbys=none) for the production PDB on the primary site. So with this step we comply with the “no downtime allowed” rule. Just after that hot-clone, we will put the cloned PDB in read-only mode and we will perform a COLD clone from it. With this second step, we circumvent the recovery limitation of the 19c – no additional recovery needed in the standby. Is that all? No because… how can the standby CDB access the source datatafiles in order to copy them?

So to deal with this last requirement we will be using the STANDBY_PDB_SOURCE_FILE_DBLINK parameter . This parameter was though to be used for remote clones where the source PDB was open in read-only mode, which is a case we have not delved into; it is as simple as it sounds. But for this scenario, we will give it a more interesting use. We will be creating a dblink from the primary CDB to the primary CDB. Yes!, a self-referencing dblink. So what happens If we create a self-referencing dblink in a primary dataguard CDB?. The same dblink is created in the standby CDB and so… now we have a dblink available on the standby CDB pointing to the primary CDB.

How can we then use this dblink to perform the hot clone from the transient PDB to the final PDB?. Just define the STANDBY_PDB_SOURCE_FILE_DBLINK on the standby site and execute the cold clone as usual, making sure that the new PDB name is included in the ENABLED_PDBS_ON_STANDBY parameter (both primary and standby for DG symmetry). Similarly to STANDBY_PDB_SOURCE_FILE_DIRECTORY behavior, the standby database will automatically try to reach the database on the other side of STANDBY_PDB_SOURCE_FILE_DBLINK* and copy files from there.

*Unlike with STANDBY_PDB_SOURCE_FILE_DIRECTORY, using STANDBY_PDB_SOURCE_FILE_DBLINK requires Active Dataguard licensing.

Come on man! This is too complex!

It is convenient for the DBAs precisely because of it’s simplicity… see this. First, some preparation steps:

--Primary
SQL> create user c##cloner identified by We1c0m3_We1c0m3_ container=all;
SQL> grant create session, create pluggable database to c##cloner identified by xxxx container=all;

--Standby
SQL> create database link origen connect to c##cloner identified by xxxx using 'CDBA_G2R_FRA';
SQL> select ora_database_name from dual@origen;
ORA_DATABASE_NAME
-----------------
CDBA.TFEXSUBDBSYS.TFEXVCNDBSYS.ORACLEVCN.COM

SQL> alter system set standby_pdb_source_file_dblink=ORIGEN scope=both sid='*';

Now lets implement the procedure itself:

--Primary
SQL> show pdbs
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDB1 			  READ WRITE NO
	 5 PDBA 			  READ WRITE NO

SQL> alter system set enabled_PDBs_on_standby="PDB1","PDBA","COLD","HOT" scope=both;


--Standby
SQL> show pdbs
CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED			  READ ONLY  NO
4 PDB1 			  MOUNTED
5 PDBA 			  MOUNTED

SQL> alter system set enabled_PDBs_on_standby="PDB1","PDBA","COLD","HOT" scope=both;

--Primary

SQL> create pluggable database HOT from PDB1;
SQL> alter pluggable database HOT open;
SQL> show pdbs
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 HOT				  READ WRITE NO
	 4 PDB1 			  READ WRITE NO
	 5 PDBA 			  READ WRITE NO
   
SQL> select name, recovery_status from v$pdbs;

NAME						RECOVERY
----------------------------------------------- --------
PDB$SEED					ENABLED
HOT						ENABLED
PDB1						ENABLED
PDBA						ENABLED

SQL> alter pluggable database hot open read only force;
SQL> create pluggable database COLD from HOT;

Let’s see whats in the standby alert log:

2023-11-06T15:57:13.295917+00:00
Recovery created pluggable database COLD
Recovery attempting to copy datafiles for pdb-COLD from           source pdb-HOT at dblink-ORIGEN
2023-11-06T15:57:21.369606+00:00
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file +DATA/CDBA_D7D_FRA/097ECEB5639F6011E0632814010ACE60/DATAFILE/system.362.1152201435 from +DATA/CDBA_G2R_FRA/097ECEB563996011E0632814010ACE60/DATAFILE/system.335.1152201325
COLD(6):Datafile 94 added to flashback set
COLD(6):Successfully added datafile 94 to media recovery
COLD(6):Datafile #94: '+DATA/CDBA_D7D_FRA/097ECEB5639F6011E0632814010ACE60/DATAFILE/system.362.1152201435'
2023-11-06T15:57:27.141853+00:00
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file +DATA/CDBA_D7D_FRA/097ECEB5639F6011E0632814010ACE60/DATAFILE/sysaux.363.1152201441 from +DATA/CDBA_G2R_FRA/097ECEB563996011E0632814010ACE60/DATAFILE/sysaux.309.1152201325
COLD(6):Datafile 95 added to flashback set
COLD(6):Successfully added datafile 95 to media recovery
COLD(6):Datafile #95: '+DATA/CDBA_D7D_FRA/097ECEB5639F6011E0632814010ACE60/DATAFILE/sysaux.363.1152201441'
2023-11-06T15:57:28.214285+00:00
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file +DATA/CDBA_D7D_FRA/097ECEB5639F6011E0632814010ACE60/DATAFILE/undotbs1.364.1152201447 from +DATA/CDBA_G2R_FRA/097ECEB563996011E0632814010ACE60/DATAFILE/undotbs1.349.1152201325
COLD(6):Datafile 96 added to flashback set
COLD(6):Successfully added datafile 96 to media recovery
COLD(6):Datafile #96: '+DATA/CDBA_D7D_FRA/097ECEB5639F6011E0632814010ACE60/DATAFILE/undotbs1.364.1152201447'
2023-11-06T15:57:28.497585+00:00
(6):Master key must be set for this container (6) when tablespace_encryption (Hybrid Data Guard encryption) is set. Not doing so will disable Hybrid Data Guard encryption.
(6):Run administer key management set encryption key for the PDB in the primary database.
[...]

We can see how the standby has retrieved the datafiles from the primary DB. Now, in order to open read-only the “COLD” clone from production PDB we need to open it in the primary site first. Remember that any new PDB needs to be opened always at least once before we can start working with it. This includes to be able to open it in read-only in the standby site:

--Primary

SQL> alter pluggable database COLD open;

--Standby
SQL> alter pluggable database COLD open read only;
SQL> select name, recovery_status from v$pdbs;

NAME					   RECOVERY
---------        --------
PDB$SEED			 ENABLED
HOT					 DISABLED
PDB1					 ENABLED
PDBA					 ENABLED
COLD					 ENABLED

So here it is our hot-cloned production PDB running within the standby recovery and opened in read only mode. Now lets remove the transient hot PDB on the primary and verify that it is as well removed from standby CDB metadata:

--Primary
SQL> alter pluggable database hot close immediate;
SQL> drop pluggable database hot including datafiles;

--Standby
SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 4 PDB1 			  MOUNTED
	 5 PDBA 			  MOUNTED
	 6 COLD 			  READ ONLY  NO

Meanwhile our DG apply services have been running without interruption:

dgmgrl sys/xxxx@CDBA_g2r_fra


   DGMGRL> show database verbose CDBA_d7d_fra

   Database - CDBA_d7d_fra

     Role:               PHYSICAL STANDBY
     Intended State:     APPLY-ON
     Transport Lag:      0 seconds (computed 0 seconds ago)
     Apply Lag:          0 seconds (computed 0 seconds ago)
     Average Apply Rate: 106.00 KByte/s
     Active Apply Rate:  1.69 MByte/s
     Maximum Apply Rate: 2.02 MByte/s
     Real Time Query:    ON
     Instance(s):
       CDBA
[...]

Ok nice, but it takes more space

Yes, it temporarily takes more space – which can be recovered afterwards.

Yes but look, this is too slow. I cannot wait to duplicate my production database twice in order to have the copy created on both primary and standby. Cannot we make this faster?

The real power of multitenant operations is in their combinations. What If we have a pre-hot-cloned PDB created?. If you expect to need this clone, instead of doing a hot clone we can just have a local refreshable PDB. In the moment you need the hot clone, you would just perform a last refresh on the refreshable PDB, and start the hot clone right away. See? Back in the same timings as the standard clone. Or pretty close:

With this other approach we would be exchanging space resource for time resource. Perhaps the most important point is not the solution itself, but to be aware that there are probably more possible combinations that better fit our use case.

This concludes part 3 of this series on multitenant asymmetric dataguard, covering the questions raised in the article on the new hot cloning capabilities available from 21c onward. Thus finally closing the circle on PDBs and physical dataguard in 19c. I sincerely hope that these articles have given you a little more security and clarity in your day-to-day work with PDBs and physical dataguard.

Asymmetric dataguard with multitenant (part 3/3)

Share this: