Far Sync in Oracle 12c

Far SYNC is a feature introduced with 12c.

It is the dummy instance that sends the Redos from the Primary Database to other Standby Databases in the Data Guard configuration.

It has only standby control file, password file, standby redo log and archived logs. Since there are no Datafiles on it, there is no Redo Apply process.

By sending redos to the nearby location as SYNC, it guarantees zero data loss. It also preserves the availability of the Primary Database.

After guaranteeing zero data loss, redos can be sent as SYNC or ASYNC to up to 30 locations via    Far SYNC instance.

Its architectural structure is as follows.

As the database will run in Mount mode, you will likely pay license money to Oracle per core.

Redos can be sent to Standby Databases as compressed.

An alternative “Far SYNC” can be used for High Availability.

In Cascade Standby structures in versions prior to 12c, Redos can be sent from Primary to the first Standby as SYNC. However, Redos could not be sent to other Standby Databases as SYNC. 

In Cascade structures in 12c, Redos can be sent as SYNC to Far SYNC instances and Physical Standbys.

With this feature, you can have multiple SYNC Standby Databases.

How To Create Far SYNC Instance?

Step1:

Create PFILE from SPFILE in the primary database.

[Primary] SQL> create pfile='/tmp/initprmyFS.ora' from spfile;

 

File created.

Step2:

Create Control File for Far SYNC instance.

[Primary] SQL> alter database create far sync instance controlfile as '/tmp/prmyFS.ctl';

 

Database altered.

Step3:

Move Control File, PFILE and Password File to Far SYNC instance.

[oracle@primary tmp]$ scp initprmyFS.ora prmyFS:/u01/app/oracle/product/12.1.0/db_1/dbs

oracle@prmyfs's password:

initprmyFS.ora                           100% 1383     1.4KB/s   00:00    

[oracle@primary tmp]$ scp prmyFS.ctl prmyFS:/u01/app/oracle/product/12.1.0/db_1/dbs

oracle@prmyfs's password:

prmyFS.ctl                               100% 9808KB   9.6MB/s   00:00    

[oracle@primary tmp]$ scp /u01/app/oracle/product/12.1.0/db_1/dbs/orapwprimary

prmyFS:/u01/app/oracle/product/12.1.0/db_1/dbs/orapwprmyFS

oracle@prmyfs's password:

orapwprimary                             100% 7680     7.5KB/s   00:00

Step4:

Check if the files go to Far SYNC instance.

[root@prmyFS ~]# su - oracle

[oracle@prmyFS ~]$ cd $ORACLE_HOME/dbs

[oracle@prmyFS dbs]$ ls

init.ora  initprmyFS.ora  orapwprmyFS  prmyFS.ctl

Step5:

Create the necessary directories in the Far SYNC instance.

[oracle@prmyFS dbs]$ mkdir -p /u01/app/oracle/admin/prmyFS/adump

[oracle@prmyFS dbs]$ mkdir -p /u01/app/oracle/oradata/prmyFS

[oracle@prmyFS dbs]$ mkdir -p /u01/app/oracle/recovery_area/prmyFS

Step6:

Edit the PFILE file according to the behavior of the Far SYNC instance.

[oracle@prmyFS dbs]$ vi initprmyFS.ora

Update the PFILE parameter file by running:%s/primary/prmyFS/gcommand as follows.

*.audit_file_dest='/u01/app/oracle/admin/prmyFS/adump'

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='/u01/app/oracle/oradata/prmyFS/control01.ctl',

'/u01/app/oracle/recovery_area/prmyFS/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='prmyFS'

*.db_recovery_file_dest='/u01/app/oracle/recovery_area'

*.db_recovery_file_dest_size=10737418240

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prmyFSXDB)'

*.log_archive_config='dg_config=(prmyFS,prmyFS,physical,physclFS,logical)'

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST

valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=prmyFS'

*.log_archive_dest_2='SERVICE=physical ASYNC REOPEN=15

valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=physical'

*.log_archive_max_processes=8

*.open_cursors=300

*.pga_aggregate_target=1100m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=3302m

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'

After the necessary arrangements in the PFILE parameter file, the final version of the current PFILE file is as follows.

*.audit_file_dest='/u01/app/oracle/admin/prmyFS/adump'

*.audit_trail='db'

*.compatible='12.1.0.2.0'

*.control_files='/u01/app/oracle/oradata/prmyFS/prmyFS01.ctl',

'/u01/app/oracle/recovery_area/prmyFS/prmyFS02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='primary'

*.db_unique_name=prmyFS

*.fal_server=primary

*.log_file_name_convert='primary','prmyFS'

*.db_recovery_file_dest='/u01/app/oracle/recovery_area'

*.db_recovery_file_dest_size=10737418240

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prmyFSXDB)'

*.log_archive_config='dg_config=(primary,prmyFS,physical,physclFS,logical)'

*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST

valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=prmyFS'

*.log_archive_dest_2='SERVICE=physical SYNC REOPEN=15

valid_for=(STANDBY_LOGFILES,STANDBY_ROLE) db_unique_name=physical'

*.log_archive_max_processes=8

*.open_cursors=300

*.pga_aggregate_target=1100m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=3302m

*.standby_file_management='auto'

