1. Introduction
In mission-critical database environments, preventing data loss during unexpected failures is a major challenge. Even when a Primary database and Standby database are synchronized, there is still a potential risk of losing unarchived redo data present in the current redo log if the Primary database crashes abruptly.
To address this problem, Oracle Data Guard provides the FAR Sync Instance feature, which enables organizations to achieve Zero Data Loss protection while minimizing the performance impact on the Primary database.
A FAR Sync instance acts as an intermediary between the Primary and Standby databases. It receives redo data synchronously from the Primary database and then forwards it asynchronously to the remote Standby database. This architecture ensures that the most recent transactions are preserved even if the Primary database fails unexpectedly.
This document demonstrates how to configure a FAR Sync instance and perform a failover scenario to verify that no data loss occurs, even when the Primary database terminates abruptly.
2. Scenario Description:
Suppose, your primary and standby databases are in sync without any issue. If primary goes down or terminated abruptly then how to avoid zero data loss. Your standby database can be in near distance or in far distance which
Suppose, your primary and standby databases are in sync without any issue. If primary goes down or terminated abruptly then how to avoid zero data loss. Your standby database can be in near distance or in far distance which is in hundreds or thousands of miles or in any geographical area. There can be data loss due to network latency issues between primary and standby databases. Using SYNC redo transport mode to achieve zero data loss may not be a feasible option due to network latency between primary and standby databases because of the impact on the commit response times at the primary site. Here, the FAR Sync instance concept comes where FAR Sync instance can be created at Primary Site in SYNC mode where Primary is located (Refer below diagram) so that logs will get shipped immediately to FAR sync Instance and then that destination can be further cascaded to actual standby database(DR) from FAR Sync DB server. i.e. Primary---> FAR Sync ---> Standby
How the above configuration will work if Primary gets terminated or not available?
Let's say If the primary database has failed and assume that the far sync instance was synchronized at the time of the primary failure, then the FAR sync instance and the actual standby instance will coordinate a final redo shipment from the FAR sync instance to the standby to ship any redo not yet available to the standby and then perform a zero-data-loss failover. This enables zero data loss failover for all cases, except for a disaster outage of both the primary and the Far Sync instance at the same time.
3. Architecture Overview
The FAR Sync architecture introduces an additional lightweight instance between the Primary and Standby databases.
Key Components
Primary Database (PR)
The main production database that generates redo data.
FAR Sync Instance (FSYNC)
A lightweight Oracle instance that receives redo synchronously from the Primary database and immediately forwards it to the Standby database.
Physical Standby Database (DR)
A disaster recovery database that applies redo received through the FAR Sync instance.
Redo Transport Flow
Primary Database → FAR Sync Instance → Standby Database
-
The Primary database sends redo data synchronously to the FAR Sync instance.
-
FAR Sync immediately acknowledges receipt to the Primary database.
-
FAR Sync then forwards the redo data asynchronously to the remote Standby database.
-
The Standby database applies the redo logs through Managed Recovery Process (MRP).
This design allows the Primary database to maintain low commit latency while still achieving Zero Data Loss protection.
4. Overview of FAR Sync Implementation
The implementation involves configuring a dedicated FAR Sync instance and integrating it into the existing Data Guard configuration.
The overall process includes:
-
Verifying the existing Data Guard configuration
-
Ensure Primary and Standby databases are synchronized.
-
Confirm redo logs are being applied successfully.
-
-
Creating and configuring the FAR Sync instance
-
Prepare initialization parameters.
-
Configure listener and TNS entries.
-
Create FAR Sync control file from the Primary database.
-
-
Starting the FAR Sync instance
-
Start the instance in NOMOUNT mode.
-
Restore the FAR Sync control file.
-
Mount the database.
-
-
Configuring Standby Redo Logs
-
Drop existing invalid SRLs.
-
Create new SRLs for the FAR Sync instance.
-
-
Reconfiguring redo transport
-
Modify archive destinations on Primary, Standby, and FAR Sync.
-
Establish redo flow through FAR Sync.
-
-
Validating redo transport
-
Force log switches.
-
Verify redo arrival on FAR Sync and Standby databases.
-
-
Simulating Primary failure
-
Terminate the Primary database.
-
Observe that redo still exists in FAR Sync.
-
-
Performing failover
-
Execute failover on the Standby database.
-
Confirm that the most recent transactions are available.
-
This process confirms that even transactions present in the current redo log can be recovered without data loss.
5. Implementation
Step 1 : Consider you have already configured Primary and Standby DB servers. Ensure your Primary and Standby databases are in sync and logs are getting applied from Primary to Standby without any issue.
On Primary:
SQL> set lines 300 pages 3000
SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL
----- -------------- ---------- ------------- -------------------- -------
PROD PROD READ WRITE PRIMARY MAXIMUM PERFORMANCE CURRENT
16:27:07
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
275
On Standby:
SQL> set lines 300 pages 3000
SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL
---- -------------- --------- ---------------- ------------------- -------
PROD PRODSTBY MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE STANDBY
16:27:20
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
275
Step 2: Create and configure 3rd DB server as FAR Sync instance.
1) Refer below pfile for FAR Sync instance located in "D:\KHAN\Setups\WINDOWS.X64_193000_db_home\database\INITFARSYNC.ORA"
*._readable_standby_sync_timeout=60
*.audit_file_dest='D:\KHAN\Setups\FARSYNC\adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='D:\KHAN\Setups\FARSYNC\DB\FSYNC\control01.ctl','D:\KHAN\Setups\FARSYNC\DB\FSYNC\control02.ctl'
*.db_block_size=8192
*.db_keep_cache_size=218103808
*.db_name='PROD'
*.db_unique_name='FSYNC'
*.diagnostic_dest='D:\KHAN\app\rdbms'
*.log_archive_dest_1='LOCATION=D:\KHAN\Setups\FARSYNC\ARCH'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_limit=3221225472
*.pga_aggregate_target=1073741824
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2048m
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='AUTO'
*.log_archive_config='dg_config=(PROD,PRODSTBY,FSYNC)'
2) Copy existing database password file and rename as D:\KHAN\Setups\WINDOWS.X64_193000_db_home\database\PWDFSYNC.ora
3) Add below entries in listener.ora and tnsnames.ora file.
D:\KHAN\Setups\WINDOWS.X64_193000_db_home\network\admin\listener.ora
LISTENER_FSYNC =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC4))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.100.1)(PORT = 1533))
)
)
SID_LIST_LISTENER_FSYNC =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = D:\KHAN\Setups\WINDOWS.X64_193000_db_home)
(SID_NAME = FSYNC)
)
)
D:\KHAN\Setups\WINDOWS.X64_193000_db_home\network\admin\tnsnames.ora
FSYNC =
(DESCRIPTION =
(SDU = 65535)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.100.1)(PORT = 1533))
)
(CONNECT_DATA =
(SERVICE_NAME = FSYNC)
)
)
C:\windows\system32> lsnrctl start LISTENER_FSYNC
Test the connection by tnsping command.
C:\windows\system32>tnsping fsync
TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 22-MAY-2023 16:25:26
Copyright (c) 1997, 2022, Oracle. All rights reserved.
Used parameter files:
D:\KHAN\Setups\WINDOWS.X64_193000_db_home\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (SDU = 65535) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.172.100.1)(PORT = 1533))) (CONNECT_DATA = (SERVICE_NAME = FSYNC)))
OK (20 msec)
4) First create service using below command since this is Windows server.
C:\windows\system32>oradim -NEW -SID FSYNC -syspwd sys123 -STARTMODE auto -PFILE D:\KHAN\Setups\WINDOWS.X64_193000_db_home\database\INITFSYNC.ORA
OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters.
Instance created.
5) Execute below command on Primary database to set protection mode to maximum availability.
SQL> ALTER DATABASE SET STANDBY TO MAXIMIZE AVAILABILITY;
Database altered.
SQL> set lines 300 pages 3000
SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL
---- -------------- ---------- ------------- -------------------- -------
PROD PROD READ WRITE PRIMARY MAXIMUM AVAILABILITY CURRENT
6) Now create FAR Sync control file for FAR Sync instance. Execute below command in Primary database.
SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS 'D:\KHAN\Setups\FARSYNC\DB\FSYNC\far_sync.ctl';
Database altered.
When you execute "CREATE FAR SYNC" command then you will see below messages in Primary DB alert log file.
Primary DB Alert Log:
2023-05-22T16:37:27.810845+05:30
ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS 'D:\KHAN\Setups\FARSYNC\DB\FSYNC\far_sync.ctl'
2023-05-22T16:37:27.907335+05:30
Clearing standby activation ID 2831561964 (0xa8c634ec)
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;
Completed: ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS 'D:\KHAN\Setups\FARSYNC\DB\FSYNC\far_sync.ctl'
7) Now set the environment variables on FAR Sync Server and start the instance in nomount stage.
C:\windows\system32>set ORACLE_SID=FSYNC
C:\windows\system32>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 22 16:46:30 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> select name,open_mode from v$database;
select name,open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> shut abort
ORACLE instance shut down.
SQL>
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2147482432 bytes
Fixed Size 9030464 bytes
Variable Size 452984832 bytes
Database Buffers 1677721600 bytes
Redo Buffers 7745536 bytes
SQL> show parameter db_name
NAME TYPE VALUE
----------- ------- ------
db_name string PROD
SQL> show parameter db_unique_name
NAME TYPE VALUE
---------------- ------- ------
db_unique_name string FSYNC
8) Restore FAR Sync controlfile located in "D:\RUPESH\Setups\FARSYNC\DB\FSYNC\" directory.
C:\windows\system32>rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Mon May 22 16:50:04 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle and/or its affiliates. All rights reserved.
connected to target database: PR (not mounted)
RMAN> restore farsync controlfile from 'D:\KHAN\Setups\FARSYNC\DB\FSYNC\far_sync.ctl';
Starting restore at 22-MAY-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=195 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=D:\KHAN\SETUPS\FARSYNC\DB\FSYNC\CONTROL01.CTL
output file name=D:\KHAN\SETUPS\FARSYNC\DB\FSYNC\CONTROL02.CTL
Finished restore at 22-MAY-23
RMAN>
Now mount the instance. From RMAN prompt also, you can mount the instance.
RMAN> alter database mount;
released channel: ORA_DISK_1
Statement processed
RMAN>
9) You can verify the database role and control file as FAR SYNC.
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL
----- -------------- --------- ------------- -------------------- -------
PROD FSYNC MOUNTED FAR SYNC MAXIMUM AVAILABILITY FARSYNC
10) Now you have to drop and recreate standby redo log files since the FAR Sync instance has primary SRL's entry which are not physically available.
SQL> col member for a56
SQL> set lines 300 pages 3000
SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile;
GROUP# STATUS TYPE MEMBER
------ ------- ------- -------------------------------------------
3 DELETED ONLINE D:\KHAN\SETUPS\PRIMARY\DB\PR\REDO03.LOG
2 DELETED ONLINE D:\KHAN\SETUPS\PRIMARY\DB\PR\REDO02.LOG
1 DELETED ONLINE D:\KHAN\SETUPS\PRIMARY\DB\PR\REDO01.LOG
4 STANDBY D:\KHAN\SETUPS\PRIMARY\SRLS\PRIMARY01.LOG
5 STANDBY D:\KHAN\SETUPS\PRIMARY\SRLS\PRIMARY02.LOG
6 STANDBY D:\KHAN\SETUPS\PRIMARY\SRLS\PRIMARY03.LOG
7 STANDBY D:\KHAN\SETUPS\PRIMARY\SRLS\PRIMARY04.LOG
SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE,USED,ARCHIVED,STATUS from v$standby_log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS
------ ------- ---------- ---------- ---------- ---- --- ----------
4 0 0 209715200 512 0 YES UNASSIGNED
5 0 0 209715200 512 0 YES UNASSIGNED
6 1 0 209715200 512 0 YES UNASSIGNED
7 1 0 209715200 512 0 YES UNASSIGNED
SQL> alter database drop logfile group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 7;
Database altered.
SQL> alter database add standby logfile group 4 'D:\KHAN\Setups\FARSYNC\SRLs\fsyncredo01.log' size 200m;
Database altered.
SQL> alter database add standby logfile group 5 'D:\KHAN\Setups\FARSYNC\SRLs\fsyncredo02.log' size 200m;
Database altered.
SQL> alter database add standby logfile group 6 'D:\KHAN\Setups\FARSYNC\SRLs\fsyncredo03.log' size 200m;
Database altered.
SQL> alter database add standby logfile group 7 'D:\KHAN\Setups\FARSYNC\SRLs\fsyncredo04.log' size 200m;
Database altered.
SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile;
GROUP# STATUS TYPE MEMBER
------ ------- ------- ---------------------------------------------
4 STANDBY D:\KHAN\SETUPS\FARSYNC\SRLS\FSYNCREDO01.LOG
5 STANDBY D:\KHAN\SETUPS\FARSYNC\SRLS\FSYNCREDO02.LOG
6 STANDBY D:\KHAN\SETUPS\FARSYNC\SRLS\FSYNCREDO03.LOG
7 STANDBY D:\KHAN\SETUPS\FARSYNC\SRLS\FSYNCREDO04.LOG
Step 3: Now check the existing destination parameters in Primary and Standby databases. In your existing configuration, on Primary, dest_id 2 is set for standby(DR) database.
On Primary:
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------- ----------- ------------------------------
log_archive_dest_2 string service=PROD LGWR ASYNC valid_fo
r=(all_logfiles,all_roles) db_
unique_name=PRODSTBY
SQL> show parameter log_archive_dest_3
NAME TYPE VALUE
------------------- ----------- ------------------------------
log_archive_dest_3 string service=FSYNC LGWR SYNC AFFIRM v
alid_for=(all_logfiles,all_rol
es) db_unique_name=FSYNC
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------- ----------- ---------
log_archive_dest_state_2 string ENABLE
SQL> show parameter log_archive_dest_state_3
NAME TYPE VALUE
------------------------- ----------- ----------
log_archive_dest_state_3 string ENABLE
On Standby:
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
--------------------- ----------- -------------------
log_archive_dest_2 string
SQL> show parameter log_archive_dest_3
NAME TYPE VALUE
--------------------- ----------- -------------------
log_archive_dest_3 string
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
-------------------------- ----------- --------------
log_archive_dest_state_2 string enable
SQL> show parameter log_archive_dest_state_3
NAME TYPE VALUE
-------------------------- ----------- --------------
log_archive_dest_state_3 string enable
Step 4: Reset the destination parameters on Primary, Standby, and FAR SYNC instances as below.
Existing: PR ----> DR
New: PR ----> FAR SYNC ----> DR
No need to reset on Primary, you can just defer the destination for standby(DR).
On Primary:
SQL> alter system set log_archive_config='dg_config=(PROD,PRODSTBY,FSYNC)';
System altered.
SQL> alter system set log_archive_dest_3='service=FSYNC LGWR SYNC AFFIRM valid_for=(all_logfiles,all_roles) db_unique_name=FSYNC';
System altered.
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL> alter system set log_archive_dest_state_3=enable;
System altered.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
-------------------- ----------- ------------------------------
log_archive_dest_2 string service=PROD LGWR ASYNC valid_fo
r=(all_logfiles,all_roles) db_
unique_name=PRODSTBY
SQL> show parameter log_archive_dest_3
NAME TYPE VALUE
-------------------- ----------- ------------------------------
log_archive_dest_3 string service=MIN LGWR SYNC AFFIRM v
alid_for=(all_logfiles,all_rol
es) db_unique_name=MIN
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
-------------------------- ----------- ------
log_archive_dest_state_2 string DEFER
SQL> show parameter log_archive_dest_state_3
NAME TYPE VALUE
-------------------------- ----------- ------
log_archive_dest_state_3 string ENABLE
SQL> select dest_id,error from v$archive_dest where dest_id in(2,3);
DEST_ID ERROR
---------- ---------------
2
3
On Standby:
SQL> alter system set log_archive_config='dg_config=(PROD,PRODSTBY,FSYNC)';
System altered.
SQL> alter system set log_archive_dest_3='service=FSYNC LGWR ASYNC valid_for=(online_logfile,primary_role) db_unique_name=FSYNC';
System altered.
SQL> alter system set log_archive_dest_state_3=defer;
System altered.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
-------------------- ----------- ------
log_archive_dest_2 string
SQL> show parameter log_archive_dest_3
NAME TYPE VALUE
---------------------- ----------- ------------------------------
log_archive_dest_3 string service=FSYNC LGWR ASYNC valid
_for=(online_logfile,primary_r
ole) db_unique_name=FSYNC
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
-------------------------- ----------- -------
log_archive_dest_state_2 string enable
SQL> show parameter log_archive_dest_state_3
NAME TYPE VALUE
-------------------------- ----------- -------
log_archive_dest_state_3 string DEFER
SQL> show parameter log_archive_config
NAME TYPE VALUE
----------------------- ----------- ------------------------
log_archive_config string dg_config=(PROD,PRODSTBY,FSYNC)
On FAR Sync Instance:
SQL> alter system set log_archive_config='dg_config=(PROD,PRODSTBY,FSYNC)';
System altered.
1SQL> alter system set log_archive_dest_2='service=PROD LGWR ASYNC valid_for=(all_logfiles,all_roles) db_unique_name=PRODSTBY';
System altered.
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
-------------------- ----------- ------------------------------
log_archive_dest_2 string service=PROD LGWR ASYNC valid_fo
r=(all_logfiles,all_roles) db_
unique_name=PRODSTBY
SQL> show parameter log_archive_dest_3
NAME TYPE VALUE
-------------------- ----------- ------
log_archive_dest_3 string
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------- ----------- -------
log_archive_dest_state_2 string ENABLE
SQL> show parameter log_archive_dest_state_3
NAME TYPE VALUE
--------------------------- ----------- -------
log_archive_dest_state_3 string enable
SQL> show parameter log_archive_config
NAME TYPE VALUE
----------------------- ----------- ------------------------
log_archive_config string dg_config=(PROD,PRODSTBY,FSYNC)
Step 5: Ensure your SRLs(Standby Redo Log Files) are getting updated on both FAR Sync and Standby database(DR). Execute below commands to verify the same.
On Primary:
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
17:31:52 SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
300
17:37:34 SQL> set lines 300 pages 3000
17:37:34 SQL> col member for a56
17:37:34 SQL> select a.GROUP#, A.BYTES/1024/1024 SIZE_MB, b.MEMBER,a.status,a.sequence# from v$log a,v$logfile b where a.group#=b.group#;
GROUP# SIZE_MB MEMBER STATUS SEQUENCE#
------ ------- ----------------------------------------- -------- ---------
3 200 D:\KHAN\SETUPS\PRIMARY\DB\PR\REDO03.LOG CURRENT 301
2 200 D:\KHAN\SETUPS\PRIMARY\DB\PR\REDO02.LOG INACTIVE 300
1 200 D:\KHAN\SETUPS\PRIMARY\DB\PR\REDO01.LOG INACTIVE 299
On Standby:
17:33:04 SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
300
17:35:15 SQL> select a.GROUP#,a.status,a.sequence# from v$standby_log a;
GROUP# STATUS SEQUENCE#
---------- ---------- ----------
4 ACTIVE 301
5 UNASSIGNED 0
6 UNASSIGNED 0
7 UNASSIGNED 0
17:39:43 SQL> set lines 300 pages 3000
17:39:51 SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE,USED,ARCHIVED,STATUS from v$standby_log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS
------ ------- --------- ---------- ---------- ------- --- ----------
4 1 301 209715200 512 3296768 YES ACTIVE
5 1 0 209715200 512 0 NO UNASSIGNED
6 0 0 209715200 512 0 YES UNASSIGNED
7 0 0 209715200 512 0 YES UNASSIGNED
On FAR Sync:
17:38:33 SQL> select a.GROUP#,a.status,a.sequence# from v$standby_log a;
GROUP# STATUS SEQUENCE#
---------- ---------- ----------
4 UNASSIGNED 0
5 ACTIVE 301
6 UNASSIGNED 0
7 UNASSIGNED 0
17:39:10 SQL> set lines 300 pages 3000
17:39:14 SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,BLOCKSIZE,USED,ARCHIVED,STATUS from v$standby_log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARC STATUS
------ ------- --------- --------- --------- ------- --- ----------
4 1 0 209715200 512 0 NO UNASSIGNED
5 1 301 209715200 512 3278336 YES ACTIVE
6 0 0 209715200 512 0 NO UNASSIGNED
7 0 0 209715200 512 0 NO UNASSIGNED
Here, you can see the last generated archive log sequence number is 300 which is reached to FAR Sync and applied on Standby. Now the next sequence number is 301 which has to be archived on Primary which has not yet been generated. The same sequence number is active on Standby and FAR Sync.
Note: Do not execute "alter system switch logfile" command on Primary since we are going to perform scenario test.
Step 6: Now start the scenario as below.
On Primary:
17:42:47 SQL> create table employee(emp_id number(5),emp_name varchar2(12));
Table created.
17:42:49 SQL> insert into employee values(1,'ABC');
1 row created.
17:42:49 SQL> insert into employee values(2,'PQR');
1 row created.
17:42:49 SQL> insert into employee values(3,'XYZ');
1 row created.
17:42:49 SQL> select * from employee;
EMP_ID EMP_NAME
---------- ------------
1 ABC
2 PQR
3 XYZ
17:42:49 SQL> commit;
Commit complete.
You can again check the last generated archive log sequence.
17:42:50 SQL> select a.GROUP#, A.BYTES/1024/1024 SIZE_MB, b.MEMBER,a.status,a.sequence# from v$log a,v$logfile b where a.group#=b.group#;
GROUP# SIZE_MB MEMBER STATUS SEQUENCE#
------ ------- ----------------------------------------- -------- ---------
3 200 D:\KHAN\SETUPS\PRIMARY\DB\PR\REDO03.LOG CURRENT 301
2 200 D:\KHAN\SETUPS\PRIMARY\DB\PR\REDO02.LOG INACTIVE 300
1 200 D:\KHAN\SETUPS\PRIMARY\DB\PR\REDO01.LOG INACTIVE 299
17:43:17 SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
300
Here, I am terminating the instance as testing purpose. In real scenario, your primary instance will not be accessible which is similar to our case i.e. instance termination.
17:43:24 SQL> shut abort
ORACLE instance shut down.
17:44:02 SQL>
On Standby:
17:44:37 SQL> select a.GROUP#,a.status,a.sequence# from v$standby_log a;
GROUP# STATUS SEQUENCE#
---------- ---------- ----------
4 ACTIVE 301
5 UNASSIGNED 0
6 UNASSIGNED 0
7 UNASSIGNED 0
17:44:37 SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
300
On FAR Sync:
17:45:05 SQL> select a.GROUP#,a.status,a.sequence# from v$standby_log a;
GROUP# STATUS SEQUENCE#
---------- ---------- ----------
4 UNASSIGNED 0
5 ACTIVE 301
6 UNASSIGNED 0
7 UNASSIGNED 0
Step 7: Ensure destination is enabled in FAR Sync instance which points to standby database(DR) and also, the destination in standby(DR) database is set for FAR SYNC instance and can be DEFERRED.
On FAR Sync:
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
-------------------- ----------- ------------------------------
log_archive_dest_2 string service=PROD LGWR ASYNC valid_fo
r=(all_logfiles,all_roles) db_
unique_name=PRODSTBY
SQL> show parameter log_archive_dest_state_3
NAME TYPE VALUE
--------------------------- ----------- -------
log_archive_dest_state_3 string enable
On Standby:
SQL> show parameter log_archive_dest_3
NAME TYPE VALUE
---------------------- ----------- ------------------------------
log_archive_dest_3 string service=FSYNC LGWR ASYNC valid
_for=(online_logfile,primary_r
ole) db_unique_name=FSYNC
SQL> show parameter log_archive_dest_state_3
NAME TYPE VALUE
-------------------------- ----------- -------
log_archive_dest_state_3 string DEFER
Step 8: Now you are ready to perform failover command in Standby database in just one command. Execute below command in Standby Database(DR).
On Standby:
17:53:12 SQL> alter database failover to PRODSTBY;
Database altered.
Elapsed: 00:00:04.98
SQL> set lines 300 pages 3000
SQL> col DB_UNIQUE_NAME for a16
SQL> col OPEN_MODE for a14
SQL> col name for a8
SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL
---- -------------- --------- ------------- -------------------- -------
PROD PRODSTBY MOUNTED PRIMARY MAXIMUM PERFORMANCE CURRENT
17:56:41 SQL> alter database open;
Database altered.
SQL> select name,db_unique_name,open_mode,database_role,protection_mode,controlfile_type from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE CONTROL
---- -------------- ---------- ------------- -------------------- -------
PROD PROD READ WRITE PRIMARY MAXIMUM PERFORMANCE CURRENT
SQL> select * from employee;
EMP_ID EMP_NAME
---------- ------------
1 ABC
2 PQR
3 XYZ
Now you can see the the table created in Primary database which was in the current log sequence number 301 and which was not generated i.e. it was in current state in Primary database which is now available in standby database(DR) after activating standby database.
Standby Database Alert Log:
2023-05-22T17:53:27.617506+05:30
alter database failover to PRODSTBY
2023-05-22T17:53:27.622808+05:30
.... (PID:18180): The Time Management Interface (TMI) is being enabled for role transition
.... (PID:18180): information. This will result in messages beingoutput to the alert log
.... (PID:18180): file with the prefix 'TMI: '. This is being enabled to make the timing of
.... (PID:18180): the various stages of the role transition available for diagnostic purposes.
.... (PID:18180): This output will end when the role transition is complete.
TMI: dbsdrv failover to target BEGIN 2023-05-22 17:53:27.622808
Terminal Recovery requested in process 18180
TMI: adbdrv termRecovery BEGIN 2023-05-22 17:53:27.642552
.... (PID:18180): Terminal Recovery: Stopping real time apply
2023-05-22T17:53:27.658386+05:30
PR00 (PID:3532): MRP0: Background Media Recovery cancelled with status 16037
2023-05-22T17:53:27.658386+05:30
Errors in file D:\KHAN\APP\RGHUBADE\diag\rdbms\dr\dr\trace\dr_pr00_3532.trc:
ORA-16037: user requested cancel of managed recovery operation
PR00 (PID:3532): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 6948582
Stopping change tracking
2023-05-22T17:53:27.851378+05:30
Errors in file D:\KHAN\APP\RGHUBADE\diag\rdbms\dr\dr\trace\dr_pr00_3532.trc:
ORA-16037: user requested cancel of managed recovery operation
2023-05-22T17:53:28.662718+05:30
.... (PID:18180): Terminal Recovery: Stopped real time apply
TMI: adbdrv termRecovery END 2023-05-22 17:53:28.662718
2023-05-22T17:53:28.680863+05:30
Serial Media Recovery started
NET (PID:18180): Managed Standby Recovery not using Real Time Apply
Stopping change tracking
NET (PID:18180): Media Recovery Waiting for T-1.S-301 (in transit)
2023-05-22T17:53:30.783791+05:30
NET (PID:18180): Killing 1 processes (PIDS:13920) (all RFS, wait for I/O) in order to disallow current and future RFS connections. Requested by OS process 18180
2023-05-22T17:53:32.018801+05:30
NET (PID:18180): Begin: SRL archival
NET (PID:18180): End: SRL archival
NET (PID:18180): Terminal Recovery timestamp is '05/22/2023 17:53:32'
NET (PID:18180): Terminal Recovery: applying standby redo logs.
NET (PID:18180): Terminal Recovery: thread 1 seq# 301 redo required
2023-05-22T17:53:32.064685+05:30
NET (PID:18180): Terminal Recovery:
2023-05-22T17:53:32.064685+05:30
Recovery of Online Redo Log: Thread 1 Group 4 Seq 301 Reading mem 0
Mem# 0: D:\KHAN\SETUPS\STANDBY\SRLS\STANDBY01.LOG
Terminal Recovery finished with No-Data-Loss
2023-05-22T17:53:32.097036+05:30
Incomplete Recovery applied until change 6948583 time 05/22/2023 17:43:44
2023-05-22T17:53:32.192280+05:30
NET (PID:18180): Terminal Recovery enabled LAD:3
Terminal Recovery: successful completion
NET (PID:18180): Forcing ARSCN to IRSCN for TR SCN:0x00000000006a06e7
NET (PID:18180): Attempt to set limbo arscn SCN:0x00000000006a06e7 irscn SCN:0x00000000006a06e7
NET (PID:18180): Resetting standby activation ID 2831561964 (0xa8c634ec)
Stopping change tracking
Maximum wait for role transition is 15 minutes.
TMI: kcv_commit_to_so_to_primary wait for MRP to finish BEGIN 2023-05-22 17:53:32.272787
TMI: kcv_commit_to_so_to_primary wait for MRP to finish END 2023-05-22 17:53:32.272787
TMI: kcv_commit_to_so_to_primary Switchover from physical BEGIN 2023-05-22 17:53:32.272787
Backup controlfile written to trace file D:\KHAN\APP\RGHUBADE\diag\rdbms\dr\dr\trace\dr_ora_18180.trc
Standby terminal recovery start SCN: 6948582
RESETLOGS after incomplete recovery UNTIL CHANGE 6948583 time 05/22/2023 17:43:44
NET (PID:18180): ORL pre-clearing operation disabled by switchover
Online log D:\KHAN\SETUPS\STANDBY\DB\DR\REDO01.LOG: Thread 1 Group 1 was previously cleared
Online log D:\KHAN\SETUPS\STANDBY\DB\DR\REDO02.LOG: Thread 1 Group 2 was previously cleared
Online log D:\KHAN\SETUPS\STANDBY\DB\DR\REDO03.LOG: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 6948581
2023-05-22T17:53:32.480368+05:30
Setting recovery target incarnation to 3
2023-05-22T17:53:32.496402+05:30
NET (PID:18180): RT: Role transition work is not done
NET (PID:18180): The Time Management Interface (TMI) is being enabled for role transition
NET (PID:18180): information. This will result in messages beingoutput to the alert log
NET (PID:18180): file with the prefix 'TMI: '. This is being enabled to make the timing of
NET (PID:18180): the various stages of the role transition available for diagnostic purposes.
NET (PID:18180): This output will end when the role transition is complete.
NET (PID:18180): Redo network throttle feature is disabled at mount time
2023-05-22T17:53:32.567967+05:30
NET (PID:18180): Database role cleared from PHYSICAL STANDBY [kcvs.c:1114]
Switchover: Complete - Database mounted as primary
TMI: kcv_commit_to_so_to_primary Switchover from physical END 2023-05-22 17:53:32.567967
TMI: dbsdrv failover to target END 2023-05-22 17:53:32.567967
Failover completed with No-Data-Loss.
Completed: alter database failover to DR
2023-05-22T17:54:02.352171+05:30
2023-05-22T17:58:24.015551+05:30
alter database open
2023-05-22T17:58:24.015551+05:30
TMI: adbdrv open database BEGIN 2023-05-22 17:58:24.015551
Smart fusion block transfer is disabled:
instance mounted in exclusive mode.
Endian type of dictionary set to little
2023-05-22T17:58:24.110772+05:30
Assigning activation ID 2832039740 (0xa8cd7f3c)
2023-05-22T17:58:24.142819+05:30
Thread 1 advanced to log sequence 2 (thread open)
Redo log for group 2, sequence 2 is not located on DAX storage
Thread 1 opened at log sequence 2
Current log# 2 seq# 2 mem# 0: D:\RUPESH\SETUPS\STANDBY\DB\DR\REDO02.LOG
Successful open of redo thread 1
2023-05-22T17:58:24.178583+05:30
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Stopping change tracking
2023-05-22T17:58:24.186262+05:30
ARC1 (PID:6100): LGWR is scheduled to archive to LAD:3 after log switch
2023-05-22T17:58:24.340046+05:30
Undo initialization recovery: Parallel FPTR failed: start:72736812 end:72736812 diff:0 ms (0.0 seconds)
Undo initialization recovery: err:0 start: 72736812 end: 72736843 diff: 31 ms (0.0 seconds)
2023-05-22T17:58:24.392500+05:30
ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=MEMORY SID='*';
[18180] Successfully onlined Undo Tablespace 2.
Undo initialization online undo segments: err:0 start: 72736843 end: 72736890 diff: 47 ms (0.0 seconds)
2023-05-22T17:58:24.405314+05:30
ARC1 (PID:6100): Archived Log entry 52 added for T-1.S-1 ID 0xa8cd7f3c LAD:1
2023-05-22T17:58:24.411083+05:30
Undo initialization finished serial:0 start:72736812 end:72736890 diff:78 ms (0.1 seconds)
Dictionary check beginning
Dictionary check complete
Database Characterset is AL32UTF8
2023-05-22T17:58:24.447158+05:30
Starting background process SMCO
2023-05-22T17:58:24.447401+05:30
SMCO started with pid=39, OS id=6124
No Resource Manager plan active
NET (PID:18180): Enable RFS client [kcrlc.c:622]
2023-05-22T17:58:25.061081+05:30
joxcsys_required_dirobj_exists: directory object exists with required path D:\RUPESH\SETUPS\WINDOWS.X64_193000_DB_HOME\JAVAVM\ADMIN\, pid 6880 cid 0
replication_dependency_tracking turned off (no async multimaster replication found)
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Starting background process AQPC
2023-05-22T17:58:25.359229+05:30
AQPC started with pid=42, OS id=17152
2023-05-22T17:58:25.559203+05:30
TMI: adbdrv open database END 2023-05-22 17:58:25.553533
Starting background process CJQ0
2023-05-22T17:58:25.594442+05:30
CJQ0 started with pid=45, OS id=18396
2023-05-22T17:58:26.595832+05:30
Completed: alter database open
2023-05-22T17:58:26.602169+05:30
You have successfully performed zero data loss recovery.
Note: Now your current Primary database will be located on DR server. You will have question in your mind that what about my primary database server which was terminated. Once the issue is resolved at Primary site then you can perform below steps to again go live the Primary DB server.
1) Drop Primary database located at Primary site.
2) Take backup of newly activated Primary database(current PR) to configure the DR.
3) Restore the backup on Primary Site.
4) Configure the DR database.
5) Enable SYNC between new Primary and new DR.
6) Initiate switchover from new Primary to new DR
7) Go live Primary Site
8) Application sanity checks.
6. Conclusion
The FAR Sync instance provides a highly effective solution for achieving Zero Data Loss protection in Oracle Data Guard environments without negatively impacting Primary database performance.
By introducing a lightweight intermediate instance at the Primary site, organizations can ensure that redo data is captured immediately and safely transmitted to remote standby systems.
This architecture is particularly valuable in geographically distributed environments where network latency makes synchronous redo transport directly to the Standby database impractical.
Through the steps demonstrated in this document, we verified that even when the Primary database terminates unexpectedly, the Standby database can successfully recover the latest transactions using redo preserved in the FAR Sync instance.
7. Risks and Mitigation
1. FAR Sync Instance Failure
Risk: If both the Primary database and FAR Sync instance fail simultaneously, zero data loss protection cannot be guaranteed.
Mitigation:
Deploy redundant FAR Sync instances or maintain high availability at the Primary site.
2. Network Latency Between FAR Sync and Standby
Risk: High network latency can delay redo delivery to the Standby database.
Mitigation:
Ensure reliable network connectivity and sufficient bandwidth between FAR Sync and the Standby site.
3. Misconfigured Redo Transport Parameters
Risk: Incorrect archive destination parameters can interrupt redo flow.
Mitigation:
Validate log_archive_dest parameters and test redo shipping after configuration changes.
4. Insufficient Standby Redo Logs
Risk: Missing or incorrectly sized SRLs can affect redo transport.
Mitigation:
Create sufficient SRLs matching Primary redo log size.