By Mouhamadou Diaw
When dealing with cascading or far sync in a Data Guard environment, it is important to understand how to configure the RedoRoutes property.
By default, a primary database sends redo to each transport destination that is configured in the destination. We can create more complex transport topology, depending of our environment, using the RedoRoutes property.
Basically the RedoRoutes property has this format
1
|
(redo_routing_rule_1) [(redo_routing_rule_n)] |
Where each routing rule contains a redo source field and a redo destination field separated by a colon:
1
|
(redo source : redo destination) |
One can have more information in Oracle documentation
In this blog I am trying to simply explain how to configure the RedoRoutes property in a Data Guard environment with Far Sync Instance. See my previous blog for far sync instance creation.
I am using Oracle 20c.
The first configuration we consider is the following one
We have
1 primary database: prod20_site1
2 standby databases: prod20_site2 and prod20_site4
1 far sync instance fs_site3
For far sync creation with Oracle 20c see my previous blog
Below the status of the broker configuration
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
DGMGRL> show configuration Configuration - prod20 Protection Mode: MaxAvailability Members: prod20_site1 - Primary database prod20_site2 - Physical standby database prod20_site4 - Physical standby database fs_site3 - Far sync instance Fast-Start Failover: Disabled Configuration Status: SUCCESS (status updated 47 seconds ago) |
Actually, there is no configured RedoRoutes
1
2
3
4
5
6
7
|
DGMGRL> show database prod20_site1 redoroutes; RedoRoutes = '' DGMGRL> show database prod20_site2 redoroutes; RedoRoutes = '' DGMGRL> show database prod20_site4 redoroutes; RedoRoutes = '' DGMGRL> |
For this configuration I want the primary database to send the redo according following rules
prod20_site2 will receive redo directly from prod20_site1
prod20_site1 =====> prod20_site2
prod20_site4 will receive redo via fs_site3 which will forward redo to prod20_site4
prod20_site1 =====> fs_site3 =====> prod20_site4
and if fs_site3 is not available, prod20_site4 will receive directly redo from prod20_site1
prod20_site1 =====> prod20_site4
For this we have to first edit the primary database RedoRoutes property like
1
2
|
DGMGRL> edit database prod20_site1 set property redoroutes= '(local:prod20_site2,(fs_site3 priority=1,prod20_site4 priority=2))' ; Property "redoroutes" updated |
In this rule we have these meanings
local:prod20_site2: if prod20_site1 is the primary database then redo will be sent to prod20_site2
local: (fs_site3 priority=1,prod20_site4 priority=2 ): if prod20_site1 is the primary database then redo will be sent to fs_site3 or to prod20_site4. As the priority of the fs_site3 is higher, indeed smaller priority numbers mean higher priority, redo will be sent first to fs_site3, and if fs_site3 is unavailable, changes will be sent to prod20_site4.
Just note that as fs_site3 has a higher priority, if fs_site3 becomes available, redo will be again sent to fs_site3.
And then we have to tell to fs_site3 to forward redo received from prod20_site1 to prod20_site4.
1
2
|
DGMGRL> edit far_sync fs_site3 set property redoroutes= '(prod20_site1:prod20_site4 ASYNC)' ; Property "redoroutes" updated |
Below the redoroutes we have configured for prod20_site1 and fs_site3
1
2
3
4
5
6
7
8
9
|
DGMGRL> show database prod20_site1 redoroutes; RedoRoutes = '(local:prod20_site2,(fs_site3 priority=1,prod20_site4 priority=2))' DGMGRL> show database prod20_site2 redoroutes; RedoRoutes = '' DGMGRL> show database prod20_site4 redoroutes; RedoRoutes = '' DGMGRL> show far_sync fs_site3 redoroutes; RedoRoutes = '(prod20_site1:prod20_site4 ASYNC)' DGMGRL> |
And we can verify the status of our configuration
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
DGMGRL> show configuration verbose Configuration - prod20 Protection Mode: MaxPerformance Members: prod20_site1 - Primary database prod20_site2 - Physical standby database fs_site3 - Far sync instance prod20_site4 - Physical standby database prod20_site4 - Physical standby database (alternate of fs_site3) … … Fast-Start Failover: Disabled Configuration Status: SUCCESS DGMGRL> |
Let’s now consider this configuration where we have two far syn instances. As in the first configuration, we want to send first the redo to far sync instances if possible, otherwise redo will be send directly to standby databases
The RedoRoutes property of the primary can be configured as below
1
2
3
4
5
|
DGMGRL> edit database prod20_site1 set property redoroutes= '(local:(fs_site5 priority=1,prod20_site2 priority=2),(fs_site3 priority=1,prod20_site4 priority=2))' ; Warning: ORA-16677: Standby database has the same or higher priority than other members specified in the RedoRoutes group . Property "redoroutes" updated DGMGRL> |
And the redoroutes for the far sysnc fs_site5 can be adjusted like
1
2
3
|
DGMGRL> edit far_sync fs_site5 set property redoroutes= '(prod20_site1:prod20_site2 ASYNC)' ; Property "redoroutes" updated DGMGRL> |
We can then verify the satus of the configuration
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
DGMGRL> show configuration verbose Configuration - prod20 Protection Mode: MaxPerformance Members: prod20_site1 - Primary database fs_site5 - Far sync instance prod20_site2 - Physical standby database prod20_site2 - Physical standby database (alternate of fs_site5) fs_site3 - Far sync instance prod20_site4 - Physical standby database prod20_site4 - Physical standby database (alternate of fs_site3) … … Fast-Start Failover: Disabled Configuration Status: SUCCESS DGMGRL> |
As we can see when configuring RedoRoutes, we sometimes have to deal with the property PRIORITY.
This property can have a value between 1 and 8. 1 as the highest priority and 8 the lowest priority.
Let’s consider two destination A and B in the same group
Case 1: A and B with the same prority
Redo will be sent to A or B, let’s say A. When A is unavailable, redo will be then sent to B. And when A become reachable again, redo will continue to be sent to B.
1
|
( local :(A priority=1,B priority=1)) |
Case 2: A has a higher priority then B
Redo will be sent to A. If A becomes unavailable, redo will be sent to B. And if A becomes again reachable, redo will be sent to A as it has the highest priority
1
|
( local :(A priority=1,B priority=2)) |
But sometimes in the same group, we may want to send redo to both members. For example if we consider the following configuration, we just want that redo will be sent to fs_site3 if possible and if fs_site3 is not reachable then changes will be sent to both prod20_site2 et prod20_site4.
In this case we can use the PRIORITY 8 which has a special meaning. If the primary sends redo to a member with PRIORITY 8, then it must also send these redo to each member with the PRIORITY 8 in the group
In the configuration above, we want following rules
prod20_site1 will send changes to fs_site3 which will forward to prod20_site2 and prod20_site4 and if fs_site3 is not avalaible, prod20_site1 will ship redo to both standby databases.
And when fs_site3 becomes again available, redo will be send again to fs_site3
The redoRoutes for the primary database can be like
1
2
3
4
5
|
DGMGRL> edit database prod20_site1 set property redoroutes= '(local:(fs_site3 priority=1,prod20_site2 priority=8,prod20_site4 priority=8))' ; Warning: ORA-16677: Standby database has the same or higher priority than other members specified in the RedoRoutes group . Property "redoroutes" updated DGMGRL> |
And for the far sync instance
1
2
3
|
DGMGRL> edit far_sync fs_site3 set property redoroutes= '(prod20_site1:prod20_site2 ASYNC,prod20_site4 ASYNC)' ; Property "redoroutes" updated DGMGRL> |
The status of the configuration
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
DGMGRL> show configuration verbose Configuration - prod20 Protection Mode: MaxPerformance Members: prod20_site1 - Primary database fs_site3 - Far sync instance prod20_site2 - Physical standby database prod20_site4 - Physical standby database prod20_site2 - Physical standby database (alternate of fs_site3) prod20_site4 - Physical standby database (alternate of fs_site3) … … Fast-Start Failover: Disabled Configuration Status: SUCCESS DGMGRL> |
Conclusion
Depending to the configuration, the redo transport topology can be very complex. What I can recommend when dealing with far sync instances, is to think about all possible cases, including switchover and failover. And based of all possible cases to design an architecture for the redo transport. In this blog we just consider the case when prod20_site1 is the primary.