ZDLRA, Multi-site protection – ZERO RPO for Primary and Standby

ZDLRA can be used from a small single database environment to big environments where you need protection in more than one site at the same time. At every level, you can use different features of ZDLRA to provide desirable protection. Here I will show how to reach zero RPO for both primary and standby databases. All the steps, doc, and tech parts are covered.

You can check the examples the reference for every scenario int these two papers from the Oracle MAA team: MAA Overview On-Premises and Oracle MAA Reference Architectures. They provide good information on how to prepare to reduce RPO and improve RTO. In resume, the focus is the same, reduce the downtime and data loss in case of a catastrophe (zero RPO, and zero RPO).

Multi-site protection

If you looked both papers before, you saw that to provide good protection is desirable to have an additional site to, at least, send the backups. And if you go higher, for GOLD and PLATINUM environments, you start to have multiple sites synced with data guard. These Critical/Mission-critical environments need to be protected for every kind of catastrophic failure, from disk until complete site outage (some need to follow specific law’s requirements, bank as an example).

And the focus of this post is these big environments. I will show you how to use ZDLRA to protect both sites, reaching zero RPO even for standby databases. And doing that, you can survive for a catastrophic outage (like entire datacenter failure) and still have zero RPO. Going further, you can even have zero RPO if you lose completely on site when using real-time redo for ZDLRA, and this is not written in the docs by the way.

Some features I already wrote about that in the previous posts. Already wrote about ZDLRA features, how to enroll a database, and how to reach zero RPO for database protection. All of these I will use here, to protect the bigger environments it is used incremental forever strategy for backup and real-time redo to protect primary and standby databases to reach zero RPO.

MAA team already wrote about how to do that at Deploying the Zero Data Loss Recovery Appliance in a Data Guard Configuration, but frankly, it is hard to understand this doc. And here in this post, I will try to provide a better example and how to reach RPO zero for both sites.

Multiple ZDLRA’s

The way that we will use ZDLRA, in this case, is different from the replication feature that exists for ZDLRA. Here, we have two ZDLRA, one for each site. The environment in this case is:

  • ORCL19: RAC primary database.
  • ORCL19S: RAC standby database.
  • ZDLRAS1: ZDLRA that protect the primary site.
  • ZDLRAS2: ZDLRA that protects the standby site.

And the target will be:

As you can see above, each ZDLRA protects your own site, and the replication between sites is done by DG. The DG configuration it is not part of this post, but the output (and order) for all commands how I created the RAC + RAC DG you can check here – Steps-RAC-RAC-DG-Creation. If you have some doubts about it, I can explain if needed.

Index

This post is big because of the size and number of outputs of commands. So, a little index for the key topics (if you want to jump something):

ZDLRA Configuration – Protecting and Enrolling Databases

VPC

If you already have a VPC user created for your ZDLRA and want to use an existing one, you can skip this step. Just remember that the same user needs to exist in both ZDLRA’s. This is more critical if you are intending to use real-time redo and reach zero RPO.

So, if needed, create the same VPC user in both ZDLRA’s:

At ZDLRAS1:

[root@zdlras1n1 ~]# /opt/oracle.RecoveryAppliance/bin/racli add vpc_user --user_name=vpczdlra
[vpczdlra] New Password:
Sat Nov  2 19:43:59 2019: Start: Add vpc user vpczdlra.
Sat Nov  2 19:44:00 2019:        Add vpc user vpczdlra successfully.
Sat Nov  2 19:44:00 2019: End:   Add vpc user vpczdlra.
[root@zdlras1n1 ~]#

At ZDLRAS2

[root@zdlras2n1 ~]# /opt/oracle.RecoveryAppliance/bin/racli add vpc_user --user_name=vpczdlra
[vpczdlra] New Password:
Sat Nov  2 19:43:41 2019: Start: Add vpc user vpczdlra.
Sat Nov  2 19:43:42 2019:        Add vpc user vpczdlra successfully.
Sat Nov  2 19:43:42 2019: End:   Add vpc user vpczdlra.
[root@zdlras2n1 ~]#

Backup Policy

It is not needed to have the same policy in each ZDLRA and it is possible to enroll the database using different policies since they (ZDLRA’s) work separately. Here I used the same policy in both sites (but with different recovery window goals).

At ZDLRAS1:

[oracle@zdlras1n1 ~]$ sqlplus rasys/change^Me2

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 2 22:21:18 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat Nov 02 2019 11:20:06 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> BEGIN
  2  DBMS_RA.CREATE_PROTECTION_POLICY(
  3      protection_policy_name => 'ZDLRA'
  4      , description => 'Policy ZDLRA S1'
  5      , storage_location_name => 'DELTA'
  6      , recovery_window_goal => INTERVAL '5' DAY
  7      , max_retention_window => INTERVAL '10' DAY
  8      , guaranteed_copy => 'NO'
  9      , allow_backup_deletion => 'YES'
 10  );
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL>

At ZDLRAS2:

[oracle@zdlras2n1 ~]$ sqlplus rasys/change^Me2

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 2 22:22:13 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat Nov 02 2019 11:21:04 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> BEGIN
  2  DBMS_RA.CREATE_PROTECTION_POLICY(
  3      protection_policy_name => 'ZDLRA'
  4      , description => 'Policy ZDLRA S2'
  5      , storage_location_name => 'DELTA'
  6      , recovery_window_goal => INTERVAL '7' DAY
  7      , max_retention_window => INTERVAL '14' DAY
  8      , guaranteed_copy => 'NO'
  9      , allow_backup_deletion => 'YES'
 10  );
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL>

Grant DB at ZDLRA

Here we have the first key point, and it is related to the way that database is registered database inside ZDLRA. It follows the traditional way, using the ADD_DB, GRANT_DB_ACCESS, and using DB_UNIQUE_NAME as identification.

The point here is that for dataguard the DB_UNIQUE_NAME is different for each database, but one detail it is that ZDLRA internally links DBID of the database with the unique name. So, it is needed and required, that for ZDLRA who will protect the standby site that the add and registration use DB_UNIQUE_NAME from the primary. If you do not do that, the ZDLRA will understand that the standby database it is a completely different database (and will report, RMAN-03009: failure of resync command on default channel, ORA-00001: unique constraint (RASYS.ODB_P) violated, and ORA-04088: error during execution of trigger ‘RASYS.NODE_INSERT_UPDATE’).

