Use Case 1: Set up Multi-Region Environment
An organization deploys two on-premise KVStores, one each at Frankfurt and
London. As per their requirement, they create a few MR Tables in both the regions. The
Users
table is one of the many MR Tables created and maintained by
this organization. In the next few topics, let us discuss how to set up the Frankfurt
and London regions and how to create and work with an MR Table called
Users
in these two regions.
Deploy KVStore
Set Local Region Name
Learn how to set a name to the local region in a Multi-Region NoSQL Database.
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
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
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.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.
Note:
All the commands demonstrated below can be executed either from thekv
prompt or from the
sql
shell prompt. When executing these commands from the
sql
shell, discard the execute
keyword.
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
Users
table as an MR Table at both the
regions, in any order.
Note:
All the commands demonstrated below can be executed either from thekv
prompt or from the sql
shell prompt. When executing these commands
from the sql
shell, discard the execute
keyword
before each command.
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
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
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
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
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
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
id
=1 in the Users
table from the Frankfurt (FRA)
region.kv-> execute 'DELETE FROM Users WHERE id=1'
{"NumRowsDeleted":1}
1 row returned
Users
table from the London (LND)
region.kv-> execute 'SELECT * FROM Users'
{"id":2,"name":"Jack","team":"IT"}
1 row returned
Stop XRegion Service
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.