Setting up an Active Standby Timesten

Active	Standby
Server: 192.168.2.149(vtgptudserver) Data Store: master1 Installing Directory:/u01/app/TimesTen/tt1121 Port's Daemon: 53396 Port TCP/IP Server TimesTen listen on: 53397	Server: 192.168.2.133 ( PTUDServer3) Data Store: master2 Installing Directory:/u01/app/TimesTen/tt1122/ Daemon port: 53388 Port TCP/IP Server TimesTen listen on: 53389

1. Create an Active Master Database

a. Log in to server 1 (vtgptudserver) as the instance administrator and define a DSN for the active master database. In this example we will use a pre-defined DSN master1

Refer to the section Setting up Data Source Name if you need information on how to define your own DSNs.

b. Create and connect to the database master1 using ttIsql:

ttIsql master1

Active (vtgptudserver)

[orattadmin@vtgptudserver info]$ ttisql master1

Type ? or "help" for help, type "exit" to quit ttIsql.

connect "DSN=master1";

Connection successful: DSN=master1;UID=orattadmin;DataStore=/u01/app/TimesTen/tt1122/info/master1;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;

(Default setting AutoCommit=1)

Command>

If necessary, refer to the section Creating a TimesTen Database for more information.

2. Create a Database User to Administer the Replication Operations

a. Create a database user and assign it the role of the database and replication administrator. This user is responsible for setting up and managing the active standby pair.

In ttIsql, create an administrator and grant this user admin privilege. In the example, the user adm is the administrator

create user adm identified by adm;
grant admin to adm;

Active (vtgptudserver)

Command> CREATE USER adm IDENTIFIED BY adm;

User created.

Command> GRANT ADMIN TO adm;

b. Create an application user and tables in the database. In our example, we will create an application user called appuser and this user will own the customers and orders tables.

Create user appuser identified by appuser;

grant create session, create table to appuser;

Active (vtgptudserver)

Command> CREATE USER appuser IDENTIFIED BY appuser;

User created.

Command> GRANT CREATE SESSION, CREATE TABLE TO appuser;

c. Log in as the user appuser in ttIsql. Execute the following SQL statements, or run the script create_app_user_obj.sql located in the quickstart/sample_scripts/replication/create_appuser_obj.sql”

Active (vtgptudserver)

connect "dsn=master1;uid=appuser";

run "quickstart/sample_scripts/replication/create_appuser_obj.sql"

create table customers (
cust_number number,
first_name varchar2(12) not null,
last_name varchar2(12) not null,
address varchar2(100) not null,
primary key (cust_number));

insert into customers values (3700,'Peter','Burchard','882 Osborne Avenue, Boston, MA 02122');
insert into customers values (1121,'Saul','Mendoza','721 Stardust Street, Mountain View, CA 94043');

create table orders (
order_number number not null,
cust_number number not null,
prod_number char(10) not null,
order_date date not null,
primary key (order_number),
foreign key (cust_number) references customers (cust_number));

insert into ORDERS values (6853036,3700,'0028616731',to_date('2008-04-05','yyyy-mm-dd'));
insert into ORDERS values (6853041,3700,'0198612710',to_date('2009-01-12','yyyy-mm-dd'));
insert into ORDERS values (6853169,1121,'0003750299',to_date('2008-08-01','yyyy-mm-dd'));
insert into ORDERS values (6853174,1121,'0789428741',to_date('2008-10-25','yyyy-mm-dd'));
insert into ORDERS values (6853179,1121,'0198612583',to_date('2009-02-02','yyyy-mm-dd'));

d. Use the tables command in ttIsql to confirm that the tables has been created.

Active (vtgptudserver)

Command> tables;

APPUSER.CUSTOMERS

APPUSER.ORDERS

2 tables found.

Command>

After setting up an initial database, you can now define the configuration of the active standby pair and assign to the current database the active state.

3. Define the active standby pair

a. In ttIsql, connect to master1 as the replication administrator adm

connect “dsn=master1;uid=adm”;

Active (vtgptudserver)

Command> connect "dsn=master1;uid=adm";

Enter password for 'adm':

Connection successful: DSN=master1;UID=adm;DataStore=/u01/app/TimesTen/tt1122/info/master1;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/TimesTen/tt1122/lib/libtten.so;PermSize=40;TempSize=32;TypeMode=0;

(Default setting AutoCommit=1)

