Sybase to Oracle Golden Gate

Sybase 安装Golden Gate：

1. 下载，然后create subdirs.并且在两端配置好mgr，设置好端口7809
2. 创建golden gate用户ogguser,并且给它授权sa和replication

   [root@localhost ~]# su - sybase
   [sybase@localhost ~]$ vi .bash_profile --设置sybase路径还有DSQUERY=ASE1 
   [sybase@localhost ~]$ isql -Usa -P111111 -SASE1
   1> sp_addlogin ogguser,ogguser
   2> go
   Password correctly set.
   Account unlocked.
   New login created.
   (return status = 0)
   1> sp_adduser ogguser
   2> go
   New user added.
   (return status = 0)
   1> sp_role ‘grant‘,replication_role, ogguser
   2> go
   Authorization updated.
   (return status = 0)
   1> sp_role ‘grant‘,sa_role,ogguser
   2> go
   Authorization updated.
   (return status = 0)
   1> exit

3. 设置第二断点，必须拥有sa权限

   [sybase@localhost ggs]$ isql -Uogguser -Pogguser -SASE1
   1> use edr
   2> go
   1> dbcc settrunc(‘ltm‘,‘valid‘)
   2> go
    ltm_truncpage ltm_trunc_state db_rep_stat gen_id      dbid  
        dbname                                                      
        lti_version 
    ------------- --------------- ----------- ----------- ------
        ------------------------------------------------------------
        ----------- 
           205079               1           1           0      6
        edr                                                         
                  0 

4. 给要记录的表格加日志

   GGSCI (localhost.localdomain) 1> dblogin sourcedb edr@ASE1 userid ogguser password ogguser
   
   2015-04-22 20:46:17  INFO    OGG-04508  Oracle GoldenGate is searching the Sybase CS library version that is to be used. Please ignore any error that is reported by Sybase as a part of this search process.
   
   2015-04-22 20:46:17  INFO    OGG-04509  Oracle GoldenGate is using the Sybase CS library version: [15,701].
   
   2015-04-22 20:46:17  INFO    OGG-04501  Charset information: Client charset: [utf8], Database/Server charset: [iso_1], CHARSETCONVERT: [ON].
   Successfully logged into database.
   
   GGSCI (localhost.localdomain) 2> list table dbo.entity
   dbo.entity
   
   Found 1 tables matching list criteria.
   
   GGSCI (localhost.localdomain) 3> add trandata dbo.*

5. 生成定义文件

   GGSCI (localhost.localdomain) 1> edit param defgen
   
   defsfile ./dirout/source.def
   sourcedb edr@ASE1 userid ogguser password ogguser
   TABLE dbo.entity_address;
   TABLE dbo.entity;
   ...
   GGSCI (localhost.localdomain) 1> shell ./defgen paramfile dirprm/defgen.prm

   Definitions generated for 29 tables in ./dirout/source.def.

6. 编辑初始抽取参数：edit param eedr0

   EXTRACT eedr0
   sourcedb edr@ASE1 userid ogguser password ogguser
   RMTHOST 192.168.137.11, MGRPORT 7809
   RMTTASK replicat, GROUP pedr0
   TABLE dbo.entity_address;
   TABLE dbo.entity;
   ...

   GGSCI (localhost.localdomain) 4> edit param eedr0

   GGSCI (localhost.localdomain) 5> add extract eedr0, sourceistable

7. 编辑目标端的：edit param pedr0 并且加入pedr0： add replicat pedr0, specialrun 注意replicat不需要手动启动，只需要在source启动extract即可。

   EPLICAT pedr0
   USERID goldengate, PASSWORD goldengate
   sourcedefs ./dirdef/source.def
   handlecollisions
   discardfile ./dirrpt/pedr0.dsc, append, megabytes 10
   MAP dbo.entity_address,
   TARGET SWAT.ENTITY_ADDRESS,
   COLMAP(
   USEDEFAULTS,
   STATE_CODE = state_code, 
   CITY = city, 
   STREET1 = street1, 
   STREET2 = street2, 
   ZIP = zip, 
   IS_BAD_ADDRESS = bad_address )
   ;

8. 在source启动extract： start extract eedr0 并且查看它 info eedr0

   --在这里列举一些错误情况：
   --1
   2015-04-24 08:59:10  WARNING OGG-01194  EXTRACT task PT0 abended : Bad column length (8) specified for column d in table dbo.t, maximum allowable length is 3.
   --这个是源和目标端的列类型不一样，一个是字符，一个是数值，使用了方程@NUMSTR来转换，仍然报错。
   --2
   2015-04-24 08:27:56  WARNING OGG-01194  EXTRACT task PEDR0 abended : There is no trail to reposition to when doing direct load task。
   --这个一般是数字冲突造成的错误，两个情况，一个是类的数值类型不一样，没有用方程转换是报的错，用handlecollisions不管用，用方程转换以后，报另外一个错。另一个情况是主键冲突，有相同的主键值，handlecollisions。
   --3
   --当只有部分列参与mapping的时候，源和目标端必须有主键，如果全部列加入，那么主键会极大提高效率，如果没有主键，goldengate就会将阵列当成主键，开销巨大。
   --4
   --主键的列必须参加mapping。