And another common error, if you try to register the standby database (instead of primary database) is RMAN-01005: Mounted control file type must be CURRENT to register the database. This occurs because some registrations and checks done by rman and database controlfile need to be at current one.

At ZDLRAS1:

[oracle@zdlras1n1 ~]$ sqlplus rasys/change^Me2

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 3 14:11:37 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sun Nov 03 2019 11:16:12 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> BEGIN
  2  DBMS_RA.ADD_DB(
  3        db_unique_name => 'ORCL19'
  4        , protection_policy_name => 'ZDLRA'
  5        , reserved_space => '5G'
  6  );
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2  DBMS_RA.GRANT_DB_ACCESS (
  3        db_unique_name => 'ORCL19'
  4        , username => 'VPCZDLRA'
  5  );
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

At ZDLRAS2:

[oracle@zdlras2n1 ~]$ sqlplus rasys/change^Me2

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 3 16:14:06 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sun Nov 03 2019 11:16:55 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> BEGIN
  2  DBMS_RA.ADD_DB(
  3        db_unique_name => 'ORCL19'
  4        , protection_policy_name => 'ZDLRA'
  5        , reserved_space => '5G'
  6  );
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2  DBMS_RA.GRANT_DB_ACCESS (
  3        db_unique_name => 'ORCL19'
  4        , username => 'VPCZDLRA'
  5  );
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

Look above that ADD_DB and GRANT_DB_ACCESS at ZDLRAS2 used the DB_UNIQUE_NAME as ORCL19 (that it is the name for primary).

Registry database

At Primary – ORLC19

At this point, it is possible to register and backup the primary database with ZDLRAS1. The steps are the same that I described at my post on how to enroll a database at ZDLRA. The steps are, in order:

  1. Install ZDLRA library.
  2. Create a Wallet.
  3. Configure/Test tns entry to ZDLRA.
  4. Adjust sqlnet.ora file (this is needed because of the real-time redo).
[oracle@orcl19p ~]$ cd /tmp/
[oracle@orcl19p tmp]$ unzip ra_linux64.zip
Archive:  ra_linux64.zip
  inflating: libra.so
  inflating: metadata.xml
[oracle@orcl19p tmp]$
[oracle@orcl19p tmp]$ cp ./libra.so /u01/app/oracle/product/19.0.0.0/dbhome_1/lib/libra.so
[oracle@orcl19p tmp]$
[oracle@orcl19p tmp]$ cd $ORACLE_HOME/dbs
[oracle@orcl19p dbs]$
[oracle@orcl19p dbs]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -createALO
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

[oracle@orcl19p dbs]$
[oracle@orcl19p dbs]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -createCredential zdlras1-scan:1521/zdlras1:VPCZDLRA vpczdlra oracle
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

[oracle@orcl19p dbs]$
[oracle@orcl19p dbs]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

List credential (index: connect_string username)
1: zdlras1-scan:1521/zdlras1:VPCZDLRA vpczdlra
[oracle@orcl19p dbs]$
[oracle@orcl19p dbs]$ vi $ORACLE_HOME/dbs/raORCL191.ora
[oracle@orcl19p dbs]$ cat $ORACLE_HOME/dbs/raORCL191.ora
RA_WALLET='LOCATION=file:/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet CREDENTIAL_ALIAS=zdlras1-scan:1521/zdlras1:VPCZDLRA'
[oracle@orcl19p dbs]$
[oracle@orcl19p dbs]$ tnsping zdlras1

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 03-NOV-2019 15:34:09

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zdlras1-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zdlras1)))
OK (20 msec)
[oracle@orcl19p dbs]$
[oracle@orcl19p dbs]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
cat: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora: No such file or directory
[oracle@orcl19p dbs]$
[oracle@orcl19p dbs]$ vi $ORACLE_HOME/network/admin/sqlnet.ora
[oracle@orcl19p dbs]$
[oracle@orcl19p dbs]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.WALLET_OVERRIDE = true

WALLET_LOCATION =
(
   SOURCE =
      (METHOD = FILE)
      (METHOD_DATA =
         (DIRECTORY = /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet)
      )
)
[oracle@orcl19p dbs]$

At Standby – ORLC19S

The same procedure than above it is done here:

  1. Install ZDLRA library.
  2. Create a Wallet.
  3. Configure/Test tns entry to ZDLRA.
  4. Adjust sqlnet.ora file (this is needed because of the real-time redo).
[oracle@orcl19s ~]$ cd /tmp/
[oracle@orcl19s tmp]$ unzip ra_linux64.zip
Archive:  ra_linux64.zip
  inflating: libra.so
  inflating: metadata.xml
[oracle@orcl19s tmp]$
[oracle@orcl19s tmp]$ cp ./libra.so /u01/app/oracle/product/19.0.0.0/dbhome_1/lib/libra.so
[oracle@orcl19s tmp]$
[oracle@orcl19s tmp]$ cd $ORACLE_HOME/dbs
[oracle@orcl19s dbs]$
[oracle@orcl19s dbs]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -createALO
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

[oracle@orcl19s dbs]$
[oracle@orcl19s dbs]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -createCredential zdlras2-scan:1521/zdlras2:VPCZDLRA vpczdlra oracle
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

[oracle@orcl19s dbs]$
[oracle@orcl19s dbs]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

List credential (index: connect_string username)
1: zdlras2-scan:1521/zdlras2:VPCZDLRA vpczdlra
[oracle@orcl19s dbs]$
[oracle@orcl19s dbs]$ vi $ORACLE_HOME/dbs/raORCL19S1.ora
[oracle@orcl19s dbs]$
[oracle@orcl19s dbs]$ cat $ORACLE_HOME/dbs/raORCL19S1.ora
RA_WALLET='LOCATION=file:/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet CREDENTIAL_ALIAS=zdlras2-scan:1521/zdlras2:VPCZDLRA'
[oracle@orcl19s dbs]$
[oracle@orcl19s dbs]$ tnsping zdlras2

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 03-NOV-2019 16:32:57

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zdlras2-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zdlras2)))
OK (0 msec)
[oracle@orcl19s dbs]$
[oracle@orcl19s dbs]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
cat: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora: No such file or directory
[oracle@orcl19s dbs]$
[oracle@orcl19s dbs]$ vi $ORACLE_HOME/network/admin/sqlnet.ora
[oracle@orcl19s dbs]$
[oracle@orcl19s dbs]$ cat $ORACLE_HOME/network/admin/sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.WALLET_OVERRIDE = true

