oraclednaThis post demonstrates rolling forward the physical standby database to bring it back to sync with primary when archivelogs missed or have a huge gap.
When archivelogs are missed, rolling forward an Oracle Physical Standby Database entails applying incremental backups from the primary database using RMAN to synchronize it. The automated approach is using “RECOVER STANDBY DATABASE FROM SERVICE” to automate the backup, transfer and apply.
This approach is the fastest and reduces manual steps. This method applies to Version 18.1.0.0.0 and later
1. Stop the recovery managed process on standby database.
SQL> alter database recover managed standby database cancel;
Database altered.
Below is the archivelog sync gap between primary and standby databases.
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#;
Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1 66 52 14
2. Ensure that Oracle Net connectivity is established between the physical standby database and the primary database.
On Primary:
=========
[oracle@prod ~]$ tnsping stdby
TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 09-MAR-2026 19:18:36
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.142)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stdby) (UR = A)))
OK (0 msec)
[oracle@prod ~]$ sqlplus sys/manager@stdby as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 9 19:21:54 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
On Standby:
=========
[oracle@stdby ~]$ tnsping prod
TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 09-MAR-2026 19:19:28
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.141)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prod) (UR = A)))
OK (10 msec)
[oracle@stdby ~]$ sqlplus sys/manager@prod as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Mon Mar 9 19:22:42 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0
3. On Standby database, connect to RMAN and recover the standby database.
[oracle@stdby ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 – Production on Mon Mar 9 19:24:40 2026
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=608515774, not open)
RMAN> RECOVER STANDBY DATABASE FROM SERVICE prod;
Starting recover at 09-MAR-2026 19:25:40
using target database control file instead of recovery catalog
Oracle instance started
Total System Global Area 2113927120 bytes
Fixed Size 8898512 bytes
Variable Size 486539264 bytes
Database Buffers 1610612736 bytes
Redo Buffers 7876608 bytes
contents of Memory Script:
{
restore standby controlfile from service ‘prod’;
alter database mount standby database;
}
executing Memory Script
Starting restore at 09-MAR-2026 19:25:50
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service prod
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/STDBY/control01.ctl
output file name=/u01/app/oracle/oradata/STDBY/control02.ctl
Finished restore at 09-MAR-2026 19:25:53
released channel: ORA_DISK_1
Statement processed
Executing: alter system set standby_file_management=manual
contents of Memory Script:
{
recover database from service ‘prod’;
}
executing Memory Script
Starting recover at 09-MAR-2026 19:25:59
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service prod
destination for restore of datafile 00001: /u01/app/oracle/oradata/STDBY/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service prod
destination for restore of datafile 00003: /u01/app/oracle/oradata/STDBY/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using network backup set from service prod
destination for restore of datafile 00004: /u01/app/oracle/oradata/STDBY/undotbs01.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 network backup set from service prod
destination for restore of datafile 00007: /u01/app/oracle/oradata/STDBY/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 09-MAR-2026 19:26:16
Executing: alter system set standby_file_management=auto
Finished recover at 09-MAR-2026 19:26:16
Standby alert log:
=============
License high water mark = 8
2026-03-09T19:25:42.055216+05:30
USER(prelim) (ospid: 17531): terminating the instance
2026-03-09T19:25:43.098455+05:30
Instance terminated by USER(prelim), pid = 17531
2026-03-09T19:25:46.466196+05:30
Starting ORACLE instance (normal) (OS id: 17568)
2026-03-09T19:25:46.471862+05:30
****************************************************
Sys-V shared memory will be used for creating SGA
****************************************************
2026-03-09T19:25:46.473340+05:30
**********************************************************************
2026-03-09T19:25:46.473424+05:30
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
2026-03-09T19:25:46.473592+05:30
Per process system memlock (soft) limit = 3072M
2026-03-09T19:25:46.473668+05:30
Expected per process system memlock (soft) limit to lock
instance MAX SHARED GLOBAL AREA (SGA) into memory: 2018M
2026-03-09T19:25:46.473893+05:30
Available system pagesizes:
4K, 2048K
2026-03-09T19:25:46.474041+05:30
Supported system pagesize(s):
2026-03-09T19:25:46.474117+05:30
PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
2026-03-09T19:25:46.474193+05:30
4K Configured 4 516100 NONE
2026-03-09T19:25:46.474396+05:30
2048K 0 1009 0 NONE
2026-03-09T19:25:46.474472+05:30
RECOMMENDATION:
2026-03-09T19:25:46.474545+05:30
1. For optimal performance, configure system with expected number
of pages for every supported system pagesize prior to the next
instance restart operation.
2026-03-09T19:25:46.492337+05:30
**********************************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 1
Number of processor cores in the system is 1
Number of processor sockets in the system is 1
Capability Type : Network
capabilities requested : 7 detected : 0 Simulated : 0
Capability Type : Runtime Environment
capabilities requested : 400000FF detected : 40000000 Simulated : 0
Capability Type : Engineered Systems
capabilities requested : 7 detected : 0 Simulated : 0
Capability Type : Database Test
capabilities requested : 3 detected : 0 Simulated : 0
Autotune of undo retention is turned on.
IMODE=BR
ILAT =51
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Cluster configuration type = NONE [2]
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.3.0.0.0.
ORACLE_HOME: /u01/app/oracle/product/19.0.0/db_1
System name: Linux
Node name: stdby
Release: 4.14.35-1902.3.2.el7uek.x86_64
Version: #2 SMP Tue Jul 30 03:59:02 GMT 2019
Machine: x86_64
Using parameter settings in server-side spfile /u01/app/oracle/product/19.0.0/db_1/dbs/spfilestdby.ora
System parameters with non-default values:
processes = 300
nls_language = “AMERICAN”
nls_territory = “AMERICA”
sga_target = 2016M
control_files = “/u01/app/oracle/oradata/STDBY/control01.ctl”
control_files = “/u01/app/oracle/oradata/STDBY/control02.ctl”
db_file_name_convert = “/u01/app/oracle/oradata/PROD”
db_file_name_convert = “/u01/app/oracle/oradata/STDBY”
log_file_name_convert = “/u01/app/oracle/oradata/PROD”
log_file_name_convert = “/u01/app/oracle/oradata/STDBY”
db_block_size = 8192
compatible = “19.0.0”
log_archive_dest_1 = “LOCATION=/u01/arch/stdby VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby”
log_archive_dest_2 = “SERVICE=PROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod”
log_archive_dest_state_1 = “ENABLE”
log_archive_dest_state_2 = “ENABLE”
fal_client = “STDBY”
fal_server = “PROD”
log_archive_config = “DG_CONFIG=(prod,stdby)”
log_archive_format = “%s_%t_%r.arc”
standby_file_management = “AUTO”
undo_tablespace = “UNDOTBS1”
remote_login_passwordfile= “EXCLUSIVE”
dispatchers = “(PROTOCOL=TCP) (SERVICE=prodXDB)”
local_listener = “LISTENER_STDBY”
audit_file_dest = “/u01/app/oracle/admin/stdby/adump”
audit_trail = “DB”
db_name = “prod”
db_unique_name = “stdby”
open_cursors = 300
pga_aggregate_target = 670M
diagnostic_dest = “/u01/app/oracle”
2026-03-09T19:25:46.708554+05:30
============================================================
NOTE: PatchLevel of this instance 0
============================================================
WARNING: Invalid value for listener related parameter MIXED LISTENER
2026-03-09T19:25:46.732395+05:30
ORA-00141: all addresses specified for parameter LOCAL_LISTENER are invalid
ORA-00132: syntax error or unresolved network name ‘LISTENER_STDBY’
Starting background process PMON
2026-03-09T19:25:46.744510+05:30
PMON started with pid=2, OS id=17576
Starting background process CLMN
2026-03-09T19:25:46.760424+05:30
CLMN started with pid=3, OS id=17578
Starting background process PSP0
2026-03-09T19:25:46.778521+05:30
PSP0 started with pid=4, OS id=17580
Starting background process VKTM
2026-03-09T19:25:46.796702+05:30
VKTM started with pid=5, OS id=17582 at elevated (RT) priority
2026-03-09T19:25:46.797928+05:30
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Starting background process GEN0
2026-03-09T19:25:46.811539+05:30
GEN0 started with pid=6, OS id=17587
Starting background process MMAN
2026-03-09T19:25:46.877363+05:30
MMAN started with pid=8, OS id=17591
Starting background process GEN1
2026-03-09T19:25:46.947694+05:30
GEN1 started with pid=9, OS id=17593_17594
Starting background process DIAG
2026-03-09T19:25:46.999620+05:30
DIAG started with pid=11, OS id=17596
Starting background process OFSD
2026-03-09T19:25:47.074470+05:30
OFSD started with pid=12, OS id=17598_17599
2026-03-09T19:25:47.075166+05:30
Oracle running with ofslib:’Oracle File Server Library’ version=2
Starting background process DBRM
2026-03-09T19:25:47.139829+05:30
DBRM started with pid=14, OS id=17601
Starting background process VKRM
2026-03-09T19:25:47.205745+05:30
VKRM started with pid=15, OS id=17603
Starting background process SVCB
2026-03-09T19:25:47.257578+05:30
SVCB started with pid=16, OS id=17605
Starting background process PMAN
2026-03-09T19:25:47.319593+05:30
PMAN started with pid=17, OS id=17607
Starting background process DIA0
2026-03-09T19:25:47.364942+05:30
DIA0 started with pid=18, OS id=17609
Starting background process DBW0
2026-03-09T19:25:47.430701+05:30
DBW0 started with pid=19, OS id=17611
Starting background process LGWR
2026-03-09T19:25:47.479799+05:30
LGWR started with pid=20, OS id=17613
2026-03-09T19:25:47.480747+05:30
Starting background process CKPT
2026-03-09T19:25:47.539972+05:30
CKPT started with pid=21, OS id=17615
Starting background process SMON
2026-03-09T19:25:47.586207+05:30
SMON started with pid=22, OS id=17617
Starting background process SMCO
2026-03-09T19:25:47.635474+05:30
SMCO started with pid=23, OS id=17619
Starting background process RECO
2026-03-09T19:25:47.735373+05:30
RECO started with pid=25, OS id=17623
Starting background process LREG
2026-03-09T19:25:47.845842+05:30
LREG started with pid=27, OS id=17627
Starting background process PXMN
2026-03-09T19:25:47.913565+05:30
PXMN started with pid=28, OS id=17629
Starting background process FENC
2026-03-09T19:25:47.973397+05:30
FENC started with pid=29, OS id=17632
Starting background process MMON
2026-03-09T19:25:48.040691+05:30
MMON started with pid=30, OS id=17634
Starting background process MMNL
2026-03-09T19:25:48.099556+05:30
MMNL started with pid=29, OS id=17636
2026-03-09T19:25:48.100281+05:30
starting up 1 dispatcher(s) for network address ‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’…
starting up 1 shared server(s) …
Starting background process TMON
2026-03-09T19:25:48.253850+05:30
TMON started with pid=33, OS id=17642
Setting CPU count to 1
ORACLE_BASE from environment = /u01/app/oracle
2026-03-09T19:25:50.196278+05:30
Using default pga_aggregate_limit of 2048 MB
2026-03-09T19:25:53.066641+05:30
Conversion to standby controlfile pending for restored file
No controlfile conversion
No controlfile conversion
2026-03-09T19:25:53.970151+05:30
alter database mount standby database
2026-03-09T19:25:58.044020+05:30
Converting controlfile to standby
If db_file_name_convert or log_file_name_convert parameters
are not used, then RMAN intervention is required to fix the
file names in the converted control file. Refer to RMAN
documentation for how to fix all file names.
Clearing standby activation ID 639146258 (0x26189912)
The primary database controlfile was created using the
‘MAXLOGFILES 16’ clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE ‘srl1.f’ SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl2.f’ SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl3.f’ SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE ‘srl4.f’ SIZE 209715200;
Set as converted control file due to db_unique_name mismatch
Changing di2dbun from prod to stdby
…. (PID:17646): RT: Role transition work is not done
…. (PID:17646): Redo network throttle feature is disabled at mount time
2026-03-09T19:25:58.140160+05:30
Successful mount of redo thread 1, with mount id 639320513
2026-03-09T19:25:58.141680+05:30
…. (PID:17646): Database role set to PHYSICAL STANDBY [kcvfdb.c:9076]
Physical Standby Database mounted.
…. (PID:17646): STARTING ARCH PROCESSES
Starting background process ARC0
2026-03-09T19:25:58.153840+05:30
ARC0 started with pid=37, OS id=17720
…. (PID:17646): ARC0: Archival started
…. (PID:17646): STARTING ARCH PROCESSES COMPLETE
2026-03-09T19:25:58.155056+05:30
In-memory operation on ADG is currently only supported on Engineered systems and PaaS.
inmemory_adg_enabled is turned off automatically.
Please contact our support team for EXADATA solutions
Lost write protection disabled
…. (PID:17646): Using STANDBY_ARCHIVE_DEST parameter default value as /u01/arch/stdby [krsd.c:18141]
2026-03-09T19:25:58.169096+05:30
ARC0 (PID:17720): Becoming a ‘no FAL’ ARCH
2026-03-09T19:25:58.201834+05:30
TT00 (PID:17722): Gap Manager starting
2026-03-09T19:25:58.264049+05:30
TMON (PID:17642): STARTING ARCH PROCESSES
Starting background process ARC1
2026-03-09T19:25:58.272831+05:30
Archiving previously deferred ORLs (stdby)
Completed: alter database mount standby database
2026-03-09T19:25:58.334795+05:30
ARC1 started with pid=40, OS id=17726
Starting background process ARC2
2026-03-09T19:25:58.350561+05:30
ARC2 started with pid=7, OS id=17728
Starting background process ARC3
2026-03-09T19:25:58.363836+05:30
ARC3 started with pid=34, OS id=17730
TMON (PID:17642): ARC1: Archival started
TMON (PID:17642): ARC2: Archival started
TMON (PID:17642): ARC3: Archival started
TMON (PID:17642): STARTING ARCH PROCESSES COMPLETE
2026-03-09T19:25:59.662018+05:30
ALTER SYSTEM SET standby_file_management=’MANUAL’ SCOPE=BOTH;
2026-03-09T19:26:00.814962+05:30
rfs (PID:17761): Primary database is in MAXIMUM PERFORMANCE mode
2026-03-09T19:26:00.863959+05:30
rfs (PID:17761): Selected LNO:4 for T-1.S-67 dbid 608515774 branch 1227333258
2026-03-09T19:26:04.946760+05:30
Incremental restore complete of datafile 1 /u01/app/oracle/oradata/STDBY/system01.dbf
checkpoint is 2450561
last deallocation scn is 2038884
2026-03-09T19:26:10.282142+05:30
Incremental restore complete of datafile 3 /u01/app/oracle/oradata/STDBY/sysaux01.dbf
checkpoint is 2450583
last deallocation scn is 2318321
2026-03-09T19:26:15.309304+05:30
Incremental restore complete of datafile 4 /u01/app/oracle/oradata/STDBY/undotbs01.dbf
checkpoint is 2450597
last deallocation scn is 2335433
Incremental restore complete of datafile 7 /u01/app/oracle/oradata/STDBY/users01.dbf
checkpoint is 2450599
last deallocation scn is 3
2026-03-09T19:26:16.507605+05:30
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover datafile list
1 , 3 , 4 , 7
Completed: alter database recover datafile list
1 , 3 , 4 , 7
alter database recover
if needed standby start
2026-03-09T19:26:16.512534+05:30
Media Recovery Start
2026-03-09T19:26:16.512829+05:30
Serial Media Recovery started
…. (PID:17740): Managed Standby Recovery not using Real Time Apply
ORA-279 signalled during: alter database recover
if needed standby start
…
2026-03-09T19:26:16.828306+05:30
ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=BOTH;
stopping change tracking
4. Start the managed recovery process on Standby database.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
5. Check the whether archivelog gap is filled or not.
On Primary:
=========
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/arch/prod
Oldest online log sequence 67
Next log sequence to archive 69
Current log sequence 69
On Standby:
=========
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”
FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#; 2 3 4
Thread Last Sequence Received Last Sequence Applied Difference
———- ———————- ——————— ———-
1 68 68 0
Now, the archive log gap is filled and applying the new archive logs.
Disclaimer:
Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment.
