Creating Far Sync instance step by step

These are the steps of creating Far Sync instance in the command line:

Create 12.1 primary db

Create 12.1 physical standby

Create regular standby DG broker configuration

modify network file (on primary and standby) and add entries for the far_sync instance, add to tnsnames.ora on Primary hosts and standby hosts:

FarSyncInst =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = FarSyncHost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FarSyncInst)
      (UR=A)
    )
  )

    Note: (UR=A) is needed to connect to a BLOCKED unmounted database instance.

Copy tnsnames.ora to new farsync server – ensure all hosts mentioned in tnsnames are written also in /etc/hosts

Add to listener.ora in far sync server

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0 )(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER=
        (SID_LIST=
                (SID_DESC=
                        (SID_NAME=FarSyncInst)
                        (ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1)
                        (GLOBAL_DBNAME=FarSyncInst)
                )
                (SID_DESC=
                        (SID_NAME=FarSyncInst)
                        (ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1)
                        (GLOBAL_DBNAME=FarSyncInst_DGMGRL)
                )
                (SID_DESC=
                        (SID_NAME=FarSyncInst)
                        (ORACLE_HOME=/oracle/product/12.1.0.2/dbhome_1)
                        (GLOBAL_DBNAME=FarSyncInst_DGB)
                )
        )
ADR_BASE_LISTENER = /oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET

Install on Far Sync host 12.1 database software, On primary database:

SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS  '/u01/app/oracle/product/12.1.0.2/db/dbs/farsync_FarSyncInst_control.ctl';
SQL> create pfile='/u01/app/oracle/product/12.1.0.2/db/dbs/initFarSyncInst.ora' from spfile;

edit initFarSyncInst.ora, Add the following lines:

*.db_unique_name=FarSyncInst
*.cpu_count=1

Change the following lines:

*.log_archive_config='DG_CONFIG=(PrimaryDB,StandbyDB,FarSyncInst)'
*.audit_file_dest='/oracle/admin/FarSyncInst/adump'
*.fal_server='PrimaryDB','StandbyDB'
*.log_archive_dest_1 ='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'
*.control_files='/oracle/product/12.1.0.2/dbhome_1/dbs/farsync_FarSyncInst_control.ctl'
*.undo_tablespace='UNDOTBS1'
*.sga_target=384m
*.db_create_file_dest='+DATA'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=50g
*.db_file_name_convert='+DATA/PrimaryDB/', '+DATA/FarSyncInst/'
*.log_file_name_convert='+DATA/PrimaryDB/', '+DATA/FarSyncInst/'
*.dg_broker_config_file1='+DATA/FarSyncInst/dr1FarSyncInst.dat'
*.dg_broker_config_file2='+DATA/FarSyncInst/dr2FarSyncInst.dat'
*.diagnostic_dest='/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=FarSyncInstXDB)'

Delete the following lines:

.__*
.instance_number
.log_archive_format
.log_archive_trace
.thread
.undo_tablespace
log_archive_max_processes
memory_target
LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_3
cluster_database
processes
open_cursors
memory_target
remote_listener
db_writer_processes
gcs_server_processes
sessions?
resource_manager_plan?

On Primary - Copy pfile, created controlfile file  to far sync server (with the instance name).

cd $ORACLE_HOME/dbs
scp initFarSyncInst.ora farsync_FarSyncInst_control.ctl FarSyncHost:/oracle/product/12.1.0.2/dbhome_1/dbs/

On Far Sync Server - Create required directories

mkdir -p $ORACLE_HOME/rdbms/audit
mkdir -p $ORACLE_BASE/admin/FarSyncInst/adump

create grid resource

srvctl add database -d FarSyncInst  -o /oracle/product/12.1.0.2/dbhome_1 -n FarSyncInst  -i FarSyncInst  -y automatic -r FAR_SYNC

copy password file from primary

ASM> pwget --dbuniquename PrimaryDB
+DATA/PrimaryDB/PASSWORD/pwdPrimaryDB.276.913723271
ASM> pwcopy +DATA/PrimaryDB/PASSWORD/pwdPrimaryDB.276.913723271 /tmp/
copying +DATA/PrimaryDB/PASSWORD/pwdPrimaryDB.276.913723271 -> /tmp/pwdPrimaryDB.276.913723271
$ scp /tmp/pwdPrimaryDB.276.913723271 FarSyncHost: /tmp

Get password file back to ASM on FarSyncHost

ASM> mkdir +DATA/FarSyncInst
ASM> pwcopy --dbuniquename FarSyncInst /tmp/pwdPrimaryDB.276.913723271 +DATA/FarSyncInst/pwdFarSyncInst

$ export ORACLE_SID=FarSyncInst
SQL> startup nomount
SQL> create spfile from pfile='/oracle/product/12.1.0.2/dbhome_1/dbs/initFarSyncInst.ora';
SQL> shutdown abort

Create .bashrc on the far sync server and define Oracle environment parameters.

Recommended: move the redo + archives to the correct location on far sync server

Start far sync standby database

SQL> startup nomount

Restore controlfile from to ASM.

$ rman nocatalog
RMAN> connect target;
RMAN> restore controlfile to '+DATA' from '/oracle/product/12.1.0.2/dbhome_1/dbs/farsync_FarSyncInst_control.ctl';

Find the ASM full name of the controlfile.

$ asmcmd
ASMCMD [+] > cd DATA
ASMCMD [+DATA] > find --type controlfile . *
+DATA/FarSyncInst/CONTROLFILE/current.622.902053967

Update spfile with the new controlfile found in the last step.

SQL> alter system set control_files='+DATA/FarSyncInst/CONTROLFILE/current.622.902053967' scope=spfile;
SQL> create pfile='/oracle/product/12.1.0.2/dbhome_1/dbs/initFarSyncInst.ora' from spfile;
SQL> create spfile='+DATA/FarSyncInst/spfileFarSyncInst.ora' from pfile='/oracle/product/12.1.0.2/dbhome_1/dbs/initFarSyncInst.ora';

Create init.ora file calling to the ASM spfile

$ mv initFarSyncInst.ora _initFarSyncInst.ora
$ mv spfileFarSyncInst.ora _spfileFarSyncInst.ora
$ vi initFarSyncInst.ora
SPFILE='+DATA/FarSyncInst/spfileFarSyncInst.ora'

Restart instance to load with the new spfile

SQL> shutdown abort
SQL> startup mount

Setup listenerand far sync instance are starting automatically using Oracle Restart (Grid Infrastructure)

srvctl add listener -l LISTENER -o /oracle/12.1.0/grid
srvctl start listener
$ srvctl add database -d FarSyncInst -o /oracle/product/12.1.0.2/dbhome_1 -n FarSyncInst -i FarSyncInst -y automatic -r FAR_SYNC
$ srvctl start database -d FarSyncInst

Create standby log on the far sync (if it was not already created on the primary):

set serveroutput on;
declare
  log_num number;
  log_size number;
  log_num_standby number;
begin
for j in  (select inst_id i from gv$instance) loop
         select count(*) into log_num from gv$log where inst_id=j.i ;
         select count(*) into log_num_standby from gv$standby_log where inst_id=j.i ;
         select max(BYTES) into log_size from gv$log where inst_id=j.i  ;
        for i in 1..(log_num+1-log_num_standby) loop
        --execute immediate 'ALTER DATABASE ADD STANDBY LOGFILE THREAD '||j.i ||' SIZE '||log_size;
        dbms_output.put_line('ALTER DATABASE ADD STANDBY LOGFILE THREAD '||j.i ||' SIZE '||log_size||';');
        end loop;
end loop;
end;
/

Check if you need to create more threads if the primary is in RAC

select GROUP#,THREAD# from v$standby_log;

If needed add more standby redo logs at the same size to the other threads manually as much as it needed

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 52428800

Verify database role is Far Sync

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
------------------------------ -----------------
FarSyncInst                         FAR SYNC INSTANCE
PrimaryDB                           UNKNOWN
StandbyDB                           UNKNOWN

On primary database:

SQL> ALTER SYSTEM Set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PrimaryDB,StandbyDB,FarSyncInst)';
SQL> Alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
SQL> ALTER SYSTEM Set  LOG_ARCHIVE_DEST_2=' ';
SQL> ALTER DATABASE SET STANDBY TO MAXIMIZE AVAILABILITY;

