Use Case 1: Set up Multi-Region Environment

Deploy KVStore

In each region in the Multi-Region NoSQL Database setup, you must deploy its own KVStore independently.

To deploy the KVStore:

Follow the instructions given in Configuration Overview.
After deploying the KVStore of your desired topology, you can check the health of the KVStore by executing the ping command from the command line interface.
```
-bash-4.1$ java -jar $KVHOME/lib/kvstore.jar ping -port <port number> -host <host name>
```
You can also verify the topology of the KVStore by executing the show topology command from the kv prompt. See show topology.
```
kv-> show topology
```

Set Local Region Name

Learn how to set a name to the local region in a Multi-Region NoSQL Database.

After deploying the KVStore and before creating the first MR Table in each participating region, you must set a local region name. You can change the local region name as long as no MR Tables are created in that region. After creating the first MR Table, the local region name becomes immutable.

To set the local region name:

Connect to the kv prompt from the local region, and connect to the local KVStore.

Execute the following command from the kv prompt.

kv-> execute 'SET LOCAL REGION <localregion name>'

Optionally, you can execute the following command to verify that the local region name is set successfully.
```
kv-> execute 'show regions'
```

Setting the local region name from the Frankfurt (FRA) region:

[oracle@host1]$ java -jar $KVHOME/lib/kvstore.jar runadmin \ 
-helper-hosts host1:5000,host2:5000,host5:5000
 
kv-> connect store -name earnback 
Connected to earnback at host1:5000.
 
kv-> execute 'SET LOCAL REGION FRA' 
Statement completed successfully
 
kv-> execute 'show regions' 
regions
 
FRA (local, active)

Setting the local region name from the London (LND) region:

[oracle@host3]$ java -jar $KVHOME/lib/kvstore.jar runadmin \
-helper-hosts host3:5000,host4:5000,host6:5000
 
kv-> connect store -name earnback
Connected to earnback at host3:5000.
 
kv-> execute 'SET LOCAL REGION LND' 
Statement completed successfully
 
kv-> execute 'show regions' 
regions
 
LND (local, active)

Configure XRegion Service

Learn how to configure the XRegion Service in a Multi-Region Oracle NoSQL Database

Before creating any MR Table, you must deploy an XRegion Service. In simple terms, this is also called an agent. Based on the requirement, you can deploy one or more agents for a region. The XRegion Service runs independently with the local KVStore and it is recommended to deploy it close to the local KVStore. To know more about agent and agent groups, see Cross-Region Service in the Concepts Guide.

To configure the XRegion Service, execute the following tasks in each region:

Create a home directory for the XRegion Service.

Create a JSON config file in the home directory created in the step 1. The structure of the json.config file is shown below.

