Hello Friend's
Introduction
Maintaining synchronization between the primary and backup databases in Oracle Data Guard settings is critical for disaster recovery. When a backup database lags behind or has problems, restoring it straight from the primary utilizing database services is a quick option. This blog shows a real-world recovery technique complete with genuine commands and outputs.
Scenario Overview
Primary Database: CASPRI
Standby Database: CASDR
Issue: Standby needs recovery and resynchronization from primary
Method: Service-based recovery using RMAN
Step 1: Verify Current Standby Status
First, let's check the current state of our standby database:
[oracle@standby ~]$ sqlplus / as sysdba
SQL> select name, db_unique_name, database_role, open_mode, startup_time from v$database, v$instance;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE STARTUP_T
--------- ------------------------------ ---------------- -------------------- ---------
CASPRI CASDR PHYSICAL STANDBY READ ONLY 25-NOV-25
Observation: The standby is in READ ONLY mode with physical standby role.
Step 2: Change Standby from READ ONLY to MOUNT State
To perform recovery, we need to mount the standby database:
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 805304048 bytes
Fixed Size 9139952 bytes
Variable Size 218103808 bytes
Database Buffers 570425344 bytes
Redo Buffers 7634944 bytes
Database mounted.
SQL> select name, db_unique_name, database_role, open_mode, startup_time from v$database, v$instance;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE STARTUP_T
--------- ------------------------------ ---------------- -------------------- ---------
CASPRI CASDR PHYSICAL STANDBY MOUNTED 25-NOV-25
Why: The database must be in MOUNT state for RMAN recovery operations. READ ONLY mode doesn't allow the necessary media recovery.
Step 3: Cancel Current Managed Recovery and Start Service-Based Recovery
SQL> alter database recover managed standby database cancel;
Database altered.
Now, let's initiate the recovery from the primary service using RMAN:
[oracle@standby ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 25 04:07:29 2025
Version 19.27.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: CASPRI (DBID=1554717114, not open)
RMAN> recover standby database from service CASPRI;
Starting recover at 25-NOV-25
using target database control file instead of recovery catalog
Oracle instance started
Total System Global Area 805304048 bytes
Fixed Size 9139952 bytes
Variable Size 218103808 bytes
Database Buffers 570425344 bytes
Redo Buffers 7634944 bytes
contents of Memory Script:
{
restore standby controlfile from service 'CASPRI';
alter database mount standby database;
}
executing Memory Script
Starting restore at 25-NOV-25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service CASPRI
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=+DATA/CASDR/CONTROLFILE/current.257.1140390057
output file name=+RECO/CASDR/CONTROLFILE/current.256.1140390059
Finished restore at 25-NOV-25
released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA/CASDR/TEMPFILE/temp.268.1140392229";
set newname for tempfile 2 to
"+DATA/CASDR/FED12977B30E5A84E0539A2BA8C01412/TEMPFILE/temp.269.1140392239";
set newname for tempfile 3 to
"+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/TEMPFILE/temp.278.1140885177";
switch tempfile all;
set newname for datafile 1 to
"+DATA/CASDR/DATAFILE/system.266.1140391549";
set newname for datafile 3 to
"+DATA/CASDR/DATAFILE/sysaux.260.1140391497";
set newname for datafile 4 to
"+DATA/CASDR/DATAFILE/undotbs1.258.1140391495";
set newname for datafile 5 to
"+DATA/CASDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/system.264.1140391541";
set newname for datafile 6 to
"+DATA/CASDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/sysaux.261.1140391503";
set newname for datafile 7 to
"+DATA/CASDR/DATAFILE/users.262.1140391539";
set newname for datafile 9 to
"+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/system.265.1140391541";
set newname for datafile 10 to
"+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/sysaux.259.1140391497";
set newname for datafile 11 to
"+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/users.263.1140391539";
catalog datafilecopy "+DATA/CASDR/DATAFILE/system.266.1140391549",
"+DATA/CASDR/DATAFILE/sysaux.260.1140391497",
"+DATA/CASDR/DATAFILE/undotbs1.258.1140391495",
"+DATA/CASDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/system.264.1140391541",
"+DATA/CASDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/sysaux.261.1140391503",
"+DATA/CASDR/DATAFILE/users.262.1140391539",
"+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/system.265.1140391541",
"+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/sysaux.259.1140391497",
"+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/users.263.1140391539";
switch datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting implicit crosscheck backup at 25-NOV-25
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
Crosschecked 15 objects
Finished implicit crosscheck backup at 25-NOV-25
Starting implicit crosscheck copy at 25-NOV-25
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-NOV-25
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +RECO/CASDR/AUTOBACKUP/2025_06_25/s_1140399009.344.1140399021
File Name: +RECO/CASDR/AUTOBACKUP/2025_06_24/n_1140384313.260.1140392755
File Name: +RECO/CASDR/ARCHIVELOG/2025_08_16/thread_1_seq_148.389.1145057135
File Name: +RECO/CASDR/ARCHIVELOG/2025_08_16/thread_1_seq_149.388.1145057139
File Name: +RECO/CASDR/ARCHIVELOG/2025_08_16/thread_1_seq_150.387.1145057141
File Name: +RECO/CASDR/ARCHIVELOG/2025_08_16/thread_1_seq_151.386.1145057143
File Name: +RECO/CASDR/ARCHIVELOG/2025_07_21/thread_1_seq_104.361.1142808737
File Name: +RECO/CASDR/ARCHIVELOG/2025_07_21/thread_1_seq_105.362.1142809523
File Name: +RECO/CASDR/ARCHIVELOG/2025_07_21/thread_1_seq_106.363.1142809525
File Name: +RECO/CASDR/ARCHIVELOG/2025_07_21/thread_1_seq_107.364.1142809529
File Name: +RECO/CASDR/ARCHIVELOG/2025_07_21/thread_1_seq_108.365.1142809673
File Name: +RECO/CASDR/ARCHIVELOG/2025_07_21/thread_1_seq_109.366.1142809673
File Name: +RECO/CASDR/ARCHIVELOG/2025_07_21/thread_1_seq_110.367.1142809675
File Name: +RECO/CASDR/ARCHIVELOG/2025_07_02/thread_1_seq_100.357.1141139729
File Name: +RECO/CASDR/ARCHIVELOG/2025_07_02/thread_1_seq_101.358.1141141103
File Name: +RECO/CASDR/ARCHIVELOG/2025_07_02/thread_1_seq_102.359.1141141113
renamed tempfile 1 to +DATA/CASDR/TEMPFILE/temp.268.1140392229 in control file
renamed tempfile 2 to +DATA/CASDR/FED12977B30E5A84E0539A2BA8C01412/TEMPFILE/temp.269.1140392239 in control file
renamed tempfile 3 to +DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/TEMPFILE/temp.278.1140885177 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
cataloged datafile copy
datafile copy file name=+DATA/CASDR/DATAFILE/system.266.1140391549 RECID=4 STAMP=1145066945
cataloged datafile copy
datafile copy file name=+DATA/CASDR/DATAFILE/sysaux.260.1140391497 RECID=5 STAMP=1145066945
cataloged datafile copy
datafile copy file name=+DATA/CASDR/DATAFILE/undotbs1.258.1140391495 RECID=6 STAMP=1145066946
cataloged datafile copy
datafile copy file name=+DATA/CASDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/system.264.1140391541 RECID=7 STAMP=1145066946
cataloged datafile copy
datafile copy file name=+DATA/CASDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/sysaux.261.1140391503 RECID=8 STAMP=1145066946
cataloged datafile copy
datafile copy file name=+DATA/CASDR/DATAFILE/users.262.1140391539 RECID=9 STAMP=1145066946
cataloged datafile copy
datafile copy file name=+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/system.265.1140391541 RECID=10 STAMP=1145066946
cataloged datafile copy
datafile copy file name=+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/sysaux.259.1140391497 RECID=11 STAMP=1145066946
cataloged datafile copy
datafile copy file name=+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/users.263.1140391539 RECID=12 STAMP=1145066946
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1145066945 file name=+DATA/CASDR/DATAFILE/system.266.1140391549
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1145066945 file name=+DATA/CASDR/DATAFILE/sysaux.260.1140391497
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1145066946 file name=+DATA/CASDR/DATAFILE/undotbs1.258.1140391495
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1145066946 file name=+DATA/CASDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/system.264.1140391541
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1145066946 file name=+DATA/CASDR/FED12977B30E5A84E0539A2BA8C01412/DATAFILE/sysaux.261.1140391503
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1145066946 file name=+DATA/CASDR/DATAFILE/users.262.1140391539
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=1145066946 file name=+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/system.265.1140391541
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP=1145066946 file name=+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/sysaux.259.1140391497
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=1145066946 file name=+DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/users.263.1140391539
Executing: alter database rename file '+DATA/CASPRI/ONLINELOG/group_1.264.1140306383' to '+DATA/CASDR/ONLINELOG/group_1.271.1140397041'
Executing: alter database rename file '+RECO/CASPRI/ONLINELOG/group_1.262.1140306401' to '+RECO/CASDR/ONLINELOG/group_1.282.1140397041'
Executing: alter database rename file '+DATA/CASPRI/ONLINELOG/group_2.263.1140306383' to '+DATA/CASDR/ONLINELOG/group_2.272.1140397053'
Executing: alter database rename file '+RECO/CASPRI/ONLINELOG/group_2.264.1140306403' to '+RECO/CASDR/ONLINELOG/group_2.283.1140397055'
Executing: alter database rename file '+DATA/CASPRI/ONLINELOG/group_3.262.1140306383' to '+DATA/CASDR/ONLINELOG/group_3.273.1140397085'
Executing: alter database rename file '+RECO/CASPRI/ONLINELOG/group_3.263.1140306403' to '+RECO/CASDR/ONLINELOG/group_3.285.1140397087'
contents of Memory Script:
{
recover database from service 'CASPRI';
}
executing Memory Script
Starting recover at 25-NOV-25
using channel ORA_DISK_1
skipping datafile 5; already restored to SCN 2139755
skipping datafile 6; already restored to SCN 2139755
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CASPRI
destination for restore of datafile 00001: +DATA/CASDR/DATAFILE/system.266.1140391549
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service CASPRI
destination for restore of datafile 00003: +DATA/CASDR/DATAFILE/sysaux.260.1140391497
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 network backup set from service CASPRI
destination for restore of datafile 00004: +DATA/CASDR/DATAFILE/undotbs1.258.1140391495
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 network backup set from service CASPRI
destination for restore of datafile 00007: +DATA/CASDR/DATAFILE/users.262.1140391539
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 CASPRI
destination for restore of datafile 00009: +DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/system.265.1140391541
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 CASPRI
destination for restore of datafile 00010: +DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/sysaux.259.1140391497
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 CASPRI
destination for restore of datafile 00011: +DATA/CASDR/FED16F56330B66C7E0539A2BA8C0354A/DATAFILE/users.263.1140391539
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-NOV-25
Executing: alter system set standby_file_management=auto
Finished recover at 25-NOV-25
What happened: RMAN performed incremental recovery, fetching only the changed blocks from the primary database.
Step 5: Final Recovery and Database Opening
After RMAN completes, we need to perform final recovery steps:
SQL> alter database recover managed standby database until consistent;
Note: If the above command hangs or takes too long, trigger log switching on the primary:
-- On primary database:
SQL> alter system switch logfile;
System altered.
Once recovery completes, open the standby in read-only mode:
SQL> alter database open read only;
Database altered.
Step 6: Restart Managed Recovery
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Step 7: Verify Redo Log Configuration
Let's check the current redo log configuration:
SQL> set lines 400 pages 40000
SQL> col member for a50
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
3 ONLINE +DATA/CASDR/ONLINELOG/group_3.273.1140397085 NO 0
3 ONLINE +RECO/CASDR/ONLINELOG/group_3.285.1140397087 YES 0
2 ONLINE +DATA/CASDR/ONLINELOG/group_2.272.1140397053 NO 0
2 ONLINE +RECO/CASDR/ONLINELOG/group_2.283.1140397055 YES 0
1 ONLINE +DATA/CASDR/ONLINELOG/group_1.271.1140397041 NO 0
1 ONLINE +RECO/CASDR/ONLINELOG/group_1.282.1140397041 YES 0
4 STANDBY +DATA/CASPRI/ONLINELOG/group_4.274.1140309493 NO 0
4 STANDBY +RECO/CASPRI/ONLINELOG/group_4.267.1140309493 NO 0
5 STANDBY +DATA/CASPRI/ONLINELOG/group_5.275.1140309505 NO 0
5 STANDBY +RECO/CASPRI/ONLINELOG/group_5.268.1140309507 NO 0
6 STANDBY +DATA/CASPRI/ONLINELOG/group_6.276.1140309515 NO 0
6 STANDBY +RECO/CASPRI/ONLINELOG/group_6.269.1140309515 NO 0
7 STANDBY +DATA/CASPRI/ONLINELOG/group_7.277.1140309523 NO 0
7 STANDBY +RECO/CASPRI/ONLINELOG/group_7.270.1140309523 NO 0
14 rows selected.
Observation: We can see both online redo logs (groups 1-3) and standby redo logs (group 4-7) with primary database paths.
Step 8: Recreate Standby Redo Logs
The standby redo logs still point to primary database paths. Let's recreate them properly:
8.1 Drop Existing Standby Redo Logs
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database drop standby logfile group 4;
Database altered.
SQL> alter database drop standby logfile group 5;
Database altered.
SQL> alter database drop standby logfile group 6;
Database altered.
SQL> alter database drop standby logfile group 7;
Database altered.
8.2 Add New Standby Redo Logs
SQL> alter database add standby logfile group 4 ('+DATA','+RECO') size 200m;
Database altered.
SQL> alter database add standby logfile group 5 ('+DATA','+RECO') size 200m;
Database altered.
SQL> alter database add standby logfile group 6 ('+DATA','+RECO') size 200m;
Database altered.
SQL> alter database add standby logfile group 7 ('+DATA','+RECO') size 200m;
Database altered.
8.3 Verify New Standby Redo Logs
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- -------------------------------------------------- --- ----------
3 ONLINE +DATA/CASDR/ONLINELOG/group_3.273.1140397085 NO 0
3 ONLINE +RECO/CASDR/ONLINELOG/group_3.285.1140397087 YES 0
2 ONLINE +DATA/CASDR/ONLINELOG/group_2.272.1140397053 NO 0
2 ONLINE +RECO/CASDR/ONLINELOG/group_2.283.1140397055 YES 0
1 ONLINE +DATA/CASDR/ONLINELOG/group_1.271.1140397041 NO 0
1 ONLINE +RECO/CASDR/ONLINELOG/group_1.282.1140397041 YES 0
4 STANDBY +DATA/CASDR/ONLINELOG/group_4.279.1145068273 NO 0
4 STANDBY +RECO/CASDR/ONLINELOG/group_4.381.1145068275 YES 0
5 STANDBY +DATA/CASDR/ONLINELOG/group_5.280.1145068333 NO 0
5 STANDBY +RECO/CASDR/ONLINELOG/group_5.380.1145068335 YES 0
6 STANDBY +DATA/CASDR/ONLINELOG/group_6.281.1145068363 NO 0
6 STANDBY +RECO/CASDR/ONLINELOG/group_6.379.1145068365 YES 0
7 STANDBY +DATA/CASDR/ONLINELOG/group_7.282.1145068369 NO 0
7 STANDBY +RECO/CASDR/ONLINELOG/group_7.378.1145068369 YES 0
Step 9: Restart Managed Recovery
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Post-Recovery Validation
Check Recovery Status
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
MRP0 APPLYING_LOG 1 152 192512 1024
Verify Gap Status
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
SQL> SELECT MAX(SEQUENCE#) AS "Last Applied" FROM V$ARCHIVED_LOG WHERE APPLIED='YES';
Key Benefits of Service-Based Recovery
Automated: RMAN handles most complexity automatically
Efficient: Transfers only incremental changes
Network Optimized: Uses compression for network transfers
Integrated: Leverages existing Oracle networking (TNS services)
Reliable: Maintains data consistency throughout the process
Troubleshooting Tips
Stuck Recovery: If ALTER DATABASE RECOVER MANAGED STANDBY DATABASE UNTIL CONSISTENT hangs, switch logs on primary
Space Issues: Ensure sufficient space in FRA before starting recovery
Network Issues: Verify TNS connectivity between primary and standby
Service Issues: Confirm database services are running on both nodes
ConversionConversion EmoticonEmoticon