2 Getting Started
The following sections describe how to configure and start up sample replication schemes:
Note:
You must have theADMIN 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
-
Optional step: Enable TLS to encrypt communication between replication agents
-
Step 7: Duplicate the active database to the standby database
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 or Linux systems, 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
On Windows, use the ODBC Administrator to set the same connection attributes. Use defaults for all other settings.
Optional step: 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 for full details.
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; (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;
For more information, see "Starting and stopping the replication agents".
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 > 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; (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.
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; (Default setting AutoCommit=1) Command> CALL ttRepStart; Command> call ttRepStateGet; < IDLE > 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; (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.
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 classic replication scheme with one master and one subscriber
This section describes how to configure a classic 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 classic replication scheme
Description of ''Figure 2-2 Simple classic replication scheme''
This section includes the following topics:
-
Step 2: Create a table and classic replication scheme on the master database
-
Step 3: Create a table and replication scheme on the subscriber database
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 or Linux 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 classic 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; (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 classic 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.
For more information, see "Starting and stopping the replication agents".
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 classic 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 classic replication scheme from the master and subscriber databases, enter these statements on each database:
Command> DROP REPLICATION repscheme; Command> DROP TABLE employees;
Starting and stopping the replication agents
After you have defined a replication scheme, you can start the replication agents for each database involved in the replication scheme. You must have the ADMIN privilege to start or stop a replication agent.
You can start and stop a replication agent by using the ttAdmin utility with the -repStart or -repStop option. You can also use the ttRepStart and ttRepStop built-in procedures to start and stop a replication agent from the ttIsql command line.
Example 2-1 Starting and stopping the replication agent with ttAdmin
To start the replication agents for the DSNs named masterDSN and subscriberDSN, enter:
ttAdmin -repStart masterDSN ttAdmin -repStart subscriberDSN
To stop the replication agents, enter:
ttAdmin -repStop masterDSN ttAdmin -repStop subscriberDSN
Example 2-2 Starting and stopping the replication agent from ttIsql
To start and stop the replication agent for the DSN named masterDSN, enter:
> ttIsql masterDSN Command> call ttRepStart; Command> call ttRepStop;
You can also use the ttAdmin utility to set the replication restart policy. By default the policy is manual, which enables you to start and stop the replication agents as described above. Alternatively, you can set the replication restart policy for a database to always or norestart.
| Restart Policy | Start replication agent when the TimesTen daemon starts | Restart replication agent on errors or invalidation |
|---|---|---|
always |
Yes | Yes |
manual |
No | Yes |
norestart |
No | No |
Note:
The TimesTen daemon manages the replication agents. It must be running to start or stop the replication agents.When the restart policy is always, the replication agent is automatically started when the database is loaded into memory. See "Specifying a RAM policy" in Oracle TimesTen In-Memory Database Operations Guide to determine when a database is loaded into memory.
Example 2-3 Using ttAdmin to set the restart policy
To use ttAdmin to set the replication restart policy to always, enter:
ttAdmin -repPolicy always DSN
To reset the policy back to manual, enter:
ttAdmin -repPolicy manual DSN
Following a database invalidation, both manual and always policies cause the replication agent to be automatically restarted. When the agent restarts automatically, it is often the first connection to the database. This happens after a fatal error that, for example, requires all applications to disconnect. The first connection to a database usually has to load the most recent checkpoint file and often needs to do recovery. For a very large database, this process may take several minutes. During this period, all activity on the database is blocked so that new connections cannot take place and any old connections cannot finish disconnecting. This may also result in two copies of the database existing at the same time because the old one stays around until all applications have disconnected. For very large databases for which the first-connect time may be significant, you may want to wait for the old database to become inactive first before starting up the new one. You can do this by setting the restart policy to norestart to specify that the replication agent is not to be automatically restarted. For more information on setting policies that would prevent the database from being reloaded, see "Specifying a RAM policy" in Oracle TimesTen In-Memory Database Operations Guide to determine when a database is loaded into memory.