Fernando Simon (Board Member) – Page 4 – LUXOUG

Fernando Simon (Board Member)

Exadata, Using metrics to help you

Category: Engineer System Author: Fernando Simon (Board Member) Date: 5 years ago Comments: 0

Exadata, Using metrics to help you

It is well known that Exadata delivers a lot of power for databases and, besides that, has a lot of features that can be combined to reach the desired goals. But you need to understand how to use Exadata, it is not just knowing the internal hardware pieces, put some SQL hints, or use smart scan that makes a better DBA (or DMA).

Think about the “traditional” environment (DB + storage) and how you check for performance problems there. Basically, you just have/receive the number of IOPS from luns, throughput in MB/s, and latency from the storage side. But Exadata provides a lot of metrics that go beyond that and can be used to really understand what it is happening between the database and the access of data blocks.

For me, one of the most underrated (and not even well explained in web) features of Exadata is the metrics because they can help you to really understand Exadata deeply. As an example, from metrics, you can check the MB/s read from flash cache, disks (per type), flash log writes, reads that bypassed flash cache and went to disk, Smart I/O per database, PDB or consumer groups. It is not part of this post explain all the metrics (will be in another one), but you can read more at Chapter 6 of the Exadata User Guide.

In this post, I will show you one example of how to use the metric to identify and solve database problems. Sometimes it can be a hide and seek game, but I will try to show you how to use metrics and how they can help you on your daily basis.

DB_FC_IO_BY_SEC

Some weeks ago I was extending one script that I created 5 years ago to read metrics from all Exadata storage cells and saw interesting behavior for one database that can be a good example. The script can be checked in my post at Oracle OTN (Portuguese Version, Spanish Version, unfortunately, there is no English version. Was sent to be published, but never was – I don’t know why), the version published read all metrics and output it in one line. You can use it to insert it into one database or save it in one file as you desire.

The behavior that I saw was related to the values reported by DB_FC_IO_BY_SEC that represent the “The number of megabytes of I/O per second for this database to flash cache” done per database. The image below reports the GB/s read from DB_FC_IO_BY_SEC for just one database in two consecutive days, each point in the graph represent the average value from the last 10 minutes (read minute by minute).

As you can see, there are peaks of values, but in the end, as you can imagine, it does not appear to be correct. This does not appear to be/represent a normal trend/pattern and intuitively this got me attention. And ss you can see below, the activity report from EM/CC (for one/same day than above) for this database does not report the same behavior or even a hint:

Understand, or know Exadata, it is more than put database running there and forgot it. Even a normal/expected report from the default monitoring tools (EM/CC) of the database side can hide bad things. This can be worst in Exadata because you have a lot of power available. Using metrics, you can find these gaps and provide a better result about database tune, or discover and solve problems.

Remember above the metric values for DB_FC_IO_BY_SEC, they have a max peak (in the image) at 25GB/s. And if you check at Exadata X8 Data Sheet, the max value from flash read per server (not EF) is around 21GB/s. Since the values that I showed before are the sum for all cells (Half Rack here), they really bellow the maximum that Exadata can reach. The database was not struggling in Exadata (I imagine that will be in the traditional environment), but the power available at Exadata (and flash cache feature) was hiding the problem.

Returning to the example, since the report hinted some bad usage I made research from running SQL’s and hit two notes:

Slow Performance Of DBA_AUDIT_SESSION Query From “Failed logins” Metric in Enterprise Manager (Doc ID 1178736.1).
EM 12c, 11g: High Cpu Utilization from Enterprise Manger Agent perl Process Running failedLogin.pl for audit_failed_logins metric (Doc ID 1265699.1).

Basically, the EM/CC collection for failed login attempts was running and consuming a lot of resources. And since this EM/CC audit was not needed, it was can be disabled and after that, the DB_FC_IO_BY_SEC changed to:

As you can see, more normal behavior for database usage.

The Point

The point of this article is clear. Exadata can be simple to use, you just drop the database there and the features available will help. But unfortunately, the extra power that you have there can hide the bad things (as shown above). If you compare how to do the same in the traditional environment, the only details from the storage side that you can discover are just IOPS and throughput. But for Exadata, you can read a lot of other points to tune the database or show you problems.

Understand Exadata it is more than just learn about network, hardware, or put some hints to have a better performance. Remember that hardware change every release, and the database optimizer every version too. But Exadata metrics are there since the beginning and they just expand to provide a complete view about what happens between the database and the hardware itself. Know how to read and interpret it is the key to a good DMA.

I really recommend read Chapter 6 of the Exadata User Guide. But it is important too to have a good base about what Exadata HW can deliver and how the features work. The Exadata X8 Data Sheet, Technical White Paper and MAA OOW presentations provide you a good source of information.

Remember, it is more than learning about hardware or use some hints for SQL. It is more about understanding how hardware and software are integrated and what you can extract from there. And with Exadata metrics, you can really discover what is happening.

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

ZDLRA, Multi-site protection – ZERO RPO for Primary and Standby

Category: Engineer System Author: Fernando Simon (Board Member) Date: 5 years ago Comments: 0

ZDLRA, Multi-site protection - ZERO RPO for Primary and Standby

ZDLRA can be used from a small single database environment to big environments where you need protection in more than one site at the same time. At every level, you can use different features of ZDLRA to provide desirable protection. Here I will show how to reach zero RPO for both primary and standby databases. All the steps, doc, and tech parts are covered.

You can check the examples the reference for every scenario int these two papers from the Oracle MAA team: MAA Overview On-Premises and Oracle MAA Reference Architectures. They provide good information on how to prepare to reduce RPO and improve RTO. In resume, the focus is the same, reduce the downtime and data loss in case of a catastrophe (zero RPO, and zero RPO).

Multi-site protection

If you looked both papers before, you saw that to provide good protection is desirable to have an additional site to, at least, send the backups. And if you go higher, for GOLD and PLATINUM environments, you start to have multiple sites synced with data guard. These Critical/Mission-critical environments need to be protected for every kind of catastrophic failure, from disk until complete site outage (some need to follow specific law’s requirements, bank as an example).

And the focus of this post is these big environments. I will show you how to use ZDLRA to protect both sites, reaching zero RPO even for standby databases. And doing that, you can survive for a catastrophic outage (like entire datacenter failure) and still have zero RPO. Going further, you can even have zero RPO if you lose completely on site when using real-time redo for ZDLRA, and this is not written in the docs by the way.

Some features I already wrote about that in the previous posts. Already wrote about ZDLRA features, how to enroll a database, and how to reach zero RPO for database protection. All of these I will use here, to protect the bigger environments it is used incremental forever strategy for backup and real-time redo to protect primary and standby databases to reach zero RPO.

MAA team already wrote about how to do that at Deploying the Zero Data Loss Recovery Appliance in a Data Guard Configuration, but frankly, it is hard to understand this doc. And here in this post, I will try to provide a better example and how to reach RPO zero for both sites.

Multiple ZDLRA’s

The way that we will use ZDLRA, in this case, is different from the replication feature that exists for ZDLRA. Here, we have two ZDLRA, one for each site. The environment in this case is:

ORCL19: RAC primary database.
ORCL19S: RAC standby database.
ZDLRAS1: ZDLRA that protect the primary site.
ZDLRAS2: ZDLRA that protects the standby site.

And the target will be:

As you can see above, each ZDLRA protects your own site, and the replication between sites is done by DG. The DG configuration it is not part of this post, but the output (and order) for all commands how I created the RAC + RAC DG you can check here – Steps-RAC-RAC-DG-Creation. If you have some doubts about it, I can explain if needed.

ZDLRA Configuration – Protecting and Enrolling Databases

VPC

If you already have a VPC user created for your ZDLRA and want to use an existing one, you can skip this step. Just remember that the same user needs to exist in both ZDLRA’s. This is more critical if you are intending to use real-time redo and reach zero RPO.

So, if needed, create the same VPC user in both ZDLRA’s:

At ZDLRAS1:

[root@zdlras1n1 ~]# /opt/oracle.RecoveryAppliance/bin/racli add vpc_user --user_name=vpczdlra

[vpczdlra] New Password:

Sat Nov  2 19:43:59 2019: Start: Add vpc user vpczdlra.

Sat Nov  2 19:44:00 2019:        Add vpc user vpczdlra successfully.

Sat Nov  2 19:44:00 2019: End:   Add vpc user vpczdlra.

[root@zdlras1n1 ~]#

At ZDLRAS2:

[root@zdlras2n1 ~]# /opt/oracle.RecoveryAppliance/bin/racli add vpc_user --user_name=vpczdlra

[vpczdlra] New Password:

Sat Nov  2 19:43:41 2019: Start: Add vpc user vpczdlra.

Sat Nov  2 19:43:42 2019:        Add vpc user vpczdlra successfully.

Sat Nov  2 19:43:42 2019: End:   Add vpc user vpczdlra.

[root@zdlras2n1 ~]#

Backup Policy

It is not needed to have the same policy in each ZDLRA and it is possible to enroll the database using different policies since they (ZDLRA’s) work separately. Here I used the same policy in both sites (but with different recovery window goals).

At ZDLRAS1:

[oracle@zdlras1n1 ~]$ sqlplus rasys/change^Me2

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 2 22:21:18 2019

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat Nov 02 2019 11:20:06 +01:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> BEGIN

  2  DBMS_RA.CREATE_PROTECTION_POLICY(

  3      protection_policy_name => 'ZDLRA'

  4      , description => 'Policy ZDLRA S1'

  5      , storage_location_name => 'DELTA'

  6      , recovery_window_goal => INTERVAL '5' DAY

  7      , max_retention_window => INTERVAL '10' DAY

  8      , guaranteed_copy => 'NO'

  9      , allow_backup_deletion => 'YES'

 10  );

 11  END;

 12  /

PL/SQL procedure successfully completed.

SQL>

At ZDLRAS2:

[oracle@zdlras2n1 ~]$ sqlplus rasys/change^Me2

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 2 22:22:13 2019

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat Nov 02 2019 11:21:04 +01:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> BEGIN

  2  DBMS_RA.CREATE_PROTECTION_POLICY(

  3      protection_policy_name => 'ZDLRA'

  4      , description => 'Policy ZDLRA S2'

  5      , storage_location_name => 'DELTA'

  6      , recovery_window_goal => INTERVAL '7' DAY

  7      , max_retention_window => INTERVAL '14' DAY

  8      , guaranteed_copy => 'NO'

  9      , allow_backup_deletion => 'YES'

 10  );

 11  END;

 12  /

PL/SQL procedure successfully completed.

SQL>

Grant DB at ZDLRA

Here we have the first key point, and it is related to the way that database is registered database inside ZDLRA. It follows the traditional way, using the ADD_DB, GRANT_DB_ACCESS, and using DB_UNIQUE_NAME as identification.

The point here is that for dataguard the DB_UNIQUE_NAME is different for each database, but one detail it is that ZDLRA internally links DBID of the database with the unique name. So, it is needed and required, that for ZDLRA who will protect the standby site that the add and registration use DB_UNIQUE_NAME from the primary. If you do not do that, the ZDLRA will understand that the standby database it is a completely different database (and will report, RMAN-03009: failure of resync command on default channel, ORA-00001: unique constraint (RASYS.ODB_P) violated, and ORA-04088: error during execution of trigger ‘RASYS.NODE_INSERT_UPDATE’).

And another common error, if you try to register the standby database (instead of primary database) is RMAN-01005: Mounted control file type must be CURRENT to register the database. This occurs because some registrations and checks done by rman and database controlfile need to be at current one.

At ZDLRAS1:

[oracle@zdlras1n1 ~]$ sqlplus rasys/change^Me2

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 3 14:11:37 2019

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sun Nov 03 2019 11:16:12 +01:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> BEGIN

  2  DBMS_RA.ADD_DB(

  3        db_unique_name => 'ORCL19'

  4        , protection_policy_name => 'ZDLRA'

  5        , reserved_space => '5G'

  6  );

  7  END;

  8  /

PL/SQL procedure successfully completed.

SQL>

SQL> BEGIN

  2  DBMS_RA.GRANT_DB_ACCESS (

  3        db_unique_name => 'ORCL19'

  4        , username => 'VPCZDLRA'

  5  );

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL>

At ZDLRAS2:

[oracle@zdlras2n1 ~]$ sqlplus rasys/change^Me2

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 3 16:14:06 2019

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sun Nov 03 2019 11:16:55 +01:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> BEGIN

  2  DBMS_RA.ADD_DB(

  3        db_unique_name => 'ORCL19'

  4        , protection_policy_name => 'ZDLRA'

  5        , reserved_space => '5G'

  6  );

  7  END;

  8  /

PL/SQL procedure successfully completed.

SQL> BEGIN

  2  DBMS_RA.GRANT_DB_ACCESS (

  3        db_unique_name => 'ORCL19'

  4        , username => 'VPCZDLRA'

  5  );

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL>

Look above that ADD_DB and GRANT_DB_ACCESS at ZDLRAS2 used the DB_UNIQUE_NAME as ORCL19 (that it is the name for primary).

Registry database

At Primary – ORLC19

At this point, it is possible to register and backup the primary database with ZDLRAS1. The steps are the same that I described at my post on how to enroll a database at ZDLRA. The steps are, in order:

Install ZDLRA library
Create Wallet
Configure/Test tns entry to ZDLRA
Adjust sqlnet.ora file (this is needed because the real-time redo)

[oracle@orcl19p ~]$ cd /tmp/

[oracle@orcl19p tmp]$ unzip ra_linux64.zip

Archive:  ra_linux64.zip

  inflating: libra.so

  inflating: metadata.xml

[oracle@orcl19p tmp]$

[oracle@orcl19p tmp]$ cp ./libra.so /u01/app/oracle/product/19.0.0.0/dbhome_1/lib/libra.so

[oracle@orcl19p tmp]$

[oracle@orcl19p tmp]$ cd $ORACLE_HOME/dbs

[oracle@orcl19p dbs]$

[oracle@orcl19p dbs]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -createALO

Oracle Secret Store Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

[oracle@orcl19p dbs]$

[oracle@orcl19p dbs]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -createCredential zdlras1-scan:1521/zdlras1:VPCZDLRA vpczdlra oracle

Oracle Secret Store Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

[oracle@orcl19p dbs]$

[oracle@orcl19p dbs]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -listCredential

Oracle Secret Store Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

List credential (index: connect_string username)

1: zdlras1-scan:1521/zdlras1:VPCZDLRA vpczdlra

[oracle@orcl19p dbs]$

[oracle@orcl19p dbs]$ vi $ORACLE_HOME/dbs/raORCL191.ora

[oracle@orcl19p dbs]$ cat $ORACLE_HOME/dbs/raORCL191.ora

RA_WALLET='LOCATION=file:/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet CREDENTIAL_ALIAS=zdlras1-scan:1521/zdlras1:VPCZDLRA'

[oracle@orcl19p dbs]$

[oracle@orcl19p dbs]$ tnsping zdlras1

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 03-NOV-2019 15:34:09

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zdlras1-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zdlras1)))

OK (20 msec)

[oracle@orcl19p dbs]$

[oracle@orcl19p dbs]$ cat $ORACLE_HOME/network/admin/sqlnet.ora

cat: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora: No such file or directory

[oracle@orcl19p dbs]$

[oracle@orcl19p dbs]$ vi $ORACLE_HOME/network/admin/sqlnet.ora

[oracle@orcl19p dbs]$

[oracle@orcl19p dbs]$ cat $ORACLE_HOME/network/admin/sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.WALLET_OVERRIDE = true

WALLET_LOCATION =

(

   SOURCE =

      (METHOD = FILE)

      (METHOD_DATA =

         (DIRECTORY = /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet)

      )

)

[oracle@orcl19p dbs]$

At Standby – ORLC19S

The same procedure than above it is done here:

Install ZDLRA library
Create Wallet
Configure/Test tns entry to ZDLRA
Adjust sqlnet.ora file (this is needed because the real-time redo)

[oracle@orcl19s ~]$ cd /tmp/

[oracle@orcl19s tmp]$ unzip ra_linux64.zip

Archive:  ra_linux64.zip

  inflating: libra.so

  inflating: metadata.xml

[oracle@orcl19s tmp]$

[oracle@orcl19s tmp]$ cp ./libra.so /u01/app/oracle/product/19.0.0.0/dbhome_1/lib/libra.so

[oracle@orcl19s tmp]$

[oracle@orcl19s tmp]$ cd $ORACLE_HOME/dbs

[oracle@orcl19s dbs]$

[oracle@orcl19s dbs]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -createALO

Oracle Secret Store Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

[oracle@orcl19s dbs]$

[oracle@orcl19s dbs]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -createCredential zdlras2-scan:1521/zdlras2:VPCZDLRA vpczdlra oracle

Oracle Secret Store Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

[oracle@orcl19s dbs]$

[oracle@orcl19s dbs]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -listCredential

Oracle Secret Store Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.

List credential (index: connect_string username)

1: zdlras2-scan:1521/zdlras2:VPCZDLRA vpczdlra

[oracle@orcl19s dbs]$

[oracle@orcl19s dbs]$ vi $ORACLE_HOME/dbs/raORCL19S1.ora

[oracle@orcl19s dbs]$

[oracle@orcl19s dbs]$ cat $ORACLE_HOME/dbs/raORCL19S1.ora

RA_WALLET='LOCATION=file:/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet CREDENTIAL_ALIAS=zdlras2-scan:1521/zdlras2:VPCZDLRA'

[oracle@orcl19s dbs]$

[oracle@orcl19s dbs]$ tnsping zdlras2

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 03-NOV-2019 16:32:57

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zdlras2-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zdlras2)))

OK (0 msec)

[oracle@orcl19s dbs]$

[oracle@orcl19s dbs]$ cat $ORACLE_HOME/network/admin/sqlnet.ora

cat: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora: No such file or directory

[oracle@orcl19s dbs]$

[oracle@orcl19s dbs]$ vi $ORACLE_HOME/network/admin/sqlnet.ora

[oracle@orcl19s dbs]$

[oracle@orcl19s dbs]$ cat $ORACLE_HOME/network/admin/sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.WALLET_OVERRIDE = true

WALLET_LOCATION =

(

   SOURCE =

      (METHOD = FILE)

      (METHOD_DATA =

         (DIRECTORY = /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet)

      )

)

[oracle@orcl19s dbs]$

Registry Primary Database – Both ZDLRA’s

After that, we can register ORACL19 at ZDLRAS1. It is critical to register the primary database first at the ZDLRA that will protect the primary site.

[oracle@orcl19p dbs]$ rman target=/ catalog=vpczdlra/oracle@zdlras1

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 3 15:40:37 2019

Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19 (DBID=324042554)

connected to recovery catalog database

PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current

PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN>

If you want, you can do the backup of the database (it is optional), I not showed here but it is a simple backup database level 0 filesperset 1 and you can see the output here – Output-Backup-Primary-ORCL19-at-ZDLRAS1.

After registry primary database ORCL19 at ZDLRA (ZDLRAS1) that protect the primary site, we can register the primary ORCL19 at the ZDLRA that protect the standby site (ZDLRAS2)

Look that I need to add the entry of TNS names for ZDLRAS2 (at the primary server) too:

[oracle@orcl19p dbs]$ tnsping zdlras2

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 03-NOV-2019 16:37:38

Copyright (c) 1997, 2019, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zdlras2-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = zdlras2)))

OK (0 msec)

[oracle@orcl19p dbs]$

[oracle@orcl19p dbs]$ rman target=/ catalog=vpczdlra/oracle@zdlras2

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 3 17:30:06 2019

Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19 (DBID=324042554)

connected to recovery catalog database

PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current

PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN> exit

Recovery Manager complete.

[oracle@orcl19p dbs]$

Since the registration was done at primary, the controlfile of the standby database needs to be updated with one rman catalog, and it is needed to inform ZDLRAS2 (that protect standby site) who is the standby database. This is done by doing a simple resync catalog from standby, connected at ZDLRAS2.

[oracle@orcl19s dbs]$ rman target=/ catalog=vpczdlra/oracle@zdlras2

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Nov 3 20:12:32 2019

Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL19 (DBID=324042554, not open)

connected to recovery catalog database

PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current

PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL19S are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default

CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' FORMAT   '%d_%U' PARMS  "SBT_LIBRARY=/u01/app/oracle/product/19.0.0.0/dbhome_1/lib/libra.so, ENV=(RA_WALLET='location=file:/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet credential_alias=zdlras1-scan:1521/zdlras1:VPCZDLRA')" CONNECT '*';

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/snapcf_orcl19s1.f'; # default

RMAN> resync catalog;

starting resync of recovery catalog

resync complete

RMAN> list db_unique_name of database orcl19;

List of Databases

DB Key  DB Name  DB ID            Database Role    Db_unique_name

