Oracle DBA

A standby database is a transactionally consistent copy of the production database. It enables the production Oracle database to survive disasters and data corruption. When the production database becomes unavailable due to a planned or unplanned outage, Data Guard can promote a standby database to the primary role, minimizing downtime.

In addition, performance of the production database can be enhanced by offloading resource-intensive backup and reporting operations to the standby system. Therefore, it is always desirable to keep the standby database synchronized with the primary database.

A standby database may lag behind the primary due to:

• Insufficient or unavailable network bandwidth between the primary and standby databases
• Unavailability of the standby database
• Corruption or accidental deletion of archive redo data on the primary

Synchronizing the standby with the primary by applying archived logs can be time-consuming, as it applies both committed and uncommitted transactions. Using RMAN incremental backups containing only the changes since the last refresh SCN of the standby database is a faster and more efficient alternative. Incremental backups also help when archived logs are missing on the primary database.

Overview

1. Check the sequence in both nodes
2. Stop the MRP process
3. Check the SCN in the standby
4. Shut down the standby database
5. Take the RMAN incremental backup from the noted SCN in the standby database
6. Create a standby control file to restore in the standby database
7. Transfer backup files to the standby server
8. Replace the existing control file with the new one or restore it using RMAN
9. Recover the database
10. Start the MRP process
11. Verify the sequence in both nodes

Refresh Standby Using RMAN Incremental SCN-Based Backup

Step 1: Check the Sequence in Both Nodes

Primary Database:

SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

THREAD#   Last Primary Seq Generated
--------  --------------------------
1         68

Standby Database:

SQL> select thread#, max(sequence#) "Last Standby Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

THREAD#   Last Standby Seq Generated
--------  --------------------------
1         62

Step 2: Stop the MRP Process

SQL> alter database recover managed standby database finish;
Database altered.

Step 3: Check the SCN in the Standby

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
2173711

Step 4: Shut Down the Standby Database

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Step 5: Take the RMAN Incremental Backup from the SCN on the Primary

$ rman target /

RMAN> run {
allocate channel c1 type disk format '/u01/backup/archive%U.bkp';
backup incremental from scn 2173711 database;
}

Sample Output:

Starting backup at 22-JUL-25
channel c1: starting full datafile backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL/users01.dbf
channel c1: finished piece 1 at 22-JUL-25
piece handle=/u01/backup/archive2pu4o8oo_1_1.bkp
Finished backup at 22-JUL-25

Step 6: Create a Standby Control File

SQL> alter database create standby controlfile as '/u01/backup/standby.ctl';
Database altered.

Step 7: Transfer Backup Files to the Standby Server

$ scp /u01/backup/archive2* oracle@192.168.1.44:/u01/backup
$ scp /u01/backup/standby.ctl oracle@192.168.1.44:/u01/backup

Step 8: Replace and Restore the Control File

Manually copy the control file to both locations:

$ cp /u01/backup/standby.ctl /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl
$ cp /u01/backup/standby.ctl /u01/app/oracle/oradata/ORCL/control01.ctl

OR use RMAN to restore it:

SQL> startup mount;
Database mounted.

Step 9: Recover the Database

$ rman target /

RMAN> catalog start with '/u01/backup';
RMAN> recover database;

Sample Output:

Starting recover at 22-JUL-25
channel ORA_DISK_1: starting incremental datafile backup set restore
destination for restore of datafile 00001: /u01/app/oracle/oradata/ORCL/system01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/ORCL/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/ORCL/undotbs01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
media recovery complete
Finished recover at 22-JUL-25

Step 10: Start the MRP Process

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

Step 11: Verify the Sequences

Primary Database:

SQL> select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

THREAD#   Last Primary Seq Generated
--------  --------------------------
1         71

Standby Database:

SQL> select thread#, max(sequence#) "Last Standby Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

THREAD#   Last Standby Seq Generated
--------  --------------------------
1         71

Applied Logs Verification:

SQL> select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied in ('YES','IN-MEMORY')
group by thread# order by 1;

THREAD#   Last Standby Seq Applied
--------  ------------------------
1         71