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