Tuesday, October 20, 2015

12c Compression Comparison

OLTP Table compression introduced during 11g is called Advanced Row Compression in 12c. However the old syntax still works on 12c as well. Advance row compression maintains compression during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE. Oracle's claim is that this minimizes the overhead for write operation of and making it suitable for OLTP and Data Warehouses. Advanced Row Compression requires purchasing Oracle Advanced Compression option while basic table compression is a feature of Oracle Database 12c Enterprise Edition (EE).
This post compares the reduction of storage usage, overhead of CPU and redo generated for advance vs basic vs no compression options. It is not an extensive test, how useful the compression depends on data being compressed.
As said earlier compress for OLTP creates the table with advance row compression.
create table x (a number, b varchar2(100)) compress for oltp;
select table_name,COMPRESSION,COMPRESS_FOR from  user_tables where table_name='X';

TABLE_NAME COMPRESS COMPRESS_FOR
---------- -------- ------------------------------
X          ENABLED  ADVANCED

create table x (a number, b varchar2(100)) ROW STORE COMPRESS ADVANCED;
select table_name,COMPRESSION,COMPRESS_FOR from  user_tables where table_name='X';

TABLE_NAME COMPRESS COMPRESS_FOR
---------- -------- ------------------------------
X          ENABLED  ADVANCED
If no option is specified this would create basic compression or it could be explicitly specified.
create table x (a number, b varchar2(100)) compress;
SQL> select table_name,COMPRESSION,COMPRESS_FOR from  user_tables where table_name='X';

TABLE_NAME COMPRESS COMPRESS_FOR
---------- -------- ------------------------------
X          ENABLED  BASIC

create table x (a number, b varchar2(100)) ROW STORE COMPRESS basic;
select table_name,COMPRESSION,COMPRESS_FOR from  user_tables where table_name='X';

TABLE_NAME COMPRESS COMPRESS_FOR
---------- -------- ------------------------------
X          ENABLED  BASIC
For the test case three tables were created with no compression, advance and basic compression. The database is 12.1.0.2.
create table NOCOMPRESSTABLE(a number, b varchar2(100));
create table ADVCOMPRESSTABLE(a number, b varchar2(100)) ROW STORE COMPRESS ADVANCED;
create table BASICVCOMPRESSTABLE(a number, b varchar2(100)) ROW STORE COMPRESS basic;

SQL> select table_name,COMPRESSION,COMPRESS_FOR from  user_tables where table_name like '%COMPRESSTABLE';

TABLE_NAME           COMPRESS COMPRESS_FOR
-------------------- -------- -------------
NOCOMPRESSTABLE      DISABLED
BASICVCOMPRESSTABLE  ENABLED  BASIC
ADVCOMPRESSTABLE     ENABLED  ADVANCED
Each table consists of two columns and was populated using the following anonymous PL/SQL block. It inserts a unique value to first column while second column get the same value inserted with each row.
begin
    for i in 1 .. 1000000
    loop
    insert into NOCOMPRESSTABLE values (i,'asdfghjkllqwertyuiopzxcvbnm134567890');
    insert into ADVCOMPRESSTABLE values(i,'asdfghjkllqwertyuiopzxcvbnm134567890');
    insert into BASICVCOMPRESSTABLE values(i,'asdfghjkllqwertyuiopzxcvbnm134567890');
    commit;
    end loop;
end;
/
At the end of the insert the segment size of each table was measured
select segment_name,bytes/1024/1024 as MB from user_segments where segment_name like '%COMPRESS%';

SEGMENT_NAME          MB
--------------------- ---
ADVCOMPRESSTABLE      14
BASICVCOMPRESSTABLE   47
NOCOMPRESSTABLE       52
Results are no surprise as the segment of the table created with advance compression is the smallest and table with no compression is the largest. However this test is the most optimistic of cases where one column consists of only a single value.





Therefore the test was rerun after recreating the tables and inserts were modified as below where some of the values inserted to second column are duplicates but not all of them
begin
    for i in 1 .. 1000000
    loop
--    insert into NOCOMPRESSTABLE values (i,'asdfghjkllqwertyuiopzxcvbnm134567890'||mod(i,500));
    insert into ADVCOMPRESSTABLE values(i,'asdfghjkllqwertyuiopzxcvbnm134567890'||mod(i,500));
--    insert into BASICVCOMPRESSTABLE values(i,'asdfghjkllqwertyuiopzxcvbnm134567890'||mod(i,500));
    commit;
    end loop;
    end;
    /
Only one table was inserted at a time and CPU used by session and redo size were measured for each test, shown on the graphs below.
Strangely the inserts done with compress options used less CPU than when inserted without any compression (test were repeated 3 times and same pattern was observed). However the difference wasn't huge. On the other hand use of advance compression option resulted in more redo being created than basic compression or no compression.
Comparing the segment size revealed the following.
select segment_name,bytes/1024/1024 as MB from user_segments where segment_name like '%COMPRESS%';

