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.
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:
Install ZDLRA library
Create Wallet
Configure/Test tns entry to ZDLRA
Adjust sqlnet.ora file (this is needed because 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:
Install ZDLRA library
Create Wallet
Configure/Test tns entry to ZDLRA
Adjust sqlnet.ora file (this is needed because 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:
log_archive_config: Check that for primary it is ZDLRAS1, and ZDLRAS2 for standby
log_archive_dest_3: Again, each site points to the own ZDLRA. And look the type defined at VALID_FOR
Create the user for redo_transport_user inside database.
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
[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 ~]$
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 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 : zdlras2 async )';
Property "redoroutes" updated
DGMGRL> edit database orcl19s set property RedoRoutes = '(orcl19s : orcl19 sync, zdlras2 async)( orcl19 : zdlras1 async )';
Property "redoroutes" updated
DGMGRL>
Just to explain what this means:
RedoRoutes for ORCL19: When ORCL19 is the primary database, it will send the redo to ORCL19S in sync mode, and to ZDLRAS1 in async mode. And at the same time, the ORCL19S will send their redo (stand by redo – since it is standby) to ZDLRAS2 in async mode.
RedoRoutes for ORCL19S: When ORCL19S is primary database, it will send the redo to ORCL19 in sync mode, and to ZDLRAS2 in async mode. And at the same time, the ORCL19 will send their redo (stand by redo – since it is standby) to ZDLRAS1 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 stabdby (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.”
