- 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:-
- Adding TNS Entry and listener
- Creating the Standby Database
- create Far Sync Instance
- Adding Standby Logfiles in all the Databases
- 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:-
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