Show Subscriber Database Information
Replication uses the TimesTen transaction log to retain information that must be transmitted to subscriber sites. When communication to subscriber databases is interrupted or the subscriber sites are down, the transaction log data accumulates.
Use the following methods to display information for subscriber databases:
Display Subscriber Status With the ttRepAdmin Utility
To display information about subscribers, use the ttRepAdmin
utility
with the -receiver -list
options.
ttRepAdmin -dsn masterDSN
-receiver -list
This example uses the ttRepAdmin
utility to display information
about the subscribers described in Multiple Subscriber Classic Replication Schemes.
> ttRepAdmin -dsn masterds -receiver -list Peer name Host name Port State Proto ---------------- ------------------------ ------ ------- ----- subscriber1ds server2 Auto Start 10 Last Msg Sent Last Msg Recv Latency TPS RecordsPS Logs ------------- ------------- ------- ------- --------- ---- 0:01:12 - 19.41 5 5 52 2 Peer name Host name Port State Proto ---------------- ------------------------ ------ ------- ----- subscriber2ds server3 Auto Start 10 Last Msg Sent Last Msg Recv Latency TPS RecordsPS Logs ------------- ------------- ------- ------- --------- ---- 0:01:04 - 20.94 4 48 2
The first line of the display contains the subscriber definition. The following row of the display contains latency and rate information, as well as the number of transaction log files being retained on behalf of this subscriber. The latency for subscriber1ds
is 19.41 seconds, and it is 2 logs behind the master. This is a high latency, indicating a problem if it continues to be high and the number of logs continues to increase.
Note:
If you have more than one scheme specified in the TTREP.REPLICATIONS
table, you must use the -scheme
option to specify which scheme you want to list. Otherwise you receive the following error:
Must specify -scheme to identify which replication scheme to use
See ttRepAdmin in the Oracle TimesTen In-Memory Database Reference.
Display Subscriber Status With the ttReplicationStatus Built-In Procedure
Within ttIsql, you can display status for a one or more subscriber databases by using
the ttReplicationStatus
built-in procedure, which reports only on the
status of the subscribers for the master database on which this built-in procedure is
called.
The following retrieves status for the subscriber master2
that is located on host1
. If the host name is excluded, the subscriber is located solely on its name.
Command> call ttReplicationStatus('master2', 'host1'); < MASTER2, HOST1, 0, start, 1, 26, _ACTIVESTANDBY , TTREP > 1 row found.
The information shown is that the subscriber master2
located on host1
that is listening on an automatically assigned port. The TCP/IP port is used by the subscriber agent to receive updates from the master. However, since the value is zero, this indicates replication has automatically assigned the port.
This subscriber is in the start state. There is only one transaction log being held for this peer and 26 seconds have passed since the last replication. The name of the replication scheme is _ACTIVESTANDBY
and the owner is TTREP
.
If you do not provide either a subscriber or the subscriber host names, then the status for all subscribers of this master are returned. The following shows the same status as above, since there is only one subscriber set up for this master.
Command> call ttReplicationStatus(); < MASTER2, HOST1, 0, start, 1, 26, _ACTIVESTANDBY , TTREP > 1 row found.
See ttReplicationStatus in the Oracle TimesTen In-Memory Database Reference.
Display Information About Subscribers Through Querying Replication Tables
You can obtain the same information about a master's subscribers from a program by
querying the TTREP.REPPEERS
, TTREP.TTSTORES
, and
SYS.MONITOR
tables with a SELECT
statement.
SELECT t1.tt_store_name, t1.host_name, t1.rep_port_number, p.state, p.protocol, p.timesend, p.timerecv, p.latency, p.tps, p.recspersec, t3.last_log_file - p.sendlsnhigh + 1 FROM ttrep.reppeers p, ttrep.ttstores t1, ttrep.ttstores t2, sys.monitor t3 WHERE p.tt_store_id = t1.tt_store_id AND t2.is_local_store = 0X01 AND p.subscriber_id = t2.tt_store_id AND p.replication_name = 'repscheme' AND p.replication_owner = 'repl' AND (p.state = 0 OR p.state = 1);
The following is sample output from the 3 statement above:
< subscriber1ds, server2, 0, 0, 7, 1003941635, 0, -1.00000000000000, -1, -1, 1 > < subscriber2ds, server3, 0, 0, 7, 1003941635, 0, -1.00000000000000, -1, -1, 1 >
Subscriber Information
The output from either the ttRepAdmin
utility or the
SELECT
statement contains fields describing the subscriber
database.
Field | Description |
---|---|
|
Name of the subscriber database |
|
Name of the machine that hosts the subscriber. |
|
TCP/IP port used by the subscriber agent to receive updates from the master. A value of 0 indicates replication has automatically assigned the port. |
|
Current replication state of the subscriber with respect to its master database (see Show Subscriber Database Information). |
|
Internal protocol used by replication to communicate between this master and its subscribers. You can ignore this value. |
|
Time (in seconds) since the master sent the last message to the subscriber. This includes the "heartbeat" messages sent between the databases. |
|
Time (in seconds) since this subscriber received the last message from the master. |
|
The average latency time (in seconds) between when the master sends a message and when it receives the final acknowledgement from the subscriber. (See note below.) |
|
The average number of transactions per second that are committed on the master and processed by the subscriber. (See note below.) |
|
The average number of transmitted records per second. (See note below.) |
|
Number of transaction log files the master database is retaining for a subscriber. |
Note:
Latency
, TPS
, and RecordsPS
report averages detected while replicating a batch of records. These values can be unstable if the workload is not relatively constant. A value of -1 indicates the master's replication agent has not yet established communication with its subscriber replication agents or sent data to them.