![Cover image for Rolling Forward a Physical Standby using "Recover From Service" command [12.1 feature]](https://media.dev.to/cdn-cgi/image/width=1000,height=420,fit=cover,gravity=auto,format=auto/https%3A%2F%2Fdev-to-uploads.s3.amazonaws.com%2Fi%2Fdqbsj6k8osgq71j1u6jq.jpg)
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)
=========================================================================
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 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...
Top comments (0)