Oracle GoldenGate19c & Oracle GoldenGate Veridata12c 实验（二）-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_42145355/article/details/114112294

本文详细介绍了如何部署Oracle GoldenGate Veridata 19c，包括安装JDK、WebLogic Server，配置源与目标端的server和agent。在模拟验证中，通过OGG同步测试了Blob、Clob、Long和XML数据类型的同步，并针对同步问题使用Veridata修复工具进行数据一致性修复。然而，实验中发现针对Long字段的同步存在限制，导致同步丢失，需要进一步排查可能的原因。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、部署Oracle GlodenGate Veridata软件

Oracle GoldenGate Veridata不需要安装Oracle
GoldenGate复制软件。如果您确实使用该软件，请在其他位置安装Oracle GoldenGate Veridata。
在为您的操作系统安装经过认证的JDK之后，请使用Oracle Fusion Middleware
12.2.1.4.0基础结构安装程序来安装Oracle WebLogic Server 12.2.1.4.0和Oracle Java必需文件（JRF）基础结构服务。

1.1先决条件

在这里插入图片描述

JDK版本更新

[oracle@centos7 bin]$ java -version
java version "1.8.0_281"
Java(TM) SE Runtime Environment (build 1.8.0_281-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.281-b09, mixed mode)

Oracle Fusion Middleware 基础架构安装
需要安装同版本的融合中间件的基础架构
需要在源上安装veridata agent以及veridata server，所以需要准备以下两个文件

[root@centos7 Oracle]# pwd
/data/Oracle
[root@centos7 Oracle]# ls -l
total 1886948
-rw-r--r-- 1 weblogic weblogic 1635717400 Feb  2  2021 fmw_12.2.1.4.0_infrastructure_Disk1_1of1.zip
-rwxrwxrwx 1 weblogic weblogic  296507706 Jan 27 20:24 fmw_12.2.1.4.0_ogg_Disk1_1of1.zip

设置ORACLE_HOME=/data/Oracle,使用weblogic用户安装




useradd -g weblogic -G oinstall weblogic
[root@centos7 Oracle]# su - weblogic
[weblogic@centos7 ~]$ export DISPLAY=192.168.3.1:0.0
[weblogic@centos7 ~]$ cd /data/Oracle/
[weblogic@centos7 Oracle]$ java -jar fmw_12.2.1.4.0_infrastructure.jar

在这里插入图片描述

3.2在源端上安装veridata server和agent

java -jar fmw_12.2.1.4.0_ogg.jar

在这里插入图片描述

在目标端上安装Oracle GoldenGate Veridata Agent

方法类似，不需要额外安装中间件基础设置
安装时选择Oracle GoldenGate veridata agent

配置源与目标端agent

[weblogic@centos7 agent]$ pwd
/data/Oracle/Oracle-Home/veridata/agent
[weblogic@centos7 agent]$ ./agent_config.sh /data/Oracle/veridata_1 true

[weblogic@centos7 veridata_1]$ cd /data/Oracle/veridata_1/
[weblogic@centos7 veridata_1]$ cp agent.properties.sample agent.properties
vi agent.properties
server.port=7890
database.url=jdbc:oracle:thin:@192.168.3.103:1521:pri

server.driversLocation=drivers
server.jdbcDriver=ojdbc8.jar
pool.maxSize=20

[weblogic@centos7 veridata_1]$ ./agent.sh start
[weblogic@centos7 veridata_1]$ ./agent.sh reloadLog
[VERIAGT-BOOT] INFO Looking for home directory.
[VERIAGT-BOOT] INFO Found bootstrap class in file:/data/Oracle/Oracle-Home/veridata/agent/JavaAgent.jar!/com/goldengate/veridata/agent/Bootstrap.class.
[VERIAGT-BOOT] INFO Home directory: /data/Oracle/Oracle-Home/veridata/agent
[VERIAGT-BOOT] INFO Preparing classpath.
[VERIAGT-BOOT] INFO Driver location: /data/Oracle/veridata_1/drivers
[VERIAGT-BOOT] INFO Drivers to load: ojdbc6.jar
[VERIAGT-BOOT] INFO Classpath:
[VERIAGT-BOOT] INFO     /data/Oracle/Oracle-Home/veridata/agent/JavaAgent.jar
[VERIAGT-BOOT] INFO     /data/Oracle/veridata_1/drivers/ojdbc6.jar
[VERIAGT-BOOT] INFO     /data/Oracle/Oracle-Home/veridata/agent/agent-manifest.jar
[VERIAGT-BOOT] INFO Initializing.
[VERIAGT-BOOT] INFO Invoking startup method: public static void com.goldengate.veridata.agent.VeridataAgent.main(java.lang.String[])
2021-01-31 10:21:26.495 NOTIFICATION OGGV-60150 Configuring logging.
2021-01-31 10:21:26.605 NOTIFICATION OGGV-60151 Config file for logger is /data/Oracle/veridata_1/config/odl.xml
2021-01-31 10:21:27.027 NOTIFICATION OGGV-60162 Reset log request complete: OK
[weblogic@centos7 veridata_1]$ ps -ef|grep agent
weblogic 17709     1  8 10:21 pts/1    00:00:01 /usr/java/jdk1.8.0_281-amd64/bin/java -Djava.util.logging.config.class=oracle.core.ojdl.logging.LoggingConfiguration -Doracle.core.ojdl.logging.config.file=/data/Oracle/veridata_1/config/odl.xml -Dhome=/data/Oracle/Oracle-Home/veridata/agent -DagentHome=/data/Oracle/veridata_1 -XX:+UseParallelGC -Xmx1024M -Xms1024M -Dagent-manifest.jar=/data/Oracle/Oracle-Home/veridata/agent/agent-manifest.jar -jar /data/Oracle/Oracle-Home/veridata/agent/JavaAgent.jar
weblogic 17767 16599  0 10:21 pts/1    00:00:00 grep --color=auto agent