SEGMENT_NAME          MB
--------------------- ---
ADVCOMPRESSTABLE      55
BASICVCOMPRESSTABLE   50
NOCOMPRESSTABLE       55
It seems basic compression is slightly better than no compression option but advance compression didn't yield any benefit at all. Reason could be that advance compression didn't encounter enough duplicate values inside a block to make any significant reduction. It's important to remember that compression happens at block level.
Test was rerun this time however the tables were created on a tablespace with a block size of 32k. The segment size at the end of the test are as below
select segment_name,bytes/1024/1024 as MB from user_segments where segment_name like '%COMPRESS%';

SEGMENT_NAME          MB
--------------------- ---
ADVCOMPRESSTABLE      49
BASICVCOMPRESSTABLE   49
NOCOMPRESSTABLE       54
This time there's difference between segment size whose tables were created with a compression options and table created without any compression. However the reduction in segment size is not same as when all the values were duplicates.
From this simple test case it seems, to benefit from a compression strategy, the nature of the data is more important than the compression option being used. The reduction in space is dictated by how many duplicate values are found inside a block.

Wednesday, October 14, 2015

Change in Table Locking (TM) Behavior When Foreign Key Columns are Unindexed in 12.1.0.2

One of the most common cause of Table locks related wait enq: TM - contention is unindexed foreign key columns. There are two previous post related to this which areTM enq: Locking and Invisible Indexes and TM enq: Locks. One of the scenarios examined in these previous posts was where one session inserting to parent table and another session updating the primary key column or deleting a row on the parent table. In this scenario the second session would hang, observed both in 11.2.0.3 and 11.2.0.4. However this is no longer the case in 12c, tested on 12.1.0.2. This post look the change in this behavior.
First the test on 11.2.0.4. The test case is same as the one used in previous post where table X is the parent table and Y is the child table which refers table X and foreign key column is not indexed. Tables were populated same as before. Two sessions were created using sqlplus and autocommit set to off.
Session one does an insert to the primary table
SQL> select sys_context('userenv','sid') sid from dual;

SID
----
32

SQL> insert into x values (11,21);

1 row created.
Looking at the locks taken by session one (id : 32) it could be seen TM locks for both parent and child tables are there.
SQL> select * from v$lock where sid=32;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0000000090D90E10 0000000090D90E68         32 AE        100          0          4          0        134          2
00007FFC99FE6E10 00007FFC99FE6E70         32 TM      72423          0          3          0         70          2
00007FFC99FE6E10 00007FFC99FE6E70         32 TM      72425          0          3          0         70          2
000000008DBA98F0 000000008DBA9968         32 TX     458766       5244          6          0         70          2

SQL> select object_name,object_id from user_objects where object_id in (72423,72425);

OBJECT_NAME  OBJECT_ID
----------- ----------
X               72423
Y               72425
The ID1 column gives the object ids and querying the user object view shows that TM locks are taken for both parent (X) and child (Y) tables. The locking mode is 3 which is row-X (SX). 11.2 Oracle documentation also says "Inserts into the parent table do not acquire table locks on the child table" which is not clearly the case however this text has been changed in 12c documentation to state "Inserts into the parent table do not acquire blocking table locks that prevent DML on the child table".
Second session does an update of the parent table by changing a value in primary key column. This results in second session hanging and this is also stated in 11.2 documentation "When both of the following conditions are true, the database acquires a full table lock on the child table:
1.No index exists on the foreign key column of the child table.
2.A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. Inserts into the parent table do not acquire table locks on the child table.
". Condition 1 is true in this case but so far there has been only an insert to parent table and this has resulted in a TM lock even though documentation says otherwise. Second condition is true only for second session which updates a primary key.
SQL> select sys_context('userenv','sid') sid from dual;

SID
-----
149

SQL> update x set a = 12 where a = 7;
The update hangs and querying locks for both session 32 and 149 gives the following
SQL>  select * from v$lock where sid in (149,32);

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0000000090D8EF40 0000000090D8EF98        149 AE        100          0          4          0       1027          2
0000000090D90E10 0000000090D90E68         32 AE        100          0          4          0       1029          2
00007FFC99FE6E10 00007FFC99FE6E70         32 TM      72423          0          3          0        965          2
00007FFC99FE6E10 00007FFC99FE6E70         32 TM      72425          0          3          0        965          1
00007FFC99FE6E10 00007FFC99FE6E70        149 TM      72423          0          3          0         58          2
00007FFC99FE6E10 00007FFC99FE6E70        149 TM      72425          0          0          4         58          0
000000008DBA98F0 000000008DBA9968         32 TX     458766       5244          6          0        965          2
From the lock view it could be seen that second session (id :149) is also has locked (object id 72423 which table X, parent table) and trying to lock the child table same as session 32. However it's failing to get the lock on child table which it request with mode 4 but locked by session 32 with mode 3 and is not getting the lock. The block column shows that session 32 lock on object 72425 (child table Y) is the blocker.
DBA Waiters view give the holding and blocking session
SQL> select * from dba_waiters;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE                  MODE_HELD  MODE_REQUE   LOCK_ID1   LOCK_ID2
--------------- --------------- -------------------------- ---------- ---------- ---------- ----------
            149              32 DML                        Row-X (SX) Share           72425          0
