Rolling Forward a Physical Standby using "Recover From Service" command [12.1 feature]
Tags: oracle, database, recovery, tutorial
Published: 2020-07-12 15:07:17

This is what we can see from the Oracle Doc ID 1987763.1 regarding Recover standby from Service option added in 12.1:

[Rolling Forward a Physical Standby Using Recover From Service Command in 12c (Doc ID 1987763.1)](https://support.oracle.com/knowledge/Oracle%20Database%20Products/1987763_1.html)

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

Prior to 12c, in order to roll forward the standby database using incremental backups you would need to:

* Determine the necessary SCN of the standby

* Take an incremental backup on the primary starting from that SCN# of the standby database.

* Copy the incremental backup to the standby host

* Catalog the backups (copied from the primary) into the standby controlfile.

* Cancel managed recovery of the standby database and apply the incremental backup on the standby database. 

* Create a control file for the standby database on the primary database.

* Mount the standby database with newly created standby control file.

* Start managed recovery of standby database.

In 12c, this procedure has been dramatically simplified. In 12c, you can use the RECOVER â€¦ FROM SERVICE command to synchronize the physical standby database with the primary database.  This command does the following:

* Creates an incremental backup containing the changes to the primary database.   All changes to data files on the primary database, beginning with the SCN in the standby data file header, are included in the incremental backup.
* Transfers the incremental backup over the network to the physical standby database.
* Applies the incremental backup to the physical standby database.

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

Lets try it out :)


First, we stop transport from out Primary (db121)
```
DGMGRL> show configuration

Configuration - dg_broker_config

  Protection Mode: MaxPerformance
  Members:
  db121 - Primary database
    st121 - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 14 seconds ago)

DGMGRL>


DGMGRL> edit database db121 SET STATE=TRANSPORT-OFF; 
Succeeded.

```

Now, we execute couple of log switches to make sure we have higher squences in primary

```
13:22:26 db1211 > alter system archive log current;

System altered.

13:22:32 db1211 > alter system archive log current;

System altered.

13:22:42 db1211 > alter system archive log current;

System altered.

```

We can compare the latest Squence from Primary and standby
```
## Primary ##
13:22:52 db1211 > select  thread#,max(sequence#) from v$archived_log where first_time > sysdate -1 and status ='A' group by thread# order by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
	 1	       57
	 2	       42

13:24:02 db1211 > 


## Standby ##
13:15:58 st1211 > select  thread#,max(sequence#) from v$archived_log where first_time > sysdate -1 and status ='A' group by thread# order by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
	 1	       55
	 2	       40

13:23:56 st1211 > 
```

Let's delete de archivelog. Since they are not applied in the standby, we will need to use FORCE option

```
RMAN> run
{
delete force noprompt archivelog until sequence=57 thread 1;
delete force noprompt archivelog until sequence=42 thread 2;
}2> 3> 4> 5> 

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=426 instance=db1211 device type=DISK
List of Archived Log Copies for database with db_unique_name DB121
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
156     1    55      A 07-JUL-20
        Name: +RECO/DB121/ARCHIVELOG/2020_07_07/thread_1_seq_55.323.1045142549

160     1    56      A 07-JUL-20
        Name: +RECO/DB121/ARCHIVELOG/2020_07_07/thread_1_seq_56.329.1045142559

162     1    57      A 07-JUL-20
        Name: +RECO/DB121/ARCHIVELOG/2020_07_07/thread_1_seq_57.339.1045142565

deleted archived log
archived log file name=+RECO/DB121/ARCHIVELOG/2020_07_07/thread_1_seq_55.323.1045142549 RECID=156 STAMP=1045142550
deleted archived log
archived log file name=+RECO/DB121/ARCHIVELOG/2020_07_07/thread_1_seq_56.329.1045142559 RECID=160 STAMP=1045142558
deleted archived log
archived log file name=+RECO/DB121/ARCHIVELOG/2020_07_07/thread_1_seq_57.339.1045142565 RECID=162 STAMP=1045142565
Deleted 3 objects


released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=426 instance=db1211 device type=DISK
List of Archived Log Copies for database with db_unique_name DB121
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
158     2    40      A 07-JUL-20
        Name: +RECO/DB121/ARCHIVELOG/2020_07_07/thread_2_seq_40.1476.1045142551

161     2    41      A 07-JUL-20
        Name: +RECO/DB121/ARCHIVELOG/2020_07_07/thread_2_seq_41.335.1045142559

163     2    42      A 07-JUL-20
        Name: +RECO/DB121/ARCHIVELOG/2020_07_07/thread_2_seq_42.341.1045142565

deleted archived log
archived log file name=+RECO/DB121/ARCHIVELOG/2020_07_07/thread_2_seq_40.1476.1045142551 RECID=158 STAMP=1045142550
deleted archived log
archived log file name=+RECO/DB121/ARCHIVELOG/2020_07_07/thread_2_seq_41.335.1045142559 RECID=161 STAMP=1045142559
deleted archived log
archived log file name=+RECO/DB121/ARCHIVELOG/2020_07_07/thread_2_seq_42.341.1045142565 RECID=163 STAMP=1045142565
Deleted 3 objects


RMAN> 
```

