2 Getting Started

The following sections describe how to configure and start up sample replication schemes:

Configuring an active standby pair with one subscriber
Configuring a replication scheme with one master and one subscriber

Note:

You must have the ADMIN privilege to complete the procedures in this chapter.

Configuring an active standby pair with one 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"

This section includes the following topics:

Step 1: Create the DSNs for the master and the subscriber databases
Step 2: Create a table in one of the master databases
Step 3: Define the active standby pair
Step 4: Start the replication agent on a master database
Step 5: Set the state of a master database to 'ACTIVE'
Step 6. Create a user on the active database
Step 7: Duplicate the active database to the standby database
Step 8: Start the replication agent on the standby database
Step 9. Duplicate the standby database to the subscriber
Step 10: Start the replication agent on the subscriber
Step 11: Insert data into the table on the active database
Step 12: Drop the active standby pair and the table

Step 1: 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.

On UNIX systems, use a text editor to create the following odbc.ini file:

[master1]
DRIVER=install_dir/lib/libtten.so
DataStore=/tmp/master1
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

[master2]
DRIVER=install_dir/lib/libtten.so
DataStore=/tmp/master2
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

[subscriber1]
DRIVER=install_dir/lib/libtten.so
DataStore=/tmp/subscriber1
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

On Windows, use the ODBC Administrator to set the same connection attributes. Use defaults for all other settings.

Step 2: 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;TypeMode=0;
(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)
 > ) ;

Step 3: Define the active standby pair

Define the active standby pair on master1:

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

For more information about defining an active standby pair, see Chapter 3, "Defining an Active Standby Pair Replication Scheme".

Step 4: Start the replication agent on a master database

Start the replication agent on master1:

Command> CALL ttRepStart;

Step 5: 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:

Command> CALL ttRepStateSet('ACTIVE');

Verify the state of master1:

Command> CALL ttRepStateGet;
< ACTIVE, NO GRID >
1 row found.

Step 6. 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;

Step 7: 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