and session wait shows the wait event
SQL> select sid,event from v$session_wait where sid in (32,149);

       SID EVENT
---------- -------------------------
        32 SQL*Net message to client
       149 enq: TM - contention



Now the same test on 12c (12.1.0.2) both on EE and SE2. Session one does the insert
SQL> select sys_context('userenv','sid') sid from dual;

SID
-----
399

SQL> insert into x values (11,21);

1 row created.
Looking the locks it could be seen the child table is locked in mode 2 (row-S (SS)), a shared mode which is different to behavior in 11.2
SQL> select * from v$lock where sid=399;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
000000013D4CBA98 000000013D4CBB10        399 AE        133          0          4          0        155          0          0
000000013731A468 000000013731A4E8        399 TX     393216      33536          6          0         89          0          0
00002B104C568A28 00002B104C568A90        399 TM     137537          0          3          0         89          0          0
00002B104C568A28 00002B104C568A90        399 TM     137539          0          2          0         89          0          0

SQL> select object_name,object_id from user_objects where object_id in (137537,137539);

OBJECT_NAME  OBJECT_ID
----------- ----------
X               137537
Y               137539
Unlike the 11.2 environment in 12c the second session running the update statement will succeed.
SQL> select sys_context('userenv','sid') sid from dual;

SID
-----
769

SQL>  update x set a = 12 where a = 7;

1 row updated.
Looking at the lock view it shows that second session only has table locks on parent table and no locks on child table
SQL> select * from v$lock where sid in (769,399);

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
000000013D4CBA98 000000013D4CBB10        399 AE        133          0          4          0        426          0          0
000000013D4CCDC8 000000013D4CCE40        769 AE        133          0          4          0        423          0          0
00002B104C5679F0 00002B104C567A58        399 TM     137537          0          3          0        360          0          0
00002B104C5679F0 00002B104C567A58        399 TM     137539          0          2          0        360          0          0
00002B104C5679F0 00002B104C567A58        769 TM     137537          0          3          0         79          0          0
00000001373198C8 0000000137319948        769 TX     327692      33608          6          0         79          0          0
000000013731A468 000000013731A4E8        399 TX     393216      33536          6          0        360          0          0
This a change in behavior on 12c and oracle documentation states "Inserts into the parent table do not acquire blocking table locks that prevent DML on the child table. In the case of inserts, the database acquires a lock on the child table that prevents structural changes, but not modifications of existing or newly added rows."
This change of locking behavior in 12c could result in removing some indexes on child tables if the only reason for creating them is to prevent locking situations similar to above that were observed in 11.2. However the other locking situations due to unindexed foreign key columns are still there.

Thursday, October 1, 2015

Setting Up Data Guard Broker for an Existing Data Guard Configuration with Far Sync

This post list the steps for setting up data guard broker configuration for an existing data guard configuration with far sync. The data guard configuration is the same setup that had far sync instances added earlier. The current setup of the data guard configuration is given below.
Before setting up the dg broker, patch 19399918 (included in 12.1.0.2.4) and 19571599 (at the time of writing only available for system patched with 12.1.0.2.4) must be applied on all oracle homes involved, this include oracle homes used for far sync instances as well. Without the patch there'll be issues with the dg configuration and the switchover will fail when the redo route has an alternate option. However the switchover works without the alternate option on the redo route.
DGMGRL> show database ent12c1 LogXptStatus
Error: ORA-16777: unable to find the destination entry of a standby database in V$ARCHIVE_DEST

DGMGRL> switchover to ent12c1s;
Performing switchover NOW, please wait...
Error: ORA-16778: redo transport
Another pre-req is clearing of log_archvie_dest_* parameters in all the instances (including far sync instances). Trying to create a dg configuration or add a (far_sync) instance will fail.
DGMGRL> create configuration ent12c1_dgb as primary database is ent12c1 connect identifier is ent12c1tns;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

DGMGRL> add database ent12c1s as connect identifier is ent12c1stns;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

DGMGRL> ADD FAR_SYNC fs12c1 AS CONNECT IDENTIFIER IS fs12c1tns;
Error: ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added
This is a new requirement introduced in 12c which states "as of Oracle Database 12c Release 1 (12.1), for all databases to be added to a broker configuration, any LOG_ARCHIVE_DEST_n parameters that have the SERVICE attribute set, but not the NOREGISTER attribute, must be cleared."
1. To add the dg broker configuration clear the existing log_archive_dest_* parameter. In the existing setup destination 2 and 3 were used for log shipping.
alter system set log_Archive_dest_2='' scope=both;
alter system set log_Archive_dest_3='' scope=both;
alter system set log_archive_dest_state_3='enable' scope=both;
2. Create the dg broker configuration by adding primary, standby and far sync instances. In this case ENT12C1 is the primary database while ENT12C1S is the standby and FS12C1 is the far sync instance the primary database ships redo synchronously. After a role switch when the ENT12C1S becomes the new primary then it uses FS12C1S as the far sync instance to transport redo synchronously.
create configuration ent12c1_dgb as primary database is ent12c1 connect identifier is ent12c1tns;
add database ent12c1s as connect identifier is ent12c1stns;
add FAR_SYNC fs12c1 as connect identifier is fs12c1tns;
add FAR_SYNC fs12c1s as connect identifier is fs12c1stns;

