Oracle 12c Far Sync Standby Instance

12c Data Guard-Far Sync Standby

    1.  Create the control file for the far sync instance (the primary database does not have to be open, but it must at least be mounted):

SQL> startup mount;

2. Create Far-Sync control file

mkdir-p /u01/app/oracle/oradata/FSTESTP

SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/u01/app/oracle/oradata/FSTESTP/control01.ctl';

Database altered.

·         copy the far sync instance control file created in Step 2 for the far sync instance.

3. Create the required directories, oratab entry, password file.

4 . Create a parameter file (PFILE) from the server parameter file (SPFILE) used by the primary database and modified the below parameters for Primary Database, TESTP

·         DB_UNIQUE_NAME=TESTP

·         FAL_SERVER= TESTS;
·         LOG_ARCHIVE_CONFIG='DG_CONFIG=( TESTP, FSTESTP, TESTS)';
·         LOG_ARCHIVE_DEST_1='LOCATION= /u01/app/oracle/oradata/TESTP/arch
·         VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= TESTP';
·         LOG_ARCHIVE_DEST_2='SERVICE= FSTESTP SYNC AFFIRM
·         VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= FSTESTP '
5. copy the parameter file created for the primary system to the far sync instance.

cp initTESTP.ora initFSTESTP.ora

Modify the following parameters for Far Sync Instance, FSTESTP:

·         DB_UNIQUE_NAME=FSTESTP
·         FAL_SERVER=TESTP
·         LOG_FILE_NAME_CONVERT='TESTP','FSTESTP'
·         LOG_ARCHIVE_CONFIG='DG_CONFIG=(TESTP,FSTESTP,TESTS)'
·         LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST
·         VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME= FSTESTP'
·         LOG_ARCHIVE_DEST_2='SERVICE=TESTS ASYNC
·         VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=TESTS'

6. Configure tns and listener entry

               -----------------------------------------------
·         Configure a listener for the far sync instance.
·         Create a network service name for the far sync instance (FSTESTP) that will be used by redo transport services.
·         Create a network service name for the primary (TESTP) and the terminal standby (TESTS) that will be used by redo transport services.

7. Startup Primary and far-Sync( Mount mode)

 

SQL> startup mount pfile=initTESTP.ora;

SQL> create spfile from pfile;

SQL> shutdown

SQL>startup

FAR SYNC

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

SQL> startup nomount pfile='?/dbs/initFSTESTP.ora';

SQL> alter database mount;

SQL> select * from V$DATAGUARD_CONFIG ;

DB_UNIQUE_NAME                 PARENT_DBUN                    DEST_ROLE

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

CURRENT_SCN     CON_ID

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

FSTESTP                        UNKNOWN                        FAR SYNC INSTANCE

          0          0

 TESTS                          FSTESTP                        UNKNOWN

         0          0

TESTP                          UNKNOWN                        UNKNOWN

          0          0

SQL> Select database_role from v$database;

DATABASE_ROLE

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

FAR SYNC

8. Check Standby log files

On Primary Database

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

SQL> select member,type from v$logfile;

MEMBER

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

TYPE

-------

/u01/app/oracle/oradata/TESTP/redo03.log

ONLINE

/u01/app/oracle/oradata/TESTP/redo02.log

ONLINE

/u01/app/oracle/oradata/TESTP/redo01.log

ONLINE

SQL> alter database add standby logfile THREAD 1 group 5 ('/u01/app/oracle/oradata/TESTP/sdlog05.log') size 200M;

Database altered.

1* alter database add standby logfile THREAD 1 group 6 ('/u01/app/oracle/oradata/TESTP/sdlog06.log') size 200M

SQL> /

Database altered.

alter database add standby logfile THREAD 1 group 4 ('/u01/app/oracle/oradata/TESTP/sdlog04.log') size 200M;

on far-sync standby, create standby redo logs in the same way they are created for a regular standby. 

SQL> alter database add standby logfile THREAD 1 group 5 ('/u01/app/oracle/oradata/FSTESTP/arch/sdlog05.log') size 200M;

Database altered.

alter database add standby logfile THREAD 1 group 6 ('/u01/app/oracle/oradata/FSTESTP/arch/sdlog06.log') size 200M

SQL> /

Database altered.

alter database add standby logfile THREAD 1 group 4 ('/u01/app/oracle/oradata/FSTESTP/arch/sdlog04.log') size 200M

SQL> /

Database altered. 

9. Enable log shipping on the Primary database

alter system set log_archive_dest_state_2 = enable scope = memory;

10. Verify that the far sync instance is operating properly (Redo logs are coming or not)

Log Switch on Primary Side:

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

QL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> !ls -l /u01/app/oracle/oradata/TESTP/arch

total 772

-rw-r----- 1 oracle dba 275968 Jul 13 05:52 1_18_820524767.dbf