Step 8: 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;TypeMode=0;
(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, NO GRID >
1 row found.

Step 9. 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

Step 10: 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;TypeMode=0;
(Default setting AutoCommit=1)

Command> CALL ttRepStart;

Command> call ttRepStateGet;
< IDLE, NO GRID >
1 row found.

Step 11: 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;TypeMode=0;
(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;TypeMode=0;
(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.

Step 12: Drop the active standby pair and the table

Connect to each database using ttIsql and stop the replication agents on each database:

Command> CALL ttRepStop;

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;

Configuring a replication scheme with one master and one subscriber

This section describes how to configure a replication scheme that replicates the contents of a single table in a master database (masterds) to a table in a subscriber database (subscriberds). To keep the example simple, both databases reside on the same computer.

Figure 2-2 Simple replication scheme

Description of "Figure 2-2 Simple replication scheme"

This section includes the following topics:

Step 1: Create the DSNs for the master and the subscriber
Step 2: Create a table and replication scheme on the master database
Step 3: Create a table and replication scheme on the subscriber database
Step 4: Start the replication agent on each database
Step 5: Insert data into the table on the master database
Step 6: Drop the replication scheme and table

Step 1: Create the DSNs for the master and the subscriber

Create DSNs named masterds and subscriberds as described in "Managing TimesTen Databases" in Oracle TimesTen In-Memory Database Operations Guide.

On UNIX systems, use a text editor to create the following odbc.ini file on each database:

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

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

On Windows, use the ODBC Administrator to set the same connection attributes. Use defaults for all other settings.

Step 2: Create a table and replication scheme on the master database

Connect to masterds with the ttIsql utility:

% ttIsql masterds
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=masterds";
Connection successful: DSN=masterds;UID=timesten;
DataStore=/tmp/masterds;DatabaseCharacterSet=AL32UTF8;
ConnectionCharacterSet=AL32UTF8;TypeMode=0;
(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)
 > ) ;

Create a replication scheme called repscheme to replicate the employees table from masterds to subscriberds.

Command> CREATE REPLICATION repscheme
       > ELEMENT e TABLE employees
       > MASTER masterds
       > SUBSCRIBER subscriberds;

Step 3: Create a table and replication scheme on the subscriber database

Connect to subscriberds and create the same table and replication scheme, using the same procedure described in Step 2.

Step 4: Start the replication agent on each database

Start the replication agents on masterds and subscriberds:

Command> call ttRepStart;

Exit ttIsql. Use the ttStatus utility to verify that the replication agents are running for both databases:

% ttStatus
TimesTen status report as of Thu Aug 11 17:05:23 2011
 
Daemon pid 18373 port 4134 instance ttuser
TimesTen server pid 18381 started on port 4136
------------------------------------------------------------------------
Data store /tmp/masterds
There are 16 connections to the data store
Shared Memory KEY 0x0201ab43 ID 5242889
PL/SQL Memory KEY 0x0301ab43 ID 5275658 Address 0x10000000
Type            PID     Context     Connection Name              ConnID
Process         20564   0x081338c0  masterds                          1
Replication     20676   0x08996738  LOGFORCE                          5
Replication     20676   0x089b69a0  REPHOLD                           2
Replication     20676   0x08a11a58  FAILOVER                          3
Replication     20676   0x08a7cd70  REPLISTENER                       4
Replication     20676   0x08ad7e28  TRANSMITTER                       6
Subdaemon       18379   0x080a11f0  Manager                        2032
Subdaemon       18379   0x080fe258  Rollback                       2033
Subdaemon       18379   0x081cb818  Checkpoint                     2036
Subdaemon       18379   0x081e6940  Log Marker                     2035
Subdaemon       18379   0x08261e70  Deadlock Detector              2038
Subdaemon       18379   0xae100470  AsyncMV                        2040
Subdaemon       18379   0xae11b508  HistGC                         2041
Subdaemon       18379   0xae300470  Aging                          2039
Subdaemon       18379   0xae500470  Flusher                        2034
Subdaemon       18379   0xae55b738  Monitor                        2037
Replication policy  : Manual
Replication agent is running.
Cache Agent policy  : Manual
PL/SQL enabled.
------------------------------------------------------------------------
Data store /tmp/subscriberds
There are 16 connections to the data store
Shared Memory KEY 0x0201ab41 ID 5177351
PL/SQL Memory KEY 0x0301ab41 ID 5210120 Address 0x10000000
Type            PID     Context     Connection Name              ConnID
Process         20594   0x081338f8  subscriberds                      1
Replication     20691   0x0893c550  LOGFORCE                          5
Replication     20691   0x089b6978  REPHOLD                           2
Replication     20691   0x08a11a30  FAILOVER                          3
Replication     20691   0x08a6cae8  REPLISTENER                       4
Replication     20691   0x08ad7ba8  RECEIVER                          6
Subdaemon       18376   0x080b1450  Manager                        2032
Subdaemon       18376   0x0810e4a8  Rollback                       2033
Subdaemon       18376   0x081cb8b0  Flusher                        2034
Subdaemon       18376   0x08246de0  Monitor                        2035
Subdaemon       18376   0x082a20a8  Deadlock Detector              2036
Subdaemon       18376   0x082fd370  Checkpoint                     2037
Subdaemon       18376   0x08358638  Aging                          2038
Subdaemon       18376   0x083b3900  Log Marker                     2040
Subdaemon       18376   0x083ce998  AsyncMV                        2039
Subdaemon       18376   0x08469e90  HistGC                         2041
Replication policy  : Manual
Replication agent is running.
Cache Agent policy  : Manual
PL/SQL enabled.

Step 5: Insert data into the table on the master database

Use ttIsql to connect to the master database and insert some rows into the employees table:

% ttIsql masterds
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.

Open a second command prompt window for the subscriber. Connect to the subscriber database and check the contents of the employees table:

% ttIsql subscriberds
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.

Figure 2-3 shows that the rows that are inserted into masterds are replicated to subscriberds.

Figure 2-3 Replicating changes to the subscriber database

Description of "Figure 2-3 Replicating changes to the subscriber database"

Step 6: Drop the replication scheme and table

After you have completed your replication tests, stop the replication agents on both masterds and subscriberds:

Command> CALL ttRepStop;

To remove the employees table and repscheme replication scheme from the master and subscriber databases, enter these statements on each database:

Command> DROP REPLICATION repscheme;
Command> DROP TABLE employees;