11 Setting Up a Replicated System

Identifying database hosts and network interfaces using the ROUTE clause

When specifying the host for a database in a replication element, you should always use the name returned by the hostname command, as replication uses the this same host name to verify that the current host is involved in the replication scheme. Replication schemes may not be created that do not include the current host.

If a host contains multiple network interfaces (with different IP addresses), you should specify which interfaces are to be used by replication using the ROUTE clause. You must specify a priority for each interface. Replication tries to first connect using the address with the highest priority, and if a connection cannot be established, it tries the remaining addresses in order of priority until a connection is established. If a connection to a host fails while using one IP address, replication attempts to re-connect (or fall back) to another IP address, if more than one address has been specified in the ROUTE clause.

See "Configuring network operations" for more information.

Identifying database hosts on UNIX without using the ROUTE clause

If a replication configuration is specified using host names rather than IP addresses, replication must be able to translate host names of peers into IP addresses. For this to happen efficiently on Windows, make sure each Windows machine is set up to query either a valid WINS server or a valid DNS server that has correct information about the hosts on the network. In the absence of such servers, static HOST-to-IP entries can be entered in either:

When possible, you should use the ROUTE clause of a replication scheme to identify database hosts and the network interfaces to use for replication. However, if you have a legacy replication configuration that does not use the ROUTE clause, this section explains how to configure operating system and DNS files for a replication host with multiple network interfaces.

If a host contains multiple network interfaces (with different IP addresses) and replication is not configured with a ROUTE clause, TimesTen replication tries to connect to the IP addresses in the same order as returned by the gethostbyname call. It will try to connect using the first address; if a connection cannot be established, it tries the remaining addresses in order until a connection is established. TimesTen replication uses this same sequence each time it establishes a new connection to a host. If a connection to a host fails on one IP address, TimesTen replication attempts to re-connect (or fall back) to another IP address for the host in the same manner described above.

There are two basic ways you can configure a host to use multiple IP addresses on UNIX platforms: DNS or the /etc/hosts file.

For example, if your machine has two NICs, use the following syntax for your /etc/hosts file:

127.0.0.1  localhost
IP_address_for_NIC_1  official_hostname optional_alias
IP_address_for_NIC_2  official_hostname optional_alias

The host name official_hostname is the name returned by the hostname command.

When editing the /etc/hosts file, keep in mind that:

• You must log in as root to change the /etc/hosts file.

• There should only be one line per IP address.

• There can be multiple alias names on each line.

• When there are multiple IP addresses for the same host name, they must be on consecutive lines.

• The host name can be up to 30 characters long.

For example, the following entry in the /etc/hosts file on a UNIX platform describes a server named Machine1 with two IP addresses:

127.0.0.1        localhost
10.10.98.102     Machine1
192.168.1.102    Machine1

To specify the same configuration for DNS, your entry in the domain zone file would look like:

Machine1     IN     A     10.10.98.102
             IN     A     192.168.1.102

In either case, you only need to specify Machine1 as the host name in your replication scheme and replication will use the first available IP address when establishing a connection.

In an environment in which multiple IP addresses are used, you can also assign multiple host names to a single IP address in order to restrict a replication connection to a specific IP address. For example, you might have an entry in your /etc/hosts file that looks like:

127.0.0.1        localhost
10.10.98.102     Machine1
192.168.1.102    Machine1 RepMachine1

or a DNS zone file that looks like:

Machine1     IN     A     10.10.98.102
             IN     A     192.168.1.102
RepMachine1  IN     A     192.168.1.102

If you want to restrict replication connections to IP address 192.168.1.102 for this host, you can specify RepMachine1 as the host name in your replication scheme. Another option is to simply specify the IP address as the host name in the CREATE REPLICATION statement used to configure your replication scheme.

Host name resolution on Windows

If a replication configuration is specified using host names rather than IP addresses, replication must be able to translate host names of peers into IP addresses. For this to happen efficiently on Windows, make sure each Windows machine is set up to query either a valid WINS server or a valid DNS server that has correct information about the hosts on the network. In the absence of such servers, static host-to-IP entries can be entered in either:

%windir%\system32\drivers\etc\hosts

or

%windir%\system32\drivers\etc\lmhosts

Without any of these options, a Windows machine resorts to broadcasting, which is extremely slow, to detect peer nodes.

You may also encounter extremely slow host name resolution if the Windows machine cannot communicate with the defined WINS servers or DNS servers, or if the host name resolution set up is incorrect on those servers. Use the ping command to test whether a host can be efficiently located. The ping command responds immediately if host name resolution is set up properly.

User-specified addresses for TimesTen daemons and subdaemons

By default, the TimesTen main daemon, all subdaemons and all agents use any available address to listen on a socket for requests. You can modify the ttendaemon.options file to specify an address for communication among the agents and daemons by including a -listenaddr option. See "Managing TimesTen daemon options" in Oracle TimesTen In-Memory Database Operations Guide for details.

Suppose that your machine has two NICs whose addresses are 10.10.10.100 and 10.10.11.200. The loopback address is 127.0.0.1. Then keep in mind the following as it applies to the replication agent:

• If you do not set the -listenaddr option in the ttendaemon.options file, then any process can talk to the daemons and agents.

• If you set -listenaddr to 10.10.10.100, then any process on the local host or the 10.10.10 net can talk to daemons and agents on 10.10.10.100. No processes on the 10.10.11 net can talk to the daemons and agents on 10.10.10.100.

• If you set -listenaddr to 127.0.0.1, then only processes on the local host can talk to the daemons and agents. No processes on other hosts can talk the daemons and agents.

Connection attributes for replicated databases

Databases that replicate to each other must have the same DatabaseCharacterSet data store attribute. TimesTen does not perform any character set conversion between replicated databases.

If you wish to configure parallel replication for replication schemes that are not active standby pairs, see "Increasing replication throughput for other replication schemes" for information about setting the ReplicationParallelism and ReplicationApplyOrdering data store attributes.

See "Setting connection attributes for logging" for recommendations for managing the replication log files.

It is possible to replicate between databases with different settings for the TypeMode data store attribute. However, you must make sure that the underlying data type for each replicated column is the same on each node. See "TypeMode" in Oracle TimesTen In-Memory Database Reference for more information.

Table requirements and restrictions for replication schemes

Tables to be replicated in any type of replication scheme must have the following characteristics:

• The name, owner, and column definitions of the tables participating in the replication scheme must be identical on both the master and subscriber databases unless you specify a TABLE DEFINITION CHECKING value of RELAXED in the CREATE REPLICATION statement. If you specify RELAXED, then the tables must have the same key definition, number of columns and column data types. See "Setting STORE attributes".

• Tables to be replicated must have one of the following:

  • A primary key

  • A unique index over non-nullable columns

  Replication uses the primary key or unique index to uniquely identify each row in the replicated table. Replication always selects the first usable index that turns up in a sequential check of the table's index array. If there is no primary key, replication selects the first unique index without NULL columns it encounters. The selected index on the replicated table in the master database must also exist on its counterpart table in the subscriber.

  Note:

  The keys on replicated tables are transported in each update record to the subscribers. Smaller keys transport most efficiently.

• VARCHAR2, NVARCHAR2, VARBINARY and TT_VARCHAR columns in replicated tables is limited to a size of 4 megabytes. For a VARCHAR2 column, the maximum length when using character length semantics depends on the number of bytes each character occupies when using a particular database character set. For example, if the character set requires four bytes for each character, the maximum possible length is 64,000 characters. For an NVARCHAR2 column, which requires two bytes for each character, the maximum length when using character length semantics is 128,000 characters.

If these requirements and restrictions present difficulties, you may want to consider using the Transaction Log API (XLA) as a replication mechanism. See "Using XLA as a replication mechanism" in Oracle TimesTen In-Memory Database C Developer's Guide.

Tasks to perform on the source host

Using a text editor, create a new SQL file, named newrepscheme.sql, that defines the replication scheme and calls the ttRepStart procedure to start replication:

CREATE REPLICATION repscheme
  ELEMENT e TABLE tab
  MASTER masterds ON "server1"
  SUBSCRIBER newdatabase ON "server2";

call ttRepStart;

From the command line, configure masterds with the replication scheme and start the replication agent:

> ttIsql -f newrepscheme.sql masterds

Tasks to perform on the target host

From the command line, copy the contents of the masterds database into the newdatabase database:

> ttRepAdmin -dsn newdatabase -duplicate -from masterds -host "server1" -uid ttuser

You will be prompted for the password of ttuser.

The newdatabase database should now have the same contents as the masterds database.

You can also do a duplication operation similar to that shown above from a C program by using the ttRepStart procedure and ttRepDuplicateEx C function. See "Starting and stopping the replication agents" and "Recovering a failed database" for more information.

About log buffer size and persistence

A common misconception among TimesTen users is that there is a relationship between the size of the log buffer and lost transactions. The size of the log buffer has no impact on persistence.

If your DSN is configured with DurableCommits=0, then transactions are written durably to disk only under the following circumstances:

• When the log buffer fills up.

• When the ttDurableCommit procedure is called or when a transaction on a connection with DurableCommits=1 is committed or rolled back.

• When the replication agent sends a batch of transactions to a subscriber and the master has been configured for replication with the TRANSMIT DURABLE attribute (the default). (See "Default replication".)

• When the replication agent periodically executes a durable commit, whether the primary database is configured with TRANSMIT DURABLE or not.

• When your DSN is configured with LogFlushMethod=2, writes are written to disk before control is returned to the application.

The size of the log buffer has no influence on the ability of TimesTen to write data to disk under any of the circumstances listed above.

About transaction log growth on a master database

In databases that do not use replication, Transaction Log API (XLA), cache groups, or incremental backup, unneeded records in the log buffer and unneeded transaction log files are purged each time a checkpoint is initiated, either by the automatic background checkpointing thread or by an application's call to the ttCkpt or ttCkptBlocking procedures. With a replicated database, transactions remain in the log buffer and transaction log files until the master replication agent confirms they have been fully processed by the subscriber, as described in "Replication agents". Only then can the master consider purging them from the log buffer and transaction log files.

A master database transaction log can grow much larger than it would on an unreplicated database if there are changes to its subscriber state. See "Setting the replication state of subscribers" for information on the subscriber states. When the subscriber is in the Start state, the master can purge logged data after it receives confirmation it has been received by the subscriber. However, if a subscriber becomes unavailable or set to the Pause state, the log on the master database cannot be flushed and the space used for logging can be exhausted. When the log space is exhausted, subsequent updates on the master database are aborted.

Setting connection attributes for logging

LogBufMB specifies the maximum size of the in-memory log buffer in megabytes. This buffer is flushed to a transaction log file on the disk when it becomes full. The minimum size for LogBufMB is 8 times the value of LogBufParallelism.

You need to establish enough disk space for the replication log files. There are two settings that control the amount of disk space used by the log:

• The LogFileSize setting in the DSN specifies the maximum size of a transaction log file. If logging requirements exceed this value, additional transaction log files with the same maximum size are created. If you set the LogFileSize to a smaller value than LogBufMB, TimesTen automatically increases the LogFileSize to match LogBufMB. For best performance, set LogBufMB and LogFileSize to their maximum values.

• The log failure threshold setting specifies the maximum number of transaction log files allowed to accumulate before the master assumes a subscriber has failed. The threshold value is the number of transaction log files between the most recently written to transaction log file and the earliest transaction log file being held for the subscriber. For example, if the last record successfully received by all subscribers was in Log File 1 and the last log record written to disk is at the beginning of Log File 4, then replication is at least 2 transaction log files behind (the contents of Log Files 2 and 3). If the threshold value is 2, then the master sets the subscriber to the Failed state after detecting the threshold value had been exceeded. This may take up to 10 seconds. See "Setting the log failure threshold" for more information.

Because transactions are logged to disk, you can use bookmarks to detect the log record identifiers of the update records that have been replicated to subscribers and those that have been written to disk. To view the location of the bookmarks for the subscribers associated with masterDSN, use the C utility or ttBookmark built-in procedure, as described in "Show replicated log records".

If a subscriber goes down and then comes back up before the threshold is reached, then replication automatically "catches up" as the committed transactions in the transaction log files following the bookmark are automatically transmitted. However, if the threshold is exceeded, the master sets the subscriber to the Failed state. A failed subscriber must use ttRepAdmin -duplicate to copy the master database and start over, as described in Chapter 12, "Managing Database Failover and Recovery".

See Oracle TimesTen In-Memory Database Reference for more information about TimesTen connection attributes, built-in procedures and utilities.

Configuring user-specified parallel replication

Enable parallel replication by setting connection attributes at database creation time as follows:

• Set ReplicationParallelism to a number from 2 to 64. This number indicates the number of transmitter threads on the source database and the number of receiver threads on the target database. The default is 1, which indicates a single thread.

• Set ReplicationApplyOrdering to 1.

See "ReplicationParallelism" and "ReplicationApplyOrdering" in Oracle TimesTen In-Memory Database Reference.

In addition, the application needs to assign transactions to tracks by one of four methods:

• Set the ReplicationTrack general connection attribute to a nonzero number that is less than or equal to the maximum number of tracks specified by the ReplicationParallelism data store attribute.

• Use the ALTER SESSION SQL statement to set the replication track number for the current connection.

• Use the TT_REPLICATION_TRACK ODBC connection option for the SQLSetConnectOption ODBC function.

• Use the setReplicationTrack() method of the TimesTenConnection JDBC class.

For more information about these methods of setting the ReplicationTrack general connection attribute, see:

• "ReplicationTrack" in Oracle TimesTen In-Memory Database Reference

• "ALTER SESSION" in Oracle TimesTen In-Memory Database SQL Reference

• "Setting up user-specified parallel replication" in Oracle TimesTen In-Memory Database C Developer's Guide

• "Setting up user-specified parallel replication" in Oracle TimesTen In-Memory Database Java Developer's Guide

Use the ttConfiguration built-in procedure to return the replication track number for the current connection. Use the ttLogHolds built-in procedure to verify that multiple tracks are being used.

Restrictions on user-specified parallel replication

• Active standby pairs cannot be configured for user-specified parallel replication.

• Do not configure user-specified parallel replication for tables that have an aging policy defined.

• Databases configured for user-specified parallel replication cannot contain cache groups.

• A database cannot be defined as a propagator when user-specified parallel replication is configured.

• User-specified parallel replication is not supported for synchronous replication, including databases with the RETURN RECEIPT and RETURN TWOSAFE attributes.

• ReplicationParallelism and ReplicationApplyOrdering are data store attributes and cannot be changed after database creation.

• The ALTER TABLE SQL statement with the ADD and DROP clauses is not supported for databases configured for user-specified parallel replication.

• Cross-release replication and migration from a database that does not have user-specified parallel replication enabled to a database that does have parallel replication enabled is not supported from release 11.2.1.6.0 and later. It is supported from releases earlier than 11.2.1.6.0.