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.”
