配置GoldenGate同步DDL语句.docx
- 文档编号:3790774
- 上传时间:2022-11-25
- 格式:DOCX
- 页数:12
- 大小:19.60KB
配置GoldenGate同步DDL语句.docx
《配置GoldenGate同步DDL语句.docx》由会员分享,可在线阅读,更多相关《配置GoldenGate同步DDL语句.docx(12页珍藏版)》请在冰豆网上搜索。
配置GoldenGate同步DDL语句
配置GoldenGate同步DDL语句
(1)
December6,2010
在配置Goldengate同步DDL语句前,要求在数据库内完成一系列的预安装先题步骤;其中包括建立合适的Goldengate用户,如:
createusermacleanidentifiedbymaclean;
grantdbatomaclean;
/*之后maclean用户将用以配置extract和pump*/
之后我们需要执行一系列同步DLL语句要用到的GoldenGate脚本,这些脚本存放在GG的安装目录下:
[maclean@rh2gg]$pwd
/home/maclean/gg
[maclean@rh2gg]$ls-ldd*.sql
-r--r--r--1macleanoinstall10592010-03-12ddl_cleartrace.sql
-r--r--r--1macleanoinstall41892010-03-12ddl_ddl2file.sql
-r--r--r--1macleanoinstall7462010-03-12ddl_disable.sql
-r--r--r--1macleanoinstall6922010-03-12ddl_enable.sql
-r--r--r--1macleanoinstall3882010-03-12ddl_nopurgeRecyclebin.sql
-r--r--r--1macleanoinstall124242010-05-21ddl_ora10.sql
-r--r--r--1macleanoinstall38632010-05-21ddl_ora10upCommon.sql
-r--r--r--1macleanoinstall110642010-03-12ddl_ora11.sql
-r--r--r--1macleanoinstall123652010-03-12ddl_ora9.sql
-r--r--r--1macleanoinstall10262010-03-12ddl_pin.sql
-r--r--r--1macleanoinstall12272010-03-12ddl_purgeRecyclebin.sql
-r--r--r--1macleanoinstall36862010-05-13ddl_remove.sql
-r--r--r--1macleanoinstall4252009-06-29ddl_session1.sql
-r--r--r--1macleanoinstall10532009-06-23ddl_session.sql
-r-xr-xr-x1macleanoinstall22860606-2913:
48ddl_setup.sql
-r--r--r--1macleanoinstall88722010-03-12ddl_status.sql
-r--r--r--1macleanoinstall25062010-03-12ddl_staymetadata_off.sql
-r--r--r--1macleanoinstall25012010-03-12ddl_staymetadata_on.sql
-r--r--r--1macleanoinstall29552010-03-12ddl_tracelevel.sql
-r--r--r--1macleanoinstall25432010-03-12ddl_trace_off.sql
-r--r--r--1macleanoinstall28622010-03-12ddl_trace_on.sql
/*执行goldengateddl同步安装脚本要求以SYSDBA身份登录*/
[maclean@rh2gg]$sqlplus/assysdba
SQL*Plus:
Release10.2.0.4.0-ProductiononMonDec618:
01:
462010
Copyright(c)1982,2007,Oracle.AllRightsReserved.
Connectedto:
OracleDatabase10gEnterpriseEditionRelease10.2.0.4.0-64bitProduction
WiththePartitioning,OLAP,DataMiningandRealApplicationTestingoptions
SQL>altersystemsetrecyclebin=off;
Systemaltered.
/*同步DDL要求我们关闭10g中的回收站特性*/
SQL>@marker_setup
Markersetupscript
YouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.
NOTE:
Theschemamustbecreatedpriortorunningthisscript.
NOTE:
StopallDDLreplicationbeforestartingthisinstallation.
EnterGoldenGateschemaname:
maclean
Markersetuptablescriptcomplete,runningverificationscript...
PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:
SettingschemanametoMACLEAN
MARKERTABLE
-------------------------------
OK
MARKERSEQUENCE
-------------------------------
OK
Scriptcomplete.
/*以SYSDBA登陆数据库并执行当前GG安装目录下的market_setup脚本,该脚本用以建立一个DDL标记表
包括以下列:
seqNoNUMBERNOTNULL,--sequencenumber
fragmentNoNUMBERNOTNULL,--fragmentnumber(messagedividedintofragments)
optimeCHAR(19)NOTNULL,--timeofoperation
TYPEVARCHAR2(100)NOTNULL,--typeofmarker
SUBTYPEVARCHAR2(100)NOTNULL,--subtypeofmarker
marker_textVARCHAR2(4000)NOTNULL,
--fragmenttext(messagedividedintofragmentsnumberedwithfragmentNo)
*/
SQL>@ddl_setup
GoldenGateDDLReplicationsetupscript
VerifyingthatcurrentuserhasprivilegestoinstallDDLReplication...
Checkingusersessions...
Checkcomplete.
YouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.
NOTE:
ForanOracle10gsource,thesystemrecyclebinmustbedisabled.ForOracle11gandlater,itcanbeenabled.
NOTE:
Theschemamustbecreatedpriortorunningthisscript.
NOTE:
StopallDDLreplicationbeforestartingthisinstallation.
EnterGoldenGateschemaname:
maclean
Youwillbepromptedforthemodeofinstallation.
ToinstallorreinstallDDLreplication,enterINITIALSETUP
ToupgradeDDLreplication,enterNORMAL
Entermodeofinstallation:
INITIALSETUP
Working,pleasewait...
Spoolingtofileddl_setup_spool.txt
UsingMACLEANasaGoldenGateschemaname,INITIALSETUPasamodeofinstallation.
Working,pleasewait...
RECYCLEBINmustbeempty.
ThisinstallationwillpurgeRECYCLEBINforallusers.
Toproceed,enteryes.Tostopinstallation,enterno.
Enteryesorno:
yes
DDLreplicationsetupscriptcomplete,runningverificationscript...
PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:
SettingschemanametoMACLEAN
DDLORA_GETTABLESPACESIZESTATUS:
Line/posError
---------------------------------------------------------------------------
NoerrorsNoerrors
CLEAR_TRACESTATUS:
Line/posError
---------------------------------------------------------------------------
NoerrorsNoerrors
CREATE_TRACESTATUS:
Line/posError
---------------------------------------------------------------------------
NoerrorsNoerrors
TRACE_PUT_LINESTATUS:
Line/posError
---------------------------------------------------------------------------
NoerrorsNoerrors
INITIAL_SETUPSTATUS:
Line/posError
---------------------------------------------------------------------------
NoerrorsNoerrors
DDLVERSIONSPECIFICPACKAGESTATUS:
Line/posError
---------------------------------------------------------------------------
NoerrorsNoerrors
DDLREPLICATIONPACKAGESTATUS:
Line/posError
---------------------------------------------------------------------------
NoerrorsNoerrors
DDLREPLICATIONPACKAGEBODYSTATUS:
Line/posError
---------------------------------------------------------------------------
NoerrorsNoerrors
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/posError
---------------------------------------------------------------------------
NoerrorsNoerrors
DDLTRIGGERINSTALLSTATUS
-----------------------------------
OK
DDLTRIGGERRUNNINGSTATUS
-----------------------------------
ENABLED
STAYMETADATAINTRIGGER
-----------------------------------
OFF
DDLTRIGGERSQLTRACING
-----------------------------------
0
DDLTRIGGERTRACELEVEL
-----------------------------------
0
LOCATIONOFDDLTRACEFILE
--------------------------------------------------------------------------------
/s01/10gdb/admin/clinica/udump/ggs_ddl_trace.log
Analyzinginstallationstatus...
STATUSOFDDLREPLICATION
--------------------------------------------------------------------------------
SUCCESSFULinstallationofDDLReplicationsoftwarecomponents
Scriptcomplete.
/*以INITIALSETUP选项运行ddl_setup.sql将在数据库中创建捕获DDL语句的Trigger等必要组件*/
SQL>@role_setup
GGSRolesetupscript
ThisscriptwilldropandrecreatetheroleGGS_GGSUSER_ROLE
Touseadifferentrolename,quitthisscriptandthenedittheparams.sqlscriptto
changethegg_roleparametertothepreferredname.(Donotrunthescript.)
YouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.
NOTE:
Theschemamustbecreatedpriortorunningthisscript.
NOTE:
StopallDDLreplicationbeforestartingthisinstallation.
EnterGoldenGateschemaname:
maclean
Wrotefilerole_setup_set.txt
PL/SQLproceduresuccessfullycompleted.
Rolesetupscriptcomplete
GrantthisroletoeachuserassignedtotheExtract,GGSCI,andManagerprocesses,byusingthefollowingSQLcommand:
GRANTGGS_GGSUSER_ROLETO
whereistheuserassignedtotheGoldenGateprocesses.
/*role_setup脚本用以建立GGS_GGSUSER_ROLE角色*/
我们需要将该GGS_GGSUSER_ROLE授予给extractgroup参数中定义的userid用户
SQL>grantGGS_GGSUSER_ROLEtomaclean;
Grantsucceeded.
SQL>@ddl_enable
Triggeraltered.
/*ddl_enable.sql将正式enableddl捕获触发器,即:
ALTERTRIGGERsys.&ddl_trigger_nameENABLE;*/
SQL>@?
/rdbms/admin/dbmspool
Packagecreated.
Grantsucceeded.
Viewcreated.
Packagebodycreated.
/*执行dbmspool包将在数据库中创建DBMS_SHARED_POOL包,之后需要用到*/
SQL>@ddl_pin
Entervaluefor1:
maclean
PL/SQLproceduresuccessfullycompleted.
Entervaluefor1:
maclean
PL/SQLproceduresuccessfullycompleted.
PL/SQLproceduresuccessfullycompleted.
/*ddl_pin.sql通过dbms_shared_pool.keep存储过程将DDLReplication相关的对象keep在共享池中,
以保证这些对象不要reload,提升性能
*/
以上脚本都运行完成后,DDL语句同步的先题条件就达成了。
接着我们要来配置GG部分的extract和replicat:
[maclean@rh2gg]$ggsci
OracleGoldenGateCommandInterpreterforOracle
Version11.1.1.0.0Build078
Linux,x64,64bit(optimized),Oracle10onJul28201013:
21:
11
Copyright(C)1995,2010,Oracleand/oritsaffiliates.Allrightsreserved.
GGSCI()1>addextractload1,tranlog,beginnow
EXTRACTadded.
GGSCI()2>addrmttrail/s01/rmt/ma,megabytes100,extractload1
RMTTRAILadded.
GGSCI()5>encryptpasswordmaclean
Nokeyspecified,usingdefaultkey...
Encryptedpassword:
AACAAAAAAAAAAAHANCUEFHPAVCYDNJVD
GGSCI()16>startextractload1
SendingSTARTrequesttoMANAGER...
EXTRACTLOAD1starting
GGSCI()19>viewparamsload1
extractload1
useridmaclean,passwordAACAAAAAAAAAAAHANCUEFHPAVCYDNJVD,encryptkeydefault
RMTHOST,MGRPORT7809
RMTTRAIL/s01/rmt/ma
DDLINCLUDEMAPPED
Tablesender.*;
/*以上为源端的extractload1的配置,采用了DDLINCLUDEMAPPED的DDL同步方式*/
GGSCI()59>addreplicatrep1,exttrail/s01/rmt/ma,beginnow,checkpointtablemaclean.checkpoint
REPLICATadded.
GGSCI()62>viewparamsrep1
replicatrep1
useridmaclean,passwordmaclean
ASSUMETAR
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 配置 GoldenGate 同步 DDL 语句
![提示](https://static.bdocx.com/images/bang_tan.gif)