Skip Headers
Oracle® TimesTen In-Memory Database TimesTen to TimesTen Replication Guide
Release 11.2.1

Part Number E13072-14
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

11 Setting Up a Replicated System

This chapter describes how to set up and start replication. The typical tasks related to setting up and starting a replicated system are:

Task See...
Configure the network "Configuring the network".
Establish databases and set up environment "Setting up the replication environment".
Define a replication scheme Chapter 10, "Defining Replication Schemes"
Apply replication scheme to the databases See "Applying a replication scheme to a database".
Start and stop the replication agent for each database See "Starting and stopping the replication agents".
Set the replication state of subscribers See "Setting the replication state of subscribers".

Note:

To set up an active standby pair, see:

This chapter includes the following topics:

Configuring the network

This section describes some of the issues to be considered when replicating TimesTen data over a network. The general topics are:

Network bandwidth requirements

The network bandwidth required for TimesTen replication depends on the bulk and frequency of the data being replicated. This discussion explores the types of transactions that characterize the high and low ends of the data range and the network bandwidth required to replicate the data between TimesTen databases.

The high end of the data range can be characterized by updates or inserts of small amounts of data, such as inserting 128 bytes into a row, which can result in approximately 1.5 to 1.6 MB per second of replicated data. The lower end might be characterized by a single CHAR(10) column update running with return receipt, which can result in approximately 125 KB per second of replicated data.

The following table provides guidelines for calculating the size of replicated records.

Record Type Size
Begin transaction 48 bytes
Update 116 bytes

+ 18 bytes per column updated

+ size of old column values

+ size of new column values

+ size of the primary key or unique key

Delete 104 bytes

+ size of the primary key or unique key

Insert 104 bytes

+ size of the primary key or unique key

+ size of inserted row


Transactions are sent between replicated databases in batches. A batch is created whenever there is no more data in the transaction log buffer in the master database, or when the current batch is roughly 256K bytes. See "Copying updates between databases" for more information.

As shown in the table below, the 100 Base-T Ethernet typical in a LAN can sustain speeds of around 10 MB per second, which is more than enough sustained bandwidth for the most demanding replication rates. However, if servers are communicating in a WAN, the configuration of the replication scheme and transaction load must be carefully matched to the available bandwidth of the network.

Network Area Network Sustained Speed
LAN 100 Base-T Ethernet 10 MB per second
WAN T3 4.8 MB per second
WAN T2 780 KB per second
WAN T1 197 KB per second

As shown in the above table, with an available bandwidth of 4.8 MB per second, a T3 line should provide sufficient bandwidth to support 2 subscribers operating at the fastest possible transaction rates (totaling 3.2 MB/s) without loss of performance.

In contrast, a T1 line should provide sufficient bandwidth to accommodate return receipt replication for users inserting less than 1 KB into rows.

Replication in a WAN environment

TimesTen replication uses the TCP/IP protocol, which is not optimized for a WAN environment. You can improve replication performance over a WAN by installing a third-party "TCP stack" product. If replacing the TCP stack is not a feasible solution, you can reduce the amount of network traffic that the TCP/IP protocol has to deal with by setting the COMPRESS TRAFFIC attribute in the CREATE REPLICATION statement. See "Compressing replicated traffic" for details.

See installation information for your platform in Oracle TimesTen In-Memory Database Installation Guide for information about changing TCP/IP kernel parameters for better performance.

Configuring host IP addresses

In a replication scheme, you need to identify the name of the host machine on which your database resides. The operating system translates this host name to one or more IP addresses. This section describes how to configure replication so that it uses the correct host names and IP addresses each host machine.

This section includes these topics:

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.

Note:

Addresses for the ROUTE clause may be specified as either host names or IP addresses. However, if your host has more than one IP address configured for a given host name, you should only configure the ROUTE clause using the IP addresses, in order to ensure that replication uses only the IP addresses that you intend.

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.

Note:

If you have multiple network interface cards (NICs), be sure that "multi on" is specified in the /etc/host.conf file. Otherwise, gethostbyname will not return multiple addresses.

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.

Note:

You must be consistent in identifying a database host in a replication scheme. Do not identify a host using its IP address for one database and then use its host name for the same or another database.

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.

Identifying the local host of a replicated database

Ordinarily, TimesTen replication is able to identify the hosts involved in a replication configuration using normal operating system host name resolution methods. However, in some rare instances, if the host has an unusual host name configuration, TimesTen is unable to determine that the local host matches the host name as specified in the replication scheme. When this occurs, you receive error 8191, "This store is not involved in a replication scheme," when attempting to start replication using ttRepStart or ttAdmin -repStart. The ttHostNameSet built-in procedure may be used in this instance to explicitly indicate to TimesTen that the current database is in fact the database specified in the replication scheme. See "ttHostNameSet" in Oracle TimesTen In-Memory Database Reference for more information.

Setting up the replication environment

The topics related to setting up your replication environment include:

Establishing the databases

You can replicate one or more tables on any existing database. If the database you want to replicate does not yet exist, you must first create one, as described in "Managing TimesTen Databases" in Oracle TimesTen In-Memory Database Operations Guide.

After you have identified or created the master database, create a DSN definition for the subscriber database on the receiving machine. Set the connection attributes for the master and subscriber databases as described in "Connection attributes for replicated databases".

After you have defined the DSN for your subscriber, you can populate the subscriber database with the tables to be replicated from the master in one of two ways:

  • Connect to the database and use SQL statements to create new tables in the subscriber database that match those to be replicated from the master.

  • Use the ttRepAdmin -duplicate utility to copy the entire contents of the master database to the subscriber. See "Copying a master database to a subscriber".

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.

Copying a master database to a subscriber

A short method for populating a subscriber database that will fully replicate its master database is to copy the contents of the master. Copying a database in this manner is also essential when recovering a failed database, as described in Chapter 12, "Managing Database Failover and Recovery".

You can use either the ttRepAdmin -duplicate utility or the ttRepDuplicateEx C function to duplicate a database. See "Duplicating a database".

Before copying the contents of a master database to populate a subscriber database, you must:

  1. Create a DSN for the new subscriber database.

  2. Create or alter a replication scheme to include the new subscriber database and its host. See "Defining a replication scheme".

  3. Apply the replication scheme to the master database. See "Applying a replication scheme to a database".

  4. Start the replication agent for the master database. See "Starting and stopping the replication agents".

For example, on host server1, there is a DSN named masterDSN that describes the masterds database. On host server2, there is a DSN named newdatabaseDSN that describes the newdatabase database. The ttuser user on masterDSN has the ADMIN privilege.

To populate the newdatabase database with the contents of masterds, perform the tasks described in "Tasks to perform on the source host" and "Tasks to perform on the target host".

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.

Note:

The -host can be identified with either the name of the remote host or its TCP/IP address. If you identify hosts using TCP/IP addresses, you must identify the address of the local host (server2 in this example) by using the -localhost option. For details, see "ttRepAdmin" in Oracle TimesTen In-Memory Database Reference.

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.

Managing the transaction log on a replicated database

This section includes these topics:

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.

Increasing replication throughput for active standby pairs

Use the ReceiverThreads first connection attribute to increase the number of threads that apply changes from the active database to the standby database from 1 to 2. If you set ReceiverThreads to 2 on the standby, you should also set it to 2 on the active to maintain increased throughput if there is a failover.

You can also set ReceiverThreads to 2 on one or more read-only subscribers in an active standby pair to increase replication throughput from the standby database.

Databases must be hosted on systems that are 2-way or larger to take advantage of setting this attribute to 2.

Increasing replication throughput for other replication schemes

If your application has predictable transactional dependencies and does not require the commit order on the target database be the same as the order on the source database, then you can increase replication throughput by configuring parallel replication.

User-specified parallel replication configures multiple threads for sending updates from the source database to the target database and for applying the updates on the target database. The application assigns transactions to tracks. The application specifies which track a transaction belongs to when the transaction starts on the source database. The transactions in each track are applied in the order in which they are received on the target database, but commit order is not maintained for transactions across the different tracks.

Use caution in assigning tracks to transactions that affect tables with foreign key relationships. If transactions on related tables are assigned to different tracks, one of the transactions can be lost because the transactions may be applied out of commit order.

In general, transactions that modify the same table should be assigned to the same replication track. However, if all transactions are inserts to a particular table, they can be assigned to different tracks to increase replication throughput.

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:

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.

Configuring a large number of subscribers

A replication scheme can include up to 128 subscribers. A replication scheme with propagator databases can have up to 128 propagators, and each propagator can have up to 128 subscribers. An active standby pair replication scheme can include up to 127 read-only subscribers. If you are planning a replication scheme that includes a large number of subscribers, then ensure the following:

  • The log buffer size should result in the value of LOG_FS_READS in the SYS.MONITOR table being 0 or close to 0. This ensures that the replication agent does not have to read any log records from disk. If the value of LOG_FS_READS is increasing, then increase the log buffer size.

  • CPU resources are adequate. The replication agent on the master database spawns a thread for every subscriber database. Each thread reads and processes the log independently and needs adequate CPU resources to make progress.

Replicating databases across releases

Replication functions across releases only if the database of the more recent version of TimesTen was upgraded using ttMigrate from a database of the older version of TimesTen. A database created in the more recent version of TimesTen is not guaranteed to replicate correctly with the older version.

For example, replication between a database created in TimesTen release 5.1 and a database created in TimesTen release 6.0 is not supported. However, if one database was created in TimesTen release 5.1, and the peer database was created in TimesTen release 5.1 and then upgraded to TimesTen release 6.0, replication between them is supported.

See "Database Upgrades" in Oracle TimesTen In-Memory Database Installation Guide.

Applying a replication scheme to a database

Define your replication scheme as described in Chapter 10, "Defining Replication Schemes". Save the CREATE REPLICATION statement in a SQL file.

After you have described your replication scheme in a SQL file, you can execute the SQL on the database using the -f option to the ttIsql utility. The syntax is:

ttIsql -f schemefile.sql - connstr "dsn=DSN"

Example 11-1 Creating a replication scheme by executing a SQL file

If your replication scheme is described in a file called repscheme.sql, you can execute the file on a DSN, called masterDSN, by entering:

> ttIsql -f repscheme.sql -connstr "dsn=masterDSN"

Under most circumstances, you should apply the same scheme to all of your replicated databases. You must invoke a separate ttIsql command on each host to apply the replication scheme.

Example 11-2 Executing a SQL file on each host

If your scheme includes the databases masterDSN on host S1, subscriber1DSN on host S2, and subscriber2DSN on host S3, do the following:

On host S1, enter:

> ttIsql -f repscheme.sql masterDSN

On host S2, enter:

> ttIsql -f repscheme.sql subscriber1DSN

On host S3, enter:

> ttIsql -f repscheme.sql subscriber2DSN

You can also execute the SQL file containing your replication scheme from the ttIsql command line. For example:

Command> run repscheme.sql;

Starting and stopping the replication agents

After you have defined a replication scheme, you can start the replication agents for each database involved in the replication scheme. You must have the ADMIN privilege to start or stop a replication agent.

You can start and stop replication agents from either the command line or from your program, as described in the sections:

Note:

If a database does not participate in a replication scheme, attempts to start a replication agent for that database fail.

Controlling replication agents from the command line

To start and stop a replication agent from the command line, use the ttAdmin utility with the -repStart or -repStop option:

ttAdmin -repStart DSN
ttAdmin -repStop DSN

Note:

Replication DDL that is not permitted when the replication agent is running may be possible during the brief period of time between issuing ttAdmin -repStart command and the actual start of the replication agent. For example, it may be possible to drop a replication scheme during this time.

Example 11-3 Starting and stopping the replication agent with ttAdmin

To start the replication agents for the DSNs named masterDSN and subscriberDSN, enter:

ttAdmin -repStart masterDSN
ttAdmin -repStart subscriberDSN

To stop the replication agents, enter:

ttAdmin -repStop masterDSN
ttAdmin -repStop subscriberDSN

You can also use the ttRepStart and ttRepStop procedures to start and stop a replication agent from the ttIsql command line.

Example 11-4 Starting and stopping the replication agent from the ttIsql command line

To start and stop the replication agent for the DSN named masterDSN, enter:

> ttIsql masterDSN
Command> call ttRepStart;
Command> call ttRepStop;

You can also use the ttAdmin utility to set the replication restart policy. By default the policy is manual, which enables you to start and stop the replication agents as described above. Alternatively, you can set the replication restart policy for a database to always or norestart.

Restart Policy Start replication agent when the TimesTen daemon starts Restart replication agent on errors or invalidation
always Yes Yes
manual No Yes
norestart No No

Note:

The replication agents are managed by the TimesTen daemon, which must be started before starting any replication agents.

When the restart policy is always, the replication agent is automatically started when the database is loaded into memory. See "Specifying a RAM policy" in Oracle TimesTen In-Memory Database Operations Guide to determine when a database is loaded into memory.

Example 11-5 Using ttAdmin to set the restart policy

To use ttAdmin to set the replication restart policy to always, enter:

ttAdmin -repPolicy always DSN

To reset the policy back to manual, enter:

ttAdmin -repPolicy manual DSN

Following an error or database invalidation, both manual and always policies cause the replication agent to be automatically restarted. When the agent restarts automatically, it is often the first connection to the database. This happens after a fatal error that, for example, requires all applications to disconnect. The first connection to a database usually has to load the most recent checkpoint file and often needs to do recovery. For a very large database, this process may take several minutes. During this period, all activity on the database is blocked so that new connections cannot take place and any old connections cannot finish disconnecting. This may also result in two copies of the database existing at the same time because the old one stays around until all applications have disconnected. For very large databases for which the first-connect time may be significant, you may want to wait for the old database to become inactive first before starting up the new one. You can do this by setting the restart policy to norestart to specify that the replication agent is not to be automatically restarted. For more information on setting policies that would prevent the database from being reloaded, see "Specifying a RAM policy" in Oracle TimesTen In-Memory Database Operations Guide to determine when a database is loaded into memory.

Controlling replication agents from a program

To start and stop the replication agent for a database from your program, connect to the replicated database and use the ttRepStart and ttRepStop procedures.

Example 11-6 Starting and stopping the replication agent from a program

To start and stop the replication agent for the database that is identified by the hdbc connection handle:

rc = SQLAllocStmt( hdbc, &hstmt );
rc = SQLExecDirect( hstmt, (SQLCHAR *)
    "CALL ttRepStart()", SQL_NTS );
rc = SQLExecDirect( hstmt, (SQLCHAR *)
    "CALL ttRepStop()", SQL_NTS );

Example 11-7 Setting the restart policy from a program

To set the replication policy to always for the database identified by the hdbc connection handle:

rc = SQLAllocStmt( hdbc, &hstmt );
rc = SQLExecDirect( hstmt, (SQLCHAR *)
     "CALL ttRepPolicy ('always')", SQL_NTS );

Setting the replication state of subscribers

The state of a subscriber replication agent is described by its master database. When recovering a failed subscriber database, you must reset the replication state of the subscriber database with respect to the master database it communicates with in a replication scheme. You can reset the state of a subscriber database from either the command line or your program:

See "Monitoring Replication" for information about querying the state of a database.

A master database can set a subscriber database to either the Start, Pause, or Stop states. The database state appears as an integer value in the STATE column in the TTREP.REPPEERS table, as shown in the following table.

State Description
Start

STATE value: 0

Replication updates are collected and transmitted to the subscriber database as soon as possible. If replication for the subscriber database is not operational, the updates are saved in the transaction log files until they can be sent.
Pause

STATE value: 1

Replication updates are retained in the log with no attempt to transmit them. Transmission begins when the state is changed to Start.
Stop

STATE value: 2

Replication updates are discarded without being sent to the subscriber database. Placing a subscriber database in the Stop state discards any pending updates from the master's transaction log.
Failed

STATE value: 4

Replication to a subscriber is considered failed because the threshold limit (log data) has been exceeded. This state is set by the system is a transitional state before the system sets the state to Stop.Applications that connect to a Failed database receive a warning. See "General failover and recovery procedures" for more information.

When a master database sets one of its subscribers to the Start state, updates for the subscriber are retained in the master's log. When a subscriber is in the Stop state, updates intended for it are discarded.

When a subscriber is in the Pause state, updates for it are retained in the master's log, but are not transmitted to the subscriber database. When a master transitions a subscriber from Pause to Start, the backlog of updates stored in the master's log is transmitted to the subscriber. (There is an exception to this, which is described in Chapter 12, "Managing Database Failover and Recovery".) If a master database is unable to establish a connection to a stated subscriber, the master periodically attempts to establish a connection until successful.

Example 11-8 Using ttRepAdmin to set the subscriber state

To use ttRepAdmin from the command line to direct the masterds master database to set the state of the subscriberds subscriber database to Stop:

ttRepAdmin -dsn masterds -receiver -name subscriberds -state stop

Note:

If you have multiple subscribers with the same name on different hosts, use the -host option of the ttRepAdmin utility to identify the host for the subscriber.

Example 11-9 Setting the subscriber state to Stop from a program

Assuming the replication scheme is named scheme, the following ttRepSubscriberStateSet procedure directs the master database to set the state of the subscriber database (subscriberds ON system1) to Stop:

rc = SQLAllocStmt( hdbc, &hstmt );
rc = SQLExecDirect( hstmt, (SQLCHAR *)
    "CALL ttRepSubscriberStateSet('repscheme', 'repl',
          'subscriberds', 'system1', 2)", SQL_NTS );

Example 11-10 Setting the subscriber state to Pause from a program

The following ttRepSubscriberStateSet procedure directs the master database to set the state of all of its subscriber databases to Pause:

rc = SQLAllocStmt( hdbc, &hstmt );
rc = SQLExecDirect( hstmt, (SQLCHAR *)
    "CALL ttRepSubscriberStateSet( , , , , 1 )", SQL_NTS );

Only ttRepSubscriberStateSet can be used to set all of the subscribers of a master to a particular state.The ttRepAdmin utility does not have any equivalent functionality.