WALLET_LOCATION =
(
   SOURCE =
      (METHOD = FILE)
      (METHOD_DATA =
         (DIRECTORY = /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet)
      )
)
[oracle@orcl19s dbs]$

Registry Primary Database – Both ZDLRA’s

After that, we can register ORACL19 at ZDLRAS1. It is critical to register the primary database first at the ZDLRA that will protect the primary site.

[oracle@orcl19p dbs]$ rman target=/ catalog=vpczdlra/oracle@zdlras1

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 3 15:40:37 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19 (DBID=324042554)
connected to recovery catalog database
PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current
PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

If you want, you can do the backup of the database (it is optional), I not showed here but it is a simple backup database level 0 filesperset 1 and you can see the output here – Output-Backup-Primary-ORCL19-at-ZDLRAS1.

After registry primary database ORCL19 at ZDLRA (ZDLRAS1) that protect the primary site, we can register the primary ORCL19 at the ZDLRA that protect the standby site (ZDLRAS2)

Look that I need to add the entry of TNS names for ZDLRAS2 (at the primary server) too:

[oracle@orcl19p dbs]$ tnsping zdlras2

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 03-NOV-2019 16:37:38

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zdlras2-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zdlras2)))
OK (0 msec)
[oracle@orcl19p dbs]$
[oracle@orcl19p dbs]$ rman target=/ catalog=vpczdlra/oracle@zdlras2

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 3 17:30:06 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19 (DBID=324042554)
connected to recovery catalog database
PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current
PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> exit


Recovery Manager complete.
[oracle@orcl19p dbs]$

Since the registration was done at primary, the controlfile of the standby database needs to be updated with one rman catalog, and it is needed to inform ZDLRAS2 (that protect standby site) who is the standby database. This is done by doing a simple resync catalog from standby, connected at ZDLRAS2.

[oracle@orcl19s dbs]$ rman target=/ catalog=vpczdlra/oracle@zdlras2

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 3 20:12:32 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19 (DBID=324042554, not open)
connected to recovery catalog database
PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current
PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL19S are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' FORMAT   '%d_%U' PARMS  "SBT_LIBRARY=/u01/app/oracle/product/19.0.0.0/dbhome_1/lib/libra.so, ENV=(RA_WALLET='location=file:/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet credential_alias=zdlras1-scan:1521/zdlras1:VPCZDLRA')" CONNECT '*';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/snapcf_orcl19s1.f'; # default

RMAN> resync catalog;

starting resync of recovery catalog
resync complete

RMAN> list db_unique_name of database orcl19;


List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
835     ORCL19   324042554        PRIMARY          ORCL19
835     ORCL19   324042554        STANDBY          ORCL19S

RMAN>

Look at some points above:

  • show all read the already registered information from rman catalog (look channel set)
  • After the resync catalog, the list db_unique_name reported both databases, primary (ORCL19) and standby (ORCL19S)

After that, you can configure the correct channel for ORCL19S to point to ZDLRAS2, and do the backup. This output can be checked here – Output-Backup-Primary-ORCL19S-at-ZDLRAS2.

Environment protected

At this point, you have both sites protected, which one with dedicated local backup with ZDLRA. Primary ORCL19 database does the backup at ZDLRAS1, and standby database ORCL19S does the backup at ZDLRAS2:

But this not protect you in case of failure of one site. In this case, the RPO will not be zero because we don’t have where to send the archivelogs.

Think that you lose your primary site, and after the switch to standby the machine failures again. What will be your RPO? The last backup of standby will be, because your standby (new primary) it is not sending archivelogs to the old primary (because it is out for outage), and you don’t have real-time redo configured for ZDLRA. So, if you have an outage at standby you lose until the point of your last backup. This is not clear at the docs from MAA Overview On-Premises and Oracle MAA Reference Architectures.

ZERO RPO

To reach zero RPO we just need to enable real-time redo for both sites. We can do manually or using the broker. If you want to see in detail how to configure you can check my two posts: ZDLRA, Real-Time Redo and ZDLRA, Real-Time Redo and Zero RPO, here I will post resumed steps.

Be careful with the order of the steps below, they are important because we are changing the user (redo_transport_user) that will be configured in both databases. If you change first at standby you will face issued because the user is created at primary first and the “creation” it sends automatically by redo. By the way, this user needs to be the same as VPC user that you used to configure/add/grant database access for ZDLRA. And need to be the same for every ZDLRA because both databases use the same user. This is the second key point of this config.

Manually Configuration

The manually way differs because here we set manually the log_archive_dest parameters. The others need to be done besides the way that we choose. And the manual config is the only way for 11G databases.

Basic config

For real-time redo we need to configure some parameters, but basically, I made these configs in order:

  1. log_archive_config: Check that for primary it is ZDLRAS1, and ZDLRAS2 for standby
  2. log_archive_dest_3: Again, each site points to the own ZDLRA. And look the type defined at VALID_FOR
  3. Create the user for redo_transport_user inside the database.
  4. redo_transport_user: Same for both databases.

At Primary ORCL19