------- ------- ----------------- ---------------  ------------------

835     ORCL19   324042554        PRIMARY          ORCL19

835     ORCL19   324042554        STANDBY          ORCL19S

RMAN>

Look at some points above:

show all read the already registered information from rman catalog (look channel set)
After the resync catalog, the list db_unique_name reported both databases, primary (ORCL19) and standby (ORCL19S)

After that, you can configure the correct channel for ORCL19S to point to ZDLRAS2, and do the backup. This output can be checked here – Output-Backup-Primary-ORCL19S-at-ZDLRAS2.

Environment protected

At this point, you have both sites protected, which one with dedicated local backup with ZDLRA. Primary ORCL19 database does the backup at ZDLRAS1, and standby database ORCL19S does the backup at ZDLRAS2:

But this not protect you in case of failure of one site. In this case, the RPO will not be zero because we don’t have where to send the archivelogs.

Think that you lose your primary site, and after the switch to standby the machine failures again. What will be your RPO? The last backup of standby will be, because your standby (new primary) it is not sending archivelogs to the old primary (because it is out for outage), and you don’t have real-time redo configured for ZDLRA. So, if you have an outage at standby you lose until the point of your last backup. This is not clear at the docs from MAA Overview On-Premises and Oracle MAA Reference Architectures.

ZERO RPO

To reach zero RPO we just need to enable real-time redo for both sites. We can do manually or using the broker. If you want to see in detail how to configure you can check my two posts: ZDLRA, Real-Time Redo and ZDLRA, Real-Time Redo and Zero RPO, here I will post resumed steps.

Be careful with the order of the steps below, they are important because we are changing the user (redo_transport_user) that will be configured in both databases. If you change first at standby you will face issued because the user is created at primary first and the “creation” it sends automatically by redo. By the way, this user needs to be the same as VPC user that you used to configure/add/grant database access for ZDLRA. And need to be the same for every ZDLRA because both databases use the same user. This is the second key point of this config.

Manually Configuration

The manually way differs because here we set manually the log_archive_dest parameters. The others need to be done besides the way that we choose. And the manual config is the only way for 11G databases.

Basic config

For real-time redo we need to configure some parameters, but basically, I made these configs in order:

log_archive_config: Check that for primary it is ZDLRAS1, and ZDLRAS2 for standby
log_archive_dest_3: Again, each site points to the own ZDLRA. And look the type defined at VALID_FOR
Create the user for redo_transport_user inside database.
redo_transport_user: Same for both databases.

At Primary ORCL19:

[oracle@orcl19p ~]$ sqlplus / as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 3 22:34:28 2019

Version 19.5.0.0.0




Copyright (c) 1982, 2019, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0




SQL> show parameter log_archive_config;




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string      DG_CONFIG=(orcl19,orcl19s)

SQL> ALTER SYSTEM SET log_archive_config='DG_CONFIG=(orcl19,orcl19s,zdlras1)' SCOPE=SPFILE SID='*';




System altered.




SQL> ALTER SYSTEM SET log_archive_dest_3='SERVICE="zdlras1-scan:1521/zdlras1:VPCZDLRA" ASYNC NOAFFIRM DB_UNIQUE_NAME=zdlras1 VALID_FOR=(ONLINE_LOGFILE,ALL_ROLES)' scope = spfile sid = '*';




System altered.




SQL>

SQL> alter system set log_archive_dest_state_3=DEFER scope = spfile sid = '*';




System altered.




SQL>

SQL> col username format a20

SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users;




USERNAME             SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM

-------------------- ----- ----- ----- ----- ----- -----

SYS                  TRUE  TRUE  FALSE FALSE FALSE FALSE




SQL> show parameter redo_transport_user;




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

redo_transport_user                  string

SQL> alter system set redo_transport_user='VPCZDLRA' scope = spfile SID='*';




System altered.




SQL>

SQL> alter session set "_ORACLE_SCRIPT"=true;




Session altered.




SQL> create user VPCZDLRA identified by oracle;




User created.




SQL> grant sysoper to VPCZDLRA;




Grant succeeded.




SQL> alter session set "_ORACLE_SCRIPT"=false;




Session altered.




SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users;




USERNAME             SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM

-------------------- ----- ----- ----- ----- ----- -----

SYS                  TRUE  TRUE  FALSE FALSE FALSE FALSE

VPCZDLRA             FALSE TRUE  FALSE FALSE FALSE FALSE




SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0

[oracle@orcl19p ~]$

At Standby ORCL19S:

[oracle@orcl19s dbs]$ sqlplus / as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 3 22:53:05 2019

Version 19.5.0.0.0




Copyright (c) 1982, 2019, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0




SQL> show parameter log_archive_config;




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string      DG_CONFIG=(orcl19,orcl19s)

SQL> ALTER SYSTEM SET log_archive_config='DG_CONFIG=(orcl19,orcl19s,zdlras2)' SCOPE=SPFILE SID='*';




System altered.




SQL> ALTER SYSTEM SET log_archive_dest_3='SERVICE="zdlras2-scan:1521/zdlras2:VPCZDLRA" ASYNC NOAFFIRM DB_UNIQUE_NAME=zdlras2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' scope = spfile sid = '*';




System altered.




SQL> alter system set redo_transport_user='VPCZDLRA' scope = spfile SID='*';




System altered.




SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0

[oracle@orcl19s dbs]$

[oracle@orcl19s dbs]$ srvctl stop database -d orcl19s -o immediate

[oracle@orcl19s dbs]$

As you can see here, the user is not created at standby because the creation will come by redo. And as you can see in the end, I stopped the standby database.

Password file

Since one user was created in just one site, we need to copy the password file from production to the standby.

At Primary ORCL19:

[root@orcl19p ~]# su - grid

Last login: Sun Nov  3 23:03:09 CET 2019

[grid@orcl19p ~]$ asmcmd

ASMCMD> cd +DATA/ORCL19/

ASMCMD> ls -l

Type             Redund  Striped  Time             Sys  Name

                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/

                                                   Y    95D86C40430C3E85E0533205A00A0EBA/

                                                   Y    CONTROLFILE/

                                                   Y    DATAFILE/

                                                   Y    DATAGUARDCONFIG/

                                                   Y    ONLINELOG/

                                                   Y    PARAMETERFILE/

                                                   Y    PASSWORD/

                                                   Y    TEMPFILE/

DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 23:00:00  N    dr1orcl19.dat => +DATA/ORCL19/DATAGUARDCONFIG/ORCL19.275.1023297961

ASMCMD> ls -l PASSWORD/

Type      Redund  Striped  Time             Sys  Name

PASSWORD  UNPROT  COARSE   OCT 26 23:00:00  Y    pwdorcl19.256.1022714087

ASMCMD> ls -l

Type             Redund  Striped  Time             Sys  Name

                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/

                                                   Y    95D86C40430C3E85E0533205A00A0EBA/

                                                   Y    CONTROLFILE/

                                                   Y    DATAFILE/

                                                   Y    DATAGUARDCONFIG/

                                                   Y    ONLINELOG/

                                                   Y    PARAMETERFILE/

                                                   Y    PASSWORD/

                                                   Y    TEMPFILE/

DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 23:00:00  N    dr1orcl19.dat => +DATA/ORCL19/DATAGUARDCONFIG/ORCL19.275.1023297961

ASMCMD>

ASMCMD> cp PASSWORD/pwdorcl19.256.1022714087 /tmp

copying +DATA/ORCL19/PASSWORD/pwdorcl19.256.1022714087 -> /tmp/pwdorcl19.256.1022714087

ASMCMD> exit

[grid@orcl19p ~]$

As you saw, I saved the password file at /tmp folder.

At Standby ORCL19S:

[root@orcl19s ~]# su - grid

Last login: Sun Nov  3 23:03:00 CET 2019

[grid@orcl19s ~]$

[grid@orcl19s ~]$

[grid@orcl19s ~]$ scp orcl19p:/tmp/pwdorcl19.256.1022714087 /tmp/pwdorcl19.256.1022714087

grid@orcl19p's password:

pwdorcl19.256.1022714087                                                                                                                                                                                   100% 2560     4.3MB/s   00:00

[grid@orcl19s ~]$

[grid@orcl19s ~]$

[grid@orcl19s ~]$

[grid@orcl19s ~]$ asmcmd

ASMCMD> ls -l +DATA/ORCL19S/

Type             Redund  Striped  Time             Sys  Name

                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/

                                                   Y    95D86C40430C3E85E0533205A00A0EBA/

                                                   Y    CONTROLFILE/

                                                   Y    DATAFILE/

                                                   Y    DATAGUARDCONFIG/

                                                   Y    ONLINELOG/

                                                   Y    PARAMETERFILE/

                                                   Y    PASSWORD/

DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 19:00:00  N    dr1orcl19s.dat => +DATA/ORCL19S/DATAGUARDCONFIG/orcl19s.275.1023298021

PASSWORD         UNPROT  COARSE   NOV 02 15:00:00  N    orapworcl19s => +DATA/ORCL19S/PASSWORD/pwdorcl19s.268.1023290373

PARAMETERFILE    UNPROT  COARSE   NOV 03 22:00:00  N    spfile.ora => +DATA/ORCL19S/PARAMETERFILE/spfile.272.1023290247

ASMCMD>

ASMCMD> pwcopy --dbuniquename orcl19s '/tmp/pwdorcl19.256.1022714087' '+DATA/ORCL19S/orapworcl19s' -f

ASMCMD-9453: failed to register password file as a CRS resource

copying /tmp/pwdorcl19.256.1022714087 -> +DATA/ORCL19S/orapworcl19s

ASMCMD-9453: failed to register password file as a CRS resource

ASMCMD>

ASMCMD>

ASMCMD> ls -l +DATA/ORCL19S/

Type             Redund  Striped  Time             Sys  Name

                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/

                                                   Y    95D86C40430C3E85E0533205A00A0EBA/

                                                   Y    CONTROLFILE/

                                                   Y    DATAFILE/

                                                   Y    DATAGUARDCONFIG/

                                                   Y    ONLINELOG/

                                                   Y    PARAMETERFILE/

                                                   Y    PASSWORD/

DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 19:00:00  N    dr1orcl19s.dat => +DATA/ORCL19S/DATAGUARDCONFIG/orcl19s.275.1023298021

PASSWORD         UNPROT  COARSE   NOV 03 23:00:00  N    orapworcl19s => +DATA/ORCL19S/PASSWORD/pwdorcl19s.268.1023405041

PARAMETERFILE    UNPROT  COARSE   NOV 03 22:00:00  N    spfile.ora => +DATA/ORCL19S/PARAMETERFILE/spfile.272.1023290247

ASMCMD>

ASMCMD> exit

[grid@orcl19s ~]$

As you saw above, the file from primary was copied and overwritten the old.

Bounce everything

After doing this it is time to bounce the primary database to reload the parameters and start the standby database to read password file and new parameters

At Primary ORCL19:

[oracle@orcl19p ~]$ srvctl stop database -d orcl19s -o immediate

[oracle@orcl19p ~]$ srvctl start database -d orcl19

[oracle@orcl19p ~]$

At Standby ORCL19S



[oracle@orcl19s ~]$ srvctl start database -d orcl19s

[oracle@orcl19s ~]$

[oracle@orcl19s ~]$ sqlplus / as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Nov 3 23:13:22 2019

Version 19.5.0.0.0




Copyright (c) 1982, 2019, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0




SQL> col username format a20

SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users;




USERNAME             SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM

-------------------- ----- ----- ----- ----- ----- -----

SYS                  TRUE  TRUE  FALSE FALSE FALSE FALSE

VPCZDLRA             FALSE TRUE  FALSE FALSE FALSE FALSE


SQL>

Checking ZERO RPO

To check if the conf is OK, you can do a simple archivelog at primary and verify if everything was OK for both sites.

At Primary ORCL19:

[oracle@orcl19p ~]$ sqlplus / as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 4 00:46:46 2019

Version 19.5.0.0.0




Copyright (c) 1982, 2019, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0




SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     117

Next log sequence to archive   119

Current log sequence           119

SQL> alter system archive log current;




System altered.




SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0

[oracle@orcl19p ~]$ rman target=/ catalog=vpczdlra/oracle@zdlras1




Recovery Manager: Release 19.0.0.0.0 - Production on Mon Nov 4 00:47:04 2019

Version 19.5.0.0.0




Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.




connected to target database: ORCL19 (DBID=324042554)

connected to recovery catalog database

PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current

PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current




RMAN> list backup of archivelog sequence 118;







List of Backup Sets

===================







BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ -------------------

1965    8.00K      SBT_TAPE    00:00:01     04/11/2019 00:45:49

        BP Key: 1966   Status: AVAILABLE  Compressed: YES  Tag: TAG20191104T004548

        Handle: $RSCN_1_RTIM_1022714106_THRD_1_SEQ_118_CTKEY_1946_BACKUP   Media:




  List of Archived Logs in backup set 1965

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    118     2137116    04/11/2019 00:44:35 2137128    04/11/2019 00:44:39




RMAN> exit







Recovery Manager complete.

[oracle@orcl19p ~]$

At Standby ORCL19S

[oracle@orcl19s ~]$ rman target=/ catalog=vpczdlra/oracle@zdlras2




Recovery Manager: Release 19.0.0.0.0 - Production on Mon Nov 4 00:48:39 2019

Version 19.5.0.0.0




Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.




connected to target database: ORCL19 (DBID=324042554, not open)

connected to recovery catalog database

PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current

PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current




RMAN> list backup of archivelog sequence 118;







List of Backup Sets

===================







BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ -------------------

2240    8.00K      SBT_TAPE    00:00:00     04/11/2019 00:45:35

        BP Key: 2241   Status: AVAILABLE  Compressed: YES  Tag: TAG20191104T004535

        Handle: $RSCN_1_RTIM_1022714106_THRD_1_SEQ_118_CTKEY_2228_BACKUP   Media:




  List of Archived Logs in backup set 2240

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    118     2137116    04/11/2019 00:44:35 2137128    04/11/2019 00:44:39




RMAN> exit







Recovery Manager complete.

[oracle@orcl19s ~]$

So, as you can see above. The ZDLRAS1 received the onlinelog (sequence 118) from ORCL19. And the ZDLRAS2 received a copy of it (standby redo log) from ORCL19S. This means that now you have zero RPO from primary and standby.

Broker config

From 12.1 and new versions, you can have recovery_appliance configured directly from broker when using DG. This executes the configuration for log_archive_dest parameters automatically when you add the ZDLRA.

Basic config

Pretty same config that was made as manual config before.

At Primary ORCL19:

[oracle@orcl19p ~]$ sqlplus / as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 9 16:24:27 2019

Version 19.5.0.0.0




Copyright (c) 1982, 2019, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0




SQL>

SQL> col username format a20

SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users;




USERNAME             SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM

-------------------- ----- ----- ----- ----- ----- -----

SYS                  TRUE  TRUE  FALSE FALSE FALSE FALSE




SQL> show parameter redo_transport_user;




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

redo_transport_user                  string

SQL> alter system set redo_transport_user='VPCZDLRA' scope = spfile SID='*';




System altered.




SQL>

SQL> alter session set "_ORACLE_SCRIPT"=true;




Session altered.




SQL> create user VPCZDLRA identified by oracle;




User created.




SQL> grant sysoper to VPCZDLRA;




Grant succeeded.




SQL> alter session set "_ORACLE_SCRIPT"=false;




Session altered.




SQL> select USERNAME,SYSDBA,SYSOPER,SYSASM,SYSBACKUP,SYSDG,SYSKM from v$pwfile_users;




USERNAME             SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM

-------------------- ----- ----- ----- ----- ----- -----

SYS                  TRUE  TRUE  FALSE FALSE FALSE FALSE

VPCZDLRA             FALSE TRUE  FALSE FALSE FALSE FALSE




SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0

[oracle@orcl19p ~]$

At Standby ORCL19S

[oracle@orcl19s ~]$ sqlplus / as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 9 16:25:09 2019

Version 19.5.0.0.0




Copyright (c) 1982, 2019, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0




SQL>

SQL> alter system set redo_transport_user='VPCZDLRA' scope = spfile SID='*';




System altered.




SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.5.0.0.0

[oracle@orcl19s dbs]$

Password file

At Primary ORCL19:

[grid@orcl19p ~]$ asmcmd

ASMCMD> cd +DATA/ORCL19/

ASMCMD> ls -l

Type             Redund  Striped  Time             Sys  Name

                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/

                                                   Y    95D86C40430C3E85E0533205A00A0EBA/

                                                   Y    CONTROLFILE/

                                                   Y    DATAFILE/

                                                   Y    DATAGUARDCONFIG/

                                                   Y    ONLINELOG/

                                                   Y    PARAMETERFILE/

                                                   Y    PASSWORD/

                                                   Y    TEMPFILE/

DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 23:00:00  N    dr1orcl19.dat => +DATA/ORCL19/DATAGUARDCONFIG/ORCL19.275.1023297961

ASMCMD> ls -l PASSWORD/

Type      Redund  Striped  Time             Sys  Name

PASSWORD  UNPROT  COARSE   OCT 26 23:00:00  Y    pwdorcl19.256.1022714087

ASMCMD> ls -l

Type             Redund  Striped  Time             Sys  Name

                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/

                                                   Y    95D86C40430C3E85E0533205A00A0EBA/

                                                   Y    CONTROLFILE/

                                                   Y    DATAFILE/

                                                   Y    DATAGUARDCONFIG/

                                                   Y    ONLINELOG/

                                                   Y    PARAMETERFILE/

                                                   Y    PASSWORD/

                                                   Y    TEMPFILE/

DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 23:00:00  N    dr1orcl19.dat => +DATA/ORCL19/DATAGUARDCONFIG/ORCL19.275.1023297961

ASMCMD>

ASMCMD> cp PASSWORD/pwdorcl19.256.1022714087 /tmp

copying +DATA/ORCL19/PASSWORD/pwdorcl19.256.1022714087 -> /tmp/pwdorcl19.256.1022714087

ASMCMD> exit

[grid@orcl19p ~]$

At Standby ORCL19S

[grid@orcl19p ~]$ asmcmd

ASMCMD> cd +DATA/ORCL19/

ASMCMD> ls -l

Type             Redund  Striped  Time             Sys  Name

                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/

                                                   Y    95D86C40430C3E85E0533205A00A0EBA/

                                                   Y    CONTROLFILE/

                                                   Y    DATAFILE/

                                                   Y    DATAGUARDCONFIG/

                                                   Y    ONLINELOG/

                                                   Y    PARAMETERFILE/

                                                   Y    PASSWORD/

                                                   Y    TEMPFILE/

DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 23:00:00  N    dr1orcl19.dat => +DATA/ORCL19/DATAGUARDCONFIG/ORCL19.275.1023297961

ASMCMD> ls -l PASSWORD/

Type      Redund  Striped  Time             Sys  Name

PASSWORD  UNPROT  COARSE   OCT 26 23:00:00  Y    pwdorcl19.256.1022714087

ASMCMD> ls -l

Type             Redund  Striped  Time             Sys  Name

                                                   Y    95D7C2B2568F0A70E0533205A00AC96B/

                                                   Y    95D86C40430C3E85E0533205A00A0EBA/

                                                   Y    CONTROLFILE/

                                                   Y    DATAFILE/

                                                   Y    DATAGUARDCONFIG/

                                                   Y    ONLINELOG/

                                                   Y    PARAMETERFILE/

                                                   Y    PASSWORD/

                                                   Y    TEMPFILE/

DATAGUARDCONFIG  UNPROT  COARSE   NOV 03 23:00:00  N    dr1orcl19.dat => +DATA/ORCL19/DATAGUARDCONFIG/ORCL19.275.1023297961

ASMCMD>

ASMCMD> cp PASSWORD/pwdorcl19.256.1022714087 /tmp

copying +DATA/ORCL19/PASSWORD/pwdorcl19.256.1022714087 -> /tmp/pwdorcl19.256.1022714087

ASMCMD> exit

[grid@orcl19p ~]$

Wallet Configuration

When using broker configuration, it is recommended to add both credential entries, of ZDLRA from primary and standby, in the wallet from both databases.

At Primary ORCL19:

[oracle@orcl19p ~]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -createCredential zdlras2-scan:1521/zdlras2:VPCZDLRA vpczdlra oracle

Oracle Secret Store Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.




[oracle@orcl19p ~]$

[oracle@orcl19p ~]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -listCredential

Oracle Secret Store Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.




List credential (index: connect_string username)

2: zdlras2-scan:1521/zdlras2:VPCZDLRA vpczdlra

1: zdlras1-scan:1521/zdlras1:VPCZDLRA vpczdlra

[oracle@orcl19p ~]$

At Standby ORCL19S:

