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.
- 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.
- 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 themax
attribute in thelaggingMS
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 themax
attribute in thelaggingMS
field, and hence it has the most up-to-date data for the MR table.
Output: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 } } } }
- 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
Tip:
Oracle recommends that you stop all the write activity to the MR tables that are being restored.- 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,
Output:kv-> SHOW TABLE -NAME users
{ "json_version": 1, "type": "table", "name": "users", "regions": { "1": "fra", "2": "lnd" }, ..... ..... }
- 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 Using TableRequest API to drop table. - 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.
- 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.