*.undo_tablespace='UNDOTBS1'

Step7:

Copy the Control File file to the locations specified in the Parameter file.

[oracle@prmyFS dbs]$ cp prmyFS.ctl /u01/app/oracle/oradata/prmyFS/prmyFS01.ctl

[oracle@prmyFS dbs]$ cp prmyFS.ctl /u01/app/oracle/fast_recovery_area/prmyFS/prmyFS02.ctl

[oracle@prmyFS dbs]$ rm -rf prmyFS.ctl

Step8:

Create SPFILE from PFILE on the Far SYNC instance.

[oracle@prmyFS dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 8 15:30:46 2017

 

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

 

Connected to an idle instance.

 

[PrimaryFS] SQL> create spfile from pfile='/u01/app/oracle/product/12.1.0/db_1

/dbs/initprmyFS.ora';

 

File created.

Step9:

Mount the Far SYNC instance.

[PrimaryFS] SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 3472883712 bytes

Fixed Size                  2930272 bytes

Variable Size             822086048 bytes

Database Buffers         2634022912 bytes

Redo Buffers               13844480 bytes

Database mounted.

[PrimaryFS] SQL> select status from v$instance;

 

STATUS

------------

MOUNTED

Step10:

Check the Database Role.

[PrimaryFS] SQL> select database_role, open_mode, protection_mode from v$database;

 

DATABASE_ROLE    OPEN_MODE            PROTECTION_MODE

---------------- -------------------- --------------------

FAR SYNC         MOUNTED              MAXIMUM PERFORMANCE

Step11:

Update the Primary Database’s LOG_ARCHIVE_DEST_2 parameter to send the logs to the Far SYNC instance.

[Primary] SQL> alter system set log_archive_dest_2='SERVICE=prmyFS SYNC REOPEN=15

valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=prmyFS' scope=both;

 

System altered.

Step12:

Check if Archives go to Far SYNC instance by performing Log Switch operation.

[Primary] SQL> alter system switch logfile;

 

System altered.

 

[Primary] SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

 

MAX(SEQUENCE#)    THREAD#

-------------- ----------

            14          1

[PrimaryFS] SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

 

MAX(SEQUENCE#)    THREAD#

-------------- ----------

            14          1

[Physical] SQL> SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;

 

MAX(SEQUENCE#)    THREAD#

-------------- ----------

            14          1

You can see if Standby Redo Logs are created in Far SYNC instance as below.

[oracle@prmyFS dbs]$ ls -la /u01/app/oracle/oradata/prmyFS/stdby*

-rw-r----- 1 oracle oinstall 52429312 Feb  8 15:38

/u01/app/oracle/oradata/prmyFS/stdbyredo01.log

-rw-r----- 1 oracle oinstall 52429312 Feb  8 15:36

/u01/app/oracle/oradata/prmyFS/stdbyredo02.log

-rw-r----- 1 oracle oinstall 52429312 Feb  8 15:36

/u01/app/oracle/oradata/prmyFS/stdbyredo03.log

-rw-r----- 1 oracle oinstall 52429312 Feb  8 15:36

/u01/app/oracle/oradata/prmyFS/stdbyredo04

Step13:

Test it. To do this, create a new table in Primary and see if it is transferred to Physical Standby.

[Primary] SQL> select table_name from dba_tables where owner='TEST';

 

TABLE_NAME

--------------------------------------------------------------------------------

JOBS_YEDEK

EMPLOYEES_YEDEK

[Physical] SQL> select table_name from dba_tables where owner='TEST';

 

TABLE_NAME

--------------------------------------------------------------------------------

JOBS_YEDEK

EMPLOYEES_YEDEK

[Primary] SQL> create table test.regions as select * from hr.regions;

 

Table created.

 

[Primary] SQL> select table_name from dba_tables where owner='TEST';

 

TABLE_NAME

--------------------------------------------------------------------------------

JOBS_YEDEK

EMPLOYEES_YEDEK

REGIONS

[Physical] SQL> select table_name from dba_tables where owner='TEST';

 

TABLE_NAME

--------------------------------------------------------------------------------

JOBS_YEDEK

EMPLOYEES_YEDEK

We saw that the created table was not transferred to Physical Standby. The reason is that the Log Transport attribute in the LOG_ARCHIVE_DEST_2 parameter in the Far SYNC instance is SYNC.

In order for Redos to be transferred in Real-Time, this parameter must be set as ASYNC.

Step14:

Update the LOG_ARCHIVE_DEST_2 parameter in the Far SYNC instance.

[PrimaryFS] SQL> alter system set log_archive_dest_2='SERVICE=physical

ASYNC NOAFFIRM REOPEN=15

valid_for=(STANDBY_LOGFILES,STANDBY_ROLE)

db_unique_name=physical' scope=both;

 

System altered.

Step15:

Check if the created table is transferred to Physical Standby.

[Physical] SQL> select table_name from dba_tables where owner='TEST';

 

TABLE_NAME

--------------------------------------------------------------------------------

JOBS_YEDEK

EMPLOYEES_YEDEK

Although we updated the LOG_ARCHIVE_DEST_2 parameter, the table was not transferred. This is because the Redo Transport mode will be enabled after the Log Switch operation.