[oracle@orcl19p ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 3 22:34:28 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> show parameter log_archive_config;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(orcl19,orcl19s)
SQL> ALTER SYSTEM SET log_archive_config='DG_CONFIG=(orcl19,orcl19s,zdlras1)' SCOPE=SPFILE SID='*';

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_3='SERVICE="zdlras1-scan:1521/zdlras1:VPCZDLRA" ASYNC NOAFFIRM DB_UNIQUE_NAME=zdlras1 VALID_FOR=(ONLINE_LOGFILE,ALL_ROLES)' scope = spfile sid = '*';

System altered.

SQL>
SQL> alter system set log_archive_dest_state_3=DEFER scope = spfile sid = '*';

System altered.

SQL>
SQL> col username format a20
SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users;

USERNAME             SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM
-------------------- ----- ----- ----- ----- ----- -----
SYS                  TRUE  TRUE  FALSE FALSE FALSE FALSE

SQL> show parameter redo_transport_user;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
redo_transport_user                  string
SQL> alter system set redo_transport_user='VPCZDLRA' scope = spfile SID='*';

System altered.

SQL> 
SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> create user VPCZDLRA identified by oracle;

User created.

SQL> grant sysoper to VPCZDLRA;

Grant succeeded.

SQL> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users;

USERNAME             SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM
-------------------- ----- ----- ----- ----- ----- -----
SYS                  TRUE  TRUE  FALSE FALSE FALSE FALSE
VPCZDLRA             FALSE TRUE  FALSE FALSE FALSE FALSE

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
[oracle@orcl19p ~]$

At Standby ORCL19S

[oracle@orcl19s dbs]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 3 22:53:05 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> show parameter log_archive_config;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string      DG_CONFIG=(orcl19,orcl19s)
SQL> ALTER SYSTEM SET log_archive_config='DG_CONFIG=(orcl19,orcl19s,zdlras2)' SCOPE=SPFILE SID='*';

System altered.

SQL> ALTER SYSTEM SET log_archive_dest_3='SERVICE="zdlras2-scan:1521/zdlras2:VPCZDLRA" ASYNC NOAFFIRM DB_UNIQUE_NAME=zdlras2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' scope = spfile sid = '*';

System altered.

SQL> alter system set redo_transport_user='VPCZDLRA' scope = spfile SID='*';

System altered.

SQL> 
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
[oracle@orcl19s dbs]$ 
[oracle@orcl19s dbs]$ srvctl stop database -d orcl19s -o immediate
[oracle@orcl19s dbs]$

As you can see here, the user is not created at standby because the creation will come by redo. And as you can see in the end, I stopped the standby database.

Password file

Since one user was created in just one site, we need to copy the password file from production to the standby.

At Primary ORCL19

[root@orcl19p ~]# su - grid
Last login: Sun Nov  3 23:03:09 CET 2019
[grid@orcl19p ~]$ asmcmd
ASMCMD> cd +DATA/ORCL19/
ASMCMD> ls -l
Type             Redund  Striped  Time             Sys  Name
                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/
                                                   Y    95D86C40430C3E85E0533205A00A0EBA/
                                                   Y    CONTROLFILE/
                                                   Y    DATAFILE/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    ONLINELOG/
                                                   Y    PARAMETERFILE/
                                                   Y    PASSWORD/
                                                   Y    TEMPFILE/
DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 23:00:00  N    dr1orcl19.dat => +DATA/ORCL19/DATAGUARDCONFIG/ORCL19.275.1023297961
ASMCMD> ls -l PASSWORD/
Type      Redund  Striped  Time             Sys  Name
PASSWORD  UNPROT  COARSE   OCT 26 23:00:00  Y    pwdorcl19.256.1022714087
ASMCMD> ls -l
Type             Redund  Striped  Time             Sys  Name
                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/
                                                   Y    95D86C40430C3E85E0533205A00A0EBA/
                                                   Y    CONTROLFILE/
                                                   Y    DATAFILE/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    ONLINELOG/
                                                   Y    PARAMETERFILE/
                                                   Y    PASSWORD/
                                                   Y    TEMPFILE/
DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 23:00:00  N    dr1orcl19.dat => +DATA/ORCL19/DATAGUARDCONFIG/ORCL19.275.1023297961
ASMCMD>
ASMCMD> cp PASSWORD/pwdorcl19.256.1022714087 /tmp
copying +DATA/ORCL19/PASSWORD/pwdorcl19.256.1022714087 -> /tmp/pwdorcl19.256.1022714087
ASMCMD> exit
[grid@orcl19p ~]$

As you saw, I saved the password file at /tmp folder.

At Standby ORCL19S

[root@orcl19s ~]# su - grid
Last login: Sun Nov  3 23:03:00 CET 2019
[grid@orcl19s ~]$ 
[grid@orcl19s ~]$
[grid@orcl19s ~]$ scp orcl19p:/tmp/pwdorcl19.256.1022714087 /tmp/pwdorcl19.256.1022714087
grid@orcl19p's password:
pwdorcl19.256.1022714087                                                                                                                                                                                   100% 2560     4.3MB/s   00:00
[grid@orcl19s ~]$
[grid@orcl19s ~]$
[grid@orcl19s ~]$
[grid@orcl19s ~]$ asmcmd
ASMCMD> ls -l +DATA/ORCL19S/
Type             Redund  Striped  Time             Sys  Name
                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/
                                                   Y    95D86C40430C3E85E0533205A00A0EBA/
                                                   Y    CONTROLFILE/
                                                   Y    DATAFILE/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    ONLINELOG/
                                                   Y    PARAMETERFILE/
                                                   Y    PASSWORD/
DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 19:00:00  N    dr1orcl19s.dat => +DATA/ORCL19S/DATAGUARDCONFIG/orcl19s.275.1023298021
PASSWORD         UNPROT  COARSE   NOV 02 15:00:00  N    orapworcl19s => +DATA/ORCL19S/PASSWORD/pwdorcl19s.268.1023290373
PARAMETERFILE    UNPROT  COARSE   NOV 03 22:00:00  N    spfile.ora => +DATA/ORCL19S/PARAMETERFILE/spfile.272.1023290247
ASMCMD> 
ASMCMD> pwcopy --dbuniquename orcl19s '/tmp/pwdorcl19.256.1022714087' '+DATA/ORCL19S/orapworcl19s' -f
ASMCMD-9453: failed to register password file as a CRS resource
copying /tmp/pwdorcl19.256.1022714087 -> +DATA/ORCL19S/orapworcl19s
ASMCMD-9453: failed to register password file as a CRS resource
ASMCMD>
ASMCMD>
ASMCMD> ls -l +DATA/ORCL19S/
Type             Redund  Striped  Time             Sys  Name
                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/
                                                   Y    95D86C40430C3E85E0533205A00A0EBA/
                                                   Y    CONTROLFILE/
                                                   Y    DATAFILE/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    ONLINELOG/
                                                   Y    PARAMETERFILE/
                                                   Y    PASSWORD/
DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 19:00:00  N    dr1orcl19s.dat => +DATA/ORCL19S/DATAGUARDCONFIG/orcl19s.275.1023298021
PASSWORD         UNPROT  COARSE   NOV 03 23:00:00  N    orapworcl19s => +DATA/ORCL19S/PASSWORD/pwdorcl19s.268.1023405041
PARAMETERFILE    UNPROT  COARSE   NOV 03 22:00:00  N    spfile.ora => +DATA/ORCL19S/PARAMETERFILE/spfile.272.1023290247
ASMCMD>
ASMCMD> exit
[grid@orcl19s ~]$

As you saw above, the file from primary was copied and overwritten the old.

Bounce everything

After doing this it is time to bounce the primary database to reload the parameters and start the standby database to read password file and new parameters

At Primary ORCL19

[oracle@orcl19p ~]$ srvctl stop database -d orcl19s -o immediate
[oracle@orcl19p ~]$ srvctl start database -d orcl19
[oracle@orcl19p ~]$

At Standby ORCL19S

[oracle@orcl19s ~]$ srvctl start database -d orcl19s
[oracle@orcl19s ~]$
[oracle@orcl19s ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 3 23:13:22 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> col username format a20
SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users;

USERNAME             SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM
-------------------- ----- ----- ----- ----- ----- -----
SYS                  TRUE  TRUE  FALSE FALSE FALSE FALSE
VPCZDLRA             FALSE TRUE  FALSE FALSE FALSE FALSE

SQL>

Checking ZERO RPO

To check if the conf is OK, you can do a simple archivelog at primary and verify if everything was OK for both sites.

At Primary ORCL19

[oracle@orcl19p ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 4 00:46:46 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     117
Next log sequence to archive   119
Current log sequence           119
SQL> alter system archive log current;

System altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
[oracle@orcl19p ~]$ rman target=/ catalog=vpczdlra/oracle@zdlras1

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Nov 4 00:47:04 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19 (DBID=324042554)
connected to recovery catalog database
PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current
PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current

RMAN> list backup of archivelog sequence 118;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1965    8.00K      SBT_TAPE    00:00:01     04/11/2019 00:45:49
        BP Key: 1966   Status: AVAILABLE  Compressed: YES  Tag: TAG20191104T004548
        Handle: $RSCN_1_RTIM_1022714106_THRD_1_SEQ_118_CTKEY_1946_BACKUP   Media:

  List of Archived Logs in backup set 1965
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    118     2137116    04/11/2019 00:44:35 2137128    04/11/2019 00:44:39

RMAN> exit


Recovery Manager complete.
[oracle@orcl19p ~]$

At Standby ORCL19S

[oracle@orcl19s ~]$ rman target=/ catalog=vpczdlra/oracle@zdlras2

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Nov 4 00:48:39 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19 (DBID=324042554, not open)
connected to recovery catalog database
PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current
PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current

RMAN> list backup of archivelog sequence 118;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
2240    8.00K      SBT_TAPE    00:00:00     04/11/2019 00:45:35
        BP Key: 2241   Status: AVAILABLE  Compressed: YES  Tag: TAG20191104T004535
        Handle: $RSCN_1_RTIM_1022714106_THRD_1_SEQ_118_CTKEY_2228_BACKUP   Media:

  List of Archived Logs in backup set 2240
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    118     2137116    04/11/2019 00:44:35 2137128    04/11/2019 00:44:39

RMAN> exit


Recovery Manager complete.
[oracle@orcl19s ~]$

So, as you can see above. The ZDLRAS1 received the onlinelog (sequence 118) from ORCL19. And the ZDLRAS2 received a copy of it (standby redo log) from ORCL19S. This means that now you have zero RPO from primary and standby.

Broker config

From 12.1 and new versions, you can have recovery_appliance configured directly from broker when using DG. This executes the configuration for log_archive_dest parameters automatically when you add the ZDLRA.

Basic config

Pretty same config that was made as manual config before.

At Primary ORCL19

[oracle@orcl19p ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 9 16:24:27 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL>
SQL> col username format a20
SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users;

USERNAME             SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM
-------------------- ----- ----- ----- ----- ----- -----
SYS                  TRUE  TRUE  FALSE FALSE FALSE FALSE

SQL> show parameter redo_transport_user;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
redo_transport_user                  string
SQL> alter system set redo_transport_user='VPCZDLRA' scope = spfile SID='*';

System altered.

SQL> 
SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> create user VPCZDLRA identified by oracle;

User created.

SQL> grant sysoper to VPCZDLRA;

Grant succeeded.

SQL> alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users;

USERNAME             SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM
-------------------- ----- ----- ----- ----- ----- -----
SYS                  TRUE  TRUE  FALSE FALSE FALSE FALSE
VPCZDLRA             FALSE TRUE  FALSE FALSE FALSE FALSE

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
[oracle@orcl19p ~]$

At Standby ORCL19S

[oracle@orcl19s ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 9 16:25:09 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL>
SQL> alter system set redo_transport_user='VPCZDLRA' scope = spfile SID='*';

System altered.

SQL> 
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
[oracle@orcl19s dbs]$

Password file

At Primary ORCL19

[grid@orcl19p ~]$ asmcmd
ASMCMD> cd +DATA/ORCL19/
ASMCMD> ls -l
Type             Redund  Striped  Time             Sys  Name
                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/
                                                   Y    95D86C40430C3E85E0533205A00A0EBA/
                                                   Y    CONTROLFILE/
                                                   Y    DATAFILE/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    ONLINELOG/
                                                   Y    PARAMETERFILE/
                                                   Y    PASSWORD/
                                                   Y    TEMPFILE/
DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 23:00:00  N    dr1orcl19.dat => +DATA/ORCL19/DATAGUARDCONFIG/ORCL19.275.1023297961
ASMCMD> ls -l PASSWORD/
Type      Redund  Striped  Time             Sys  Name
PASSWORD  UNPROT  COARSE   OCT 26 23:00:00  Y    pwdorcl19.256.1022714087
ASMCMD> ls -l
Type             Redund  Striped  Time             Sys  Name
                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/
                                                   Y    95D86C40430C3E85E0533205A00A0EBA/
                                                   Y    CONTROLFILE/
                                                   Y    DATAFILE/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    ONLINELOG/
                                                   Y    PARAMETERFILE/
                                                   Y    PASSWORD/
                                                   Y    TEMPFILE/
DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 23:00:00  N    dr1orcl19.dat => +DATA/ORCL19/DATAGUARDCONFIG/ORCL19.275.1023297961
ASMCMD>
ASMCMD> cp PASSWORD/pwdorcl19.256.1022714087 /tmp
copying +DATA/ORCL19/PASSWORD/pwdorcl19.256.1022714087 -> /tmp/pwdorcl19.256.1022714087
ASMCMD> exit
[grid@orcl19p ~]$

At Standby ORCL19S

[root@orcl19s ~]# su - grid
Last login: Sun Nov  3 23:03:00 CET 2019
[grid@orcl19s ~]$ 
[grid@orcl19s ~]$
[grid@orcl19s ~]$ scp orcl19p:/tmp/pwdorcl19.256.1022714087 /tmp/pwdorcl19.256.1022714087
grid@orcl19p's password:
pwdorcl19.256.1022714087                                                                                                                                                                                   100% 2560     4.3MB/s   00:00
[grid@orcl19s ~]$
[grid@orcl19s ~]$
[grid@orcl19s ~]$
[grid@orcl19s ~]$ asmcmd
ASMCMD> ls -l +DATA/ORCL19S/
Type             Redund  Striped  Time             Sys  Name
                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/
                                                   Y    95D86C40430C3E85E0533205A00A0EBA/
                                                   Y    CONTROLFILE/
                                                   Y    DATAFILE/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    ONLINELOG/
                                                   Y    PARAMETERFILE/
                                                   Y    PASSWORD/
DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 19:00:00  N    dr1orcl19s.dat => +DATA/ORCL19S/DATAGUARDCONFIG/orcl19s.275.1023298021
PASSWORD         UNPROT  COARSE   NOV 02 15:00:00  N    orapworcl19s => +DATA/ORCL19S/PASSWORD/pwdorcl19s.268.1023290373
PARAMETERFILE    UNPROT  COARSE   NOV 03 22:00:00  N    spfile.ora => +DATA/ORCL19S/PARAMETERFILE/spfile.272.1023290247
ASMCMD> 
ASMCMD> pwcopy --dbuniquename orcl19s '/tmp/pwdorcl19.256.1022714087' '+DATA/ORCL19S/orapworcl19s' -f
ASMCMD-9453: failed to register password file as a CRS resource
copying /tmp/pwdorcl19.256.1022714087 -> +DATA/ORCL19S/orapworcl19s
ASMCMD-9453: failed to register password file as a CRS resource
ASMCMD>
ASMCMD>
ASMCMD> ls -l +DATA/ORCL19S/
Type             Redund  Striped  Time             Sys  Name
                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/
                                                   Y    95D86C40430C3E85E0533205A00A0EBA/
                                                   Y    CONTROLFILE/
                                                   Y    DATAFILE/
                                                   Y    DATAGUARDCONFIG/
                                                   Y    ONLINELOG/
                                                   Y    PARAMETERFILE/
                                                   Y    PASSWORD/
DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 19:00:00  N    dr1orcl19s.dat => +DATA/ORCL19S/DATAGUARDCONFIG/orcl19s.275.1023298021
PASSWORD         UNPROT  COARSE   NOV 03 23:00:00  N    orapworcl19s => +DATA/ORCL19S/PASSWORD/pwdorcl19s.268.1023405041
PARAMETERFILE    UNPROT  COARSE   NOV 03 22:00:00  N    spfile.ora => +DATA/ORCL19S/PARAMETERFILE/spfile.272.1023290247
ASMCMD>
ASMCMD> exit
[grid@orcl19s ~]$

Wallet Configuration

When using broker configuration, it is recommended to add both credential entries, of ZDLRA from primary and standby, in the wallet from both databases.

At Primary ORCL19

[oracle@orcl19p ~]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -createCredential zdlras2-scan:1521/zdlras2:VPCZDLRA vpczdlra oracle
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

[oracle@orcl19p ~]$
[oracle@orcl19p ~]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

List credential (index: connect_string username)
2: zdlras2-scan:1521/zdlras2:VPCZDLRA vpczdlra
1: zdlras1-scan:1521/zdlras1:VPCZDLRA vpczdlra
[oracle@orcl19p ~]$

At Standby ORCL19S

[oracle@orcl19s ~]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -createCredential zdlras1-scan:1521/zdlras1:VPCZDLRA vpczdlra oracle
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

[oracle@orcl19s ~]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

List credential (index: connect_string username)
2: zdlras1-scan:1521/zdlras1:VPCZDLRA vpczdlra
1: zdlras2-scan:1521/zdlras2:VPCZDLRA vpczdlra
[oracle@orcl19s ~]$

Bounce everything

At Standby ORCL19S

[oracle@orcl19s ~]$ srvctl stop database -d orcl19s -o immediate
[oracle@orcl19s ~]$

At Primary ORCL19

[oracle@orcl19p ~]$ srvctl stop database -d orcl19 -o immediate
[oracle@orcl19p ~]$ srvctl start database -d orcl19
[oracle@orcl19p ~]$

At Standby ORCL19S

[oracle@orcl19s ~]$ srvctl start database -d orcl19s
[oracle@orcl19s ~]$

Broker config

Now I can add the ZDLRA at the config. First, look the config:

[oracle@orcl19p ~]$ dgmgrl sys/oracle@orcl19
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Nov 9 16:26:26 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "ORCL19"
Connected as SYSDBA.
DGMGRL>
DGMGRL> show configuration verbose;

Configuration - orcl19

  Protection Mode: MaxAvailability
  Members:
  orcl19  - Primary database
    orcl19s - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '0'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'orcl19_CFG'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL>

Now, add both ZDLRA entries. Look that connect identifier it is the same as the credential name. This is a key point too.

DGMGRL> add recovery_appliance zdlras1 as connect identifier is 'zdlras1-scan:1521/zdlras1:VPCZDLRA';
Recovery Appliance "zdlras1" added
DGMGRL> add recovery_appliance zdlras2 as connect identifier is 'zdlras2-scan:1521/zdlras2:VPCZDLRA';
Recovery Appliance "zdlras2" added
DGMGRL> show configuration verbose;

Configuration - orcl19

  Protection Mode: MaxAvailability
  Members:
  orcl19  - Primary database
    orcl19s - Physical standby database
      zdlras2 - Recovery appliance (disabled)
        ORA-16905: The member was not enabled yet.

    zdlras1 - Recovery appliance (disabled)
      ORA-16905: The member was not enabled yet.

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '0'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'
    ConfigurationWideServiceName    = 'orcl19_CFG'

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS

DGMGRL>

Look that now I have both ZDLRA in the config, but they did not enable yet. Before I enable it, I need to configure the RedoRoutes. If I do not do that, both ZDLRA will be enabled for the primary site as log_archive_dest. This is a key point too.

DGMGRL> edit database orcl19 set property RedoRoutes = '(orcl19 : orcl19s sync, zdlras1 async)( orcl19s : zdlras1 async )';
Property "redoroutes" updated
DGMGRL> edit database orcl19s set property RedoRoutes = '(orcl19s : orcl19 sync, zdlras2 async)( orcl19 : zdlras2 async )';
Property "redoroutes" updated
DGMGRL>

Just to explain what this means:

  • RedoRoutes for ORCL19: For ORCL19 database, when/while ORCL19 is the primary database, ORCL19 it will send the redo to ORCL19S in sync mode, and to ZDLRAS1 in async mode. The second part means that when/while ORCL19S database is the primary, ORCL19 will send the redo to ZDLRAS1 in async mode.
  • RedoRoutes for ORCL19S: For ORCL19S database, when/while ORCL19S is the primary database, ORCL19S it will send the redo to ORCL19 in sync mode, and to ZDLRAS2 in async mode. The second part means that when/while ORCL19 database is the primary, ORCL19S will send the redo to ZDLRAS2 in async mode.

Now, I need to enable ZDLRA’s. I will do one by one:

DGMGRL> show configuration;

Configuration - orcl19

  Protection Mode: MaxAvailability
  Members:
  orcl19  - Primary database
    orcl19s - Physical standby database
      zdlras2 - Recovery appliance (disabled)
        ORA-16905: The member was not enabled yet.

    zdlras1 - Recovery appliance (disabled)
      ORA-16905: The member was not enabled yet.

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 35 seconds ago)

DGMGRL>
DGMGRL> enable recovery_appliance zdlras1;
Enabled.
DGMGRL>

And at primary (ORCL19) alertlog will show (look the parameter set):

2019-11-09T21:36:24.189975+01:00
Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(ORCL19,orcl19s,zdlras1)']
2019-11-09T21:36:24.205453+01:00
ALTER SYSTEM SET log_archive_config='dg_config=(ORCL19,orcl19s,zdlras1)' SCOPE=BOTH;
2019-11-09T21:36:36.625928+01:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
2019-11-09T21:36:36.638226+01:00
RSM0 (PID:9918): Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST [krsd.c:18157]
2019-11-09T21:36:36.650443+01:00
ALTER SYSTEM SET log_archive_dest_3='service="zdlras1-scan:1521/zdlras1:VPCZDLRA"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="zdlras1" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;
2019-11-09T21:36:36.670412+01:00
ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH;
2019-11-09T21:36:36.670547+01:00
ALTER SYSTEM ARCHIVE LOG
2019-11-09T21:36:36.683698+01:00
Thread 1 cannot allocate new log, sequence 242
Checkpoint not complete
  Current log# 3 seq# 241 mem# 0: +DATA/ORCL19/ONLINELOG/group_3.260.1022714131
  Current log# 3 seq# 241 mem# 1: +RECO/ORCL19/ONLINELOG/group_3.259.1022714135
2019-11-09T21:36:38.922616+01:00
LGWR (PID:7900): SRL selected to archive T-1.S-242
LGWR (PID:7900): SRL selected for T-1.S-242 for LAD:2
2019-11-09T21:36:38.934895+01:00
Thread 1 advanced to log sequence 242 (LGWR switch)
  Current log# 1 seq# 242 mem# 0: +DATA/ORCL19/ONLINELOG/group_1.258.1022714111
  Current log# 1 seq# 242 mem# 1: +RECO/ORCL19/ONLINELOG/group_1.257.1022714115
2019-11-09T21:36:38.974260+01:00
RSM0 (PID:9918): Archived Log entry 588 added for T-1.S-241 ID 0x135c2429 LAD:1

And in alertlog from standby (ORCL19S):

2019-11-09T21:36:23.036628+01:00
Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(orcl19s,orcl19,zdlras1)']
2019-11-09T21:36:23.051110+01:00
ALTER SYSTEM SET log_archive_config='dg_config=(orcl19s,orcl19,zdlras1)' SCOPE=BOTH;
2019-11-09T21:36:38.755320+01:00
 rfs (PID:22533): Standby controlfile consistent with primary
2019-11-09T21:36:38.770320+01:00
 rfs (PID:22533): Selected LNO:4 for T-1.S-242 dbid 324042554 branch 1022714106
2019-11-09T21:36:38.773001+01:00
MRP0 (PID:11871): Media Recovery Waiting for T-1.S-242 (in transit)
2019-11-09T21:36:38.776175+01:00
ARC0 (PID:9009): Archived Log entry 304 added for T-1.S-241 ID 0x135c2429 LAD:1
2019-11-09T21:36:39.836191+01:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 242 Reading mem 0
  Mem# 0: +DATA/ORCL19S/ONLINELOG/group_4.279.1023289067
  Mem# 1: +RECO/ORCL19S/ONLINELOG/group_4.269.1023289069

And now, the second ZDLRA:

DGMGRL> enable recovery_appliance zdlras2;
Enabled.
DGMGRL>

Again, at primary (ORCL19) alertlog:

2019-11-09T21:37:32.341087+01:00
Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(ORCL19,orcl19s,zdlras1,zdlras2)']
2019-11-09T21:37:32.353276+01:00
ALTER SYSTEM SET log_archive_config='dg_config=(ORCL19,orcl19s,zdlras1,zdlras2)' SCOPE=BOTH;
2019-11-09T21:37:40.827841+01:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
2019-11-09T21:37:41.943026+01:00
LGWR (PID:7900): SRL selected to archive T-1.S-243
LGWR (PID:7900): SRL selected for T-1.S-243 for LAD:2
2019-11-09T21:37:41.949207+01:00
Thread 1 advanced to log sequence 243 (LGWR switch)
  Current log# 2 seq# 243 mem# 0: +DATA/ORCL19/ONLINELOG/group_2.259.1022714121
  Current log# 2 seq# 243 mem# 1: +RECO/ORCL19/ONLINELOG/group_2.258.1022714125
2019-11-09T21:37:41.981177+01:00
ARC1 (PID:10098): Archived Log entry 591 added for T-1.S-242 ID 0x135c2429 LAD:1

And in the alertlog from standby (ORCL19S):

2019-11-09T21:37:31.175857+01:00
Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(orcl19s,orcl19,zdlras1,zdlras2)']
2019-11-09T21:37:31.193280+01:00
ALTER SYSTEM SET log_archive_config='dg_config=(orcl19s,orcl19,zdlras1,zdlras2)' SCOPE=BOTH;
2019-11-09T21:37:37.424268+01:00
RSM0 (PID:10393): Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST [krsd.c:18157]
2019-11-09T21:37:37.438735+01:00
ALTER SYSTEM SET log_archive_dest_2='service="zdlras2-scan:1521/zdlras2:VPCZDLRA"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="zdlras2" net_timeout=30','valid_for=(standby_logfile,all_roles)' SCOPE=BOTH;
2019-11-09T21:37:37.476545+01:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
2019-11-09T21:37:41.778391+01:00
MRP0 (PID:11871): Media Recovery Waiting for T-1.S-243 (in transit)
2019-11-09T21:37:41.791532+01:00
ARC0 (PID:9009): Archived Log entry 312 added for T-1.S-242 ID 0x135c2429 LAD:1
2019-11-09T21:37:41.793625+01:00
 rfs (PID:22533): Standby controlfile consistent with primary
2019-11-09T21:37:41.800377+01:00
 rfs (PID:22533): Selected LNO:5 for T-1.S-243 dbid 324042554 branch 1022714106
2019-11-09T21:37:42.826630+01:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 243 Reading mem 0
  Mem# 0: +DATA/ORCL19S/ONLINELOG/group_5.278.1023289071
  Mem# 1: +RECO/ORCL19S/ONLINELOG/group_5.268.1023289073

And now the conf reports:

DGMGRL> show configuration;

Configuration - orcl19

  Protection Mode: MaxAvailability
  Members:
  orcl19  - Primary database
    orcl19s - Physical standby database
      zdlras2 - Recovery appliance (receiving current redo)
    zdlras1 - Recovery appliance

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 54 seconds ago)

DGMGRL>

As you can see, the primary is cascading the redo to ORCL19S and ZDLRAS1. And the ORCL19S is cascading the redo (standby redo) to ZDLRAS2.

Checking ZERO RPO

To check if everything is fine, and we have zero RPO in both sited I made a simple archivelog at the primary site and check the cascade of events.

At Primary ORCL19

[oracle@orcl19p ~]$ rman target=/ catalog=vpczdlra/oracle@zdlras1

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Nov 9 21:43:25 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19 (DBID=324042554)
connected to recovery catalog database
PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current
PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current

RMAN> alter system archive log current;

Statement processed

RMAN> list copy of archivelog all completed after "sysdate - 5/1440";

List of Archived Log Copies for database with db_unique_name ORCL19
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
3296    1    243     A 09/11/2019 21:37:41
        Name: +RECO/ORCL19/ARCHIVELOG/2019_11_09/thread_1_seq_243.389.1023918423


RMAN> list backup of archivelog all completed after "sysdate - 5/1440";


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
3304    322.00K    SBT_TAPE    00:00:02     09/11/2019 21:48:02
        BP Key: 3305   Status: AVAILABLE  Compressed: YES  Tag: TAG20191109T214800
        Handle: $RSCN_1_RTIM_1022714106_THRD_1_SEQ_243_CTKEY_3206_BACKUP   Media:

  List of Archived Logs in backup set 3304
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    243     3082409    09/11/2019 21:37:41 3083705    09/11/2019 21:47:03

RMAN>

At Standby ORCL19S

[oracle@orcl19s ~]$ rman target=/ catalog=vpczdlra/oracle@zdlras2

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Nov 9 21:44:58 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19 (DBID=324042554, not open)
connected to recovery catalog database
PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current
PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current

RMAN> list copy of archivelog all completed after "sysdate - 5/1440";

List of Archived Log Copies for database with db_unique_name ORCL19S
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - -------------------
3428    1    243     A 09/11/2019 21:37:41
        Name: +RECO/ORCL19S/ARCHIVELOG/2019_11_09/thread_1_seq_243.371.1023918423


RMAN> list backup of archivelog all completed after "sysdate - 5/1440";


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
3419    322.00K    SBT_TAPE    00:00:00     09/11/2019 21:47:10
        BP Key: 3420   Status: AVAILABLE  Compressed: YES  Tag: TAG20191109T214710
        Handle: $RSCN_1_RTIM_1022714106_THRD_1_SEQ_243_CTKEY_3306_BACKUP   Media:

  List of Archived Logs in backup set 3419
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    243     3082409    09/11/2019 21:37:41 3083705    09/11/2019 21:47:03

RMAN>

As you can see here, the sequence 243 was created at primary, sent by DG to ORCL19S. And ZDLRAS1 created the backup for archivelog automatically, and the same was made by ZDLRAS2.

Multiple levels of protection

The idea of this post was to show how to protect a big environment with ZDLRA, reaching ZERO RPO for both sites, and going beyond the protection defined by Oracle MAA docs. If you look closely, the environment that I showed here has zero RPO and zero RTO. You will lose data only if you have a catastrophic failure in both sites (but you can protect even more if you use tape backup offloads from ZDLRA).

As told before, these critical/extreme critical environments need to follow some strict rules for data protection. Adding ZDLRA for it, provide an additional level of protection and allow you to use some features like incremental backup strategy.

 

Disclaimer: “The postings on this site are my own and don’t necessarily represent my actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications were removed to allow reach the generic audience and to be useful for the community. Post protected by copyright.”

8 thoughts on “ZDLRA, Multi-site protection – ZERO RPO for Primary and Standby

  1. Bryan Grenn

    This is a fantastic article on how to manually configure both the Primary Database and the Dataguard Database to get you as close to ZERO RPO as possible.

    I really appreciate you showing all the Dataguard Broker pieces of the configuration.

    OEM 13 will go through and configure all of this for you, but it requires Dataguard Broker to be configured.

    You can clearly see how Dataguard broker helps make the configuration of both Primary and Dataguard Database backups possible through OEM.

    Reply
  2. Grzegorz Goryszewski

    Thank You, one of the best and comprehensive article regarding ZDLRA I’ve found.

    Reply
  3. Pingback: ZDLRA, Dataguard, Archivelogs and RMAN-08137 | Fernando Simon

  4. Jey

    Hello, this is the best article so far that explains dataguard setup with ZDLRA.

    You explained the redoroutes for broker configuration nicely and I am going to use these steps to tryout in my environment.

    thanks and appreciated!

    Reply
  5. Suhail Moinuddin

    Thank You for sharing this amazing post. It was comprehensive and No.of the best articles regarding ZDLRA multi sites configuration.

    Reply
  6. Pingback: ZDLRA, Replication | Fernando Simon

Leave a Reply

Your email address will not be published. Required fields are marked *