今天测试了下oracle goldengate，主站是oracle 10.2.0.5.0，目标库是 11.2.0.1.0，详细配置如下：
由于主从数据库版本不一致(OS都是linux x86-64)，下载的安装包也不一样
1.解压并安装gg：
主数据库：fbo_ggs_Linux_x64_ora10g_64bit.zip
从数据库：fbo_ggs_Linux_x64_112100_ora11g_64bit.zip
[oracle@trptdb ~]$ unzip fbo_ggs_Linux_x64_112100_ora11g_64bit.zip| tar -xvof
tar命令最后解压出来大概100多个文件，我这里安装目录:/home/oracle/ggs
由于我使用的是oracle用户进行安装的，所以直接在oracle家目录下修改.bash_profile文件，把gg的安装目录添加到库文件目录，如下：
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/home/oracle/ggs;
安装很简单，如下：
[oracle@testdb ~]$ pwd
/home/oracle
[oracle@testdb ~]$ cd ggs
[oracle@testdb ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 10g on Oct 4 2011 23:50:20
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (testdb) 1> create subdirs
目标库也按照上述步骤安装GoldenGate并创建工作目录。
2.配置主数据库：
主库必须置于归档模式，force logging，并且启用supplemental logging。查看这几个选项是否启动，最简单的方式是查询v$database视图，例如：
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUP FOR
------------ --- ---
ARCHIVELOG YES YES
由于主数据库之前配置了stream，所以这些设置都已经启用，启用上述几个选项的操作如下，以sysdba身份登录到sqlplus命令行，执行下列命令：
--启动到mount状态：
startup mount;
--置于归档模式：
alter database archivelog;
--强制日志记录：
alter database force logging;
--启用最少附加日志
alter database add supplemental log data;
--启动数据库并查询状态：
SQL> alter database open;
Database altered.
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database; LOG_MODE SUP FOR
------------ --- ---
ARCHIVELOG YES YES
创建goldengate用户，并赋相应的权限:
SQL> create user ogg identified by ogg;
User created.
grant execute on utl_file to ogg ;
grant dba to ogg ;
grant connect to ogg ;
grant resource to ogg ;
禁用recycle bin
对于11g: 需要重启才能生效
ALTER SYSTEM SET recyclebin = OFF scope=spfile;
对于10g:
Alter system set recyclebin=off;
编辑全局参数文件
GGSCI (testdb) 1> edit params ./GLOBAL
GGSCHEMA ogg
CHECKPOINTTABLE ogg.checkpoint
为了支持ddl同步，需要执行以下脚本：
marker_setup.sql--该脚本安装marker系统，这部分为启用DLL支持所必备，执行该脚本时会提示输入GoldenGate管理帐户schema名.
ddl_setup-- 执行该脚本要确认关闭掉所有ORACLE会话，未被关闭的会话会以列表形式显示，执行过程中会要求希望对象属主，并选择安装模式。如果是初次安装就选择 “INITIALSETUP”，该模式假设当前没有任何GoldenGate DDL对象存在，如果存在则会删除并重建。如果是重新安装，则应该选择“NORMAL”.
role_setup--该操作会重建DDL同步所需的权限，授予GoldenGate中的DDL对象以DML权限.
ddl_enable--启用DDL触发器，以捕获DDL操作.
要提高DDL触发器的性能，可以通过ddl_pin脚本，该脚本会将触发器使用的包加载到内存，以此提高效率。该脚本执行时需要引用dbms_shared_pool系统包，因此在使用ddl_pin脚本前需要确保dbms_shared_pool可用。
@?/rdbms/admin/dbmspool.sql
ddl_pin--执行ddl_pin脚本需要指定GoldenGate管理员schema名称.
然后，用于同步DDL操作的对象就创建完成了。
配置主数据库gg相关：
--配置MANAGER参数：
GGSCI (testdb) 1>edit params mgr
PORT 7809
PURGEOLDEXTRACTS ./dirdat/k1*,USECHECKPOINTS,MINKEEPFILES 10
AUTORESTART ER *, RETRIES 3, WAITMINUTES 5
PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30
PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30
--配置抽取进程ext1参数：
GGSCI (testdb) 1> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (testdb) 2> add extract ext1,tranlog, begin now
EXTRACT added.
GGSCI (testdb) 4>add exttrail /home/oracle/ggs/dirdat/k1, extract ext1
EXTTRAIL added.
GGSCI (testdb) 5> edit params ext1
EXTRACT EXT1
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
USERID ogg,PASSWORD ogg
exttrail /home/oracle/ggs/dirdat/k1
DYNAMICRESOLUTION
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA,REPORT
FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT
STATOPTIONS REPORTFETCH
WARNLONGTRANS 1H, CHECKINTERVAL 5M
TABLE mynet_app.*;
--创建datapump
GGSCI (testdb) 4>> add extract dpump,exttrailsource /home/oracle/ggs/dirdat/k1
EXTRACT added.
GGGSCI (testdb) 4> add rmttrail /home/oracle/ggs/dirdat/k1, extract dpump
RMTTRAIL added.
GGSCI (testdb) 4>> view params dpump
extract dpump
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
userid ogg, password ogg
rmthost 10.1.8.142, mgrport 7809
rmttrail /home/oracle/ggs/dirdat/k1
DYNAMICRESOLUTION
table mynet_app.*;
GGSCI (testdb) 5> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPUMP 00:00:00 00:08:49
EXTRACT STOPPED EXT1 00:00:00 00:11:37
启动捕获进程：
GGSCI (testdb) 6> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPUMP 00:00:00 00:00:08
EXTRACT RUNNING EXT1 00:00:00 00:00:06
3.配置从数据库：
–创建Replicat
GGSCI (trptdb) 1> view param ./GLOBALS
GGSCHEMA ogg
CHECKPOINTTABLE ogg.checkpoint
GGSCI (trptdb) 1> dblogin userid ogg,password ogg
GGSCI (trptdb) 2> add checkpointtable ogg.checkpoint
Successfully created checkpoint table OGG.CHECKPOINT.
GGSCI (trptdb) 2> add replicat rep1,exttrail /home/oracle/ggs/dirdat/k1, checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (trptdb) 3> edit param rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID ogg, PASSWORD ogg
discardfile ./dirdat/rep1_discard.txt,append,megabytes 5
mapexclude mynet_app.test0410
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
BATCHSQL
DBOPTIONS DEFERREFCONST
DBOPTIONS LOBWRITESIZE 102400
HANDLECOLLISIONS
DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20
--grouptransops 1
--maxtransops 1
APPLYNOOPUPDATES --解决不能更新操作
MAP mynet_app.*, TARGET mynet_app.*;
------------------------------------------------------------------------------------------------------------------------
4.初始化：
如果要求零停机，DB事务就会不间断进行，可以通过如下两种方法来保证初始化过程中事务的完整性和数据的准确性呢.
（1）. 利用 Keys + Handlecollisions
Handlecollisions参数依赖于表上的Key(Primarykey/Unique key)来对数据进行重复行和缺失行的处理
利用 commit SCN/CSN
可以使用一下几种方法来实现：
一致性的exp和imp
通过exp和expdp的一致性参数(flashback_scn)，导出特定SCN点上的一致性版本.
FLASHBACK_SCN 参数用于指定导出特定SCN时刻的表数据，如
使用这种方法初始化存在一个问题，就是构造一致性数据过程中会对undo造成
比较大的压力，尤其对大型数据库来讲，可以通过分割的datapump来实现
数据的分组同步，分散undo的压力，然后合适时间将分组的datapump合并即
可。
（2）一致性的expdp和impdp (表空间移植,但是需要将表控制置于read only模式)
（3） 基于备份的表空间搬移
对于不同字节顺序的source-target平台初始化，需要进行convert），但是需要
10g以上版本才支持，同样受到表空间搬移的那些限制条件。
Dataguard 适合同平台版本
我这里采用的是用带flashback_scn参数的expdp/impdp完成初始化，具体操作如下：
–主数据库
[oracle@trptdb ggs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 23 13:48:11 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select current_scn from v$database ;
CURRENT_SCN
———–
70653113406
--导出导入（从数据库）
impdp strmadmin directory = DPDATA1 network_link = uat schemas=mynet_app flashback_scn=70653113406 TABLE_EXISTS_ACTION =replace
。。。
导入完毕，丛库启动replicat进程：
GGSCI (trptdb) 3> start rep1,aftercsn 70653113406
之后主从开始同步，日志如下：
主库：
2012-03-23 14:08:36 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: Object MYNET_APP.M_WEEKLY_REPORT_SOURCING has NOLOGGING option specified either at the table level or at the level of one of its (sub)partitions.
2012-03-23 14:08:36 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: No unique key is defined for table M_WEEKLY_REPORT_SOURCING. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2012-03-23 14:10:33 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: Object MYNET_APP.M_WEEKLY_REPORT_CATEGORY has NOLOGGING option specified either at the table level or at the level of one of its (sub)partitions.
2012-03-23 14:10:33 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: No unique key is defined for table M_WEEKLY_REPORT_CATEGORY. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2012-03-23 14:11:42 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: Object MYNET_APP.M_WEEKLY_REPORT_ITEM has NOLOGGING option specified either at the table level or at the level of one of its (sub)partitions.
2012-03-23 14:11:42 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: No unique key is defined for table M_WEEKLY_REPORT_ITEM. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2012-03-23 14:12:19 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: No unique key is defined for table M_MEMBER_COMMENT. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2012-03-23 14:14:15 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: Object MYNET_APP.M_WEEKLY_REPORT_SUPPLIER has NOLOGGING option specified either at the table level or at the level of one of its (sub)partitions.
2012-03-23 14:14:15 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, ext1.prm: No unique key is defined for table M_WEEKLY_REPORT_SUPPLIER. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
丛库：
2012-03-23 14:11:36 INFO OGG-01670 Oracle GoldenGate Collector for Oracle: Closing /home/oracle/ggs/dirdat/k1000096.
2012-03-23 14:11:36 INFO OGG-01669 Oracle GoldenGate Collector for Oracle: Opening /home/oracle/ggs/dirdat/k1000097 (byte -1, current EOF 0).
2012-03-23 14:11:54 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep1.prm: No unique key is defined for table 'M_WEEKLY_REPORT_SOURCING'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2012-03-23 14:13:37 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rep1.prm: Setting current schema for DDL operation to [mynet_app].
2012-03-23 14:13:37 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rep1.prm: Restoring current schema for DDL operation to [ogg].
2012-03-23 14:13:38 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rep1.prm: Setting current schema for DDL operation to [mynet_app].
2012-03-23 14:13:38 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rep1.prm: Restoring current schema for DDL operation to [ogg].
2012-03-23 14:13:38 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rep1.prm: Setting current schema for DDL operation to [mynet_app].
2012-03-23 14:13:38 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rep1.prm: Restoring current schema for DDL operation to [ogg].
2012-03-23 14:13:48 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, rep1.prm: No unique key is defined for table 'M_WEEKLY_REPORT_CATEGORY'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
5.同步测试：
主库建张表并插入些数据：
[oracle@testdb ggs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Mar 23 14:17:07 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn mynet_app
Enter password:
Connected.
SQL> create table test121312(id number);
Table created.
SQL> insert into test121312 select rownum from dual connect by rownum<=10;
10 rows created.
SQL> commit;
Commit complete.
SQL> select count(9) from test121312;
COUNT(9)
----------
10
SQL>
丛库进行查询：
[oracle@trptdb ggs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 23 14:23:44 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn mynet_app
Enter password:
Connected.
SQL> select count(9) from test121312;
COUNT(9)
----------
10
SQL>
成功同步，在此记录一下~
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27042095/viewspace-758119/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/27042095/viewspace-758119/
558
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