目标端同样设置

3.3配置资料档案库

veridata server端创建

[weblogic@centos7 veridata_1]$ cd /data/Oracle/Oracle-Home/oracle_common/bin
[weblogic@centos7 bin]$ ./rcu

在这里插入图片描述

3.3 配置Weblogic域

[weblogic@centos7 ~]$ cd /data/Oracle/Oracle-Home/wlserver/common/bin/
[weblogic@centos7 bin]$ ./config.sh

在这里插入图片描述

启动Weblogic控制台

[weblogic@centos7 veridata_domain]$ pwd
/data/Oracle/Oracle-Home/user_projects/domains/veridata_domain
[weblogic@centos7 veridata_domain]$ sh startWebLogic.sh 

https://192.168.3.103:7001/console

在这里插入图片描述

3.4使用Oracle GoldenGate Veridata WebUI

启动veridataserver（源端）

#调整启动参数
[weblogic@centos7 bin]$ pwd
/data/Oracle/Oracle-Home/user_projects/domains/veridata_domain/bin
[weblogic@centos7 bin]$ vi setStartupEnv.sh 

JAVA_OPTIONS="${JAVA_OPTIONS} -Djava.protocol.handler.pkgs=oracle.mds.net.protocol"   
改为
JAVA_OPTIONS="{JAVA_OPTIONS} –Djava.protocol.handler.pkgs=oracle.mds.net.protocol -Djava.awt.headless=true"

 SERVER_MEM_ARGS_64HotSpot="-Xms256m -Xmx1024m"
 改为
 SERVER_MEM_ARGS_64HotSpot="-Xms1024m –Xmx2048m"
 
 
 [weblogic@centos7 bin]$ pwd
/data/Oracle/Oracle-Home/user_projects/domains/veridata_domain/bin
[weblogic@centos7 bin]$ vi startManagedWebLogic.sh

填上weblogic用户名以及密码

 WLS_USER="weblogic"
export WLS_USER

WLS_PW="weblogic123"
export WLS_PW

#启动veridataserver

[weblogic@centos7 bin]$ pwd
/data/Oracle/Oracle-Home/user_projects/domains/veridata_domain/veridata/bin
[weblogic@centos7 bin]$ ./veridataServer.sh start &

在这里插入图片描述

在源端和目标端创建相关用户以及表空间

在源库创建一个表空间ggs和用户ggs，

create tablespace ggs datafile size 50m autoextend on;
create user ggs identified by ggs default tablespace ggs ;

grant connect,resource to ggs;
grant select any table to ggs;

#目标库创建
create tablespace ggs datafile size 500m autoextend on;
create user ggs identified by ggs default tablespace ggs;

grant connect,resource to ggs;
grant selectany table to ggs;
grant update any table to ggs;
grant insert any table to ggs;
grant delete any table to ggs;
grant alter any table to ggs;

登陆veridata服务控制台

https://192.168.3.103:8830/veridata
输入用户名口令
veridataadmin/veridataadmin123

在这里插入图片描述

3.4使用Oracle GoldenGate Veridata Job

点击主页Job configuration 新建Job

二、模拟验证

2.1模拟OGG同步对Blob，Clob，Long，XML的同步成功

保证源与目标端ogg进程正常运行

#源
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXP1        00:00:10      00:00:00    
EXTRACT     RUNNING     PMP1        00:00:00      00:00:10   

#目标

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP1        00:00:00      00:00:03