let's enable transport in primary and confirm the Standby is waiting for those logs, creating what is call a GAP

```
## Primary ##
DGMGRL> edit database db121 SET STATE=TRANSPORT-ON;  
Succeeded.
DGMGRL> 

## Standby ##
13:23:56 st1211 > select inst_id, process, status MRP_stat, thread#, sequence#, block#, BLOCKS "Total Blocks"
from gv$managed_standby
where process like 'MRP%' or process like 'RFS%' and status != 'IDLE'
order by inst_id,process,thread#; 


14:05:10 st1211 > 14:05:10 st1211 > 14:05:10   2  14:05:10   3  14:05:10   4  
   INST_ID PROCESS   MRP_STAT	     THREAD#  SEQUENCE#     BLOCK# Total Blocks
---------- --------- ------------ ---------- ---------- ---------- ------------
	 1 MRP0      WAIT_FOR_GAP	   1	     56 	 0	      0

14:05:11 st1211 >

DGMGRL> show configuration

Configuration - dg_broker_config

  Protection Mode: MaxPerformance
  Members:
  db121 - Primary database
    Error: ORA-16724: cannot resolve gap for one or more standby databases

    st121 - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 39 seconds ago)

DGMGRL> 

```

Let's stop the recovery process and restart the Standby Database as Mounted

```
DGMGRL> edit database st121 SET STATE=APPLY-OFF;
Succeeded.



[oracle@rac2-node1 ~]$ srvctl stop database -d st121 ; srvctl start database -d st121 -o mount
[oracle@rac2-node1 ~]$ 


14:20:12 st1211 > SELECT INSTANCE_NAME, DATABASE_ROLE, OPEN_MODE from GV$INSTANCE, V$DATABASE;

INSTANCE_NAME    DATABASE_ROLE     OPEN_MODE
---------------  ----------------  -----------
st1211		     PHYSICAL STANDBY  MOUNTED
st1212		     PHYSICAL STANDBY  MOUNTED

14:21:50 st1211 > 

```


We can start now the recovery process from RMAN
```
[oracle@rac2-node1 ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jul 7 14:14:28 2020

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

connected to target database: DB121 (DBID=275414281)

RMAN> recover database from service db121;

Starting recover at 07-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 instance=st1211 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service db121
destination for restore of datafile 00001: +DATA12/ST121/DATAFILE/system.271.1026674613
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service db121
destination for restore of datafile 00002: +DATA12/ST121/DATAFILE/undotbs2.264.1026674613
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service db121
destination for restore of datafile 00003: +DATA12/ST121/DATAFILE/sysaux.256.1026674613
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service db121
destination for restore of datafile 00004: +DATA12/ST121/DATAFILE/undotbs1.257.1026674613
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service db121
destination for restore of datafile 00005: +DATA12/ST121/DATAFILE/example.270.1026674613
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service db121
destination for restore of datafile 00006: +DATA12/ST121/DATAFILE/users.265.1026674613
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

starting media recovery

archived log for thread 1 with sequence 58 is already on disk as file +RECO/ST121/ARCHIVELOG/2020_07_07/thread_1_seq_58.732.1045145205
archived log for thread 1 with sequence 59 is already on disk as file +RECO/ST121/ARCHIVELOG/2020_07_07/thread_1_seq_59.953.1045145819
archived log for thread 2 with sequence 40 is already on disk as file +RECO/ST121/ARCHIVELOG/2020_07_07/thread_2_seq_40.733.1045142551
archived log for thread 2 with sequence 43 is already on disk as file +RECO/ST121/ARCHIVELOG/2020_07_07/thread_2_seq_43.734.1045145205
archived log for thread 2 with sequence 44 is already on disk as file +RECO/ST121/ARCHIVELOG/2020_07_07/thread_2_seq_44.950.1045145817
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-JUL-20

RMAN> 

```

