3 Defining an Active Standby Pair Replication Scheme

Overview of master database states

This section summarizes the possible states of a master database. These states are referenced in the tasks described in the rest of the chapter.

The master databases can be in one of the following states:

• ACTIVE - A database in this state is the active database. Applications can update its replicated tables.

• STANDBY - A database in this state is the standby database. Applications can update only nonreplicated tables in the standby database. Nonreplicated tables are tables that have been excluded from the replication scheme by using the EXCLUDE TABLE or EXCLUDE CACHE GROUP clauses of the CREATE ACTIVE STANDBY PAIR statement.

• FAILED - A database in this state is a failed master database. No updates can be replicated to it.

• IDLE - A database in this state has not yet had its role in the active standby pair assigned. It cannot be updated. Every database comes up in the IDLE state.

• RECOVERING - When a previously failed master database is synchronizing updates with the active database, it is in the RECOVERING state.

You can use the ttRepStateGet built-in procedure to discover the state of a master database.

Duplicating a database

When you set up a replication scheme or administer a recovery, a common task is to duplicate a database. Use the -duplicate option of the ttRepAdmin utility or the ttRepDuplicateEx C function to duplicate a database.

To duplicate a database, these conditions must be fulfilled:

• The instance administrator performs the duplicate operation.

• The instance administrator user name must be the same on both instances involved in the duplication.

• You must provide the user name and password for a user with the ADMIN privilege on the source database.

• The target DSN cannot include client/server attributes.

On the source database, create a user and grant the ADMIN privilege to the user:

Command> CREATE USER ttuser IDENTIFIED BY ttuser;
User created.

Command> GRANT ADMIN TO ttuser;

Assume the user name of the instance administrator is timesten. Logged in as timesten on the target host, duplicate the dsn1 database on host1 to dsn2:

ttRepAdmin -duplicate -from dsn1 -host host1 dsn2

Enter internal UID at the remote datastore with ADMIN privileges: ttuser 
Enter password of the internal Uid at the remote datastore:

Enter ttuser when prompted for the password of the internal user at the remote database.

If you want to use a specific local or remote network interface over which the database duplication occurs, you can optionally specify either by providing an alias or the IP address of the network interface. You can specify the local and remote network interfaces for the source and target hosts by using the -localIP and -remoteIP options of ttRepAdmin -duplicate. If you do not specify one or both network interfaces, TimesTen chooses them.

If you are duplicating an active database that has cache groups, use the -keepCG option. You must also specify the cache administration user name and password with the -cacheUid and -cachePwd options. If you do not provide the cache administration user password, ttRepAdmin prompts for a password. If the cache administration user name is orauser and the password is orapwd, duplicate database dsn1 on host1:

ttRepAdmin -duplicate -from dsn1 -host host1 -keepCG 
 -connStr "DSN=dsn2;UID=;PWD="

Enter internal UID at the remote datastore with ADMIN privileges: ttuser 
Enter password of the internal Uid at the remote datastore:

Enter ttuser when prompted for the password. The ttRepAdmin utility then prompts for the cache administration user and password:

Enter cache administrator UID: orauser
Enter cache administrator password: 

Enter orapwd when prompted for the cache administration password.

The UID and PWD for dsn2 are specified as null values in the connection string so that the connection is made as the current OS user, which is the instance administrator. Only the instance administrator can run ttRepAdmin -duplicate. If dsn2 is configured with PWDCrypt instead of PWD, then the connection string should be "DSN=dsn2;UID=;PWDCrypt=".

When you duplicate a standby database with cache groups to a read-only subscriber, use the -nokeepCG option. In this example, dsn2 is the standby database and sub1 is the read-only subscriber:

ttRepAdmin -duplicate -from dsn2 -host host2 -nokeepCG 
 -connStr "DSN=sub1;UID=;PWD="

The ttRepAdmin utility prompts for values for -uid and -pwd.

If you cannot access the Oracle database (either the Oracle database is down or you cannot connect to it) while performing a duplicate for a replication scheme with AWT or incremental autorefresh cache groups, then the ttRepAdmin -duplicate command cannot update the metadata on the Oracle database (that cache uses to manage AWT and autorefresh cache groups) after AWT or incremental autorefresh cache groups are duplicated. In this case, use one of the following options to perform the duplicate:

• If you are using ttRepAdmin -duplicate to recover either a failed active or standby master where all AWT or incremental autorefresh cache groups are included in the active standby pair replication scheme, then use the -keepCG -recoveringNode options. When this option is used, changes that occur during the duplicate operation are tracked and so may not need to initiate a full autorefresh.

• Otherwise, use the -keepCG -deferCacheUpdate options. This option may initiate a full autorefresh.

After completion of the duplicate operation with either -keepCG -recoveringNode options or -keepCG -deferCacheUpdate options, warning messages are posted informing you that while the duplicate operation was successful, updates to the Oracle database metadata are deferred until the cache and replication agents are started. Thus, once the duplicate operation is complete, start both the cache and replication agents on the new node. If there are cascading TimesTen node failures and intermittent connectivity problems with the Oracle database, then starting the cache and replication agents may initiate a full autorefresh.

For more information about the ttRepAdmin utility, see "ttRepAdmin" in Oracle TimesTen In-Memory Database Reference. For more information about the ttRepDuplicateEx C function, see "ttRepDuplicateEx" in Oracle TimesTen In-Memory Database C Developer's Guide.

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 active and standby master databases as well as all subscriber databases should be on different machines.

• 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. See "CREATE ACTIVE STANDBY PAIR" in the Oracle TimesTen In-Memory Database SQL Reference for a full description of STORE attributes.

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 want to configure replication to use an interface other than the default interface. For details, see "Configuring network interfaces with the ROUTE clause".

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 is set to 2 or larger 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.