12 Monitoring Replication

This chapter describes some of the TimesTen utilities and built-in 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 Views Reference.

Note:

You can only access the TimesTen SYS and TTREP tables for queries. Do not try to alter the contents of these tables.

This chapter includes the following topics:

Show state of replication agents

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".

Using ttStatus to obtain replication agent status

Use the ttStatus utility to confirm that the replication agent is started for the master database.

Example 12-1 Using ttStatus to obtain replication agent status

> ttStatus
TimesTen status report as of Thu Aug 11 17:05:23 2011
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.

Using ttAdmin -query to confirm policy settings

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".

Example 12-2 Using ttAdmin to confirm policy settings

> ttAdmin -query masterDSN
RAM Residence Policy : inUse
Manually Loaded In Ram : False
Replication Agent Policy : manual
Replication Manually Started : True
Cache Agent Policy : manual
Cache Agent Manually Started : False

Using ttDataStoreStatus to obtain replication agent status

To obtain the status of the replication agents from a program, use the ttDataStoreStatus built-in procedure.

Example 12-3 Calling ttDataStoreStatus

Call the ttDataStoreStatus built-in procedure 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 "Using ttStatus to obtain replication agent status".

Show master database information

You can display information for a master database:

Using ttRepAdmin to display information about the master database

Use the ttRepAdmin utility with the -self -list options to display information about the master database:

ttRepAdmin -dsn masterDSN -self -list

Example 12-4 Using ttRepAdmin to display information about a master database

This example shows the output for the master database described in "Multiple subscriber classic replication 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 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.

Querying replication tables to obtain information about a master database

Use the following SELECT statement to query the TTREP.TTSTORES and TTREP.REPSTORES replication tables to obtain information about a master database:

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;

This is the output of the SELECT statement for the master database described in "Multiple subscriber classic replication 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>

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. Part of the output from the queries described in this section enables you to see how much transaction log data has accumulated on behalf of each subscriber database and the amount of time since the last successful communication with each subscriber database.

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

Example 12-5 Using ttRepAdmin to display information about subscribers

This example shows the output for the subscribers described in "Multiple subscriber classic replication 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.

Note:

See "Subscriber information" for details on the subscriber information displayed.

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

For more information, 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.

Example 12-6 Display status of a single subscriber of this master

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.

Example 12-7 Display status for all subscribers of this master

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. 

For more information, 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 the following 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 >

See "Subscriber information" for details on the subscriber information displayed.

Subscriber information

The output from either the ttRepAdmin utility or the 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.

Show the configuration of replicated databases

You can display the configuration of your replicated databases:

Display configuration information with the ttIsql repschemes command

To display the configuration of your replicated databases from the ttIsql prompt, use the repschemes command:

Command> repschemes;

Example 12-8 shows the configuration output from the replication scheme shown in "Propagation scheme".

Example 12-8 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

Display configuration information with the ttRepAdmin utility

To display the configuration of your replicated databases, use the ttRepAdmin utility with the -showconfig option:

ttRepAdmin -showconfig -dsn masterDSN

Example 12-9 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 12-9 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 "Display information about subscribers through querying replication tables" 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.

Display configuration information through querying replication tables

Use the following SELECT statements to query the TTREP.TTSTORES, TTREP.REPSTORES, TTREP.REPPEERS, SYS.MONITOR, TTREP.REPELEMENTS, and TTREP.REPSUBSCRIPTIONS tables for configuration information:

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;

Example 12-10 Output from queries

The output from the queries refer to the databases configured by the replication scheme shown in "Propagation scheme".

The output from the first query might be:

< finance, 0, centralds, 120, 0 >

It shows the host name, port number and the database name. The fourth value (120) 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 value (0) is the log failure threshold value described in "Setting the transaction log failure threshold".

The output from the second query might be:

< propds, nethandler, 0, 0, 7, 1004378953, 0, -1.00000000000000, -1, -1, 1 >

See "Display information about subscribers through querying replication tables" for a description of the fields.

The output from the last query might be:

< repl, tab, centralds, propds >
< repl, tab, propds, backup1ds >
< repl, tab, propds, backup2ds >

The rows show the replicated table and the names of its master (sender) and subscriber (receiver) databases.

Show replicated log records

In a replicated database, transactions remain in the transaction log buffer and transaction log files until the master replication agent confirms they have been fully processed by the subscriber. In an active standby pair replication scheme that contains subscribers, transactions remain in the transaction logs until the active master confirms that they are processed by both the standby master and any subscribers. Only then can the active master consider purging them from the log buffer and transaction log files. When the log space is exhausted, subsequent updates on the master database are aborted.

Note:

For more information about transaction log growth, see "Monitoring accumulation of transaction log files" in Oracle TimesTen In-Memory Database Operations Guide.

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.

You can monitor replication through bookmarks and the log sequence numbers with the following tools:

Monitor replication with the TTREP.REPPEERS table

