show
Encapsulates commands that display the state of the store and its components or schemas. The subcommands are as follows:
show admins
show admins [-json]kv-> show admins -json
{
"operation" : "show admins",
"returnCode" : 5000,
"description" : "Operation ends successfully",
"returnValue" : {
"admins" : [ {
"id" : "admin1",
"storageNode" : "sn1",
"type" : "PRIMARY",
"connected" : true,
"adminStatus" : "RUNNING",
"replicationState" : "MASTER",
"authoritative" : true
}, {
"id" : "admin2",
"storageNode" : "sn2",
"type" : "PRIMARY",
"connected" : false,
"adminStatus" : "RUNNING",
"replicationState" : "REPLICA",
"authoritative" : true
} ]
}
}show events
show events [-id <id>] | [-from <date>] [-to <date> ]
[-type <stat | log | perf>] [-json]Displays event details or list of store events. The status events indicate changes in service status.
Log events are noted if they require attention.
Performance events are not usually critical but may merit investigation. Events marked "SEVERE" should be investigated.
The following date/time formats are accepted. They are interpreted in the local time zone.
-
MM-dd-yy HH:mm:ss:SS
-
MM-dd-yy HH:mm:ss
-
MM-dd-yy HH:mm
-
MM-dd-yy
-
HH:mm:ss:SS
-
HH:mm:ss
-
HH:mm
kv-> show events -json
{
"operation" : "show events",
"returnCode" : 5000,
"description" : "Operation ends successfully",
"returnValue" : {
"events" : [ {
"event" : "j84a16s3S STAT 2017-09-28 09:48:12.819 UTC sn1 RUNNING sev1"
}, {
"event" : "j84a17j0S STAT 2017-09-28 09:48:13.788 UTC sn2 RUNNING sev1"
}, {
"event" : "j84a19xoS STAT 2017-09-28 09:48:16.908 UTC sn3 RUNNING sev1"
}, {
"event" : "j84a1cznS STAT 2017-09-28 09:48:20.867 UTC rg1-rn1 RUNNING sev1"
}, {
"event" : "j84a1f75S STAT 2017-09-28 09:48:23.729 UTC rg1-rn2 RUNNING sev1"
}, {
"event" : "j84a1h7xS STAT 2017-09-28 09:48:26.349 UTC rg1-rn3 RUNNING sev1"
}, {
"event" : "j84a3i9rS STAT 2017-09-28 09:50:01.023 UTC rg1-rn3 STOPPED sev1 (reported by sn3)"
}, {
"event" : "j84a4oquS STAT 2017-09-28 09:50:56.070 UTC rg1-rn3 RUNNING sev1"
}, {
"event" : "j84a5hfeS STAT 2017-09-28 09:51:33.242 UTC rg1-rn3 STOPPED sev1 (reported by sn3)"
}, {
"event" : "j84aw53tS STAT 2017-09-28 10:12:16.985 UTC sn3 UNREACHABLE sev2 (reported by
admin1)"
}, {
"event" : "j84b585yL LOG 2017-09-28 10:19:20.854 UTC SEVERE [admin1] Plan 24 [Remove Admin
Replica] task [DestroyAdmin admin3] of plan 24 ended in state ERROR with java.rmi.ConnectException:
Unable to connect to the storage node agent at host localhost, port 22000, which may not be running;
nested exception is: "
}, {
"event" : "j84b585zL LOG 2017-09-28 10:19:20.854 UTC SEVERE [admin1] Plan [null] failed. Attempt 1
[RUNNING] start=2017-09-28 10:19:20 UTC end=2017-09-28 10:19:20 UTC "
} ]
}
}show faults
show faults [-last] [-command <command index>] [-json]kv-> show faults -json
{
"operation" : "show faults",
"returnCode" : 5000,
"description" : "Operation ends successfully",
"returnValue" : {
"faultCommands" : [ {
"faultCommand" : "503 plan remove-admin -admin 3 -json -wait: class
oracle.kv.util.shell.ShellException"
}, {
"faultCommand" : "526 topology create -name mytopo -pool snpool -json -partitions 300 -json: class
java.lang.NullPointerException"
} ]
}
}show indexes
show indexes [-table <name>] [-name <name>] [-json]Displays index metadata. By default the indexes metadata of all tables are listed.
If a specific table is named, its indexes metadata are displayed. If a specific index of the table is named, its metadata is displayed. For more information, see plan add-index.
Use SHOW INDEX statement to indicate the index type (TEXT, SECONDARY) when you enable text-searching capability to Oracle NoSQL Database, in-concert with the tables interface.
For example:
kv-> show index
Indexes on table Joke
JokeIndex (category, txt), type: TEXTkv-> show indexes -json
{
"operation" : "show indexes",
"returnCode" : 5000,
"description" : "Operation ends successfully",
"returnValue" : {
"tables" : [ {
"table" : {
"tableName" : "t1",
"indexes" : [ {
"name" : "idx1",
"fields" : [ "id1", "id2" ],
"type" : "SECONDARY",
"description" : null
}, {
"name" : "idx2",
"fields" : [ "id2" ],
"type" : "SECONDARY",
"description" : null
} ]
},
"childTable" : [ {
"tables" : [ ]
} ]
} ]
}
}show mrtable-agent-statistics
show mrtable-agent-statistics [-agent <agentID>][-table <tableName>][-json]Shows the latest statistics as of the last one minute for multi-region table agents. With no arguments, this command shows combined statistics over all regions the MR Table spans.
Input Parameters
Table 7-1 Input Parameters
| Flag | Parameter | Description |
|---|---|---|
- agent |
agentID |
Limits the statistics to the agent ID specified. You can find the agent ID from the JSON config file created while configuring your agent. See Configure XRegion Service. |
- table |
tableName |
Limits the statistics to the MR Table specified. |
- json |
- | Returns the complete statistics in JSON format. Even though the statistics are returned in JSON format by default, specifying this flag adds additional information in the output such as operation, return code, and the return code's description. |
Output Statistics
show mrtable-agent-statistics can be
categorized as those used to:
- Monitor streams from other regions
Table 7-2 Output Statistics 1
Statistic Description completeWriteOpsNumber of complete write operations per region. lastMessageMsTimestamp when the agent sees the last message from a remote region, in milliseconds.
If this statistic information is not available, -1 is printed as its output value.
lastModificationMsTimestamp of the last operation performed in each remote region, in milliseconds.
If this statistic information is not available, -1 is printed as its output value.
laggingMs (avg, max, min)In a multi-region KVStore, each shard in a region pushes all the operations performed on all its tables to the agent's queue. The agent replicates the contents of its queue, in event order, to all other regions. The lagging statistic represents the time difference between an event being pushed into the queue and replicated to the other regions by the agent. If this value is high, it indicates that the queue is getting backed up. A smaller value indicates that the agent is able to keep up with the number of events coming from remote regions. The lagging statistics are reported as average, minimum, and maximum in milliseconds for each remote region.
If this statistic information is not available, -1 is printed as its output value.
latencyMs (avg, max, min)In MR tables, the latency statistic indicates the time taken in milliseconds for each operation to travel from its origin (remote) region to the target (local) region. The latency is computed as T2 - T1, where:- T1 is the timestamp when an operation is performed in the remote region, and
- T2 is the timestamp when the agent persisted the replicated operation to the local region.
If this statistic information is not available, -1 is printed as its output value.
- Check the persistence of remote data
Table 7-3 Output Statistics 2
Statistic Description putsNumber of write operations received. delsNumber of delete operations received. streamBytesTotal bytes replicated from a remote region. persistStreamBytesReports the total number of bytes that are successfully committed in the local region. This is different from the total bytes replicated from a remote region because in case of any conflicts with operations from other regions, some of the operations may not persist if they fail the built-in conflict resolution rule. winPutsNumber of write operations performed successfully. More specifically, this statistic excludes the writes that failed to win the conflict resolution rule, in case of a conflict with writes in other regions. winDelsNumber of delete operations performed successfully. More specifically, this statistic excludes the deletes that failed to win the conflict resolution rule, in case of a conflict with deletes in other regions. incompatibleRowsNumber of operations that did not persist because of incompatible table schemas. This can happen when there is a schema mismatch between the origin region and the region that is trying to replicate the row to its local data store. - Monitor the interaction between admin and the agent
Table 7-4 Output Statistics 3
Statistic Description requestsAll the DDL commands executed by the user on an MR table are converted into requests to the agent by the admin. This statistic reports the number of requests posted by the admin. responsesNumber of requests processed and responded by the agent. - Monitor multi-region tablesWhen you execute the
show mrtable-agent-statisticscommand with the-tableflag, it returns the table level statistics indicating:- Persistence of remote data in the local region: This
includes the statistics such as
puts,dels,winPuts,winDels,streamBytes,persistStreamBytes, andincompatibleRowsdiscussed above. - Progress of table initialization in each remote region:
This is indicated by the
stateattribute under theInitialization statisticsin the output. The table below lists the different possible values for state and their meaning.Table 7-5 Table Initialization States
State Description NOT_STARTMR table initialization has not started, or there is no need to do initialization. For example, if the agent resumes the stream from an existing checkpoint successfully, there is no need to re-initialize the MR table. IN_PROGRESSMR table initialization is ongoing, that is, the MR table initialization has started and the data is being replicated from the remote regions. COMPLETEMR table initialization is complete and table transfer is done. The agent is streaming from the remote region. ERRORMR table initialization cannot complete because of an irrecoverable error. You can view the error severity in the agent log as WARNINGorSEVERE. The agent log can be found in the directory specified in the JSON config file. See Configure XRegion Service.SHUTDOWNMR table initialization cannot complete because the service is shut down. - Persistence of the table data per remote region:
Table 7-6 Output Statistics 4
Statistic Description transferStartMsTimestamp of the initiation of table initialization, in milliseconds.
If this statistic information is not available, -1 is printed as its output value.
transferCompleteMsTimestamp of the completion of table initialization, in milliseconds.
If this statistic information is not available, -1 is printed as its output value.
elapsedMsThe time elapsed from the start of the table initialization until its completion.
elapsedMs = transferCompleteMs - transferStartMs
This statistic is reported in milliseconds. Before the transfer completion, it reports -1 indicating the unavailability of this statistic.
transferBytesNumber of bytes transferred from the remote (origin or source) region to the local (target) region. transferRowsNumber of rows transferred from the remote region to the local region successfully. expireRowsNumber of rows expired before transferring from the remote region. Because of their TTL value, some rows might expire during the replication. Such rows expire by the time they reach the agent. This statistic counts such expired rows. persistBytesReports the total number of bytes that are successfully committed in the local region. This excludes the rows that are not committed in the local region because they failed the built-in conflict resolution rule. In case of row updates, the entire row is counted for this statistic. persistRowsReports the total number of rows that are successfully committed in the local region. Similar to the above statistic, the rows that are not committed in the local region because of the built-in conflict resolution rule are excluded for this count.
- Persistence of remote data in the local region: This
includes the statistics such as
Example
Note:
If any of the statistics information is not available, -1 is reported as the value for that statistic parameter in the output.# MR table agent statistics for a specific agent
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": {
"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
}
}
}
}# MR table agent statistics for a specific MR table
kv-> show mrtable-agent-statistics -table users -json
{
"operation": "show mrtable-agent-statistics",
"returnCode": 5000,
"description": "Operation ends successfully",
"returnValue": {
"XRegionService-1_0": {
"tableID": 12,
"tableName": "users",
"timestamp": 1592901300001,
"statistics": {
"agentId": "XRegionService-1_0",
"beginMs": 1592901240001,
"dels": 1000,
"endMs": 1592901300001,
"expiredPuts": 200,
"incompatibleRows": 100,
"initialization": {
"lnd": {
"elapsedMs": 476,
"expireRows": 100,
"persistBytes": 6492160,
"persistRows": 6340,
"state": "COMPLETE",
"transferBytes": 8115200,
"transferCompleteMs": 1592822625333,
"transferRows": 7925,
"transferStartMs": 1592822624857
},
"dub": {
"transferStartMs": 0,
"transferCompleteMs": 0,
"elapsedMs": -1,
"transferRows": 0,
"persistRows": 0,
"expireRows": 0,
"transferBytes": 0,
"persistBytes": 0,
"state": "NOT_START"
}
},
"intervalMs": 60000,
"localRegion": "fra",
"persistStreamBytes": 104960000,
"puts": 100000,
"streamBytes": 115200000,
"tableId": 12,
"tableName": "users",
"winDels": 745,
"winPuts": 90000
}
}
}
}show parameters
show parameters -policy | -service <name> Displays service parameters and state for the specified service. The service may be a Replication Node, Storage Node, or Admin service, as identified by any valid string, for example rg1-rn1, sn1, admin2, etc. Use the -policy flag to show global policy parameters. Use the -security flag to show global security parameters.
show parameters -service sn1 When you enable text-searching capability to Oracle NoSQL Database, in-concert with the tables interface, the show parameter command also provides information on the Elasticsearch cluster name and transport port as values for the parameters searchClusterMembers and searchClusterName.
For more information, see Integration with Elastic Search for Full Text Search in the Integrations Guide.
show plans
show plans [-last] [-id <id>] [-from <date>] [-to <date>][-num <howMany>]Shows details of the specified plan or list all plans that have been created along with their corresponding plan IDs and status.
-
The
-lastoption shows details of the most recently created plan. -
The
-id <n>option details the plan with the given id. If -num <n> is also given, list <n> plans, starting with plan #<id>. -
The
-num <n>option sets the number of plans to the list. The default is 10. -
The
-from <date>option lists plans after <date>. -
The
-to <date>option lists plans before <date>.
Combining -from with -to describes the range between the two dates. Otherwise -num applies.
The following date formats are accepted. They are interpreted in the UTC time zone.
-
yyyy-MM-dd HH:mm:ss.SSS
-
yyyy-MM-dd HH:mm:ss
-
yyyy-MM-dd HH:mm
-
yyyy-MM-dd
-
MM-dd-yyyy HH:mm:ss.SSS
-
MM-dd-yyyy HH:mm:ss
-
MM-dd-yyyy HH:mm
-
MM-dd-yyyy
-
HH:mm:ss.SSS
-
HH:mm:ss
-
HH:mm
For more information on plan review, see Reviewing Plans.
show snapshots
show snapshots [-sn <id>] Lists snapshots on the specified Storage Node. If no Storage Node is specified, one is chosen from the store. You can use this command to view the existing snapshots.
show regions
show regionsDisplays the list of all the remote regions included in a Multi-Region Oracle NoSQL Database setup.
kv-> execute 'show regions'
regions
DEN
show tables
show tables -name table_name Displays the table information. Use -original flag to show the original table information if you are building a table for evolution. The flag is ignored for building table for addition. For more information, see plan add-table and plan evolve-table
Use show table -name table_name statement to list the full text index. This command provides the table structure including the indexes that have been created for that table. For more information, see Creating FTI in the Integrations Guide.
show topology
show topology [-zn] [-rn] [-an] [-sn] [-store] [-status] [-json] [-verbose] Displays the current, deployed topology. By default it shows the entire topology, including the number of shards. The first set of optional flags restrict the display to one or more zones, Replication Nodes, Storage Nodes, Arbiter Nodes, store name, or to specify service status. Use –json to display the results in JSON format. If you specify -verbose, then additional information will be displayed, including Replication Node storage directories, storage directory sizes, log directories, and JE HA ports.
kv-> show topology
store=mystore numPartitions=1000 sequence=2376
zn: id=zn1 name=myzone repFactor=3 type=PRIMARY allowArbiters=false
masterAffinity=false
sn=[sn1] zn:[id=zn1 name=myzone] nodeA:5000 capacity=1 RUNNING
[rg1-rn1] RUNNING
single-op avg latency=0.0 ms multi-op avg latency=0.0 ms
sn=[sn2] zn:[id=zn1 name=myzone] nodeB:5000 capacity=1 RUNNING
[rg1-rn2] RUNNING
single-op avg latency=0.0 ms multi-op avg latency=0.0 ms
sn=[sn3] zn:[id=zn1 name=myzone] nodeC:5000 capacity=1 RUNNING
[rg1-rn3] RUNNING
single-op avg latency=0.0 ms multi-op avg latency=0.0 ms
sn=[sn4] zn:[id=zn1 name=myzone] nodeD:5000 capacity=1 RUNNING
[rg2-rn1] RUNNING
No performance info available
sn=[sn5] zn:[id=zn1 name=myzone] nodeE:5000 capacity=1 RUNNING
[rg2-rn2] RUNNING
single-op avg latency=0.0 ms multi-op avg latency=0.0 ms
sn=[sn6] zn:[id=zn1 name=myzone] nodeF:5000 capacity=1 RUNNING
[rg2-rn3] RUNNING
single-op avg latency=0.0 ms multi-op avg latency=0.0 ms
numShards=2
shard=[rg1] num partitions=500
[rg1-rn1] sn=sn1
[rg1-rn2] sn=sn2
[rg1-rn3] sn=sn3
shard=[rg2] num partitions=500
[rg2-rn1] sn=sn4
[rg2-rn2] sn=sn5
[rg2-rn3] sn=sn6
show upgrade-order
show upgrade-order [-json]Lists the Storage Nodes which need to be upgraded in an order that prevents disruption to the store's operation.
This command displays one or more Storage Nodes on a line. Multiple Storage Nodes on a line are separated by a space. If multiple Storage Nodes appear on a single line, then those nodes can be safely upgraded at the same time. When multiple nodes are upgraded at the same time, the upgrade must be completed on all nodes before the nodes next on the list can be upgraded.
If at some point you lose track of which group of nodes should be upgraded next, you can always run the show upgrade-order command again.
kv-> show upgrade-order -json
{
"operation" : "show upgrade-order",
"returnCode" : 5000,
"description" : "Operation ends successfully",
"returnValue" : {
"singleTextResult" : "Calculating upgrade order, target version: 12.2.4.6.0, prerequisite:
12.1.3.0.5\nUnable to contact sn3 Unable to connect to the storage node agent at host localhost, port
22000, which may not be running; nested exception is: \n\tjava.rmi.ConnectException: Connection refused
to host: localhost; nested exception is: \n\tjava.net.ConnectException: Connection refused (Connection
refused)\nThere are no nodes that need to be upgraded"
}
}show users
show users -name <name> Lists the names of all users, or displays information about a specific user. If no user is specified, lists the names of all users. If a user is specified using the -name option, then lists detailed information about the user.
show versions
show versions [-json]Lists the client and server version information.
For example
kv-> show versions
Client version: 12.1.3.4.0
Server version: 12.1.3.4.0
kv-> show versions -json
{
"operation" : "show version",
"returnCode" : 5000,
"description" : "Operation ends successfully",
"returnValue" : {
"clientVersion" : "12.2.4.6.0",
"serverVersion" : "12.2.4.6.0"
}
}show zones
show zones [-zn <id>] | -znname <name>] [-json]Lists the names of all zones, or display information about a specific zone.
-zn or the -znname flag to specify the zone that you want to show additional information; including the names of all of the Storage Nodes in the specified zone, and whether that zone is a primary of secondary zone. kv-> show zones -json
{
"operation" : "show zone",
"returnCode" : 5000,
"description" : "Operation ends successfully",
"returnValue" : {
"zones" : [ {
"zone" : {
"id" : "zn1",
"name" : "1",
"repfactor" : 1,
"type" : "PRIMARY",
"allowArbiters" : false
}
}, {
"zone" : {
"id" : "zn2",
"name" : "2",
"repfactor" : 1,
"type" : "PRIMARY",
"allowArbiters" : false
}
}, {
"zone" : {
"id" : "zn3",
"name" : "3",
"repfactor" : 1,
"type" : "PRIMARY",
"allowArbiters" : false
}
} ]
}
}