Use Case 5: Backup and Restore a Multi-Region Table

An organization deploys three on-premise data stores, one each at Frankfurt, London, and Dublin, and they have created MR Tables spanning all three regions. A multi-region table is a single table that spans multiple regions and is kept in sync all the time.

One day due to an application bug or illegal modifications, the organization suffers table-level data loss or corruption for an MR table in one region. Since the system keeps the MR table in sync in all the regions, the corruption or data loss gets replicated that to the other regions as well. Therefore, the organization wants to restore the MR table in all the regions from the MR table backup that they have been regularly creating as part of their data safety policy.

In this topic, you learn how to backup and restore an MR Table.

Backup a Multi-Region Table

Creating a backup of MR tables helps you in restoring the table data later in case you suffer inadvertent application corruption of the data.

To create a backup of an MR table:

1. Using multi-region table statistics, find the most up-to-date region for the table that you want to backup. Run the following command in the Admin Command Line Interface (CLI):

   show mrtable-agent-statistics -agent 0 -json

   For more information about MR table statistics, see show mrtable-agent-statistics.

2. In the statistics returned by the show mrtable-agent-statistics command, locate "returnValue"[]."statistics"."regionStat"[]."laggingMs"."max" attribute and find the region that has the smallest value for the max attribute in the laggingMS field. That region contains the most up-to-date data of your MR table.
   In the example below, the Frankfurt region has the smallest value for the max attribute in the laggingMS field, and hence it has the most up-to-date data for the MR table.

   kv-> show mrtable-agent-statistics -agent 0 -json
   {
     "operation": "show mrtable-agent-statistics",
     "returnCode": 5000,
     "description": "Operation ends successfully",
     "returnValue": {
       "XRegionService-1_0": {
         "timestamp": 1592901180001,
         "statistics": {
           "agentId": "XRegionService-1_0",
           "beginMs": 1592901120001,
           "dels": 1024,
           "endMs": 1592901180001,
           "incompatibleRows": 100,
           "intervalMs": 60000,
           "localRegion": "slc1",
           "persistStreamBytes": 524288,
           "puts": 2048,
           "regionStat": {
             "fra": {
               "completeWriteOps": 10,
               "laggingMs": {
                 "avg": 502,
                 "max": 885,
                 "min": 26
               },
               "lastMessageMs": 1591594977587,
               "lastModificationMs": 1591594941686,
               "latencyMs": {
                 "avg": 20,
                 "max": 40,
                 "min": 10
               }
             },           
             "lnd": {
               "completeWriteOps": 10,
               "laggingMs": {
                 "avg": 512,
                 "max": 998,
                 "min": 31
               },
               "lastMessageMs": 1591594977587,
               "lastModificationMs": 1591594941686,
               "latencyMs": {
                 "avg": 20,
                 "max": 40,
                 "min": 10
               }
             },
             "dub": {
               "completeWriteOps": 20,
               "laggingMs": {
                 "avg": 535,
                 "max": 1024,
                 "min": 45
               },
               "lastMessageMs": 1591594978254,
               "lastModificationMs": 1591594956786,
               "latencyMs": {
                 "avg": 30,
                 "max": 45,
                 "min": 15
               }
             }
           },
           "requests": 12,
           "responses": 12,
           "streamBytes": 1048576,
           "winDels": 1024,
           "winPuts": 2048
         }
       }
     }
   }

3. Using the Oracle NoSQL Database Migrator connect to the region identified in Step#2 in the Source configuration to export the MR tables. And use the appropriate Sink type based on your requirement to import the MR tables. For more information on the source and sink see, Using Oracle NoSQL Data Migrator.

   Note:

   Make sure that you save the backup of the MR table on remote storage, which is not local to a NoSQL Storage Node in the NoSQL topology.

Restore a Multi-Region Table

You can restore an MR table from an MR table backup in case you suffer data loss or data corruption and want to revert to the most up-to-date version of the MR table.

Tip:

Oracle recommends that you stop all the write activity to the MR tables that are being restored.

To restore an MR table from backup:

1. Find the list of regions associated with the MR Table by executing the following command from the kv prompt.

   kv-> SHOW TABLE -NAME <table name>

   For example,

   kv-> SHOW TABLE -NAME users
   {
     "json_version": 1,
     "type": "table",
     "name": "users",
     "regions": {
       "1": "fra",
       "2": "lnd"
     },
    .....
    .....
   }

2. Using the DROP TABLE statement, drop the MR table in each region with which the MR table is associated. For more information on how to drop an MR table, see Drop Tables and Indexes.
3. Re-create the MR table in every region, specifying the remote regions you want to associate with the MR table. For more information, see Create Multi-Region Tables.
4. Using the Oracle NoSQL Database Migrator connect to any one region identified in Step#1 in the Sink configuration to restore the MR tables. And use the appropriate Source configuration type based on the where the MR table backup resides. During the loading of the backup, the Oracle NoSQL Database synchronizes the table in each remote region. For more information on see, Using Oracle NoSQL Data Migrator.