Express guide to create a Data Guard Configuration with a Far Sync Instance

Oracle Community

Express guide to create a Data Guard Configuration with a Far Sync Instance

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.

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.

For this express guide I will use 3 servers:

  1. db12102 - Primary Database
  2. db12102s - Standby Database
  3. db12103f - Far Sync Instance

NOTE: The 3 servers have the directory structure.

There are 2 prerequisites that we have to complete before to start to have fun, you have to remember these prerequisites because if you start building the Data Guard configuration it's so likely that you will forget these basic things, you will have issues in the configuration, you will lose a lot of time, some times you will make it more complex instead of fix the issues, and so on. So always remember this tip that I call "basic things for DG":

  1. Static Services ending with "_DGMGLR"
  2. tnsnames well configured with entries for 3 instances.

I won't show you how to configure static services, instead of that I will show you how the services look in my servers.

TIP: listener.ora is very sensitive to spaces, special characters, etc. Don't make it complex, use "netmgr" to create these services.

NOTE: Don't forget reboot the listener after create these services.

[oracle@db12102 ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-DEC-2015 17:41:22

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "cdb1_DGMGRL" has 1 instance(s).
Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER

[oracle@db12102f ~]$ lsnrctl service

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-DEC-2015 12:32:32

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "cdb1f_DGMGRL" has 1 instance(s).
Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
[oracle@db12102f ~]$

[oracle@db12102s ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-DEC-2015 12:32:47

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "cdb1s_DGMGRL" has 1 instance(s).
Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
[oracle@db12102s ~]$

Now the second basic thing for DG is a well configured tnsnames, again I won't show you how to do this since it is not complex, you can do it manually by using "vi", remember to put in "SERVICE_NAME" the exact name that you specified earlier in "first basic thing for DG", anyways I have used colors to make it easier for you (green primary, blue standby, purple far sync), so whenever you see the color you will know which instance/DG role we are talking about. I have the following lines in my three tnsnames.ora files. Write it in one file and copy it and paste it in the other two.

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

CDB1S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db12102s)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1s_DGMGRL)
)
)

CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db12102)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1_DGMGRL)
)
)

CDB1F =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db12102f)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1f_DGMGRL)
)
)

So here is where the fun starts. We have spent around 15 minutes configuring the static services and tnsnames, so we don't have time to prepare our cup of coffee, unfortunately. Let's continue with next steps and let's complete this DG as fast as we can :)

Our Express Guide has the following steps:

  • First Step - Building the Standby Database.
  • Second Step - Create our Far Sync Instance
  • Third Step - Adding Standby Logfiles in all the Databases:
  • Fourth Step - Creating our Data Guard configuration with far sync instance:
  • Fifth Step - Configure Routes

First Step - Building the Standby Database.

In primary side:

Create a spfile, this spfile will be transfered to the other 2 servers:

TIP:  Try to use always spfile. This save a lot of time when you have to change parameters.

SQL> create spfile='/home/oracle/spfile.ora' from memory;

File created.

Transfering the server parameter file to the others 2 servers:

[oracle@db12102 ~]$ scp /home/oracle/spfile.ora db12102s:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/
oracle@db12102s's password:
spfile.ora 100% 1363 1.3KB/s 00:00
[oracle@db12102 ~]$ scp /home/oracle/spfile.ora db12102f:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/
oracle@db12102f's password:
spfile.ora 100% 1363 1.3KB/s 00:00

Let's take advantage that we are logged in into primary server and let's transfer also the password file (if you don't have any, add a couple of minutes and create one):

[oracle@db12102 ~]$ scp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwcdb1 db12102f:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/
oracle@db12102f's password:
orapwcdb1 100% 7680 7.5KB/s 00:00
[oracle@db12102 ~]$ scp /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwcdb1 db12102s:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/
oracle@db12102s's password:
orapwcdb1 100% 7680 7.5KB/s 00:00
[oracle@db12102 ~]$

In "db12102s" as oracle user:

Set the environment variables:

[oracle@db12102s ~]$ env|grep ORA
ORACLE_SID=cdb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@db12102s ~]$

Creating the instance:

[oracle@db12102s ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Dec 27 14:48:59 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size 2923680 bytes
Variable Size 452985696 bytes
Database Buffers 704643072 bytes
Redo Buffers 13852672 bytes
SQL> alter system set db_unique_name='cdb1s' scope=spfile;

System altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size 2923680 bytes
Variable Size 452985696 bytes
Database Buffers 704643072 bytes
Redo Buffers 13852672 bytes
SQL>

NOTE: We had to restart the instance in order to change the only parameter that we need to change "db_unique_name".

If listener services, /etc/hosts and tnsnames.ora files are well configured you won't have any issue in the next command:

[oracle@db12102s ~]$ rman target sys/****@CDB1 auxiliary sys/****@CDB1S

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Dec 27 14:49:51 2015

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

connected to target database: CDB1 (DBID=870988040)
connected to auxiliary database: CDB1 (not mounted)

Once you are connected to RMAN, let's raise the duplicate:

RMAN> DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;

When the duplicate has completed, we will see our standby database mounted:

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

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
CDB1      MOUNTED              PHYSICAL STANDBY

 

Second Step - Create our Far Sync Instance

We have to create a controlfile in primary database that will be used to create our far sync instance, so as oracle user connected to "cdb1" database:

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

Database altered.

Transfering the controlfile to db12102f:

[oracle@db12102 ~]$ scp /home/oracle/controlFarSync.ctl db12102f:/home/oracle
oracle@db12102f's password:
controlFarSync.ctl 100% 17MB 17.4MB/s 00:01
[oracle@db12102 ~]$

As oracle user in db12102f: Creating the far sync instance:

Set the environment variables:

[oracle@db12102f ~]$ env|grep ORA
ORACLE_SID=cdb1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@db12102s ~]$

Creating the far sync instance:

[oracle@db12102f ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Dec 28 05:58:02 2015

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 1174405120 bytes

Fixed Size 2923680 bytes
Variable Size 452985696 bytes
Database Buffers 704643072 bytes
Redo Buffers 13852672 bytes

RMAN> alter system set db_unique_name='cdb1f' scope=spfile;

Statement processed

RMAN> shutdown abort;

Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 1174405120 bytes

Fixed Size 2923680 bytes
Variable Size 452985696 bytes
Database Buffers 704643072 bytes
Redo Buffers 13852672 bytes

RMAN>

NOTE: We changed the only parameter we have to modify, db_unique_name.

Restoring the far sync controlfile:

RMAN> restore controlfile from '/home/oracle/controlFarSync.ctl';

Starting restore at 27-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/CDB1F/CONTROLFILE/current.263.899565679
Finished restore at 27-DEC-15

Mounting our far sync instance:

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

 

Third Step - Adding Standby Logfiles in all the Databases:

The recommendation is to add the standby logfiles with the same size than redo log files, and add 1 extra standby logfile, so let's see how many groups we have to redo and what is the size of those groups.

As oracle user connected to "cdb1" primary database:

SQL> select g.group#, g.bytes/1024/1024 MB, l.member from v$log g, v$logfile l where g.group#=l.group#

GROUP#     MB         MEMBER
---------- ---------- --------------------------------------------------
3          50         +DATA/CDB1/ONLINELOG/group_3.268.897270601
2          50         +DATA/CDB1/ONLINELOG/group_2.269.897270601
1          50         +DATA/CDB1/ONLINELOG/group_1.277.897270601

Adding 4 standby logfiles groups with a size of 50M. We have to add these Standby log Groups to the 3 dateabases (cdb1, cdb1s, cdb1f):

ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 4 ('+DATA') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 5 ('+DATA') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 6 ('+DATA') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE thread 1 GROUP 7 ('+DATA') SIZE 50M;

TIP: I strongly recommend to put "THREAD" clause, because there is a bug related with Broker in 12c: Warning: standby redo logs not configured for thread <n> on <db_unique_name> (Doc ID 1956103.1)

Verify the standby logs groups added:

SQL> select g.group#, g.bytes/1024/1024 MB, l.member from v$standby_log g, v$logfile l where g.group#=l.group#;

GROUP#     MB         MEMBER
---------- ---------- --------------------------------------------------
4          50         +DATA/CDB1/ONLINELOG/group_4.271.899420145
5          50         +DATA/CDB1/ONLINELOG/group_5.274.899420157
6          50         +DATA/CDB1/ONLINELOG/group_6.261.899571435
7          50         +DATA/CDB1/ONLINELOG/group_7.258.899571443

 

Fourth Step - Creating our Data Guard configuration with far sync instance:

Let's connect to Primary broker:

[oracle@db12102s ~]$ dgmgrl sys/***@CDB1
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.

Adding our Primary database:

DGMGRL> CREATE CONFIGURATION 'oraworld' AS PRIMARY DATABASE IS 'cdb1' CONNECT IDENTIFIER IS 'CDB1';
Configuration "oraworld" created with primary database "cdb1"

TIP: The first value is "db_unique_name" and the second value is the tnsnames entry.

Adding our Standby database:

DGMGRL> ADD DATABASE 'cdb1s' AS CONNECT IDENTIFIER IS 'CDB1S';
Database "cdb1s" added

TIP: The first value is "db_unique_name" and the second value is the tnsnames entry.

Enabling our Configuration:

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - oraworld

Protection Mode: MaxPerformance
Members:
cdb1 - Primary database
cdb1s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 3 seconds ago)

Adding our far sync instance:

DGMGRL> ADD FAR_SYNC 'cdb1f' AS CONNECT IDENTIFIER IS 'CDB1F';
far sync instance "cdb1f" added

TIP: The first value is "db_unique_name" and the second value is the tnsnames entry.

Enabling our far sync instance:

DGMGRL> ENABLE FAR_SYNC 'cdb1f';
Enabled.
DGMGRL> show configuration;

Configuration - oraworld

Protection Mode: MaxPerformance
Members:
cdb1 - Primary database
cdb1s - Physical standby database
cdb1f - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 53 seconds ago)

 

Fifth Step - Configure Routes:

Creating the redo routes:

"Redo Routes" is kind of a new concept in 12c and it what it says, we can create routes to send redo data. Before 12c, we had only 1 route (Primary -> Standby), well you could have (Primary -> Standby -> Standby) but that is a topic for other day with a beer in the hand (as my friend Anibal says). Starting with 12c, we can have the following Routes:

Primary |-> Far Sync -> Standby
       *|-> Standby

Primary |-> Far Sync 1 -> Standby
       *|-> Far Sync 2 -> Standby

You can play with it. There are another new concept as well "Alternate Destination", you can configure a second destination that will be used when the first and main destination is broken ( *| ).

So the routes that I will configure are the following:

Primary (cdb1) -> Far Sync (cdb1f) -> Standby (cdb1s)
Primary (cdb1s) -> Far Sync (cdb1f) -> Standby (cdb1)

Adding the routes:

DGMGRL> EDIT DATABASE 'cdb1' SET PROPERTY RedoRoutes='(LOCAL:cdb1f SYNC)';
Property "redoroutes" updated
DGMGRL> EDIT DATABASE 'cdb1s' SET PROPERTY RedoRoutes='(LOCAL:cdb1f SYNC)';
Property "redoroutes" updated
DGMGRL> EDIT FAR_SYNC 'cdb1f' SET PROPERTY RedoRoutes='(cdb1:cdb1s)(cdb1s:cdb1)';
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

DGMGRL> show configuration;

Configuration - oraworld

Protection Mode: MaxAvailability
Members:
cdb1 - Primary database
cdb1f - Far sync instance
cdb1s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 4 seconds ago)

What about if 'cdb1s' becomes Primary?

DGMGRL> SHOW CONFIGURATION WHEN PRIMARY IS 'cdb1s' ;

Configuration when cdb1s is primary - oraworld

Members:
cdb1s - Primary database
cdb1f - Far sync instance
cdb1 - Physical standby database

Congratulations! If you reached this step you have your Data Guard Configuration with a Far Sync instance.

Follow me:

      

2201 2 /
Follow / 18 Jan 2016 at 10:44am

Great Artical :)

Follow / 8 Feb 2016 at 4:32am

Thank you @hitgon!