{
  "path" : "<entire path to the home directory for the XRegion Service>",
  "agentGroupSize" : <number of service agents>,
  "agentId" : <agent id using 0-based numbering>,
  "region" : "<local region name>",
  "store" : "<local store name>",
  "helpers" : [ "<host1>:<port>","<host2>:<port>",…,"<hostn>:<port> ],
  "security" : "<entire path to the security file of the local store>",
  "regions" : [ {
    "name" : "<remote region name>",
    "store" : "<remote store name>",
    "security" : "<entire path to the security file of the remote store>",
    "helpers" : [ "<host1>:<port>","<host2>:<port>",…,"<hostn>:<port> ]
  }, {
    "name" : "<remote region name>",
    "store" : "<remote store name>",
    "security" : "<entire path to the security file of the remote store>",
    "helpers" : [ "<host1>:<port>","<host2>:<port>",…,"<hostn>:<port> ]
  }, ... ]
  "durability" : "<durability setting>"
}

Where each attribute in the json.config file is explained below:

path This is the root directory of the XRegion Service. The agents use this directory to dump logs, statistics and other auxiliary files. The directory shall be readable and writable to the agents.

agentGroupSize and agentId

Specifies the number of service agents and the Agent ID in the agent group. The Agent ID is specified as numbers starting from 0. These details are used to form a group of agents that serve the local region. Forming a group of agents achieves horizontal scalability.

Note:

The current release supports only a single service for each local region. Therefore the agentGroupSize is set to 1 and the agentId is set to 0.

security Specifies the security file used by the agent. This attribute must be defined for the local store as well as the remote stores.

region Specifies the local region name defined for the region where you are configuring the agent.

store Specifies the name of the store in the local region.

helpers Specifies the list of host and port numbers used for configuring the local store. These helper hosts are those you used to create a KV client. For XRegion Service to connect to the local and remote regions, each region's firewall must be configured to open the registry port and HA ports.

regions

After defining the local region, you must define a list of remote regions. At least one remote region shall be defined in order to create an MR Table.

Specifies the region name, store name, and helper hosts for each remote region you want to include.

Note:

The remote region names listed here must be same as the local region names defined for them.

durability

This is an optional parameter. It specifies the durability setting for Master commit synchronization. The possible values are:

COMMIT_NO_SYNC
COMMIT_SYNC
COMMIT_WRITE_NO_SYNC

The default durability setting is COMMIT_NO_SYNC.

Grant the following privileges to the XRegion Service Agent:

Write permission to system table
Read and Write permission to all the user tables

— create role for the agent -- 
CREATE ROLE <Agent Role>

— grant privileges to the role --   
GRANT WRITE_SYSTEM_TABLE to <Agent Role>
GRANT READ_ANY_TABLE to <Agent Role>
GRANT INSERT_ANY_TABLE to <Agent Role>

— grant role to the agent user -- 
GRANT <Agent Role> to user <Agent User>

Note:

This step is required only for secure KVStores. In a non-secure KVStore setup, this step can be skipped.

Contents of the json.config file in the Frankfurt (FRA) region.

{
"path": "/home/oracle/xrshome",
"agentGroupSize": 1,
"agentId": 0,
"region": "FRA",
"store": "earnback",
"security" : "/home/oracle/security/test.security",
"helpers": [
"host1:5000",
"host2:5000",
"host5:5000"
],
"regions": [
{
"name": "LND",
"store": "mrtstore",
"security" : "/home/oracle/remoteSecureStore/security/test.security",
"helpers": [
"host3:5000",
"host4:5000",
"host6:5000"
]
}
]
}

Contents of the json.config file in the London (LND) region.

{
"path": "/home/oracle/xrshome",
"agentGroupSize": 1,
"agentId": 0,
"region": "LND",
"store": "mrtstore",
"security" : "/home/oracle/security/test.security",
"helpers": [
"host3:5000",
"host4:5000",
"host6:5000"
],
"regions": [
{
"name": "DEN",
"store": "earnback",
"security" : "/home/oracle/remoteSecureStore/security/test.security",
"helpers": [
"host1:5000",
"host2:5000",
"host5:5000"
]
}
]
}

Start XRegion Service

You must start the XRegion service in each region using the XRSTART command providing the complete path to the JSON config file. As this service is a long-running process, it is recommended to invoke it as a background process by appending the & at the end of the command.

Note:

The local KVStore must be started before starting the XRegion Service. If the KVStore in the local region has not started or is not reachable, the XRegion Service will not start.

To start the XRegion Service:

Execute the xrstart command for each KVStore separately.

nohup java -Xms256m -Xmx2048m -jar $KVHOME/lib/kvstore.jar xrstart \
 -config <complete path to the json.config file> > \
<complete path to the home directory for the XRegion Service>/nohup.out &

where each parameter is explained below:

-config Specifies the complete path where the json.config file is placed.

> Instructs to redirect the output to the file specified next to it.

nohup.out Specifies the file to be used for logging the status messages.

Optionally, you can view the status of the xrstart command execution by reading the contents of nohup.out.
```
cat <complete path to the home directory for the XRegion Service>/nohup.out
```
You can even check the detailed logs in the service log, that is available in the XRegion Service home directory specified in the XRegion Service configuration file (json.config) earlier.

Starting the XRegion Service in the Frankfurt (FRA) region:

nohup java -Xms256m -Xmx2048m -jar $KVHOME/lib/kvstore.jar xrstart \
-config /home/oracle/xrshome/json.config  > /home/oracle/xrshome/nohup.out &

[1] 24356

[oracle@host1 xrshome]$ nohup: ignoring input and redirecting stderr to stdout

[oracle@host1 xrshome]$ cat nohup.out

Cross-region agent (region=FRA, store=earnback, helpers=[host1:5000, host2:5000, host5:5000]) starts up from config file=/home/oracle/xrshome/json.config at 2019-11-07 08:57:34 UTC

Starting the XRegion Service in the London (LND) region:

[oracle@host3 xrshome]$ nohup java -Xms256m -Xmx2048m -jar $KVHOME/lib/kvstore.jar xrstart \
-config /home/oracle/xrshome/json.config  > /home/oracle/xrshome/nohup.out &

[1] 17587

[oracle@host3 xrshome]$ nohup: ignoring input and redirecting stderr to stdout

[oracle@host3 xrshome]$ cat nohup.out

Cross-region agent (region=LND, store=mrtstore, helpers=[host3:5000, host4:5000, host6:5000]) starts up from config file=/home/oracle/xrshome/json.config at 2019-11-07 08:58:57 UTC

Create Remote Regions

Learn to create remote regions from each region in a Multi-Region NoSQL Database.

Before creating and operating on an MR table, you must define the remote regions. You have already set the local region name for each region, in an earlier step. In this step, you define all the remote regions for each region. A remote region is different from the local region where the command is executed.

Note:

All the commands demonstrated below can be executed either from the kv prompt or from the sql shell prompt. When executing these commands from the sql shell, discard the execute keyword.

To create the remote regions:

Connect to the kv prompt from the local region, and connect to the local KVStore.

Execute the following command from the kv prompt.

kv-> execute 'CREATE REGION <remote region name>'

Optionally, you can execute the following command to list the remote regions that are created successfully.
```
kv-> execute 'show regions'
```

Creating a remote region named LND from the Frankfurt (FRA) region:

[oracle@host1 xrshome]$ java -jar $KVHOME/lib/kvstore.jar runadmin \
-helper-hosts host1:5000,host2:5000,host5:5000

kv-> connect store -name earnback
Connected to earnback at host1:5000.

kv-> execute 'CREATE REGION LND'
Statement completed successfully

kv-> execute 'show regions'
regions

  FRA (local, active) 

  LND (remote, active)

Creating a remote region named FRA from the London (LND) region:

[oracle@host3 ~]$ java -jar $KVHOME/lib/kvstore.jar runadmin \
-helper-hosts host3:5000,host4:5000,host6:5000

kv-> connect store -name mrtstore
Connected to mrtstore at host3:5000.

kv-> execute 'CREATE REGION FRA'
Statement completed successfully

kv-> execute 'show regions'
regions

  LND (local, active) 

  FRA (remote, active)

Create Multi-Region Tables

You must create an MR Table on each KVStore in the connected graph, and specify the list of regions that the table should span. In the use case in our discussion, to create the Users table as an MR Table at both the regions, in any order.

Note:

All the commands demonstrated below can be executed either from the kv prompt or from the sql shell prompt. When executing these commands from the sql shell, discard the execute keyword before each command.

To create an MR Table:

To create a table definition, use a CREATE TABLE statement. See Create Table in the SQL Reference Guide.
Optionally, you can verify the MR Table creation by executing the following command.
```
kv-> show table -name Users
```

Creating an MR Table called Users from the Frankfurt (FRA) region, using the kv prompt:

kv-> execute 'CREATE TABLE Users (id INTEGER, name STRING, team STRING, PRIMARY KEY (id)) IN REGIONS FRA, LND'
Statement completed successfully
kv-> show table -name Users
{
  "json_version" : 1,
  "type" : "table",
  "name" : "Users",
  "shardKey" : [ "id" ],
  "primaryKey" : [ "id" ],
  "fields" : [ {
    "name" : "id",
    "type" : "INTEGER",
    "nullable" : false,
    "default" : null
  }, {
    "name" : "name",
    "type" : "STRING",
    "nullable" : true,
    "default" : null
  }, {
    "name" : "team",
    "type" : "STRING",
    "nullable" : true,
    "default" : null
  } ],
  "regions" : {
    "2" : "LND",
    "1" : "FRA"
  }
}

Note:

The region with id 1 under the regions attribute refers to the local region.

Creating an MR Table called Users from the London (LND) region, using the sql shell prompt:

sql-> CREATE TABLE Users (
id INTEGER, 
name STRING, 
team STRING, 
PRIMARY KEY (id)) 
IN REGIONS LND, FRA;
Statement completed successfully

Verifying the Users table structure from the London (LND) region:

kv-> show table -name users
{
  "json_version" : 1,
  "type" : "table",
  "name" : "Users",
  "shardKey" : [ "id" ],
  "primaryKey" : [ "id" ],
  "fields" : [ {
    "name" : "id",
    "type" : "INTEGER",
    "nullable" : false,
    "default" : null
  }, {
    "name" : "name",
    "type" : "STRING",
    "nullable" : true,
    "default" : null
  }, {
    "name" : "team",
    "type" : "STRING",
    "nullable" : true,
    "default" : null
  } ],
 "regions" : {
     "2" : "FRA"
     "1" : "LND"
  }
}

Access and Manipulate Multi-Region Tables

After creating the MR Table, you can perform read or write operations on the table using the existing data access APIs or DML statements. There is no change to any existing data access APIs or DML statements to work with the MR Tables. See Data Row Management in the SQL Reference Guide.

Inserting new rows into the Users table from the Frankfurt (FRA) region.

kv-> execute 'INSERT INTO Users VALUES(1,"Amy","HR")'
{"NumRowsInserted":1}

1 row returned
kv-> execute 'INSERT INTO Users VALUES(2,"Jack","HR")'
{"NumRowsInserted":1}

1 row returned

Reading the rows in the Users table from the London (LND) region.

kv-> execute 'SELECT * FROM Users'
{"id":2,"name":"Jack","team":"HR"}
{"id":1,"name":"Amy","team":"HR"}

2 rows returned

Updating a row with id=2 in the Users table from the London (LND) region.

kv-> execute 'UPDATE Users SET team="IT" WHERE id=2'
{"NumRowsUpdated":1}

1 row returned

Reading the updated rows in the Users table from the Frankfurt (FRA) region.

kv-> execute 'SELECT * FROM Users'
{"id":2,"name":"Jack","team":"IT"}
{"id":1,"name":"Amy","team":"HR"}

2 rows returned

Deleting the row with id=1 in the Users table from the Frankfurt (FRA) region.

kv-> execute 'DELETE FROM Users WHERE id=1'
{"NumRowsDeleted":1}

1 row returned

Reading the rows in the Users table from the London (LND) region.

kv-> execute 'SELECT * FROM Users'
{"id":2,"name":"Jack","team":"IT"}

1 row returned

Stop XRegion Service

In a case where you want to relocate your XRegion Service to another host machine, you must shut it down in the current machine and then restart it in the new host machine.

To stop the XRegion Service:

Execute the xrstop command for each KVStore separately.

java -Xmx1024m -Xms256m -jar $KVHOME/lib/kvstore.jar xrstop \
-config <complete path to the json.config file>

Stopping XRegion Service in the Frankfurt (FRA) region:

-bash-4.1$ java -Xmx1024m -Xms256m -jar $KVHOME/lib/kvstore.jar xrstop \
-config /home/oracle/xrshome/json.config

Similarly, you must stop the XRegion Service in all the other regions.