ZDLRA, Real-Time Redo and Zero RPO

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:

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>

Generating some load

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. Post protected by copyright.”

11 thoughts on “ZDLRA, Real-Time Redo and Zero RPO

  1. Pingback: ZDLRA, Multi-site protection - ZERO RPO for Primary and Standby | Fernando Simon

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

  3. Pingback: ZDLRA, Virtual Private Catalog User - VPC | Fernando Simon

  4. Rahul Dere

    Dear Fernando,

    Once Real Time redo has been enabled for protected database, Is there any data dictionary view to check that redo transport has been enabled?

    I know that in OEM we can check the redo transport enabled/disabled in protected database section of Recovery Appliance. But if i want to check the same from data dictionary view is there any way?

    Regards
    Rahul

    Reply
  5. Pingback: ZDLRA + MAA, Protection for Bronze Architecture | Fernando Simon

  6. Pingback: ZDLRA + MAA, Protection for Silver Architecture | Fernando Simon

  7. Alexey

    I really dont see any difference between ZDLRA real-time redo and simply miltiplexing the Online Redo Log on another storage device.
    Could you please clarify?

    Reply
    1. Simon Post author

      Real-time redo transport works similar to data guard, BUT, when you have a crash at the database the ZDLRA will generate a “partial” archivelog until the last received transaction. So, it will be practically zero RPO like I show in the example of my post (when I killed and vanish the database while doing the insert).
      This is impossible to do with the multiple archivelog destinations because the replication is done from the database itself when the archivelog is generated. So, in case of a crash, the data that is just at redo will not be there and you will have data loss.
      Multiplexing redos are normal and required by Oracle to provide some kind of protection, but thinking about multiple levels of protection you will need to have additional storage to be protected from a whole storage failure. And this is for all databases.
      The point for ZDLRA is that it can do Real-Time Redo Transport PLUS the virtual backups PLUS validations for every block backed up PLUS replication PLUS clone to cloud/tape. So, with one product you can have several layers of protection for all your databases.

      Reply

Leave a Reply

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