Step by Step Configure Oracle 12c Data Guard Far Sync Standby Database

Description:-

  • In this article we are going to see Step by Step Configure Oracle 12c Data Guard Far Sync Standby Database
  • An Oracle Data Guard far sync instance is a remote Oracle Data Guard destination that accepts redo from the primary database and then ships that redo to other members of the Oracle Data Guard configuration.
  • A far sync instance manages a control file, receives redo into standby redo logs (SRLs), and archives those SRLs to local archived redo logs, but that is where the similarity with standbys ends. A far sync instance does not have user data files, cannot be opened for access, cannot run redo apply, and can never function in the primary role or be converted to any type of standby database.
  • Far sync instances are part of the Oracle Active Data Guard Far Sync feature, which requires an Oracle Active Data Guard license.
  • Creating a far sync instance close to the primary has the benefit of minimizing impact on commit response times to an acceptable threshold (due to the smaller network latency between primary and far sync instance) while allowing for higher data protection guarantees — if the primary were to fail, and assuming the far sync instance was synchronized at the time of the failure, the far sync instance and the terminal standby would 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.
  • Summary:-
    • A light-weight/remote standby instance
    • It contains instance, standby controlfile and set of redo logs only Not datafile
    • Far sync standby database consumes minimal resources over the server (CPU, Memory, I/O etc)
    • Overcome network latency issues while shipping redo synchronously
      Guarantees zero data loss capabilities
    • Keep it close to the primary site, 40-150 miles
    • Far sync instance doesn’t have any physical structure
    • Multiple Far sync instance can be configured for active/passive purpose
    • Supports a maximum of 29 remote standby databases

Far Sync Standby Architecture:-

High Level Steps:-

  1. Adding TNS Entry and listener
  2. Creating the Standby Database
  3. create Far Sync Instance
  4. Adding Standby Logfiles in all the Databases
  5. Creating our Data Guard configuration with far sync instance

Let’s start the demo:-

Environment Details:-

Adding TNS entry (check all the nodes)

[oracle@prod1 admin]$ tnsping oradbwrp

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 20:53:25
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbwr)))
OK (10 msec)

[oracle@prod1 admin]$ tnsping oradbwrs

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 20:53:28
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.26)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbwrs)))
OK (10 msec)

[oracle@prod1 admin]$ tnsping oradbwrf

TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 17-JUL-2018 20:53:31
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.25)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradbwrf)))
OK (10 msec)

Adding  Listener (check all the nodes) Mark Red colour (DG Broker) 

[oracle@prod1 dbs]$ lsnrctl start

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 19-JUL-2018 04:18:57
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/prod1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 19-JUL-2018 04:18:58
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/prod1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.24)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "oradbwr" has 1 instance(s).
Instance "oradbwr", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwr_DGMGRL" has 1 instance(s).
Instance "oradbwr", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrf" has 1 instance(s).
Instance "oradbwrf", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrf_DGMGRL" has 1 instance(s).
Instance "oradbwrf", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrs" has 1 instance(s).
Instance "oradbwrs", status UNKNOWN, has 1 handler(s) for this service...
Service "oradbwrs_DGMGRL" has 1 instance(s).
Instance "oradbwrs", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Enabling archivelog & flashback

