Recover standby database from service Dataguard on 19c

The Oracle Dataguard, part from MAA (Max Availability Architecture), is a prouct vastly utilized all over the entire globe by several companies whom willing to keep their data safe, preventing lost of data in case of human erros, hardware failures and other kind of disasters. The main function of this product is provide a copy from your primary database, synchronized by archivelogs, usually maintained into a different geographical location, in most of cases. With the Active Dataguard introduction, on Oracle 11g, an option, licensed, leverage this product to another level, even better, in my humble opinion. This feature allows the users to run some reports on standby database with the state read only with apply, this can deviate a lot of heavy workloads from principal database and reducing a lot of resources on it.

With Oracle database 19c, Oracle introduced a feature that allows some DML done on standby database be redirected to the primary database and the shipped by archivelogs again to standby. Note, this is not recomended for usage with intense workloads, because of performance matters, use more, cpu, network and etc. Just mention this features for you follow up some improvements on the product on each version. Today we will talk about one more enhancement that make it easier DBA’s life time to time.

Sometimes, because of some hardware failures, human failures, bugs or even some reconfiguration, we need to recreate the standby database, and on Oracle 18c and onwards, Oracle has introduced a single RMAN command to performance the whole resynchronization from standby databases over network. I don’t need to tell you that is no suitable perform multi-terabyte database restoration over network, most of time (you can have a dedicated network to play with).

Benefits

Simplicity to create or recreate the standby database

Best control of the process

 

Scenario

 

Primary database: orclcdb

Standby database: orclstb

 

The primary database and also the standby are managed by Dataguard broker (DGMGRL), I like to use this command line utility to manage the Dataguard environments. It helps to perform all dataguard administration like switchover, configure and a lot more of tasks in a Dataguard environment, if you want to have more information about this incredible tool, please refer the official documentation on the link https://docs.oracle.com/en/database/oracle/oracle-database/19/dgbkr/index.html

Verification

 

As I mentioned before, using the command line interface from Dataguard Broker, we can use the command show configuration to display the configuration made by us, previously, in our case, on this Dataguard configuration we have the primary database orclcdb and also the standby database orclstb, we can have multiple standby databases and also ZDLRA registered on broker. We will cause some damage on standby database just to show how to solve it on this article, by removing a datafile, let’s say system01.dbf, just for fun.

              We can see on the image below, there are no problems there, like a gap or any other kind of issues, in this case, if we lose the primary database, the standby will be able to become the primary database with just a manual switchover. It is also possible to automate this task, by configuring Fast-Start Failover.

Causing some damage on standby

 

On the following image, we can verify all belonging datafiles from our standby database, where we will cause some damage and recover it just using recover standby database from service. The list below, shows all datafiles from our standby (orclstb)

Now, we will remove the main datafile on the standby, /u01/app/oracle/oradata/ORCLSTB/system01.dbf

As we expected, after remove, one of the datafiles on purpose, our environment crashed, look:

Disabling the apply on standby database

 

Before to initiate with the recover process, from this standby database, we will neneed  stop the archivelog synchronization, to do that, we can use the commands below, to edit the state of the database within broker. If you don’t do that, we will face this error:

 

starting media recovery

media recovery failed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 01/19/2020 11:16:26

RMAN-03015: error occurred in stored script Memory Script

RMAN-11003: failure during parse/execution of SQL statement: alter database recover

 if needed standby start

ORA-01153: an incompatible media recovery is active

As we can see, the command to stop apply on DGMGRL was issued, and now, no more error will happen.

After stopping the recover, we just need to login on RMAN and issue the command recover standby database from service, as we can follow up on up coming lines of this article:

[oracle@ora19c ~]$ rman target=sys/oracle@orclstb



Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 19 11:27:59 2020

Version 19.3.0.0.0


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

connected to target database: ORCLCDB (DBID=2780785463, not open)


RMAN> recover standby database from service orclcdb;



Starting recover at 19-JAN-20
using target database control file instead of recovery catalog
Oracle instance started


Total System Global Area    1895823376 bytes

Fixed Size                     9136144 bytes
Variable Size                436207616 bytes
Database Buffers            1442840576 bytes
Redo Buffers                   7639040 bytes



contents of Memory Script:

{
   restore standby controlfile from service  'orclcdb';
   alter database mount standby database;
}

executing Memory Script


Starting restore at 19-JAN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orclcdb
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/ORCLSTB/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORCLSTB/control02.ctl
Finished restore at 19-JAN-20



released channel: ORA_DISK_1
Statement processed