As better explained under this post from [redgate](https://www.red-gate.com/simple-talk/sql/oracle/rolling-forward-a-physical-standby-database-using-the-recover-command/) we still missing a step, since the standby controlfile still not updated.

For that, lets restart the system as "nomount", restore the controlfile from primary using "restore standby controlfile from service" command and restart the standby again



```
[oracle@rac2-node1 ~]$ srvctl stop database -d st121 ; srvctl start database -d st121 -o nomount
[oracle@rac2-node1 ~]$ 



RMAN> restore standby controlfile from service db121;

Starting restore at 07-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=420 instance=st1211 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service db121
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
output file name=+DATA12/ST121/CONTROLFILE/current.269.1026674399
Finished restore at 07-JUL-20

RMAN> 
```

An additional step I needed to execute was to mount the database, catalog the datafiles since the location from primary and Standby are different and then switch to copy
```
RMAN> catalog start with '+data12/ST121/datafile/';

Starting implicit crosscheck backup at 07-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
Crosschecked 3 objects
Finished implicit crosscheck backup at 07-JUL-20

Starting implicit crosscheck copy at 07-JUL-20
using channel ORA_DISK_1
Finished implicit crosscheck copy at 07-JUL-20

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

List of Cataloged Files
=======================
File Name: +RECO/ST121/ARCHIVELOG/2020_07_07/thread_2_seq_44.950.1045145817
File Name: +RECO/ST121/ARCHIVELOG/2020_07_07/thread_1_seq_59.953.1045145819

searching for all files that match the pattern +data12/ST121/datafile/

List of Files Unknown to the Database
=====================================
File Name: +DATA12/ST121/DATAFILE/example.270.1026674613
File Name: +DATA12/ST121/DATAFILE/system.271.1026674613
File Name: +DATA12/ST121/DATAFILE/sysaux.256.1026674613
File Name: +DATA12/ST121/DATAFILE/undotbs1.257.1026674613
File Name: +DATA12/ST121/DATAFILE/undotbs2.264.1026674613
File Name: +DATA12/ST121/DATAFILE/users.265.1026674613

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +DATA12/ST121/DATAFILE/example.270.1026674613
File Name: +DATA12/ST121/DATAFILE/system.271.1026674613
File Name: +DATA12/ST121/DATAFILE/sysaux.256.1026674613
File Name: +DATA12/ST121/DATAFILE/undotbs1.257.1026674613
File Name: +DATA12/ST121/DATAFILE/undotbs2.264.1026674613
File Name: +DATA12/ST121/DATAFILE/users.265.1026674613


RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA12/ST121/DATAFILE/system.271.1026674613"
datafile 2 switched to datafile copy "+DATA12/ST121/DATAFILE/undotbs2.264.1026674613"
datafile 3 switched to datafile copy "+DATA12/ST121/DATAFILE/sysaux.256.1026674613"
datafile 4 switched to datafile copy "+DATA12/ST121/DATAFILE/undotbs1.257.1026674613"
datafile 5 switched to datafile copy "+DATA12/ST121/DATAFILE/example.270.1026674613"
datafile 6 switched to datafile copy "+DATA12/ST121/DATAFILE/users.265.1026674613"

RMAN> 
```
Is also necessary to clear up the standby redolog to avoid issues

```
14:55:40 st1211 > alter database clear logfile group 1;
Database altered.

14:56:37 st1211 > alter database clear logfile group 2;
Database altered

14:56:37 st1211 > alter database clear logfile group 3;
Database altered
[......]
```


After that, we will see how the DB can be restarted and will be on sync with the Primary:


```
15:00:26 st1211 > select  thread#,max(sequence#) from v$archived_log where first_time > sysdate -1 and status ='A' group by thread# order by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
	 1	       68
	 2	       53

15:04:53 st1211 > 


DGMGRL> show database st121

Database - st121

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 32.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    st1211 (apply instance)
    st1212

Database Status:
SUCCESS

DGMGRL> 
```


In summary, we will need to following steps:



- Stop the Recovery process and restart database as MOUNTED

- Execute "RECOVER DATABASE FROM SERVICE" from RMAN

- Restart the Standby as NO-MOUNTED

- Excute "RESTORE STANDBY CONTROLFILE FROM SERVICE" from RMAN

- Mount the standby (from RMAN or restarting it with Mount option)

- Catalog/Switch to copy if your datafiles are in different location than primary
- Clear Standby Redolog groups

- Restart Standby Database

So, even thouigh we still have some different steps to follow and is not an fully automatic process, we get rid of the process of makig a backup from Primary, transport the backup to the standby system etc...