Monday, December 23, 2019

Oracle - Alternate Destinations, FRA, and balancing 2 destinations


My previous post was on using Groups/Priorities when specifying an alternate destination for sending Redo to a remote destination. You can find it here.

This post covers 2 topics that come up whenever I talk about alternate destinations.

  • Does it balance 2 destinations with the same group/priority. and once it choses a destination, does it remain sticky to that destination and ignore other destinations with the same "group/priority"
  • Does my FRA know both destinations can be considered the same for "shipped" status?


First I will show you what I configured my tests.

In my example, I actually have both a standby database and a Far Sync instance for my primary database.

bsg18   --> Primary database
bsg18d --> Dataguard database
bsg18f  --> Far Sync database

This is what my 2 remote destinations look like in my configuration.


log_archive_dest_2 ='service="bsg18d" ASYNC NOAFFIRM max_failure=0 db_unique_name="bsg18d" group=1 priority=1 valid_for=(online_logfile,all_roles)';

log_archive_dest_3 ='Service="bsg18f" SYNC AFFIRM  max_failure=1 db_unique_name="bsg18f" group=1 priority=1 valid_for=(online_logfile,all_roles)';


You can see that both Dataguard (bsg18d) and Far Sync (bsg18f) are in "group=1 priority=1"

Multiple Destinations with the same Group and Priority - Does it balance them ?


First I'm going to look at where the logs are going.

   DEST_ID  SEQUENCE# S SHI APPLIED   DEL FAL
---------- ---------- - --- --------- --- ---
         2       1751 A YES NO        NO  NO
         2       1752 A YES NO        NO  NO
         2       1753 A YES NO        NO  NO


I can see that they are going to DEST_2, which is my dataguard instance.  I am going to shut it down, do a few log switches and see what happens.

   DEST_ID  SEQUENCE# S SHI APPLIED   DEL FAL
---------- ---------- - --- --------- --- ---
         2       1751 A YES YES       NO  NO
         1       1751 A YES NO        NO  NO
         1       1752 A YES NO        NO  NO
         2       1752 A YES YES       NO  NO
         1       1753 A YES NO        NO  NO
         2       1753 A YES YES       NO  NO
         1       1754 A YES NO        NO  NO
         2       1754 A YES NO        NO  NO
         1       1755 A YES NO        NO  NO
         1       1756 A YES NO        NO  NO
         1       1757 A YES NO        NO  NO
         1       1758 A YES NO        NO  NO
         1       1759 A YES NO        NO  NO
         1       1760 A YES NO        NO  NO


I've including DEST_1 this time to show that my sequence# had moved forward, but any sending of redo my standby has stopped.

Now I started it back up, and did a few log switches.

  DEST_ID  SEQUENCE# S SHI APPLIED   DEL FAL
---------- ---------- - --- --------- --- ---
         2       1751 A YES YES       NO  NO
         2       1752 A YES YES       NO  NO
         2       1753 A YES YES       NO  NO
         2       1754 A YES YES       NO  NO
         2       1761 A YES NO        NO  NO
         3       1755 A YES YES       NO  YES
         3       1758 A YES NO        NO  YES
 .....
         3       1754 A YES NO        NO  YES
         3       1752 A YES NO        NO  YES
         3       1762 A YES NO        NO  NO
         3       1763 A YES NO        NO  NO


After restarting the database, I can see that it is now using DEST_3 (my Far Sync) instance instead of  DEST_2 (dataguard).

Now for a final test on this, I am going to STOP my Far Sync instance.

   DEST_ID  SEQUENCE# S SHI APPLIED   DEL FAL
---------- ---------- - --- --------- --- ---
         2       1751 A YES YES       NO  NO
         2       1752 A YES YES       NO  NO
         2       1753 A YES YES       NO  NO
         2       1754 A YES YES       NO  NO
         2       1761 A YES YES       NO  NO
         3       1755 A YES YES       NO  YES
         3       1758 A YES NO        NO  YES
 ....
         3       1754 A YES YES       NO  YES
         3       1752 A YES YES       NO  YES
         3       1762 A YES NO        NO  NO
         3       1763 A YES NO        NO  NO
         2       1764 A YES NO        NO  YES
         2       1765 A YES NO        NO  NO
         2       1766 A YES NO        NO  NO


I stopped bsg18f (Far Sync), when it was on sequence 1764.  I can see that the database automatically switched to sending logs to bsg18d (dataguard) when DEST_3 because unavailable. 