show configuration;

Configuration - ent12c1_dgb

  Protection Mode: MaxPerformance
  Members:
  ent12c1  - Primary database
    ent12c1s - Physical standby database
    fs12c1   - Far sync instance
    fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED
3. Enable the configuration and far_sync.
enable configuration;
Enabled.
enable far_sync fs12c1;
Enabled.
enable far_sync fs12c1s;
Enabled.
4. Set the redo transport modes sync for far_sync (default is async) and appropriate redo routes for each instance. In this first instance the redo routes are set such that primary ship to far sync and far sync ships to standby. There's no redo route between primary and standby. This is different to earlier setup and will be changed in subsequent steps. It's also worth noting that "if a database receives redo from a database or far sync instance where the RedoRoutes property has been configured with a redo transport mode, then the mode specified by that RedoRoutes property value overrides the value of the LogXptMode property".
EDIT far_sync fs12c1 SET PROPERTY LogXptMode='SYNC';
EDIT far_sync fs12c1s SET PROPERTY LogXptMode='SYNC';

EDIT DATABASE ent12c1 SET PROPERTY 'RedoRoutes' = '(LOCAL : FS12C1 SYNC)';
EDIT FAR_SYNC fs12c1 SET PROPERTY 'RedoRoutes' = '(ent12c1 : ent12c1s ASYNC)';
EDIT DATABASE ent12c1s SET PROPERTY 'RedoRoutes' = '(LOCAL : FS12C1S SYNC)';
EDIT FAR_SYNC fs12c1s SET PROPERTY 'RedoRoutes' = '(ent12c1s : ent12c1 ASYNC)';
5. Verify the configuration and validate the database and far sync instances
DGMGRL> show configuration

Configuration - ent12c1_dgb

  Protection Mode: MaxPerformance
  Members:
  ent12c1  - Primary database
    fs12c1   - Far sync instance
      ent12c1s - Physical standby database

  Members Not Receiving Redo:
  fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 49 seconds ago)

DGMGRL> show configuration when primary is ent12c1s

Configuration when ent12c1s is primary - ent12c1_dgb

  Members:
  ent12c1s - Primary database
    fs12c1s  - Far sync instance
      ent12c1  - Physical standby database

  Members Not Receiving Redo:
  fs12c1   - Far sync instance

DGMGRL> validate far_sync fs12c1
    Member Role:        Far Sync Instance
    When Primary Is:    ent12c1

    Active Redo Source: ent12c1
    Redo Destinations:
                        ent12c1s

    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              ent12c1                 fs12c1
    1         3                       4                       Sufficient SRLs

DGMGRL> validate far_sync fs12c1s when primary is ent12c1s
    Member Role:        Far Sync Instance
    When Primary Is:    ent12c1s
    Redo Destinations:
                        ent12c1

    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              ent12c1s                fs12c1s
    1         3                       4                       Sufficient SRLs


DGMGRL> validate database ent12c1s

  Database Role:     Physical standby database
  Primary Database:  ent12c1

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    ent12c1:   Off
    ent12c1s:  Off

  Transport-Related Property Settings:
    Property                        ent12c1 Value            ent12c1s Value
    RedoRoutes                      (LOCAL : FS12C1 SYNC)    (LOCAL : FS12C1S SYNC)

DGMGRL> validate database ent12c1

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    ent12c1:  Off
6. During the validation of the databases the alert log will show the following warning.
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database
SWITCHOVER VERIFY COMPLETE
However the switchover worked fine without any issues.
DGMGRL> switchover to ent12c1s
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1s" on database "ent12c1s"
Connecting to instance "ent12c1s"...
Connected as SYSDBA.
New primary database "ent12c1s" is opening...
Operation requires start up of instance "ent12c1" on database "ent12c1"
Starting instance "ent12c1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1s"


DGMGRL> show configuration

Configuration - ent12c1_dgb

  Protection Mode: MaxPerformance
  Members:
  ent12c1s - Primary database
    fs12c1s  - Far sync instance
      ent12c1  - Physical standby database

  Members Not Receiving Redo:
  fs12c1   - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 3 seconds ago)

DGMGRL> switchover to ent12c1
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1" on database "ent12c1"
Connecting to instance "ent12c1"...
Connected as SYSDBA.
New primary database "ent12c1" is opening...
Operation requires start up of instance "ent12c1s" on database "ent12c1s"
Starting instance "ent12c1s"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1"
7. Once the switchovers are completed successfully it's time to modify the redo routes so there is an alternate path between primary and standby in case of far_sync failure. The redo transport mode between primary and standby would be async. In order to provide an alternative redo route the maxfailure property must be set to a non-zero value. Without this change alternate route cannot be specified
DGMGRL> EDIT DATABASE ent12c1 SET PROPERTY RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s ASYNC FALLBACK))';
Error: ORA-16863: A member whose MaxFailure property is set to zero cannot have an alternate destination.

