Thursday, November 17, 2016

Using Standby as Alternate for Far Sync,

limitations & Considerations:

Based on Oracle 12.1

The Recommended Data Guard configuration is in Maximum Availability mode, when using Far Sync which is located near the Primary:

Primary à Far Sync - network I/O is synchronously (SYNC)

Far Sync à Standby - network I/O is asynchronously (ASYNC)

Primary à Standby – As alternate (when Far Sync is not reachable) network I/O is asynchronously (ASYNC)
 


SQL way

·        Primary

Must have parameters

Parameter
Value
Remarks
LOG_ARCHIVE_DEST_2

point to Far Sync service
Alternate
log_archive_dest_3
changing to standby destination after failures mentioned in the next parameter
max_failure
1
important to be more then 0 (infinite) and not too much since it should fail after n attempts and failover to the next destination mentioned in alternate parameter.
SYNC





LOG_ARCHIVE_DEST_STATE_2
enable

LOG_ARCHIVE_DEST_3

point to Standby service
Alternate
log_archive_dest_2
changing to standby destination after failures mentioned in the next parameter
max_failure
0
important 0 means infinite, it will not give up trying.
ASYNC


LOG_ARCHIVE_DEST_STATE_3
alternate

Recommended parameters

Parameter
Value
Remarks
compression
Enabled
Depend if you have Advanced Compression license
max_connection
4

net_timeout
30

Reopen
15

max_connections
4

AFFIRM / NOAFFIRM



Example:
ALTER SYSTEM SET log_archive_dest_2='service="[FARSYNC_INST]", SYNC AFFIRM delay=0 optional compression=disable max_failure=1 max_connections=1 reopen=15 db_unique_name="[FARSYNC_INST]" net_timeout=30 alternate=LOG_ARCHIVE_DEST_3 valid_for=(online_logfile,all_roles)';
ALTER SYSTEM SET log_archive_dest_state_2=enable;
ALTER SYSTEM SET log_archive_dest_3='service="[STANDBY_INST]", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=15 db_unique_name="[STANDBY_INST]" net_timeout=30 alternate=LOG_ARCHIVE_DEST_2 valid_for=(online_logfile,all_roles)';
ALTER SYSTEM SET log_archive_dest_state_2=alternate;

·        Far Sync

Must have parameters

Parameter
Value
Remarks
LOG_ARCHIVE_DEST_2

point to Standby service
max_failure
0
important to be more then 0 (infinite) and not too much since it should fail after n attempts and failover to the next destination mentioned in alternate parameter.
ASYNC


NOAFFIRM


LOG_ARCHIVE_DEST_STATE_2
enable

Recommended parameters

Parameter
Value
Remarks
compression
Enabled
Depend if you have Advanced Compression license
max_connection
4

net_timeout
30

Reopen
15

max_connections
4


Example:
ALTER SYSTEM SET log_archive_dest_2='service="[STANDBY_INST]", ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=4 reopen=15 db_unique_name="[STANDBY_INST]" net_timeout=30 valid_for=(standby_logfile,all_roles)';
ALTER SYSTEM SET log_archive_dest_state_2=enable;
ALTER SYSTEM SET log_archive_dest_3='service="[STANDBY_INST]", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=15 db_unique_name="[STANDBY_INST]" net_timeout=30 alternate=LOG_ARCHIVE_DEST_2 valid_for=(online_logfile,all_roles)';
ALTER SYSTEM SET log_archive_dest_state_2=alternate;

·        Standby

Must have parameters

Parameter
Value
Remarks
LOG_ARCHIVE_DEST_2

Point to Far Sync service – only if you have local/near Far Sync.
Otherwise - point to Original Primary service.
Set this only when using switchover
Alternate
log_archive_dest_3
changing to original primary destination after failures mentioned in the next parameter
Set this only when using switchover and if Far Sync were used as service destination
max_failure
1
important to be more then 0 (infinite) and not too much since it should fail after n attempts and failover to the next destination mentioned in alternate parameter.
SYNC





LOG_ARCHIVE_DEST_STATE_2
enable
Set this only when using switchover.
LOG_ARCHIVE_DEST_3

