6 Troubleshooting Replication 

Check status of TimesTen daemon and replication agents

Use the ttStatus utility to confirm the main TimesTen daemon is running and the replication agents are started for all of your master and subscriber databases. The output from a simple replication scheme using a single master and subscriber database should look like that shown in Example 6-1.

If the TimesTen daemon is running, but the replication agents are not, the output looks like that shown in Example 6-2. In this case, start the replication agents as described in "Starting and stopping the replication agents" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

If neither the TimesTen daemon or replication agents are running, the output looks like that shown in Example 6-3. In this case, confirm you have correctly installed TimesTen and the Data Manager service is started, as described in "TimesTen Installation" in the Oracle TimesTen In-Memory Database Installation Guide.

C:\>ttStatus
TimesTen status report as of Thu Jan 25 16:23:33 2007
Daemon pid 5088 port 17000 instance MYINSTANCE
TimesTen server pid 4344 started on port 17002
TimesTen webserver pid 4216 started on port 17004
------------------------------------------------------------------------
Data store c:\temp\subscriber1ds
There are 12 connections to the data store
Data store is in shared mode
Shared Memory KEY Global\DBI45b9471c.2.SHM.2 HANDLE 0x280
Type            PID     Context     Connection Name              ConnID
Process         1244    0x00d08fb0  subscriber1ds                     1
Replication     4548    0x00aed2f8  LOGFORCE                          4
Replication     4548    0x00b03470  TRANSMITTER                       5
Replication     4548    0x00b725a8  RECEIVER                          6
Replication     4548    0x00b82808  REPHOLD                           2
Replication     4548    0x00b98980  REPLISTENER                       3
Subdaemon       2752    0x00526768  Worker                         2042
Subdaemon       2752    0x0072a758  Flusher                        2043
Subdaemon       2752    0x007308c0  Checkpoint                     2044
Subdaemon       2752    0x00736a28  HistGC                         2046
Subdaemon       2752    0x067f02f8  Aging                          2045
Subdaemon       2752    0x068364a0  Monitor                        2047
Replication policy  : Manual
Replication agent is running.
Cache agent policy : Manual
------------------------------------------------------------------------
Data store c:\temp\masterds
There are 12 connections to the data store
Data store is in shared mode
Shared Memory KEY Global\DBI45b945d0.0.SHM.6 HANDLE 0x2bc
Type            PID     Context     Connection Name              ConnID
Process         5880    0x00d09008  masterds                          1
Replication     3728    0x00aed570  LOGFORCE                          4
Replication     3728    0x00b036e8  TRANSMITTER                       5
Replication     3728    0x00b168b8  REPHOLD                           3
Replication     3728    0x00b1ca20  REPLISTENER                       2
Replication     3728    0x00b22b88  RECEIVER                          6
Subdaemon       3220    0x00526768  Worker                         2042
Subdaemon       3220    0x0072e768  Flusher                        2043
Subdaemon       3220    0x007348d0  Checkpoint                     2044
Subdaemon       3220    0x067b0068  Aging                          2045
Subdaemon       3220    0x067c0040  Monitor                        2047
Subdaemon       3220    0x068404c8  HistGC                         2046
Replication policy  : Manual
Replication agent is running.
Cache agent policy : Manual
------------------------------------------------------------------------
Data store c:\temp\demo
There are no connections to the data store
Replication policy : Manual
Cache agent policy : Manual
------------------------------------------------------------------------
End of report

> ttStatus
TimesTen status report as of Tue Oct 28 10:31:30 2006

Daemon pid 3396 port 15000 instance MYINSTANCE
TimesTen server pid 3436 started on port 15002
-----------------------------------------------------------------
Data store c:\temp\subscriberds
There are no connections to the data store
cache agent restart policy: manual
-----------------------------------------------------------------
Data store c:\temp\masterds
There are no connections to the data store
cache agent restart policy: manual
-----------------------------------------------------------------
End of report

> ttStatus
ttStatus: Could not connect to TimesTen daemon: Connection refused