Command>

b. Define an active standby pair schema by using CREATE ACTIVE STANDBY PAIR statement. The default replication mode for an active standby pair is asynchronous.

CREATE ACTIVE STANDBY PAIR master1 ON "vtgptudserver", master2 ON "PTUDServer3"

Command> CREATE ACTIVE STANDBY PAIR master1 ON "ptudvtg149", master2 ON "PTUDServer3";

Note: The data store names master1 and master2 used in the CREATE ACTIVE STANDBY statement are not DSNs. They are the filenames defined in the DataStore attribute. The hostnames used in defining the active standby pair should match the values returned by the OS hostname command on the respective servers.

c. The ttIsql command repschemes can be used to verify the current replication scheme deployed in the database.

repschemes;

Active (vtgptudserver)

Command> repschemes;

Replication Scheme Active Standby:

Master Store: MASTER1 on vtgptudserver

Master Store: MASTER2 on PTUDSERVER3

Excluded Tables:

None

Excluded Cache Groups:

None

Excluded sequences:

None

Store: MASTER1 on vtgptudserver

Port: (auto)

Log Fail Threshold: (none)

Retry Timeout: 120 seconds

Compress Traffic: Disabled

Store: MASTER2 on PTUDSERVER3

Port: (auto)

Log Fail Threshold: (none)

Retry Timeout: 120 seconds

Compress Traffic: Disabled

1 replication scheme found.

Command> ALTER ACTIVE STANDBY PAIR

> ALTER STORE master1 on "ptudvtg149"SET PORT 53388 TIMEOUT 180

> ALTER STORE master2 on "PTUDServer3" SET PORT 53388 TIMEOUT 180;

4. Start the replication agent

a.	The replication agent is responsible for replicating data between TimesTen databases. It must be running on both databases in an active standby pair. Use the built-in procedure ttRepStart to start the Replication agent.

	call ttrepstart;

5. Set the replication state to Active

	Use the built-in procedure ttRepStateSet to designate the current database (repdb1_1121) the active state in the active standby pair. ttRepStateGet returns the current replication state of the database.

	call ttrepstateset ('active'); call ttrepstateget;

Active (vtgptudserver)
Command> call ttrepstart; Command> Command> call ttrepstateset ('active'); Command> call ttrepstateget; < ACTIVE, NO GRID > 1 row found.

After setting up an active master database and defining the active standby pair configuration, you can now create the standby database

6. Duplicate the active database to the standby

a.	Log in to server 2 (PTUDServer3) as the instance administrator. Use the ttRepAdmin utility to duplicate from the active database to the standby database.

	ttrepadmin -duplicate -from master1 -host "ptudvtg149" -uid adm -pwd adm "dsn=master2"

	Note that the -from clause is using the data store name master1 and not the DSN name; -uid and -pwd correspond to the username and password of the ADMIN user on the active database.
Standby(PTUDServer3)
[orattadmin@PTUDServer3 info]$ ttrepadmin -duplicate -remoteDaemonPort 53396 -verbosity 2 -from master1 -host "ptudvtg149" -uid adm -pwd adm "dsn=master2" 14:30:15 Contacting remote main daemon at 192.168.2.149 port 53396 14:30:15 Contacting the replication agent for MASTER1 ON PTUDVTG149 (192.168.2.149) port 53388 14:30:15 Beginning transfer from MASTER1 ON PTUDVTG149 to MASTER2 ON PTUDSERVER3 14:30:22 Checkpoint transfer 10 percent complete 14:30:22 Checkpoint transfer 20 percent complete 14:30:22 Checkpoint transfer 100 percent complete 14:30:22 Checkpoint transfer phase complete 14:30:24 Log transfer 100 percent complete 14:30:24 Log transfer phase complete 14:30:24 Transfer complete 14:30:27 Duplicate Operation Ends

7. Start the replication agent

a.	Using ttIsql, connect to the database master2 as the replication administrator adm.

	ttisql "dsn=master2;uid=adm"

b.	The replication agent on the standby is responsible for processing the data from the active database. Use the built-in procedure ttRepStart to start the replication agent.

	call ttrepstart;

c.	Wait for master2 to enter the standby state. Use the built-in procedure ttRepStateGet to verify the current replication state of the database.

	call ttrepstateget;
Standby(PTUDServer3)
Command> call ttrepstateget; < STANDBY, NO GRID > 1 row found.