- 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.
- 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:
| 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>
2. Now stop the MRP process at Standby. Also shutdown the DB & start in nomount.
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>
Thanks for visiting!!
0 comments:
Post a Comment