- Introduction: From 12c onwards, Oracle provides a new commands RECOVER FROM SERVICE for make the database sync if standby database is out of sync by reducing the steps. In this article, we will recover the standby using RECOVER FROM SERVICE method.
- Prerequisites:
- Oracle Data Guard should be configured.
- Disable DG broker if any as it will start MRP automatically.
- Oracle version should be 12c or later.
- Environment:
- Now proceed for recovery activity step by step:
| 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 |
1. Check difference of SCN at DB and file level between Primary and Standby.
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.
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>
As per above output we can see the gap between Primary & Standy database. Also we have observed the difference in SCN.
2. Now cancel the MRP and shutdown the standby database. Then startup in nomount state.
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>
[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>
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.
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>
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
Comments
Post a Comment