| 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.
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 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=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;
|
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;
|
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. | ||
8. Verify the data is being replicated between the active and the standby
| a. | In ttIsql, connect to master1 as appuser, and insert a record into the orders table. | |
| | | |
| | connect "dsn=master1; uid=appuser"; insert into orders values (6853180,1121,'9999999999', sysdate); | |
| |
| |
| |  | |
| | | |
| b. | Verify that the insert is replicated to the standby database master2. | |
| | | |
| | select * from appuser.orders where order_number=6853180; | |
| |
| |
| |  |
Reference:
