Configuring an Active Standby Pair With One Subscriber

You can create an active standby pair with a single subscriber.

This section describes how to create an active standby pair with one subscriber. The active database is master1. The standby database is master2. The subscriber database is subscriber1. To keep the example simple, all databases reside on the same computer, server1.

Figure 2-1 shows this configuration.

Figure 2-1 Active Standby Pair With One Subscriber

Description of "Figure 2-1 Active Standby Pair With One Subscriber"

Create the DSNs for the master and the subscriber databases.

Create DSNs named master1, master2 and subscriber1 as described in Managing TimesTen Databases in Oracle TimesTen In-Memory Database Operations Guide.

Use a text editor to create the following odbc.ini file:

[master1]
DataStore=/tmp/master1
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

[master2]
DataStore=/tmp/master2
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

[subscriber1]
DataStore=/tmp/subscriber1
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

Optional: Enable TLS to encrypt communication between replication agents.

You can enable secure TCP/IP network connections between replication agents (and utilities that communicate with the replication agents) by using Transport Layer Security (TLS), which requires mutual authentication to encrypt communication over connections. You can also generate certificates. See Transport Layer Security for TimesTen Replication in the Oracle TimesTen In-Memory Database Security Guide.

Create a table in one of the master databases.

Use the ttIsql utility to connect to the master1 database:

% ttIsql master1
 
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=master1";
Connection successful: DSN=master1;UID=timesten;DataStore=/tmp/master1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;
(Default setting AutoCommit=1)
Command>

Create the employees table;

Command> CREATE TABLE employees
    ( employee_id   NUMBER(6) PRIMARY KEY,
     first_name     VARCHAR2(20),
     last_name      VARCHAR2(25) NOT NULL,
     email          VARCHAR2(25) NOT NULL UNIQUE,
     phone_number   VARCHAR2(20),
     hire_date      DATE NOT NULL,
     job_id         VARCHAR2(10) NOT NULL,
     salary         NUMBER(8,2),
     commission_pct NUMBER(2,2),
     manager_id     NUMBER(6),
     department_id  NUMBER(4)
  ) ;

Define the active standby pair.

The following defines the active standby pair on master1:
```
Command> CREATE ACTIVE STANDBY PAIR master1, master2
        SUBSCRIBER subscriber1;
```
See Defining an Active Standby Pair Replication Scheme.
Start the replication agent on a master database.

The following starts the replication agent on master1:
```
Command> CALL ttRepStart;
```
See Starting and Stopping the Replication Agents.
Set the state of a master database to ACTIVE.

The state of a new database in an active standby pair is IDLE until the active database has been set.

Use the ttRepStateSet built-in procedure to designate master1 as the active database:
```
CALL ttRepStateSet('ACTIVE');
```
Verify the state of master1:
```
Command> CALL ttRepStateGet;
< ACTIVE >
1 row found.
```
Create a user on the active database.

Create a user ttuser with a password of ttuser and grant ttuser the ADMIN privilege. Creating a user with the ADMIN privilege is required by Access Control for the next step.
```
Command> CREATE USER ttuser IDENTIFIED BY ttuser;
User created.
Command> GRANT ADMIN TO ttuser;
```
Duplicate the active database to the standby database.

Exit ttIsql and use the ttRepAdmin utility as the ttuser (the user created with the ADMIN privilege) with the -duplicate option to duplicate the active database to the standby database. If you are using two different hosts, enter the ttRepAdmin command from the target host.
```
% ttRepAdmin -duplicate -from master1 -host server1 -uid ttuser -pwd ttuser 
master2
```

Start the replication agent on the standby database.

Use ttIsql to connect to the master2 database and start the replication agent:

% ttIsql master2
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=master2";
Connection successful: DSN=master2;UID=timesten;DataStore=/tmp/master2;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;
(Default setting AutoCommit=1)

Command> CALL ttRepStart;

Starting the replication agent for the standby database automatically sets its state to 'STANDBY'. Verify the state of master2:

Command> CALL ttRepStateGet;
< STANDBY >
1 row found.

Duplicate the standby database to the subscriber.

Exit ttIsql and use the ttRepAdmin utility as the ttuser (the user created with the ADMIN privilege) to duplicate the standby database to the subscriber database:
```
% ttRepAdmin -duplicate -from master2 -host server1 -uid ttuser -pwd ttuser 
subscriber1
```

Start the replication agent on the subscriber.

Use ttIsql to connect to subscriber1 and start the replication agent. Verify the state of subscriber1. Starting the replication agent for the subscriber database automatically sets its state to 'IDLE'. All subscriber databases have their state set to 'IDLE'.

% ttIsql subscriber1
 
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=subscriber1";
Connection successful: DSN=subscriber1;UID=timesten;DataStore=/stmp/subscriber1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;
(Default setting AutoCommit=1)

Command> CALL ttRepStart;

Command> call ttRepStateGet;
< IDLE >
1 row found.

Insert data into the table on the active database.

Use the ttIsql utility to connect to the master1 database:

% ttIsql master1
 
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=master1";
Connection successful: DSN=master1;UID=timesten;DataStore=/tmp/master1;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;
(Default setting AutoCommit=1)
Command>

Insert a row into the employees table on master1.

Command> INSERT INTO employees VALUES
      ( 202, 
        'Pat', 
        'Fay',
        'PFAY',
        '603-123-7777',
        TO_DATE('17-AUG-1997', 'dd-MON-yyyy'),
        'MK_REP',
        6000,
        NULL,
        201,
        20
   );
1 row inserted.
Command> SELECT * FROM employees;
< 202, Pat, Fay, PFAY, 603-123-7777, 1997-08-17 00:00:00, MK_REP, 
 6000, <NULL>, 201, 20 >
1 row found.

Verify that the insert is replicated to master2 and subscriber1. Use ttIsql to connect to master2:

% ttIsql master2
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
 
connect "DSN=master2";
Connection successful: DSN=master2;UID=timesten;DataStore=/tmp/master2;
DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;
(Default setting AutoCommit=1)

Verify that the data is replicated to master2:

Command> SELECT * FROM employees;
< 202, Pat, Fay, PFAY, 603-123-7777, 1997-08-17 00:00:00, MK_REP, 
6000, <NULL>, 201, 20 >
1 row found.

Perform the same step on subscriber1 to verify the data is replicated to the subscriber.

Drop the active standby pair and the table.
1. Connect to each database using ttIsql and stop the replication agents on each database:
```
Command> CALL ttRepStop;
```
2. Drop the active standby pair on each database. You can then drop the employees table on any database in which you have dropped the active standby pair.
```
Command> DROP ACTIVE STANDBY PAIR;
Command> DROP TABLE employees;
```