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.
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
################################################
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
$ 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>
#############################################
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.
#################################################
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
##################################################
$ 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)
##################################################
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
##################################################
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
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
