Goldengate Replication - Sybase to Sybase Database - Step By Step

This post covers Oracle Goldengate data replication between two sybase databases.

Installation is not covered in this post as it is a simple process of just extracting the
.tar file.

#### Setting Environment Variables on Sybase Server ####

go to gg install home

cd /GGS/home

$ vi .ggsprof and add following two entries

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/GGS/home

export PATH=$PATH:/GGS/home

$ chmod 775 .ggsprof

################################################

#### Preparing Sybase Database for GG Capture ####

isql -U dbadmin -P frodo -- logging into isql

>sp_helpdb
>Go

-- will list all databases

>use rknet -- switching to database we want to use
>Go


-- setting secondary truncation point in sybase logs

1> dbcc settrunc ('ltm','valid')
2> Go
 ltm_truncpage ltm_trunc_state db_rep_stat gen_id      dbid
         dbname                         lti_version
 ------------- --------------- ----------- ----------- ------
         ------------------------------ -----------
       2746034               1           1           0      9
         rknet              

#### Logging into db from ggsci prompt ####

$ cd /GGS/home
$ ggsci

GGSCI (kondorapp) 1> dblogin sourcedb rknet, USERID dbadmin
Password:

2015-11-20 12:41:30  INFO    OGG-04508  Oracle GoldenGate is searching the Sybas                                                             e CS library version that is to be used. Please ignore any error that is reporte                                                             d by Sybase as a part of this search process.


The context allocation routine failed.

The following problem caused the failure:

Invalid context version.


The context allocation routine failed.

The following problem caused the failure:

Invalid context version.

2015-11-20 12:41:30  INFO    OGG-04509  Oracle GoldenGate is using the Sybase CS                                                              library version: [15001].

2015-11-20 12:41:30  INFO    OGG-04501  Charset information: Client charset: [is                                                             o_1], Database/Server charset: [iso_1], CHARSETCONVERT: [OFF].
Successfully logged into database. --- check this and ignore all errors above

GGSCI (kondorapp as dbadmin@KONDOR) 2>

#############################################

#### Adding Trandata on Table ####

NOTE - UNLIKE ORACLE, ADDING TRANDATA IN SYBASE CAN CAUSE DEADLOCKS SO EXECUTE IT DURING LEAN HOURS.

GGSCI (kondorapp as dbadmin@KONDOR) 2> add trandata dbo.ScheduleLog

Transaction logging enabled for table dbo.ScheduleLog.

GGSCI (kondorapp as dbadmin@KONDOR) 3> info trandata dbo.ScheduleLog

Transaction logging enabled for table dbo.ScheduleLog.

#################################################

#### Configuring Extract ####

ggsci > edit params ext1
EXTRACT ext1
SOURCEDB rknet, userid dbadmin, password frodo
EXTTRAIL ./dirdat/ET
TABLE dbo.ScheduleLog;

ggsci> add extract ext1, tranlog, begin now

ggsci> add exttrail ./dirdat/ET, extract ext1, megabytes 1024

ggsci> start ext1

##################################################

###### SYBASE DATABASE OPERATIONS #######

$ isql -U dbadmin -P frodo

>use rknet -- switching to database we want to use
>Go


--taking backup of schedulelog table

1> select * into ScheduleLog_backup from ScheduleLog
2> Go
(2887526 rows affected)
1> commit
2> Go


--creating empty table from schedulelog


1> select * into ScheduleLogRep from ScheduleLog where 1=2
2> Go
(0 rows affected)
1> select * from ScheduleLogRep
2> Go
 DealLogId                  SettlementLeg FlowDate
         FlowAmount                  FlowCustom                  ZeroIfNetting
         SettlCoeff                  CLS
 -------------------------- ------------- --------------------------
         --------------------------- --------------------------- -------------
         --------------------------- ------

(0 rows affected)
##################################################

###### CONFIGURING REPLICAT ON SAME SERVER SAME DB #######

NOTE - REPLICATE WILL READ TRAIL FILE PRODUCED BY EXTRACT, SINCE BOTH EXT AND REP ARE ON SAME
SERVER, THERE IS NO USE OF A DATA PUMP EXTRACT.

REPLICATION IS FROM DBO.SCHEDULELOG TO DBO.SCHEDULELOGREP IN THE SAME DATABASE RKNET


ggsci> edit params rep1

REPLICAT REP1
TARGETDB rknet, userid dbadmin, password frodo
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rep1.dsc, purge
MAP dbo.ScheduleLog, TARGET dbo.ScheduleLogRep;

ggsci> add replicat rep1, exttrail ./dirdat/ET

ggsci> start rep 1

##################################################

#### Populating Data and Checking Status of Replication ####

SELF INSERT INTO THE SOURCE TABLE IS CARRIED OUT.

1> insert into ScheduleLog select * from ScheduleLog
2> Go
(2887526 rows affected)
1> commit
2> Go
1> select count(1) from dbo.ScheduleLogRep
2> Go

 -----------
     2887526



#### Checking Status of Replicat Process ####

Sending STATS request to REPLICAT REP1 ...

Start of Statistics at 2015-11-20 14:32:07.

Replicating from dbo.ScheduleLog to dbo.ScheduleLogRep:

*** Total statistics since 2015-11-20 14:17:08 ***
        Total inserts                                2887526.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                             2887526.00



##################################################


Note -
There are lot of limitations w.r.t Sybase database. e.g. encrypted data types, lobs & DDL Replication. Do check out the install guide for the same here
https://docs.oracle.com/cd/E18101_01/doc.1111/e17806.pdf