In this article we will see setting up a far sync configuration in your Dr Environment, We will also see how to integrate it with the broker but before the practical setup we will understand the concept of FAR SYNC INSTANCE.
In previous releases before 12C, the problem in sync configuration of primary to standby was that if geographical distance was very huge commit latency was affected for the primary because sync means transaction is not complete till the time standby acknowledges the redo change vector. Wouldnt it be great if primary database ships the redo to a dummy instance which is close to it geographically which means it can very well work in sync mode with it with a very good commit latency and later that dummy instance would transfer the redo changes to other standby databases in ASYNC mode without affecting the transaction commit latency for the primary database. Well yes it happens in Oracle Database 12C with a thing known as FAR SYNC INSTANCE.
FAR SYNC is a new enhancement in Oracle 12C Data guard, A Data Guard far sync instance is a destination geographically close to the primary which will accept redo from primary database and then ship it to the other standby terminals. This far sync instance is basically a light weight instance which just has a controlfile , standby redo logs and it archives the standby redo logfiles to local archive redo logs. This type of instance does not have any user datafiles so it means it cannot be opened for read access and it cannot run redo apply and also it can never function in primary role and also it cannot be converted to any type of standby. Its function is to receive redo quickly from primary in sync mode so that transaction on primary commits quickly and later if geographical distance is big between far sync and standby terminals it can keep up with those standby terminals later easily in asynch mode without affecting primary database commit latency.
Setting up a far sync configuration in your Dr Environment, We will also see how to integrate it with the broker.
## In our configuration we have a primary database (PROD1) and 1 physical standby (DR1) and we will see how to add a far sync PRODFS in this configuraton
## In the previous article on this blog of mine i have shown the setup of Data guard for primary PROD1 and standby DR1
## lets create a far sync instance named as prodfs
## create far sync controlfile from primary database prod1
SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/u01/app/oracle/oradata/prodfs/control01.ctl';
## Making changes in spfile of Primary database PROD1
Make the following changes in spfile
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod1,prodfs,dr1)'
LOG_ARCHIVE_DEST_2='SERVICE=prodfs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prodfs'
## Making changes in spfile of FAR SYNC (PRODFS)
Make the following changes in spfile
DB_UNIQUE_NAME=prodfs
CONTROL_FILES='/u01/app/oracle/oradata/prodfs/control01.ctl'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod1','/u01/app/oracle/oradata/prodfs'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod1','/u01/app/oracle/oradata/prodfs'
FAL_SERVER=prod1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod1,prodfs,dr1)'
LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prodfs'
LOG_ARCHIVE_DEST_2='SERVICE=dr1 ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=dr1'
## Making changes in spfile of PHYSICAL STANDBY (DR1)
Make the following changes in spfile
## Below are the commands shown practically this time
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod1,prodfs,dr1)' ;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=prod1 SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod1' ;
System altered.
SQL> alter system set fal_server = 'prodfs','prod' ;
System altered.
## Lets login to far sync instance
[oracle@node4 ~]$ export ORACLE_SID=prodfs
[oracle@node4 ~]$
[oracle@node4 ~]$
[oracle@node4 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Oct 2 17:25:55 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/u01/initprodfs.ora' ;
File created.
SQL> startup mount
ORACLE instance started.
Total System Global Area 617975808 bytes
Fixed Size 2291376 bytes
Variable Size 276826448 bytes
Database Buffers 335544320 bytes
Redo Buffers 3313664 bytes
Database mounted.
## We are almost done
_____________________________________________________________
Broker setup for far sync standby
export ORACLE_SID=prod1
[oracle@node4 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production
Copyright (c) 2000, 2012, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected as SYSDG.
DGMGRL>
DGMGRL> create configuration conf1 as primary database is prod1 connect identifier is prod1 ;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
Failed.
Fixing it on primary database
SQL> alter system set log_archive_dest_2='' scope=both;
System altered.
DGMGRL> create configuration conf1 as primary database is 'prod1' connect identifier is prod1 ;
Configuration "conf1" created with primary database "prod1"
DGMGRL>
DGMGRL>
DGMGRL> add database 'dr1' as connect identifier is dr1 maintained as physical ;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
Failed.
## Fixing this on our standby terminal
SQL> alter system set log_archive_dest_2='' scope=both;
System altered.
DGMGRL> add database 'dr1' as connect identifier is dr1 maintained as physical ;
Database "dr1" added
DGMGRL> enable configuration ;
Enabled.
DGMGRL>
DGMGRL> ADD FAR_SYNC 'prodfs' AS CONNECT IDENTIFIER IS prodfs;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
Failed.
## fixing this on far sync instance
SQL> alter system set log_archive_dest_2='' scope=both;
System altered.
DGMGRL> ADD FAR_SYNC 'prodfs' AS CONNECT IDENTIFIER IS prodfs;
far sync instance "prodfs" added
DGMGRL> ENABLE FAR_SYNC 'prodfs';
Enabled.
DGMGRL> SHOW CONFIGURATION;
Configuration - conf1
Protection Mode: MaxPerformance
Databases:
prod1 - Primary database
dr1 - Physical standby database
prodfs - Far Sync (inactive)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
## Far sync still shows inactive, to enable this we need to do the following
DGMGRL> EDIT DATABASE 'prod1' SET PROPERTY RedoRoutes='(LOCAL : PRODFS SYNC)';
Property "redoroutes" updated
DGMGRL> EDIT FAR_SYNC 'prodfs' SET PROPERTY RedoRoutes='(PROD1 : DR1)' ;
Property "redoroutes" updated
DGMGRL> SHOW CONFIGURATION;
Configuration - conf1
Protection Mode: MaxPerformance
Databases:
prod1 - Primary database
prodfs - Far Sync
dr1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.
DGMGRL> show configuration
Configuration - conf1
Protection Mode: MaxAvailability
Databases:
prod1 - Primary database
prodfs - Far Sync
dr1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
______________________________________________
In previous releases before 12C, the problem in sync configuration of primary to standby was that if geographical distance was very huge commit latency was affected for the primary because sync means transaction is not complete till the time standby acknowledges the redo change vector. Wouldnt it be great if primary database ships the redo to a dummy instance which is close to it geographically which means it can very well work in sync mode with it with a very good commit latency and later that dummy instance would transfer the redo changes to other standby databases in ASYNC mode without affecting the transaction commit latency for the primary database. Well yes it happens in Oracle Database 12C with a thing known as FAR SYNC INSTANCE.
FAR SYNC is a new enhancement in Oracle 12C Data guard, A Data Guard far sync instance is a destination geographically close to the primary which will accept redo from primary database and then ship it to the other standby terminals. This far sync instance is basically a light weight instance which just has a controlfile , standby redo logs and it archives the standby redo logfiles to local archive redo logs. This type of instance does not have any user datafiles so it means it cannot be opened for read access and it cannot run redo apply and also it can never function in primary role and also it cannot be converted to any type of standby. Its function is to receive redo quickly from primary in sync mode so that transaction on primary commits quickly and later if geographical distance is big between far sync and standby terminals it can keep up with those standby terminals later easily in asynch mode without affecting primary database commit latency.
Setting up a far sync configuration in your Dr Environment, We will also see how to integrate it with the broker.
## In our configuration we have a primary database (PROD1) and 1 physical standby (DR1) and we will see how to add a far sync PRODFS in this configuraton
## In the previous article on this blog of mine i have shown the setup of Data guard for primary PROD1 and standby DR1
## lets create a far sync instance named as prodfs
## create far sync controlfile from primary database prod1
SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/u01/app/oracle/oradata/prodfs/control01.ctl';
## Making changes in spfile of Primary database PROD1
Make the following changes in spfile
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod1,prodfs,dr1)'
LOG_ARCHIVE_DEST_2='SERVICE=prodfs SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prodfs'
## Making changes in spfile of FAR SYNC (PRODFS)
Make the following changes in spfile
DB_UNIQUE_NAME=prodfs
CONTROL_FILES='/u01/app/oracle/oradata/prodfs/control01.ctl'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod1','/u01/app/oracle/oradata/prodfs'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/prod1','/u01/app/oracle/oradata/prodfs'
FAL_SERVER=prod1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod1,prodfs,dr1)'
LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prodfs'
LOG_ARCHIVE_DEST_2='SERVICE=dr1 ASYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=dr1'
## Making changes in spfile of PHYSICAL STANDBY (DR1)
Make the following changes in spfile
## Below are the commands shown practically this time
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod1,prodfs,dr1)' ;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=prod1 SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod1' ;
System altered.
SQL> alter system set fal_server = 'prodfs','prod' ;
System altered.
## Lets login to far sync instance
[oracle@node4 ~]$ export ORACLE_SID=prodfs
[oracle@node4 ~]$
[oracle@node4 ~]$
[oracle@node4 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Oct 2 17:25:55 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/u01/initprodfs.ora' ;
File created.
SQL> startup mount
ORACLE instance started.
Total System Global Area 617975808 bytes
Fixed Size 2291376 bytes
Variable Size 276826448 bytes
Database Buffers 335544320 bytes
Redo Buffers 3313664 bytes
Database mounted.
## We are almost done
_____________________________________________________________
Broker setup for far sync standby
export ORACLE_SID=prod1
[oracle@node4 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production
Copyright (c) 2000, 2012, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected as SYSDG.
DGMGRL>
DGMGRL> create configuration conf1 as primary database is prod1 connect identifier is prod1 ;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
Failed.
Fixing it on primary database
SQL> alter system set log_archive_dest_2='' scope=both;
System altered.
DGMGRL> create configuration conf1 as primary database is 'prod1' connect identifier is prod1 ;
Configuration "conf1" created with primary database "prod1"
DGMGRL>
DGMGRL>
DGMGRL> add database 'dr1' as connect identifier is dr1 maintained as physical ;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
Failed.
## Fixing this on our standby terminal
SQL> alter system set log_archive_dest_2='' scope=both;
System altered.
DGMGRL> add database 'dr1' as connect identifier is dr1 maintained as physical ;
Database "dr1" added
DGMGRL> enable configuration ;
Enabled.
DGMGRL>
DGMGRL> ADD FAR_SYNC 'prodfs' AS CONNECT IDENTIFIER IS prodfs;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
Failed.
## fixing this on far sync instance
SQL> alter system set log_archive_dest_2='' scope=both;
System altered.
DGMGRL> ADD FAR_SYNC 'prodfs' AS CONNECT IDENTIFIER IS prodfs;
far sync instance "prodfs" added
DGMGRL> ENABLE FAR_SYNC 'prodfs';
Enabled.
DGMGRL> SHOW CONFIGURATION;
Configuration - conf1
Protection Mode: MaxPerformance
Databases:
prod1 - Primary database
dr1 - Physical standby database
prodfs - Far Sync (inactive)
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
## Far sync still shows inactive, to enable this we need to do the following
DGMGRL> EDIT DATABASE 'prod1' SET PROPERTY RedoRoutes='(LOCAL : PRODFS SYNC)';
Property "redoroutes" updated
DGMGRL> EDIT FAR_SYNC 'prodfs' SET PROPERTY RedoRoutes='(PROD1 : DR1)' ;
Property "redoroutes" updated
DGMGRL> SHOW CONFIGURATION;
Configuration - conf1
Protection Mode: MaxPerformance
Databases:
prod1 - Primary database
prodfs - Far Sync
dr1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.
DGMGRL> show configuration
Configuration - conf1
Protection Mode: MaxAvailability
Databases:
prod1 - Primary database
prodfs - Far Sync
dr1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
______________________________________________
Hi Sir,
ReplyDeletePlease help me to solve this issue. I am configuring the DGMGRL, but im getting the following error:
--primary
SQL> alter system set log_archive_dest_2='' scope=both;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=" " scope=both;
System altered.
DGMGRL> add database orcl_stby as connect identifier is ORCL_STBY;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
Failed.
--STANDBY
INSTANCE_NAME STATUS
---------------- ------------
orcl_stby MOUNTED
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=" " scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=" " scope=both
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16028: new LOG_ARCHIVE_DEST_2 causes less destinations than
LOG_ARCHIVE_MIN_SUCCEED_DEST requires
Please, let me know your comments.
Thanks