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 transportAnother 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 addedThis 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:
DISABLED3. 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: Off6. 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 COMPLETEHowever 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 specifiedDGMGRL> 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 ASYNCCheck if the alternate location is visible on the v$archive_dest view of the primary databaseSQL> 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 ASYNCHRONOUSIn 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 ASYNCHRONOUS10. 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 SYNCHRONOUSEven 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 ASYNCHRONOUSWhen 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 modeThere 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" updatedOn 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 PARALLELSYNCOn 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 SYNCHRONOUSIncrease 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 upSQL> 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 PARALLELSYNCAfter 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 PARALLELSYNCEven 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 modeA 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 logPrimary database is in MAXIMUM PERFORMANCE mode Changing standby controlfile to MAXIMUM PERFORMANCE modeWhen 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 levelOn standby's alert log
Primary database is in MAXIMUM PERFORMANCE mode Changing standby controlfile to MAXIMUM AVAILABILITY mode Changing standby controlfile to RESYNCHRONIZATION levelEven 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 modeAnother 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 PARALLELSYNCThis 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 ent12c1sWhen standby becomes primaryDGMGRL> show database ent12c1s InconsistentLogXptProps;
INCONSISTENT LOG TRANSPORT PROPERTIES
INSTANCE_NAME STANDBY_NAME PROPERTY_NAME MEMORY_VALUE BROKER_VALUE
ent12c1s fs12c1s RedoRoutes NONE ent12c1Show database would have entries similar to followingInstance(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_DESTOne of the easiest way to resolve this is to disable and enable redo transport on the primaryDGMGRL> 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 ASYNCHRONOUSContinue 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]
