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: TEXT
kv-> 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 5-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 5-2 Output Statistics 1
Statistic Description completeWriteOps
Number of complete write operations per region. lastMessageMs
Timestamp 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.
lastModificationMs
Timestamp 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 5-3 Output Statistics 2
Statistic Description puts
Number of write operations received. dels
Number of delete operations received. streamBytes
Total bytes replicated from a remote region. persistStreamBytes
Reports 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. winPuts
Number 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. winDels
Number 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. incompatibleRows
Number 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 5-4 Output Statistics 3
Statistic Description requests
All 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. responses
Number of requests processed and responded by the agent. - Monitor multi-region tablesWhen you execute the
show mrtable-agent-statistics
command with the-table
flag, 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
, andincompatibleRows
discussed above. - Progress of table initialization in each remote region:
This is indicated by the
state
attribute under theInitialization statistics
in the output. The table below lists the different possible values for state and their meaning.Table 5-5 Table Initialization States
State Description NOT_START
MR 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_PROGRESS
MR table initialization is ongoing, that is, the MR table initialization has started and the data is being replicated from the remote regions. COMPLETE
MR table initialization is complete and table transfer is done. The agent is streaming from the remote region. ERROR
MR table initialization cannot complete because of an irrecoverable error. You can view the error severity in the agent log as WARNING
orSEVERE
. The agent log can be found in the directory specified in the JSON config file. See Configure XRegion Service.SHUTDOWN
MR table initialization cannot complete because the service is shut down. - Persistence of the table data per remote region:
Table 5-6 Output Statistics 4
Statistic Description transferStartMs
Timestamp of the initiation of table initialization, in milliseconds.
If this statistic information is not available, -1 is printed as its output value.
transferCompleteMs
Timestamp of the completion of table initialization, in milliseconds.
If this statistic information is not available, -1 is printed as its output value.
elapsedMs
The 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.
transferBytes
Number of bytes transferred from the remote (origin or source) region to the local (target) region. transferRows
Number of rows transferred from the remote region to the local region successfully. expireRows
Number 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. persistBytes
Reports 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. persistRows
Reports 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
-last
option 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 regions
Displays 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 tls-credentials
The show-tls-credentials
command shows information about the TLS credentials installed, and the updates waiting to be installed, on all SNAs. The command shows the summary information on the overall status of TLS credentials. Additionally, it also provides information about every SNA in the data store.
In the output of this show command, the two entries installedCredentialsStatus
and pendingUpdatesStatus
summarize the information about the TLS credentials installed.
installedCredentialsStatus
: Verifies if the same pair of keystore and truststore files are installed in all SNs. This entry has one of the following values:
consistent
: All SNs report having the same pair of keystore and truststore files installed.mixed
: Some SNAs are found to have different pairs of keystore and truststore files installed.maybe-consistent
: All SNAs that responded reported having the same pair of files installed, but not all SNAs could be contacted.
pendingUpdatesStatus
: Provides information about any pending updates. This entry has one of the following values:
none
: No SNAs have any pending updates, or the update files match currently installed files.consistent
: There are pending updates and all installed files will be consistent once the pending updates are installed.mixed
: There are pending updates and some installed files will be different after the pending updates are installed.maybe-consistent
: There are pending updates, all SNAs that responded will have the same files after the updates are installed, but not all SNAs could be contacted.
installedCredentials
, and, pendingUpdates
each with keystore
and truststore
entries for all the files present. For each credential file found, the following entries are present.
file
: Provides the name of the file relative to the security subdirectory under the SN's root directory ($KVROOT
)hash
: Represents the SHA-256 hash of the file contents represented as a 64-character hexadecimal value. You can compute an SHA-256 hash of the credentials files and compare the calculated value with this hash value to ensure that the credential file has not been modified after it was copied to the SNAs.modtime
: Represents the modification time of the file in the UTC timezone Note: If there is a problem contacting an SNA, the value for that SNA will have an exception entry whose value is a string describing the problem.
Note:
If there is a problem contacting an SNA, the value for that SNA will have anexception
entry whose value is a string describing the problem.
{
"operation": "show tls-credentials",
"returnCode": 5000,
"description": "Operation ends successfully",
"returnValue": {
"installedCredentialsStatus": "consistent",
"pendingUpdatesStatus": "consistent",
"sns": {
"sn1": {
"installedCredentials": {
"keystore": {
"file": "store.keys",
"hash": "b157d9dc61bed1e2642425adb6791dd13e2c0868160b693963a85596130c388d",
"modTime": "2024-06-07 09:21:53 UTC"
},
"truststore": {
"file": "store.trust",
"hash": "c5a4c67820b64d324a1b8ed493041e04ef91697f64d4a605d750b9dbe8708f6b",
"modTime": "2024-06-07 09:21:57 UTC"
}
},
"pendingUpdates": {
"truststore": {
"file": "updates/store.trust",
"hash": "4fe0231942e732d829cf97cb3742c9aa86ae06db8a80939b6fb834382c205a53",
"modTime": "2024-06-08 10:00:00 UTC"
}
}
},
"sn2": { ... },
"sn3": { ... }
}
}
}
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
}
} ]
}
}