[oracle@prod1 ~]$ export ORACLE_SID=oradbwr
[oracle@prod1 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 17 21:27:26 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> startup mount
ORACLE instance started.

Total System Global Area 935329792 bytes
Fixed Size 8627344 bytes
Variable Size 343935856 bytes
Database Buffers 578813952 bytes
Redo Buffers 3952640 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> alter database force logging;
Database altered.

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

Total System Global Area 935329792 bytes
Fixed Size 8627344 bytes
Variable Size 343935856 bytes
Database Buffers 578813952 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.

SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/oradbwr/redo04.log' size 50m;
Database altered.

SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/oradbwr/redo05.log' size 50m;
Database altered.

SQL>alter database add standby logfile group 6 '/u01/app/oracle/oradata/oradbwr/redo06.log' size 50m; 
Database altered.

Changing parameters in primary database

SQL> ALTER SYSTEM SET db_unique_name='oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_config='dg_config=(oradbwr,oradbwrs)' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_2='service=stand async valid_for=(online_logfiles,primary_role) db_unique_name=oradbwrs' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET remote_login_passwordfile='EXCLUSIVE' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET fal_server='oradbwrs' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET fal_client='oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_archive_format='oradbwr_%t_%s_%r.arc' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/oradbwrs','/u01/app/oracle/oradata/oradbwr' SCOPE=SPFILE;
System altered.

SQL> ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/oradbwrs','/u01/app/oracle/oradata/oradbwr' SCOPE=SPFILE;
System altered.

Standby database side

Step:1 Password file creation

copy the remote login password file (orapworadbwr) from the primary database server to the $ORACLE_HOME/dbs directory on the
standby database server, renaming it to orapworadbwrs.

Step:2 Changing parameters in standby database

In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initstand.ora
Containing a single parameter: DB_NAME=oradbwrs

Step:3 Create directory Structure in Standby database

cd $ORACLE_BASE/admin/
mkdir oradbwrs
cd oradbwrs
mkdir adump pfile dpdump
mkdir -p /u01/app/oracle/oradata/oradbwrs

Step:4 start the standby database using pfile

startup nomount pfile=$ORACLE_HOME/dbs/initoradbwrs.ora

Step:5 connect to the rman

[oracle@prod3 ~]$ rman target sys/oracle@oradbwrp auxiliary sys/oracle@oradbwrs

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jul 18 23:34:41 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORADBWR (DBID=1408230468)
connected to auxiliary database: ORADBWR (not mounted)

RMAN> run
{
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate channel p4 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'oradbwr','oradbwrs'
set db_name='oradbwr'
set db_unique_name='oradbwrs' 
set db_file_name_convert='/u01/app/oracle/oradata/oradbwr','/u01/app/oracle/oradata/oradbwrs'
set log_file_name_convert='/u01/app/oracle/oradata/oradbwr','/u01/app/oracle/oradata/oradbwrs'
set control_files='/u01/app/oracle/oradata/oradbwrs/standby1.ctl'
set log_archive_max_processes='5'
set fal_client='oradbwrs'
set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/oradbwrs'
set db_recovery_file_dest_size='8000m'
set fal_server='oradbwr'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(oradbwrs,oradbwr)'
set compatible='12.2.0.1.0'
set sga_target='1500m'
nofilenamecheck;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=75 device type=DISK
allocated channel: p2
channel p2: SID=76 device type=DISK
allocated channel: p3
channel p3: SID=71 device type=DISK
allocated channel: p4
channel p4: SID=63 device type=DISK
allocated channel: s1
channel s1: SID=36 device type=DISK
Starting Duplicate Db at 18-JUL-18
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.2.0.1/db_1/dbs/orapworadbwr' auxiliary format 
'/u01/app/oracle/product/12.2.0.1/db_1/dbs/orapworadbwrs' targetfile 
'/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwr.ora' auxiliary format 
'/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwrs.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwrs.ora''";
}
executing Memory Script
Starting backup at 18-JUL-18
Finished backup at 18-JUL-18
sql statement: alter system set spfile= ''/u01/app/oracle/product/12.2.0.1/db_1/dbs/spfileoradbwrs.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest = 
''/u01/app/oracle/admin/oradbwrs/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers = 
''(PROTOCOL=TCP) (SERVICE=oradbwrsXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 = 
''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 = 
''service=oradbwrs async valid_for=(online_logfiles,primary_role) db_unique_name=oradbwrss'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_format = 
''oradbwrs_%t_%s_%r.arc'' comment=
'''' scope=spfile";
sql clone "alter system set db_name = 
''oradbwr'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name = 
''oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert = 
''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert = 
''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set control_files = 
''/u01/app/oracle/oradata/oradbwrs/standby1.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes = 
5 comment=
'''' scope=spfile";
sql clone "alter system set fal_client = 
''oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest = 
''/u01/app/oracle/fast_recovery_area/oradbwrs'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest_size = 
8000m comment=
'''' scope=spfile";
sql clone "alter system set fal_server = 
''oradbwr'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management = 
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config = 
''dg_config=(oradbwrs,oradbwr)'' comment=
'''' scope=spfile";
sql clone "alter system set compatible = 
''12.2.0.1.0'' comment=
'''' scope=spfile";
sql clone "alter system set sga_target = 
1500m comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/oradbwrs/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=oradbwrsXDB)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=stand async valid_for=(online_logfiles,primary_role) db_unique_name=oradbwrss'' comment= '''' scope=spfile
sql statement: alter system set log_archive_format = ''oradbwrs_%t_%s_%r.arc'' comment= '''' scope=spfile
sql statement: alter system set db_name = ''oradbwr'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/oradbwr'', ''/u01/app/oracle/oradata/oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u01/app/oracle/oradata/oradbwrs/standby1.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''/u01/app/oracle/fast_recovery_area/oradbwrs'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest_size = 8000m comment= '''' scope=spfile
sql statement: alter system set fal_server = ''oradbwr'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(oradbwrs,oradbwr)'' comment= '''' scope=spfile
sql statement: alter system set compatible = ''12.2.0.1.0'' comment= '''' scope=spfile
sql statement: alter system set sga_target = 1500m comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1577058304 bytes
Fixed Size 8621136 bytes
Variable Size 503317424 bytes
Database Buffers 1056964608 bytes
Redo Buffers 8155136 bytes
allocated channel: s1
channel s1: SID=34 device type=DISK
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/oradbwrs/standby1.ctl';
}
executing Memory Script
Starting backup at 18-JUL-18
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/12.2.0.1/db_1/dbs/snapcf_oradbwr.f tag=TAG20180718T233622
channel p1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 18-JUL-18
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to 
"/u01/app/oracle/oradata/oradbwrs/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to 
"/u01/app/oracle/oradata/oradbwrs/system01.dbf";
set newname for datafile 3 to 
"/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf";
set newname for datafile 4 to 
"/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf";
set newname for datafile 7 to 
"/u01/app/oracle/oradata/oradbwrs/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/system01.dbf" datafile 
3 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf" datafile 
4 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf" datafile 
7 auxiliary format 
"/u01/app/oracle/oradata/oradbwrs/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/oradbwrs/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 18-JUL-18
channel p1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/oradbwr/system01.dbf
channel p2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/oradbwr/sysaux01.dbf
channel p3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/oradbwr/undotbs01.dbf
channel p4: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/oradbwr/users01.dbf
output file name=/u01/app/oracle/oradata/oradbwrs/users01.dbf tag=TAG20180718T233628
channel p4: datafile copy complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf tag=TAG20180718T233628
channel p3: datafile copy complete, elapsed time: 00:00:07
output file name=/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf tag=TAG20180718T233628
channel p2: datafile copy complete, elapsed time: 00:01:15
output file name=/u01/app/oracle/oradata/oradbwrs/system01.dbf tag=TAG20180718T233628
channel p1: datafile copy complete, elapsed time: 00:01:25
Finished backup at 18-JUL-18
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=981848275 file name=/u01/app/oracle/oradata/oradbwrs/users01.dbf
Finished Duplicate Db at 18-JUL-18
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1

Step6 :- Post check status for Standby database,

SQL> select name,database_role,open_mode from v$database;

NAME               DATABASE_ROLE                           OPEN_MODE
——— —————- ——————–
ORADBWR             PHYSICAL STANDBY                        MOUNTED

Step 7:- Enable the recovery:

SQL> alter database recover managed standby database disconnect from session;

Step 8:- Check the Standby database sync status with primary:

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# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1                 24                   24                 0

Deploying Far Sync instance

Step1:-We need to create a controlfile in primary database that will be used to create far sync instance

SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/home/oracle/stbyfarsync.ctl';
Database altered.

Step2:-Copy the far sync controlfile & passwordfile

Copy the far sync controlfile & passwordfile
[oracle@prod1 ~]$ scp stbyfarsync.ctl oracle@192.168.1.25:/u01/app/oracle/oradata/oradbwrf
oracle@192.168.1.25's password: 
stbyfarsync.ctl 100% 10MB 10.1MB/s 00:00 
[oracle@prod1 ~]$ cd $ORACLE_HOME/dbs
[oracle@prod1 dbs]$ scp orapworadbwr oracle@192.168.1.25:$ORACLE_HOME/dbs
oracle@192.168.1.25's password: 
orapworadbwr

step3:-Farsync instance Pfile

audit_file_dest='/u01/app/oracle/oradbwrf/adump'
audit_trail='db'
compatible='12.2.0.1.0'
control_files='/u01/app/oracle/oradata/oradbwrf/stbyfarsync.ctl'
db_block_size=8192
db_domain=''
db_name='oradbwr'
db_unique_name='oradbwrf'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/oradbwrf'
db_recovery_file_dest_size=4800m
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=oradbwrfXDB)'
log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=oradbwrf'
log_archive_dest_2='service=oradbwrs ASYNC valid_for=(standby_logfiles,standby_role) db_unique_name=oradbwrs'
log_archive_config='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)'
open_cursors=300
pga_aggregate_target=300m
processes=300
remote_login_passwordfile='EXCLUSIVE'
sga_target=1300m
undo_tablespace='UNDOTBS1'
fal_server='oradbwr'
db_file_name_convert='/u01/app/oracle/oradata/oradbwr/','/u01/app/oracle/oradata/oradbwrf/'
log_file_name_convert='/u01/app/oracle/oradata/oradbwr/','/u01/app/oracle/oradata/oradbwrf/'