EDIT DATABASE ent12c1 SET PROPERTY 'MaxFailure' = 1;
EDIT FAR_SYNC fs12c1 SET PROPERTY 'MaxFailure' = 1;
EDIT DATABASE ent12c1s SET PROPERTY 'MaxFailure' = 1;
EDIT FAR_SYNC fs12c1s SET PROPERTY 'MaxFailure' = 1;
The maxfailure property must be set for far sync as well. Even though they don't have a alternate destination set on them, failure to do so will result in the above error and it's not possible to change the maxfailure to 0 once the alternate destination is set.
DGMGRL>  show far_sync fs12c1 RedoRoutes
  RedoRoutes = '(ent12c1 : ent12c1s ASYNC)'

DGMGRL> EDIT FAR_SYNC fs12c1 SET PROPERTY 'MaxFailure' =0;
Error: ORA-16864: The MaxFailure property cannot be set to zero for a member that has an alternate destination.
8. Set the redo routes for the databases so there's an alternate route for redo transport if far sync is not available. As mentioned earlier the patch 19399918 must be applied on all instances (inclusive of far_sync) for this to work.
EDIT DATABASE ent12c1 SET PROPERTY RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s ASYNC FALLBACK))';
EDIT DATABASE ent12c1s SET PROPERTY RedoRoutes = '(LOCAL : FS12C1s SYNC ALT=(ent12c1 ASYNC FALLBACK))';
9. Verify the configuration shows the alternate destination.
DGMGRL> show configuration verbose

Configuration - ent12c1_dgb

  Protection Mode: MaxPerformance
  Members:
  ent12c1  - Primary database
    fs12c1   - Far sync instance
      ent12c1s - Physical standby database
    ent12c1s - Physical standby database (alternate of fs12c1)

  Members Not Receiving Redo:
  fs12c1s  - Far sync instance

DGMGRL> show configuration when primary is ent12c1s

Configuration when ent12c1s is primary - ent12c1_dgb

  Members:
  ent12c1s - Primary database
    fs12c1s  - Far sync instance
      ent12c1  - Physical standby database
    ent12c1  - Physical standby database (alternate of fs12c1s)

  Members Not Receiving Redo:
  fs12c1   - Far sync instance