-rw-r----- 1 oracle dba 325120 Jul 13 05:52 1_19_820524767.dbf

-rw-r----- 1 oracle dba   5632 Jul 13 05:52 1_20_820524767.dbf

-rw-r----- 1 oracle dba   3584 Jul 13 05:52 1_21_820524767.dbf

-rw-r----- 1 oracle dba   1536 Jul 13 05:52 1_22_820524767.dbf

-rw-r----- 1 oracle dba 148992 Jul 13 05:54 1_23_820524767.dbf

-rw-r----- 1 oracle dba   1536 Jul 13 05:54 1_24_820524767.dbf

Check the archive logs are received on the far sync standby :

[oracle@cloud FSTESTP]$ ls -l /u01/app/oracle/oradata/FSTESTP/arch

total 615516

-rw-r----- 1 oracle dba    325120 Jul 13 05:53 1_19_820524767.dbf

-rw-r----- 1 oracle dba      1536 Jul 13 05:52 1_22_820524767.dbf

-rw-r----- 1 oracle dba    148992 Jul 13 05:54 1_23_820524767.dbf

-rw-r----- 1 oracle dba  52429312 Jul 13 05:54 1_25_820524767.dbf

Good, all logs are coming properly to far-Sync instance.

11. create Physical Standby instance, TESTS

Setup parameter file:
[oracle@cloud dbs]$ more initTESTS.ora
TESTS.__data_transfer_cache_size=0
TESTS.__db_cache_size=289406976
TESTS.__java_pool_size=4194304
TESTS.__large_pool_size=4194304
TESTS.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TESTS.__pga_aggregate_target=293601280
TESTS.__sga_target=440401920
TESTS.__shared_io_pool_size=0
TESTS.__shared_pool_size=134217728
TESTS.__streams_pool_size=0
*.compatible='12.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/TESTS/control01.ctl','/u01/app/oracle/oradata/TESTS/co
ntrol02.ctl'
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oracle/oradata/TESTP','/u01/app/oracle/oradata/TESTS'
*.db_name='TESTP'
##*.db_recovery_file_dest='/u01/app/oracle/oradata/TESTS/arc'
##*.db_recovery_file_dest_size=4586M
*.db_unique_name='TESTS'
##*.dg_broker_config_file1='/u01/app/oracle/oradata/TESTS/arc/dr1TESTSB.dat'
##*.dg_broker_config_file2='/u01/app/oracle/oradata/TESTS/arc/dr2TESTSB.dat'
##*.dg_broker_start=true
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='FSTESTP','TESTP'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(TESTP,FSTESTP,TESTS)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/TESTS/arc VALID_FOR=(ALL_LOGFILES,ALL_RO
LES) DB_UNIQUE_NAME=TESTS'
*.LOG_ARCHIVE_DEST_2='SERVICE=TESTP ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NA
ME=FSTESTP'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.log_archive_format='c%t_%s_%r.dbf'
##*.log_file_name_convert='/u01/app/oracle/oradata/TESTDB1','/u01/app/oracle/oradata/TESTS'
*.memory_target=734003200
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='exclusive'
*.standby_file_management='auto'
*.undo_tablespace='UNDOTBS1'

Create Standby database using rman duplicate:

[oracle@cloud dbs]$ . oraenv

