ZDLRA, Real-Time REDO and RPO ZERO
The idea for Real-Time Redo is to reach zero RPO for every kind of database and this includes ones with and without DG. As you can see in my last post, where I showed how to configure Real-Time Redo for one database, some little steps need to be executed and they are pretty similar than a remote destination for archivelog for DG.
But if you noticed, the configuration for the remote destination was defined as ASYNC, and hinted like that at ZDLRA docs (“Protection of Ongoing Transactions” or at “How Real-Time Redo Transport Works”). In the same post, I suggested as “controversial” because the ASYNC does not guarantee the RPO zero.
You can see more in the DataGuard docs at (Oracle Data Guard Protection Modes and Oracle Data Guard Concepts and Administration), but the resume it is:
ASYNC: The primary database does not wait for the response from a remote destination.
SYNC/NOAFIRM: The Primary database holds commit until the remote destination report that received the redo data. It does not wait until the remote site report that wrote the data in the disc.
SYNC/AFFIRM: The primary database holds commit until the remote destination report that received redo data and wrote it at the disk.
You can read with more details the difference here: Best Practices for Synchronous Redo Transport and Best Practices for Asynchronous Redo Transport.
The idea is simple, if you use ASYNC, there is no guarantee for zero data loss between the primary database and the remote destination.
ZDLRA Protection
If needed, the configuration to reach zero RPO, when using ZDLRA, it is simple. You just need to put the archive destination in SYNC. Simple like that. Before put archivelog destination in SYNC, I will do an example using ASYNC to show some features of real-time redo.
Saving crashes
One interesting point is that ZDLRA generates for you a new archivelog in case of a primary database crash. As the documentation says:
If the redo stream terminates unexpectedly, then the Recovery Appliance can close the incoming redo stream and create a partial archived redo log file backup, thereby protecting transactions up to the last change that the appliance received.
So, even not using DG or sync, you can protect it until the last transaction. In real life it is this (I will simulate a catastrophic failure; check the current archivelog, and kill the instances process after that; at the end, check how far the protection was):
Current archivelog:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 29
Next log sequence to archive 31
Current log sequence 31
SQL>
SQL> ALTER SYSTEM ARCHIVE LOG current;
System altered.
SQL>
SQL> show parameter dest_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_2 string
log_archive_dest_2 string SERVICE="zeros1ig-scan.simo.fz
net.org:1521/zeros1:dedicated"
ASYNC NOAFFIRM delay=0 option
al compression=disable max_fai
lure=1 max_connections=1 reope
n=10 DB_UNIQUE_NAME=zeros1 net
_timeout=8 group=1 priority=1
VALID_FOR=(ONLINE_LOGFILE,ALL_
ROLES)
log_archive_dest_20 string
…
…
Backups of archivelog (look the “Next Time”)
RMAN> list backup of archivelog sequence 31;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
50958158 1.12M SBT_TAPE 00:00:00 2019-10-18_19-28-21
BP Key: 50958159 Status: AVAILABLE Compressed: YES Tag: TAG20191018T222821
Handle: $RSCN_1_RTIM_1022003674_THRD_1_SEQ_31_CTKEY_50958151_BACKUP Media:
List of Archived Logs in backup set 50958158
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 31 1025023 2019-10-18_19-27-40 1025761 2019-10-18_19-28-20
RMAN> list backup of archivelog sequence 32;
specification does not match any backup in the repository
RMAN>
Killing the instance:
[oracle@exac1vm01-ORAD18]$ ps -ef |grep pmon_ORAD18
oracle 31646 1 0 22:27 ? 00:00:00 ora_pmon_ORAD18
oracle 37116 396809 0 22:29 pts/2 00:00:00 grep pmon_ORAD18
[oracle@exac1vm01-ORAD18]$ ps -ef |grep smon_ORAD18
oracle 31731 1 0 22:27 ? 00:00:00 ora_smon_ORAD18
oracle 37213 396809 0 22:29 pts/2 00:00:00 grep smon_ORAD18
[oracle@exac1vm01-ORAD18]$ ps -ef |grep lgwr_ORAD18
oracle 31724 1 0 22:27 ? 00:00:00 ora_lgwr_ORAD18
oracle 37245 396809 0 22:29 pts/2 00:00:00 grep lgwr_ORAD18
[oracle@exac1vm01-ORAD18]$ ps -ef |grep dbw0_ORAD18
oracle 31722 1 0 22:27 ? 00:00:00 ora_dbw0_ORAD18
oracle 40104 396809 0 22:29 pts/2 00:00:00 grep dbw0_ORAD18
[oracle@exac1vm01-ORAD18]$ ps -ef |grep arc* |grep ORAD18
oracle 31805 1 0 22:27 ? 00:00:00 ora_mark_ORAD18
oracle 32021 1 0 22:27 ? 00:00:00 ora_arc0_ORAD18
oracle 32042 1 0 22:27 ? 00:00:00 ora_arc1_ORAD18
oracle 32050 1 0 22:27 ? 00:00:00 ora_arc2_ORAD18
oracle 32057 1 0 22:27 ? 00:00:00 ora_arc3_ORAD18
[oracle@exac1vm01-ORAD18]$
[oracle@exac1vm01-ORAD18]$
[oracle@exac1vm01-ORAD18]$
[oracle@exac1vm01-ORAD18]$ date
Fri Oct 18 22:30:32 CEST 2019
[oracle@exac1vm01-ORAD18]$ kill -9 31646 31731 31724 31722 32021 32042 32050 32057
[oracle@exac1vm01-ORAD18]$
#####
At alertlog:
2019-10-18T22:30:33.160912+02:00
RMS0 (ospid: 31718): terminating the instance due to ORA error 472
Cause - 'Instance is being terminated due to fatal process PMON being dead.'
2019-10-18T22:30:33.290189+02:00
System state dump requested by (instance=1, osid=31718 (RMS0)), summary=[abnormal instance termination]. error - 'Instance is terminating.
'
System State dumped to trace file /u01/app/oracle/diag/rdbms/orad18/ORAD18/trace/ORAD18_diag_31692_20191018223033.trc
2019-10-18T22:30:34.378356+02:00
Dumping diagnostic data in directory=[cdmp_20191018223033], requested by (instance=1, osid=31718 (RMS0)), summary=[abnormal instance termination].
2019-10-18T22:30:35.642670+02:00
Instance terminated by RMS0, pid = 31718
Above look the hour that I killed the instance.
How Far it Was
[oracle@exac1vm01-ORAD18]$ export ORACLE_SID=SIMON
[oracle@exac1vm01-SIMON]$ rman target / catalog vpcusr/welcome1@zeros1ig-scan:1521/zeros1:dedicated
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Oct 18 22:31:39 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> startup force nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/18.0.0/dbhome_1/dbs/initSIMON.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1073740616 bytes
Fixed Size 8665928 bytes
Variable Size 281018368 bytes
Database Buffers 775946240 bytes
Redo Buffers 8110080 bytes
RMAN> set dbid 2464352672;
executing command: SET DBID
database name is "ORAD18" and DBID is 2464352672
RMAN> list backup of archivelog sequence 32;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
50958220 72.50K SBT_TAPE 00:00:00 2019-10-18_22-30-42
BP Key: 50958221 Status: AVAILABLE Compressed: YES Tag: TAG20191018T223042
Handle: $RSCN_1_RTIM_1022003674_THRD_1_SEQ_32_CTKEY_50958156_BACKUP Media:
List of Archived Logs in backup set 50958220
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1025761 2019-10-18_22-28-20 1026104 2019-10-18_22-30-32
RMAN>
Above you can see that now I have at catalog one backup of archivelog sequence number 32. And the “Next Time” for it is 22:30:32 that was the same moment that I killed the instance. So, the ZDLRA protected until the last point before the crash (even in ASYNC mode).
One little explanation. As you saw, I opened one new instance (called SIMON) to check the catalog because I wanted to avoid that when opening the same instance (ORAD18) the partial archivelog generated by ZDLRA was removed because it believes that database returned. This is explained in the doc:
If the protected database crashes, redo data received from the current redo log group until the time of the crash is backed up at the Recovery Appliance as a “partial” archived redo log. If the protected database is reopened, crash recovery of the protected database will complete the current redo log group at the time of the crash, and the completed redo log will be re-shipped to the Recovery Appliance through the automatic Data Guard Gap fetching feature. The “complete” archived redo log will be used in any future restore/recover operations instead of the previously backed up “partial” archived redo log.
Saving crashed, now with load
Using the example above you can argue that ZDLRA reached RPO zero just because I don’t have load over the database. And, being worst, without SYNC you can’t guarantee zero data loss. Yes, I agree with you for both (and I already exposed thins in my last post and in the introduction of this post). To show you how to use ZDLRA with SYNC for archive log destination.
Sync mode
Putting the destination in SYNC mode. Just changed to SYNC NOAFFIRM:
[oracle@exac1vm01-ORAD18]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Oct 18 23:04:25 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> ALTER SYSTEM SET log_archive_dest_2='SERVICE="zeros1ig-scan.simo.fznet.org:1521/zeros1:dedicated" SYNC NOAFFIRM delay=0 optional compression=disable max_failure=1 max_connections=1 reopen=10 DB_UNIQUE_NAME=zeros1 net_timeout=8 group=1 priority=1 VALID_FOR=(ONLINE_LOGFILE,ALL_ROLES)' scope = spfile sid = '*';
System altered.
SQL>
Current archivelog:
After bouncing the instance, checking the current archivelog and creating a new one:
SQL> alter system archive log current;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>
Backups of archivelog (look the “Next Time”)
Check that does not exist archivelog from the current redo log:
[oracle@exac1vm01-ORAD18]$ rman target / catalog vpcusr/welcome1@zeros1ig-scan:1521/zeros1:dedicated
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Oct 18 23:05:35 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORAD18 (DBID=2464352672)
connected to recovery catalog database
RMAN> list backup of archivelog sequence 4;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
50958759 41.00K SBT_TAPE 00:00:00 2019-10-18_23-05-23
BP Key: 50958760 Status: AVAILABLE Compressed: YES Tag: TAG20191018T230523
Handle: $RSCN_1129803_RTIM_1022011106_THRD_1_SEQ_4_CTKEY_50958752_BACKUP Media:
List of Archived Logs in backup set 50958759
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 4 1131626 2019-10-18_23-04-53 1131667 2019-10-18_23-05-09
RMAN> list backup of archivelog sequence 5;
specification does not match any backup in the repository
RMAN>
The simulate load I created a table with 3 columns and put two sessions loading data and generating a huge number of commits. After every insert, I commit the data and show the current time for each insert. Look at the sessions:
[oracle@exac1vm01-ORAD18]$ for i in {1..100000}
> do
> echo "Insert Data $i - `date +%d-%m-%Y-%H%M%S`"
> sqlplus -s / as sysdba<<EOF
> set heading on feedback on;
> insert into testIns(c1, c2, c3) values ($i, sysdate, 'Loop');
> commit;
> EOF
> done
Insert Data 1 - 18-10-2019-230723
1 row created.
Commit complete.
Insert Data 2 - 18-10-2019-230723
1 row created.
Commit complete.
Insert Data 3 - 18-10-2019-230723
1 row created.
Commit complete.
Insert Data 4 - 18-10-2019-230724
1 row created.
Commit complete.
....
....
[oracle@exac1vm01-ORAD18]$
[oracle@exac1vm01-ORAD18]$ for i in {1..100000}
> do
> echo "Insert Data $i - `date +%d-%m-%Y-%H%M%S`"
> sqlplus -s / as sysdba<<EOF
> set heading on feedback on;
> insert into testIns(c1, c2, c3) values ($i, sysdate, 'Loop2');
> commit;
> EOF
> done
Insert Data 1 - 18-10-2019-230816
1 row created.
Commit complete.
Insert Data 2 - 18-10-2019-230816
1 row created.
Commit complete.
Insert Data 3 - 18-10-2019-230816
1 row created.
Commit complete.
...
...
Killing the instance
[oracle@exac1vm01-]$
[oracle@exac1vm01-]$
[oracle@exac1vm01-]$ ps -ef |grep pmon_ORAD18
oracle 93700 1 0 22:51 ? 00:00:00 ora_pmon_ORAD18
oracle 140193 357155 0 23:09 pts/1 00:00:00 grep pmon_ORAD18
[oracle@exac1vm01-]$ ps -ef |grep smon_ORAD18
oracle 93786 1 0 22:51 ? 00:00:00 ora_smon_ORAD18
oracle 140394 357155 0 23:09 pts/1 00:00:00 grep smon_ORAD18
[oracle@exac1vm01-]$ ps -ef |grep lgwr_ORAD18
oracle 93780 1 0 22:51 ? 00:00:00 ora_lgwr_ORAD18
oracle 140561 357155 0 23:09 pts/1 00:00:00 grep lgwr_ORAD18
[oracle@exac1vm01-]$ ps -ef |grep dbw0_ORAD18
oracle 93776 1 0 22:51 ? 00:00:00 ora_dbw0_ORAD18
oracle 140738 357155 0 23:09 pts/1 00:00:00 grep dbw0_ORAD18
[oracle@exac1vm01-]$ ps -ef |grep arc* |grep ORAD18
oracle 93835 1 0 22:51 ? 00:00:00 ora_mark_ORAD18
oracle 103814 1 0 22:55 ? 00:00:00 ora_arc0_ORAD18
oracle 103816 1 0 22:55 ? 00:00:00 ora_arc1_ORAD18
oracle 103818 1 0 22:55 ? 00:00:00 ora_arc2_ORAD18
oracle 103820 1 0 22:55 ? 00:00:00 ora_arc3_ORAD18
[oracle@exac1vm01-]$
[oracle@exac1vm01-]$ date
Fri Oct 18 23:09:44 CEST 2019
[oracle@exac1vm01-]$ kill -9 93700 93786 93780 93776 103814 103816 103818 103820
[oracle@exac1vm01-]$
Failed load
As expected, the two sessions failed:
...
...
Insert Data 1016 - 18-10-2019-230944
1 row created.
Commit complete.
Insert Data 1017 - 18-10-2019-230944
1 row created.
commit
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 142277
Session ID: 53 Serial number: 30197
...
...
Insert Data 646 - 18-10-2019-230944
1 row created.
Commit complete.
Insert Data 647 - 18-10-2019-230944
1 row created.
commit
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 142274
Session ID: 41 Serial number: 3186
Losing everything
So, the session loading string “Loop” apparently loaded until the value 1016 with success. And the session 2, loading with string “ Loop2” loaded until value 647. These values are the mark until we want to reach.
But to be more precise and catastrophic, I removed the data from filesystem/ASM too:
ASMCMD> cd +DATAC1/ORAD18/
ASMCMD> rm -rf CONTROLFILE/
ASMCMD> rm -rf DATAFILE/
ASMCMD> rm -rf ONLINELOG/
ASMCMD> rm -rf TEMPFILE/
ASMCMD> cd +RECOC1/ORAD18/
ASMCMD> rm -rf ARCHIVELOG/
ASMCMD> rm -rf AUTOBACKUP/
ASMCMD> rm -rf CONTROLFILE/
ASMCMD> rm -rf ONLINELOG/
ASMCMD>
Now, there is no escape. I need to recover completely the database to retrieve it until the last transaction. Let’s see if ZDLRA works 😉
Recovery Everything – ZERO RPO
Below I restore the database until the last transaction. The major steps follow the same as every full disaster recovery:
Restore Controlfile
Discover the last backup of archivelog
Restore and recover database until the found scn
Look at how it is:
[oracle@exac1vm01-ORAD18]$ rman target / catalog vpcusr/welcome1@zeros1ig-scan:1521/zeros1:dedicated
Recovery Manager: Release 18.0.0.0.0 - Production on Fri Oct 18 23:12:19 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN> startup nomount;
Oracle instance started
Total System Global Area 2147480376 bytes
Fixed Size 8659768 bytes
Variable Size 671088640 bytes
Database Buffers 1459617792 bytes
Redo Buffers 8114176 bytes
RMAN> list backup of controlfile completed after "sysdate - 10/1440";
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
50958685 Full 50.25M SBT_TAPE 00:00:01 2019-10-18_23-03-15
BP Key: 50958686 Status: AVAILABLE Compressed: NO Tag: TAG20191018T230314
Handle: c-2464352672-20191018-0b Media: Recovery Appliance (ZDLRAK)
Control File Included: Ckp SCN: 1131375 Ckp time: 2019-10-18_23-03-14
RMAN> restore controlfile from tag = TAG20191018T230314;
Starting restore at 2019-10-18_23-13-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=414 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: RA Library (ZDLRAK) SID=95344A4C604F5404E0538C43B20A52E2
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: restoring control file
channel ORA_SBT_TAPE_1: reading from backup piece c-2464352672-20191018-0b
channel ORA_SBT_TAPE_1: piece handle=c-2464352672-20191018-0b tag=TAG20191018T230314
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:02
output file name=+DATAC1/ORAD18/CONTROLFILE/current.741.1022012007
output file name=+RECOC1/ORAD18/CONTROLFILE/current.968.1022012009
Finished restore at 2019-10-18_23-13-28
RMAN> list backup of database completed after "sysdate - 15/1440";
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
50958689 Incr 1 40.00K SBT_TAPE 00:00:01 2019-10-18_23-03-11
BP Key: 50958690 Status: AVAILABLE Compressed: YES Tag: BKP-LEVEL1
Handle: VB$_1965521110_50958684I Media:
List of Datafiles in backup set 50958689
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 1 Incr 1131350 2019-10-18_23-03-10 NO +DATAC1/ORAD18/DATAFILE/system.894.1022011045
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
50958693 Incr 0 249.44M SBT_TAPE 00:00:01 2019-10-18_23-03-11
BP Key: 50958694 Status: AVAILABLE Compressed: YES Tag: BKP-LEVEL1
Handle: VB$_1965521110_50958684_1 Media:
List of Datafiles in backup set 50958693
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
1 0 Incr 1131350 2019-10-18_23-03-10 NO +DATAC1/ORAD18/DATAFILE/system.894.1022011045
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
50958710 Incr 1 40.00K SBT_TAPE 00:00:01 2019-10-18_23-03-10
BP Key: 50958711 Status: AVAILABLE Compressed: YES Tag: BKP-LEVEL1
Handle: VB$_1965521110_50958707I Media:
List of Datafiles in backup set 50958710
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
3 1 Incr 1131348 2019-10-18_23-03-09 NO +DATAC1/ORAD18/DATAFILE/undotbs1.496.1022011015
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
50958723 Incr 0 10.68M SBT_TAPE 00:00:01 2019-10-18_23-03-10
BP Key: 50958724 Status: AVAILABLE Compressed: YES Tag: BKP-LEVEL1
Handle: VB$_1965521110_50958707_3 Media:
List of Datafiles in backup set 50958723
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
3 0 Incr 1131348 2019-10-18_23-03-09 NO +DATAC1/ORAD18/DATAFILE/undotbs1.496.1022011015
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
50958727 Incr 1 40.00K SBT_TAPE 00:00:01 2019-10-18_23-03-13
BP Key: 50958728 Status: AVAILABLE Compressed: YES Tag: BKP-LEVEL1
Handle: VB$_1965521110_50958714I Media:
List of Datafiles in backup set 50958727
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
4 1 Incr 1131357 2019-10-18_23-03-12 NO +DATAC1/ORAD18/DATAFILE/users.353.1022011029
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
50958731 Incr 0 40.00K SBT_TAPE 00:00:01 2019-10-18_23-03-13
BP Key: 50958732 Status: AVAILABLE Compressed: YES Tag: BKP-LEVEL1
Handle: VB$_1965521110_50958714_4 Media:
List of Datafiles in backup set 50958731
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
4 0 Incr 1131357 2019-10-18_23-03-12 NO +DATAC1/ORAD18/DATAFILE/users.353.1022011029
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
50958736 Incr 1 40.00K SBT_TAPE 00:00:01 2019-10-18_23-03-12
BP Key: 50958737 Status: AVAILABLE Compressed: YES Tag: BKP-LEVEL1
Handle: VB$_1965521110_50958735I Media:
List of Datafiles in backup set 50958736
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
2 1 Incr 1131353 2019-10-18_23-03-11 NO +DATAC1/ORAD18/DATAFILE/sysaux.921.1022010999
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
50958740 Incr 0 93.84M SBT_TAPE 00:00:01 2019-10-18_23-03-12
BP Key: 50958741 Status: AVAILABLE Compressed: YES Tag: BKP-LEVEL1
Handle: VB$_1965521110_50958735_2 Media:
List of Datafiles in backup set 50958740
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
2 0 Incr 1131353 2019-10-18_23-03-11 NO +DATAC1/ORAD18/DATAFILE/sysaux.921.1022010999
RMAN> list backup of archivelog all completed after "sysdate - 15/1440";
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
50958604 2.00M SBT_TAPE 00:00:00 2019-10-18_23-02-52
BP Key: 50958605 Status: AVAILABLE Compressed: NO Tag: BKP-ARCH
Handle: 14uel9vc_1_1 Media: Recovery Appliance (ZDLRAK)
List of Archived Logs in backup set 50958604
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 2 1129806 2019-10-18_22-58-29 1131219 2019-10-18_23-02-50
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
50958618 1.15M SBT_TAPE 00:00:00 2019-10-18_23-02-54
BP Key: 50958619 Status: AVAILABLE Compressed: YES Tag: TAG20191018T230254
Handle: $RSCN_1129803_RTIM_1022011106_THRD_1_SEQ_2_CTKEY_50958445_BACKUP Media:
List of Archived Logs in backup set 50958618
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 2 1129806 2019-10-18_22-58-29 1131219 2019-10-18_23-02-50
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
50958754 67.50K SBT_TAPE 00:00:00 2019-10-18_23-05-00
BP Key: 50958755 Status: AVAILABLE Compressed: YES Tag: TAG20191018T230500
Handle: $RSCN_1129803_RTIM_1022011106_THRD_1_SEQ_3_CTKEY_50958595_BACKUP Media:
List of Archived Logs in backup set 50958754
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 3 1131219 2019-10-18_23-02-50 1131626 2019-10-18_23-04-53
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
50958759 41.00K SBT_TAPE 00:00:00 2019-10-18_23-05-23
BP Key: 50958760 Status: AVAILABLE Compressed: YES Tag: TAG20191018T230523
Handle: $RSCN_1129803_RTIM_1022011106_THRD_1_SEQ_4_CTKEY_50958752_BACKUP Media:
List of Archived Logs in backup set 50958759
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 4 1131626 2019-10-18_23-04-53 1131667 2019-10-18_23-05-09
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
50958809 2.05M SBT_TAPE 00:00:00 2019-10-18_23-10-12
BP Key: 50958810 Status: AVAILABLE Compressed: YES Tag: TAG20191018T231012
Handle: $RSCN_1129803_RTIM_1022011106_THRD_1_SEQ_5_CTKEY_50958757_BACKUP Media:
List of Archived Logs in backup set 50958809
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 5 1131667 2019-10-18_23-05-09 1135762 2019-10-18_23-09-44
RMAN>
RMAN> alter database mount;
released channel: ORA_DISK_1
released channel: ORA_SBT_TAPE_1
Statement processed
RMAN>
RMAN> run{
2> set until scn 1135762;
3> restore database;
4> recover database;
5> }
executing command: SET until clause
Starting restore at 2019-10-18_23-16-04
Starting implicit crosscheck backup at 2019-10-18_23-16-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 2019-10-18_23-16-05
Starting implicit crosscheck copy at 2019-10-18_23-16-05
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2019-10-18_23-16-05
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=419 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: RA Library (ZDLRAK) SID=953453F453376CA6E0538C43B20A62A0
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00001 to +DATAC1/ORAD18/DATAFILE/system.894.1022011045
channel ORA_SBT_TAPE_1: reading from backup piece VB$_1965521110_50958684_1
channel ORA_SBT_TAPE_1: piece handle=VB$_1965521110_50958684_1 tag=BKP-LEVEL1
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00003 to +DATAC1/ORAD18/DATAFILE/undotbs1.496.1022011015
channel ORA_SBT_TAPE_1: reading from backup piece VB$_1965521110_50958707_3
channel ORA_SBT_TAPE_1: piece handle=VB$_1965521110_50958707_3 tag=BKP-LEVEL1
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00004 to +DATAC1/ORAD18/DATAFILE/users.353.1022011029
channel ORA_SBT_TAPE_1: reading from backup piece VB$_1965521110_50958714_4
channel ORA_SBT_TAPE_1: piece handle=VB$_1965521110_50958714_4 tag=BKP-LEVEL1
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00002 to +DATAC1/ORAD18/DATAFILE/sysaux.921.1022010999
channel ORA_SBT_TAPE_1: reading from backup piece VB$_1965521110_50958735_2
channel ORA_SBT_TAPE_1: piece handle=VB$_1965521110_50958735_2 tag=BKP-LEVEL1
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
Finished restore at 2019-10-18_23-17-11
Starting recover at 2019-10-18_23-17-12
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
starting media recovery
channel ORA_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_SBT_TAPE_1: reading from backup piece $RSCN_1129803_RTIM_1022011106_THRD_1_SEQ_3_CTKEY_50958595_BACKUP
channel ORA_SBT_TAPE_1: piece handle=$RSCN_1129803_RTIM_1022011106_THRD_1_SEQ_3_CTKEY_50958595_BACKUP tag=TAG20191018T230500
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:01
archived log file name=+RECOC1/ORAD18/ARCHIVELOG/2019_10_18/thread_1_seq_3.967.1022012235 thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=+RECOC1/ORAD18/ARCHIVELOG/2019_10_18/thread_1_seq_3.967.1022012235 RECID=33 STAMP=1022012236
channel ORA_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_SBT_TAPE_1: reading from backup piece $RSCN_1129803_RTIM_1022011106_THRD_1_SEQ_4_CTKEY_50958752_BACKUP
channel ORA_SBT_TAPE_1: piece handle=$RSCN_1129803_RTIM_1022011106_THRD_1_SEQ_4_CTKEY_50958752_BACKUP tag=TAG20191018T230523
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:02
archived log file name=+RECOC1/ORAD18/ARCHIVELOG/2019_10_18/thread_1_seq_4.967.1022012237 thread=1 sequence=4
channel default: deleting archived log(s)
archived log file name=+RECOC1/ORAD18/ARCHIVELOG/2019_10_18/thread_1_seq_4.967.1022012237 RECID=34 STAMP=1022012237
channel ORA_SBT_TAPE_1: starting archived log restore to default destination
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_SBT_TAPE_1: reading from backup piece $RSCN_1129803_RTIM_1022011106_THRD_1_SEQ_5_CTKEY_50958757_BACKUP
channel ORA_SBT_TAPE_1: piece handle=$RSCN_1129803_RTIM_1022011106_THRD_1_SEQ_5_CTKEY_50958757_BACKUP tag=TAG20191018T231012
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:01
archived log file name=+RECOC1/ORAD18/ARCHIVELOG/2019_10_18/thread_1_seq_5.967.1022012239 thread=1 sequence=5
channel default: deleting archived log(s)
archived log file name=+RECOC1/ORAD18/ARCHIVELOG/2019_10_18/thread_1_seq_5.967.1022012239 RECID=35 STAMP=1022012240
media recovery complete, elapsed time: 00:00:01
Finished recover at 2019-10-18_23-17-21
RMAN> alter database open resetlogs;
Statement processed
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> exit
Recovery Manager complete.
[oracle@exac1vm01-ORAD18]$
Some details in this log:
The archivelog sequence 5 was generated by ZDLRA. Compare in the previous output that I don’t have it when listed the generated archivelog.
The archivelog “Next Time” was “23:09:44” that was the same moment of failure by instance crash.
I used the SCN to pick up exactly the last transaction, covered by SCN.
Checking data
[oracle@exac1vm01-ORAD18]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Oct 18 23:18:37 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL>
SQL>
SQL> select count(*) from testIns group by c3;
COUNT(*)
----------
646
1016
SQL>
Look above that I have exactly the 646 and 1016 data that I was inserting. Everything was protected and I had RPO zero.
Even in ASYNC
Besides the SYNC mode, I made the same test as ASYNC mode, and you can see that even in ASYNC mode we reached RPO zero (and doing load during the test). You can see in the txt file that all the transactions were protected.
ZDLRA and ZERO RPO
As you saw above with the examples, you can reach RPO zero for ZDLRA. As already told in my previous post (and in the Oracle docs), the real-time redo is based on DG remote transport of redo.
But with ZDLRA you can have zero RPO even for databases without DG configurated. You saw that ZDLRA protected the database even in case of catastrophic failure (losing all data and redos). Everything was protected.
Think about how it was to save this failure in the traditional backup environment. Until the last backup of archivelog? Until the last sync of storage that holds the archivelogs/redo? How much data your company can loose?
The idea of SYNC with ZDLRA, besides the docs hinting to use ASYNC to avoid overload the primary database, if fine to use. I understand that they don’t want to overload primary database and put it to wait ZDLRA writes at disks. The usage of SYNC with NOAFFIRM was proved that work. I don’t think that SYNC/AFFIRM cam improve even more the protection, because ZDLRA it is not a full DG remote destination (there are no standby redo logs), and the write to disks can’t be synchronous. The impact for SYNC/NOAFFIRM is almost zero since it involves only memory to memory copy (redo log buffers from source to remote destination).
Besides that, think about what kind of environment you usually have ZDLRA. Critical databases right? And even the name of the product says ZERO DATA LOSS. So, SYNC is the only way to truly guarantee this.
Now it is easy to understand how real-time redo it is important. You can protect your database (from small to the critical) and reach RPO zero. And if you go further and follow MAA best practices you can reach RPO and RTO zero.
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.”
