oracle 11g goldengate搭建（一）
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了oracle 11g goldengate搭建（一），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含12013字，纯文字阅读大概需要18分钟。
内容图文
初学ogg，基本了解ogg原理及架构之后，趁热打铁，搭建一个简单的学习环境，以实现目标：将sourcedb数据库的2个表：sourceuser.test01和sourceuser.dept通过ogg分别同步到targetdb数据库的targetuser.test01和targetuser.dept表中，要求实现DML(insert、update、delete)同步。
基础环境
| 源端 | 目标端 | |
| 操作系统版本 | redhat 6.7 | redhat 6.7 |
| 数据库版本 | 11.2.0.4 | 11.2.0.4 |
| ogg版本 | Oracle GoldenGate 11.2.1.0.3 for Oracle 11g on Linux x86-64 | Oracle GoldenGate 11.2.1.0.3 for Oracle 11g on Linux x86-64 |
| 数据库/实例名称 | sourcedb/sourcedb | targetdb/targetdb |
(一)解压软件（源端和目标端执行）
[oracle@source-node ogg]$ unzip -q V34339-01.zip
[oracle@source-node ogg]$ ls
fbo_ggs_Linux_x64_ora11g_64bit.tar Oracle GoldenGate_11.2.1.0.3_README.txt
OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf V34339-01.zip
Oracle_GoldenGate_11.2.1.0.3_README.doc
[oracle@source-node ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
（二）配置环境变量
[oracle@source-node ~]$ vim .bash_profile
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH:$GG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export GG_HOME=/u01/app/ogg
alias ggsci='cd $GG_HOME;ggsci'
（三）源端数据库配置
（3.1）开启归档
查看是否开启归档
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 4
Current log sequence 6
SQL> alter system set log_archive_dest_1='LOCATION=/archlog';
System altered.
SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2254952 bytes
Variable Size 390072216 bytes
Database Buffers 134217728 bytes
Redo Buffers 7917568 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open ;
Database altered.
（3.2）打开强制生成日志
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
（3.3）打开附加日志
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
NO
SQL>
SQL>
SQL> alter database add supplemental log data;
Database altered.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
（3.4）修改goldengate参数
SQL> show parameter golden
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
SQL> alter system set enable_goldengate_replication=true;
System altered.
SQL> show parameter golden
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
（3.5）创建goldengat用户
创建专属表空间：
create tablespace tbs_ogg datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/sourcedb/tbs_ogg01.dbf' size 100m autoextend on next 10m maxsize 30g;
创建用户：
create user ogg identified by ogg default tablespace tbs_ogg;
授予权限：
grant resource to ogg;
grant create session,alter session to ogg;
grant select any dictionary to ogg;
grant flashback any table to ogg;
grant alter any table to ogg;
grant select any table to ogg;
grant execute on dbms_flashback to ogg;
（3.6）创建测试用户及数据
create user sourceuser identified by sourceuser;
grant connect,resource to sourceuser;
conn sourceuser/sourceuser
--创建表
create table test01 (id number,name varchar2(20));
--增加主键
alter table test01 add primary key(id);
--插入测试数据
insert into test01 values(1,'lijiaman');
insert into test01 values(2,'xiaoming');
insert into test01 values(3,'xiaohua');
--创建表
create table dept (deptno number,dname varchar2(14),loc varchar2(13));
--增加主键
alter table dept add primary key(deptno);
--插入测试数据
insert into dept values(10,'ACCOUNTING','NEW YORK');
insert into dept values(20,'RESEARCH','DALLAS');
insert into dept values(30,'SALES','CHICAGO');
insert into dept values(40,'OPERATIONS','BOSTON');
（3.7）导出数据（使用数据泵）
step1:创建路径
$[/home/oracle] mkdir datapump
$[/home/oracle] sqlplus sys/passwd as sysdba
sql > create or replace DIRECTORY exp_dir as '/home/oracle/datapump'
step2:为导出数据的用户授权
SQL> GRANT READ,WRITE ON DIRECTORY exp_dir TO sourceuser;
SQL> GRANT DATAPUMP_EXP_FULL_DATABASE TO sourceuser;
step3:执行数据导出
$[/home/oracle/datapump]expdp sourceuser/sourceuser directory = exp_dir dumpfile = sourcedb_table.dmp logfile = sourcedb_table.log tables = sourceuser.test01,sourceuser.dept;
（四）目标端数据库配置
（4.1）创建goldengat用户
创建专属表空间：
create tablespace tbs_ogg datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/targetdb/tbs_ogg01.dbf' size 100m autoextend on next 10m maxsize 30g;
创建用户：
create user ogg identified by ogg default tablespace tbs_ogg;
授予权限：
grant resource to ogg;
grant create session,alter session to ogg;
grant select any dictionary to ogg;
grant flashback any table to ogg;
grant alter any table to ogg;
grant select any table to ogg;
grant execute on dbms_flashback to ogg;
grant insert any table to ogg;
grant update any table to ogg;
grant delete any table to ogg;
（4.2）创建测试用户
create user targetuser identified by targetuser;
grant connect,resource to targetuser;
（4.3）导入源端已经导出的数据（初始化数据）
step1：创建路径
$[/home/oracle] mkdir datapump
$[/home/oracle] sqlplus sys/passwd as sysdba
sql > create or replace DIRECTORY imp_dir as '/home/oracle/datapump';
（2）为导入数据的用户授权
SQL> GRANT READ,WRITE ON DIRECTORY imp_dir TO targetuser;
SQL> GRANT DATAPUMP_IMP_FULL_DATABASE TO targetuser;
（3）将文件拷贝过来（在源端执行）
cd /home/oracle/datapump
scp sourcedb_table.dmp oracle@192.168.10.12:/home/oracle/datapump
（4）执行导入
$[/home/oracle/datapump]impdp targetuser/targetuser directory=imp_dir dumpfile =sourcedb_table.dmp remap_schema=sourceuser:targetuser;
（五）源端ogg配置
（5.1）创建ogg相关目录
[oracle@source-node ~]$ cd $GG_HOME
[oracle@source-node ogg]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (source-node) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter files /u01/app/ogg/dirprm: already exists
Report files /u01/app/ogg/dirrpt: created
Checkpoint files /u01/app/ogg/dirchk: created
Process status files /u01/app/ogg/dirpcs: created
SQL script files /u01/app/ogg/dirsql: created
Database definitions files /u01/app/ogg/dirdef: created
Extract data files /u01/app/ogg/dirdat: created
Temporary files /u01/app/ogg/dirtmp: created
Stdout files /u01/app/ogg/dirout: created
（5.2）配置并启动mgr进程
GGSCI (source-node) 2> edit param mgr
port 7809
dynamicportlist 7840-7845
GGSCI (source-node) 3> start mgr
Manager started.
GGSCI (source-node) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
（5.3）开启待同步表的额外日志
GGSCI (source-node) 6> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (source-node) 7> add trandata sourceuser.test01
Logging of supplemental redo data enabled for table SOURCEUSER.TEST01.
GGSCI (source-node) 10> add trandata sourceuser.dept
Logging of supplemental redo data enabled for table SOURCEUSER.DEPT.
（5.4）配置抽取进程exta
GGSCI (source-node) 11> add extract exta,tranlog,begin now
EXTRACT added.
GGSCI (source-node) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXTA 00:00:00 00:00:05
GGSCI (source-node) 13> add exttrail ./dirdat/ra,extract exta
EXTTRAIL added.
GGSCI (source-node) 14> edit param exta
extract exta
userid ogg,password ogg
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
exttrail ./dirdat/ra
dynamicresolution
gettruncates
table sourceuser.test01;
table sourceuser.dept;
GGSCI (source-node) 16> start exta
Sending START request to MANAGER ...
EXTRACT EXTA starting
GGSCI (source-node) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTA 00:07:51 00:00:05
（5.5）配置投递进程
GGSCI (source-node) 21> add extract dpa,exttrailsource ./dirdat/ra
EXTRACT added.
GGSCI (source-node) 22> edit param dpa
extract dpa
userid ogg,password ogg
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
passthru
rmthost 192.168.10.12,mgrport 7809
rmttrail ./dirdat/ra
table sourceuser.test01;
table sourceuser.dept;
GGSCI (source-node) 23> add rmttrail ./dirdat/ra,extract dpa
RMTTRAIL added.
GGSCI (source-node) 26> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPA 00:00:00 00:03:46
EXTRACT RUNNING EXTA 00:00:00 00:00:05
（六）目标端ogg配置
（6.1）创建ogg相关目录
[oracle@target-node ~]$ cd $GG_HOME
[oracle@target-node ogg]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (target-node) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter files /u01/app/ogg/dirprm: already exists
Report files /u01/app/ogg/dirrpt: created
Checkpoint files /u01/app/ogg/dirchk: created
Process status files /u01/app/ogg/dirpcs: created
SQL script files /u01/app/ogg/dirsql: created
Database definitions files /u01/app/ogg/dirdef: created
Extract data files /u01/app/ogg/dirdat: created
Temporary files /u01/app/ogg/dirtmp: created
Stdout files /u01/app/ogg/dirout: created
（6.2）配置并启动mgr进程
GGSCI (target-node) 2> edit param mgr
port 7809
dynamicportlist 7840-7845
GGSCI (target-node) 3> start mgr
Manager started.
GGSCI (target-node) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
（6.3）配置ogg检查点
GGSCI (target-node) 5> edit param ./globals
checkpointtable ogg.checkpoint
GGSCI (target-node) 6> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (target-node) 7> add checkpointtable ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
（6.4）配置复制进程
GGSCI (target-node) 14> add replicat repa,exttrail ./dirdat/ra,checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (target-node) 36> edit param repa
replicat repa
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password ogg
numfiles 500
grouptransops 10000
handlecollisions
assumetargetdefs
allownoopupdates
dynamicresolution
discardfile ./dirrpt/repa_discard.txt,append,megabytes 10
map sourceuser.*,target targetuser.*;
GGSCI (target-node) 17> start repa
Sending START request to MANAGER ...
REPLICAT REPA starting
GGSCI (target-node) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPA 00:00:00 00:00:02
（七）测试ogg复制功能
1.在sourceuser.test01表中插入数据，发现targetuser.test01中有新数据进入；
2.在源端执行delete from test01并提交，目标端targetuser.test01中的数据也全部删除；
3.对源表非主键列进行update操作：update dept set loc='sichuan' where deptno=40，目标端也自动更新；
4.对源表主键列进行update操作：update dept set deptno=deptno+1，目标端也自动更新；
【完】
内容总结
以上是互联网集市为您收集整理的oracle 11g goldengate搭建（一）全部内容，希望文章能够帮你解决oracle 11g goldengate搭建（一）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。