Setting up a One-way Replication from Sybase to Oracle

In my previous blog, Installing Oracle GoldenGate for Sybase, I discussed how to install Oracle GoldenGate for Sybase. In this blog, I will continue the topic on how to set up a one-way replication from Sybase ASE database to Oracle Database. Let's start from checking the installations of Oracle GoldenGate.

Step 1: Start the GGSCI on Source and Target

I assume that you already know how to install Oracle GoldenGate for Oracle Database. Now, we have one Oracle GoldenGate for Sybase setup for Sybase ASE (source) and one Oracle GoldenGate for Oracle setup for Oracle Database.

Source

Target

Oracle GoldenGate Command Interpreter for Sybase 
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x86, 32bit (optimized), Sybase 15_5 on Apr 23 2012 05:45:52
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
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 10g on Apr 23 2012 07:06:02
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.  

As shown the preceding table, both instances are in Oracle GoldenGate version 11.2.1.0.1.

Tip: Should I use the same version of Oracle GoldenGate on the Source and Target?
Yes, it is recommended to use the same version on both the source and target. However, If the Oracle GoldenGate versions on the source and target database are different, it is a good practice to use the RELEASE option of the EXTFILE/RMTFILE and EXTTRAIL/RMTTRAIL parameter, especially if you have a lower version of Oracle GoldenGate on the target.

Example: EXTFILE  ./dirdat/em, FORMAT RELEASE 10.4

We then need to modify and check the Oracle GoldenGate Manager parameter file to prepare the replication. The following table shows the Oracle GoldenGate Manager setup on the source and target system:

Source

Target

export DSQUERY=ASE1 
GGSCI> edit param mgr 

port 15002 
purgeoldextracts ./dirdat/em*, usecheckpoints, minkeephours 2


GGSCI> start mgr
GGSCI> info mgr
Manager is running (IP port dislab.us.oracle.com.15002).

GGSCI> edit param mgr 

port 15021
dynamicportlist 15011-15020
dynamicportreassigndelay 5


GGSCI> start mgr
GGSCI> info mgr
Manager is running (IP port dislab.us.oracle.com.15021).

As discussed in the previous blog, on the Sybase side, you can setup the DSQUERY environment variable to avoid specify Sybase ASE server name in the dblogin command.

Step 2: Set up the Source and Target Database

In this example, we create a table REGIONS in both the source and target database, and insert some sample data. The steps are shown as follows: 

Source (Sybase ASE 15.5)

Target (Oracle Database 11.2.0.1)

isql -Ususer -Psybaseuser -SASE1
>use sydatabase
>go

connect sybase_user/sybase_user

CREATE TABLE regions(
region_id NUMERIC (6,0)
CONSTRAINT region_id NOT NULL,
region_name VARCHAR(25))
CREATE UNIQUE INDEX reg_id_pk ON regions (region_id)
ALTER TABLE regions ADD CONSTRAINT reg_id_pk PRIMARY KEY (region_id)

create table regions(
region_id number not null primary key,
region_name varchar2(25));

INSERT INTO regions VALUES (1, 'Europe')
INSERT INTO regions VALUES ( 2, 'Americas')
INSERT INTO regions VALUES ( 3, 'Asia')
INSERT INTO regions VALUES ( 4, 'Middle East and Africa')

INSERT INTO regions VALUES (1, 'Europe');
INSERT INTO regions VALUES ( 2, 'Americas');
INSERT INTO regions VALUES ( 3, 'Asia');
INSERT INTO regions VALUES ( 4, 'Middle East and Africa');

> select count(1) from regions
> go
-----------
4

SQL> select count(1) from regions;
COUNT(1)
----------
         4

Step 3: Set up the Secondary Truncation Point in Sybase Transaction Log

When using Oracle GoldenGate for Sybase, you need to setup the secondary truncation point. This is because Oracle GoldenGate uses the secondary truncation point to identify data that remains to be processed.

To set up the secondary truncation point, you need to login Sybase database with a user who has sa_role privilege and run the “dbcc settrunc(‘ltm’,valid)” command. The detailed steps are shown as follows:

Source

Shell> isql -Uogguser -Pogguser -SASE1

1> use sydatabase

2> go

1> dbcc settrunc('ltm', 'valid')

2> go

