Transfer redo in async-mode to the Gold/Master copy of the Production DB for ACFS snapshots

If you store your databases on the cluster filesystem ACFS you may use the provided Perl-script gDBClone from OTN to clone databases or create snapshot databases. It is an interesting approach to create clones from the Production DB in minutes regardless of the production DB size. What you do is to create a standby DB from your production DB on a separate cluster and use that standby DB as a Gold/Master copy for ACFS snapshots.

In a Production environment with Data Guard Broker we wanted to use that technique, but were confronted with an issue:

The Production DB had already a physical standby DB with the Data Guard Broker running. The protection mode was MaxAvailability, which means transport of the redo in sync mode. The master/gold copy to get the snapshots from should receive the redo data in async mode. How to achieve that?

Actually you can achieve it by adding a standby database and set the database property LogXptMode = ‘ASYNC’, but that would add a “non-production” database to your production broker configuration.

There is an alternative. Not very common parameters in a Broker configuration are


ExternalDestination1
ExternalDestination2


With those parameters (which are available in 11.2.0.4 and 12.1.0.2 onwards) you actually can send your redo to a destination in async mode. The parameters are documented as follows:

The ExternalDestination1 configuration property is used to specify a redo transport destination that can receive redo data from the current primary database. To set up transport of redo data to the specified destination, the broker uses the values specified for this parameter to define a LOG_ARCHIVE_DEST_n initialization parameter on the primary database. The broker also monitors the health of the transport to the specified destination.

ALTERNATE
DELAY
LOCATION
MANDATORY
MAX_FAILURE
NET_TIMEOUT
SYNC
VALID_FOR

So let’s assume I created my DB GOLDCOP as a standby DB using the rman duplicate command


RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;


or alternatively using


# ./gDBClone clone -sdbname PRIM -sdbscan scoda7 -tdbname GOLDCOP -tdbhome OraDb11g_home1 -dataacfs /cloudfs -standby


In the broker configuration I added the DB GOLDCOP as follows:

DGMGRL> show configuration;
 
Configuration - MYPROD
 
Protection Mode: MaxAvailability
Databases:
PRIM - Primary database
STBY - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
DGMGRL> edit configuration set property ExternalDestination1 = 'service=goldcop db_unique_name=GOLDCOP noaffirm async';
Property "externaldestination1" updated
DGMGRL> show configuration;
 
Configuration - MYPROD
 
Protection Mode: MaxAvailability
Databases:
PRIM - Primary database
STBY - Physical standby database
GOLDCOP - External destination 1
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS


Let’s check if I really do NOAFFIRM ASYNC redo transport on PRIM:

SQL> select DEST_NAME, DB_UNIQUE_NAME, AFFIRM, TRANSMIT_MODE from v$archive_dest where dest_id in (2,3);
 
DEST_NAME DB_UNIQUE_NAME AFF TRANSMIT_MOD
-------------------------------- ------------------------------ --- ------------
LOG_ARCHIVE_DEST_2 STBY YES PARALLELSYNC
LOG_ARCHIVE_DEST_3 GOLDCOP NO ASYNCHRONOUS


The external destination is not a database in the configuration:

DGMGRL> show database "GOLDCOP";
Object "GOLDCOP" was not found


But the transport to its destination is monitored by the Broker. I.e. when shutting down the DB GOLDCOP I do get an error:

DGMGRL> show configuration;
 
Configuration - MYPROD
 
Protection Mode: MaxAvailability
Databases:
PRIM - Primary database
Error: ORA-16778: redo transport error for one or more databases
 
STBY - Physical standby database
GOLDCOP - External destination 1
 
Fast-Start Failover: DISABLED
 
Configuration Status:
ERROR
 
DGMGRL> show instance "PRIM";
 
Instance 'PRIM' of database 'PRIM'
 
Instance Error(s):
ORA-16737: the redo transport service for standby database "GOLDCOP" has an error
 
Instance Status:
ERROR


As the External destination 1 is not “a database” in the broker configuration, it actually also does not matter if the broker is started (dg_broker_start=TRUE) at the external destination GOLDCOP or not.
To start applying redo on the external destination, you have to start managed recovery as you would without a broker configuration:

alter database recover managed standby database using current logfile disconnect from session;


And redo real time apply is happening on GOLDCOP:

SQL> select name,value
2 from v$dataguard_stats
3 where name in ('apply lag','transport lag');
 
NAME VALUE
-------------------- --------------------
transport lag +00 00:00:00
apply lag +00 00:00:00
 
SQL>
SQL> select inst_id,process,pid,status,thread#,sequence#, block#
2 from gv$managed_standby
3 where process like 'MRP%';
 
INST_ID PROCESS PID STATUS THREAD# SEQUENCE# BLOCK#
---------- --------- ---------- ------------ ---------- ---------- ----------
1 MRP0 5155 APPLYING_LOG 1 50 420


To make the external destination self managing I did set the archivelog deletion policy on GOLDCOP to

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

in rman so that applied archives become reclaimable automatically in the fast recovery area. In addition I set

fal_server='PRIM'


on GOLDCOP to ensure that archive gaps can be resolved.

The pro’s of above configuration are:
– the GOLDCOP-DB does not cause much overhead for my Production DB (async redo transport).
– Decoupling my GOLDCOP DB from Primary (temporarily) is fast and easy:
edit configuration set property ExternalDestination1 = '';

REMARK: Of course I do also have the other advantages of the gDBClone-approach:
– A production copy on a separate cluster which serves as a gold-copy to take snapshots from for testing or development purposes.
– Creating a snapshot database takes minutes regardless of the DB size.

Con’s:
– I have to take care to start managed standby database recovery on my GOLDCOP-DB. I.e. the same as when running data guard without the Broker.

To create a snapshot DB I just do something like:


# ./gDBClone snap -sdbname GOLDCOP -tdbname PRODCOP1


Et voilà a production copy in 2 minutes.

If PRODCOP1 is no longer needed I can delete it:


# ./gDBClone deldb -tdbname PRODCOP1


Besides using the configuration property ExternalDestination1 there are other possibilities in 12c to run a standby DB as a master copy for snapshots without affecting the production system (like e.g. the support of cascaded standby DBs in the Broker), but I still think that the external destinations feature offers a good possibility to run a master copy.