Introduction
From 12c onwards, Oracle provides the RECOVER ... FROM SERVICE syntax, which lets you resynchronize a standby database directly over the network from the primary, without needing a fresh RMAN backup transferred manually. It significantly reduces the steps compared to a full re-instantiation, since RMAN pulls only what's needed straight from the primary's service. This is especially useful when a standby has fallen too far behind for normal redo apply to catch up, but not so far gone that a complete rebuild is necessary. In this article, we'll walk through recovering an out-of-sync standby using this method.
Prerequisites
- Oracle Data Guard should already be configured between the primary and standby.
- Disable Data Guard Broker if it's in use, since Broker will automatically restart managed recovery (MRP) and interfere with the manual steps below.
- Oracle version 12c or later, this syntax isn't available on older releases.
Environment Used in This Guide
| Server | Primary | Standby |
|---|---|---|
| Hostname | Source | Target |
| IP | 192.168.80.51 | 192.168.80.111 |
| OS | OEL 9 | OEL 9 |
| SID | ORCLDC | ORCLDR |
| Service Name | ORCLDC | ORCLDR |
Step 1: Confirm the Gap Between Primary and Standby
Before recovering anything, confirm just how far behind the standby actually is, both at the archive log sequence level and the SCN (System Change Number) level. This tells you whether RECOVER FROM SERVICE is even the right tool, if the gap is enormous or datafiles are corrupted, a full rebuild might be faster.
At Primary:
SQL> select name, db_unique_name, database_role, open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL ORCLDC PRIMARY READ WRITE
SQL>
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq, a.last_app_timestamp,
b.last_seq - a.applied_seq ARC_DIFF
from
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp
from gv$archived_log
where applied='YES'
group by thread#) a,
(select thread#, MAX(sequence#) last_seq
from gv$archived_log
group by thread#) b,
(select db_unique_name, database_role
from v$database) d
where a.thread# = b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF
------------------------------ ---------------- ---------- ---------- ----------- -------------------- ----------
ORCLDC PRIMARY 1 210 104 08-FEB-2025 20:39:23 106
SQL>
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4123407
SQL>
SQL> set lines 333
SQL> col file for a60
SQL> select HXFIL File_num, substr(HXFNM,1,60) "file", fhscn from x$kcvfh;
FILE_NUM file FHSCN
-------- ------------------------------------------------------------ --------------------
1 /u01/app/oracle/oradata/ORCL/system01.dbf 4122798
3 /u01/app/oracle/oradata/ORCL/sysaux01.dbf 4122798
4 /u01/app/oracle/oradata/ORCL/undotbs01.dbf 4122798
5 /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf 2345308
6 /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf 2345308
7 /u01/app/oracle/oradata/ORCL/users01.dbf 4122798
8 /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf 2345308
9 /u01/app/oracle/oradata/ORCL/orclpdb/system01.dbf 4122847
10 /u01/app/oracle/oradata/ORCL/orclpdb/sysaux01.dbf 4123033
11 /u01/app/oracle/oradata/ORCL/orclpdb/undotbs01.dbf 4123159
12 /u01/app/oracle/oradata/ORCL/orclpdb/users01.dbf 4123189
13 /u01/app/oracle/oradata/ORCL/orclpdb/test01.dbf 4123195
12 rows selected.
At Standby:
SQL> select name, db_unique_name, database_role, open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ -------------------- -------------------------
ORCL ORCLDR PHYSICAL STANDBY READ ONLY WITH APPLY
SQL>
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq,
b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp)*24*60,2) Gap_in_Mins,
round((sysdate - a.last_app_timestamp)*24*60*60,2) Gap_in_Seconds
from
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp
from gv$archived_log
where REGISTRAR='RFS' and applied='YES'
group by thread#) a,
(select thread#, MAX(sequence#) last_seq
from gv$archived_log
group by thread#) b,
(select db_unique_name, database_role
from v$database) d
where a.thread# = b.thread#;
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- -------- -------------------- ------------ ---------------
ORCLDR PHYSICAL STANDBY 1 210 104 106 08-FEB-2025 20:39:23 71667.78 4300067
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2950967
SQL> set lines 333
SQL> col file for a60
SQL> select HXFIL File_num, substr(HXFNM,1,60) "file", fhscn from x$kcvfh;
FILE_NUM file FHSCN
-------- ------------------------------------------------------------ --------------------
1 /u01/app/oracle/oradata/ORCLDR/system01.dbf 2950968
3 /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf 2950968
4 /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf 2950968
5 /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf 2345308
6 /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf 2345308
7 /u01/app/oracle/oradata/ORCLDR/users01.dbf 2950968
8 /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf 2345308
9 /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf 2950968
10 /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf 2950968
11 /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf 2950968
12 /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf 2950968
13 /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf 2950968
12 rows selected.
SQL>
Comparing the two outputs shows both an apply gap (106 sequences behind) and a clear SCN mismatch between primary and standby, confirming the standby needs to be resynchronized.
Step 2: Cancel MRP and Start the Standby in Nomount Mode
Managed recovery needs to be stopped before we can restore a fresh control file, and the instance needs to come back up in nomount mode since we're about to replace its control file entirely.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1459616616 bytes
Fixed Size 9177960 bytes
Variable Size 1224736768 bytes
Database Buffers 218103808 bytes
Redo Buffers 7598080 bytes
SQL>
Step 3: Restore the Standby Control File From the Primary's Service
Connect RMAN to the standby and restore its control file directly from the primary over the network, using the primary's TNS service name. This is the first step that actually uses the FROM SERVICE capability, no manual backup transfer required.
[oracle@target ~]$ rman target sys
Recovery Manager: Release 19.0.0.0.0 - Production on Sun Mar 30 15:39:31 2025
Version 19.24.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: ORCL (not mounted)
RMAN> restore standby controlfile from service orcldc;
Starting restore at 30-MAR-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service orcldc
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/ORCLDR/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORCLDR/control02.ctl
Finished restore at 30-MAR-25
RMAN>
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN> report schema;
Starting implicit crosscheck backup at 30-MAR-25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
Crosschecked 15 objects
Finished implicit crosscheck backup at 30-MAR-25
Starting implicit crosscheck copy at 30-MAR-25
using channel ORA_DISK_1
Finished implicit crosscheck copy at 30-MAR-25
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_03_30/o1_mf_1_208_myl27nc7_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_03_30/o1_mf_1_209_myl33owm_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_03_30/o1_mf_1_210_myl3zxlm_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/archivelog/2025_03_30/o1_mf_1_211_myl4vyvz_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/autobackup/2025_01_23/o1_mf_s_1191149732_ms3npt02_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/autobackup/2025_01_24/o1_mf_s_1191234421_ms67n42x_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/autobackup/2025_01_24/o1_mf_s_1191235588_ms68kdwf_.bkp
File Name: /u01/app/oracle/fast_recovery_area/ORCLDR/autobackup/2025_01_24/o1_mf_s_1191236506_ms69n194_.bkp
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLDR
List of Permanent Datafiles
=======================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1100 SYSTEM *** /u01/app/oracle/oradata/ORCLDR/system01.dbf
3 890 SYSAUX *** /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
4 710 UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
5 450 PDB$SEED:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf
6 430 PDB$SEED:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf
7 5 USERS *** /u01/app/oracle/oradata/ORCLDR/users01.dbf
8 230 PDB$SEED:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf
9 460 ORCLPDB:SYSTEM *** /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
10 490 ORCLPDB:SYSAUX *** /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
11 230 ORCLPDB:UNDOTBS1 *** /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
12 5 ORCLPDB:USERS *** /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
13 1024 ORCLPDB:TEST *** /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 32 TEMP 32767 /u01/app/oracle/oradata/ORCLDR/temp01.dbf
2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/pdbseed/temp012025-01-22_00-17-43-687-AM.dbf
3 174 ORCLPDB:TEMP 32767 /u01/app/oracle/oradata/ORCLDR/orclpdb/temp01.dbf
RMAN>
The RMAN-06139 warning is expected here, the freshly restored control file is newer than the datafiles on disk, which is exactly the mismatch the next step resolves.
Step 4: Recover the Standby From the Primary's Service
This is the core step. RECOVER DATABASE FROM SERVICE ... NOREDO USING COMPRESSED BACKUPSET pulls only the incremental changes needed to bring each datafile up to date, directly from the primary, rather than restoring full datafiles from scratch. Notice RMAN automatically skips datafiles 5, 6, and 8 (the PDB$SEED files), they're already at the correct SCN and don't need touching.
RMAN> recover database from service orcldc noredo using compressed backupset;
Starting recover at 30-MAR-25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
skipping datafile 5; already restored to SCN 2345308
skipping datafile 6; already restored to SCN 2345308
skipping datafile 8; already restored to SCN 2345308
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcldc
destination for restore of datafile 00001: /u01/app/oracle/oradata/ORCLDR/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcldc
destination for restore of datafile 00003: /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:02:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcldc
destination for restore of datafile 00004: /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcldc
destination for restore of datafile 00007: /u01/app/oracle/oradata/ORCLDR/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcldc
destination for restore of datafile 00009: /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcldc
destination for restore of datafile 00010: /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:01:27
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcldc
destination for restore of datafile 00011: /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service orcldc
destination for restore of datafile 00012: /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf
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 compressed network backup set from service orcldc
destination for restore of datafile 00013: /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 30-MAR-25
RMAN> exit
Recovery Manager complete.
Step 5: Verify the SCN and Restart Managed Recovery
Check the standby's SCN and per-datafile SCNs to confirm they now align much more closely with the primary, then restart managed recovery so the standby resumes normal redo apply going forward. Finally, switch a few logs on the primary to confirm the standby is catching up in real time.
SQL> select name, db_unique_name, database_role, open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- --------------------
ORCL ORCLDR PHYSICAL STANDBY MOUNTED
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4121015
SQL> set lines 333
SQL> col file for a60
SQL> select HXFIL File_num, substr(HXFNM,1,60) "file", fhscn from x$kcvfh;
FILE_NUM file FHSCN
-------- ------------------------------------------------------------ ---------
1 /u01/app/oracle/oradata/ORCLDR/system01.dbf 4122274
3 /u01/app/oracle/oradata/ORCLDR/sysaux01.dbf 4122323
4 /u01/app/oracle/oradata/ORCLDR/undotbs01.dbf 4122531
5 /u01/app/oracle/oradata/ORCLDR/pdbseed/system01.dbf 2345308
6 /u01/app/oracle/oradata/ORCLDR/pdbseed/sysaux01.dbf 2345308
7 /u01/app/oracle/oradata/ORCLDR/users01.dbf 4122616
8 /u01/app/oracle/oradata/ORCLDR/pdbseed/undotbs01.dbf 2345308
9 /u01/app/oracle/oradata/ORCLDR/orclpdb/system01.dbf 4122847
10 /u01/app/oracle/oradata/ORCLDR/orclpdb/sysaux01.dbf 4123033
11 /u01/app/oracle/oradata/ORCLDR/orclpdb/undotbs01.dbf 4123159
12 /u01/app/oracle/oradata/ORCLDR/orclpdb/users01.dbf 4123189
13 /u01/app/oracle/oradata/ORCLDR/orclpdb/test01.dbf 4123195
12 rows selected.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> set lines 200 pages 300
SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select d.db_unique_name, d.database_role, a.thread#, b.last_seq, a.applied_seq,
b.last_seq - a.applied_seq ARC_DIFF, a.last_app_timestamp,
round((sysdate - a.last_app_timestamp) * 24 * 60, 2) Gap_in_Mins,
round((sysdate - a.last_app_timestamp) * 24 * 60 * 60, 2) Gap_in_Seconds
FROM
(select thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp
from gv$archived_log
where REGISTRAR='RFS' and applied='YES'
group by thread#) a,
(select thread#, MAX(sequence#) last_seq from gv$archived_log group by thread#) b,
(select db_unique_name, database_role from v$database) d
WHERE a.thread# = b.thread#;
SQL>
DB_UNIQUE_NAME DATABASE_ROLE THREAD# LAST_SEQ APPLIED_SEQ ARC_DIFF LAST_APP_TIMESTAMP GAP_IN_MINS GAP_IN_SECONDS
------------------------------ ---------------- ---------- ---------- ----------- ---------- -------------------- ----------- --------------
ORCLDR PHYSICAL STANDBY 1 217 217 0 30-MAR-2025 15:59:50 5.1 306
SQL>
ARC_DIFF is back to 0, confirming the standby has fully caught up and is now applying redo in real time. The datafile SCNs are also much closer to the primary's, well within the range managed recovery can close on its own from here.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment