3 Defining an Active Standby Pair Replication Scheme

Restrictions on active standby pairs

When you are planning an active standby pair, keep in mind the following:

• For the initial setup, you create the standby database by duplicating the active database with the ttRepAdmin -duplicate utility or the ttRepDuplicateEx C function.

• To ensure high availability, each master and subscriber database must be on a different machine.

• To avoid performance issues, especially in intensive operations such as a master database catchup, standby master recovery, or processing a return service, we strongly recommend that the network has a latency of less than 100 milliseconds between the hosts on which the master databases are installed.

  Note:

  When using return services, the active master waits for a response from the standby master. If the bandwidth causes the latency response time to exceed the defined timeout, the performance of the transaction is negatively affected. For more information on the timeout period for return services, see "Setting the return service timeout period".

• The clock skew between the active node and the standby node cannot exceed 250 milliseconds. When adjusting the system clocks on any nodes to be synchronized with each other, do not set any clock backward in time.

• ALTER ACTIVE STANDBY PAIR statements can be executed only on the active database. If ALTER ACTIVE STANDBY PAIR is executed on the active database, then the standby database must be regenerated by duplicating the active database. All subscribers must also be regenerated from the standby database. See "Duplicating a database".

• Read-only subscribers can be created only by duplicating the standby database. If the standby database is unavailable, then the read-only subscribers can be created by duplicating the active database. See "Duplicating a database".

• You can specify at most 127 subscriber databases.

• Replication from the standby database to the read-only subscribers occurs asynchronously.

• Writes on replicated tables are not allowed on the standby database or the subscriber databases. However, operations on sequences and XLA bookmarks are allowed on the standby database and the subscriber databases. Reads are also allowed.

• After failover, the new standby database can only be recovered from the active database by duplicating the active database unless return twosafe replication is used between the active and the standby databases. If return twosafe replication is used, the automated master catch-up feature may be used instead. See "Automatic catch-up of a failed master database".

• You cannot replicate a temporary database.

• You cannot replicate tables with compressed columns.

Defining the DSNs for the databases

Before you define the active standby pair, define the DSNs for the active, standby, and read-only subscriber databases. On UNIX, create an odbc.ini file. On Windows, use the ODBC Administrator to name the databases and set connection attributes. See "Step 1: Create the DSNs for the master and the subscriber databases" for an example.

Each database "name" specified in a replication scheme must match the prefix of the database file name (without the path) given for the DataStore data store attribute in the DSN definition for the database. To avoid confusion, use the same name for both the DataStore and Data Source Name data store attributes in each DSN definition. Values for DataStore are case-sensitive. For example, if the database path is directory/subdirectory/foo.ds0, then foo is the database name that you should use.

Table requirements for active standby pairs

Before you can create an active standby pair, you must create an object to be replicated.

Tables that are replicated in an active standby pair 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 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 active database must also exist on its counterpart table in the standby database.

Replicated tables have these data type restrictions:

• VARCHAR2, NVARCHAR2, VARBINARY and TT_VARCHAR columns in replicated tables are 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 one million characters. For an NVARCHAR2 column, which requires two bytes for each character, the maximum length when using character length semantics is two million characters.

• Columns with the BLOB data type in replicated tables are limited to a size of 16 megabytes. Columns with the CLOB or NCLOB data type in replicated tables are limited to a size of 4 megabytes.

• A primary key column cannot have a LOB data type.

You cannot replicate tables with compressed columns.

Defining an active standby pair replication scheme

Use the CREATE ACTIVE STANDBY PAIR SQL statement to create an active standby pair replication scheme. You must have the ADMIN privilege to use the CREATE ACTIVE STANDBY PAIR statement and to perform other replication operations. Only the instance administrator can duplicate databases.

Table 3-1 shows the components of the CREATE ACTIVE STANDBY PAIR statement that are used to create the active standby pair replication scheme. Each component is described with the identified topics in this chapter.

Identifying the databases in the active standby pair

The first component identifies the active database, standby database, and any subscriber databases. The first database name designates the active database. The second database name designates the standby database. Read-only subscriber databases are indicated by the SUBSCRIBER clause.

Use the full database name described in "Defining the DSNs for the databases".

Command> CREATE ACTIVE STANDBY PAIR master1, master2
       > SUBSCRIBER subscriber1;

The active database and the standby database should be on separate hosts to achieve a highly available system. Read-only subscribers can be either local or remote. A remote subscriber provides protection from site-specific disasters.

You can also specify the hosts where the databases reside by using an IP address or a literal host name surrounded by double quotes. Provide a host ID as part of FullDatabaseName:

DatabaseName [ON Host]

Host can be either an IP address or a literal host name. Use the value returned by the hostname operating system command. It is good practice to surround a host name with double quotes. For example:

Command> CREATE ACTIVE STANDBY PAIR 
  > repdb1 ON "host1", 
  > repdb2 ON "host2";

Using a return service for an active standby pair

You can configure your replication scheme with a return service to ensure a higher level of confidence that your replicated data is consistent on the active and standby databases. For full details on how to configure a return service for your replication scheme, see "Using a return service".

Setting STORE attributes for an active standby pair

The STORE attributes clause in either the CREATE ACTIVE STANDBY PAIR or ALTER ACTIVE STANDBY PAIR statements are used to set optional behavior for return services, compression, timeouts, durable commit behavior, and table definition checking. The full list of STORE attributes are described in "CREATE ACTIVE STANDBY PAIR" in the Oracle TimesTen In-Memory Database SQL Reference.

See "Setting STORE attributes" for more details on how to use and configure the STORE attributes for an active standby pair.

Configuring network operations for an active standby pair

If a replication host has more than one network interface, you may wish to configure replication to use an interface other than the default interface. For details, see "Configuring network operations".

Using automatic client failover for an active standby pair

Automatic client failover is for use in High Availability scenarios with a TimesTen active standby pair replication configuration. If failure of the active TimesTen node results in the original standby node becoming the new active node, then automatic client failover feature automatically transfers the application connection to the new active node.

For full details on how to configure and use automatic client failover, see "Using automatic client failover" in the Oracle TimesTen In-Memory Database Operations Guide.

Including or excluding database objects from replication

An active standby pair replicates an entire database by default. Use the INCLUDE clause to replicate only the tables, cache groups and sequences that are listed in the INCLUDE clause. No other database objects are replicated in an active standby pair that is defined with an INCLUDE clause. For example, this INCLUDE clause specifies three tables to be replicated by the active standby pair:

INCLUDE TABLE employees, departments, jobs

You can choose to exclude specific tables, cache groups or sequences from replication by using the EXCLUDE clause of the CREATE ACTIVE STANDBY PAIR statement. Use one EXCLUDE clause for each object type. For example:

EXCLUDE TABLE ttuser.tab1, ttuser.tab2
EXCLUDE CACHE GROUP ttuser.cg1, ttuser.cg2
EXCLUDE SEQUENCE ttuser.seq1, ttuser.seq2

Replicating tables with foreign key relationships in an active standby pair

With the active standby pair replication scheme, you may choose to replicate all or a subset of tables that have foreign key relationships with one another. You can create the tables and the foreign key relationship on the active master either before or after the active standby pair replication scheme is created.

• Before creation of active standby pair: You can create the tables and the foreign key relationship on the active master before the active standby pair replication scheme is created. Then, create the active standby pair replication scheme.

• After creation of active standby pair: You can create the tables and the foreign key relationship on the active master after the active standby pair replication scheme is created. In order for the tables to be automatically replicated to the standby master and added to the replication scheme, you must be using the default mode where DDLReplicationLevel=2 and DDLReplicationAction=INCLUDE.

If a child table with a foreign key defines ON DELETE CASCADE, then you must replicate any other table with a foreign key relationship to the child table. This requirement prevents foreign key conflicts from occurring on the standby master tables when a cascade deletion occurs on the active master database.

TimesTen replicates a cascade deletion as a single operation, rather than replicating to the subscriber each individual row deletion which occurs on the child table when a row is deleted on the parent. As a result, any row on the child table on the subscriber database, which contains the foreign key value that was deleted on the parent table, is also deleted, even if that row did not exist on the child table on the master database.

Materialized views in an active standby pair

When you replicate a database containing a materialized or nonmaterialized view, only the detail tables associated with the view are replicated. The view itself is not replicated. A matching view can be defined on the standby database, but it is not required. If detail tables are replicated, TimesTen automatically updates the corresponding view. However, TimesTen replication verifies only that the replicated detail tables have the same structure on both databases. It does not enforce that the materialized views are the same on each database.

Replicating sequences in an active standby pair

Sequences are replicated unless you exclude them from the active standby pair or unless they have the CYCLE attribute. See "Including or excluding database objects from replication". Replication of sequences is optimized by reserving a range of sequence numbers on the standby database each time a sequence is updated on the active database. Reserving a range of sequence numbers reduces the number of updates to the transaction log. The range of sequence numbers is called a cache. Sequence updates on the active database are replicated only when they are followed by or used in replicated transactions.

Consider a sequence named my.sequence with a MINVALUE of 1, an INCREMENT of 1 and the default Cache of 20. The very first time that you reference my.sequence.NEXTVAL, the current value of the sequence on the active database is changed to 2, and a new current value of 21 (20+1) is replicated to the standby database. The next 19 references to my.seq.NEXTVAL on the active database result in no new current value being replicated, because the current value of 21 on the standby database is still ahead of the current value on the active database. On the twenty-first reference to my.seq.NEXTVAL, a new current value of 41 (21+20) is transmitted to the standby database because the previous current value of 21 on the standby database is now behind the value of 22 on the active database.

Operations on sequences such as SELECT my.seq.NEXTVAL FROM sys.dual, while incrementing the sequence value, are not replicated until they are followed by transactions on replicated tables. A side effect of this behavior is that these sequence updates are not purged from the log until followed by transactions on replicated tables. This causes ttRepSubscriberWait and ttRepAdmin -wait to fail when only these sequence updates are present at the end of the log.