Recover Oracle Standby / Dataguard Database From Service

 

Though there are many documents  online for recovering standby using service , i thought of documenting mine  to have handy steps for myself and  help others too 

Before  we take decision  to recover from service . try  below troubleshooting steps  first 

-- take new archive from primary .  This is ensure  current log on standby is not corrupted 

-- restart standby  database 
-- take new standby controlfile from primary 
--  try to start  mrp  with no real time apply 
--  try to start  mrp   in mount 

--  try to start  mrp without parallel 

--  try to start  mrp on another rac node 

 

If there are any nologging changes we can  use  below doc to fix  nologging changes . 

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes (Doc ID 958181.1)

Recovery  Steps : 

1. Get the latest SCN from standby:

select to_char(current_scn) from v$database;

 

CURRENT_SCN#

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

644203931

2. Stop Managed Recovery and Redo Transport  via Data Guard Broker 

DGMGRL> edit database <primary> set state=TRANSPORT-OFF;

Succeeded.

DGMGRL> edit database <standby> set state=APPLY-OFF;

Succeeded.

3. Take  backup of location  of files 

select name from v$datafile  

union all 

select member from v$tempfile 

union all 

select memeber from   v$logfile ;

4.  Restore Standby Controlfile 

$ srvctl stop database -d <standbydb> -o immediate

$ rman target / nocatalog 

RMAN> startup nomount

RMAN> restore standby controlfile from service <tns alias for primary database>;

RMAN> alter database mount;

RMAN> catalog start with '<DATA DISKGROUP>/<standby db_unique_name/';

RMAN> catalog start with '<RECO DISKGROUP>/<standby db_unique_name>/';

5. Restore Any Missing Files  that are added  recently after gap 

RMAN> select file# from v$datafile where creation_change# >= 644203931;   -- from step 1 .  ( this is  run on primary ) 

Below are  on standby 

RMAN> run {

allocate channel c1 type disk;

allocate channel c2 type disk;

allocate channel c3 type disk;

allocate channel c4 type disk;

allocate channel c5 type disk;

allocate channel c6 type disk;

allocate channel c7 type disk;

allocate channel c8 type disk;

set newname for database to NEW;

restore datafile <comma separate list of files> from service <tns alias for primary database> section size <section size>;

}

RMAN> switch database to copy;

6. Clear Online Redo Logs and Standby Redo Logs  .

Execute the following query in SQL*PLUS on the standby to create new logfiles.

SQL> begin

for log_cur in ( select group# group_no from v$log )

loop

execute immediate 'alter database clear logfile group '||log_cur.group_no;

end loop;

end;

/

SQL> begin

for log_cur in ( select group# group_no from v$standby_log )

loop

execute immediate 'alter database clear logfile group '||log_cur.group_no;

end loop;

end;

7.  Restart All Instances to Mount and Re-Enable Redo Transport 

$ srvctl stop database -db <dbname> -o immediate

$ srvctl start database -db <dbname> -o mount

DGMGRL> edit database <primary> set state=TRANSPORT-ON;

8 Run Recover From Service 

Dont use noredo    or  "recover standby database"  because  in some cases the RECOVER DATABASE NOREDO command does not apply the incremental changes to the standby database and it just completes in few seconds.

The NOREDO clause specifies that the archived redo log files must not be applied during recovery. Its mainly use  when Olr are lost on standby . 

The RECOVER STANDBY DATABASE command restarts the standby instance, refreshes the control file from the primary database, and automatically renames data files, temp files, and online logs. It restores new data files that were added to the primary database and recovers the standby database up to the current time.

When you use the RECOVER STANDBY DATABASE command to refresh a standby database, you specify either a FROM SERVICE clause or a NOREDO clause. The FROM SERVICE clause specifies the name of a primary service. The NOREDO clause specifies that backups should be used for the refresh, which allows a standby to be rolled forward to a specific time or SCN

.

If you wat to  prefer to use  "RECOVER STANDBY DATABASE"    method its documented in   2431311.1 .  However  we might face  Implicit Crosschecking and Cataloging issue  using  "RECOVER STANDBY DATABASE"    and  we have to set     db_recovery_file_dest=''  as documented in  Doc  1489027.1

$ rman target sys/<password>  <- It is necessary to connect with the password

RMAN > run {

allocate channel c1 type disk connect '/@<standby instance 1 SID_NAME>';

allocate channel c2 type disk connect '/@<standby instance 1 SID_NAME>';

allocate channel c3 type disk connect '/@<standby instance 1 SID_NAME>';

allocate channel c4 type disk connect '/@<standby instance 1 SID_NAME>';

allocate channel c5 type disk connect '/@<standby instance 2 SID_NAME>';

allocate channel c6 type disk connect '/@<standby instance 2 SID_NAME>';

allocate channel c7 type disk connect '/@<standby instance 2 SID_NAME>';

allocate channel c8 type disk connect '/@<standby instance 2 SID_NAME>';

recover database from service '<primary unique name>' section size <section size> using compressed backupset;  

}

select INST_ID,SID,SERIAL#,OPNAME, (sofar/totalwork)*100 as "%complete", ELAPSED_SECONDS, TIME_REMAINING

from gv$session_longops

where sofar<>totalwork

and totalwork<>0

and SID||SERIAL# in (select SID||SERIAL# from v$session);

From the primary:

SQL> alter system archive log current;

Then from SQL*PLUS on the standby issue the command below.  The UNTIL CONSISTENT clause cannot be used in RMAN.

SQL> recover automatic standby database until consistent;

select inst_id,GROUP#,TYPE,MEMBER from gv$logfile    -->  Verify    log mapped to right path 

Verify standby_file_management  on standby 

9.  Re-Enable Flashback Database

SQL> alter database flashback on;

10.  Restart the Standby and Managed Recovery

$ srvctl stop database -db <dbname> -o immediate

$ srvctl start database -db <dbname> -o 'read only'

DGMGRL> edit database <standby> set state=APPLY-ON;

 

Reference : 

How to Roll Forward a Standby Database Using Recover Database From Service (Doc ID 2850185.1)

Roll Forward a Physical Standby Database Using RMAN Incremental Backups 'Recover Database Noredo' Does Nothing (Doc ID 841765.1)

Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes (Doc ID 958181.1)

Roll Forward Physical Standby Using RMAN Incremental Backup in Single Command (Doc ID 2431311.1)