Check that replication agents are communicating

Use ttRepAdmin -receiver -list to see that the replication agents are communicating with each other. If the masterds database is replicating to subscriberds, the output should look similar to the following:

> ttRepAdmin -receiver -list masterDSN
Peer name         Host name                 Port    State  Proto
----------------  ------------------------ ------  ------- -----
SUBSCRIBERDS      MYHOST                    Auto   Start       10

Last Msg Sent Last Msg Recv Latency TPS     RecordsPS Logs
------------- ------------- ------- ------- --------- ----
0:01:12       -               19.41       5        52    2

Check replication state

Use the ttReplicationStatus procedure to check state of the subscriber database with respect to its master. If the subscriber is in the Stop, Pause, or Failed state, use the ttReplicationStatus procedure to reset the subscriber state to Start, as described in "Setting the replication state of subscribers" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

> ttIsql masterDSN
Command> CALL ttReplicationStatus ('subscriberds');
< SUBSCRIBERDS, MYHOST, 0, pause, 1, 10, REPSCHEME, REPL >
1 row found.

Command> CALL ttRepSubscriberStateSet('REPSCHEME', 'REPL', 'SUBSCRIBERDS', 'MYHOST', 0)
Command> CALL ttReplicationStatus ('subscriberds');
< SUBSCRIBERDS, MYHOST, 0, start, 1, 152959, REPSCHEME, REPL >
1 row found.

Check replication scheme configuration

This section describes some procedures you can use to confirm the correct configuration of the various components in your replicated system. The basic procedure categories are:

• Check ttRepAdmin -showconfig

• Check the TTREP.TTSTORES table

• Check host names

> ttRepAdmin -showconfig masterDSN
Self host "MYHOST", port auto, name "MASTERDS", LSN 4/2970276, timeout 120, threshold 0
List of subscribers
-----------------
Peer name         Host name                 Port    State  Proto
----------------  ------------------------ ------  ------- -----
SUBSCRIBERDS      MYHOST                    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 : REPL.TAB
Master Name               Subscriber Name
-----------               -------------
MASTERDS                  SUBSCRIBERDS

SELECT * FROM ttrep.ttstores WHERE is_local_store <> 0x0;

Command> select * from ttrep.ttstores where is_local_store <> 0x0;
< -5193371075573733683, MYHOST, MASTERDS, 01, 0, 0, 4, 0 >
1 row found.

SELECT DISTINCT host_name FROM ttrep.ttstores;

Check owner names

As described in "Table requirements and restrictions for replication schemes" and "Owner of the replication scheme and tables" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide, the owner names of your replication scheme and your replicated tables must be consistent across all participating databases.

> ttIsql masterDSN
Command> select * from ttrep.replications;
< REPSCHEME            , REPL                 , C, 0, 0, -1 >
1 row found.

> ttIsql -connStr "dsn=subscriberDSN"
Command> select * from ttrep.replications;
< REPSCHEME            , REPL                 , C, 0, 0, -1 >
1 row found.

> ttIsql masterDSN
Command> select * from ttrep.replications;
< REPSCHEME            , SYSTEM1               , C, 0, 0, -1 >
1 row found.

> ttIsql -connStr "dsn=subscriberDSN"
Command> select * from ttrep.replications;
< REPSCHEME            , SYSTEM2               , C, 0, 0, -1 >
1 row found.

SYS.CACHE_GROUP

SYS.CACHE_GROUP

SYS.COLUMNS

SYS.COLUMNS

SYS.COL_STATS

SYS.COL_STATS

SYS.INDEXES

SYS.INDEXES

SYS.MONITOR

SYS.MONITOR

SYS.PLAN

SYS.PLAN

SYS.TABLES

SYS.TABLES

SYS.TBL_STATS

SYS.TBL_STATS

SYS.TRANSACTION_LOG_API

SYS.TRANSACTION_LOG_API

REPL.TAB

REPL.TAB

TTREP.REPELEMENTS

TTREP.REPELEMENTS

TTREP.REPLICATIONS

TTREP.REPLICATIONS

TTREP.REPPEERS

TTREP.REPPEERS

TTREP.REPSTORES

TTREP.REPSTORES

TTREP.REPSUBSCRIPTIONS

TTREP.REPSUBSCRIPTIONS

TTREP.REPTABLES

TTREP.REPTABLES

TTREP.TTSTORES

TTREP.TTSTORES

SYS.CACHE_GROUP

SYS.CACHE_GROUP

SYS.COLUMNS

SYS.COLUMNS

SYS.COL_STATS

SYS.COL_STATS

SYS.INDEXES

SYS.INDEXES

SYS.MONITOR

SYS.MONITOR

SYS.PLAN

SYS.PLAN

SYS.TABLES

SYS.TABLES

SYS.TBL_STATS

SYS.TBL_STATS

SYS.TRANSACTION_LOG_API

SYS.TRANSACTION_LOG_API

SYSTEM1.TAB

SYSTEM2.TAB

TTREP.REPELEMENTS

TTREP.REPELEMENTS

TTREP.REPLICATIONS

TTREP.REPLICATIONS

TTREP.REPPEERS

TTREP.REPPEERS

TTREP.REPSTORES

TTREP.REPSTORES

TTREP.REPSUBSCRIPTIONS

TTREP.REPSUBSCRIPTIONS

TTREP.REPTABLES

TTREP.REPTABLES

TTREP.TTSTORES

TTREP.TTSTORES

Check consistency between replicated tables

Replicated tables on both master and subscriber databases must be exactly the same.

11:37:58.00 Info: REP:  9430: REP1:transmitter.c(4936): TT16136: Sending 
definition for table TTUSER.MYDSN (1 column)
11:37:58.00 Info: REP:  9412: REP2:receiver.c(5928): TT16193: Adding definition
for table: TTUSER.MYDSN
11:37:58.00 Info: REP:  9412: REP2:meta.c(5580):TTUSER.MYDSN ptn 0: srcoff 0, 
destoff 0, length 8
11:37:58.00 Info: REP:  9412: REP2:meta.c(5580):TTUSER.MYDSN ptn 1: srcoff 8, 
destoff 12, length 12
11:37:58.00 Err : REP:  9412: REP2:receiver.c(6203): TT16198: Table definition 
mismatch on number of columns for table TTUSER.MYDSN.  Local definition: 2; 
transmitting peer: 1
11:37:58.00 Err : REP:  9412: REP2:receiver.c(6380): TT16204: Table TTUSER.MYDSN 
marked invalid. Will not apply transactions received for it until a valid 
definition is received
11:37:58.00 Err : REP:  9412: REP2:receiver.c(7200): TT16078: Table definition 
for ID 637068 is invalid (Original failure 11:37:58
REP2:receiver.c(6203): TT16198: Table definition mismatch on number of columns 
for table TTUSER.MYDSN.  Local definition: 2; transmitting peer: 1)
11:37:58.00 Err : REP:  9412: REP2:receiver.c(5002): TT16187: Transaction 
1173958671/2; Error: transient 0, permanent 1

Check replication state

Use the ttReplicationStatus procedure to check state of the subscriber database with respect to its master. If the subscriber is in the Failed state, see "Managing database failover and recovery" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for information on how to deal with failed databases.

> ttIsql masterDSN
Command> CALL ttReplicationStatus ('subscriberds');
< SUBSCRIBERDS, MYHOST, 0, failed, 1, 10, REPSCHEME, REPL >
1 row found.

Check return receipt timeout setting

Use the ttRepSyncGet procedure to check the return receipt timeout setting. A value of -1 indicates the application is to wait until it receives an acknowledgement from the subscriber. Network latency or other issues might delay receipt of the subscriber acknowledgment. You either address these issues or use the ttRepSyncGet procedure to reset the return receipt timeout period.

See "Checking the status of return service transactions" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for more information.

Check network bandwidth

Replication agents typically communicate over some type of network connection. If replicating over a network slower than 10 MB per second (such as common with a 100 Base-T Ethernet typical in a LAN), you must be careful to match the transaction load to the available bandwidth of the network. see "Network bandwidth requirements" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for details.

Check use of return receipt blocking

Unless you need receipt confirmation for all your transactions, disable RETURN RECEIPT BLOCKING. If you require receipt confirmation for some transactions, then set RETURN RECEIPT BY REQUEST and call the ttRepSyncSet procedure to enable the return receipt service for specific transactions. See "RETURN RECEIPT BY REQUEST" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for details.

Check replication configuration

In addition to return-receipt setting described above, other factors related to the configuration of your replication scheme could impact replication performance. As described in "Making decisions about performance and recovery trade-offs" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide, you often have to weigh the ability to efficiently failover and recover a database against replication performance.

For more information about direct replication, see "Direct replication or propagation" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

Check size of log buffer

Setting your log buffer too small may impact replication performance. Instead, Set the LogBufMB DSN attribute to a larger size. For more information on this DSN attribute, see "Setting connection attributes for logging" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide

Check durability settings

You can improve replication performance by setting TRANSMIT NONDURABLE on the replication ELEMENT to eliminate the flush-log-to-disk operation from the replication cycle. See "Setting transmit durability on data store elements" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for details.

Enabling the DURABLE COMMIT option in your replication scheme also impacts performance. See "DURABLE COMMIT" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for more information.

Check for reads from transaction log files

In some situations a "log reader," such as a master replication agent 'transmitter' thread or a ttXlaNextUpdate call in an XLA application, may not be able to keep up with the update rate of the applications writing to the database. Normally, replication and XLA readers get update records from the log buffer in memory. When the readers fall behind the application update rate, transaction log files can accumulate on the disk until the backlog can be cleared. This forces the readers to read transactions from the transaction log files on disk, which is much slower. Should you detect reads from the transaction log files, you may want to respond by decreasing the rate of application updates to that sustainable by the log readers.

Applications can monitor whether log readers are obtaining update records from transaction log files on disk rather than from the log buffer in memory by tracking the SYS.MONITOR table entry LOG_FS_READS. For example, you can check the value of LOG_FS_READS for the database, MASTERDSN, with the following ttIsql command:

% ttIsql -v1 -e "select log_fs_reads from monitor; quit;" -connStr dsn=MASTERDSN

If the LOG_FS_READS counter is increasing, the log readers are falling behind or clearing out a backlog in the transaction log files.

For more complete monitoring of replication progress, create a simple shell script like the following:

!/bin/sh
trap exit 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
DSN=$1

while [ 1 ] ; do
   date
   ttRepAdmin -receiver -list -connStr dsn=$DSN
   echo -n "Log reads from disk: "
   ttIsql -v1 -e "select log_fs_reads from monitor; quit;" -connStr dsn=$DSN
   echo
   ttRepAdmin -bookmark -connStr dsn=$DSN
sleep 15
done

$ monitorLog masterdsn

Mon Aug  2 10:44:40  2004
Peer name         Host name                 Port    State  Proto
----------------  ------------------------ ------  ------- -----
SUBSCRIBER1DSN    MYHOST                    Auto    Start   12

Last Msg Sent Last Msg Recv Latency TPS     RecordsPS Logs
------------- ------------- ------- ------- --------- ----
00:00:05      -               -1.00      -1        -1    1

Log reads from disk: < 0 >

Replication hold LSN ...... 10/2656136
Last written LSN .......... 10/4015824
Last LSN forced to disk ... 10/3970152

The output from:

ttRepAdmin -bookmark -connStr dsn=$DSN

displays the number of the transaction log files and the location of the bookmarks set by the log manager, as described in "From the command line: ttRepAdmin -bookmark" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide. The difference between the Replication hold LSN and the last written LSN indicates the number of records in the transaction log that have not yet been transmitted to the subscribers. A steady increase in the difference between these values is another indication that replication latency is increasing and log file reads are likely to occur.

Peer name         Host name                Port    State   Proto
----------------  ------------------------ ------  ------- -----
SUBSCRIBER1DSN    MYHOST                   Auto    Start   12

Last Msg Sent Last Msg Recv Latency TPS     RecordsPS Logs
------------- ------------- ------- ------- --------- ----
00:00:03      -               -1.00      -1        -1    4

Log reads from disk: <20>

Replication hold LSN ...... 14/7007464
Last written LSN .......... 17/465336
Last LSN forced to disk ... 17/456152

Problems using ttRepAdmin -duplicate

If you connected to your new subscriber DSN before running ttRepAdmin -duplicate, the database has already been created. In this situation, -duplicate returns:

Error : Restore not done : The datastore already exists.
Unable to restore datastore locally

Confirm the existence of the database by running ttStatus and checking to see if the database is in the returned list. If the new subscriber database exists, destroy it and try ttRepAdmin -duplicate again:

> ttDestroy /tmp/newstore
> ttRepAdmin -dsn newstoreDSN -duplicate -name newstore
-from masterds -host "server1"

If you have made an error entering the subscriber database name or host name in the replication scheme, you may see something like the following:

Unable to swap datastore locally
No receiver NEWSTORE on SERVER2 found to swap with

Returns 'Must specify -scheme' error

If you have more than one scheme specified in your TTREP.REPLICATIONS table, some ttRepAdmin commands may return the error:

Must specify -scheme to identify which replication scheme to use

To check the names of the replication schemes used by your database, use the ttIsql utility to connect, and enter:

Command> SELECT * from TTREP.REPLICATIONS;

> ttIsql -connStr "dsn=subDSN"
Command> SELECT * from TTREP.REPLICATIONS;
< REPSCHEME1      , REPL                , C, 0, 0, -1 >
< REPSCHEME2      , REPL                , C, 0, 0, -1 >
2 rows found.
Command> exit
> ttRepAdmin -dsn subDSN -receiver -list -scheme REPSCHEME1
Peer name         Host name                 Port    State  Proto
----------------  ------------------------ ------  ------- -----
SUBSCRIBER1        MYHOST                   Auto   Start      10

Last Msg Sent Last Msg Recv Latency TPS     RecordsPS Logs
------------- ------------- ------- ------- --------- ----
0:01:12       -               19.41       5        52    2

Column cannot be used for replication timestamp

When attempting to set CHECK CONFLICTS for an element in a CREATE REPLICATION statement, you may encounter an error similar to the following:

8004: Column REPL.TABS.TS cannot be used for replication timestamp checking if 
in an index or added by ALTER TABLE; and must be binary(8) with NULL values allowed.

In this situation, check:

• That the timestamp column in the specified table is a nullable column of type BINARY(8). In the above example, the TS column in the REPL.TAB table should have a type of BINARY(8).

• The timestamp column is defined in the original CREATE TABLE statement, rather than added later using ALTER TABLE.

Timestamp does not exist

You may receive an error similar to the following:

2208: Column TS does not exist in table.

In this situation, confirm that you have specified the correct name for the timestamp column in the CHECK CONFLICTS clause and that it exists in the specified table.

Also, make sure the timestamp column is not part of a primary key or index.

Conflict reporting slows down replication

If you have configured replication to check conflicts, TimesTen sends reports to the local host. You can also configure a report file. See "Diagnostics through SNMP Traps" in the Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.

If there is a large number of conflicts in a short period of time, subscriber performance can slow down because of the reporting requirements. You can use store attributes in the CREATE REPLICATION or ALTER REPLICATION statements to suspend and resume conflict reporting at specified rates of conflict:

• CONFLICT REPORTING SUSPEND AT rate

• CONFLICT REPORTING RESUME AT rate

Information about conflicts that occur while reporting is suspended cannot be retrieved.

See "Reporting conflicts" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.