DGMGRL> validate database ent12c1s

  Database Role:     Physical standby database
  Primary Database:  ent12c1

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    ent12c1:   Off
    ent12c1s:  Off

  Transport-Related Property Settings:
    Property                        ent12c1 Value            ent12c1s Value
    RedoRoutes                      (LOCAL : FS12C1 SYNC ALT=(ent12c1s ASYNC(LOCAL : FS12C1s SYNC ALT=(ent12c1 ASYNC
Check if the alternate location is visible on the v$archive_dest view of the primary database
SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME            STATUS    DESTINATION                    TRANSMIT_MOD
-------------------- --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1   VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2   VALID     fs12c1tns                      PARALLELSYNC
LOG_ARCHIVE_DEST_3   ALTERNATE ent12c1stns                    ASYNCHRONOUS
In case of far_sync failure the alternate destination will become valid.
SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME                      STATUS    DESTINATION                    TRANSMIT_MOD
------------------------------ --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1             VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2             ALTERNATE fs12c1tns                      PARALLELSYNC
LOG_ARCHIVE_DEST_3             VALID     ent12c1stns                    ASYNCHRONOUS
10. Check of inconsistent properties and rectify if any exists. Finally carry out switchovers.
DGMGRL> show database ent12c1 InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE

DGMGRL> show database ent12c1s InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE

DGMGRL> show far_sync fs12c1 InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE

DGMGRL>  show far_sync fs12c1s InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE

DGMGRL> switchover to ent12c1s
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1s" on database "ent12c1s"
Connecting to instance "ent12c1s"...
Connected as SYSDBA.
New primary database "ent12c1s" is opening...
Operation requires start up of instance "ent12c1" on database "ent12c1"
Starting instance "ent12c1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1s"

DGMGRL> switchover to ent12c1
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1" on database "ent12c1"
Connecting to instance "ent12c1"...
Connected as SYSDBA.
New primary database "ent12c1" is opening...
Operation requires start up of instance "ent12c1s" on database "ent12c1s"
Starting instance "ent12c1s"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1"
This completes setting up of dg broker for existing data guard configuration with far sync instances. The below section list some of the issues observed during various testing of the setup.





Observed Issues
1. It seems how the far sync is terminated has an effect on the fail over of the archive destination. If the far sync was terminated abruptly (shutdown abort) then the primary database's archive log destination fails over to alternative path give (v$archive_dest output given earlier). But when the far_sync starts up again the log archive destination status for the path from far_sync to standby comes up as idle. Following output is from far_sync
SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME            STATUS    DESTINATION                              TRANSMIT_MOD
-------------------- --------- ---------------------------------------- ------------
LOG_ARCHIVE_DEST_1   VALID     USE_DB_RECOVERY_FILE_DEST                SYNCHRONOUS
LOG_ARCHIVE_DEST_2   IDLE      ent12c1stns                              ASYNCHRONOUS
STANDBY_ARCHIVE_DEST VALID     USE_DB_RECOVERY_FILE_DEST                SYNCHRONOUS
Even after leaving it for 24 hour period the destination did not resolve itself. Manually intervention was needed
ALTER SYSTEM SET log_archive_dest_state_2='enable' SCOPE=BOTH;
2. If the far_sync has a clean shutdown (shutdown immediate instead of shutdown abort) then when it comes up again the log archive dest status come up as valid. However the failover on the primary doesn't happen to alternate destination. The destination to far_sync remains deferred and alternate stays as it is. Following output from primary when far_sync is shutdown cleanly
SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME                      STATUS    DESTINATION                    TRANSMIT_MOD
------------------------------ --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1             VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2             DEFERRED  fs12c1tns                      PARALLELSYNC
LOG_ARCHIVE_DEST_3             ALTERNATE ent12c1stns                    ASYNCHRONOUS
When the far_sync comes up again the deferred destination becomes valid again. In this scenario can lead to data loss as redo transport doesn't happen between primary and standby even though an alternate path was specified.

3. Increasing the protection mode was not possible with the redo routes defined with ALT and ASYNC.
DGMGRL> show database ent12c1 RedoRoutes
  RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s ASYNC FALLBACK))'
DGMGRL> show database ent12c1s RedoRoutes
  RedoRoutes = '(LOCAL : FS12C1s SYNC ALT=(ent12c1 ASYNC FALLBACK))'
DGMGRL> show far_sync fs12c1 RedoRoutes
  RedoRoutes = '(ent12c1 : ent12c1s ASYNC)'
DGMGRL> show far_sync fs12c1s RedoRoutes
  RedoRoutes = '(ent12c1s : ent12c1 ASYNC)'

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
There was no such problem before when the setup was not using the dg broker. Whatever the problem seem to be localized to dg broker setup. Removing the alternate option on the redo route from primary and standby databases allowed the upgrade of the protection mode, even though between far sync and standby redo was shipping async.
DGMGRL> EDIT DATABASE ent12c1 SET PROPERTY 'RedoRoutes' = '(LOCAL : FS12C1 SYNC)';
Property "RedoRoutes" updated
DGMGRL> EDIT DATABASE ent12c1s SET PROPERTY 'RedoRoutes' = '(LOCAL : FS12C1S SYNC)';
Property "RedoRoutes" updated
On primary
SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME            STATUS    DESTINATION                    TRANSMIT_MOD
-------------------- --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1   VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2   VALID     fs12c1tns                      PARALLELSYNC
On far sync
SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME            STATUS    DESTINATION                              TRANSMIT_MOD
-------------------- --------- ---------------------------------------- ------------
LOG_ARCHIVE_DEST_1   VALID     USE_DB_RECOVERY_FILE_DEST                SYNCHRONOUS
LOG_ARCHIVE_DEST_2   VALID     ent12c1stns                              ASYNCHRONOUS
STANDBY_ARCHIVE_DEST VALID     USE_DB_RECOVERY_FILE_DEST                SYNCHRONOUS
Increase of protection mode works and switchvoer is possible without any issue
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

DGMGRL> show configuration;

Configuration - ent12c1_dgb

  Protection Mode: MaxAvailability
  Members:
  ent12c1  - Primary database
    fs12c1   - Far sync instance
      ent12c1s - Physical standby database

  Members Not Receiving Redo:
  fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 59 seconds ago)

DGMGRL> switchover to ent12c1s
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1s" on database "ent12c1s"
Connecting to instance "ent12c1s"...
Connected as SYSDBA.
New primary database "ent12c1s" is opening...
Operation requires start up of instance "ent12c1" on database "ent12c1"
Starting instance "ent12c1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1s"


DGMGRL> show configuration

Configuration - ent12c1_dgb

  Protection Mode: MaxAvailability
  Members:
  ent12c1s - Primary database
    fs12c1s  - Far sync instance
      ent12c1  - Physical standby database

  Members Not Receiving Redo:
  fs12c1   - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

DGMGRL> switchover to ent12c1
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1" on database "ent12c1"
Connecting to instance "ent12c1"...
Connected as SYSDBA.
New primary database "ent12c1" is opening...
Operation requires start up of instance "ent12c1s" on database "ent12c1s"
Starting instance "ent12c1s"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1"
Even though ALT+ASYCN combination didn't work, ALT+SYNC combination allowed increase of protection mode and switchover without any issue.
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS maxperformance;
Succeeded.

DGMGRL> show configuration;

Configuration - ent12c1_dgb

  Protection Mode: MaxPerformance
  Members:
  ent12c1  - Primary database
    fs12c1   - Far sync instance
      ent12c1s - Physical standby database

  Members Not Receiving Redo:
  fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 31 seconds ago)