This is exactly what I would expect to happen when I have 2 destinations with the same Group/Priority.
  • It chooses one of the 2 destinations and becomes "sticky" once chosen.
  • If the "sticky" destination becomes unavailable, it then automatically switches to the second destination.

FRA - If you use an FRA (Fast Recovery Area), how does it understand that I my redo can go to an alternate location?

Now after all this, you can see from my previous tests, some logs were sent from DEST_2 and some were sent from DEST_3.  Both of these were members of the same group.
Let's see if the FRA sees them as shipped (my retention policy).

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                         21.78                     21.78             555          0
BACKUP PIECE                          7.54                       .45              38          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                            0                         0               0          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0



Perfect !  All the archive log space is reclaimable.  When I have different remote destinations that are members of the same group, as long as the Redo Log was successfully shipped to a destination in the group, the log is eligible for deletion.

Thursday, December 19, 2019

ZDLRA alternate redo destination replaced with Groups/Priority in 12.2

Alternate destination is depreciated in 12.2 for remote locations.

This diagram shows the use of an alternate destination for sending logs.
An alternate destination can be local to the database (to be used if the disk location fills up), but they can also be used to change the remote destination that the logs get sent to.

Configuring an "ALTERNATE" for a local destination is still supported

Below is what this looks like from the 18C manual.

LOG_ARCHIVE_DEST_1='LOCATION=/disk1 MANDATORY MAX_FAILURE=1 
ALTERNATE=LOG_ARCHIVE_DEST_2'
 
LOG_ARCHIVE_DEST_STATE_1=ENABLE
 
LOG_ARCHIVE_DEST_STATE_2=ALTERNATE

LOG_ARCHIVE_DEST_2='LOCATION=/disk2 MANDATORY'

In this example, if /disk1 is not available (fills up, etc). the database will switch to sending logs to /disk2 so that the database will not get hung.

HOWEVER, for remote destinations AKA destinations using "SERVICE" , this changes with 12.2

12.2 introduces the idea of "GROUP" and "PRIORITY". in place of a simple "ALTERNATE".
I'm sure this was added for Far Sync but it also comes into play with the ZDLRA.

Using only "ALTERNATE" gives you the ability to switch to a single remote destination. Using GROUP and PRIORITY lets you set multiple destinations and decide the order in which they are used.

Now let's start with simple example with FAR SYNC.
I have 2 locations. San Francisco (SF) is Primary with 2 local farsync servers, and my remote destination is New York (NYC).


I can group them together and set the priority that they use. I can even switch from sync to async.  Below is what the new syntax would look like for the destinations.

LOG_ARCHIVE_DEST_2='SERVICE=FARSYNC1 SYNC GROUP=1 PRIORITY=1'
LOG_ARCHIVE_DEST_3='SERVICE=FARSYNC2 SYNC GROUP=1 PRIORITY=1'
LOG_ARCHIVE_DEST_4='SERVICE=NYC ASYNC GROUP=1 PRIORITY=2’

This behaves in the following manner.


  • My database in San Francisco sends archive logs to EITHER of the two services specified as PRIORITY=1.  The log could go to FARSYNC1 or FARSYNC2 and get passed on to NYC.
  • If either of these 2 PRIORITY=1 destinations becomes unavailable the other will continue to work.
  • If both of these 2 PRIORITY=1 destinations become unavailable, the redo will be sent to NYC as ASYNC.
  • If NYC is the destination (because of a double failure), it will switch back to the either of the 2 FARSYNC services when they become available.
You can add up to 8 layers of PRIORITY and include a remote FARSYNC server if you would like.

Now, what does this have to do with ZDLRA ?

You can also use an alternate destination for redo traffic on the ZDLRA.  The alternate can be the downstream ZDLRA if replication is used, or it can be the upstream ZDLRA if store-and-forward is being used.  These are outlined in the ZDLRA admin guide under "Implementing Additional High Availability Strategies".

In the case of the ZDLRA you would use 2 destinations.
The first destination is the primary ZDLRA, and the second destination is the alternate ZDLRA.
The configuration would look like this.


LOG_ARCHIVE_DEST_2='SERVICE=ZDLRA_SF_SCAN:1521/ZDLRASF:dedicated" ASYNC GROUP=1 PRIORITY=1'
LOG_ARCHIVE_DEST_3='SERVICE=ZDLRA_NYC_SCAN:1521/ZDLRANYC:dedicated" ASYNC GROUP=1 PRIORITY=2'



This is an interesting change to the ALTERNATE setting on remote destinations.