[oracle@orcl19s ~]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -createCredential zdlras1-scan:1521/zdlras1:VPCZDLRA vpczdlra oracle

Oracle Secret Store Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.




[oracle@orcl19s ~]$ mkstore -wrl /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/ra_wallet -listCredential

Oracle Secret Store Tool Release 19.0.0.0.0 - Production

Version 19.4.0.0.0

Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.




List credential (index: connect_string username)

2: zdlras1-scan:1521/zdlras1:VPCZDLRA vpczdlra

1: zdlras2-scan:1521/zdlras2:VPCZDLRA vpczdlra

[oracle@orcl19s ~]$

Bounce everything

At Standby ORCL19S:

[oracle@orcl19s ~]$ srvctl stop database -d orcl19s -o immediate

[oracle@orcl19s ~]$

At Primary ORCL19

[oracle@orcl19p ~]$ srvctl stop database -d orcl19 -o immediate

[oracle@orcl19p ~]$ srvctl start database -d orcl19

[oracle@orcl19p ~]$

At Standby ORCL19S

[oracle@orcl19s ~]$ srvctl start database -d orcl19s

[oracle@orcl19s ~]$

Broker config

Now I can add the ZDLRA at config. First, look the config:

[oracle@orcl19p ~]$ dgmgrl sys/oracle@orcl19

DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Nov 9 16:26:26 2019

Version 19.5.0.0.0




Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.




Welcome to DGMGRL, type "help" for information.

Connected to "ORCL19"

Connected as SYSDBA.

DGMGRL>

DGMGRL> show configuration verbose;




Configuration - orcl19




  Protection Mode: MaxAvailability

  Members:

  orcl19  - Primary database

    orcl19s - Physical standby database




  Properties:

    FastStartFailoverThreshold      = '30'

    OperationTimeout                = '30'

    TraceLevel                      = 'USER'

    FastStartFailoverLagLimit       = '0'

    CommunicationTimeout            = '180'

    ObserverReconnect               = '0'

    FastStartFailoverAutoReinstate  = 'TRUE'

    FastStartFailoverPmyShutdown    = 'TRUE'

    BystandersFollowRoleChange      = 'ALL'

    ObserverOverride                = 'FALSE'

    ExternalDestination1            = ''

    ExternalDestination2            = ''

    PrimaryLostWriteAction          = 'CONTINUE'

    ConfigurationWideServiceName    = 'orcl19_CFG'




Fast-Start Failover:  Disabled




Configuration Status:

SUCCESS




DGMGRL>

Now, add both ZDLRA entries. Look that connect identifier it is the same as the credential name. This is a key point too.

DGMGRL> add recovery_appliance zdlras1 as connect identifier is 'zdlras1-scan:1521/zdlras1:VPCZDLRA';

Recovery Appliance "zdlras1" added

DGMGRL> add recovery_appliance zdlras2 as connect identifier is 'zdlras2-scan:1521/zdlras2:VPCZDLRA';

Recovery Appliance "zdlras2" added

DGMGRL> show configuration verbose;




Configuration - orcl19




  Protection Mode: MaxAvailability

  Members:

  orcl19  - Primary database

    orcl19s - Physical standby database

      zdlras2 - Recovery appliance (disabled)

        ORA-16905: The member was not enabled yet.




    zdlras1 - Recovery appliance (disabled)

      ORA-16905: The member was not enabled yet.




  Properties:

    FastStartFailoverThreshold      = '30'

    OperationTimeout                = '30'

    TraceLevel                      = 'USER'

    FastStartFailoverLagLimit       = '0'

    CommunicationTimeout            = '180'

    ObserverReconnect               = '0'

    FastStartFailoverAutoReinstate  = 'TRUE'

    FastStartFailoverPmyShutdown    = 'TRUE'

    BystandersFollowRoleChange      = 'ALL'

    ObserverOverride                = 'FALSE'

    ExternalDestination1            = ''

    ExternalDestination2            = ''

    PrimaryLostWriteAction          = 'CONTINUE'

    ConfigurationWideServiceName    = 'orcl19_CFG'




Fast-Start Failover:  Disabled




Configuration Status:

SUCCESS




DGMGRL>

Look that now I have both ZDLRA in the config, but they did not enable yet. Before I enable it, I need to configure the RedoRoutes. If I do not do that, both ZDLRA will be enabled for the primary site as log_archive_dest. This is a key point too.

DGMGRL> edit database orcl19 set property RedoRoutes = '(orcl19 : orcl19s sync, zdlras1 async)( orcl19s : zdlras2 async )';

Property "redoroutes" updated

DGMGRL> edit database orcl19s set property RedoRoutes = '(orcl19s : orcl19 sync, zdlras2 async)( orcl19 : zdlras1 async )';

Property "redoroutes" updated

DGMGRL>

Just to explain what this means:

RedoRoutes for ORCL19: When ORCL19 is the primary database, it will send the redo to ORCL19S in sync mode, and to ZDLRAS1 in async mode. And at the same time, the ORCL19S will send their redo (stand by redo – since it is standby) to ZDLRAS2 in async mode.
RedoRoutes for ORCL19S: When ORCL19S is primary database, it will send the redo to ORCL19 in sync mode, and to ZDLRAS2 in async mode. And at the same time, the ORCL19 will send their redo (stand by redo – since it is standby) to ZDLRAS1 in async mode.

Now, I need to enable ZDLRA’s. I will do one by one:

DGMGRL> show configuration;




Configuration - orcl19




  Protection Mode: MaxAvailability

  Members:

  orcl19  - Primary database

    orcl19s - Physical standby database

      zdlras2 - Recovery appliance (disabled)

        ORA-16905: The member was not enabled yet.




    zdlras1 - Recovery appliance (disabled)

      ORA-16905: The member was not enabled yet.




Fast-Start Failover:  Disabled




Configuration Status:

SUCCESS   (status updated 35 seconds ago)




DGMGRL>

DGMGRL> enable recovery_appliance zdlras1;

Enabled.

DGMGRL>

And at primary (ORCL19) alertlog will show (look the parameter set):

2019-11-09T21:36:24.189975+01:00

Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(ORCL19,orcl19s,zdlras1)']

2019-11-09T21:36:24.205453+01:00

ALTER SYSTEM SET log_archive_config='dg_config=(ORCL19,orcl19s,zdlras1)' SCOPE=BOTH;

2019-11-09T21:36:36.625928+01:00

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

2019-11-09T21:36:36.638226+01:00

RSM0 (PID:9918): Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST [krsd.c:18157]

2019-11-09T21:36:36.650443+01:00

ALTER SYSTEM SET log_archive_dest_3='service="zdlras1-scan:1521/zdlras1:VPCZDLRA"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="zdlras1" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;

2019-11-09T21:36:36.670412+01:00

ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH;

2019-11-09T21:36:36.670547+01:00

ALTER SYSTEM ARCHIVE LOG

2019-11-09T21:36:36.683698+01:00

Thread 1 cannot allocate new log, sequence 242

Checkpoint not complete

  Current log# 3 seq# 241 mem# 0: +DATA/ORCL19/ONLINELOG/group_3.260.1022714131

  Current log# 3 seq# 241 mem# 1: +RECO/ORCL19/ONLINELOG/group_3.259.1022714135

2019-11-09T21:36:38.922616+01:00

LGWR (PID:7900): SRL selected to archive T-1.S-242

LGWR (PID:7900): SRL selected for T-1.S-242 for LAD:2

2019-11-09T21:36:38.934895+01:00

Thread 1 advanced to log sequence 242 (LGWR switch)

  Current log# 1 seq# 242 mem# 0: +DATA/ORCL19/ONLINELOG/group_1.258.1022714111

  Current log# 1 seq# 242 mem# 1: +RECO/ORCL19/ONLINELOG/group_1.257.1022714115

2019-11-09T21:36:38.974260+01:00

RSM0 (PID:9918): Archived Log entry 588 added for T-1.S-241 ID 0x135c2429 LAD:1




And in alertlog from standby (ORCL19S):

2019-11-09T21:36:23.036628+01:00

Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(orcl19s,orcl19,zdlras1)']

2019-11-09T21:36:23.051110+01:00

ALTER SYSTEM SET log_archive_config='dg_config=(orcl19s,orcl19,zdlras1)' SCOPE=BOTH;

2019-11-09T21:36:38.755320+01:00

 rfs (PID:22533): Standby controlfile consistent with primary

2019-11-09T21:36:38.770320+01:00

 rfs (PID:22533): Selected LNO:4 for T-1.S-242 dbid 324042554 branch 1022714106

2019-11-09T21:36:38.773001+01:00

MRP0 (PID:11871): Media Recovery Waiting for T-1.S-242 (in transit)

2019-11-09T21:36:38.776175+01:00

ARC0 (PID:9009): Archived Log entry 304 added for T-1.S-241 ID 0x135c2429 LAD:1

2019-11-09T21:36:39.836191+01:00

Recovery of Online Redo Log: Thread 1 Group 4 Seq 242 Reading mem 0

  Mem# 0: +DATA/ORCL19S/ONLINELOG/group_4.279.1023289067

  Mem# 1: +RECO/ORCL19S/ONLINELOG/group_4.269.1023289069

And now, the second ZDLRA:

DGMGRL> enable recovery_appliance zdlras2;

Enabled.

DGMGRL>

Again, at primary (ORCL19) alertlog:

2019-11-09T21:37:32.341087+01:00

Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(ORCL19,orcl19s,zdlras1,zdlras2)']

2019-11-09T21:37:32.353276+01:00

ALTER SYSTEM SET log_archive_config='dg_config=(ORCL19,orcl19s,zdlras1,zdlras2)' SCOPE=BOTH;

2019-11-09T21:37:40.827841+01:00

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';

2019-11-09T21:37:41.943026+01:00

LGWR (PID:7900): SRL selected to archive T-1.S-243

LGWR (PID:7900): SRL selected for T-1.S-243 for LAD:2

2019-11-09T21:37:41.949207+01:00

Thread 1 advanced to log sequence 243 (LGWR switch)

  Current log# 2 seq# 243 mem# 0: +DATA/ORCL19/ONLINELOG/group_2.259.1022714121

  Current log# 2 seq# 243 mem# 1: +RECO/ORCL19/ONLINELOG/group_2.258.1022714125

2019-11-09T21:37:41.981177+01:00

ARC1 (PID:10098): Archived Log entry 591 added for T-1.S-242 ID 0x135c2429 LAD:1

And in the alertlog from stabdby (ORCL19S):

2019-11-09T21:37:31.175857+01:00

Data Guard Broker executes SQL [alter system set log_archive_config='dg_config=(orcl19s,orcl19,zdlras1,zdlras2)']

2019-11-09T21:37:31.193280+01:00

ALTER SYSTEM SET log_archive_config='dg_config=(orcl19s,orcl19,zdlras1,zdlras2)' SCOPE=BOTH;

2019-11-09T21:37:37.424268+01:00

RSM0 (PID:10393): Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST [krsd.c:18157]

2019-11-09T21:37:37.438735+01:00

ALTER SYSTEM SET log_archive_dest_2='service="zdlras2-scan:1521/zdlras2:VPCZDLRA"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="zdlras2" net_timeout=30','valid_for=(standby_logfile,all_roles)' SCOPE=BOTH;

2019-11-09T21:37:37.476545+01:00

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

2019-11-09T21:37:41.778391+01:00

MRP0 (PID:11871): Media Recovery Waiting for T-1.S-243 (in transit)

2019-11-09T21:37:41.791532+01:00

ARC0 (PID:9009): Archived Log entry 312 added for T-1.S-242 ID 0x135c2429 LAD:1

2019-11-09T21:37:41.793625+01:00

 rfs (PID:22533): Standby controlfile consistent with primary

2019-11-09T21:37:41.800377+01:00

 rfs (PID:22533): Selected LNO:5 for T-1.S-243 dbid 324042554 branch 1022714106

2019-11-09T21:37:42.826630+01:00

Recovery of Online Redo Log: Thread 1 Group 5 Seq 243 Reading mem 0

  Mem# 0: +DATA/ORCL19S/ONLINELOG/group_5.278.1023289071

  Mem# 1: +RECO/ORCL19S/ONLINELOG/group_5.268.1023289073

And now the conf reports:

DGMGRL> show configuration;




Configuration - orcl19




  Protection Mode: MaxAvailability

  Members:

  orcl19  - Primary database

    orcl19s - Physical standby database

      zdlras2 - Recovery appliance (receiving current redo)

    zdlras1 - Recovery appliance




Fast-Start Failover:  Disabled




Configuration Status:

SUCCESS   (status updated 54 seconds ago)




DGMGRL>

As you can see, the primary is cascading the redo to ORCL19S and ZDLRAS1. And the ORCL19S is cascading the redo (standby redo) to ZDLRAS2.

Checking ZERO RPO

To check if everything is fine, and we have zero RPO in both sited I made a simple archivelog at the primary site and check the cascade of events.

At Primary ORCL19:

[oracle@orcl19p ~]$ rman target=/ catalog=vpczdlra/oracle@zdlras1




Recovery Manager: Release 19.0.0.0.0 - Production on Sat Nov 9 21:43:25 2019

Version 19.5.0.0.0




Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.




connected to target database: ORCL19 (DBID=324042554)

connected to recovery catalog database

PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current

PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current




RMAN> alter system archive log current;




Statement processed




RMAN> list copy of archivelog all completed after "sysdate - 5/1440";




List of Archived Log Copies for database with db_unique_name ORCL19

=====================================================================




Key     Thrd Seq     S Low Time

------- ---- ------- - -------------------

3296    1    243     A 09/11/2019 21:37:41

        Name: +RECO/ORCL19/ARCHIVELOG/2019_11_09/thread_1_seq_243.389.1023918423







RMAN> list backup of archivelog all completed after "sysdate - 5/1440";







List of Backup Sets

===================







BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ -------------------

3304    322.00K    SBT_TAPE    00:00:02     09/11/2019 21:48:02

        BP Key: 3305   Status: AVAILABLE  Compressed: YES  Tag: TAG20191109T214800

        Handle: $RSCN_1_RTIM_1022714106_THRD_1_SEQ_243_CTKEY_3206_BACKUP   Media:




  List of Archived Logs in backup set 3304

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    243     3082409    09/11/2019 21:37:41 3083705    09/11/2019 21:47:03




RMAN>

At Standby ORCL19S

[oracle@orcl19s ~]$ rman target=/ catalog=vpczdlra/oracle@zdlras2




Recovery Manager: Release 19.0.0.0.0 - Production on Sat Nov 9 21:44:58 2019

Version 19.5.0.0.0




Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.




connected to target database: ORCL19 (DBID=324042554, not open)

connected to recovery catalog database

PL/SQL package VPCZDLRA.DBMS_RCVCAT version 19.03.00.00. in RCVCAT database is not current

PL/SQL package VPCZDLRA.DBMS_RCVMAN version 19.03.00.00 in RCVCAT database is not current




RMAN> list copy of archivelog all completed after "sysdate - 5/1440";




List of Archived Log Copies for database with db_unique_name ORCL19S

=====================================================================




Key     Thrd Seq     S Low Time

------- ---- ------- - -------------------

3428    1    243     A 09/11/2019 21:37:41

        Name: +RECO/ORCL19S/ARCHIVELOG/2019_11_09/thread_1_seq_243.371.1023918423







RMAN> list backup of archivelog all completed after "sysdate - 5/1440";







List of Backup Sets

===================







BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ -------------------

3419    322.00K    SBT_TAPE    00:00:00     09/11/2019 21:47:10

        BP Key: 3420   Status: AVAILABLE  Compressed: YES  Tag: TAG20191109T214710

        Handle: $RSCN_1_RTIM_1022714106_THRD_1_SEQ_243_CTKEY_3306_BACKUP   Media:




  List of Archived Logs in backup set 3419

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    243     3082409    09/11/2019 21:37:41 3083705    09/11/2019 21:47:03




RMAN>

As you can see here, the sequence 243 was created at primary, sent by DG to ORCL19S. And ZDLRAS1 created the backup for archivelog automatically, and the same was made by ZDLRAS2.

Multiple levels of protection

The idea of this post was to show how to protect a big environment with ZDLRA, reaching ZERO RPO for both sites, and going beyond the protection defined by Oracle MAA docs. If you look closely, the environment that I showed here has zero RPO and zero RTO. You will lose data only if you have a catastrophic failure in both sites (but you can protect even more if you use tape backup offloads from ZDLRA).

As told before, these critical/extreme critical environments need to follow some strict rules for data protection. Adding ZDLRA for it, provide an additional level of protection and allow you to use some features like incremental backup strategy.

ZDLRA, Real-Time REDO and RPO ZERO

Category: Engineer System Author: Fernando Simon (Board Member) Date: 5 years ago Comments: 0

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.

ZDLRA, Real-Time Redo

Category: Engineer System Author: Fernando Simon (Board Member) Date: 5 years ago Comments: 0

ZDLRA, Real-Time Redo

Real-time redo transport is the feature that allows you to reduce to zero the RPO (Recovery Point Objective) for your database. Check how to configure real-time redo, the steps, parameters, and other details that need to be modified to enable it.

The idea behind real-time redo transport it is easy, basically the ZDLRA it is a remote destination for your redo log buffers/archivelogs of your database. It is really, really, similar to what occurs for data guard configurations (but here you don’t need to set all datafiles as an example). It is not the same too because ZDLRA can detect if the database stops/crash and will generate the archivelog (at ZDLRA side) with all the received redo and this can be used to restore to, at least zero/sub-seconds, of data loss.

Using real-time redo it is the only way to reach RPO zero. With other features of ZDLRA, you can have a better backup window time (but just that) using incremental backups. Just using real-time redo you reach zero RPO and this impacts directly how to configure for MAA compliance. There are a lot of options and level of protection for MAA that you can check at “Maximum Availability Architecture (MAA) – On-Premises HA Reference Architectures 2019”, “Maximum Availability Architecture Best Practices for Oracle Cloud”, “Oracle MAA Reference Architectures”, “Maximum Availability Architecture – Best Practices for Oracle Database 19c”.

This post starts from one environment that you already enrolled in the database at ZDLRA. I already wrote about how to do that, you can check here in my previous post. This is the first post about real-time redo, here you will see how to configure and verify it is working.

Pre-Check

The way that I configure real-time redo for database it is a little different than what is in the official docs. I added some pre-checks and another order for some steps, but the result is the same. If you want to check the official steps you can check at Zero Data Loss Recovery Appliance Protected Database Configuration Guide or at Zero Data Loss Recovery Appliance Administrator’s Guide.

Just contextualization of environment:

ZDLRA database name: zdlras1
Database name: orcl19
VPC user: cat_zdlra

Check Configuration

The first step that I do is verify the current configuration. Mainly the preexistence of wallet:

[oracle@orcloel7 ~]$ mkstore -wrl /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/ra_wallet -listCredential

Oracle Secret Store Tool Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.




List credential (index: connect_string username)

1: zdlras1-scan:1521/zdlras1:CAT_ZDLRA cat_zdlra

[oracle@orcloel7 ~]$

[oracle@orcloel7 ~]$ cat $ORACLE_HOME/dbs/raORCL19.ora

RA_WALLET='LOCATION=file:/u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/ra_wallet CREDENTIAL_ALIAS=zdlras1-scan:1521/zdlras1:CAT_ZDLRA'

[oracle@orcloel7 ~]$

The most import here is the credential name (zdlras1-scan:1521/zdlras1:CAT_ZDLRA in this case) because it needs to be the same name for the database file that points to RA_WALLET. The config file is for each database/instance and has the name ra<DBNAME>.ora (it was already configured by the previous post pointed before).

After that, I check if the sqlnet.ora and tnsnames.ora have the information for wallet and TNS entry respectively:

[oracle@orcloel7 ~]$ cat $ORACLE_HOME/network/admin/sqlnet.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)




SQLNET.WALLET_OVERRIDE = true




WALLET_LOCATION =

(

   SOURCE =

      (METHOD = FILE)

      (METHOD_DATA =

         (DIRECTORY = /u01/app/oracle/product/19.3.0.0/dbhome_1/dbs/ra_wallet)

      )

)

[oracle@orcloel7 ~]$

[oracle@orcloel7 ~]$ tnsping zdlras1




TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-OCT-2019 23:30:36




Copyright (c) 1997, 2019, Oracle.  All rights reserved.




Used parameter files:

/u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/sqlnet.ora







Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zdlras1-scan)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = zdlras1)))

OK (0 msec)

[oracle@orcloel7 ~]$

Check Database Configuration

After check outside of the database config, it is important to check some database requirements. The most import is verifying if REMOTE_LOGIN_PASSWORDFILE it is set as “exclusive” or “shared”:

[oracle@orcloel7 ~]$ sqlplus / as sysdba




SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 1 23:32:09 2019

Version 19.3.0.0.0




Copyright (c) 1982, 2019, Oracle.  All rights reserved.







Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0




SQL>

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE;




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

remote_login_passwordfile            string      EXCLUSIVE

SQL>

SQL>

Other parameters to verify are log_archive_config and db_unique_name. They are important to verify the current configuration in cases of databases using DG as an example.

Configuring Real-Time Redo

After these pre-checks, we can start the configuration. Here I set everything at spfile to, after configuring everything, bounce the database and start with everything up and running.

REDO_TRANSPORT_USER

It is the parameter that defines the user that the database utilizes to send the redo to another database. This user needs to exist in both databases (for DG), and because of that needs to exist in ZDLRA. When using ZDLRA and real-time redo it needs to be the same as VPC inside the database. So, the same VPC user needs to exists in database side and set for this parameter:

SQL> show parameter redo_transport_user;




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

redo_transport_user                  string

SQL>

SQL> alter system set redo_transport_user='CAT_ZDLRA' scope = spfile SID='*';




System altered.




SQL>

SQL> create user cat_zdlra identified by s3nhazdlra;




User created.




SQL> grant sysoper to cat_zdlra;




Grant succeeded.




SQL>

As you can see the user CAT_ZDLRA was created inside of the database. The user needs to have at least the grant SYSOPER to work properly.

LOG_ARCHIVE_CONFIG

This parameter works the same for DG environments, and since ZDLRA real-time redo it is a remote destination for archive, we do the same. Here, we set this specifying the database unique name for ZDLRA here (zdlras1 in my current environment):

SQL> show parameter log_archive_config;




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

log_archive_config                   string

SQL>

SQL> show parameter db_unique_name;




NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_unique_name                       string      ORCL19

SQL>

SQL> ALTER SYSTEM SET log_archive_config='DG_CONFIG=(orcl19,zdlras1)' SCOPE=SPFILE SID='*';




System altered.




SQL>

If you already have DG configured, you need to just add the entry for ZDLRA database here in the parameter.

LOG_ARCHIVE_DEST_n

SQL> ALTER SYSTEM SET log_archive_dest_2='SERVICE="zdlras1-scan:1521/zdlras1:CAT_ZDLRA" ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=1 max_connections=1 reopen=10 DB_UNIQUE_NAME=zdlras1 net_timeout=8 group=1 priority=1 VALID_FOR=(ONLINE_LOGFILE,ALL_ROLES)' scope = spfile sid = '*';




System altered.




SQL>

SQL> alter system set log_archive_dest_state_2=DEFER scope = spfile sid = '*';




System altered.




SQL>

The secrets are:

SERVICE: it is the connection to ZDLRA. It can be a TNS service name, or an ezconnect. If you sawm I have the credential name, as the same of ezconnection. I recommend to use this, so you know what the credential it is for. If you use TNS entry for SERVICE, recommend having a credential name with the same name.
ASYNC NOAFFIRM: This is controversy (I will explain later), but this defines the mode that redo log is shipped for the remote destination.
DB_UNIQUE_NAME: Database unique name of recovery appliance database.

All the others are the normal parameters for DG config and you can check at LOG_ARCHIVE_DEST_n Parameter Attributes.

As I told before, the ASYNC NOAFFIRM are controversial because if you look it was defined as ASYNC, and it is the same in the ZDLRA docs. You can read here at “Protection of Ongoing Transactions” (or at “How Real-Time Redo Transport Works”):

“Redo data from the protected database is written asynchronously to the Recovery Appliance as it is generated. Load on production database servers is minimized because redo data is shipped directly from the memory to the Recovery Appliance without the involvement of disk I/O”

“To avoid degrading the performance of the protected database, protected databases transfer redo asynchronously to the Recovery Appliance. If a protected database is lost, zero to subsecond data loss is expected in most cases.”

The controversy here is because the appliance it is ZERO DATA LOSS, and ASYNC does not guarantee zero data loss. So, is it a scam case and we need to change the name for Non-Zero Data Loss Recovery Appliance? No, they just are trying to remember you that to minimize the impact over the database, it was configured in async mode. But you can configure in SYNC mode, it is allowed, but remember that this can/will impact the database.

If you want to understand the impact over that SYNC/ASYNC/AFFIRM/NOAFFIRM have over the database, read the “Best Practices for Synchronous Redo Transport Data Guard and Active Data Guard” doc. It is fundamental.

I will cover this in the next post about real-time redo.

Using Real-Time Redo

After pre-check and configure all the parameters above, we can start to use it. If you have some part already configured (like the redo transport user, and the wallet), you can config without restart database. But, here, I made the restart of the database (and enabled the log_archive_dest_state_n).

The best way to show/check real-time redo working is through rman, where I can describe the archivelogs and backups that exist for each one:

 [oracle@orcloel7 ~]$ rman target=/ catalog=cat_zdlra/s3nhazdlra@zdlras1




Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 1 23:57:42 2019

Version 19.3.0.0.0




Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.




connected to target database: ORCL19 (DBID=310627084)

connected to recovery catalog database




RMAN> list copy of archivelog all;




specification does not match any archived log in the repository




RMAN> alter system archive log current;




Statement processed




RMAN> list copy of archivelog all;




List of Archived Log Copies for database with db_unique_name ORCL19

=====================================================================




Key     Thrd Seq     S Low Time

------- ---- ------- - -------------------

4329    1    47      A 02-10-2019_00:09:32

        Name: /u01/app/oracle/oradata/ORCL19/archivelog/2019_10_02/o1_mf_1_47_gs7mzgkn_.arc







RMAN> list backup of archivelog all completed after "sysdate - 5/1440";







List of Backup Sets

===================







BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ -------------------

4365    595.50K    SBT_TAPE    00:00:00     02-10-2019_00:15:34

        BP Key: 4366   Status: AVAILABLE  Compressed: YES  Tag: TAG20191002T001534

        Handle: $RSCN_1920977_RTIM_1009298641_THRD_1_SEQ_47_CTKEY_4222_BACKUP   Media:




  List of Archived Logs in backup set 4365

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    47      2370920    02-10-2019_00:09:32 2372594    02-10-2019_00:15:10




RMAN>

Look the example above, I started with no copy of archivelogs locally. After I made a switch for archivelog and sequence 47 was generated. Instantly, if I do a list backup archivelog you can see that already exists one copy of the same sequence.

For the copy, you can identify that it is a real-time redo and not a copy because of the handle. It has a specific format starting as $RSCN.

If I do one more time, the same behavior:

RMAN> list copy of archivelog all;




List of Archived Log Copies for database with db_unique_name ORCL19

=====================================================================




Key     Thrd Seq     S Low Time

------- ---- ------- - -------------------

4329    1    47      A 02-10-2019_00:09:32

        Name: /u01/app/oracle/oradata/ORCL19/archivelog/2019_10_02/o1_mf_1_47_gs7mzgkn_.arc







RMAN> alter system archive log current;




Statement processed




RMAN> list copy of archivelog all;




List of Archived Log Copies for database with db_unique_name ORCL19

=====================================================================




Key     Thrd Seq     S Low Time

------- ---- ------- - -------------------

4329    1    47      A 02-10-2019_00:09:32

        Name: /u01/app/oracle/oradata/ORCL19/archivelog/2019_10_02/o1_mf_1_47_gs7mzgkn_.arc




4406    1    48      A 02-10-2019_00:15:10

        Name: /u01/app/oracle/oradata/ORCL19/archivelog/2019_10_02/o1_mf_1_48_gs7n1ck8_.arc







RMAN> list backup of archivelog all completed after "sysdate - 5/1440";







List of Backup Sets

===================







BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ -------------------

4365    595.50K    SBT_TAPE    00:00:00     02-10-2019_00:15:34

        BP Key: 4366   Status: AVAILABLE  Compressed: YES  Tag: TAG20191002T001534

        Handle: $RSCN_1920977_RTIM_1009298641_THRD_1_SEQ_47_CTKEY_4222_BACKUP   Media:




  List of Archived Logs in backup set 4365

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    47      2370920    02-10-2019_00:09:32 2372594    02-10-2019_00:15:10




BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ -------------------

4445    41.00K     SBT_TAPE    00:00:00     02-10-2019_00:16:32

        BP Key: 4446   Status: AVAILABLE  Compressed: YES  Tag: TAG20191002T001632

        Handle: $RSCN_1920977_RTIM_1009298641_THRD_1_SEQ_48_CTKEY_4294_BACKUP   Media:




  List of Archived Logs in backup set 4445

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    48      2372594    02-10-2019_00:15:10 2372793    02-10-2019_00:16:11




RMAN>

Another point is that if you try to do the backup of this archivelog you will see that they was already backed:

RMAN> BACKUP DEVICE TYPE SBT FILESPERSET 100 FORMAT '%U' ARCHIVELOG ALL NOT BACKED UP TAG 'BKP-ARCH';




Starting backup at 02-10-2019_00:18:30

current log archived

released channel: ORA_DISK_1

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=86 device type=SBT_TAPE

channel ORA_SBT_TAPE_1: RA Library (ZDLRAS1) SID=93E18A9FC482277FE053010310AC3886

skipping archived logs of thread 1 from sequence 47 to 48; already backed up

Real-Time Redo… in real

The real-time redo transport configuration that I showed here just covered the configuration part. You can use it as a guide do understand what you need to configure and check. But this post does not cover full details about it.

For the next post about real-time redo, I will explore more details about the controversy about SYNC/ASYNC mode for transport. I will test and simulate other details, like a complete crash of the environment and verify if everything will be safe and we reach zero RPO.

But in the end, we have the full “Delta Push” for our database. The incremental forever strategy and real-time redo enabled.

ZDLRA Internals, Virtual Full Backup

Category: Engineer System Author: Fernando Simon (Board Member) Date: 5 years ago Comments: 0

ZDLRA Internals, Virtual Full Backup

Virtual Full Backup probably is the most know feature of Oracle Zero Data Loss Recovery Appliance (ZDLRA) and you can check here how it works. In this post I will show how virtual full backup works internally and integrate INDEX_BACKUP task with tables like PLANS, PLAN_DETAILS, CHUNKS, and BLOCKS.

About the internal tables, you can check my previous post “ZDLRA Internals, Tables and Storage” where I explained details about that. To understand the INDEX_BACKUP task, check my post “ZDLRA Internals, INDEX_BACKUP task in details”. But if you know nothing and want to start reading about ZDLRA, you can check my post “Understanding ZDLRA” and check all the features and details.

The base for this article is virtual full backup and incremental forever strategy. I explained both at “ZDLRA, Virtual Full Backup and Incremental Forever” and I included hot it’s work integrated with rman backup. Basically, after an initial backup level 0, you execute just level 1 backups and ZDLRA generated a virtual backup level 0. But here, in this post, I will show you how it works in some internal details.

Database Environment

In this post, I used a new datafile with 1MB (block size of 8k) and I create a small table to load some data. After every load, I took backup for the datafile (level 1). The idea is to show how ZDLRA will index the backups, generate, and store internally the virtual full backup. The database here runs over 19c, and the ZDLRA it is running 19c version too. But, this works the same for every database version supported, and for every ZDLRA version.

Creating the tablespace, checking the datafile, and creating the table:

SQL> create tablespace simon datafile '/u01/app/oracle/oradata/ORCL19/simon01.dbf' size 1m autoextend on next 1m maxsize 10m;




Tablespace created.




SQL>

SQL> select file_id from dba_data_files where tablespace_name = 'SIMON';




   FILE_ID

----------

         5




SQL>

SQL> create table test(c1 decimal(1,0), c2 varchar2(128)) tablespace simon;




Table created.




SQL> insert into test (c1, c2) values (0, 'SIMON');




1 row created.




SQL> commit;




Commit complete.




SQL>

And after that, doing the backup level 0 for the datafile:

RMAN> list backup of datafile 5;




specification does not match any backup in the repository




RMAN> BACKUP INCREMENTAL LEVEL 0 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 5;




Starting backup at 22-09-2019_17:54:27

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=75 device type=SBT_TAPE

channel ORA_SBT_TAPE_1: RA Library (ZDLRAS1) SID=9327516A92A43E0DE053010310ACCB56

channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL19/simon01.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 22-09-2019_17:54:28

channel ORA_SBT_TAPE_1: finished piece 1 at 22-09-2019_17:54:29

piece handle=ORCL19_2aucdsak_1_1 tag=TAG20190922T175427 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

Finished backup at 22-09-2019_17:54:29




Starting Control File and SPFILE Autobackup at 22-09-2019_17:54:29

piece handle=c-310627084-20190922-05 comment=API Version 2.0,MMS Version 12.2.0.2

Finished Control File and SPFILE Autobackup at 22-09-2019_17:54:30




RMAN>




RMAN> list backup of datafile 5;







List of Backup Sets

===================







BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

2729    Incr 0  40.00K     SBT_TAPE    00:00:02     22-09-2019_17:54:30

        BP Key: 2730   Status: AVAILABLE  Compressed: YES  Tag: TAG20190922T175427

        Handle: VB$_1887643964_2728I   Media:

  List of Datafiles in backup set 2729

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  5    0  Incr 2320763    22-09-2019_17:54:28              NO    /u01/app/oracle/oradata/ORCL19/simon01.dbf




RMAN>