ltm_truncpage ltm_trunc_state db_rep_stat gen_id dbid dbname lti_version
------------- --------------- ----------- ----------- ------ ------------ -----------
      524307                1           1           0      5   sydatabase           0

Step 4: Prepare the Source Database and Target Database

Connect to the source DB and add trandata. In addition, confirm that Oracle GoldenGate user can access on the target database shown as follows:

Source                                                                                                         

Target

GGSCI >dblogin sourcedb sydatabase@ASE1 userid ogguser password ogguser
GGSCI > list table suser.*
suser.regions
Found 1 tables matching list criteria.
GGSCI > info trandata suser.regions
Transaction logging disabled for table suser.regions.
GGSCI > add trandata suser.regions
Transaction logging enabled for table suser.regions.

GGSCI > dblogin userid orclgg, password Oracle
Successfully logged into database.
GGSCI > list table sybase_user.*
SYBASE_USER.REGIONS
Found 1 tables matching list criteria.

Why I get the "ERROR: Cannot use database syd_database. error, no additional information available." error?
Answer: The problem is the parsing issue in Oracle GoldenGate 11.2.1 version if the database has special characters such as “_” in the database and schema name. The current solution is to remove the special characters.

Because we are working on heterogeneous database replication, you have to create the database metadata definition file on the source system and copy the generated definition file to the target.

Source                                                                   

Target

GGSCI>edit param defgen

defsfile./dirout/regions.def
sourcedb sydatabase@ASE1 userid ogguser PASSWORD ogguser
TABLE suser.regions;

GGSCI> shell ./defgen paramfile dirprm/defgen.prm

Copy the generated ./dirout/regions.def on the source to the target.

Why do I get the "OGG-05500  Detected database metadata mismatch" error?

Answer: Are you using sourecedefs? When the source and target table structure are different, especially when databases are not in the same type, the metadata is different, we must use sourcedefs.

Step 5: Create the Extract (Capture) and Replicat (Delivery)

The setup is shown as follows: 

Source 

Target 

emaster.prm:

extract emastr
exttrail ./dirdat/em
sourcedb sydatabase@ASE1 userid ogguser password ogguser
statoptions resetreportstats
report at 00:01
reportcount every 60 seconds, rate
table suser.regions;     

 rmaster.prm:

replicat rmastr
userid orclgg, password oracle
sourcedefs ./dirdef/regions.def
discardfile ./dirrpt/rmastr.dsc, purge
map suser.regions, target sybase_user.regions;


Adding the Extract:
GGSCI> add extract emastr, tranlog, begin now
GGSCI> add exttrail ./dirdat/em, extract emastr, megabytes 50  

Adding the Replicat:
GGSCI >edit param ./GLOBALS
CHECKPOINTTABLE orclgg.ogg_chktable
GGSCI>add checkpointtable
GGSCI>add replicat rmastr, exttrail ./dirdat/pm

pmastr.prm:

extract pmastr
rmthost localhost, mgrport 15021, compress, timeout 30
rmttrail ./dirdat/pm
passthru
table suser.regions;

Adding the Pump:
GGSCI > add extract pmastr, exttrailsource ./dirdat/em
EXTRACT added.
GGSCI > add rmttrail ./dirdat/pm, extract pmastr, megabytes 50
RMTTRAIL added.

Insert a New Record in Sybase
1>  Insert into regions values(5,'Antarctica')
2> go
(1 row affected)

SQL> select * from regions;
 REGION_ID REGION_NAME
---- ----------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Antarctica

Delete a Record in Sybase
1> delete from regions where region_id=5
2> go
(1 row affected)

SQL> select * from regions;
 REGION_ID REGION_NAME
---- ----------------------
         1 Europe
         2 Americas
        3 Asia
         4 Middle East and Africa

Now, we have completed the basic setups of a one-way replication from Sybase ASE database to Oracle Database.Feel free to let me know if you have any questions or comments on the tips I provided above.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Learn the latest trends, use cases, product updates, and customer success examples for Oracle's data integration products-- including Oracle Data Integrator, Oracle GoldenGate and Oracle Enterprise Data Quality

Search

Archives
« May 2015
SunMonTueWedThuFriSat
     
1
2
3
4
6
7
8
9
10
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
      
Today