contents of Memory Script:
{
set newname for datafile  1 to
 "/u01/app/oracle/oradata/ORCLSTB/system01.dbf";
   restore from service  'orclcdb' datafile
    1;
   catalog datafilecopy  "/u01/app/oracle/oradata/ORCLSTB/system01.dbf";
   switch datafile all;
}
executing Memory Script



executing command: SET NEWNAME

Starting restore at 19-JAN-20

Starting implicit crosscheck backup at 19-JAN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 19-JAN-20



Starting implicit crosscheck copy at 19-JAN-20

using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 19-JAN-20


searching for all files in the recovery area
cataloging files...
cataloging done


List of Cataloged Files

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

File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_25_gyq4g4hz_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_26_gyq5km3v_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_27_gyq64bom_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_28_gyq64n7f_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_29_gyq64zy8_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_30_gyq6vxg5_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_32_gyq6vzrv_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_31_gyq6vzs6_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_33_gyq84b59_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_34_gyq88dll_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_35_gyq9rvx9_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_36_gyqcf807_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_37_gyqcfsk1_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_38_gyqcg4l3_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_39_gyqckh4o_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_41_gyqckkfk_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_40_gyqckkg6_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_42_gyqcpsko_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_07/o1_mf_1_43_gyqdchj7_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_08/o1_mf_1_44_gyso1jwz_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_08/o1_mf_1_45_gytopnjo_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2019_12_08/o1_mf_1_46_gytowb7h_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2020_01_09/o1_mf_1_47_h1gstntb_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2020_01_09/o1_mf_1_48_h1gtcgd0_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2020_01_09/o1_mf_1_49_h1gv3wqn_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2020_01_09/o1_mf_1_50_h1gv8rbs_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2020_01_09/o1_mf_1_51_h1gvo2x8_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2020_01_16/o1_mf_1_52_h21lqfd2_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2020_01_16/o1_mf_1_53_h21m7fwt_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/archivelog/2020_01_19/o1_mf_1_54_h28w8y78_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/autobackup/2019_12_07/o1_mf_s_1026372241_gyq6h263_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/autobackup/2019_12_07/o1_mf_s_1026373077_gyq7r09k_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORCLSTB/autobackup/2019_12_07/o1_mf_s_1026378166_gyqd9fxy_.bkp



using channel ORA_DISK_1




channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orclcdb
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCLSTB/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 19-JAN-20




cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/ORCLSTB/system01.dbf RECID=5 STAMP=1030102136


datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=1030102136 file name=/u01/app/oracle/oradata/ORCLSTB/system01.dbf


contents of Memory Script:

{
  recover database from service  'orclcdb';
}

executing Memory Script



Starting recover at 19-JAN-20

using channel ORA_DISK_1

skipping datafile 1; already restored to SCN 4595092
skipping datafile 3; already restored to SCN 4594570
skipping datafile 5; already restored to SCN 2163739
skipping datafile 6; already restored to SCN 2163739
skipping datafile 7; already restored to SCN 4594577
skipping datafile 8; already restored to SCN 2163739
skipping datafile 9; already restored to SCN 4594580
skipping datafile 10; already restored to SCN 4594582
skipping datafile 12; already restored to SCN 4594588
skipping datafile 13; already restored to SCN 4594593
skipping datafile 14; already restored to SCN 4594596
skipping datafile 15; already restored to SCN 4594598
skipping datafile 19; already restored to SCN 4594600
skipping datafile 20; already restored to SCN 4594604
skipping datafile 21; already restored to SCN 4594611



starting media recovery




media recovery complete, elapsed time: 00:00:00
Finished recover at 19-JAN-20

Finished recover at 19-JAN-20




RMAN>

 

Thus, we are able to see,  with just one simple command line on RMAN, how can be easy recover/rebuild my entire dataguard database and stay ready for the next disaster recover, we hope not face that, but problems happens. The final result, our standby database was restored and can be fully syncrhonized again with the production database.

 

So, via DGMGRL interface, we can enable the synchronization again as we can see on the image:

For validation purposes, we will issue a switchover to change the roles of our databases, making the primary database becoming the standby and standby becoming the primary:

Now, our database orclstb, originally standby database is the primary, and the database orclcdb was changed to physical standby role:

All are opened and ready to use, the instainswith db_unique_name = orclstb now is with PRIMARY ROLE.

Conclusion: 

Oracle is always innovating and make our lives easier. With every launched version, new features are added. We can agree on this, this functionality saves a lot of work, but, be concerned that you restore is fully using network, if your database is huge, you may kill the performance of your entire network.