Friday 3 October 2014

FAR SYNC in Oracle 12C Database

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

______________________________________________



1 comment:

  1. Hi Sir,

    Please 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

    ReplyDelete