11 Monitoring Replication

Example 11-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 ttuserTimesTen server pid 18381 started on port 4136------------------------------------------------------------------------Data store /tmp/masterdsThere are 16 connections to the data storeShared Memory KEY 0x0201ab43 ID 5242889PL/SQL Memory KEY 0x0301ab43 ID 5275658 Address 0x10000000Type            PID     Context     Connection Name              ConnIDProcess         20564   0x081338c0  masterds                          1Replication     20676   0x08996738  LOGFORCE                          5Replication     20676   0x089b69a0  REPHOLD                           2Replication     20676   0x08a11a58  FAILOVER                          3Replication     20676   0x08a7cd70  REPLISTENER                       4Replication     20676   0x08ad7e28  TRANSMITTER                       6Subdaemon       18379   0x080a11f0  Manager                        2032Subdaemon       18379   0x080fe258  Rollback                       2033Subdaemon       18379   0x081cb818  Checkpoint                     2036Subdaemon       18379   0x081e6940  Log Marker                     2035Subdaemon       18379   0x08261e70  Deadlock Detector              2038Subdaemon       18379   0xae100470  AsyncMV                        2040Subdaemon       18379   0xae11b508  HistGC                         2041Subdaemon       18379   0xae300470  Aging                          2039Subdaemon       18379   0xae500470  Flusher                        2034Subdaemon       18379   0xae55b738  Monitor                        2037Replication policy  : ManualReplication agent is running.Cache Agent policy  : ManualPL/SQL enabled.

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

Example 11-3 Calling ttDataStoreStatus

Call ttDataStoreStatus to obtain the status of the replication agents for the masterds data stores:

> 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 11-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);
  }

Example 11-5 Using ttRepAdmin to display information about a master data store

This example shows the output for the master data store described in "Multiple subscriber schemes with return services and a failure threshold".

> ttRepAdmin -dsn masterds -self -list
Self host "server1", port auto, name "masterds", LSN 0/2114272

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

Example 11-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 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 11-9 for a description of each field in the ttRepAdmin output.

Example 11-8 Using ttReplicationStatus from a program

You can use ttReplicationStatus to obtain the replication status of the subscriberds data store in relation to its master data store. From the master data store, enter:

> ttIsql masterds
Command> CALL ttReplicationStatus ('subscriberds');
< subscriberds, myhost, 0, start, 1, 152959, repscheme, repl>
1 row found.

See Example 11-9 for an explanation of the output fields.

Example 11-9 Using ttReplicationStatus from a SQLExecDirect function

You can also call ttReplicationStatus within a SQLExecDirect function to obtain the replication status of the subscriberds data store:

#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:

Example 11-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 data store 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 11-11 Verifying that a standby data store has received all updates

You can use the ttRepSubscriberSync built-in procedure to verify whether the standby data store in an active standby pair has received and applied all updates from the active data store. Call the procedure from the standby data store. 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);

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

Example 11-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 11-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 data stores.

Example 11-14 Output from SELECT queries

The output from the above queries for the data stores 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 11-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 data store waits for a response from another data store before resending a message. The last column is the log failure threshold value described in "Setting the log failure threshold".

See Example 11-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) data stores.

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