Step4:- startup the Far sync instance and check

[oracle@prod2 ~]$ sqlplus ‘/as sysdba’;

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 19 03:54:43 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 1375731712 bytes
Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

SQL> alter database mount;
Database altered.

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
FAR SYNC

SQL> select DB_UNIQUE_NAME,DEST_ROLE from V$DATAGUARD_CONFIG;

DB_UNIQUE_NAME                    DEST_ROLE
------------------------------ -----------------
oradbwrf                        FAR SYNC INSTANCE
oradbwrs                        PHYSICAL STANDBY
oradbwr                         PRIMARY DATABASE

Data Guard configuration with far sync instance

Parameters change 

Primary:-
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradbwr' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=oradbwrf SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradbwrf' scope=both;

Far sync Standby:-
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ordbwrf' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ordbwrs ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=ordbwrs' scope=both;

Standby:-
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(oradbwr,oradbwrf,oradbwrs)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=oradbwrs' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=oradbwr ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=oradbwr' scope=both;
[oracle@prod1 admin]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Thu Jul 19 06:26:35 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@oradbwrp
Connected to "oradbwr"
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION 'chennai' AS PRIMARY DATABASE IS 'oradbwr' CONNECT IDENTIFIER IS 'oradbwr';
Configuration "chennai" created with primary database "oradbwr"
DGMGRL> ADD DATABASE 'oradbwrs' AS CONNECT IDENTIFIER IS 'oradbwrs' maintained as physical;
Database "oradbwrs" added
DGMGRL> ADD FAR_SYNC 'oradbwrf' AS CONNECT IDENTIFIER IS 'oradbwrf';
far sync instance "oradbwrf" added
DGMGRL> show configuration;

Configuration - chennai

Protection Mode: MaxAvailability
Members:
oradbwr - Primary database
oradbwrs - Physical standby database 
oradbwrf - Far sync instance 
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED

Check the Standby database sync status with primary:

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# ORDER BY 1;

Thread Last Sequence Received Last Sequence Applied     Difference
---------- ---------------------- --------------------- ----------
1                 37                     37                0

Successfully configured Far sync standby database.

Reference:-

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sbydb/creating-oracle-data-guard-far-sync-instance.html#GUID-8AD7FBA2-42B0-46CF-852B-1AF0CB4A36E8

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter:  https://twitter.com/hariprasathdba

Leave a Reply

Your email address will not be published. Required fields are marked *