Configuring a Classic Replication Scheme With One Master and One Subscriber
You can configure a classic replication scheme that replicates the contents of a single table in a master database to a table in a subscriber database.
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"
-
Create the DSNs for the master and the subscriber.
Create DSNs named
masterdsandsubscriberdsas described in Managing TimesTen Databases in Oracle TimesTen In-Memory Database Operations Guide.Use a text editor to create the following
odbc.inifile:[masterds] DataStore=/tmp/masterds DatabaseCharacterSet=AL32UTF8 ConnectionCharacterSet=AL32UTF8 [subscriberds] DataStore=/tmp/subscriberds DatabaseCharacterSet=AL32UTF8 ConnectionCharacterSet=AL32UTF8
-
Create a table and classic replication scheme on the master database.
-
Connect to
masterdswith thettIsqlutility:% 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
employeestable: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
repschemeto replicate theemployeestable frommasterdstosubscriberds.Command> CREATE REPLICATION repscheme ELEMENT e TABLE employees MASTER masterds SUBSCRIBER subscriberds;
-
-
Create a table and replication scheme on the subscriber database.
Connect to
subscriberdsand create the same table and replication scheme, using the same procedure described in Step 2. -
Start the replication agent on each database.
Start the replication agents on
masterdsandsubscriberds:Command> call ttRepStart;
Exit
ttIsql. Use thettStatusutility 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.
-
Insert data into the table on the master database.
-
Use
ttIsqlto connect to the master database and insert some rows into theemployeestable:% 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
employeestable:% 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
masterdsare replicated tosubscriberds.Figure 2-3 Replicating Changes to the Subscriber Database
Description of "Figure 2-3 Replicating Changes to the Subscriber Database" -
-
Drop the classic replication scheme and table.
-
After you have completed your replication tests, stop the replication agents on both
masterdsandsubscriberds:Command> CALL ttRepStop;
-
To remove the
employeestable andrepschemeclassic replication scheme from the master and subscriber databases, enter these statements on each database:Command> DROP REPLICATION repscheme; Command> DROP TABLE employees;
-