Environment (two virtual machines):
- host db (192.168.2.131), OEL 5.2. x86, Oracle DB EE 11.2.0.3.0, Golden Gate 11.2.1
- host tt1 (192.168.2.156), OEL 6.3. x86_64, TimesTen 11.2.2.4.1, Golden Gate 11.1.1
1. First of all, install Golden Gate software on each host. I've already written about it here.
2. Prepare the source database (host db) for replication.
Run the following commands as sysdba:
shutdown immediate; startup mount; alter database archivelog; alter database open; select log_mode from v$database; alter database add supplemental log data; alter system set recyclebin=off scope=spfile; purge dba_recyclebin;In this case I've enabled acrhivelog mode for database, added additional information into log files and turned off the recyclebin feature. Next, create a schema that will contain the Oracle GoldenGate DDL objects (this step is not necessary, because the DDL replication doesn't work in heterogenous environment).
create tablespace gg_tbls datafile '/u01/app/oracle/oradata/orcl/gg_tbls.dbf' size 100m reuse autoextend on; create user ggate identified by oracle default tablespace gg_tbls quota unlimited on gg_tbls; grant create session, connect, resource to ggate; grant dba to ggate; -- just in case grant execute on utl_file to ggate;Next, run the following scripts:
SQL> @marker_setup.sql SQL> @ddl_setup.sql SQL> @role_setup.sql SQL> grant GGS_GGSUSER_ROLE to ggate; SQL> @ddl_enable.sql3. Create the schemes and tables for replication on both hosts (db, tt1).
Create the scheme and the table for replication (db host):
create user source identified by oracle default tablespace users temporary tablespace temp; grant connect,resource,unlimited tablespace to source; connect source/oracle create table t1 (id number primary key, name varchar2(50));Next, prepare the target database (host tt1) for replication. Create a following DSN (Don't forget that database character set should be the same on both hosts):
[gg_rep] Driver=/u01/app/oracle/product/11.2.2.4.1/TimesTen/tt1122/lib/libtten.so DataStore=/u01/app/oracle/product/datastore/gg_rep PermSize=80 TempSize=32 PLSQL=1 DatabaseCharacterSet=WE8MSWIN1252 ... [gg_rep_cl] TTC_SERVER=tt1 TTC_SERVER_DSN=gg_repI've created the two DSN, because Golden Gate uses a client\server connection to TimesTen. Next, create a user and table for replication.
[oracle@tt1 gg]$ ttisql gg_rep create user target identified by oracle; grant connect, create table to target; grant admin to target; connect "DSN=gg_rep;UID=target;PWD=oracle"; create table t1 (id number primary key, name varchar2(50));4. Golden Gate configuration
4.1. Source host configuration
Add information about your Oracle GoldenGate DDL scheme into file GLOBALS.
[oracle@db gg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (db.us.oracle.com) 1> EDIT PARAMS ./GLOBALS GGSCHEMA ggate GGSCI (db.us.oracle.com) 2>Start manager process.
GGSCI (db.us.oracle.com) 2> edit params mgr PORT 7809 GGSCI (db.us.oracle.com) 3> start manager Manager started. GGSCI (db.us.oracle.com) 4> info mgr Manager is running (IP port db.us.oracle.com.7809). GGSCI (db.us.oracle.com) 5>Login into database and add additional information about primary keys into log files and create extract.
dblogin userid ggate password oracle add schematrandata source add extract ext1, tranlog, begin now add exttrail /u01/app/oracle/product/gg/dirdat/tr, extract ext1Edit file ext1 like the following:
GGSCI (db.us.oracle.com) 6> edit params ext1 extract ext1 userid ggate, password oracle exttrail /u01/app/oracle/product/gg/dirdat/tr, format release 11.1 table source.t1;I added the parameter "format release 11.1", because I use 11.1.1 Golden Gate version on target. Then, add one more extract.
add extract pump1, exttrailsource /u01/app/oracle/product/gg/dirdat/tr , begin now add rmttrail /u01/app/oracle/product/gg/dirdat/tr, extract pump1Edit file pump1 like the following:
GGSCI (db.us.oracle.com) 7> edit params pump1 EXTRACT pump1 USERID ggate, PASSWORD oracle RMTHOST tt1, MGRPORT 7809 RMTTRAIL /u01/app/oracle/product/gg/dirdat/tr, format release 11.1 PASSTHRU table source.t1;After that run all extracts.
start extract ext1 start extract pump1 info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:00:00 00:00:28 EXTRACT RUNNING PUMP1 00:00:00 00:00:034.2. Creating DEF file on source host.
I have to create a DEF file for replicated object for avoiding the following error:
2013-02-20 12:27:14 ERROR OGG-01163 Oracle GoldenGate Delivery for TimesTen, rep1.prm: Bad column length (8) specified for column ID in table SOURCE.T1, maximum allowable length is 3. 2013-02-20 12:27:14 ERROR OGG-01668 Oracle GoldenGate Delivery for TimesTen, rep1.prm: PROCESS ABENDING.So, let's create DEF file. The parameter file is the following (./dirprm/defgen.prm):
defsfile ./dirdef/tt.defs userid ggate password oracle table source.t1;Then, run defgen utility.
./defgen paramfile ./dirprm/defgen.prmBUT, this method doesn't work in my case (I use different Golden Gate versions). Details in support.oracle.com (BUG:13999007 - SOURCE DEF FILE INCORRECT, CAUSE REPLICAT READ INCORRECT). Solution is - using NOEXTATTR parameter (Replicat abend with ERROR OGG-00303 Problem at line xx. Expecting file, table, or record definition. [ID 1455370.1]). In my case I have to create a DEF file by the following command:
./defgen paramfile ./dirprm/defgen.prm NOEXTATTRIn this case DEF file will have a suitable format for 11.1.1 version. Copy DEF file (/dirdef/tt.defs) to tt1 host.
4.3. Target host configuration
Change the GLOBAL file.
GGSCI (tt1) 1> EDIT PARAMS ./GLOBALS CHECKPOINTTABLE target.ggchkptStart manager process.
GGSCI (tt1) 2> edit params mgr PORT 7809 GGSCI (tt1) 3> start manager Manager started. GGSCI (tt1) 4> info mgr Manager is running (IP port tt1.7809). GGSCI (tt1) 5>Create the checkpoint table and add replicat.
dblogin SOURCEDB gg_rep_cl userid target password oracle add checkpointtable target.ggchkpt add replicat rep1, exttrail /u01/app/oracle/product/gg/dirdat/tr, begin nowEdit file rep1 and start replicat.
GGSCI (tt1) 5> edit params rep1 REPLICAT rep1 SOURCEDEFS /u01/app/oracle/product/gg/dirdef/tt.defs TARGETDB gg_rep_cl, USERID target, PASSWORD oracle discardfile /u01/app/oracle/product/gg/discard/rep1_discard.txt, append, megabytes 10 map source.t1, target target.t1; GGSCI (tt1) 6> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (tt1) 7> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:035. Checking replication
DB host.
[oracle@db ggate]$ sqlplus source/oracle SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 25 15:25:38 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production With the Partitioning and Real Application Testing options SQL> select * from t1; no rows selected SQL>TimesTen.
[oracle@tt1 gg]$ ttisql "DSN=gg_rep;UID=target;PWD=oracle" Copyright (c) 1996-2011, Oracle. All rights reserved. Type ? or "help" for help, type "exit" to quit ttIsql. connect "DSN=gg_rep;UID=target;PWD=oracle"; Connection successful: DSN=gg_rep;UID=target;DataStore=/u01/app/oracle/product/datastore/gg_rep;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/u01/app/oracle/product/11.2.2.4.1/TimesTen/tt1122/lib/libtten.so;PermSize=80;TempSize=32;TypeMode=0; (Default setting AutoCommit=1) Command> select * from t1; 0 rows found. Command>Insert a row into t1 table.
SQL> insert into t1 values (1,'test');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID NAME
---------- --------------------------------------------------
1 test
SQL>
Check TimesTen.
Command> select * from t1; < 1, test > 1 row found. Command>Now, update the row.
SQL> update t1 set name='test3' where id = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID NAME
---------- --------------------------------------------------
1 test3
SQL>
Check TimesTen.
Command> select * from t1; < 1, test3 > 1 row found. Command>Replication works fine.