DGMGRL> EDIT DATABASE ent12c1 SET PROPERTY RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s SYNC FALLBACK))';
Property "redoroutes" updated
DGMGRL> EDIT DATABASE ent12c1s SET PROPERTY RedoRoutes = '(LOCAL : FS12C1s SYNC ALT=(ent12c1 SYNC FALLBACK))' ; 
Property "redoroutes" updated

DGMGRL> show database ent12c1 RedoRoutes
  RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s SYNC FALLBACK))'
DGMGRL>  show database ent12c1s RedoRoutes
  RedoRoutes = '(LOCAL : FS12C1s SYNC ALT=(ent12c1 SYNC FALLBACK))'
DGMGRL> show far_sync fs12c1 RedoRoutes
  RedoRoutes = '(ent12c1 : ent12c1s ASYNC)'
DGMGRL>  show far_sync fs12c1s RedoRoutes
  RedoRoutes = '(ent12c1s : ent12c1 ASYNC)'


DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

DGMGRL> show configuration

Configuration - ent12c1_dgb

  Protection Mode: MaxAvailability
  Members:
  ent12c1  - Primary database
    fs12c1   - Far sync instance
      ent12c1s - Physical standby database

  Members Not Receiving Redo:
  fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 50 seconds ago)

DGMGRL> switchover to ent12c1s
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1s" on database "ent12c1s"
Connecting to instance "ent12c1s"...
Connected as SYSDBA.
New primary database "ent12c1s" is opening...
Operation requires start up of instance "ent12c1" on database "ent12c1"
Starting instance "ent12c1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1s"

DGMGRL> show configuration

Configuration - ent12c1_dgb

  Protection Mode: MaxAvailability
  Members:
  ent12c1s - Primary database
    fs12c1s  - Far sync instance
      ent12c1  - Physical standby database

  Members Not Receiving Redo:
  fs12c1   - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 2 seconds ago)

DGMGRL> switchover to ent12c1
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1" on database "ent12c1"
Connecting to instance "ent12c1"...
Connected as SYSDBA.
New primary database "ent12c1" is opening...
Operation requires start up of instance "ent12c1s" on database "ent12c1s"
Starting instance "ent12c1s"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1"
This creates a alternate route between primary and standby when far sync fails. In this case redo shipment happens in SYNC mode. When far sync is up
SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME                                STATUS    DESTINATION                    TRANSMIT_MOD
---------------------------------------- --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1                       VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2                       VALID     fs12c1tns                      PARALLELSYNC
LOG_ARCHIVE_DEST_3                       ALTERNATE ent12c1stns                    PARALLELSYNC
After far sync fails
SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME                                STATUS    DESTINATION                    TRANSMIT_MOD
---------------------------------------- --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1                       VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2                       ALTERNATE fs12c1tns                      PARALLELSYNC
LOG_ARCHIVE_DEST_3                       VALID     ent12c1stns                    PARALLELSYNC
Even though this works it's not same as the setup exited before dg broker was used and one of the key points of far sync to avoid sync redo shipping between primary and standby over long distance. It is also not possible to change the redo mode to ASYNC.
DGMGRL> EDIT DATABASE ent12c1 SET PROPERTY RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s ASYNC FALLBACK))';
Error: ORA-16900: change of LogXptMode or RedoRoutes property violates overall protection mode
A SR was raised about this issue. After some investigation Oracle came with a patch "Patch 20695119 MERGE REQUEST ON TOP OF DATABASE PSU 12.1.0.2.2 FOR BUGS 19399918 19571599" (only works of 12.1.0.2.2). After applying this patch the dg broke allowed increasing the protection mode but when a switchover was issued failed with "ORA-00600: internal error code, arguments: [kghstack_underflow_internal_1], [0x2B29FF483BA0], [rfrxpts2dfp.1]". There was no resolution for this from Oracle and SR went nowhere.
Searching MOS it was discovered that recently Oracle has released patch 19571599 (requires 12.1.0.2.4 applied DB Home) which was not available outside of the merge patch earlier. After applying this patch it was possible to create the dg broker configuration same as before.
EDIT DATABASE ent12c1 SET PROPERTY RedoRoutes = '(LOCAL : FS12C1 SYNC ALT=(ent12c1s ASYNC FALLBACK))';
EDIT DATABASE ent12c1s SET PROPERTY RedoRoutes = '(LOCAL : FS12C1s SYNC ALT=(ent12c1 ASYNC FALLBACK))';

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;
Succeeded.

DGMGRL> show configuration

Configuration - ent12c1_dgb

  Protection Mode: MaxAvailability
  Members:
  ent12c1  - Primary database
    fs12c1   - Far sync instance
      ent12c1s - Physical standby database

  Members Not Receiving Redo:
  fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 35 seconds ago)
