Oracle DB to Oracle TimesTen replication using Golden Gate

I wrote a post about Oracle - Oracle replication by using Golden Gate last year, therefore I decided to continue write about Golden Gate technology and at this time, I would like to describe Oracle to TimesTen replication.

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

In this example I use complicated example, because I'm setting a replication between different databases (Oracle DB and TimesTen), different platforms (x86 and x86_64) and different golden gate versions (11.2.1 and 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.sql

3. 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_rep

I'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 ext1

Edit 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 pump1

Edit 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:03

4.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.prm

BUT, 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 NOEXTATTR

In 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.ggchkpt

Start 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 now

Edit 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:03

5. 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.