1:实验环境
2:实验步骤
--下面的2.1-2.2步骤,都需要在源端和目标端分别执行。
2.1:准备工作
2.1.1 建表空间
create tablespace ogg datafile '/u01/app/oracle/oradata/orcl/ogg01.dbf' size 200m;
2.1.2 创建用户并授权
--建立用于复制的数据库账号并授权:
create user ogg identified by oracle default tablespace ogg;
grant connect,resource,dba to ogg;
为了方便,这里直接赋予dba权限,如果对数据库安全要求高,可以去查询ogg文档,赋予复制需要的最小权限!(source和target端做相同的操作)
2.1.3 配环境变量
--在源端和目标端配置环境变量(这里以源端为例):
[oracle@source_pc ~]$ vi .bash_profile
添加一行:
export GG_HOME=/home/oracle/goldengate
[oracle@source_pc ~]$ source .bash_profile
2.1.4 启动监听
[oracle@source_pc admin]$ lsnrctl start
2.1.5 配TNS
2.1.5.1 源端
[oracle@source_pc ~]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@source_pc admin]$ vi tnsnames.ora
添加:
gg_target =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.226)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
--测试看是否配置成功(tnsping gg_target)
--显示OK字样,表示配置成功
2.1.5.2 目标端
[oracle@target_pc admin]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@target_pc admin]$ vi tnsnames.ora
添加:
gg_source=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.225)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
2.1.6 检查是否处于归档模式
如果是distabled,则需要关库,启动到mount状态,alter database archivelog;
2.1.7 开启数据库附加日志
SQL> select supplemental_log_data_min
2 from v$database;
SUPPLEME
--------
NO
SQL> alter database add supplemental log data;
Database altered.
SQL> select supplemental_log_data_min
2 from v$database;
SUPPLEME
--------
YES
SQL>
2.1.8 开启force logging
SQL> select force_logging
2 from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging
2 from v$database;
FOR
---
YES
2.1.9 准备测试数据
源端scott用户下有一个t表,有7条数据:
目标端scott用户下新建一个t表,但是不插入数据(只复制表定义,不填充数据 ):
2.2:安装goldengate
--目标端及源端都需安装
2.2.1下载安装包
我的机器是32位的,所以用安装包:
2.2.2 将安装包上传到Linux下
2.2.3 解压
[oracle@source_pc goldengate]$ unzip ogg112101_fbo_ggs_Linux_x86_ora10g_32bit.zip
Archive: ogg112101_fbo_ggs_Linux_x86_ora10g_32bit.zip
inflating: fbo_ggs_Linux_x86_ora10g_32bit.tar
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
inflating: Oracle GoldenGate 11.2.1.0.1 README.doc
[oracle@source_pc goldengate]$ tar -xvf fbo_ggs_Linux_x86_ora10g_32bit.tar
UserExitExamples/
UserExitExamples/ExitDemo_passthru/
UserExitExamples/ExitDemo_passthru/Makefile_passthru.AIX
UserExitExamples/ExitDemo_passthru/exitdemo_passthru.c
UserExitExamples/ExitDemo_passthru/Makefile_passthru.LINUX
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HP_OSS
UserExitExamples/ExitDemo_passthru/exitdemopassthru.vcproj
UserExitExamples/ExitDemo_passthru/readme.txt
UserExitExamples/ExitDemo_passthru/Makefile_passthru.SOLARIS
UserExitExamples/ExitDemo_passthru/Makefile_passthru.HPUX
UserExitExamples/ExitDemo_lobs/
UserExitExamples/ExitDemo_lobs/exitdemo_lob.vcproj
UserExitExamples/ExitDemo_lobs/Makefile_lob.HPUX
UserExitExamples/ExitDemo_lobs/exitdemo_lob.c
UserExitExamples/ExitDemo_lobs/Makefile_lob.AIX
UserExitExamples/ExitDemo_lobs/Makefile_lob.SOLARIS
UserExitExamples/ExitDemo_lobs/Makefile_lob.LINUX
UserExitExamples/ExitDemo_lobs/readme.txt
UserExitExamples/ExitDemo/
UserExitExamples/ExitDemo/exitdemo.vcproj
UserExitExamples/ExitDemo/Makefile_exit_demo.SOLARIS
UserExitExamples/ExitDemo/exitdemo_utf16.c
UserExitExamples/ExitDemo/readme.txt
UserExitExamples/ExitDemo/exitdemo.c
UserExitExamples/ExitDemo/Makefile_exit_demo.HPUX
UserExitExamples/ExitDemo/Makefile_exit_demo.AIX
UserExitExamples/ExitDemo/Makefile_exit_demo.LINUX
UserExitExamples/ExitDemo/Makefile_exit_demo.HP_OSS
UserExitExamples/ExitDemo_pk_befores/
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.c
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.SOLARIS
UserExitExamples/ExitDemo_pk_befores/exitdemo_pk_befores.vcproj
UserExitExamples/ExitDemo_pk_befores/readme.txt
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.AIX
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.LINUX
UserExitExamples/ExitDemo_pk_befores/Makefile_pk_befores.HPUX
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.vcproj
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.AIX
UserExitExamples/ExitDemo_more_recs/readme.txt
UserExitExamples/ExitDemo_more_recs/exitdemo_more_recs.c
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.LINUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS
bcpfmt.tpl
bcrypt.txt
cfg/
cfg/ProfileConfig.xml
cfg/jps-config-jse.xml
cfg/password.properties
cfg/MPMetadataSchema.xsd
cfg/mpmetadata.xml
cfg/Config.properties
chkpt_ora_create.sql
cobgen
convchk
pccntl.tpl
ddl_cleartrace.sql
ddl_ddl2file.sql
ddl_disable.sql
ddl_enable.sql
ddl_filter.sql
ddl_nopurgeRecyclebin.sql
ddl_ora10.sql
ddl_ora10upCommon.sql
ddl_ora11.sql
ddl_ora9.sql
ddl_pin.sql
ddl_purgeRecyclebin.sql
ddl_remove.sql
ddl_session.sql
ddl_session1.sql
ddl_setup.sql
ddl_status.sql
ddl_staymetadata_off.sql
ddl_staymetadata_on.sql
ddl_trace_off.sql
ddl_trace_on.sql
ddl_tracelevel.sql
ddlcob
defgen
demo_more_ora_create.sql
demo_more_ora_insert.sql
demo_ora_create.sql
demo_ora_insert.sql
demo_ora_lob_create.sql
demo_ora_misc.sql
demo_ora_pk_befores_create.sql
demo_ora_pk_befores_insert.sql
demo_ora_pk_befores_updates.sql
dirjar/
dirjar/xmlparserv2.jar
dirjar/spring-security-cas-client-3.0.1.RELEASE.jar
dirjar/spring-security-taglibs-3.0.1.RELEASE.jar
dirjar/org.springframework.transaction-3.0.0.RELEASE.jar
dirjar/identityutils.jar
dirjar/xpp3_min-1.1.4c.jar
dirjar/jps-api.jar
dirjar/jacc-spi.jar
dirjar/org.springframework.jdbc-3.0.0.RELEASE.jar
dirjar/org.springframework.aspects-3.0.0.RELEASE.jar
dirjar/identitystore.jar
dirjar/jps-mbeans.jar
dirjar/fmw_audit.jar
dirjar/commons-codec-1.3.jar
dirjar/jmxremote_optional-1.0-b02.jar
dirjar/spring-security-core-3.0.1.RELEASE.jar
dirjar/log4j-1.2.15.jar
dirjar/commons-logging-1.0.4.jar
dirjar/org.springframework.context-3.0.0.RELEASE.jar
dirjar/org.springframework.asm-3.0.0.RELEASE.jar
dirjar/org.springframework.expression-3.0.0.RELEASE.jar
dirjar/org.springframework.instrument-3.0.0.RELEASE.jar
dirjar/jps-wls.jar
dirjar/jps-upgrade.jar
dirjar/jdmkrt-1.0-b02.jar
dirjar/jps-ee.jar
dirjar/jps-common.jar
dirjar/org.springframework.beans-3.0.0.RELEASE.jar
dirjar/jps-manifest.jar
dirjar/oraclepki.jar
dirjar/spring-security-acl-3.0.1.RELEASE.jar
dirjar/jagent.jar
dirjar/osdt_xmlsec.jar
dirjar/jps-patching.jar
dirjar/org.springframework.context.support-3.0.0.RELEASE.jar
dirjar/jps-unsupported-api.jar
dirjar/monitor-common.jar
dirjar/osdt_cert.jar
dirjar/spring-security-web-3.0.1.RELEASE.jar
dirjar/spring-security-config-3.0.1.RELEASE.jar
dirjar/org.springframework.aop-3.0.0.RELEASE.jar
dirjar/xstream-1.3.jar
dirjar/jps-internal.jar
dirjar/osdt_core.jar
dirjar/org.springframework.core-3.0.0.RELEASE.jar
dirjar/slf4j-api-1.4.3.jar
dirjar/ldapjclnt11.jar
dirjar/org.springframework.test-3.0.0.RELEASE.jar
dirjar/org.springframework.orm-3.0.0.RELEASE.jar
dirjar/org.springframework.web-3.0.0.RELEASE.jar
dirjar/slf4j-log4j12-1.4.3.jar
dirjar/jsr250-api-1.0.jar
dirprm/
dirprm/jagent.prm
emsclnt
extract
freeBSD.txt
ggMessage.dat
ggcmd
ggsci
help.txt
jagent.sh
keygen
libantlr3c.so
libdb-5.2.so
libgglog.so
libggrepo.so
libicudata.so.38
libicui18n.so.38
libicuuc.so.38
libxerces-c.so.28
libxml2.txt
logdump
marker_remove.sql
marker_setup.sql
marker_status.sql
mgr
notices.txt
oggerr
params.sql
prvtclkm.plb
pw_agent_util.sh
remove_seq.sql
replicat
retrace
reverse
role_setup.sql
sequence.sql
server
sqlldr.tpl
tcperrs
ucharset.h
ulg.sql
usrdecs.h
zlib.txt
2.2.4 create subdirs
[oracle@source_pc goldengate]$
[oracle@source_pc goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (source_pc) 1> create subdirs
Creating subdirectories under current directory /home/oracle/goldengate
Parameter files /home/oracle/goldengate/dirprm: already exists
Report files /home/oracle/goldengate/dirrpt: created
Checkpoint files /home/oracle/goldengate/dirchk: created
Process status files /home/oracle/goldengate/dirpcs: created
SQL script files /home/oracle/goldengate/dirsql: created
Database definitions files /home/oracle/goldengate/dirdef: created
Extract data files /home/oracle/goldengate/dirdat: created
Temporary files /home/oracle/goldengate/dirtmp: created
Stdout files /home/oracle/goldengate/dirout: created
2.3 goldengate配置
2.3.1 将源端t表现有数据全部同步到目标端t表里
--可以通过exp,imp实现完全同步,也可以用extract,replicate进程同步。这里使用的后者。
2.3.1.1 启动mgr进程
--在源端和目标端皆需要启动
源端:
GGSCI (source_pc) 14> edit params mgr
在该参数文件输入: PORT 7809
GGSCI (source_pc) 4> view params mgr
PORT 7809
GGSCI (source_pc) 5> start mgr
Manager started.
GGSCI (source_pc) 6> info mgr
Manager is running (IP port source_pc.7809).
2.3.1.2 source端添加extract进程
GGSCI (source_pc) 7> add extract einig1,sourceistable
EXTRACT added.
--sourceistable代表直接从表中读取数据
GGSCI (source_pc) 4> edit params einig1
--einig1代表extract initial load group 1缩写
在该文件里添加以下内容:
extract einig1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password oracle
rmthost 192.168.8.226,mgrport 7809
rmttask replicat,group rinig1
table scott.t;
2.3.1.3 target端添加replicat进程
GGSCI (target_pc) 1> add replicat rinig1,specialrun
REPLICAT added.
GGSCI (target_pc) 2> edit params rinig1
--rinig1代表replicat initial load group 1缩写
添加如下内容:
replicat rinig1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
assumetargetdefs
userid ogg,password oracle
discardfile ./dirrpt/rinig1.dsc,purge
map scott.t,target scott.t;
--rinig1的名字必须同source端定义的group名字相同
2.3.1.4 source端启动extract进程
GGSCI (source_gg) 19> start extract einig1
2.3.1.5 target端验证
现在,虽然源端的数据能够全部同步到目标端了。但是还没有实现实时同步,比如,我现在在源端里插入一条新的数据,目标库是查不到的。
2.3.2 实时同步复制
2.3.2.1:修改参数文件
GGSCI (source_pc) 3> edit params eora_t1
在该参数文件中添加:
extract eora_t1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password oracle
exttrail ./dirdat/aa
table scott.t;
2.3.2.2:开启scott用户下表的附加日志
GGSCI (source_pc) 1> dblogin userid ogg,password oracle
Successfully logged into database.
GGSCI (source_pc) 4> add trandata scott.t
Logging of supplemental redo data enabled for table SCOTT.T.
2.3.2.3:添加extract进程,添加trail文件
--文件名前缀不能超过2个字符
GGSCI (source_pc) 1> add extract eora_t1,tranlog,begin now
EXTRACT added.
GGSCI (source_pc) 2> add exttrail ./dirdat/aa,extract eora_t1,megabytes 100 //添加trail文件
EXTTRAIL added.
GGSCI (source_pc) 3> start extract eora_t1
Sending START request to MANAGER ...
EXTRACT EORA_T1 starting
--这时,在/home/oracle/goldengate/dirdat下可以看到生成的文件aa000000。
2.3.2.4:添加pump进程
GGSCI (source_pc) 5> edit params pora_t1
添加如下内容:
extract pora_t1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
passthru
rmthost 192.168.8.226,mgrport 7809
rmttrail ./dirdat/pa
table scott.t;
GGSCI (source_pc) 9> add extract pora_t1,exttrailsource ./dirdat/aa
//这里aa文件名同前面extract进程参数文件中定义的trail文件名一致
EXTRACT added.
GGSCI (source_pc) 10> add rmttrail ./dirdat/pa,extract pora_t1,megabytes 100
//添加传输到target数据库的trail问文件名,应该同参数文件中描述的一致
RMTTRAIL added.
GGSCI (source_pc) 11> start extract pora_t1
Sending START request to MANAGER ...
EXTRACT PORA_T1 starting
2.3.2.5:在target端添加检查表,配置replicat进程
GGSCI (target_pc) 1> edit params ./GLOBALS
GGSCI (target_pc) 2> view params ./GLOBALS
checkpointtable ogg.ggschkpt
GGSCI (target_pc) 3> exit //这里需要退出ggsci终端
[oracle@ target_pc ~]$ sqlplus ogg/oracle
SQL> select tname from tab;
no rows selected
[oracle@ target_pc ogg]$ ggsci
GGSCI target_ (pc) 1> dblogin userid ogg,password oracle
Successfully logged into database.
GGSCI (target_pc) 2> add checkpointtable
No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...
Successfully created checkpoint table ogg.ggschkpt.
SQL> select tname from tab;
TNAME
------------------------------
GGSCHKPT
GGSCHKPT_LOX
GGSCI (target_pc) 3> edit params rora_t1
GGSCI (target_pc) 4> view params rora_t1
replicat rora_t1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password oracle
handlecollisions
assumetargetdefs
discardfile ./dirrpt/rora_t1.dsc,purge
map scott.t ,target scott.t;
GGSCI (target_pc) 5> add replicat rora_t1,exttrail ./dirdat/pa
REPLICAT added.
GGSCI (target_pc) 6> start replicat rora_t1
Sending START request to MANAGER ...
REPLICAT RORA_T1 starting
2.3.2.6: 测试
在源端:
目标端:
--假如目标端有源端没有的数据,也不妨碍源端将新数据同步过来。