插入前各表数据量

SQL> select count(*) from TAB_BLOB;

  COUNT(*)
----------
         5

SQL> select count(*) from tab_clob;

  COUNT(*)
----------
        82

SQL> select count(*) from tab_long;

  COUNT(*)
----------
        31

SQL> select count(*) from tab_xml;

  COUNT(*)
----------
        19

准备源端表的插入数据脚本

#-*- coding:utf-8 -*-
import cx_Oracle
import os
import sys
os.environ['NLS_LANG']='SIMPLIFIED CHINESE_CHINA.UTF8'





def IstTab(conn):
    cursor = conn.cursor()
    #将本地图片插入TAB_BLOB
    path = 'E:\\人社厅硬件交接\\巡检\\巡检报告\\7月2日巡检\\5500v3\\'
    for file in os.listdir(path):
        name = file
        content = open(path + file,'rb')
        content = content.read()
        sqlstr = "insert into tab_blob(name,content) values('%s',:blobData)" %(file)
        cursor.setinputsizes(blobData=cx_Oracle.BLOB)
        cursor.execute(sqlstr,{'blobData':content})
        cursor.execute('commit')
    #将本地文本文件插入TAB_CLOB
    path1 = 'E:\工作\巡检\\'
    for file in os.listdir(path1):
        name = file
        content = open(path1 + name, 'rb')
        content = content.read()
        sqlstr = "insert into tab_clob(name,content) values('%s',:clobData)" % (file)
        cursor.setinputsizes(clobData=cx_Oracle.CLOB)
        cursor.execute(sqlstr, {'clobData': content})
        cursor.execute('commit')

    #插入long对象类型至TAB_LONG
    for i in range(32, 50):
        id = i + 1
        content = 'This_is_Long_Object.' * 1000 * i
        sqlstr = "insert into tab_LONG(name,content) values('%s',:LongData)" % (id)
        cursor.setinputsizes(LongData=cx_Oracle.LONG_STRING)
        cursor.execute(sqlstr, {'LongData': content})
        cursor.execute('commit')
    #插入Xml对象到表TAB_XML
    for i in range(20, 30):
        i = i + 1
        con = "<h1>this_is_the_xml_type</h1>"
        sqlstr = '''insert into tab_xml(id,content) values('%s',sys.xmlType.createXML('%s'))''' % (i, con)
        cursor.execute(sqlstr)
        cursor.execute('commit')
    cursor.close()


def main():
    conn = cx_Oracle.connect('scott', 'scott', '192.168.3.103:1521/pri')
    print('已连接到数据库！')
    IstTab(conn)
    print('已插入相关数据！')
    conn.close()

if __name__ == '__main__':
    main()

在这里插入图片描述

再次查看数据量

SQL> select count(*) from TAB_BLOB;

  COUNT(*)
----------
        10

SQL> select count(*) from tab_clob;

  COUNT(*)
----------
       101

SQL> select count(*) from tab_long;

  COUNT(*)
----------
        49

SQL> select count(*) from tab_xml;

  COUNT(*)
----------
        29

查看ogg状态

#源
MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXP1        00:00:09      00:00:03    
EXTRACT     RUNNING     PMP1        00:00:00      00:00:07  

Extracting from SCOTT.TAB_BLOB to SCOTT.TAB_BLOB:
    *** Latest statistics since 2021-01-31 16:01:15 ***
        Total inserts                                      5.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00
        
Extracting from SCOTT.TAB_CLOB to SCOTT.TAB_CLOB:
*** Latest statistics since 2021-01-31 16:01:15 ***
        Total inserts                                     19.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                  19.00    
Extracting from SCOTT.TAB_LONG to SCOTT.TAB_LONG:
 *** Latest statistics since 2021-01-31 16:01:15 ***
        Total inserts                                     18.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                  18.00
Extracting from SCOTT.TAB_XML to SCOTT.TAB_XML:
    
 
*** Latest statistics since 2021-01-31 16:01:15 ***
        Total inserts                                     10.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                  10.00
        
#目标
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP1        00:00:00      00:00:04 

 Replicating from SCOTT.TAB_BLOB to TIM.TAB_BLOB:

*** Total statistics since 2021-02-01 00:35:00 ***
        Total inserts                                      5.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00                  

 Replicating from SCOTT.TAB_CLOB to TIM.TAB_CLOB:

*** Total statistics since 2021-02-01 00:35:00 ***
        Total inserts                                     19.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                  19.00   
        
Replicating from SCOTT.TAB_LONG to TIM.TAB_LONG:

*** Total statistics since 2021-02-01 00:35:00 ***
        Total inserts                                     18.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                  18.00
Replicating from SCOTT.TAB_XML to TIM.TAB_XML:

