This chapter describes some of the TimesTen utilities and procedures you can use to monitor the replication status of your databases.
You can monitor replication from both the command line and within your programs. The ttStatus and ttRepAdmin utilities described in this chapter are useful for command line queries. To monitor replication from your programs, you can use the TimesTen built-in procedures described in Oracle TimesTen In-Memory Database Reference or create your own SQL SELECT statements to query the replication tables described in Oracle TimesTen In-Memory Database System Tables and Limits Reference.
Note:
You can only access the TimesTenSYS and TTREP tables for queries. Do not try to alter the contents of these tables.This chapter includes the following topics:
You can display information about the current state of the replication agents:
You can also obtain the state of specific replicated databases as described in "Show subscriber database information" and "Show the configuration of replicated databases".
Use the ttStatus utility to confirm that the replication agent is started for the master database.
Example 13-1 Using ttStatus to obtain replication agent status
> ttStatus TimesTen status report as of Thu Jan 29 12:16:27 2009 Daemon pid 18373 port 4134 instance ttuser TimesTen server pid 18381 started on port 4136 ------------------------------------------------------------------------ Data store /tmp/masterds There are 16 connections to the data store Shared Memory KEY 0x0201ab43 ID 5242889 PL/SQL Memory KEY 0x0301ab43 ID 5275658 Address 0x10000000 Type PID Context Connection Name ConnID Process 20564 0x081338c0 masterds 1 Replication 20676 0x08996738 LOGFORCE 5 Replication 20676 0x089b69a0 REPHOLD 2 Replication 20676 0x08a11a58 FAILOVER 3 Replication 20676 0x08a7cd70 REPLISTENER 4 Replication 20676 0x08ad7e28 TRANSMITTER 6 Subdaemon 18379 0x080a11f0 Manager 2032 Subdaemon 18379 0x080fe258 Rollback 2033 Subdaemon 18379 0x081cb818 Checkpoint 2036 Subdaemon 18379 0x081e6940 Log Marker 2035 Subdaemon 18379 0x08261e70 Deadlock Detector 2038 Subdaemon 18379 0xae100470 AsyncMV 2040 Subdaemon 18379 0xae11b508 HistGC 2041 Subdaemon 18379 0xae300470 Aging 2039 Subdaemon 18379 0xae500470 Flusher 2034 Subdaemon 18379 0xae55b738 Monitor 2037 Replication policy : Manual Replication agent is running. Cache Agent policy : Manual PL/SQL enabled.
Use the ttAdmin utility with the -query option to confirm the policy settings for a database, including the replication restart policy described in "Starting and stopping the replication agents".
To obtain the status of the replication agents from a program, use the ttDataStoreStatus procedure.
Example 13-3 Calling ttDataStoreStatus
Call ttDataStoreStatus to obtain the status of the replication agents for the masterds databases:
> ttIsql masterds
Command> CALL ttDataStoreStatus('/tmp/masterds');
< /tmp/masterds, 964, 00000000005D8150, subdaemon, Global\DBI3b3234c0.0.SHM.35 >
< /tmp/masterds, 1712, 00000000016A72E0, replication, Global\DBI3b3234c0.0.SHM.35 >
< /tmp/masterds, 1712, 0000000001683DE8, replication, Global\DBI3b3234c0.0.SHM.35 >
< /tmp/masterds, 1620, 0000000000608128, application, Global\DBI3b3234c0.0.SHM.35 >
4 rows found.
The output from ttDataStoreStatus is similar to that shown for the ttStatus utility in "From the command line: ttStatus"
Example 13-4 Using ttDataStoreStatus in a SQLExecDirect function
You can also call ttDataStoreStatus within a SQLExecDirect function to obtain the status of the masterds replication agent:
#define STATUS_LEN 30
UCHAR status[STATUS_LEN];
rc = SQLExecDirect( hstmt, (SQLCHAR *)
"CALL ttDataStoreStatus ('/tmp/masterds')", SQL_NTS );
if (rc == SQL_SUCCESS) {
SQLBindCol(hstmt, 4, SQL_C_CHAR, status, STATUS_LEN, &cbStat);
}
You can display information for a master database:
To display information for a master database from the command line, use the ttRepAdmin utility with the -self -list options:
ttRepAdmin -dsn masterDSN -self -list
Example 13-5 Using ttRepAdmin to display information about a master database
This example shows the output for the master database described in "Multiple subscriber schemes with return services and a log failure threshold".
> ttRepAdmin -dsn masterds -self -list Self host "server1", port auto, name "masterds", LSN 0/2114272
The following table describes the fields.
| Field | Description |
|---|---|
host |
The name of the host machine for the database. |
port |
TCP/IP port used by a replication agent of another database to receive updates from this database. A value of 0 (zero) indicates replication has automatically assigned the port. |
name |
Name of the database |
Log file/Replication hold LSN |
Indicates the oldest location in the transaction log that is held for possible transmission to the subscriber. A value of -1/-1 indicates replication is in the Stop state with respect to all subscribers. |
To obtain the information for a master database from a program, use the following SQL SELECT statement to query the TTREP.TTSTORES and TTREP.REPSTORES tables:
SELECT t.host_name, t.rep_port_number, t.tt_store_name
FROM ttrep.ttstores t, ttrep.repstores s
WHERE t.is_local_store = 0x01
AND t.tt_store_id = s.tt_store_id;
Use the ttBookmark procedure to obtain the replication hold LSN, as described in "Show replicated log records".
This is the output of the above SELECT statement for the master database described in "Multiple subscriber schemes with return services and a log failure threshold". The fields are the host name, the replication port number, and the database name.
< server1, 0, masterds>
Example 13-6 Using ttBookmark to obtain the replication hold LSN
Call the ttBookmark procedure to obtain the replication hold LSN.
> ttIsql masterds Command> call ttBookMark(); < 10, 928908, 10, 280540, 10, 927692 > 1 row found.
The output fields are defined as follows:
| Column | Data Type | Description |
|---|---|---|
writeLFN |
TT_INTEGER |
Last written transaction log file |
writeLFO |
TT_INTEGER |
Last written offset in transaction log file |
forceLFN |
TT_INTEGER |
Last transaction log file forced to disk |
forceLFO |
TT_INTEGER |
Offset of last transaction log file forced to disk |
holdLFN |
TT_INTEGER |
Replication bookmark transaction log file |
holdLFO |
TT_INTEGER |
Replication bookmark log offset |
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 log data accumulates. Part of the output from the queries described in this section allows you to see how much log data has accumulated on behalf of each subscriber database and the amount of time since the last successful communication with each subscriber database.
You can display information for subscriber databases:
To display information about a master database's subscribers from the command line, use the ttRepAdmin utility with the -receiver -list options:
ttRepAdmin -dsn masterDSN -receiver -list
Example 13-7 Using ttRepAdmin to display information about subscribers
This example shows the output for the subscribers described in "Multiple subscriber schemes with return services and a log failure threshold".
> 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.
See Example 13-9 for a description of each field in the ttRepAdmin output.
If you have more than one scheme specified in your TTREP.REPLICATIONS table, you must use the -scheme option to specify which scheme you wish to list. Otherwise you receive the following error:
Must specify -scheme to identify which replication scheme to use
For the latest troubleshooting information, "Troubleshooting Replication" in Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide.
You can obtain more detailed status for a specific replicated database from a program by using the ttReplicationStatus built-in procedure.
Example 13-8 Using ttReplicationStatus from a program
You can use ttReplicationStatus to obtain the replication status of the subscriberds database in relation to its master database. From the master database, enter:
> ttIsql masterds
Command> CALL ttReplicationStatus ('subscriberds');
< subscriberds, myhost, 0, start, 1, 152959, repscheme, repl>
1 row found.
See Example 13-9 for an explanation of the output fields.
Example 13-9 Using ttReplicationStatus from a SQLExecDirect function
You can also call ttReplicationStatus within a SQLExecDirect function to obtain the replication status of the subscriberds database:
#define STATUS_LEN 30
UCHAR status[STATUS_LEN];
rc = SQLExecDirect( hstmt, (SQLCHAR *)
"CALL ttReplicationStatus ('subscriberds')", SQL_NTS );
if (rc == SQL_SUCCESS) {
SQLBindCol(hstmt, 4, SQL_C_CHAR, status, STATUS_LEN, &cbStat);
}
The columns in the returned row are shown in the following table:
| Column | Description |
|---|---|
Subscriber name |
Name of the subscriber database. |
Host name |
Name of the machine that hosts the subscriber. |
Port |
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. |
State |
Current state of the subscriber with respect to its master database (see "Setting the replication state of subscribers" for information). |
Logs |
Number of transaction log files the master database is retaining for this subscriber. |
Last Msg Sent |
Time (in seconds) since the master sent the last message to the subscriber. This includes the "heartbeat" messages sent between the databases. |
Replication scheme name |
The name of the replication scheme used. |
Owner name |
The name of the owner of the replication scheme. |
To obtain information about a master's subscribers from a program, use the following SQL SELECT statement to query the TTREP.REPPEERS, TTREP.TTSTORES, and SYS.MONITOR tables:
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 = t2.tt_store_id
AND t2.is_local_store = 0X01
AND p.subscriber_id = t1.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 SELECT 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 >
The output from either the ttRepAdmin utility or the SQL SELECT statement contains the following fields:
| Field | Description |
|---|---|
Peer name |
Name of the subscriber database |
Host name |
Name of the machine that hosts the subscriber |
Port |
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. |
State |
Current replication state of the subscriber with respect to its master database (see "Show subscriber database information" for information). |
Protocol |
Internal protocol used by replication to communicate between this master and its subscribers. You can ignore this value. |
Last message sent |
Time (in seconds) since the master sent the last message to the subscriber. This includes the "heartbeat" messages sent between the databases. |
Last message received |
Time (in seconds) since this subscriber received the last message from the master. |
Latency |
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.) |
Transactions per second |
The average number of transactions per second that are committed on the master and processed by the subscriber. (See note below.) |
Records per second |
The average number of transmitted records per second. (See note below.) |
Logs |
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.You can use the ttRepSubscriberSync built-in procedure to verify that the subscriber from which you call the procedure has received all committed updates from the master database, has applied the updates and has committed them durably.
Example 13-10 Verifying that a subscriber has received all updates
The name of the replication scheme is simple. The owner of the replication scheme is ttuser. The name of the master database is master1. The master host is host1.The built-in procedure waits up to 300 seconds to see whether the subscriber has received and applied all updates that were committed on the master at the time of the call.
Command> call ttRepSubscriberSync('simple','ttuser','master1','host1',300);
The built-in procedure returns 0x00 if the subscriber has received all updates and 0x01 if the subscriber has not received all updates by the time 300 seconds has passed.
Example 13-11 Verifying that a standby database has received all updates
You can use the ttRepSubscriberSync built-in procedure to verify whether the standby database in an active standby pair has received and applied all updates from the active database. Call the procedure from the standby database. The name of an active standby pair replication scheme is always _activestandby. You can verify the owner of the active standby pair by checking the TTREP.REPLICATIONS system table.
Command> call ttRepSubscriberSync
> ('_activestandby','ttuser','activeDS','activehost',300);
You can display the configuration of your replicated databases:
To display the configuration of your replicated databases from the ttIsql prompt, use the repschemes command:
Command> repschemes;
Example 13-12 shows the configuration output from the replication scheme shown in "Propagation scheme".
Example 13-12 Output from ttIsql repschemes command
Replication Scheme PROPAGATOR:
Element: A
Type: Table TAB
Master Store: CENTRALDS on FINANCE Transmit Durable
Subscriber Store: PROPDS on NETHANDLER
Element: B
Type: Table TAB
Propagator Store: PROPDS on NETHANDLER Transmit Durable
Subscriber Store: BACKUP1DS on BACKUPSYSTEM1
Subscriber Store: BACKUP2DS on BACKUPSYSTEM2
Store: BACKUP1DS on BACKUPSYSTEM1
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled
Store: BACKUP2DS on BACKUPSYSTEM2
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled
Store: CENTRALDS on FINANCE
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled
Store: PROPDS on NETHANDLER
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled
To display the configuration of your replicated databases from the command line, use the ttRepAdmin utility with the -showconfig option:
ttRepAdmin -showconfig -dsn masterDSN
Example 13-13 shows the configuration output from the propagated databases configured by the replication scheme shown in "Propagation scheme". The propds propagator shows a latency of 19.41 seconds and is 2 logs behind the master.
Example 13-13 ttRepAdmin output
> ttRepAdmin -showconfig -dsn centralds Self host "finance", port auto, name "centralds", LSN 0/155656, timeout 120, threshold 0 List of subscribers ----------------- Peer name Host name Port State Proto ---------------- ------------------------ ------ ------- ----- propds nethandler Auto Start 10 Last Msg Sent Last Msg Recv Latency TPS RecordsPS Logs ------------- ------------- ------- ------- --------- ---- 0:01:12 - 19.41 5 52 2 List of tables and subscriptions -------------------------------- Table details ------------- Table : tab Timestamp updates : - Master Name Subscriber Name ----------- ------------- centralds propds Table details ------------- Table : tab Timestamp updates : - Master Name Subscriber name ----------- ------------- propds backup1ds propds backup2ds
See Example 13-9 for the meaning of the "List of subscribers" fields. The "Table details" fields list the table and the names of its master (Sender) and subscriber databases.
To display the configuration of your replicated databases from a program, use the following SQL SELECT statements to query the TTREP.TTSTORES, TTREP.REPSTORES, TTREP.REPPEERS, SYS.MONITOR, TTREP.REPELEMENTS, and TTREP.REPSUBSCRIPTIONS tables:
SELECT t.host_name, t.rep_port_number, t.tt_store_name, s.peer_timeout, s.fail_threshold
FROM ttrep.ttstores t, ttrep.repstores s
WHERE t.is_local_store = 0X01
AND t.tt_store_id = s.tt_store_id;
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 = t2.tt_store_id
AND t2.is_local_store = 0X01
AND p.subscriber_id = t1.tt_store_id
AND (p.state = 0 OR p.states = 1);
SELECT ds_obj_owner, DS_OBJ_NAME, t1.tt_store_name,t2.tt_store_name
FROM ttrep.repelements e, ttrep.repsubscriptions s,
ttrep.ttstores t1, ttrep.ttstores t2
WHERE s.element_name = e.element_name
AND e.master_id = t1.tt_store_id
AND s.subscriber_id = t2.tt_store_id
ORDER BY ds_obj_owner, ds_obj_name;
Use the ttBookmark procedure to obtain the replication hold LSN, as described in "From a program: ttBookMark procedure".
Example 13-14 Output from SELECT queries
The output from the above queries for the databases configured by the replication scheme shown in "Propagation scheme" might look like the following:
< finance, 0, centralds, 120, 0 > < propds, nethandler, 0, 0, 7, 1004378953, 0, -1.00000000000000, -1, -1, 1 > < repl, tab, centralds, propds > < repl, tab, propds, backup1ds > < repl, tab, propds, backup2ds >
See Example 13-6 for descriptions for the first three columns in the first row. The fourth column is the TIMEOUT value that defines the amount of time a database waits for a response from another database before resending a message. The last column is the log failure threshold value described in "Setting the log failure threshold".
See Example 13-9 for a description of the second row. The last three rows show the replicated table and the names of its master (sender) and subscriber (receiver) databases.
Transactions are stored in the log in the form of log records. You can use bookmarks to detect which log records have or have not been replicated by a master database.
A bookmark consists of log sequence numbers (LSNs) that identify the location of particular records in the transaction log that you can use to gauge replication performance. The LSNs associated with a bookmark are: hold LSN, last written LSN, and last LSN forced to disk. The hold LSN describes the location of the lowest (or oldest) record held in the log for possible transmission to a subscriber. You can compare the hold LSN with the last written LSN to determine the amount of data in the transaction log that have not yet been transmitted to the subscribers. The last LSN forced to disk describes the last records saved in a transaction log file on disk.
A more accurate way to monitor replication to a particular subscriber is to look at the send LSN for the subscriber, which consists of the SENDLSNHIGH and SENDLSNLOW fields in the TTREP.REPPEERS table. In contrast to the send LSN value, the hold LSN returned in a bookmark is computed every 10 seconds to describe the minimum send LSN for all the subscribers, so it provides a more general view of replication progress that does not account for the progress of replication to the individual subscribers. Because replication acknowledgements are asynchronous for better performance, the send LSN can also be some distance behind. Nonetheless, the send LSN for a subscriber is the most accurate value available and is always ahead of the hold LSN.
You can display replicated log records:
To display the location of the bookmarks from the command line, use the ttRepAdmin utility with the -bookmark option:
> ttRepAdmin -dsn masterds -bookmark Replication hold LSN ...... 10/927692 Last written LSN .......... 10/928908 Last LSN forced to disk ... 10/280540 Each LSN is defined by two values: Log file number / Offset in log file
The LSNs output from ttRepAdmin -bookmark are:
| Line | Description |
|---|---|
Replication hold LSN |
The location of the lowest (or oldest) record held in the log for possible transmission to a subscriber. A value of -1/-1 indicates replication is in the Stop state with respect to all subscribers (or the queried database is not a master database). |
Last written LSN |
The location of the most recently generated transaction log record for the database. |
Last LSN forced to disk |
The location of the most recent transaction log record written to the disk. |
To display the location of the bookmarks from a program, use the ttBookmark procedure.
Example 13-15 Using ttBookmark to display bookmark location
> ttIsql masterds Command> call ttBookMark(); < 10, 928908, 10, 280540, 10, 927692 > 1 row found.
The first two columns in the returned row define the "Last written LSN," the next two columns define the "Last LSN forced to disk," and the last two columns define the "Replication hold LSN."
You can use the ttRepAdmin utility with the -showstatus option to display the current status of the replication agent. The status output includes the bookmark locations, port numbers, and communication protocols used by the replication agent for the queried database.
The output from ttRepAdmin -showstatus includes the status of the main thread and the TRANSMITTER and RECEIVER threads used by the replication agent. A master database has a TRANSMITTER thread and a subscriber database has a RECEIVER thread. A database that serves a master/subscriber role in a bidirectional replication scheme has both a TRANSMITTER and a RECEIVER thread.
Each replication agent has a single REPLISTENER thread that listens on a port for peer connections. On a master database, the REPLISTENER thread starts a separate TRANSMITTER thread for each subscriber database. On a subscriber database, the REPLISTENER thread starts a separate RECEIVER thread for each connection from a master.
If the TimesTen daemon requests that the replication agent stop or if a fatal error occurs in any of the other threads used by the replication agent, the main thread waits for the other threads to gracefully terminate. The TimesTen daemon may or may not restart the replication agent, depending upon certain fatal errors. The REPLISTENER thread never terminates during the lifetime of the replication agent. A TRANSMITTER or RECEIVER thread may stop but the replication agent may restart it. The RECEIVER thread terminates on errors from which it cannot recover or when the master disconnects.
Example 13-15 shows ttRepAdmin -showstatus output for a unidirectional replication scheme in which the rep1 database is the master and rep2 database is the subscriber. The first ttRepAdmin -showstatus output shows the status of the rep1 database and its TRANSMITTER thread. The second output shows the status of the rep2 database and its RECEIVER thread.
Following the example are sections that describe the meaning of each field in the ttRepAdmin -showstatus output:
Example 13-16 Unidirectional replication scheme
Consider the unidirectional replication scheme from the rep1 database to the rep2 database:
CREATE REPLICATION r ELEMENT e1 TABLE t MASTER rep1 SUBSCRIBER rep2;
The replication status for the rep1 database should look similar to the following:
> ttRepAdmin -showstatus rep1
DSN : rep1
Process ID : 1980
Replication Agent Policy : MANUAL
Host : MYHOST
RepListener Port : 1113 (AUTO)
Last write LSN : 0.1487928
Last LSN forced to disk : 0.1487928
Replication hold LSN : 0.1486640
Replication Peers:
Name : rep2
Host : MYHOST
Port : 1154 (AUTO)
Replication State : STARTED
Communication Protocol : 12
TRANSMITTER thread(s):
For : rep2
Start/Restart count : 2
Send LSN : 0.1485960
Transactions sent : 3
Total packets sent : 10
Tick packets sent : 3
MIN sent packet size : 48
MAX sent packet size : 460
AVG sent packet size : 167
Last packet sent at : 17:41:05
Total Packets received: 9
MIN rcvd packet size : 48
MAX rcvd packet size : 68
AVG rcvd packet size : 59
Last packet rcvd'd at : 17:41:05
Earlier errors (max 5):
TT16060 in transmitter.c (line 3590) at 17:40:41 on 08-25-2004
TT16122 in transmitter.c (line 2424) at 17:40:41 on 08-25-2004
Note that the Replication hold LSN, the Last write LSN and the Last LSN forced to disk are very close, which indicates that replication is operating satisfactorily. If the Replication hold LSN falls behind the Last write LSN and the Last LSN, then replication is not keeping up with updates to the master.
The replication status for the rep2 database should look similar to the following:
> ttRepAdmin -showstatus rep2 DSN : rep2 Process ID : 2192 Replication Agent Policy : MANUAL Host : MYHOST RepListener Port : 1154 (AUTO) Last write LSN : 0.416464 Last LSN forced to disk : 0.416464 Replication hold LSN : -1.-1 Replication Peers: Name : rep1 Host : MYHOST Port : 0 (AUTO) Replication State : STARTED Communication Protocol : 12 RECEIVER thread(s): For : rep1 Start/Restart count : 1 Transactions received : 0 Total packets sent : 20 Tick packets sent : 0 MIN sent packet size : 48 MAX sent packet size : 68 AVG sent packet size : 66 Last packet sent at : 17:49:51 Total Packets received: 20 MIN rcvd packet size : 48 MAX rcvd packet size : 125 AVG rcvd packet size : 52 Last packet rcvd'd at : 17:49:51
The following fields are output for the MAIN thread in the replication agent for the queried database.
| MAIN Thread | Description |
|---|---|
DSN |
Name of the database to be queried. |
Process ID |
Process Id of the replication agent. |
Replication Agent Policy |
The restart policy, as described in "Starting and stopping the replication agents" |
Host |
Name of the machine that hosts this database. |
RepListener Port |
TCP/IP port used by the replication agent to listen for connections from the TRANSMITTER threads of remote replication agents. A value of 0 indicates that this port has been assigned automatically to the replication agent (the default), rather than being specified as part of a replication scheme. |
Last write LSN |
The location of the most recently generated transaction log record for the database. See "Show replicated log records" for more information. |
Last LSN forced to disk |
The location of the most recent transaction log record written to the disk. See "Show replicated log records" for more information. |
Replication hold LSN |
The location of the lowest (or oldest) record held in the log for possible transmission to a subscriber. A value of -1/-1 indicates replication is in the Stop state with respect to all subscribers. See "Show replicated log records" for more information. |
The following fields are output for each replication peer that participates in the replication scheme with the queried database. A "peer" could play the role of master, subscriber, propagator or both master and subscriber in a bidirectional replication scheme.
| Replication Peers | Description |
|---|---|
Name |
Name of a database that is a replication peer to this database. |
Host |
Host machine of peer database. |
Port |
TCP/IP port used by the replication agent for the peer database. A value of 0 indicates this port has been assigned automatically to the replication agent (the default), rather than being specified as part of a replication scheme. |
Replication State |
Current replication state of the replication peer with respect to the queried database (see "Show subscriber database information" for information). |
Communication Protocol |
Internal protocol used by replication to communicate between the peers. (For internal use only.) |
The following fields are output for each TRANSMITTER thread used by a master replication agent to send transaction updates to a subscriber. A master with multiple subscribers has multiple TRANSMITTER threads.
Note:
The counts in theTRANSMITTER output begin to accumulate when the replication agent is started. These counters are reset to 0 only when the replication agent is started or restarted.| TRANSMITTER Thread | Description |
|---|---|
For |
Name of the subscriber database that is receiving replicated data from this database. |
Start/Restart count |
Number of times this TRANSMITTER thread was started or restarted by the replication agent due to a temporary error, such as operation timeout, network failure, and so on. |
Send LSN |
The last LSN transmitted to this peer. See "Show replicated log records" for more information. |
Transactions sent |
Total number of transactions sent to the subscriber. |
Total packets sent |
Total number of packets sent to the subscriber (including tick packets) |
Tick packets sent |
Total number of tick packets sent. Tick packets are used to maintain a "heartbeat" between the master and subscriber. You can use this value to determine how many of the 'Total packets sent' packets are not related to replicated data. |
MIN sent packet size |
Size of the smallest packet sent to the subscriber. |
MAX sent packet size |
Size of the largest packet sent to the subscriber. |
AVG sent packet size |
Average size of the packets sent to the subscriber. |
Last packet sent at |
Time of day last packet was sent (24-hour clock time) |
Total packets received |
Total packets received from the subscriber (tick packets and acknowledgement data) |
MIN rcvd packet size |
Size of the smallest packet received |
MAX rcvd packet size |
Size of the largest packet received |
AVG rcvd packet size |
Average size of the packets received |
Last packet rcvd at |
Time of day last packet was received (24-hour clock time) |
Earlier errors (max 5) |
Last five errors generated by this thread |
The following fields are output for each RECEIVER thread used by a subscriber replication agent to receive transaction updates from a master. A subscriber that is updated by multiple masters has multiple RECEIVER threads.
Note:
The counts in theRECEIVER output begin to accumulate when the replication agent is started. These counters are reset to 0 only when the replication agent is started or restarted.| RECEIVER Thread | Description |
|---|---|
For |
Name of the master database that is sending replicated data from this database |
Start/Restart count |
Number of times this RECEIVER thread was started or restarted by the replication agent due to a temporary error, such as operation timeout, network failure, and so on. |
Transactions received |
Total number of transactions received from the master |
Total packets sent |
Total number of packets sent to the master (tick packets and acknowledgement data) |
Tick packets sent |
Total number of tick packets sent to the master. Tick packets are used to maintain a "heartbeat" between the master and subscriber. You can use this value to determine how many of the 'Total packets sent' packets are not related to acknowledgement data. |
MIN sent packet size |
Size of the smallest packet sent to the master |
MAX sent packet size |
Size of the largest packet sent to the master |
AVG sent packet size |
Average size of the packets sent to the master |
Last packet sent at |
Time of day last packet was sent to the master (24-hour clock time) |
Total packets received |
Total packets of acknowledgement data received from the master |
MIN rcvd packet size |
Size of the smallest packet received |
MAX rcvd packet size |
Size of the largest packet received |
AVG rcvd packet size |
Average size of the packets received |
Last packet rcvd at |
Time of day last packet was received (24-hour clock time) |
You can determine whether the return service for a particular subscriber has been disabled by the DISABLE RETURN failure policy by calling the ttRepSyncSubscriberStatus built-in procedure or by means of the SNMP trap, ttRepReturnTransitionTrap. The ttRepSyncSubscriberStatus procedure returns a value of '1' to indicate the return service has been disabled for the subscriber, or a value of '0' to indicate that the return service is still enabled.
Example 13-17 Using ttRepSyncSubscriberStatus to obtain return receipt status
To use ttRepSyncSubscriberStatus to obtain the return receipt status of the subscriberds database with respect to its master database, masterDSN, enter:
> ttIsql masterDSN
Command> CALL ttRepSyncSubscriberStatus ('subscriberds');
< 0 >
1 row found.
This result indicates that the return service is still enabled.
See "DISABLE RETURN" for more information.
You can check the status of the last return receipt or return twosafe transaction executed on the connection handle by calling the ttRepXactTokenGet and ttRepXactStatus procedures.
First, call ttRepXactTokenGet to get a unique token for the last return service transaction. If you are using return receipt, the token identifies the last return receipt transaction committed on the master database. If you are using return twosafe, the token identifies the last twosafe transaction on the master that, in the event of a successful commit on the subscriber, is committed by the replication agent on the master. However, in the event of a timeout or other error, the twosafe transaction identified by the token is not committed by the replication agent on the master.
Next, pass the token returned by ttRepXactTokenGet to the ttRepXactStatus procedure to obtain the return service status. The output of the ttRepXactStatus procedure reports which subscriber or subscribers are configured to receive the replicated data and the current status of the transaction (not sent, received, committed) with respect to each subscriber. If the subscriber replication agent encountered a problem applying the transaction to the subscriber database, the ttRepXactStatus procedure also includes the error string. If you are using return twosafe and receive a timeout or other error, you can then decide whether to unconditionally commit or retry the commit, as described in "RETURN TWOSAFE".
Note:
IfttRepXactStatus is called without a token from ttRepXactTokenGet, it returns the status of the most recent transaction on the connection which was committed with the return receipt or return twosafe replication service.The ttRepXactStatus procedure returns the return service status for each subscriber as a set of rows formatted as:
subscriberName, status, error
Example 13-18 Reporting the status of each subscriber
For example, you can use ttRepXactTokenGet and ttRepXactStatus in a GetRSXactStatus function to report the status of each subscriber in your replicated system:
SQLRETURN GetRSXactStatus (HDBC hdbc)
{
SQLRETURN rc = SQL_SUCCESS;
HSTMT hstmt = SQL_NULL_HSTMT;
char xactId [4001] = "";
char subscriber [62] = "";
char state [3] = "";
/* get the last RS xact id executed on this connection */
SQLAllocStmt (hdbc, &hstmt);
SQLExecDirect (hstmt, "CALL ttRepXactTokenGet ('R2')", SQL_NTS);
/* bind the xact id result as a null terminated hex string */
SQLBindCol (hstmt, 1, SQL_C_CHAR, (SQLPOINTER) xactId,
sizeof (xactId), NULL);
/* fetch the first and only row */
rc = SQLFetch (hstmt);
/* close the cursor */
SQLFreeStmt (hstmt, SQL_CLOSE);
if (rc != SQL_ERROR && rc != SQL_NO_DATA_FOUND)
{
/* display the xact id */
printf ("\nRS Xact ID: 0x%s\n\n", xactId);
/* get the status of this xact id for every subscriber */
SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARBINARY, 0, 0,
(SQLPOINTER) xactId, strlen (xactId), NULL);
/* execute */
SQLExecDirect (hstmt, "CALL ttRepXactStatus (?)", SQL_NTS);
/* bind the result columns */
SQLBindCol (hstmt, 1, SQL_C_CHAR, (SQLPOINTER) subscriber,
sizeof (subscriber), NULL);
SQLBindCol (hstmt, 2, SQL_C_CHAR, (SQLPOINTER) state,
sizeof (state), NULL);
/* fetch the first row */
rc = SQLFetch (hstmt);
while (rc != SQL_ERROR && rc != SQL_NO_DATA_FOUND)
{
/* report the status of this subscriber */
printf ("\n\nSubscriber: %s", subscriber);
printf ("\nState: %s", state);
/* are there more rows to fetch? */
rc = SQLFetch (hstmt);
}
}
/* close the statement */
SQLFreeStmt (hstmt, SQL_DROP);
return rc;
}
To increase replication performance, consider these tips:
Use asynchronous replication, which is the default. For more information, see "Making decisions about performance and recovery tradeoffs". However, if you are using active standby pairs, return twosafe (synchronous replication) has better performance than return receipt (semi-synchronous replication).
Set the LogFileSize and LogBufMB first connection attributes to their maximum values. For more information, see "Setting connection attributes for logging".
If the workload is heavy enough that replication sometimes falls behind, replicated changes must be captured from the transaction logs on disk rather than from the in-memory log buffer. Using the fastest possible storage for the TimesTen transaction logs reduces I/O contention between transaction log flushing and replication capture and helps replication to catch up more quickly during periods of reduced workload. Consider using a high performance, cached disk array using a RAID-0 stripe across multiple fast disks or solid state storage.
Experiment with the number of connections to the database where the updates are applied. If you need more than 64 concurrent connections, set the Connections first connection attribute to a higher value. See "Connections" in Oracle TimesTen In-Memory Database Reference.