An 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.

Monitor replication with the ttLogHolds built-in procedure

Use the ttLogHolds built-in procedure to get information about replication log holds.

The following example shows the output of ttLogHolds built-in procedure for an active standby pair replication scheme, where the active master is master1 and the standby master is master2, with a single subscriber, subscriber1. All transactions are replicated from the active master first to the standby master who then propagates the transactions to the subscriber. Thus, the subscriber's progress is slightly behind the standby master's progress.

The active master monitors the progress of both the standby master and the subscriber; therefore, if the standby master goes down for any reason, the active master can take over the replication to the subscriber. The active master receives acknowledgements when transactions are applied on the subscriber so the active master knows when it can release pertinent log records that might be needed if the standby master fails (upon which the active master switches to replicate directly to the subscribers). The transactions remain in the transaction logs until they are processed on both the standby master and the subscriber.

Command> call ttLogHolds;
< 0, 3569664, Checkpoint                    , master1.ds0 >
< 0, 15742976, Checkpoint                    , master1.ds1 >
< 0, 16351496, Replication                   , ADC6160529:SUBSCRIBER1 >
< 0, 16351640, Replication                   , ADC6160529:MASTER2 >
4 rows found.

If you are using an AWT cache group, it uses the replication agent to asynchronously propagate transactions to the Oracle database. When you call the ttLogHolds built-in procedure, the description field contains "_ORACLE" to identify the transaction log hold for the AWT cache group propagation.

Command> call ttLogHolds();
< 0, 18958336, Checkpoint                    , cachealone1.ds0 >
< 0, 19048448, Checkpoint                    , cachealone1.ds1 >
< 0, 19050904, Replication                   , ADC6160529:_ORACLE >
3 rows found.

For more details on the ttLogHolds built-in procedure, see "ttLogHolds" in the Oracle TimesTen In-Memory Database Reference.

Monitor replication with the ttRepAdmin utility

Use the ttRepAdmin utility with the -bookmark option to display the location of bookmarks:

> 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).

If you are monitoring an active standby pair with one or more subscribers, then this value denotes the oldest record held for all nodes involved in the replication scheme. For example, in an active standby pair with subscribers, the oldest record could be held in the log for the standby master or any of the subscribers.

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.

Monitor replication with the ttBookMark built-in procedure

Use the ttBookmark built-in procedure to display the location of bookmarks.

Example 12-11 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."

If you are monitoring an active standby pair with one or more subscribers, then the "Replication hold LSN" denotes the oldest record held for all nodes involved in the replication scheme. For example, in an active standby pair with subscribers, the oldest record could be held in the log for the standby master or any of the subscribers.

Use ttRepAdmin to show replication status

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 12-11 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 12-12 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

MAIN thread status fields

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.

Replication peer status fields

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 of the 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.)

TRANSMITTER thread status fields

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 the TRANSMITTER 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.

RECEIVER thread status fields

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 the RECEIVER 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).

Check the status of return service transactions

The following sections describe how to determine status of a return service or to find out what the last returned status was:

Determine if return service is disabled

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 built-in 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 12-13 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.

For information on how to enable or disable the return service, see "Managing return service timeout errors and replication state changes".

Check last returned status for a return service

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 built-in procedures.

First, call the ttRepXactTokenGet built-in procedure 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 built-in procedure to obtain the return service status. The output of the ttRepXactStatus built-in 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 built-in 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. This is described in "Using a return service".

Note:

If ttRepXactStatus 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 built-in procedure returns the return service status for each subscriber as a set of rows formatted as:

subscriberName, status, error

Example 12-14 Reporting the status of each subscriber

You can call the ttRepXactTokenGet and ttRepXactStatus built-in procedures 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;
}

Analyze outstanding transactions in the replication log

You can use the -logAnalyze command in the ttXactLog utility to analyze the replication logs and determine the following:

  • Measure how much is left to replicate from a master to any subscribers at the current time. When replication seems to be taking longer than expected, you can determine how many transactions are left to replicate or if replication is processing a long-running transaction.

  • Measure if the current configuration distributes the load appropriately across all manual and automatic tracks for parallel replication.

Execute the log analyze command against a particular data store to generate the following information:

  • The number of transactions that are waiting to be replicated. For each transaction that has not been replicated, the information collected includes the number of operations for each transaction and the total size of each transaction (including partial rollbacks).

  • The amount of operations left in each transaction including its specific type (either DDL or DML) and how many of each statement type are in each transaction. The tool also generates the total size of every operation left to replicate.

  • Retrieves information on how the workload is split across tracks. When you use manual parallel replication, you can use -logAnalyze to monitor whether the application is distributing work evenly across the replication tracks.

  • Returns the largest transaction left to replicate.

  • Returns the start and end LSN. The start LSN is the starting point in the transaction log where the transmitter starts reading; the end LSN is the end of the transaction log.

Note:

In the transaction log analysis output, transactions are shown in commit order.

You can specify how much information is displayed with the verbose command. For example, when you set verbose to 1, the following is displayed:

$ ttXactLog -v1 -logAnalyze rep1
Summary:
Total transactions left to replicate: 4
Total rows left to replicate: 4
Size of transactions left to replicate: 1.86 KiB
Size of rows left to replicate: 488.00 B
Total inserts remaining: 4
Total partial rollbacks: 5
Total rollbacks: 3

Start LSN = 0.3793736
End LSN = 0.18769920 

If a subscriber is specified, then the summary displays only for that particular subscriber. However, by default, the summary is displayed for all subscribers.

When you specify verbose to 2, then the information includes both a summary of each parallel track in addition to the overall summary information.

$ ttXactLog -v2 -logAnalyze rep1

Track analysis for track number: 0
Transactions left to replicate: 2
Rows left to replicate: 2
Size of transactions left to replicate: 880.00 B
Size of rows left to replicate: 312.00 B
Total inserts remaining: 1
Total partial rollbacks: 4

Track analysis for track number: 1
Transactions left to replicate: 2
Rows left to replicate: 2
Size of transactions left to replicate: 1.14 KiB
Size of rows left to replicate: 244.00 B
Total inserts remaining: 2
Total partial rollbacks: 1
Total rollbacks: 3

Summary:
Total transactions left to replicate: 4
Total rows left to replicate: 4
Size of transactions left to replicate: 2.00 KiB
Size of rows left to replicate: 556.00 B
Total inserts remaining: 3
Total partial rollbacks: 5
Total rollbacks: 3

Start LSN = 0.3793736
End LSN = 0.20506624

When you provide the verbose level 3, the information generated includes a transaction analysis that includes a description of the contents of every transaction in every track:

$ ttXactLog -v3 -logAnalyze rep1

Transaction id:    3.10
Track for this xid: 1
Logmarker before this xid: 275
Rows left to replicate: 1
Transaction size: 800.00 B
Size of rows left: 122.00 B
Total inserts remaining: 1
 
Transaction id:    2.1
Track for this xid: 0
Logmarker before this xid: 276
Rows left to replicate: 1
Transaction size: 368.00 B
Size of rows left: 122.00 B
Total inserts remaining: 1
 
Transaction id:    2.19
Track for this xid: 1
Logmarker before this xid: 823
Rows left to replicate: 1
Transaction size: 368.00 B
Size of rows left: 122.00 B
Total inserts remaining: 1
 
Transaction id:    3.2
Track for this xid: 0
Logmarker before this xid: 842
Rows left to replicate: 1
Transaction size: 368.00 B
Size of rows left: 122.00 B
Total inserts remaining: 1
 
Track analysis for track number: 0
Transactions left to replicate: 2
Rows left to replicate: 2
Size of transactions left to replicate: 736.00 B
Size of rows left to replicate: 244.00 B
Total inserts remaining: 2
Total partial rollbacks: 4
 
Track analysis for track number: 1
Transactions left to replicate: 2
Rows left to replicate: 2
Size of transactions left to replicate: 1.14 KiB
Size of rows left to replicate: 244.00 B
Total inserts remaining: 2
Total partial rollbacks: 1
Total rollbacks: 3
 
Summary:
Total transactions left to replicate: 4
Total rows left to replicate: 4
Size of transactions left to replicate: 1.86 KiB
Size of rows left to replicate: 488.00 B
Total inserts remaining: 4
Total partial rollbacks: 5
Total rollbacks: 3
 
Start LSN = 0.3793736
End LSN = 0.21444608

When you specify the XID, the tool displays verbose level 3 output where the transaction analysis is based on the XID. If an XID is used by two separate transactions, the report shows with the LogMarker entry the nearest point in the transaction log just before the start of each transaction.

$ ttXactLog -logAnalyze -xid 2.19 rep1;

Transaction id:    2.19
Track for this xid: 1
Logmarker before this xid: 823
Rows left to replicate: 1
Transaction size: 368.00 B
Size of rows left: 122.00 B
Total inserts remaining: 1
 
Track analysis for track number: 0
Transactions left to replicate: 0
Rows left to replicate: 0
Size of transactions left to replicate: 0.00 B
Size of rows left to replicate: 0.00 B
 
Track analysis for track number: 1
Transactions left to replicate: 1
Rows left to replicate: 1
Size of transactions left to replicate: 368.00 B
Size of rows left to replicate: 122.00 B
Total inserts remaining: 1
 
Summary:
Total transactions left to replicate: 1
Total rows left to replicate: 1
Size of transactions left to replicate: 368.00 B
Size of rows left to replicate: 122.00 B
Total inserts remaining: 1
 
Start LSN = 0.3793736
End LSN = 0.20514816 

Note:

For more details, see "ttXactLog" in the Oracle TimesTen In-Memory Database Reference.