OGG11g同构GoldenGateORACLE异构GoldenGateMYSQL同步配置及错误解析.docx
- 文档编号:5383209
- 上传时间:2022-12-15
- 格式:DOCX
- 页数:36
- 大小:35.72KB
OGG11g同构GoldenGateORACLE异构GoldenGateMYSQL同步配置及错误解析.docx
《OGG11g同构GoldenGateORACLE异构GoldenGateMYSQL同步配置及错误解析.docx》由会员分享,可在线阅读,更多相关《OGG11g同构GoldenGateORACLE异构GoldenGateMYSQL同步配置及错误解析.docx(36页珍藏版)》请在冰豆网上搜索。
OGG11g同构GoldenGateORACLE异构GoldenGateMYSQL同步配置及错误解析
OGG同构(ORACLE-ORACLE)、异构(ORACLE-MYSQL)同步配置及错误解析
环境:
11.2.0.3(已安装数据库实例)+OEL5.7
192.168.1.55 zlm sid:
zlm11g
192.168.1.60 zlm2 sid:
zlm11g
一、安装软件,配置环境,创建相关用户
1.1 下载介质并安装OGG软件
从官方网址下载最新版OGG FOR ORACLE 11g ON LINUX X86-64软件:
Oracle GoldenGate V11.2.1.0.1 for Oracle 11g on Linux x86-64 (86 MB)
介质名称:
ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
1.2 把OGG软件包复制到源端oracle主目录,创建安装目录gg11后2次解压到gg11
[oracle@zlm ~]$ ls
ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@zlm ~]$ cd $OACLE_BASE
[oracle@zlm oracle]$ mkdir gg11
[oracle@zlm oracle]$ cd gg11
[oracle@zlm gg11]$ unzip /home/oracle/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[oracle@zlm gg11]$ ll
total 223764
-rw-rw-r-- 1 oracle oinstall 228556800 Apr 23 2012 fbo_ggs_Linux_x64_ora11g_64bit.tar
-rwxrwxrwx 1 oracle oinstall 220546 May 2 2012 OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
-rwxrwxrwx 1 oracle oinstall 93696 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.doc
-rwxrwxrwx 1 oracle oinstall 24390 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.txt
[oracle@zlm gg11]$ tar xvof fbo_ggs_Linux_x64_ora11g_64bit.tar
1.3 修改环境变量文件.bash_profile,加入OGG的环境变量
export GGHOME=$ORACLE_BASE/gg11
export PATH=$ORACLE_BASE/gg11:
$ORACLE_HOME/bin:
/usr/bin/:
$PATH
[oracle@zlm gg11]$ . ~/.bash_profile
[oracle@zlm gg11]$ ggsci
ggsci:
error while loading shared libraries:
libnnz11.so:
cannot open shared object file:
No such file or directory
由于没有设置LD_LIBRARY_PATH环境变量,所以无法执行ggsci,添加export LD_LIBLARY_PATH=$ORACLE_BASE/gg11:
$ORACLE_HOME/lib:
$ORACLE_HOME/bin
到环境变量.bash_profile并source,或者创建一个link文件:
[oracle@zlm gg11]$ ln -s /u01/app/oracle/product/11.2.0/db_1/lib/libnnz11.so -
/u01/app/oracle/product/11.2.0/db_1/lib/libnnz10.so
1.4 创建OGG专用目录subdirs
GGSCI (zlm) 1> create subdirs
Creating subdirectories under current directory /u01/app/oracle/gg11
Parameter files /u01/app/oracle/gg11/dirprm:
already exists
Report files /u01/app/oracle/gg11/dirrpt:
created
Checkpoint files /u01/app/oracle/gg11/dirchk:
created
Process status files /u01/app/oracle/gg11/dirpcs:
created
SQL script files /u01/app/oracle/gg11/dirsql:
created
Database definitions files /u01/app/oracle/gg11/dirdef:
created
Extract data files /u01/app/oracle/gg11/dirdat:
created
Temporary files /u01/app/oracle/gg11/dirtmp:
created
Stdout files /u01/app/oracle/gg11/dirout:
created
1.5 开启归档模式、强制日志、附加日志
查看v$database看这3个参数是否已开启,使用OGG必须是开启状态
SQL> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG YES YES
各参数开启方法:
--archivelog
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
--force logging
SQL> alter database force logging;
--supplemental log data
SQL> alter database add supplemental log data;
1.6 关闭数据库的recyclebin(10gDDL必须,11gDDL可选)
SQL> alter system set recyclebin=off scope=spfile; --同步DDL要求关闭10g中的回收站特性
1.7 创建复制用户ogg并授予权限
SQL> create user ogg identified by ogg default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to ogg;
1.8 创建测试用户sender并授予权限
SQL> create user sender identified by sender default tablespace users temporary tablespace temp;
SQL> grant connect,resource,unlimited tablespace to sender;
1.9 配置复制的DDL支持(必须SYSDBA登录执行)
SQL> grant execute on utl_file to ogg;
SQL> @$GGHOME/marker_setup.sql; --建立一个DDL标记表
SQL> @$GGHOME/ddl_setup.sql; --INITIALSETUP选项运行ddl_setup.sql 将在数据库中创建捕获DDL语句的Trigger等必要组件(注意,执行时必须断开GGSCI连接,否则报错)
SQL> @$GGHOME/role_setup.sql; --建立GGS_GGSUSER_ROLE角色
SQL> grant GGS_GGSUSER_ROLE to ogg; --授予给extract group参数中定义的userid用户
SQL> @$GGHOME/ddl_enable.sql; --enable ddl捕获触发器
注意:
下面2个SQL脚本只是为了提高DDL复制性能,不是必须的
SQL> @?
/rdbms/admin/dbmspool --创建DBMS_SHARED_POOL包
SQL> @ddl_pin --通过dbms_shared_pool.keep存储过程将DDLReplication相关的对象keep在共享池中,以保证这些对象不要reload,提升性能
1.10 目标端重复配置以上1.1-1.9全部内容,至此,ORACLE-ORACLE环境搭建完毕
二、用EXPDP/IMPDP初始化测试数据(仅限ORACLE-ORACLE)
2.1 创建EXPDP/IMPDP使用的directory及其对应的本地目录
SQL> set lin 200 pages 999
SQL> col owner for a5
SQL> col directory_name for a25
SQL> col directory_path for a75
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
----- ------------------------- ----------------------------------
SYS GGS_DDL_TRACE /u01/app/oracle/diag/rdbms/zlm11g/zlm11g/trace
SYS SUBDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/
SYS BACKUP /u01/backup
SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/log/
SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/product_media/
SYS XMLDIR /u01/app/oracle/product/11.2.0/db_1/rdbms/xml
SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/sales_history/
SYS DATA_PUMP_DIR /u01/app/oracle/admin/zlm11g/dpdump/
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/state
SQL> create directory expdump as '/u01/expdp';
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
----- ------------------------- ----------------------------------
SYS GGS_DDL_TRACE /u01/app/oracle/diag/rdbms/zlm11g/zlm11g/trace
SYS EXPDUMP /u01/expdp
SYS SUBDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/
SYS BACKUP /u01/backup
SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/log/
SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/product_media/
SYS XMLDIR /u01/app/oracle/product/11.2.0/db_1/rdbms/xml
SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/sales_history/
SYS DATA_PUMP_DIR /u01/app/oracle/admin/zlm11g/dpdump/
SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/state
[oracle@zlm gg11]$ cd /u01/
[oracle@zlm u01]$ ls
app backup
[oracle@zlm u01]$ mkdir /u01/expdp
[oracle@zlm u01]$ ll
total 16
drwxr-xr-x 4 oracle oinstall 4096 Jul 13 03:
12 app
drwxrwxr-x 2 oracle oinstall 4096 Jul 21 20:
26 backup
drwxr-xr-x 2 oracle oinstall 4096 Aug 28 22:
11 expdp
2.2 登录sender用户并创建测试表test
SQL> conn sender/sender
Connected.
SQL> begin
2 for i in 1..3 loop
3 insert into test values(i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select * from test1;
ID
----------
1
2
3
2.3 赋予sender读写directory的权限,执行expdp导出测试表test
SQL> grant read,write on directory expdump to sender;
SQL> !
[oracle@zlm ~]$ expdp sender/sender directory=expdump dumpfile=test.dmp logfile=test.log tables=test
[oracle@zlm ~]$ cd /u01/expdp
[oracle@zlm expdp]$ ls
test.dmp test.log
2.4 在目标端创建directory及相应的本地路径,复制dump文件到目标端
SQL> create directory impdump as ‘/u01/impdp’;
--target
[oracle@zlm2 gg11]$ mkdir /u01/impdp
[oracle@zlm2 gg11]$ ls /u01
app backup impdp
--source
[oracle@zlm expdp]$ scp test.* zlm2:
/u01/impdp
oracle@zlm2's password:
test.dmp 100% 92KB 92.0KB/s 00:
00
test.log 100% 1011 1.0KB/s 00:
00
2.5 目标端赋予recerver用户权限,执行impdp导入测试表test
SQL> grant read,write on directory impdump to sender;
SQL> !
[oracle@zlm2 ~]$ impdp sender/sender directory=impdump dumpfile=test.dmp tables=test
注意:
expdp/impdp必须是在同一个schema下的object,否则不能执行成功,logfile可以无
SQL> conn sender/sender
Connected.
SQL> select * from test;
ID
----------
1
2
3
此时完成test表的初始化同步,注意:
如果非ORACLE-ORACLE方式同步,则只能使用OGG推荐的方式,即配置initial extract来初始化数据,具体可以参考我之前的一篇blog:
OGG配置DML单向复制一例及错误分析
三、不使用PUMP抽取进程的DML同步参数配置(ORACLE-ORACLE)
3.1 单向复制
3.1.1 创建并配置manager
[ogg@zlm gg11]$ ./ggsci
GGSCI (zlm) 1> info all
GGSCI (zlm) 2> edit params mgr
PORT 7809
ggate (zlm) 3> start manager
Manager started.
3.1.2 配置源端抽取组ext1
GGSCI (zlm) 1> add extract ext1, tranlog, begin now
GGSCI (zlm) 2> add rmttrail ./dirdat/rt, extract ext1
GGSCI (zlm) 3> edit params ext1
extract ext1
userid ogg, password ogg
rmthost zlm2, mgrport 7809
rmttrail ./dirdat/rt
ddl include mapped objname sender.*;
table sender.*;
GGSCI (zlm) 4> info all
3.1.3 配置目标端同步组
3.1.3.1 在目标端添加checkpoint表
[ogg@zlm gg11]$ ./ggsci
GGSCI (zlm2) 1> edit params ./GLOBAL
GGSCHEMA ogg
CHECKPOINTTABLE ogg.ckpt
GGSCI (zlm2) 2> dblogin userid ogg password ogg
GGSCI (zlm2) 3> add checkpointtable ogg.ckpt
3.1.3.2 创建同步组rep1
GGSCI (zlm2) 4> add replicat rep1, exttrail ./dirdat/rt, checkpointtable ogg.ckpt
GGSCI (zlm2) 5> edit params rep1
replicat rep1
ASSUMETARGETDEFS
userid ogg,password ogg
reperror default,discard
discardfile ./dirrpt/rep1.dsc, append, megabytes 5
DDL
map sender.*, target sender.*;
3.1.4 源端开启抽取组ext1,目标端开启同步组rep1
--source
GGSCI (zlm) 1> start mgr
GGSCI (zlm) 2> start extract ext1
GGSCI (zlm) 3> info all
--target
GGSCI (zlm2) 1> start mgr
GGSCI (zlm2) 2> start replicat rep1
GGSCI (zlm2) 3> info all
3.1.5 测试DML同步
3.1.5.1 insert
--source
SQL> select * from test
2 /
ID
----------
1
2
3
SQL> insert into test values(4);
1 row created.
SQL> commit;
Commit complete.
--target
SQL> select * from test;
ID
----------
1
2
3
4
注意:
可以顺
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- OGG11g 同构 GoldenGateORACLE 异构 GoldenGateMYSQL 同步 配置 错误 解析
链接地址:https://www.bdocx.com/doc/5383209.html