ORACLE_SID = [TESTP] ? TESTS
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@cloud dbs]$ sqlplus " / as sysdba "
SQL> startup nomount pfile='?/dbs/initTESTS.ora';
[oracle@cloud dbs]$ rman
Recovery Manager: Release 12.1.0.1.0 - Production on Sat Jul 13 06:21:25 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target sys/oracle@TESTP
connected to target database: TESTP (DBID=1031486427)
RMAN> connect auxiliary sys/oracle@TESTS
connected to auxiliary database: TESTP (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
12. Start applying logs for standby
SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> @seq
 SEQUENCE# FIRST_TIM COMPLETED
             ---------- --------- ---------------
        39 13-JUL-13 13-jul-13 16:42
        38 13-JUL-13 13-jul-13 16:39
        37 13-JUL-13 13-jul-13 16:26
        36 13-JUL-13 13-jul-13 16:24
        35 13-JUL-13 13-jul-13 16:24
        34 13-JUL-13 13-jul-13 16:23
        33 13-JUL-13 13-jul-13 16:23
        32 13-JUL-13 13-jul-13 16:17
        31 13-JUL-13 13-jul-13 16:04
        30 13-JUL-13 13-jul-13 16:04
10 rows selected.
SQL> !more seq.sql
select * from (
select sequence#, first_time,
to_char(first_time,'dd-mon-yy hh24:mi') as Completed
from v$log_history
order by sequence# desc)
 where rownum <=10;
13. Check the Physical standby status after shutting down the Far Sync
alert log from primary:
ARC2: Error 12514 attaching to RFS for reconnect
PING[ARC2]: Error 3113 when pinging standby FSTESTP.
Sat Jul 13 17:13:07 2013
LGWR: Failed to archive log 1 thread 1 sequence 40 (3113)

Check on Physical Standby ( Logs are not coming)
----------------------------------------------------------------------------

SQL> @seq

 SEQUENCE# FIRST_TIM COMPLETED
             ---------- --------- ---------------
        39 13-JUL-13 13-jul-13 16:42
        38 13-JUL-13 13-jul-13 16:39
        37 13-JUL-13 13-jul-13 16:26
        36 13-JUL-13 13-jul-13 16:24
        35 13-JUL-13 13-jul-13 16:24
        34 13-JUL-13 13-jul-13 16:23
        33 13-JUL-13 13-jul-13 16:23
        32 13-JUL-13 13-jul-13 16:17
        31 13-JUL-13 13-jul-13 16:04
        30 13-JUL-13 13-jul-13 16:04
10 rows selected.

Configuring an ALTERNATE Destination
-----------------------------------------
Alternate destination will reduce the amount of data loss by allowing Oracle Data Guard to ship redo asynchronously directly from the primary to the terminal standby, temporarily bypassing the far sync instance.

To configure an alternate destination, set the parameters on the primary database as follows:

 

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=FSTESTP SYNC AFFIRM MAX_FAILURE=1 ALTERNATE=LOG_ARCHIVE_DEST_3

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=FSTESTP';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_3='ALTERNATE';

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_3='SERVICE=TESTS ASYNC ALTERNATE=LOG_ARCHIVE_DEST_2

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTS'
  2    3  ;
System altered.

Check on Physical Standby:

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

SQL> @seq

  SEQUENCE# FIRST_TIM COMPLETED

---------- --------- ---------------
        43 13-JUL-13 13-jul-13 17:13
        42 13-JUL-13 13-jul-13 17:13
         41 13-JUL-13 13-jul-13 17:13
        40 13-JUL-13 13-jul-13 16:58
        39 13-JUL-13 13-jul-13 16:42
        38 13-JUL-13 13-jul-13 16:39
        37 13-JUL-13 13-jul-13 16:26
        36 13-JUL-13 13-jul-13 16:24
        35 13-JUL-13 13-jul-13 16:24
        34 13-JUL-13 13-jul-13 16:23

10 rows selected.

 When the far sync instance becomes available again, Oracle Data Guard automatically resynchronizes the far sync instance FSTESTP and returns to the original configuration in which the primary sends redo to the far sync instance and the far sync instance forwards that redo to the terminal standby. When the synchronization is complete, the alternate destination (LOG_ARCHIVE_DEST_3 in the preceding example) will again become dormant as the alternate.

Startup far sync

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

SQL> startup mount;

SQL> archive log list;
SQL> !ls -l /u01/app/oracle/oradata/FSTESTP/arch

[oracle@cloud arch]$ ls -ltra

[oracle@cloud arch]$ ls -ltra
total 623456
drwxr-xr-x 7 oracle dba      4096 Jul 13 07:52 ..
-rw-r----- 1 oracle dba   1170944 Jul 13 16:19 1_31_820652583.dbf
-rw-r----- 1 oracle dba     81408 Jul 13 16:31 1_36_820652583.dbf
-rw-r----- 1 oracle dba    555520 Jul 13 16:40 1_37_820652583.dbf
-rw-r----- 1 oracle dba     94720 Jul 13 16:46 1_38_820652583.dbf
-rw-r----- 1 oracle dba    776704 Jul 13 17:01 1_39_820652583.dbf
-rw-r----- 1 oracle dba    527872 Jul 13 17:40 1_44_820652583.dbf
-rw-r----- 1 oracle dba 209715712 Jul 13 18:26 sdlog05.log
-rw-r----- 1 oracle dba 209715712 Jul 13 18:26 sdlog04.log
-rw-r----- 1 oracle dba   5366272 Jul 13 18:29 1_45_820652583.dbf
-rw-r----- 1 oracle dba      2048 Jul 13 18:29 1_46_820652583.dbf
drwxr-xr-x 2 oracle dba      4096 Jul 13 18:29 .

Check on Standby Database
----------------------------------
SQL> @seq
 SEQUENCE# FIRST_TIM COMPLETED
---------- --------- ---------------
        46 13-JUL-13 13-jul-13 18:26
        45 13-JUL-13 13-jul-13 17:40
        44 13-JUL-13 13-jul-13 17:27
        43 13-JUL-13 13-jul-13 17:13
        42 13-JUL-13 13-jul-13 17:13
        41 13-JUL-13 13-jul-13 17:13
        40 13-JUL-13 13-jul-13 16:58
        39 13-JUL-13 13-jul-13 16:42
        38 13-JUL-13 13-jul-13 16:39
        37 13-JUL-13 13-jul-13 16:26
Now new logs are applying to physical standby through Far Sync instance again.