Add to data guard broker setup

$ dgmgrl /
DGMGRL> ADD FAR_SYNC FarSyncInst AS CONNECT IDENTIFIER IS FarSyncInst;
DGMGRL> EDIT FAR_SYNC FarSyncInst SET PROPERTY RedoRoutes = '(PrimaryDB : StandbyDB ASYNC)';
DGMGRL> EDIT DATABASE StandbyDB SET PROPERTY LogXptMode = 'SYNC';
DGMGRL> EDIT DATABASE PrimaryDB SET PROPERTY MaxFailure = 0;
DGMGRL> EDIT FAR_SYNC FarSyncInst SET PROPERTY MaxFailure = 1;
DGMGRL> EDIT DATABASE StandbyDB SET PROPERTY MaxFailure = 0;
DGMGRL> EDIT DATABASE StandbyDB SET PROPERTY RedoRoutes = '(LOCAL : PrimaryDB ASYNC)';

Oracle 12.2 Priority

DGMGRL> ENABLE CONFIGURATION;
DGMGRL> EDIT DATABASE PrimaryDB SET PROPERTY RedoRoutes = '(LOCAL : ( FarSyncInst SYNC PRIORITY=1, StandbyDB ASYNC PRIORITY=2 ) )';

Oracle 12.1 Alternate

DGMGRL> EDIT DATABASE PrimaryDB SET PROPERTY RedoRoutes = '(LOCAL : FarSyncInst SYNC ALT =(StandbyDB ASYNC FALLBACK))';

Change the mode Maximum Availability

DGMGRL> DISABLE CONFIGURATION;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
DGMGRL> ENABLE CONFIGURATION;