详解DDL同步配置.docx
- 文档编号:23647993
- 上传时间:2023-05-19
- 格式:DOCX
- 页数:15
- 大小:19.27KB
详解DDL同步配置.docx
《详解DDL同步配置.docx》由会员分享,可在线阅读,更多相关《详解DDL同步配置.docx(15页珍藏版)》请在冰豆网上搜索。
详解DDL同步配置
详解DDL同步配置
在配置DDL同步之前,建议先动手配置DML的同步环境,如果你还不会配置GoldenGate–DML环境,可以参考OracleGoldenGateforOracle11g(Solaris10x86-64)toOracle10g(CentOS4.8)configuration这篇博文。
DDL复制与DML复制的机制原理是完全不同的,DDL基于trigger,DML基于捕获日志,二者的数据捕获是没有任何关系的。
也就是说DDL和DML复制是相互独立,DDL启动后无论DML复制是否运行都不会影响DDL复制的正常工作,反过来说DDL的启动或者停止也不会影响到DML。
它们只是在extract启动时根据SCN号进行排序,没有其他任何联系。
DDL复制只是简单的SQL复制,通过trigger捕获DDL抓取原始的SQL语句发送到目标端重新执行一遍。
为了清楚分别源端和目标端机器
红色字体代表源端操作
蓝色字体代表目标端操作
分别在源端和目标端安装GoldenGate10,此过程省略,不知道如何安装的请看OracleGoldenGateforOracle11g(Solaris10x86-64)toOracle10g(CentOS4.8)configuration这篇博文。
关闭源端数据库的recyclebin
[oracle@gg01~]$sqlplus/assysdbaSQL*Plus:
Release10.2.0.1.0-ProductiononSunJan1512:
49:
392012
Copyright(c)1982,2005,Oracle.Allrightsreserved.
Connectedto:
OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-Production
WiththePartitioning,OLAPandDataMiningoptions
SYS@gg01:
~>showparameterrecyclebin
NAMETYPEVALUE
-----------------------------------------------------------------------------
recyclebinstringon
SYS@gg01:
~>altersystemsetrecyclebin=off;
Systemaltered.
SYS@gg01:
~>showparameterrecyclebin
NAMETYPEVALUE
-----------------------------------------------------------------------------
recyclebinstringOFF
创建DDL复制用户
SYS@gg01:
~>createuserggadminidentifiedbyggadmindefaulttablespaceusers;
Usercreated.
SYS@gg01:
~>grantdbatoggadmin;
Grantsucceeded.
SYS@gg01:
~>
编辑ogg的globals参数,指定DDL用户
GGSCI(gg01)3>viewparams./GLOBALS
ggschemaggadmin
安装DDL对象,运行marker_setup.sql脚本,提示输入OGG用户,这里是之前定义的ggadmin
SYS@gg01:
~>@marker_setup
Markersetupscript
YouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.
NOTE:
Theschemamustbecreatedpriortorunningthisscript.
NOTE:
StopallDDLreplicationbeforestartingthisinstallation.
EnterGoldenGateschemaname:
ggadmin
Markersetuptablescriptcomplete,runningverificationscript...
PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:
SettingschemanametoGGADMIN
MARKERTABLE
-------------------------------
OK
MARKERSEQUENCE
-------------------------------
OK
Scriptcomplete.
SYS@gg01:
~>
运行ddl_setup.sql脚本,提示输入用户:
ggadmin
SYS@gg01:
~>@ddl_setup
GoldenGateDDLReplicationsetupscript
VerifyingthatcurrentuserhasprivilegestoinstallDDLReplication...
YouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.
NOTE:
Theschemamustbecreatedpriortorunningthisscript.
NOTE:
OnOracle10gandup,systemrecyclebinmustbedisabled.
NOTE:
StopallDDLreplicationbeforestartingthisinstallation.
EnterGoldenGateschemaname:
ggadmin
Youwillbepromptedforthemodeofinstallation.
ToinstallorreinstallDDLreplication,enterINITIALSETUP
ToupgradeDDLreplication,enterNORMAL
Entermodeofinstallation:
INITIALSETUP
Working,pleasewait...
Spoolingtofileddl_setup_spool.txt
UsingGGADMINasaGoldenGateschemaname,INITIALSETUPasamodeofinstallation.
Working,pleasewait...
RECYCLEBINmustbeempty.
ThisinstallationwillpurgeRECYCLEBINforallusers.
Toproceed,enteryes.Tostopinstallation,enterno.
Enteryesorno:
yes
DDLreplicationsetupscriptcomplete,runningverificationscript...
PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:
SettingschemanametoGGADMIN
DDLORA_GETTABLESPACESIZESTATUS:
Line/pos
--------------------
Error
-----------------------------------------------------------------
Noerrors
Noerrors
CLEAR_TRACESTATUS:
Line/pos
--------------------
Error
-----------------------------------------------------------------
Noerrors
Noerrors
CREATE_TRACESTATUS:
Line/pos
--------------------
Error
-----------------------------------------------------------------
Noerrors
Noerrors
TRACE_PUT_LINESTATUS:
Line/pos
--------------------
Error
-----------------------------------------------------------------
Noerrors
Noerrors
INITIAL_SETUPSTATUS:
Line/pos
--------------------
Error
-----------------------------------------------------------------
Noerrors
Noerrors
DDLVERSIONSPECIFICPACKAGESTATUS:
Line/pos
--------------------
Error
-----------------------------------------------------------------
Noerrors
Noerrors
DDLREPLICATIONPACKAGESTATUS:
Line/pos
--------------------
Error
-----------------------------------------------------------------
Noerrors
Noerrors
DDLREPLICATIONPACKAGEBODYSTATUS:
Line/pos
--------------------
Error
-----------------------------------------------------------------
Noerrors
Noerrors
DDLHISTORYTABLE
-----------------------------------
OK
DDLHISTORYTABLE
(1)
-----------------------------------
OK
DDLDUMPTABLES
-----------------------------------
OK
DDLDUMPCOLUMNS
-----------------------------------
OK
DDLDUMPLOGGROUPS
-----------------------------------
OK
DDLDUMPPARTITIONS
-----------------------------------
OK
DDLDUMPPRIMARYKEYS
-----------------------------------
OK
DDLSEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDLTRIGGERCODESTATUS:
Line/pos
--------------------
Error
-----------------------------------------------------------------
Noerrors
Noerrors
DDLTRIGGERINSTALLSTATUS
-----------------------------------
OK
DDLTRIGGERRUNNINGSTATUS
----------------------------------------------------------------------
ENABLED
STAYMETADATAINTRIGGER
----------------------------------------------------------------------
OFF
DDLTRIGGERSQLTRACING
----------------------------------------------------------------------
0
DDLTRIGGERTRACELEVEL
----------------------------------------------------------------------
0
LOCATIONOFDDLTRACEFILE
--------------------------------------------------------------------------------
/u01/app/oracle/admin/gg01/udump/ggs_ddl_trace.log
Analyzinginstallationstatus...
STATUSOFDDLREPLICATION
--------------------------------------------------------------------------------
SUCCESSFULinstallationofDDLReplicationsoftwarecomponents
Scriptcomplete.
SYS@gg01:
~>
创建DDL复制角色,运行role_setup.sql脚本
SYS@gg01:
~>@role_setup
GGSRolesetupscript
ThisscriptwilldropandrecreatetheroleGGS_GGSUSER_ROLE
Touseadifferentrolename,quitthisscriptandthenedittheparams.sqlscripttochangethegg_roleparametertothepreferredname.(Donotrunthescript.)
YouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.
NOTE:
Theschemamustbecreatedpriortorunningthisscript.
NOTE:
StopallDDLreplicationbeforestartingthisinstallation.
EnterGoldenGateschemaname:
ggadmin
Wrotefilerole_setup_set.txt
PL/SQLproceduresuccessfullycompleted.
Rolesetupscriptcomplete
GrantthisroletoeachuserassignedtotheExtract,GGSCI,andManagerprocesses,byusingthefollowingSQLcommand:
GRANTGGS_GGSUSER_ROLETOloggedUser
whereloggedUseristheuserassignedtotheGoldenGateprocesses.
SYS@gg01:
~>
开启DDL功能,运行ddl_enable.sql脚本
SYS@gg01:
~>@ddl_enable
Triggeraltered.
SYS@gg01:
~>
验证DDL脚本的安装,运行marker_status.sql脚本
SYS@gg01:
~>@marker_status
PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:
ggadmin
SettingschemanametoGGADMIN
MARKERTABLE
-------------------------------
OK
MARKERSEQUENCE
-------------------------------
OK
SYS@gg01:
~>
配置DDL复制,修改extract参数
GGSCI(gg01)5>viewparamseorajj
EXTRACTEORAJJ
USERIDsystem,PASSWORDoracle
RMTHOSTgg02,MGRPORT7809
RMTTRAIL/u01/app/oracle/goldengate/dirdat/jj
DDLINCLUDEALL
--DDLERRORRESTARTSKIP100000SKIPTRIGGERERROR100000
DDLOPTIONSADDTRANDATA,REPORT
TABLEscott.*;
SEQUENCEscott.*;
GGSCI(gg01)6>
修改replicat参数
REPLICATRORAJJ
USERIDsystem,PASSWORDoracle
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE/u01/app/oracle/goldengate/dirrpt/RORAJJ.DSC,PURGE
DDLINCLUDEMAPPED
--DDLERRORDEFAULTIGNORERETRYOP
DDLOPTIONSREPORT
MAPscott.*,TARGETscott.*;
分别启动源端extract和目标端replicat进程
GGSCI(gg01)15>startextracteorajj
SendingSTARTrequesttoMANAGER...
EXTRACTEORAJJstarting
GGSCI(gg01)16>infoall
ProgramStatusGroupLagTimeSinceChkpt
MANAGERRUNNING
EXTRACTRUNNINGEORAJJ00:
00:
0000:
14:
39
GGSCI(gg02)7>startreplicatrorajj
SendingSTARTrequesttoMANAGER...
REPLICATRORAJJstarting
GGSCI(gg02)8>infoall
ProgramStatusGroupLagTimeSinceChkpt
MANAGERRUNNING
REPLICATRUNNINGRORAJJ00:
00:
0000:
00:
01
先验证DML操作,在源端插入数据
SCOTT@gg01:
~>insertintobonusvalues('ZWC','10','1000',10);
1rowcreated.
SCOTT@gg01:
~>commit;
Commitcomplete.
SCOTT@gg01:
~>
在目标端验证DML操作
SCOTT@gg02:
~>select*frombonuswhereename='ZWC';
ENAMEJOBSALCOMM
---------------------------------------
ZWC10100010
SCOTT@gg02:
~>
在目标端使用logdump查看
[oracle@gg02goldengate]$logdump
OracleGoldenGateLogFileDumpUtility
Version10.4.0.19Build002
Copyright(C)1995,2009,Oracleand/oritsaffiliates.Allrightsreserved.
Logdump12>open/u01/app/oracle/goldengate/dirdat/jj000000
CurrentLogTrailis/u01/app/oracle/goldengate/dirdat/jj000000
Logdump13>ghdron
Logdump14>headertokenon
Logdump15>usertokenon
Logdump16>detailon
Logdump17>detaildata
Logdump18>n
TokenIDx46'F'RecordHeaderInfoxff80Length927
TokenIDx30'0'TrailInfoInfox00Length442
TokenIDx31'1'MachineInfoInfox00Length85
TokenIDx32'2'DatabaseInfoInfox00Length283
TokenIDx33'3'ProducerInfoInfox00Length81
TokenIDx34'4'ContinunityInfoInfox00Length8
TokenIDx5a'Z'RecordTrailerInfoxff80Length927
2012/01/1513:
32:
52.243.607FileHeaderLen919RBA0
Name:
*FileHeader*
300001ba3000000847470d0a544c0a0d31000002|0...0...GG.
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 详解 DDL 同步 配置