So, as you can see, the tablespace SIMON was created (datafile #5). After that, the table TEST was created and some data loaded. At the end, the first backup of datafile was done and this was indexed by ZDLRA.

Basic information for database

For this database (database name ORCL19), inside internal tables of ZDLRA, we can see that have the DB_KEY as 2202 and DB_INCKEY as 2203. The DF_KEY for the datafile is 2689. This info is important to identify correctly the backups.

[oracle@zdlras1n1 ~]$ sqlplus rasys/change^Me2




SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 22 17:53:21 2019

Version 19.3.0.0.0




Copyright (c) 1982, 2019, Oracle.  All rights reserved.




Last Successful login time: Sun Sep 22 2019 17:39:03 +02:00




Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0




SQL> set linesize 250

SQL> select db_key, dbinc_key from rc_database where name = 'ORCL19';




    DB_KEY  DBINC_KEY

---------- ----------

      2202       2203




SQL>

SQL> select df_key, file#, ts#, create_scn, create_time, block_size, blocks from df where dbinc_key = 2203 and file# = 5;




    DF_KEY      FILE#        TS# CREATE_SCN CREATE_TI BLOCK_SIZE     BLOCKS

---------- ---------- ---------- ---------- --------- ---------- ----------

      2689          5          6    2319183 22-SEP-19       8192        128




SQL>

Virtual Full Backup and PLANS

After the backup ingests, and the task INDEX_BACKUP finished at ZDLRA, we have one virtual full backup linked with the datafile. This information came from VBDF and PLANS tables:

SQL> select vb_key, ckp_scn, vcbp_key, srcbp_key, blocks, chunks_used from vbdf where db_key = 2202 and df_key = 2689 order by vb_key asc;




    VB_KEY    CKP_SCN   VCBP_KEY  SRCBP_KEY     BLOCKS CHUNKS_USED

---------- ---------- ---------- ---------- ---------- -----------

      2728    2320763       2730       2701        128           1




SQL>

SQL> select * from plans where db_key = 2202 and df_key = 2689 order by vb_key asc;




      TYPE     DB_KEY     VB_KEY     DF_KEY    TASK_ID        OLD   BLKSREAD    MAXRANK  NUMCHUNKS   READSIZE  NEEDCHUNK  FREECHUNK

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

         8       2202       2728       2689                               19          1          1




SQL>

Above we can see that this virtual full backup with VB_KEY have 128 blocks (128 * 8K = 1M) and have one plan for this backup. This virtual full backup/plan has just 19 blocks (BLKSREAD) and used 1 chunk to store it (NUMCHUNKS).

But this not show how ZDLRA see the backup, the virtual full backup store the information at PLAN_DETAILS table. Reading this table we can see the foundation of virtual full backup:

SQL> select * from plans_details where df_key = 2689 order by vb_key asc, blockno asc;




    DF_KEY       TYPE     VB_KEY    BLKRANK    BLOCKNO    CHUNKNO    NUMBLKS    COFFSET   NUMBYTES

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

      2689          8       2728          1          0          1          1       8192      24576

      2689          8       2728          1          2          1         17      32788       2167

      2689          8       2728          1 4294967295          1          1      34955        294




SQL>

Let’s explain what this means:

The datafile block 0 (column BLOCKNO) until block 1 (BLOCKNO+NUNBLKS) are stored at chunk 1.
The datafile block 2 (column BLOCKNO) until block 19 (BLOCKNO+NUNBLKS) are stored at chunk 1
The datafile block 4294967295 (the last block of datafile) is stored at chunks 1.

To show this I created the schema below. This represents the first 13 blocks of the virtual full backup and will help to visualize the virtual full backup next. So, the virtual full backup with VB_KEY 2728 can be reconstructed following the PLANS_DETAILS as:

Subsequent backups

If we continue to modify data in this tablespace/datafile and execute new incremental backup level 1 we can see more details. So, first, add more data and do a backup:

SQL> BEGIN

  2    FOR i IN 1 .. 300 LOOP

  3      insert into test (c1, c2) values (2, DBMS_RANDOM.STRING('P', 128));

  4

  5      if (MOD(i, 100) = 0) then

  6         commit;

  7      end if;

  8

  9    END LOOP;

 10

 11    commit;

 12  END;

 13  /




PL/SQL procedure successfully completed.




SQL>




RMAN> BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT FILESPERSET 1 DATAFILE 5;




Starting backup at 22-09-2019_18:34:30

using channel ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL19/simon01.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 22-09-2019_18:34:31

channel ORA_SBT_TAPE_1: finished piece 1 at 22-09-2019_18:34:32

piece handle=ORCL19_2cucduln_1_1 tag=TAG20190922T183430 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

Finished backup at 22-09-2019_18:34:32




Starting Control File and SPFILE Autobackup at 22-09-2019_18:34:32

piece handle=c-310627084-20190922-06 comment=API Version 2.0,MMS Version 12.2.0.2

Finished Control File and SPFILE Autobackup at 22-09-2019_18:34:36




RMAN>

And now inside of ZDLRA we have two virtual full backups and two plans (for PLANS look the number of blocks read – this is normal since I added more data above):

SQL> select vb_key, ckp_scn, vcbp_key, srcbp_key, blocks, chunks_used from vbdf where db_key = 2202 and df_key = 2689 order by vb_key asc;




    VB_KEY    CKP_SCN   VCBP_KEY  SRCBP_KEY     BLOCKS CHUNKS_USED

---------- ---------- ---------- ---------- ---------- -----------

      2728    2320763       2730       2701        128           1

      2768    2322525       2770       2735        128           1




SQL>

SQL> select * from plans where db_key = 2202 and df_key = 2689 order by vb_key asc;




      TYPE     DB_KEY     VB_KEY     DF_KEY    TASK_ID        OLD   BLKSREAD    MAXRANK  NUMCHUNKS   READSIZE  NEEDCHUNK  FREECHUNK

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

         8       2202       2728       2689                               19          1          1

         1       2202       2768       2689                               27          1          2




SQL>

The virtual full backups have these PLAN_DETAILS:

SQL> select * from plans_details where df_key = 2689 order by vb_key asc, blockno asc;




    DF_KEY       TYPE     VB_KEY    BLKRANK    BLOCKNO    CHUNKNO    NUMBLKS    COFFSET   NUMBYTES

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

      2689          8       2728          1          0          1          1       8192      24576

      2689          8       2728          1          2          1         17      32788       2167

      2689          8       2728          1 4294967295          1          1      34955        294

      2689          1       2768          1          0       1025          1       8192      24576

      2689          1       2768          1          2       1025          2      32788        252

      2689          1       2768          1          4          1          4      33038        408

      2689          1       2768          1          8       1025         16      33040      45339

      2689          1       2768          1         71          1          3      34703        252

      2689          1       2768          1 4294967295       1025          1      78379        293




9 rows selected.




SQL>

Checking in details the last virtual full backup, VB_KEY 2768, we can see interesting things and start to understand how it’s work. So, to “mount” the virtual full backup 2768 we have:

The datafile block 0 (column BLOCKNO) until block 1 (BLOCKNO+NUNBLKS) are stored at chunk 1025.
The datafile block 2 (column BLOCKNO) until block 4 (BLOCKNO+NUNBLKS) are stored at chunk 1025.
The datafile block 4 (column BLOCKNO) until block 8 (BLOCKNO+NUNBLKS) are stored at chunk 1 (and came from previous virtual full backup).
The datafile block 8 (column BLOCKNO) until block 24 (BLOCKNO+NUNBLKS) are stored at chunk 1025.

So, the virtual full backup with VB_KEY 2768 for DF_KEY can be reconstructed following the PLANS_DETAILS. It is the image below represents the Delta Store and actual virtual full backups:

Look that ZDLRA started to save space to store the backup, being “smart” and indexing all the blocks to needed to create the virtual full backup. But the important here it is that ZDLRA understood and indexed every datafile block that was inside of backup. And the virtual full backup “does not exist”, it is basically the index for each version of the block.

If I continue to insert/delete/update some of the lines from this table I will possibly change blocks (and same blocks already created) and if I do some subsequent backups I have these plans:

SQL> select vb_key, ckp_scn, vcbp_key, srcbp_key, blocks, chunks_used from vbdf where db_key = 2202 and df_key = 2689 order by vb_key asc;

VB_KEY CKP_SCN VCBP_KEY SRCBP_KEY BLOCKS CHUNKS_USED

———- ———- ———- ———- ———- ———–

2728 2320763 2730 2701 128 1

2768 2322525 2770 2735 128 1

2818 2323607 2820 2779 128 1

2874 2324062 2876 2829 128 1

2936 2324792 2938 2885 128 1

SQL> select * from plans where db_key = 2202 and df_key = 2689 order by vb_key asc;

TYPE DB_KEY VB_KEY DF_KEY TASK_ID OLD BLKSREAD MAXRANK NUMCHUNKS READSIZE NEEDCHUNK FREECHUNK

———- ———- ———- ———- ———- ———- ———- ———- ———- ———- ———- ———-

8 2202 2728 2689 19 1 1

1 2202 2768 2689 27 1 2

1 2202 2818 2689 27 1 3

1 2202 2874 2689 27 1 3

1 2202 2936 2689 27 1 4

SQL> select * from plans_details where df_key = 2689 order by vb_key asc, blockno asc;

DF_KEY TYPE VB_KEY BLKRANK BLOCKNO CHUNKNO NUMBLKS COFFSET NUMBYTES

———- ———- ———- ———- ———- ———- ———- ———- ———-

2689 8 2728 1 0 1 1 8192 24576

2689 8 2728 1 2 1 17 32788 2167

2689 8 2728 1 4294967295 1 1 34955 294

2689 1 2768 1 0 1025 1 8192 24576

2689 1 2768 1 2 1025 2 32788 252

2689 1 2768 1 4 1 4 33038 408

2689 1 2768 1 8 1025 16 33040 45339

2689 1 2768 1 71 1 3 34703 252

2689 1 2768 1 4294967295 1025 1 78379 293

2689 1 2818 1 0 2049 1 8192 24576

2689 1 2818 1 2 1025 2 32788 252

2689 1 2818 1 4 1 4 33038 408

2689 1 2818 1 8 2049 1 32788 257

2689 1 2818 1 9 1025 7 33293 36549

2689 1 2818 1 16 2049 3 33045 17304

2689 1 2818 1 19 1025 1 76821 1026

2689 1 2818 1 20 2049 4 50349 29052

2689 1 2818 1 71 1 3 34703 252

2689 1 2818 1 4294967295 2049 1 79401 301

2689 1 2874 1 0 3073 1 8192 24576

2689 1 2874 1 2 1025 2 32788 252

2689 1 2874 1 4 1 4 33038 408

2689 1 2874 1 8 3073 1 32788 262

2689 1 2874 1 9 1025 2 33293 347

2689 1 2874 1 11 3073 13 33050 94555

2689 1 2874 1 71 1 3 34703 252

2689 1 2874 1 4294967295 3073 1 127605 296

2689 1 2936 1 0 4097 1 8192 24576

2689 1 2936 1 2 1025 2 32788 252

2689 1 2936 1 4 1 4 33038 408

2689 1 2936 1 8 3073 1 32788 262

2689 1 2936 1 9 1025 2 33293 347

2689 1 2936 1 11 3073 13 33050 94555

2689 1 2936 1 71 1 3 34703 252

2689 1 2936 1 4294967295 4097 1 32788 278

35 rows selected.

SQL>

As you can see above now I have 5 virtual full backups (2728, 2768,2818,2874, and 2936) stored in 5 chunks (1, 1025, 2049,3073,4097). In this case, if I want need to read the virtual full backup 2936 I read the PLAN_DETAILS and check that:

The datafile block 0 until block 1 are stored at chunk 4097.
The datafile block 2 until block 4 are stored at chunk 1025.
The datafile block 4 until block 8 are stored at chunk 1.
The datafile block 8 are stored at chunk 3073.
The datafile block 9 until block 10 are stored at chunk 1025.
The datafile block 11 until block 24 are stored at chunk 3073.

The image below represents this:

Automated Delta Pool Space Management

Since ZDLRA contains a “self-managed” rman catalog, the backups need are managed automatically. This means that unnecessary data are deleted from time to time to avoid redundancy and to be more space-efficient. For ZDLRA this is called “automated delta pool space management”, specifically the “delta pool optimization“, to optimize the delta pool.

Internally this means that ZDLRA constantly checks the ingest backup and try to optimize the Delta Store. If I continue to change data and to do more backups we can see this in action:

SQL> select * from plans_details where df_key = 2689 order by vb_key asc, blockno asc;




    DF_KEY       TYPE     VB_KEY    BLKRANK    BLOCKNO    CHUNKNO    NUMBLKS    COFFSET   NUMBYTES

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

      2689          1       2768          1          0       1025          1       8192      24576

      2689          1       2768          1          2       1025          2      32788        252

      2689          1       2768          1          4          1          4      33038        408

      2689          1       2768          1          8       1025         16      33040      45339

      2689          1       2768          1         71          1          3      34703        252

      2689          1       2768          1 4294967295       1025          1      78379        293

      2689          1       2818          1          0       2049          1       8192      24576

      2689          1       2818          1          2       1025          2      32788        252

      2689          1       2818          1          4          1          4      33038        408

      2689          1       2818          1          8       2049          1      32788        257

      2689          1       2818          1          9       1025          7      33293      36549

      2689          1       2818          1         16       2049          3      33045      17304

      2689          1       2818          1         19       1025          1      76821       1026

      2689          1       2818          1         20       2049          4      50349      29052

      2689          1       2818          1         71          1          3      34703        252

      2689          1       2818          1 4294967295       2049          1      79401        301

      2689          1       2874          1          0       3073          1       8192      24576

      2689          1       2874          1          2       1025          2      32788        252

      2689          1       2874          1          4          1          4      33038        408

      2689          1       2874          1          8       3073          1      32788        262

      2689          1       2874          1          9       1025          2      33293        347

      2689          1       2874          1         11       3073         13      33050      94555

      2689          1       2874          1         71          1          3      34703        252

      2689          1       2874          1 4294967295       3073          1     127605        296

      2689          1       2936          1          0       4097          1       8192      24576

      2689          1       2936          1          2       1025          2      32788        252

      2689          1       2936          1          4          1          4      33038        408

      2689          1       2936          1          8       3073          1      32788        262

      2689          1       2936          1          9       1025          2      33293        347

      2689          1       2936          1         11       3073         13      33050      94555

      2689          1       2936          1         71          1          3      34703        252

      2689          1       2936          1 4294967295       4097          1      32788        278

      2689          1       3002          1          0       5121          1       8192      24576

      2689          1       3002          1          2       5121          2      32788        255

      2689          1       3002          1          4          1          4      33038        408

      2689          1       3002          1          8       3073          1      32788        262

      2689          1       3002          1          9       1025          1      33293        128

      2689          1       3002          1         10       5121          1      33043        215

      2689          1       3002          1         11       3073         13      33050      94555

      2689          1       3002          1         24       5121         28      33258       4986

      2689          1       3002          1         52          1          1      34703         84

      2689          1       3002          1         56       5121          8      38244       1168

      2689          1       3002          1         72          1          1      34787         84

      2689          1       3002          1        128       5121        843      39412     125460

      2689          1       3002          1 4294967295       5121          1     164872        301




45 rows selected.




SQL>

Here, look the plan for virtual full backup 2768 (that was one of the first and already consolidated) changed. Look that for block 0 the chunk that store changed from 1 to 1025. And this occurred for other blocks (blocks 8-13 as an example), look the plans right now:

The red arrows represent the change that we can see in the PLAN_DETAILS table for VB_KEY 2728. The back arrows represent the blocks needed (from previous backups) if I want to read the virtual full backup 3002.

At the end if I do another backup we can see more evolution for space management:

SQL> select * from plans_details where df_key = 2689 order by vb_key asc, blockno asc;




    DF_KEY       TYPE     VB_KEY    BLKRANK    BLOCKNO    CHUNKNO    NUMBLKS    COFFSET   NUMBYTES

---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

      2689          1       2818          1          0       2049          1       8192      24576

      2689          1       2818          1          2       1025          2      32788        252

      2689          1       2818          1          4          1          4      33038        408

      2689          1       2818          1          8       2049          1      32788        257

      2689          1       2818          1          9       1025          7      33293      36549

      2689          1       2818          1         16       2049          3      33045      17304

      2689          1       2818          1         19       1025          1      76821       1026

      2689          1       2818          1         20       2049          4      50349      29052

      2689          1       2818          1         71          1          3      34703        252

      2689          1       2818          1 4294967295       2049          1      79401        301

      2689          1       2874          1          0       3073          1       8192      24576

      2689          1       2874          1          2       1025          2      32788        252

      2689          1       2874          1          4          1          4      33038        408

      2689          1       2874          1          8       3073          1      32788        262

      2689          1       2874          1          9       1025          2      33293        347

      2689          1       2874          1         11       3073         13      33050      94555

      2689          1       2874          1         71          1          3      34703        252

      2689          1       2874          1 4294967295       3073          1     127605        296

      2689          1       2936          1          0       4097          1       8192      24576

      2689          1       2936          1          2       1025          2      32788        252

      2689          1       2936          1          4          1          4      33038        408

      2689          1       2936          1          8       3073          1      32788        262

      2689          1       2936          1          9       1025          2      33293        347

      2689          1       2936          1         11       3073         13      33050      94555

      2689          1       2936          1         71          1          3      34703        252

      2689          1       2936          1 4294967295       4097          1      32788        278

      2689          1       3002          1          0       5121          1       8192      24576

      2689          1       3002          1          2       5121          2      32788        255

      2689          1       3002          1          4          1          4      33038        408

      2689          1       3002          1          8       3073          1      32788        262

      2689          1       3002          1          9       1025          1      33293        128

      2689          1       3002          1         10       5121          1      33043        215

      2689          1       3002          1         11       3073         13      33050      94555

      2689          1       3002          1         24       5121         28      33258       4986

      2689          1       3002          1         52          1          1      34703         84

      2689          1       3002          1         56       5121          8      38244       1168

      2689          1       3002          1         72          1          1      34787         84

      2689          1       3002          1        128       5121        843      39412     125460

      2689          1       3002          1 4294967295       5121          1     164872        301

      2689          1       3074          1          0       6145          1       8192      24576

      2689          1       3074          1          2       5121          2      32788        255

      2689          1       3074          1          4          1          4      33038        408

      2689          1       3074          1          8       3073          1      32788        262

      2689          1       3074          1          9       1025          1      33293        128

      2689          1       3074          1         10       5121          1      33043        215

      2689          1       3074          1         11       3073         13      33050      94555

      2689          1       3074          1         24       5121         28      33258       4986

      2689          1       3074          1         52          1          1      34703         84

      2689          1       3074          1         56       5121          8      38244       1168

      2689          1       3074          1         72          1          1      34787         84

      2689          1       3074          1        128       5121        843      39412     125460

      2689          1       3074          1 4294967295       6145          1      32788        281




52 rows selected.




SQL>

The image that represents the actual story now it is:

If you check the table and the image you can see that ZDLRA check block a block and now the block #8 for VB_KEY 2728 came from chunk 2049 (done by virtual full backup 2818). If you check with the previous report you can see that backup #8 was split from previous chunk and this information was added for VB_KEY 2728.

At the end, this means that ZDLRA optimized the delta store too, maybe, allow that chunk 1 can be deleted in the future. Probably during the INDEX_BACKUP/PLAN_DF tasks the ZDLRA checked that the block 1 it is the same that exists in others chunks and marked the existing version in chunk number 1 as obsolete.

Virtual Full Backup

This is how the virtual full backup works. Every datafile block that enters inside of ZDLRA from rman backup it is indexed and stored to create the virtual full backup. As you saw above, the idea is creating the representation for every virtual full backup of datafile (VBDF table) linking with one plan (PLANS and PLAN_DETAILS tables).

Going further, you can see that does not exist 1 to 1 relation between backup that was ingested and the virtual full backup. It is just a matrix of pointers for blocks inside chunks. This is the reason that ZDLRA it is different from other backup appliances, it can analyze block a block and index it efficiently.

ZDLRA Internals, INDEX_BACKUP task in details

Category: Engineer System Author: Fernando Simon (Board Member) Date: 5 years ago Comments: 0

ZDLRA Internals, INDEX_BACKUP task in details

For ZDLRA, the task type INDEX_BACKUP it is important (if it is not the most) because it is responsible to create the virtual full backup. This task runs for every backup that you ingest at ZDLRA and here, I will show with more details what occurs at ZDLRA: internals steps, phases, and tables involved.

I recommend that you check my previous post about ZDLRA: ZDLRA Internals, Tables and Storage, ZDLRA, Virtual Full Backup and Incremental Forever, and Understanding ZDLRA. They provide a good base to understand some aspects of ZDLRA architecture and features.

As you saw in my previous post, ZDLRA opens every backup that you sent and read every block of it to generate one new virtual full backup. And this backup is validated block a block (physically and logically) against corruption. It differs from a snapshot because it is content-aware (in this case it is proprietary Oracle datafile blocks inside another proprietary Oracle rman block) and Oracle it is the only that can do this guaranteeing that result is valid.

Backup

This generation is done thought INDEX_BACKUP task and occurs for every backup (level 0 or 1) that enter in the system. For this post, I already have a full backup level 0 inside ZDLRA and I made one new incremental level 1:

RMAN> run{

2> backup device type sbt cumulative incremental level 1 filesperset 1 datafile 29;

3> }

Starting backup at 2019-08-16_15-01-11

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=406 instance=SIMON1 device type=SBT_TAPE

channel ORA_SBT_TAPE_1: RA Library (ZDLRA) SID=903C95C93085DC14E0531E43B20AB30F

allocated channel: ORA_SBT_TAPE_2

channel ORA_SBT_TAPE_2: SID=451 instance=SIMON1 device type=SBT_TAPE

channel ORA_SBT_TAPE_2: RA Library (ZDLRA) SID=903C95D1C201DC19E0531E43B20A4C44

allocated channel: ORA_SBT_TAPE_3

channel ORA_SBT_TAPE_3: SID=502 instance=SIMON1 device type=SBT_TAPE

channel ORA_SBT_TAPE_3: RA Library (ZDLRA) SID=903C95E1BFD9DC38E0531E43B20A0038

allocated channel: ORA_SBT_TAPE_4

channel ORA_SBT_TAPE_4: SID=651 instance=SIMON1 device type=SBT_TAPE

channel ORA_SBT_TAPE_4: RA Library (ZDLRA) SID=903C95EAC1ACDC48E0531E43B20AC79D

channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00029 name=+DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

channel ORA_SBT_TAPE_1: starting piece 1 at 2019-08-16_15-01-25

channel ORA_SBT_TAPE_1: finished piece 1 at 2019-08-16_15-03-10

piece handle=SIMON_s0u9c0a5_1_1 tag=TAG20190816T150116 comment=API Version 2.0,MMS Version 3.17.1.26

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:01:45

Finished backup at 2019-08-16_15-03-10

Starting Control File and SPFILE Autobackup at 2019-08-16_15-03-11

piece handle=c-4165931009-20190816-06 comment=API Version 2.0,MMS Version 3.17.1.26

Finished Control File and SPFILE Autobackup at 2019-08-16_15-03-18

RMAN>

As you can see, it is a normal backup. And the details for this backup:

RMAN> list backupset 52141830;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

52141830 Incr 1  30.75M     SBT_TAPE    00:01:44     2019-08-16_15-03-09

        BP Key: 52141831   Status: AVAILABLE  Compressed: NO  Tag: TAG20190816T150116

        Handle: SIMON_s0u9c0a5_1_1   Media: Recovery Appliance (ZDLRA)

  List of Datafiles in backup set 52141830

  File LV Type Ckp SCN    Ckp Time            Name

  ---- -- ---- ---------- ------------------- ----

  29   1  Incr 4932560891039 2019-08-16_15-01-25 +DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

RMAN>

RMAN> list backup tag = 'TAG20190816T150116';

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

52141830 Incr 1  30.75M     SBT_TAPE    00:01:44     2019-08-16_15-03-09

        BP Key: 52141831   Status: AVAILABLE  Compressed: NO  Tag: TAG20190816T150116

        Handle: SIMON_s0u9c0a5_1_1   Media: Recovery Appliance (ZDLRA)

  List of Datafiles in backup set 52141830

  File LV Type Ckp SCN    Ckp Time            Name

  ---- -- ---- ---------- ------------------- ----

  29   1  Incr 4932560891039 2019-08-16_15-01-25 +DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

RMAN>

Here it is important to remember the TAG, backupset(BS)/backup piece (BP) numbers, and the SCN for the backup made.

INDEX_BACKUP

The INDEX_BACKUP appears as a single task inside ZDLRA, but have two major phases: fixup_unordered and q_restore_fast. And each one impacts differently inside the rman catalog and in the internal tables.

So, after the backup finish, inside of ZDLRA we can see the index task running:

SQL> l

  1  SELECT s.ba_session_id, s.instance_id, s.sid, s.serial#, s.job_name

  2         , rt.task_id, rt.DB_KEY, rt.db_unique_name, rt.task_type, rt.state, rt.waiting_on

  3         , rt.elapsed_seconds

  4         , gs.module, gs.sql_id, gs.action

  5         , rt.BP_KEY, rt.bs_key, rt.df_key, rt.vb_key

  6  FROM sessions s

  7  JOIN ra_task rt

  8  ON rt.task_id = s.current_task

  9  AND rt.task_type = 'INDEX_BACKUP'

 10  JOIN gv$session gs

 11  ON gs.inst_id = s.instance_id

 12  AND gs.sid = s.sid

 13  AND gs.serial# = s.serial#

 14* ORDER BY rt.LAST_EXECUTE_TIME DESC

SQL> /

BA_SESSION_ID INSTANCE_ID    SID    SERIAL# JOB_NAME              TASK_ID     DB_KEY DB_UNIQUE_NAME  TASK_TYPE       STATE    WAITING_ON ELAPSED_SECONDS MODULE           SQL_ID        ACTION            BP_KEY     BS_KEY     DF_KEY     VB_KEY

------------- ----------- ------ ---------- ------------------ ---------- ---------- --------------- --------------- -------- ---------- --------------- ---------------- ------------- ------------- ---------- ---------- ---------- ----------

     56215535           2   4228      30075 RA$_EXEC_56216288    56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING                  345.305895 fixup_unordered  1q0zr86n25pvu scan 6% done    52141831

SQL>

With this SQL you can check all the INDEX_BACKUP tasks running at ZDLRA. And the SQL use the tables and report:

RA_TASK: All tasks running inside ZDLRA.
SESSIONS: Sessions running inside ZDLRA, used to get the database session running the task
GV$SESSION: Databases sessions, used to get the sql_id and others info
BP_KEY: Identify the backup piece key that it is indexing. It is the start point to identify everything here.

Above you can see that task 56242962 is running, processing the BP_KEY (backup piece key) 52141831, running the module fixup_unordered, processed 6% of this step, and running SQL_ID 1q0zr86n25pvu.

INDEX_BACKUP, fixup_unordered

As showed, the index task is running the fixup_unordered module, and thus represents the first phase where ZDLRA it is checking all the blocks that are necessary to build the virtual full backup. Based on the report from the query above we can see that BP_KEY processed it is 52141831 and we can get more info from backup piece rman table:

SQL> select bp_key, bs_key, tag, handle, bytes/1024/1024/1024 from bp where bp_key = 52141831;

    BP_KEY     BS_KEY TAG                              HANDLE               BYTES/1024/1024/1024

---------- ---------- -------------------------------- -------------------- --------------------

  52141831   52141830 TAG20190816T150116               SIMON_s0u9c0a5_1_1             .030029297

SQL>

SQL> select bs_key, INCREMENTAL_LEVEL, HANDLE from rc_backup_piece where bp_key = 52141831;

    BS_KEY INCREMENTAL_LEVEL HANDLE

---------- ----------------- --------------------

  52141830                 1 SIMON_s0u9c0a5_1_1

SQL>

This tells us that the task it is processing the backup that we made before (look the result of column BP_KEY from SQL in the first topic). The BP, and rc_backup_piece are the tables from rman catalog that store info about the backup pieces, check the handle and tag to compare from rman list output.

Going further we can check the VBDF (virtual backup data file table – store the virtual full backups information) table and verify more details:

SQL> SELECT vb_key, ckp_id, df_key, db_key, blocks, vcbp_key, srcbp_key, file#, ckp_scn FROM VBDF WHERE srcbp_key = 52141831;

    VB_KEY         CKP_ID     DF_KEY     DB_KEY     BLOCKS   VCBP_KEY  SRCBP_KEY      FILE#        CKP_SCN

---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- --------------

  52141864  4932560891039     752220     752186 1655177216              52141831         29  4932560891039

SQL>

At VBDF table we can discover:

VB_KEY: Virtual backup key.
CKP_ID: Checkpoint and SCN for this virtual backup.
BLOCKS: Number of the blocks for this virtual backup.
VCBP_KEY: Is null at fixup_unordered because the virtual backup piece (VCBP) was not yet generated (it is doing by fixup_unordered). This will change in the future and I will show more details.
SRCBP_KEY: The source/original backup piece used to create this virtual full backup.

So, we can check that the backu is in the middle of the index the process since the VCBP_KEY is not yet available. But besides that, we can validate/check more details about the backups.

Check that does not exist a backup piece associated with this virtual backup key:

SQL> select bp_key, bs_key, tag, handle, bytes/1024/1024/1024, vb_key from bp where vb_key = 52141864;

no rows selected

SQL>

And that exists just one backup of datafile for this SCN and FILE#:

SQL> SELECT bdf_key, bs_key, file#, ckp_scn, incr_level from bdf where CKP_SCN = 4932560891039 and file# = 29;

   BDF_KEY     BS_KEY      FILE#        CKP_SCN INCR_LEVEL

---------- ---------- ---------- -------------- ----------

  52141832   52141830         29  4932560891039          1

SQL>

SQL> SELECT db_key, bdf_key, file#, CHECKPOINT_CHANGE#, incremental_level from rc_backup_datafile where bs_key IN(52141830);

    DB_KEY    BDF_KEY      FILE# CHECKPOINT_CHANGE# INCREMENTAL_LEVEL

---------- ---------- ---------- ------------------ -----------------

    752186   52141832         29      4932560891039                 1

SQL> SELECT db_key, bdf_key, file#, CHECKPOINT_CHANGE#, incremental_level from rc_backup_datafile where CHECKPOINT_CHANGE# = 4932560891039;

    DB_KEY    BDF_KEY      FILE# CHECKPOINT_CHANGE# INCREMENTAL_LEVEL

---------- ---------- ---------- ------------------ -----------------

    752186   52141832         29      4932560891039                 1

SQL>

Digging more, we can see that are zero plans for this virtual backup (will be zero until fixup_unordered finish – more detail further):

SQL> select count(*) from plans where VB_KEY = 52141864;

  COUNT(*)

----------

         0

SQL>

During this phase of INDEX_BACKUP, the major SQL_ID is 1q0zr86n25pvu:

SQL> select sql_fulltext from gv$sqlarea where inst_id = 2 and sql_id = '1q0zr86n25pvu';

SQL_FULLTEXT

----------------------------------------------------------------------------------------------------

SELECT * FROM (SELECT /*+

                     QB_NAME(c)

                     INDEX_RS_ASC(@c b@c)

                     NO_INDEX_FFS(@c b@c)

                     NO_INDEX_SS(@c b@c)

                     OPT_PARAM('optimizer_dynamic_sampling' 0)

                     OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')

                     OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')

                     OPT_PARAM('_optimizer_use_feedback' 'false')

                    */ BLOCKNO, SCN, CKP_ID, ENDBLK, CHUNKNO, COFFSET FROM BLOCKS B WHERE DF_KEY = :

B4 AND BLOCKNO BETWEEN :B3 AND :B2 AND SCN < :B1 ORDER BY BLOCKNO, SCN, CKP_ID ) WHERE ROWNUM < :B5

SQL>

And the bind variables in this case are:

SQL> l

  1  SELECT s.ba_session_id, s.instance_id

  2  , rt.task_id, rt.DB_KEY, rt.db_unique_name, rt.task_type, rt.state

  3  , gs.module, gs.sql_id, gs.action

  4  , rt.BP_KEY, rt.bs_key, rt.df_key, rt.vb_key

  5  , sbc.name, sbc.value_string

  6  FROM sessions s

  7  JOIN ra_task rt

  8  ON rt.task_id = s.current_task

  9  AND RT.TASK_ID = 56242962

 10  JOIN gv$session gs

 11  ON gs.inst_id = s.instance_id

 12  AND gs.sid = s.sid

 13  AND gs.serial# = s.serial#

 14  join gv$sql_bind_capture sbc

 15  on sbc.inst_id = gs.inst_id

 16  and sbc.sql_id = gs.sql_id

 17* ORDER BY rt.LAST_EXECUTE_TIME DESC

SQL>

SQL> /

BA_SESSION_ID INSTANCE_ID    TASK_ID     DB_KEY DB_UNIQUE_NAME       TASK_TYPE       STATE           MODULE           SQL_ID        ACTION              BP_KEY     BS_KEY     DF_KEY     VB_KEY NAME  VALUE_STRING

------------- ----------- ---------- ---------- -------------------- --------------- --------------- ---------------- ------------- --------------- ---------- ---------- ---------- ---------- ----- ---------------

     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 21% done     52141831                                  :B4   752220

     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 21% done     52141831                                  :B3   302875642

     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 21% done     52141831                                  :B2   1655177216

     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 21% done     52141831                                  :B1   4932554641877

     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 21% done     52141831                                  :B5   1048576

SQL>

Explaining a little. So, based on the SQL text and variables we can see that is retrieving from BLOCKS table all the blocks for the DF_KEY 752220 (you can get it in VBDF), that are between 302875642 (actual block of processing) and 1655177216 (max number of blocks for the ingested backup), and have SCN bellow 4932554641877 (from the ingested backup) and process this in package of 1048576 blocks. One information here it is that the number 302875642 will vary while it is running this phase.

So, the idea here for ZDLRA index task is processing get/check all the blocks that have SCN below of the SCN from ingested backup. And since the INDEX_BACKUP result creates one virtual full backup, all the blocks for this datafile need to be processed.

If you check this query time to time, you will see the increase of scan % increasing until the :B3 and :B4 are equal (compare with the previous execution):

SQL> /

BA_SESSION_ID INSTANCE_ID    TASK_ID     DB_KEY DB_UNIQUE_NAME       TASK_TYPE       STATE           MODULE           SQL_ID        ACTION             BP_KEY     BS_KEY     DF_KEY     VB_KEY NAME  VALUE_STRING

------------- ----------- ---------- ---------- -------------------- --------------- --------------- ---------------- ------------- -------------- ---------- ---------- ---------- ---------- ----- ---------------

     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 99% done    52141831                                  :B4   752220

     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 99% done    52141831                                  :B3   1641637658

     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 99% done    52141831                                  :B2   1655177216

     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 99% done    52141831                                  :B1   4932554641877

     56215535           2   56242962     752186 SIMON_X6S            INDEX_BACKUP    RUNNING         fixup_unordered  1q0zr86n25pvu scan 99% done    52141831                                  :B5   1048576

SQL> select rt.elapsed_seconds from ra_task rt where task_id = 56242962;

ELAPSED_SECONDS

---------------

     6618.53526

SQL>

After this hit 100% the next phase of the INDEX_BAKUP (q_restore_fast) will start. The phase fixup_unordered depends on the size of your datafile and not from the ingested backup. In this example, the backup has just 30MB, but the datafile has more than 16TB, so, the virtual full will have the same size and this takes a lot of time reading all the blocks. For you ZDLRA the DF_KEY, BS_KEY, and others will be different. Just remember that BP_KEY for the index task it is the start point for the analyses.

INDEX_BACKUP, q_restore_fast

After the fixup_unordered reach 100% of the scan, the phase q_restore_fast starts and this will create the virtual backup itself. But before let’s see what’s happened at rman catalog after the previous phase finished:

##########################################################

This is list took right after the backup finished:

RMAN> list backup tag = 'TAG20190816T150116';

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

52141830 Incr 1  30.75M     SBT_TAPE    00:01:44     2019-08-16_15-03-09

        BP Key: 52141831   Status: AVAILABLE  Compressed: NO  Tag: TAG20190816T150116

        Handle: SIMON_s0u9c0a5_1_1   Media: Recovery Appliance (ZDLRA)

  List of Datafiles in backup set 52141830

  File LV Type Ckp SCN    Ckp Time            Name

  ---- -- ---- ---------- ------------------- ----

  29   1  Incr 4932560891039 2019-08-16_15-01-25 +DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

RMAN>

##########################################################

And now after the fixup_unordered finish:

RMAN> list backup tag = 'TAG20190816T150116';

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

52146235 Incr 1  28.00M     SBT_TAPE    00:01:44     2019-08-16_15-03-09

        BP Key: 52146236   Status: AVAILABLE  Compressed: YES  Tag: TAG20190816T150116

        Handle: VB$_90959062_52141864I   Media:

  List of Datafiles in backup set 52146235

  File LV Type Ckp SCN    Ckp Time            Name

  ---- -- ---- ---------- ------------------- ----

  29   1  Incr 4932560891039 2019-08-16_15-01-25 +DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

RMAN>

As you can see, the backupset (52141830 before, and 52146235 now) and backup piece changed (the handle changed too). But look that TAG and SCN remains the same. This means that a new backup was created because ZDLRA scanned the original backup. But as you can see, the virtual full was not created yet (there is any level 0 available in the list). If you see this in your ZDLRA, that means that INDEX_BACKUP task is in process.

If we continue to check more details and use the same query than before, we can see that backup changed the BS_KEY and BP_KEY in the internal tables (look that old keys disappears):

SQL> select bs_key, INCREMENTAL_LEVEL, HANDLE from rc_backup_piece where bp_key = 52141831;

no rows selected

SQL> select bp_key, bs_key, tag, handle, bytes/1024/1024/1024, vb_key from bp where bp_key = 52141831;

no rows selected

SQL> select bp_key, bs_key, tag, handle, bytes/1024/1024/1024 from bp where tag = 'TAG20190816T150116';

    BP_KEY     BS_KEY TAG                 HANDLE                  BYTES/1024/1024/1024

---------- ---------- ------------------- ----------------------- --------------------

  52146236   52146235 TAG20190816T150116  VB$_90959062_52141864I             .02734375

SQL>

And now, checking in the VBDF table we can see that info at VCBP_KEY column appears

SQL> SELECT vb_key, ckp_id, df_key, db_key, blocks, vcbp_key, srcbp_key, file#, ckp_scn FROM VBDF WHERE srcbp_key = 52141831;

    VB_KEY         CKP_ID     DF_KEY     DB_KEY     BLOCKS   VCBP_KEY  SRCBP_KEY      FILE#        CKP_SCN

---------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- --------------

  52141864  4932560891039     752220     752186 1655177216   52146236   52141831         29  4932560891039

SQL>

And if you search now at backup piece table you can see that exists one listed for this virtual backup key (check in the topic before that this info does not exist):

SQL> select bp_key, bs_key, tag, handle, bytes/1024/1024/1024, vb_key from bp where vb_key = 52141864;

    BP_KEY     BS_KEY TAG                 HANDLE                  BYTES/1024/1024/1024     VB_KEY

---------- ---------- ------------------- ----------------------- -------------------- ----------

  52146236   52146235 TAG20190816T150116  VB$_90959062_52141864I             .02734375   52141864

SQL>

And check that exists just one backup of this datafile at this SCN:

SQL> SELECT bdf_key, bs_key, file#, ckp_scn, incr_level from bdf where CKP_SCN = 4932560891039 and file# = 29;

   BDF_KEY     BS_KEY      FILE#        CKP_SCN INCR_LEVEL

---------- ---------- ---------- -------------- ----------

  52146237   52146235         29  4932560891039          1

SQL>

In this phase, you can see that the details for index and virtual full backup starts to appear internally. If you check, now you have plans for this virtual full backup:

SQL> select count(*) from plans where VB_KEY = 52141864;

  COUNT(*)

----------

         1

SQL>

SQL> select count(*), to_char(sysdate, 'HH24:MI') as time from plans_details where VB_KEY = 52141864;

  COUNT(*) TIME

---------- -----

   2294010 18:25

SQL> select count(*), to_char(sysdate, 'HH24:MI') as time from plans_details where VB_KEY = 52141864;

  COUNT(*) TIME

---------- -----

   5773720 18:43

SQL>

As you can see above, the plans_details for this virtual backup is increasing from time to time. This means that the q_restore_fast is running and it is reading blocks to create the virtual full backup/index (and inserting at plan_details).

We can figure out this, while the INDEX_BACKUP task is running, checking the sql_id for the session. While the task is running, we have three major sql’s (I used the same query than before – where we checked the bind for the session/task):

SQL> /

BA_SESSION_ID INSTANCE_ID    TASK_ID     DB_KEY DB_UNIQUE_NAME  TASK_TYPE       STATE    MODULE          SQL_ID        ACTION                   BP_KEY     BS_KEY     DF_KEY     VB_KEY NAME  VALUE_STRING

------------- ----------- ---------- ---------- --------------- --------------- -------- --------------- ------------- -------------------- ---------- ---------- ---------- ---------- ----- ---------------

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B1

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B12

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B2

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B1

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B2

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B3

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B4

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B1   752220

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B3   1

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B4   1051071

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B11  0

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B10  4931849117847

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B9   4931848304742

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B8   4932554641877

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B7   752187

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B6   4932554641877

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  9ma189ab8x9c7 plan 0% done           52141831                                  :B5   4932554641877

17 rows selected.                                                                                       

SQL>                                                                                                    

SQL> /                                                                                                 

BA_SESSION_ID INSTANCE_ID    TASK_ID     DB_KEY DB_UNIQUE_NAME  TASK_TYPE       STATE    MODULE          SQL_ID        ACTION                   BP_KEY     BS_KEY     DF_KEY     VB_KEY NAME  VALUE_STRING

------------- ----------- ---------- ---------- --------------- --------------- -------- --------------- ------------- -------------------- ---------- ---------- ---------- ---------- ----- ---------------

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B1   752220

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B3   403380104

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B4   404440007

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B11  0

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B10  4931849117847

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B9   4931848304742

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B8   4932554641877

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B7   752187

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B6   4932554641877

     56215535           2   56242962     752186 SIMON_X6S       INDEX_BACKUP    RUNNING  q_restore_fast  brt6uuhzacdnu plan 54% done          52141831                                  :B5   4932554641877

10 rows selected.

SQL>

SQL> /

BA_SESSION_ID INSTANCE_ID    TASK_ID     DB_KEY DB_UNIQUE_NAME       TASK_TYPE       STATE      MODULE                         SQL_ID        ACTION                   BP_KEY     BS_KEY     DF_KEY     VB_KEY NAME  VALUE_STRING

------------- ----------- ---------- ---------- -------------------- --------------- ---------- ------------------------------ ------------- -------------------- ---------- ---------- ---------- ---------- ----- --------------------

     56215535           2   56242962     477602 SIMON_X6S            INDEX_BACKUP    RUNNING    q_restore_fast                 8t81c0j1w9jqu plan 89% done          41032043                                  :B1

     56215535           2   56242962     477602 SIMON_X6S            INDEX_BACKUP    RUNNING    q_restore_fast                 8t81c0j1w9jqu plan 89% done          41032043                                  :B3   752220

     56215535           2   56242962     477602 SIMON_X6S            INDEX_BACKUP    RUNNING    q_restore_fast                 8t81c0j1w9jqu plan 89% done          41032043                                  :B2   4389973931270

     56215535           2   56242962     477602 SIMON_X6S            INDEX_BACKUP    RUNNING    q_restore_fast                 8t81c0j1w9jqu plan 89% done          41032043                                  :B1   4932554641877

     56215535           2   56242962     477602 SIMON_X6S            INDEX_BACKUP    RUNNING    q_restore_fast                 8t81c0j1w9jqu plan 89% done          41032043                                  :B4   1048576

SQL>

And the SQL text for them:

SQL> select sql_fulltext from gv$sqlarea where inst_id = 2 and sql_id = '9ma189ab8x9c7';

SQL_FULLTEXT

--------------------------------------------------------------------------------------------------------------------------------

INSERT /*+

             QB_NAME(q_restore_fast)

             OPT_PARAM('optimizer_dynamic_sampling' 0)

             OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')

             OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')

             OPT_PARAM('_optimizer_use_feedback' 'false')

           */ INTO PLANS_DETAILS (DF_KEY, TYPE, VB_KEY, BLKRANK, BLOCKNO, CHUNKNO, NUMBLKS, COFFSET, NUMBYTES) SELECT :B1 , :B12

, :B2 , 1 BLKRANK, BLOCKNO, CHUNKNO, NUMBLKS, COFFSET, NUMBYTES FROM TABLE( DBMS_RA_POOL.COLLAPSE(:B1 , :B2 , :B3 , :B4 ,

 CURSOR( SELECT /*+

                     QB_NAME(q_rfast_c)

                     INDEX_RS_ASC(@q_rfast_c b@q_rfast_c)

                     NO_INDEX_FFS(@q_rfast_c b@q_rfast_c)

                     NO_INDEX_SS(@q_rfast_c b@q_rfast_c)

                     OPT_PARAM('optimizer_dynamic_sampling' 0)

                     OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')

                     OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')

                     OPT_PARAM('_optimizer_use_feedback' 'false')

                   */ CKP_ID, BLOCKNO, SCN, CHUNKNO, USED, COFFSET, ENDBLK FROM BLOCKS B WHERE DF_KEY = :B1 AND BLOCKNO BETWEEN

:B3 AND :B4 AND SCN BETWEEN :B11 AND :B10 AND CKP_ID >= :B9 AND (CKP_ID <= :B8 OR DBINC_KEY <> :B7 ) AND (SCN < :B6 OR CKP_ID =

:B5 ) ORDER BY BLOCKNO, SCN, CKP_ID, CHUNKNO ) ) )

SQL>

SQL> select sql_fulltext from gv$sqlarea where inst_id = 2 and sql_id = 'brt6uuhzacdnu';

SQL_FULLTEXT

-------------------------------------------------------------------------------------------------------------------------------

SELECT /*+ QB_NAME ("Q_RFAST_C") NO_INDEX_SS ("B") NO_INDEX_FFS ("B") INDEX_RS_ASC ("B") */ "B"."CKP_ID" "CKP_ID","B"."BLOCKNO"

"BLOCKNO","B"."SCN" "SCN","B"."CHUNKNO" "CHUNKNO","B"."USED" "USED","B"."COFFSET" "COFFSET","B"."ENDBLK" "ENDBLK" FROM "BLOCKS"

"B" WHERE "B"."DF_KEY"=:B1 AND "B"."BLOCKNO">=:B3 AND "B"."BLOCKNO"<=:B4 AND "B"."SCN">=:B11 AND "B"."SCN"<=:B10 AND "B"."CKP_ID

">=:B9 AND ("B"."CKP_ID"<=:B8 OR "B"."DBINC_KEY"<>:B7) AND ("B"."SCN"<:B6 OR "B"."CKP_ID"=:B5) ORDER BY "B"."BLOCKNO","B"."SCN",

"B"."CKP_ID","B"."CHUNKNO"

SQL>

SQL> select sql_fulltext from gv$sqlarea where inst_id = 2 and sql_id = '8t81c0j1w9jqu';

SQL_FULLTEXT

--------------------------------------------------------------------------------------------------------------------------------

SELECT NVL(MAX(BLOCKNO), :B1 ), COUNT(*) FROM (SELECT /*+

                 QB_NAME(nbr)

                 INDEX_RS_ASC(@nbr b@nbr)

                 NO_INDEX_FFS(@nbr b@nbr)

                 NO_INDEX_SS(@nbr b@nbr)

                 OPT_PARAM('optimizer_dynamic_sampling' 0)

                 OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')

                 OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')

                 OPT_PARAM('_optimizer_use_feedback' 'false')

               */ BLOCKNO FROM BLOCKS B WHERE DF_KEY = :B3 AND BLOCKNO BETWEEN :B2 AND :B1 ORDER BY BLOCKNO) WHERE ROWNUM <= :B4

SQL>

As you can see, the sql_id 9ma189ab8x9c7 is responsible to insert into PLANS_DETAILS the blocks that are needed to create this virtual full backup. This is based in SCN, and means that ZDLRA create the index for all blocks that are below the SCN from the backup that was ingested originally. The sql_id brt6uuhzacdnu returns all the blocks that are needed (based on the SCN), and it is the same for sql_id 8t81c0j1w9jqu that verify if we have a block to index. These sql’s are the base for the q_restore_fast and they alternate itself until we reach 100%.

The idea for ZDLRA in this phase is to create the index that will be the base for the virtual full backup. And “create the index” means all the blocks that are needed to create the plan for this virtual full backup key.

INDEX_BACKUP, 100%

So, after the INDEX_BACKUP finish at ZDLRA, we have this at rman catalog:

RMAN> list backup of datafile 29 tag = TAG20190816T150116;

List of Backup Sets

===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

52146235 Incr 1  28.00M     SBT_TAPE    00:01:44     2019-08-16_15-03-09

        BP Key: 52146236   Status: AVAILABLE  Compressed: YES  Tag: TAG20190816T150116

        Handle: VB$_90959062_52141864I   Media:

  List of Datafiles in backup set 52146235

  File LV Type Ckp SCN    Ckp Time            Name

  ---- -- ---- ---------- ------------------- ----

  29   1  Incr 4932560891039 2019-08-16_15-01-25 +DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

52152458 Incr 0  12.67T     SBT_TAPE    00:01:44     2019-08-16_15-03-09

        BP Key: 52152459   Status: AVAILABLE  Compressed: YES  Tag: TAG20190816T150116

        Handle: VB$_90959062_52141864_29   Media:

  List of Datafiles in backup set 52152458

  File LV Type Ckp SCN    Ckp Time            Name

  ---- -- ---- ---------- ------------------- ----

  29   0  Incr 4932560891039 2019-08-16_15-01-25 +DATAC1/simon_x6s/datafile/simontsthistoric1_tbs.399.938192975

RMAN>

Look that for the same TAG we have now two backups, and one it is the virtual full backup. Check that SCN and TAG for both are the same. And if we check internally, at rman catalog view, about the backup pieces linked to the virtual backup key that was created by the index task we can see:

SQL> select bp_key, bs_key, tag, handle, bytes/1024/1024/1024, vb_key from bp where vb_key = 52141864;

    BP_KEY     BS_KEY TAG                 HANDLE                    BYTES/1024/1024/1024     VB_KEY

---------- ---------- ------------------- ------------------------- -------------------- ----------

  52152459   52152458 TAG20190816T150116  VB$_90959062_52141864_29            12975.3698   52141864

  52146236   52146235 TAG20190816T150116  VB$_90959062_52141864I               .02734375   52141864

SQL>

Look above that now we have two backups linked to the same virtual full backup (compare with the same SQL from the previous phases). The backup set key 52152458 is the virtual full backup and was generated during the phase q_restore_fast. The backup set key 52146235 was generated during the phase fixup_unordered.

And there is now, two backups for this datafile:

SQL> SELECT bdf_key, bs_key, file#, ckp_scn, incr_level from bdf where CKP_SCN = 4932560891039 and file# = 29;

   BDF_KEY     BS_KEY      FILE#         CKP_SCN INCR_LEVEL

---------- ---------- ---------- --------------- ----------

  52146237   52146235         29   4932560891039          1

  52152460   52152458         29   4932560891039          0

SQL>

Just to show that the task took a lot of time to finish:

SQL> select rt.elapsed_seconds from ra_task rt where task_id = 56242962;

ELAPSED_SECONDS

---------------

     14563.7986

SQL>

SQL> select count(*) from plans_details where VB_KEY = 52141864;

  COUNT(*)

----------

  74505579

SQL>

INDEX_BACKUP, The internals

As you can see above, the INDEX_BACKUP task it is responsible to generate the virtual full backup and “fix” the rman catalog views to represent the new backups. This is done in two major phases (fixup_unordered and q_restore_fast) to read the backup that was ingested at ZDLRA and index all the blocks. The other phases are process_allfiles and plan_blocks, but they are fast to execute.

About the blocks, it is important to hint that it is completely based in SCN/CKP number. The index creation will search for all blocks that are bellow of the SCN of ingested backup. If you check the details, the backup file does not exist, by the way, it is just a huge list of the blocks need to this SCN (information stored at plans_details table).

The procedures, SQL, steps, phases that I showed before will have different values in your case. The DF_KEY, BS_KEY, and others will be different. But I think that you can understand the logic of how ZDLRA index the backup and internally generate the virtual full backup. I know that it is not easy to read all the numbers and link between sql’s and tables, but the logic it is simple: verify all the blocks that belong to datafile and create one plan that points to every block needed by the virtual full backup for one scn.

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

ZDLRA, Virtual Full Backup and Incremental Forever

Category: Engineer System Author: Fernando Simon (Board Member) Date: 5 years ago Comments: 0

ZDLRA, Virtual Full Backup and Incremental Forever

One of the most knows features of ZDLRA is the virtual full backup, basically incremental forever strategy. But what this means in real life? In this post, I will show some details about that and how interesting they are, check what it is Virtual Full Backup and Incremental Forever strategy for ZDLRA.

This post is based on my previous one where I showed all the steps to configure the VPC and enroll database at ZDLRA.

Virtual Full Backup

A virtual full backup appears as an incremental level 0 backup in the recovery catalog. From the user’s perspective, a virtual full backup is indistinguishable from a non-virtual full backup. Using virtual backups, Recovery Appliance provides the protection of frequent level 0 backups with only the cost of frequent level 1 backups.

This definition (and image) are in the Zero Data Loss Recovery Appliance Administrator’s Guide and I think that represents the essence of the virtual full backup. ZDLRA receive the incremental level 1 backup, index it, and generate a level 0 to you that it is indistinguishable from a normal backup level 0.

As you can see, virtual full are deeply linked with incremental forever strategy (that it is just executing incremental backups). And based in these incremental backups you receive a full backup available for usage.

To start, I open RMAN and connect in the rman catalog (ZDLRA database, using the VPC configured) and execute a level 0 backup:

RMAN> RUN {

2> BACKUP INCREMENTAL LEVEL 0 DEVICE TYPE SBT FILESPERSET 1 DATABASE TAG 'BKP-DB-INC0';

3> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';

4> BACKUP DEVICE TYPE SBT FILESPERSET 100 FORMAT '%U' ARCHIVELOG ALL NOT BACKED UP TAG 'BKP-ARCH';

5> }




Starting backup at 15-08-2019_00:44:36

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=100 device type=SBT_TAPE

channel ORA_SBT_TAPE_1: RA Library (ZDLRA) SID=901C8083732A1691E053010310AC46B7

channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL18/system01.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 15-08-2019_00:44:38

channel ORA_SBT_TAPE_1: finished piece 1 at 15-08-2019_00:47:23

piece handle=ORCL18_0tu97pnm_1_1 tag=BKP-DB-INC0 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:02:45

channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL18/sysaux01.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 15-08-2019_00:47:24

channel ORA_SBT_TAPE_1: finished piece 1 at 15-08-2019_00:48:59

piece handle=ORCL18_0uu97pss_1_1 tag=BKP-DB-INC0 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:01:35

channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL18/undotbs01.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 15-08-2019_00:48:59

channel ORA_SBT_TAPE_1: finished piece 1 at 15-08-2019_00:49:02

piece handle=ORCL18_0vu97pvr_1_1 tag=BKP-DB-INC0 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03

channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL18/users01.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 15-08-2019_00:49:02

channel ORA_SBT_TAPE_1: finished piece 1 at 15-08-2019_00:49:05

piece handle=ORCL18_10u97pvu_1_1 tag=BKP-DB-INC0 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03

Finished backup at 15-08-2019_00:49:05




Starting Control File and SPFILE Autobackup at 15-08-2019_00:49:05

piece handle=c-3914023082-20190815-02 comment=API Version 2.0,MMS Version 12.2.0.2

Finished Control File and SPFILE Autobackup at 15-08-2019_00:49:22




sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT




Starting backup at 15-08-2019_00:49:28

current log archived

using channel ORA_SBT_TAPE_1

skipping archived logs of thread 1 from sequence 4 to 9; already backed up

channel ORA_SBT_TAPE_1: starting archived log backup set

channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=10 RECID=7 STAMP=1016326166

input archived log thread=1 sequence=11 RECID=8 STAMP=1016326170

channel ORA_SBT_TAPE_1: starting piece 1 at 15-08-2019_00:49:32

channel ORA_SBT_TAPE_1: finished piece 1 at 15-08-2019_00:49:35

piece handle=12u97q0s_1_1 tag=BKP-ARCH comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03

Finished backup at 15-08-2019_00:49:35




Starting Control File and SPFILE Autobackup at 15-08-2019_00:49:35

piece handle=c-3914023082-20190815-03 comment=API Version 2.0,MMS Version 12.2.0.2

Finished Control File and SPFILE Autobackup at 15-08-2019_00:49:51




RMAN>

RMAN> list backup of datafile 1;







List of Backup Sets

===================







BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

2222    Incr 0  748.50M    SBT_TAPE    00:02:32     15-08-2019_00:47:10

        BP Key: 2223   Status: AVAILABLE  Compressed: NO  Tag: BKP-DB-INC0

        Handle: ORCL18_0tu97pnm_1_1   Media: Recovery Appliance (ZDLRA)

  List of Datafiles in backup set 2222

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  1    0  Incr 1614233    15-08-2019_00:44:39              NO    /u01/app/oracle/oradata/ORCL18/system01.dbf




RMAN>

As you can see a simple incremental level 0 backup, archive log generation, and backup. ZDLRA after receive this backup start to index it to generate the base level 0. If you go to ZDLRA database you can see:

SQL> select TASK_ID, TASK_TYPE, STATE, WAITING_ON, DB_KEY, DB_UNIQUE_NAME, CREATION_TIME, ERROR_COUNT, DF_KEY, BS_KEY, BP_KEY, VB_KEY from rasys.ra_task where archived = 'N';




   TASK_ID TASK_TYPE            STATE           WAITING_ON     DB_KEY DB_UNIQUE_NAME                 CREATION_TIME                       ERROR_COUNT     DF_KEY     BS_KEY     BP_KEY     VB_KEY

---------- -------------------- --------------- ---------- ---------- ------------------------------ ----------------------------------- ----------- ---------- ---------- ---------- ----------

      9626 INDEX_BACKUP         RUNNING                          1527 ORCL18                         15-AUG-19 12.47.12.479004 AM +02:00           0                             2223

      9627 INDEX_BACKUP         EXECUTABLE                       1527 ORCL18                         15-AUG-19 12.48.56.229219 AM +02:00           0                             2228

      9628 INDEX_BACKUP         EXECUTABLE                       1527 ORCL18                         15-AUG-19 12.49.02.200951 AM +02:00           0                             2232

      9629 INDEX_BACKUP         EXECUTABLE                       1527 ORCL18                         15-AUG-19 12.49.04.895727 AM +02:00           0                             2236




4 rows selected.




SQL>

Above look the task INDEX_BACKUP running BS_KEY 2223, and check that it is the same backupset for datafile 1 that I showed before (I executed the list backup for datafile 1 just after the backup finish). And while the INDEX_BACKUP task still running you can see check again the list backupset for datafile 1 at rman:

RMAN>  list backup of datafile 1;







List of Backup Sets

===================







BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

2222    Incr 0  748.50M    SBT_TAPE    00:02:32     15-08-2019_00:47:10

        BP Key: 2223   Status: AVAILABLE  Compressed: NO  Tag: BKP-DB-INC0

        Handle: ORCL18_0tu97pnm_1_1   Media: Recovery Appliance (ZDLRA)

  List of Datafiles in backup set 2222

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  1    0  Incr 1614233    15-08-2019_00:44:39              NO    /u01/app/oracle/oradata/ORCL18/system01.dbf




BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

2336    Incr 0  329.21M    SBT_TAPE    00:02:34     15-08-2019_00:47:12

        BP Key: 2337   Status: AVAILABLE  Compressed: YES  Tag: BKP-DB-INC0

        Handle: VB$_4142545763_2226I   Media:

  List of Datafiles in backup set 2336

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  1    0  Incr 1614233    15-08-2019_00:44:39              NO    /u01/app/oracle/oradata/ORCL18/system01.dbf




RMAN>

Look how interesting it is. Now you have a new backupset (BS key 2336) that it is a clone for the original. Have same SCN, same TAG, same completion time, and size. The only difference is the handle that has the ZDLRA pattern VB$_*. And after some tasks inside ZDLRA finish (like RM_INC_FILES and RUN_PURGE_DF) you have just one backup of datafile 1:

RMAN> list backup of datafile 1;







List of Backup Sets

===================







BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

2336    Incr 0  329.21M    SBT_TAPE    00:02:34     15-08-2019_00:47:12

        BP Key: 2337   Status: AVAILABLE  Compressed: YES  Tag: BKP-DB-INC0

        Handle: VB$_4142545763_2226I   Media:

  List of Datafiles in backup set 2336

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  1    0  Incr 1614233    15-08-2019_00:44:39              NO    /u01/app/oracle/oradata/ORCL18/system01.dbf




RMAN>

As I told in my previous post, ZDLRA has the ability to open your backupset and index every block for your datafile, and more important: validated every block for each datafile.

Incremental Forever

Above was just for the initial level 0 backup, and it is not “incremental forever”. But if you do an incremental level 1 backup (I cropped the output to reduce the post size but it was just: BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT FILESPERSET 1 DATABASE TAG ‘BKP-DB-INC1’;. Check the list backup after that:

RMAN> list backup of datafile 1;







List of Backup Sets

===================







BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

2336    Incr 0  329.21M    SBT_TAPE    00:02:34     15-08-2019_00:47:12

        BP Key: 2337   Status: AVAILABLE  Compressed: YES  Tag: BKP-DB-INC0

        Handle: VB$_4142545763_2226I   Media:

  List of Datafiles in backup set 2336

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  1    0  Incr 1614233    15-08-2019_00:44:39              NO    /u01/app/oracle/oradata/ORCL18/system01.dbf




BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

2589    Incr 1  48.00K     SBT_TAPE    00:00:02     15-08-2019_00:58:00

        BP Key: 2590   Status: AVAILABLE  Compressed: YES  Tag: BKP-DB-INC1

        Handle: VB$_4142545763_2588I   Media:

  List of Datafiles in backup set 2589

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  1    1  Incr 1614888    15-08-2019_00:57:59              NO    /u01/app/oracle/oradata/ORCL18/system01.dbf




BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

2644    Incr 0  328.08M    SBT_TAPE    00:00:02     15-08-2019_00:58:00

        BP Key: 2645   Status: AVAILABLE  Compressed: YES  Tag: BKP-DB-INC1

        Handle: VB$_4142545763_2588_1   Media:

  List of Datafiles in backup set 2644

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  1    0  Incr 1614888    15-08-2019_00:57:59              NO    /u01/app/oracle/oradata/ORCL18/system01.dbf




RMAN>

Look the details above. Inside of rman catalog at ZDLRA was made available to you a new level 0 backup that it is the virtual full backup. This level 0 have the same SCN than the incremental level 1.

So, basically, ZDLRA opened the backup incremental level 1 that you sent, read all the blocks, index it and based in the previous backup level 0 that was available (virtual or not – this is important) merged the block and present to you a new level 0. And if you do this every day, after an incremental level 1 you receive a complete virtual full backup.

In real life, it is simple like that. Again, check how interesting it is, you have now one backup level 0 available after the incremental that have a new backupset (BS key 2644) that it is a merge from the incremental level 1 (backupset 2589) with the previous level 0 (backupset 2636). And have the same SCN, same TAG, same completion time that the incremental level 1.

Some details that you need to take care of this. If you use rman encryption virtual full backups will not work. For TDE, you can use it, not a problem. But you can’t use TDE with rman compression enabled. Check here. The recommendation too is using FILESPERSET 1 while doing the backup. This is good because reduce some overhead inside of ZDLRA (to open every backup and split it), and reduce pollution inside rman catalog list backup (if you sent a backup without fileperset, ZDLRA does not remove it from the list).

If I do a backup without filersperset the ZDLRA will open the bakupset and generate a virtual full for each datafile that it is inside of the original backupset. Check below that SCN is the same than incremental level 1:

RMAN> list backup of database completed after "sysdate - 5/1440";







List of Backup Sets

===================







BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

2905    Incr 1  1.26M      SBT_TAPE    00:00:08     15-08-2019_01:04:28

        BP Key: 2906   Status: AVAILABLE  Compressed: YES  Tag: BKP-DB-INC1

        Handle: VB$_4142545763_2827I   Media:

  List of Datafiles in backup set 2905

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  1    1  Incr 1615589    15-08-2019_01:04:20              NO    /u01/app/oracle/oradata/ORCL18/system01.dbf

  3    1  Incr 1615589    15-08-2019_01:04:20              NO    /u01/app/oracle/oradata/ORCL18/sysaux01.dbf

  4    1  Incr 1615589    15-08-2019_01:04:20              NO    /u01/app/oracle/oradata/ORCL18/undotbs01.dbf

  7    1  Incr 1615589    15-08-2019_01:04:20              NO    /u01/app/oracle/oradata/ORCL18/users01.dbf




BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

2992    Incr 0  328.09M    SBT_TAPE    00:00:08     15-08-2019_01:04:28

        BP Key: 2993   Status: AVAILABLE  Compressed: YES  Tag: BKP-DB-INC1

        Handle: VB$_4142545763_2827_1   Media:

  List of Datafiles in backup set 2992

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  1    0  Incr 1615589    15-08-2019_01:04:20              NO    /u01/app/oracle/oradata/ORCL18/system01.dbf




BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

2996    Incr 0  40.00K     SBT_TAPE    00:00:08     15-08-2019_01:04:28

        BP Key: 2997   Status: AVAILABLE  Compressed: YES  Tag: BKP-DB-INC1

        Handle: VB$_4142545763_2827_7   Media:

  List of Datafiles in backup set 2996

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  7    0  Incr 1615589    15-08-2019_01:04:20              NO    /u01/app/oracle/oradata/ORCL18/users01.dbf




BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

3000    Incr 0  480.00K    SBT_TAPE    00:00:08     15-08-2019_01:04:28

        BP Key: 3001   Status: AVAILABLE  Compressed: YES  Tag: BKP-DB-INC1

        Handle: VB$_4142545763_2827_4   Media:

  List of Datafiles in backup set 3000

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  4    0  Incr 1615589    15-08-2019_01:04:20              NO    /u01/app/oracle/oradata/ORCL18/undotbs01.dbf




BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

3004    Incr 0  135.47M    SBT_TAPE    00:00:08     15-08-2019_01:04:28

        BP Key: 3005   Status: AVAILABLE  Compressed: YES  Tag: BKP-DB-INC1

        Handle: VB$_4142545763_2827_3   Media:

  List of Datafiles in backup set 3004

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  3    0  Incr 1615589    15-08-2019_01:04:20              NO    /u01/app/oracle/oradata/ORCL18/sysaux01.dbf




RMAN>

The virtual backup continue to work if you use the SECTION SIZE (it is recommended by the way, especially for huge databases). Look the example:

[oracle@orcloel7 ~]$ sqlplus / as sysdba




SQL*Plus: Release 18.0.0.0.0 - Production on Thu Aug 15 01:11:58 2019

Version 18.6.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.6.0.0.0




SQL> create tablespace simon datafile '/u01/app/oracle/oradata/ORCL18/simon.dbf' size 1G;




Tablespace created.




SQL> exit

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.6.0.0.0

[oracle@orcloel7 ~]$ rman target=/ catalog=cat_zdlra/s3nhazdlra@zdlra




Recovery Manager: Release 18.0.0.0.0 - Production on Thu Aug 15 01:15:24 2019

Version 18.6.0.0.0




Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.




connected to target database: ORCL18 (DBID=3914023082)

connected to recovery catalog database

PL/SQL package CAT_ZDLRA.DBMS_RCVCAT version 18.03.00.00. in RCVCAT database is not current

PL/SQL package CAT_ZDLRA.DBMS_RCVMAN version 18.03.00.00 in RCVCAT database is not current




RMAN> report schema;




starting full resync of recovery catalog

full resync complete

Report of database schema for database with db_unique_name ORCL18




List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    870      SYSTEM               YES     /u01/app/oracle/oradata/ORCL18/system01.dbf

3    510      SYSAUX               NO      /u01/app/oracle/oradata/ORCL18/sysaux01.dbf

4    320      UNDOTBS1             YES     /u01/app/oracle/oradata/ORCL18/undotbs01.dbf

5    1024     SIMON                NO      /u01/app/oracle/oradata/ORCL18/simon.dbf

7    5        USERS                NO      /u01/app/oracle/oradata/ORCL18/users01.dbf




List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    49       TEMP                 32767       /u01/app/oracle/oradata/ORCL18/temp01.dbf




RMAN> BACKUP INCREMENTAL LEVEL 0 SECTION SIZE 250M DEVICE TYPE SBT DATAFILE 5 TAG 'BKP-DBF5-INC0';




Starting backup at 15-08-2019_01:16:35

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: SID=68 device type=SBT_TAPE

channel ORA_SBT_TAPE_1: RA Library (ZDLRA) SID=901CF26E504F1E35E053010310AC27EC

channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL18/simon.dbf

backing up blocks 1 through 32000

channel ORA_SBT_TAPE_1: starting piece 1 at 15-08-2019_01:16:37

channel ORA_SBT_TAPE_1: finished piece 1 at 15-08-2019_01:16:38

piece handle=ORCL18_1fu97rjl_1_1 tag=BKP-DBF5-INC0 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL18/simon.dbf

backing up blocks 32001 through 64000

channel ORA_SBT_TAPE_1: starting piece 2 at 15-08-2019_01:16:38

channel ORA_SBT_TAPE_1: finished piece 2 at 15-08-2019_01:16:39

piece handle=ORCL18_1fu97rjl_2_1 tag=BKP-DBF5-INC0 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL18/simon.dbf

backing up blocks 64001 through 96000

channel ORA_SBT_TAPE_1: starting piece 3 at 15-08-2019_01:16:39

channel ORA_SBT_TAPE_1: finished piece 3 at 15-08-2019_01:16:42

piece handle=ORCL18_1fu97rjl_3_1 tag=BKP-DBF5-INC0 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:03

channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL18/simon.dbf

backing up blocks 96001 through 128000

channel ORA_SBT_TAPE_1: starting piece 4 at 15-08-2019_01:16:42

channel ORA_SBT_TAPE_1: finished piece 4 at 15-08-2019_01:16:43

piece handle=ORCL18_1fu97rjl_4_1 tag=BKP-DBF5-INC0 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL18/simon.dbf

backing up blocks 128001 through 131072

channel ORA_SBT_TAPE_1: starting piece 5 at 15-08-2019_01:16:43

channel ORA_SBT_TAPE_1: finished piece 5 at 15-08-2019_01:16:44

piece handle=ORCL18_1fu97rjl_5_1 tag=BKP-DBF5-INC0 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

Finished backup at 15-08-2019_01:16:44




Starting Control File and SPFILE Autobackup at 15-08-2019_01:16:44

piece handle=c-3914023082-20190815-08 comment=API Version 2.0,MMS Version 12.2.0.2

Finished Control File and SPFILE Autobackup at 15-08-2019_01:16:52




RMAN> list backup of datafile 5;







List of Backup Sets

===================







BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

3244    Incr 0  208.00K    SBT_TAPE    00:00:08     15-08-2019_01:16:45

  List of Datafiles in backup set 3244

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  5    0  Incr 1616756    15-08-2019_01:16:37              NO    /u01/app/oracle/oradata/ORCL18/simon.dbf




  Backup Set Copy #1 of backup set 3244

  Device Type Elapsed Time Completion Time     Compressed Tag

  ----------- ------------ ------------------- ---------- ---

  SBT_TAPE    00:00:08     15-08-2019_01:16:45 YES        BKP-DBF5-INC0




    List of Backup Pieces for backup set 3244 Copy #1

    BP Key  Pc# Status      Media                   Piece Name

    ------- --- ----------- ----------------------- ----------

    3245    1   AVAILABLE                           VB$_4142545763_3227I

    3250    2   AVAILABLE                           VB$_4142545763_3248I

    3255    3   AVAILABLE                           VB$_4142545763_3253I

    3260    4   AVAILABLE                           VB$_4142545763_3258I

    3272    5   AVAILABLE                           VB$_4142545763_3270I




RMAN> BACKUP INCREMENTAL LEVEL 1 SECTION SIZE 250M DEVICE TYPE SBT DATAFILE 5 TAG 'BKP-DBF5-INC1';




Starting backup at 15-08-2019_01:20:01

using channel ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL18/simon.dbf

backing up blocks 1 through 32000

channel ORA_SBT_TAPE_1: starting piece 1 at 15-08-2019_01:20:02

channel ORA_SBT_TAPE_1: finished piece 1 at 15-08-2019_01:20:03

piece handle=ORCL18_1lu97rq2_1_1 tag=BKP-DBF5-INC1 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL18/simon.dbf

backing up blocks 32001 through 64000

channel ORA_SBT_TAPE_1: starting piece 2 at 15-08-2019_01:20:03

channel ORA_SBT_TAPE_1: finished piece 2 at 15-08-2019_01:20:10

piece handle=ORCL18_1lu97rq2_2_1 tag=BKP-DBF5-INC1 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:07

channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL18/simon.dbf

backing up blocks 64001 through 96000

channel ORA_SBT_TAPE_1: starting piece 3 at 15-08-2019_01:20:10

channel ORA_SBT_TAPE_1: finished piece 3 at 15-08-2019_01:20:11

piece handle=ORCL18_1lu97rq2_3_1 tag=BKP-DBF5-INC1 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL18/simon.dbf

backing up blocks 96001 through 128000

channel ORA_SBT_TAPE_1: starting piece 4 at 15-08-2019_01:20:11

channel ORA_SBT_TAPE_1: finished piece 4 at 15-08-2019_01:20:12

piece handle=ORCL18_1lu97rq2_4_1 tag=BKP-DBF5-INC1 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL18/simon.dbf

backing up blocks 128001 through 131072

channel ORA_SBT_TAPE_1: starting piece 5 at 15-08-2019_01:20:12

channel ORA_SBT_TAPE_1: finished piece 5 at 15-08-2019_01:20:13

piece handle=ORCL18_1lu97rq2_5_1 tag=BKP-DBF5-INC1 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

Finished backup at 15-08-2019_01:20:13




Starting Control File and SPFILE Autobackup at 15-08-2019_01:20:13

piece handle=c-3914023082-20190815-09 comment=API Version 2.0,MMS Version 12.2.0.2

Finished Control File and SPFILE Autobackup at 15-08-2019_01:20:21




RMAN> list backup of datafile 5;







List of Backup Sets

===================







BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

3244    Incr 0  208.00K    SBT_TAPE    00:00:08     15-08-2019_01:16:45

  List of Datafiles in backup set 3244

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  5    0  Incr 1616756    15-08-2019_01:16:37              NO    /u01/app/oracle/oradata/ORCL18/simon.dbf




  Backup Set Copy #1 of backup set 3244

  Device Type Elapsed Time Completion Time     Compressed Tag

  ----------- ------------ ------------------- ---------- ---

  SBT_TAPE    00:00:08     15-08-2019_01:16:45 YES        BKP-DBF5-INC0




    List of Backup Pieces for backup set 3244 Copy #1

    BP Key  Pc# Status      Media                   Piece Name

    ------- --- ----------- ----------------------- ----------

    3245    1   AVAILABLE                           VB$_4142545763_3227I

    3250    2   AVAILABLE                           VB$_4142545763_3248I

    3255    3   AVAILABLE                           VB$_4142545763_3253I

    3260    4   AVAILABLE                           VB$_4142545763_3258I

    3272    5   AVAILABLE                           VB$_4142545763_3270I




BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

3355    Incr 1  200.00K    SBT_TAPE    00:00:12     15-08-2019_01:20:14

  List of Datafiles in backup set 3355

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  5    1  Incr 1616986    15-08-2019_01:20:02              NO    /u01/app/oracle/oradata/ORCL18/simon.dbf




  Backup Set Copy #1 of backup set 3355

  Device Type Elapsed Time Completion Time     Compressed Tag

  ----------- ------------ ------------------- ---------- ---

  SBT_TAPE    00:00:12     15-08-2019_01:20:14 YES        BKP-DBF5-INC1




    List of Backup Pieces for backup set 3355 Copy #1

    BP Key  Pc# Status      Media                   Piece Name

    ------- --- ----------- ----------------------- ----------

    3356    1   AVAILABLE                           VB$_4142545763_3354I

    3406    2   AVAILABLE                           VB$_4142545763_3404I

    3415    3   AVAILABLE                           VB$_4142545763_3413I

    3424    4   AVAILABLE                           VB$_4142545763_3422I

    3433    5   AVAILABLE                           VB$_4142545763_3431I




BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

3359    Incr 0  132.50K    SBT_TAPE    00:00:12     15-08-2019_01:20:14

  List of Datafiles in backup set 3359

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  5    0  Incr 1616986    15-08-2019_01:20:02              NO    /u01/app/oracle/oradata/ORCL18/simon.dbf




  Backup Set Copy #1 of backup set 3359

  Device Type Elapsed Time Completion Time     Compressed Tag

  ----------- ------------ ------------------- ---------- ---

  SBT_TAPE    00:00:12     15-08-2019_01:20:14 YES        BKP-DBF5-INC1




    List of Backup Pieces for backup set 3359 Copy #1

    BP Key  Pc# Status      Media                   Piece Name

    ------- --- ----------- ----------------------- ----------

    3360    1   AVAILABLE                           VB$_4142545763_3354_5

    3410    2   AVAILABLE                           VB$_4142545763_3404_5

    3419    3   AVAILABLE                           VB$_4142545763_3413_5

    3428    4   AVAILABLE                           VB$_4142545763_3422_5

    3437    5   AVAILABLE                           VB$_4142545763_3431_5




RMAN>

As you can see, I created a tablespace and made backup level 0 (and 1) with section size. And after the index was created a virtual full backup, based on the incremental, for each size. This speed up the restore phase because you can parallelize it (instead if you use a single backupset). ZDLRA will “fix” if you made an incremental level 1 without section size:

RMAN> BACKUP INCREMENTAL LEVEL 1 DEVICE TYPE SBT DATAFILE 5 TAG 'BKP-DBF5-INC1';




Starting backup at 15-08-2019_01:23:17

using channel ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set

channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set

input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL18/simon.dbf

channel ORA_SBT_TAPE_1: starting piece 1 at 15-08-2019_01:23:17

channel ORA_SBT_TAPE_1: finished piece 1 at 15-08-2019_01:23:18

piece handle=ORCL18_1ru97s05_1_1 tag=BKP-DBF5-INC1 comment=API Version 2.0,MMS Version 12.2.0.2

channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:01

Finished backup at 15-08-2019_01:23:18




Starting Control File and SPFILE Autobackup at 15-08-2019_01:23:19

piece handle=c-3914023082-20190815-0a comment=API Version 2.0,MMS Version 12.2.0.2

Finished Control File and SPFILE Autobackup at 15-08-2019_01:23:26




RMAN> list backup of datafile 5 completed after "sysdate - 3/1440";







List of Backup Sets

===================







BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

3572    Incr 1  40.00K     SBT_TAPE    00:00:02     15-08-2019_01:23:19

        BP Key: 3573   Status: AVAILABLE  Compressed: YES  Tag: BKP-DBF5-INC1

        Handle: VB$_4142545763_3571I   Media:

  List of Datafiles in backup set 3572

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  5    1  Incr 1617208    15-08-2019_01:23:18              NO    /u01/app/oracle/oradata/ORCL18/simon.dbf




BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ -------------------

3581    Incr 0  132.50K    SBT_TAPE    00:00:02     15-08-2019_01:23:19

  List of Datafiles in backup set 3581

  File LV Type Ckp SCN    Ckp Time            Abs Fuz SCN Sparse Name

  ---- -- ---- ---------- ------------------- ----------- ------ ----

  5    0  Incr 1617208    15-08-2019_01:23:18              NO    /u01/app/oracle/oradata/ORCL18/simon.dbf




  Backup Set Copy #1 of backup set 3581

  Device Type Elapsed Time Completion Time     Compressed Tag

  ----------- ------------ ------------------- ---------- ---

  SBT_TAPE    00:00:02     15-08-2019_01:23:19 YES        BKP-DBF5-INC1




    List of Backup Pieces for backup set 3581 Copy #1

    BP Key  Pc# Status      Media                   Piece Name

    ------- --- ----------- ----------------------- ----------

    3582    1   AVAILABLE                           VB$_4142545763_3576_5

    3586    2   AVAILABLE                           VB$_4142545763_3577_5

    3590    3   AVAILABLE                           VB$_4142545763_3578_5

    3594    4   AVAILABLE                           VB$_4142545763_3579_5

    3598    5   AVAILABLE                           VB$_4142545763_3580_5




RMAN>

Look that ZDLRA opened the incremental level that was sent (without section size) index it and after that generated a level 0 (that originally was done with section size) and created a virtual full backup correctly

You can see here (Output-Full-Execution-Backup-Level-0-and-Backup-Level-1) a full execution for backup level 0, subsequent levels 1, to verify the generation of virtual full backups and the incremental forever strategy. Check the SCN, creation times for backups 0 that appear in the list after I create a backup level 1.

Virtual Full backup, Incremental forever, and Backup Strategy

Virtual full backups change completely the backup strategy for your database. The incremental forever strategy starts it the key point so save you for a lot of problems.

If you start to check your backup strategy, there are a lot of details to verify. So, what it is your backup strategy? Full every weekend plus incremental every day? What it is the load for your backup appliance? And how many times do you pass doing the backup? If you have a huge database, maybe a full every weekend it is impossible, and you need to sparse in the month (1 time per month is feasible).

But the most important, the time to recover and validate the backup? You need to restore the full (form last week as an example), and all the incremental + plus archives since that. This can be really time-consuming.

But if you can do everything with a single incremental level 1 every day? You receive a full backup, that was validated by ZDLRA, and per datafile data you have. If ZDLRA detects corrupted blocks it advises you about that. The incremental forever strategy help in other places too. The overhead over the network decrease a lot (just pass incremental). The time consumption for CPU inside of database too.

Now think the restore of the database. If you need to do one restore in the middle of the week you need to use the nearest level 0 that cover your request (probably done during the weekend) and apply some days incremental and archivelogs. For ZDLRA, you have one level 0 one day before available and just need to recover just a few archives.

The base idea here is that ZDLRA it is the only appliance that delivers to you a backup full (virtual full backup, as they call) after every incremental backup that you do. This approach is called incremental forever. And even besides that, ZDLRA it is the only one that open correctly what rman sent to you (filesperset or no, section size, compressed or no) and verifies every block of datafile inside, index it, and deliver to you a validated backupset. And as you saw, in the examples before, everything related with catalog it is done automatically by ZDLRA (backup deletion and others).

In the next post, I will show you more details about what’s happens inside of ZDLRA when it receives the incremental backup. The internal tasks and where it’s store the information about the virtual backups.

« 1 2 3 4 5 6 7 »

Exadata, Using metrics to help you

In this post, I will show you one example of how to use the metric to identify and solve database problems. Sometimes it can be a hide and seek game, but I will try to show you how to use metrics and how they can help you on your daily basis.

DB_FC_IO_BY_SEC

Returning to the example, since the report hinted some bad usage I made research from running SQL’s and hit two notes:

Slow Performance Of DBA_AUDIT_SESSION Query From “Failed logins” Metric in Enterprise Manager (Doc ID 1178736.1).

EM 12c, 11g: High Cpu Utilization from Enterprise Manger Agent perl Process Running failedLogin.pl for audit_failed_logins metric (Doc ID 1265699.1).

Basically, the EM/CC collection for failed login attempts was running and consuming a lot of resources. And since this EM/CC audit was not needed, it was can be disabled and after that, the DB_FC_IO_BY_SEC changed to:

As you can see, more normal behavior for database usage.

The Point

I really recommend read Chapter 6 of the Exadata User Guide. But it is important too to have a good base about what Exadata HW can deliver and how the features work. The Exadata X8 Data Sheet, Technical White Paper and MAA OOW presentations provide you a good source of information.

Remember, it is more than learning about hardware or use some hints for SQL. It is more about understanding how hardware and software are integrated and what you can extract from there. And with Exadata metrics, you can really discover what is happening.

ZDLRA, Multi-site protection - ZERO RPO for Primary and Standby

Multi-site protection

MAA team already wrote about how to do that at Deploying the Zero Data Loss Recovery Appliance in a Data Guard Configuration, but frankly, it is hard to understand this doc. And here in this post, I will try to provide a better example and how to reach RPO zero for both sites.

Multiple ZDLRA’s

The way that we will use ZDLRA, in this case, is different from the replication feature that exists for ZDLRA. Here, we have two ZDLRA, one for each site. The environment in this case is:

ORCL19: RAC primary database.

ORCL19S: RAC standby database.

ZDLRAS1: ZDLRA that protect the primary site.

ZDLRAS2: ZDLRA that protects the standby site.

And the target will be:

ZDLRA Configuration – Protecting and Enrolling Databases

VPC

If you already have a VPC user created for your ZDLRA and want to use an existing one, you can skip this step. Just remember that the same user needs to exist in both ZDLRA’s. This is more critical if you are intending to use real-time redo and reach zero RPO.

So, if needed, create the same VPC user in both ZDLRA’s:

At ZDLRAS1:

At ZDLRAS2:

Backup Policy

It is not needed to have the same policy in each ZDLRA and it is possible to enroll the database using different policies since they (ZDLRA’s) work separately. Here I used the same policy in both sites (but with different recovery window goals).

At ZDLRAS1:

At ZDLRAS2:

Grant DB at ZDLRA

Here we have the first key point, and it is related to the way that database is registered database inside ZDLRA. It follows the traditional way, using the ADD_DB, GRANT_DB_ACCESS, and using DB_UNIQUE_NAME as identification.

And another common error, if you try to register the standby database (instead of primary database) is RMAN-01005: Mounted control file type must be CURRENT to register the database. This occurs because some registrations and checks done by rman and database controlfile need to be at current one.

At ZDLRAS1:

At ZDLRAS2:

Look above that ADD_DB and GRANT_DB_ACCESS at ZDLRAS2 used the DB_UNIQUE_NAME as ORCL19 (that it is the name for primary).

Registry database

At Primary – ORLC19

At this point, it is possible to register and backup the primary database with ZDLRAS1. The steps are the same that I described at my post on how to enroll a database at ZDLRA. The steps are, in order:

Install ZDLRA library

Create Wallet

Configure/Test tns entry to ZDLRA

Adjust sqlnet.ora file (this is needed because the real-time redo)

At Standby – ORLC19S

The same procedure than above it is done here:

Install ZDLRA library

Create Wallet

Configure/Test tns entry to ZDLRA

Adjust sqlnet.ora file (this is needed because the real-time redo)

After that, we can register ORACL19 at ZDLRAS1. It is critical to register the primary database first at the ZDLRA that will protect the primary site.

If you want, you can do the backup of the database (it is optional), I not showed here but it is a simple backup database level 0 filesperset 1 and you can see the output here – Output-Backup-Primary-ORCL19-at-ZDLRAS1.

After registry primary database ORCL19 at ZDLRA (ZDLRAS1) that protect the primary site, we can register the primary ORCL19 at the ZDLRA that protect the standby site (ZDLRAS2)

Look that I need to add the entry of TNS names for ZDLRAS2 (at the primary server) too:

Look at some points above:

show all read the already registered information from rman catalog (look channel set)

After the resync catalog, the list db_unique_name reported both databases, primary (ORCL19) and standby (ORCL19S)

After that, you can configure the correct channel for ORCL19S to point to ZDLRAS2, and do the backup. This output can be checked here – Output-Backup-Primary-ORCL19S-at-ZDLRAS2.

Environment protected

At this point, you have both sites protected, which one with dedicated local backup with ZDLRA. Primary ORCL19 database does the backup at ZDLRAS1, and standby database ORCL19S does the backup at ZDLRAS2:

But this not protect you in case of failure of one site. In this case, the RPO will not be zero because we don’t have where to send the archivelogs.

ZERO RPO

To reach zero RPO we just need to enable real-time redo for both sites. We can do manually or using the broker. If you want to see in detail how to configure you can check my two posts: ZDLRA, Real-Time Redo and ZDLRA, Real-Time Redo and Zero RPO, here I will post resumed steps.

Manually Configuration

The manually way differs because here we set manually the log_archive_dest parameters. The others need to be done besides the way that we choose. And the manual config is the only way for 11G databases.

Basic config

For real-time redo we need to configure some parameters, but basically, I made these configs in order:

log_archive_config: Check that for primary it is ZDLRAS1, and ZDLRAS2 for standby

log_archive_dest_3: Again, each site points to the own ZDLRA. And look the type defined at VALID_FOR

Create the user for redo_transport_user inside database.

redo_transport_user: Same for both databases.

At Primary ORCL19:

At Standby ORCL19S:

As you can see here, the user is not created at standby because the creation will come by redo. And as you can see in the end, I stopped the standby database.

Password file

Since one user was created in just one site, we need to copy the password file from production to the standby.

At Primary ORCL19:

As you saw, I saved the password file at /tmp folder.

At Standby ORCL19S:

As you saw above, the file from primary was copied and overwritten the old.