point to original Primary service.
Set this only when using switchover and if Far Sync were used as service destination.
Alternate
log_archive_dest_2
changing to original Primary destination after failures mentioned in the next parameter.
Set this only when using switchover and if Far Sync were used as service destination.
max_failure
0
important 0 means infinite, it will not give up trying.
ASYNC


LOG_ARCHIVE_DEST_STATE_3
alternate
Set this only when using switchover.

Recommended parameters

Parameter
Value
Remarks
compression
Enabled
Depend if you have Advanced Compression license
max_connection
4

net_timeout
30

Reopen
15

max_connections
4

AFFIRM / NOAFFIRM



Example:
ALTER SYSTEM SET log_archive_dest_2='service="[FARSYNC_INST]", SYNC AFFIRM delay=0 optional compression=disable max_failure=1 max_connections=1 reopen=15 db_unique_name="[FARSYNC_INST]" net_timeout=30 alternate=LOG_ARCHIVE_DEST_3 valid_for=(online_logfile,all_roles)';
ALTER SYSTEM SET log_archive_dest_state_2=enable;
ALTER SYSTEM SET log_archive_dest_3='service="[PRIMARY_INST]", SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=15 db_unique_name="[PRIMARY_INST]" net_timeout=30 alternate=LOG_ARCHIVE_DEST_2 valid_for=(online_logfile,all_roles)';
ALTER SYSTEM SET log_archive_dest_state_2=alternate;

Broker Way

Must have parameters

Parameter
Value
Remark
Target Type
MaxFailure
0

Primary + Standby
MaxFailure
1

Far Sync
RedoRoutes
'([PRIMARY_INST] : [STANDBY_INST] ASYNC)([STANDBY_INST] : [PRIMARY_INST] ASYNC)'

Far Sync
RedoRoutes
'(LOCAL : [FARSYNC_INST] SYNC ALT =( [PRIMARY_INST] ASYNC FALLBACK))'

Standby
RedoRoutes
'(LOCAL : [FARSYNC_INST] SYNC ALT =( [STANDBY_INST] ASYNC FALLBACK))'

Primary

Recommended parameters

Parameter
Value
Remark
Target Type
ReopenSecs
15

Primary + Far Sync + Standby
LogArchiveMaxProcesses
4

Primary + Far Sync + Standby
PROTECTION MODE
MAXAVAILABILITY



Example:
EDIT DATABASE [PRIMARY_INST] SET PROPERTY NetTimeout = 30;
EDIT DATABASE [STANDBY_INST] SET PROPERTY NetTimeout = 30;

EDIT FAR_SYNC [FARSYNC_INST] SET PROPERTY RedoRoutes = '([PRIMARY_INST] : [STANDBY_INST] ASYNC)([STANDBY_INST] : [PRIMARY_INST] ASYNC)';

EDIT DATABASE [PRIMARY_INST] SET PROPERTY MaxFailure = 0;
EDIT FAR_SYNC [FARSYNC_INST] SET PROPERTY MaxFailure = 1;
EDIT DATABASE [STANDBY_INST] SET PROPERTY MaxFailure = 0;
EDIT DATABASE [STANDBY_INST] SET PROPERTY RedoRoutes = '(LOCAL : [FARSYNC_INST] SYNC ALT =( [PRIMARY_INST] ASYNC FALLBACK))';   

EDIT DATABASE [PRIMARY_INST] SET PROPERTY RedoRoutes = '(LOCAL : [FARSYNC_INST] SYNC ALT =( [STANDBY_INST] ASYNC FALLBACK))';


EDIT DATABASE [PRIMARY_INST] SET PROPERTY ReopenSecs = '15';
EDIT DATABASE [STANDBY_INST] SET PROPERTY ReopenSecs = '15';
EDIT FAR_SYNC [FARSYNC_INST] SET PROPERTY ReopenSecs = '15';

EDIT DATABASE [PRIMARY_INST] SET PROPERTY LogArchiveMaxProcesses = '4';
EDIT DATABASE [STANDBY_INST] SET PROPERTY LogArchiveMaxProcesses = '4';
EDIT FAR_SYNC [FARSYNC_INST] SET PROPERTY LogArchiveMaxProcesses = '4';

EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;