Goldengate for oracle安装配置文档

1、golden gate配置示例创建操作系统用户在源端(包括rac1和rac2)和目标端创建用户，用于管理goldengate：useradd-goinstall-gdbagoldengatepasswdgoldengate配置资源限制：官方建议尽可能将资源限制放开，其实我们可以根据具体情况进行配置，但不能设得太小cat/etc/security/limits.confarchive log list;在源端数据库中打开forceloggingsqlselectforce_loggingfromv$database;fornosqlalterdatabaseforcelogging;databa

2、sealtered.启用附加日志u 检查源端数据库附加日志是否打开sqlselect supplemental_log_data_min from v$database;将数据库附加日志打开sqlalter database add supplemental log data;切换日志以使附加日志生效：sqlalter system archive log current; 确保asm实例的联通u 确保goldengate能够连接到数据库的asm实例（asm环境）rac中所有节点都要配置在源端tnsnames.ora中配置asm实例信息vi$oracle_home/network/admin/

3、tnsnames.oraoradb_asm=(description=(address=(protocol=tcp)(host=rac1-vip)(port=1521)(address=(protocol=tcp)(host=rac2-vip)(port=1521)(load_balance=yes)(connect_data=(server=dedicated)(service_name=+asm)在源端listener.ora中配置asm实例的相关信息vi$oracle_home/network/admin/listener.orasid_list_listener_rac1=(sid_l

4、ist=(sid_desc=(global_dbname=oradb)(oracle_home=/opt/app/oracle/product/10.2.0/db_1)(sid_name=oradb1)(sid_desc=(global_dbname=+asm)(oracle_home=/opt/app/oracle/product/10.2.0/db_1)(sid_name=+asm1)上面是rac1中的配置，rac2中的sid_list_listener_xxx和sid_name要相应修改重启监听lsnrctlreload通过sqlplussys/xxxoradb_asmassysdba来

5、连接asm实例，能连上则说明配置成功创建goldengate数据库用户u 在源端，创建goldengate系统用户，建议使用oracle用户。a) 创建goldengate用户的默认表空间create tablespace tbs_goldengate datafile /data/oradata/sbap/ tbs_goldengate_a.dbf size 2048m autoextend on extent management local segment space management autob) 创建goldengate数据库用户goldengatecreate user gol

6、dengate identified by goldengate default tablespace xxx ;grantconnect,resourcetogoldengate;grantexecuteonutl_filetogoldengate;grant dba to goldengate;sql exec dbms_streams_auth.grant_admin_privilege(ggate); 应该是下面两个之一sql exec dbms_streams_auth.grant_admin_privilege (goldengate);（10g上测试应该是这个）sql exec

7、dbms_goldengate_auth.grant_admin_privilege (goldengate);（dbms_goldengate_auth.grant_admin_privilege这个包10g上没有，但尚未在11g上测试）pl/sql procedure successfully completed.安装ddl如何配置goldengate来捕获和传输ddl，在oraclegoldengatewindowsandunixadministratorsguide中有比较详细的说明。下面列出一个表格，说明下goldengate中用于ddl同步的一些对象对象用途默认名称ddl标记表存储

8、ddl信息，该表只接收插入ggs_marker标记表中的序列用来填充标记表中的一个字段ggs_ddl_seqddl历史表存储对象元数据历史，接收插入、更新、删除ggs_ddl_hist对象id历史表包含了配置的对象的对象idggs_ddl_hist_altddl触发器针对ddl操作，将操作的信息写入到标记表盒历史表ggs_ddl_trigger_beforeddlschema包含ddl同步对象的schema必须在安装过程中在globals文件中指定用户角色创建执行ddl操作所需的角色ggs_ggsuser_role内部安装表只用于内部使用的表ggs_setupddl_pin固定ddl追踪、dd

9、l包和ddl触发器，用于性能改进ddl_pinddl_cleartrace.sql删除ddl跟踪文件ddl_cleartrace.sqlddl_status.sql确认goldengateddl对象已经安装ddl_status.sqlmarker_status.sql确认标记表已经安装marker_status.sqlddl_tracelevel.sql设置ddl跟踪的级别ddl_tracelevel.sql下面讲一下安装ddl对象的步骤：1. 为ddl对象选择一个schema2. 为该schema授予以下权限：grantexecuteonutl_fileto;3. 为这些对象选择一个表空间，

10、并且该表空间的剩余空间要能承担ggs_ddl_hist和ggs_marker表的数据增长，特别是ggs_ddl_hist，它会根据ddl操作的频繁程度按比例增长。如果没有足够的表空间，数据库中的ddl操作将不能完成，业务应用会被挂起。4. 在这个goldengate实例的主目录中打开globals文件，并将上述schema配置到参数中：ggschema5. 修改ddl对象的名称，这个步骤是可选的，而且oracle建议使用默认名称。6. 进入goldengate安装目录，断开所有数据库会话，并确保没有新的会话连接。7. 用sysdba运行sql*plus，安装ddl触发器需要这个权限，触发器会被

11、安装在sysschema中。8. 执行marker_setup脚本，该脚本安装了ddl支持需要的goldengatemarker系统，脚本执行过程中会要求输入goldengateschema的名称在源端，以oracle用户登录sqlplus，执行以下脚本执行marker_setup确保goldengate的相关进程关闭状态，任何使用oracle的应用程序都已关闭，且不会有新的会话产生。然后执行下面命令goldengategg1$cd/opt/gg/goldengate/goldengategg1goldengate$sqlplus/assysdbasqlmarker_setupmarkerse

12、tupscriptyouwillbepromptedforthenameofaschemaforthegoldengatedatabaseobjects.note:theschemamustbecreatedpriortorunningthisscript.note:stopallddlreplicationbeforestartingthisinstallation.entergoldengateschemaname:goldengatescriptcomplete.sql9. 执行ddl_setup脚本确保所有的会话都已关闭sqlddl_setupgoldengateddlreplicat

13、ionsetupscriptverifyingthatcurrentuserhasprivilegestoinstallddlreplication.checkingusersessions.checkcomplete.youwillbepromptedforthenameofaschemaforthegoldengatedatabaseobjects.note:foranoracle10gsource,thesystemrecyclebinmustbedisabled.fororacle11gandlater,itcanbeenabled.note:theschemamustbecreate

14、dpriortorunningthisscript.note:stopallddlreplicationbeforestartingthisinstallation.entergoldengateschemaname:goldengateyouwillbepromptedforthemodeofinstallation.toinstallorreinstallddlreplication,enterinitialsetuptoupgradeddlreplication,enternormalentermodeofinstallation:initialsetupworking,pleasewa

15、it.spoolingtofileddl_setup_spool.txtusinggoldengateasagoldengateschemaname,initialsetupasamodeofinstallation.working,pleasewait.recyclebinmustbeempty.thisinstallationwillpurgerecyclebinforallusers.toproceed,enteryes.tostopinstallation,enterno.enteryesorno:yesscriptcomplete.sql10. 执行role_setup脚本。该脚本删

16、除和创建ddl同步需要的角色，它授权ddl对象上的dml操作创建一个名为ggs_ggsuser_role的角色，包含了ddlobjects需要的权限sqlrole_setupggsrolesetupscriptthisscriptwilldropandrecreatetheroleggs_ggsuser_roletouseadifferentrolename,quitthisscriptandthenedittheparams.sqlscripttochangethegg_roleparametertothepreferredname.(donotrunthescript.)youwillbe

17、promptedforthenameofaschemaforthegoldengatedatabaseobjects.note:theschemamustbecreatedpriortorunningthisscript.note:stopallddlreplicationbeforestartingthisinstallation.entergoldengateschemaname:goldengatewrotefilerole_setup_set.txtpl/sqlproceduresuccessfullycompleted.rolesetupscriptcompletegrantthis

18、roletoeachuserassignedtotheextract,ggsci,andmanagerprocesses,byusingthefollowingsqlcommand:grantggs_ggsuser_roletowhereistheuserassignedtothegoldengateprocesses.sql11. 将该角色赋给goldengate抽取用户。如果这些进程使用了不同的用户，你需要为这些用户分别授权sqlgrantggs_ggsuser_roletogoldengate;grantsucceeded.sql12. 执行ddl_enable.sql脚本来启用ddl触

19、发器sqlddl_enabletriggeraltered.sql为了改进ddl触发器的性能，可以在数据库启动时，在goldengate安装目录下使用sysdba执行ddl_pin脚本sqlddl_pin该脚本会将该ddl触发器使用的pl/sql包固定到内存中。它依赖于dbms_shared_pool系统包，使用ddl_pin前确认该包已经安装如果系统中不存在dbms_shared_pool包，则手动执行脚本安装。如下sqlselectobject_name,object_typefromdba_objectswhereobject_name=dbms_shared_pool;norowsse

20、lectedsql?/rdbms/admin/dbmspoolpackagecreated.grantsucceeded.viewcreated.packagebodycreated.sqlselectobject_name,object_typefromall_objectswhereobject_name=dbms_shared_pool;object_nameobject_type-dbms_shared_poolpackagedbms_shared_poolpackagebody2rowsselected.ddl_pinddl_pin将触发器用到的plsql包放进内存中sqlddl_p

21、ingoldengatepl/sqlproceduresuccessfullycompleted.pl/sqlproceduresuccessfullycompleted.pl/sqlproceduresuccessfullycompleted.sql创建golden gate所需的子文件夹配置管理进程参数输入mgrservname $severnameggschemagoldengate保存文件。安装管理进程(windows环境)在dos命令行下运行install addservice addevents配置mgr管理进程参数输入port 7809，保存文件。7809为默认端口，需要确保该端

22、口未被占用。其它参数分类名称取值作用database user parameterdatabase user parameterif using goldengate ddl support, specify the manager user and password with the useridparameter.dynamic port parametersdynamicportlistdynamicportlist | - , .use the dynamicportlist parameter to specify up to 256 unreserved, unrestricted

23、 ports fordynamic tcp/ip communications between the source and target systems. the collector,replicat, and ggsci processes will use these portsdynamicportreassigndelaydynamicportreassigndelay use the dynamicportreassigndelay parameter to control how long manager waits to assigna port that was assign

24、ed before.autostart parametersautostartautorestart er | extract | replicat group name | wildcard, retries , waitminutes , resetminutes use the autostart parameter to start extract and replicat processes when manager starts.this can be useful, for example, if you want goldengate activities to begin i

25、mmediatelywhen you start the system, assuming manager is part of the startup routine.trail maintenance parameterpurgeoldextractsuse the purgeoldextracts parameter in a manager parameter file to purge trail files whengoldengate has finished processing them. without using purgeoldextracts, no purging

26、isperformed, and trail files can consume significant disk spaceoracle rac的mgr参数配置文件如下：ggsci(gg1)1editparamsmgrdynamicportlist7840-7914-配置了goldengate(extract和replicat)进程使用的端口范围port5898-指定manager使用的端口-当根据checkpoint发现已经完成抽取和复制的trail文件将被自动删除，但保留最近10个purgeoldextracts/opt/gg/trails/w1*,usecheckpoints,mink

27、eepfiles10purgeoldextracts/opt/gg/trails/w2*,usecheckpoints,minkeepfiles10-使抽取/复制进程失败后自动重启autorestart er*,retries3,waitminutes5-分别删除ddl历史表和marker表中的过期数据，以控制它们不会变得过于庞大。purgeddlhistoryminkeepdays3,maxkeepdays5,frequencyminutes30purgemarkerhistoryminkeepdays3,maxkeepdays5,frequencyminutes30启动mgr进程manag

28、er must be running before you start other goldengate processesto start manager from ggsci1. from the goldengate directory, run ggsci.2. in ggsci, issue the following command.start managerto stop manager1. from the goldengate directory, run ggsci.2. in ggsci, issue the following command.stop manager

29、!where: ! stops manager without user confirmation.配置extractin the manager parameter file, use the purgeoldextracts parameter to control the purging of files from the trailto configure the primary extract group on the source1. add extract , tranlog, begin , threads 2. on the source, use the add exttr

30、ail command to create a local trail add exttrail , extract 3. use the edit params command to create a parameter file for the ext_1 extract group.- identify the extract group:extract - specify database login information as needed for the database:sourcedb ,userid , password - specify the local trail

31、that this extract writes to:exttrail - specify tables to be captured:table .;示例命令如下：l add extract extsp, tranlog,begin nowl add exttrail ./dirdat/ex, extract extsp, megabytes 10l edit param extspextract extspuserid xgt,password xgtexttrail ./dirdat/extable xgt.tbldict;for oracle11g racl add extract

32、extsp, tranlog,begin now，threads 2l add exttrail ./dirdat/ex, extract extsp, megabytes 10l edit param extspextract extspuserid xgt,password xgtexttrail ./dirdat/extranlogoptions altarchivelogdest instance wlcbhrss1 +archdata/,altarchivelogdest instance wlcbhrss2 +data/wlcbhrss/archive/arch2tranlogop

33、tionsaltarchivedlogformat%t_%s_%r.dbftranlogoptions asmuser sysasm1, asmpassword wlcbhrssddloptionsaddtrandata-当创建新的表时，自动为其启用追加日志-长事务警告:超过1个小时的长事务，5分钟警告一次warnlongtrans1h,checkinterval5mddl include mapped-采用了ddl include mapped的ddl同步方式table xgt.tbldict;to configure the data pump on the source1. use th

34、e add extract command to create a data pump groupadd extract , exttrailsource , begin 2. use the add rmttrail command to specify a remote trail that will be created on the second system in the cascadeadd rmttrail , extract use the extract argument to link the remote trail to the pump_1 data pump gro

35、up. the linked data pump writes to this trail.3. use the edit params command to create a parameter file for the pump_1 data pump- identify the data pump group:extract - specify database login information as needed for the database:sourcedb ,userid , password - specify the name or ip address of secon

36、d system in cascade:rmthost , mgrport - specify the remote trail on the second system:rmttrail - allow mapping, filtering, conversion or pass data through as-is:passthru | nopassthru- specify tables to be captured:table .;示例命令如下：l add extract extpump,exttrailsource ./dirdat/ex,begin nowl add rmttrai

37、l ./dirdat/sp, extract extpump,megabytes 10l edit param extpumpextract extpumpdynamicresolutionuserid xgt,password xgtrmthost 192.168.139.128,mgrport 7809rmttrail ./dirdat/spnumfiles 300table xgt.tbldict;启动extractstart extspstart extpump目标端配置创建golden gate所需的子文件夹配置管理进程参数输入mgrservname $severnameggsche

38、magoldengatecheckpointtable target.ggschkpt保存文件。创建checkpoint表replicat通过这个表来维护trail文件中的readposition。这不是个必须的操作，如果没有这个表，则通过一个磁盘文件来维护ggsci(ggdb)2dbloginuseridgoldengate,passwordgoldengatesuccessfullyloggedintodatabase.ggsci(ggdb)3addcheckpointtablegoldengate.chkpointsuccessfullycreatedcheckpointtablegol

39、dengate.chkpoint配置mgr管理进程参数输入port 7809，保存文件。7809为默认端口，需要确保该端口未被占用。其它参数分类名称取值作用database user parameterdatabase user parameterif using goldengate ddl support, specify the manager user and password with the useridparameter.dynamic port parametersdynamicportlistdynamicportlist | - , .use the dynamicport

40、list parameter to specify up to 256 unreserved, unrestricted ports fordynamic tcp/ip communications between the source and target systems. the collector,replicat, and ggsci processes will use these portsdynamicportreassigndelaydynamicportreassigndelay use the dynamicportreassigndelay parameter to co

41、ntrol how long manager waits to assigna port that was assigned before.autostart parametersautostartautorestart er | extract | replicat group name | wildcard, retries , waitminutes , resetminutes use the autostart parameter to start extract and replicat processes when manager starts.this can be usefu

42、l, for example, if you want goldengate activities to begin immediatelywhen you start the system, assuming manager is part of the startup routine.trail maintenance parameterpurgeoldextractsuse the purgeoldextracts parameter in a manager parameter file to purge trail files whengoldengate has finished

43、processing them. without using purgeoldextracts, no purging isperformed, and trail files can consume significant disk space启动mgr进程manager must be running before you start other goldengate processesto start manager from ggsci1. from the goldengate directory, run ggsci.2. in ggsci, issue the following

44、 command.start managerto stop manager1. from the goldengate directory, run ggsci.2. in ggsci, issue the following command.stop manager !where: ! stops manager without user confirmation.配置replicatin the manager parameter file, use the purgeoldextracts parameter to control the purging of files from th

45、e trail.to configure the manager process on the second system1. create a replicat checkpoint table. this is a best practice.2. use the add replicat command to create a replicat groupadd replicat , exttrail , begin use the exttrail option to link the rep_1 group to the remote trail remote_trail_1that

46、 is on the local system.3. use the edit params command to create a parameter file for the replicat group.- identify the replicat group:replicat - state whether or not source and target definitions are identical:sourcedefs | assumetargetdefs- specify database login information as needed for the datab

47、ase:targetdb , userid , password - specify error handling rules:reperror (, )- specify tables for delivery:map ., target . , def ;示例命令如下：l add replicat repsp, exttrail ./dirdat/sp,begin nowl edit param repspreplicat repspuserid xgt,password xgt-源端与目标端表结构一致时使用assumetargetdefsdiscardfile ./dirrpt/rora

48、01.dsc, appenddynamicresolution-support ddl herddl include mapped-当出现ddl错误，重试5次，时间间隔为20秒。如果失败，replicat会继续运行，但相关信息会记录在-discardfile中ddlerrordefaultdiscardretryopmaxretries5retrydelay20ddloptionsreport-将ddl的具体信息写入到报告文件中maxtransops 1000batchsql batchesperqueue 100,opsperbatch 5000numfiles 2000map xgt.tb

49、ldict, target xgt.tbldict;注意：target与逗号之间一定要有个空格启动replicatstart repsp通过expdp/impdp来迁移初始数据准备工作1.禁用ddl的抽取和复制在extract和replicat参数中将ddl去掉。实际上，若能保证在expdp导出数据期间不会有ddl操作，可以忽略此项2.在replicat中使用handlecollisions参数。每一个目标表都必须由一个主键或唯一键。如果没有，使用keycols选项来代替，如果不能指定，祈祷不会出现两条完全一样的记录吧.在源端和目标端打开manager在源端和目标端执行ggsci命令startmgr，启