With this configuration and patches in place when the far sync goes down the dg broker lowers the protection mode to maximum performance to reflect the new redo shipping mode of ASYNC. Following entries could be seen on standby's alert log
Primary database is in MAXIMUM PERFORMANCE mode
Changing standby controlfile to MAXIMUM PERFORMANCE mode
When the far sync comes up again the protection mode is upgraded to maximum availability. On far sync alert log
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to MAXIMUM AVAILABILITY mode
Changing standby controlfile to RESYNCHRONIZATION level
On standby's alert log
Primary database is in MAXIMUM PERFORMANCE mode
Changing standby controlfile to MAXIMUM AVAILABILITY mode
Changing standby controlfile to RESYNCHRONIZATION level
Even though standby's protection level goes up and down primary's protection level remains the same at maximum availability and this can cause warning to appear on the dg broker configuation
DGMGRL> show configuration
Configuration - ent12c1_dgb

  Protection Mode: MaxAvailability
  Members:
  ent12c1  - Primary database
    Warning: ORA-16629: database reports a different protection level from the protection mode

    ent12c1s - Physical standby database (alternate of fs12c1)

  Members Not Receiving Redo:
  fs12c1   - Far sync instance
    Warning: ORA-01034: ORACLE not available

  fs12c1s  - Far sync instance

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 31 seconds ago)

DGMGRL> show database ent12c1 statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT
                   *    WARNING ORA-16629: database reports a different protection level from the protection mode
Another issue observed is that at times the alternate route disappear from the log archive dest entries even though the state of the entries remains alternate. On primary (there's no alternate option)
SQL> SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME                      STATUS    DESTINATION                    TRANSMIT_MOD
------------------------------ --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1             VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2             VALID     fs12c1stns                     PARALLELSYNC
This will lead to log transport properties being inconsistent and switchovers failing
DGMGRL> show database ent12c1 InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE
         ent12c1               fs12c1           RedoRoutes                 NONE             ent12c1s
When standby becomes primary
DGMGRL> show database ent12c1s InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
   INSTANCE_NAME         STANDBY_NAME        PROPERTY_NAME         MEMORY_VALUE         BROKER_VALUE
        ent12c1s              fs12c1s           RedoRoutes                 NONE              ent12c1
Show database would have entries similar to following
Instance(s):
    ent12c1s
      Error: ORA-16736: unable to find the destination entry of standby database "ent12c1" in V$ARCHIVE_DEST
      Warning: ORA-16715: redo transport-related property RedoRoutes of standby database "fs12c1s" is inconsistent
      Warning: ORA-16728: consistency check for property LogXptMode found ORA-16777 error
      Warning: ORA-16777: unable to find the destination entry of a standby database in V$ARCHIVE_DEST
One of the easiest way to resolve this is to disable and enable redo transport on the primary
DGMGRL>  EDIT DATABASE ent12c1s SET state='transport-off';
DGMGRL> EDIT DATABASE ent12c1s SET state='transport-on';
But this may not be possible when the protection mode is maximum availability. In such cases simply enabling transport, while it's already on works
DGMGRL> edit database ent12c1s set state='transport-off';
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed.

DGMGRL> edit database ent12c1s set state='transport-on';
Succeeded.
Check if the alternate route appears on the archive destination
SQL>  SELECT DEST_NAME,STATUS,DESTINATION,TRANSMIT_MODE FROM V$ARCHIVE_DEST WHERE DESTINATION IS NOT NULL;

DEST_NAME                      STATUS    DESTINATION                    TRANSMIT_MOD
------------------------------ --------- ------------------------------ ------------
LOG_ARCHIVE_DEST_1             VALID     USE_DB_RECOVERY_FILE_DEST      SYNCHRONOUS
LOG_ARCHIVE_DEST_2             VALID     fs12c1stns                     PARALLELSYNC
LOG_ARCHIVE_DEST_3             ALTERNATE ent12c1tns                     ASYNCHRONOUS
Continue with the switchover
DGMGRL> switchover to ent12c1;
Performing switchover NOW, please wait...
Operation requires a connection to instance "ent12c1" on database "ent12c1"
Connecting to instance "ent12c1"...
Connected as SYSDBA.
New primary database "ent12c1" is opening...
Operation requires start up of instance "ent12c1s" on database "ent12c1s"
Starting instance "ent12c1s"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "ent12c1"

Related Posts
Creating Data Guard Broker on 12c
Adding Far Sync Instances to Existing Data Guard Configuration
11gR2 RAC to RAC Data Guard
11gR2 Standalone Data Guard (with ASM and Role Separation)

Useful metalink notes
Cascaded Standby Databases in Oracle 12c [ID 1542969.1]
Create Configuration Failing with ORA-16698 [ID 1582179.1]
Data Guard 12c New Feature: Far Sync Standby [ID 1565071.1]
12c Create Dataguard Broker Configuration - DGMGRL [ID 1583588.1]
Best Practices for Corruption Detection, Prevention, and Automatic Repair - in a Data Guard Configuration [ID 1302539.1]
Configuring Active Data Guard Far Sync Using a Terminal Standby Database as Alternate Log Archive Destination in Data Guard Broker [ID 1918316.1]