*** Total statistics since 2021-02-01 00:35:00 ***
        Total inserts                                     10.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                  10.00

源与目标都获得了对以上lob表的同步支持

再次运行Job，查看数据比对结果

可以发现同步过来的name列正常，但是content列为long，最大只能同步16372字节

发现对tab_long表的同步出现问题

查看ogg目标端日志发现如下报错
2021-02-01T00:36:08.256+0800  WARNING OGG-02544  Oracle GoldenGate Delivery for Oracle, rep1.prm:  Unhandled error 
(ORA-00936: missing expression) while processing the record at SEQNO 8, RBA 6468867 in Integrated mode. 
REPLICAT will retry in Direct mode.

查看mos，可能是由于ogg用户缺失相关权限(Doc ID 1940722.1)

Grant below permission to ogg user to resolve the issue:
 SQL> execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('OGG');
The error can also happen if the Replicat db user doesn't have execute privilege on dbms_reputil2 and dbms_lob
SQL> select * From dba_tab_privs where table_name in ('DBMS_REPUTIL2', 'DBMS_LOB');
The missing privileges can be granted as following
grant execute on sys.dbms_lob to "OGG"

4.2使用Oracle GoldenGate veridata修复不一致数据

正好可以使用veridata工具对失败的部分进行修复，点击报告中的run repaire

修复正常了，再进行一次插入同步测试以及数据一致性检查

三、遗留问题

还是出现了同步丢失情况，源端long列字节数逐渐递增，但是目标端long列字节数只有固定的16372字节，可能源于某种限制，需要后续排查

查看trial文件，还是记录了大量的事务信息，包括long列的值和长度

Logdump 10 >open ./dirdat/d1000000009
Current LogTrail is /data/soft/ogg/dirdat/d1000000009 
Logdump 11 >pos 4949383
Reading forward from RBA 4949383 
Logdump 12 >n

2021/02/01 01:33:58.116.013 Insert               Len    10 RBA 4949383 
Name: SCOTT.TAB_LONG  (TDR Index: 3) 
After  Image:                                             Partition x0c   G  b   
 0000 0600 0000 0200 3535                          | ........55  
   
Logdump 13 >n

2021/02/01 01:33:58.116.013 LargeObject          Len  8020 RBA 4949504 
Name: SCOTT.TAB_LONG  (TDR Index: 3) 
After  Image:                                             Partition x0c      m   
 0000 0001 0000 0087 0000 0001 0000 0000 0000 1f40 | ...................@  
 5468 6973 5f69 735f 4c6f 6e67 5f4f 626a 6563 742e | This_is_Long_Object.  
 5468 6973 5f69 735f 4c6f 6e67 5f4f 626a 6563 742e | This_is_Long_Object.  
 5468 6973 5f69 735f 4c6f 6e67 5f4f 626a 6563 742e | This_is_Long_Object.  
 5468 6973 5f69 735f 4c6f 6e67 5f4f 626a 6563 742e | This_is_Long_Object.  
 5468 6973 5f69 735f 4c6f 6e67 5f4f 626a 6563 742e | This_is_Long_Object.  
 5468 6973 5f69 735f 4c6f 6e67 5f4f 626a 6563 742e | This_is_Long_Object.  
   
Logdump 14 >n

2021/02/01 01:33:58.116.013 LargeObject          Len  8020 RBA 4957576 
Name: SCOTT.TAB_LONG  (TDR Index: 3) 
After  Image:                                             Partition x0c      m   
 0000 0001 0000 0087 0000 0002 0000 1f40 0000 1f40 | ...............@...@  
 5468 6973 5f69 735f 4c6f 6e67 5f4f 626a 6563 742e | This_is_Long_Object.  
 5468 6973 5f69 735f 4c6f 6e67 5f4f 626a 6563 742e | This_is_Long_Object.  
 5468 6973 5f69 735f 4c6f 6e67 5f4f 626a 6563 742e | This_is_Long_Object.  
 5468 6973 5f69 735f 4c6f 6e67 5f4f 626a 6563 742e | This_is_Long_Object.  
 5468 6973 5f69 735f 4c6f 6e67 5f4f 626a 6563 742e | This_is_Long_Object.  
 5468 6973 5f69 735f 4c6f 6e67 5f4f 626a 6563 742e | This_is_Long_Object.  
   
Logdump 15 >n

2021/02/01 01:33:58.116.013 LargeObject          Len  8020 RBA 4965648

查看数据也有相关的报错信息

Mon Feb 01 01:34:07 2021
Streams APPLY OGG$REP1 encountered user error ORA-936 during eager apply: ORA-00936: missing expression

可能由于缺失相应补丁？