配置GoldenGate同步DDL语句Word格式.docx

配置GoldenGate同步DDL语句Word格式.docx

《配置GoldenGate同步DDL语句Word格式.docx》由会员分享，可在线阅读，更多相关《配置GoldenGate同步DDL语句Word格式.docx（12页珍藏版）》请在冰豆网上搜索。

-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中的回收站特性*/

@marker_setup

Markersetupscript

YouwillbepromptedforthenameofaschemafortheGoldenGatedatabaseobjects.

NOTE:

Theschemamustbecreatedpriortorunningthisscript.

StopallDDLreplicationbeforestartingthisinstallation.

EnterGoldenGateschemaname:

maclean

Markersetuptablescriptcomplete,runningverificationscript...

PleaseenterthenameofaschemafortheGoldenGatedatabaseobjects:

SettingschemanametoMACLEAN

MARKERTABLE

-------------------------------

OK

MARKERSEQUENCE

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）

*/

@ddl_setup

GoldenGateDDLReplicationsetupscript

VerifyingthatcurrentuserhasprivilegestoinstallDDLReplication...

Checkingusersessions...

Checkcomplete.

ForanOracle10gsource,thesystemrecyclebinmustbedisabled.ForOracle11gandlater,itcanbeenabled.

Youwillbepromptedforthemodeofinstallation.

ToinstallorreinstallDDLreplication,enterINITIALSETUP

ToupgradeDDLreplication,enterNORMAL

Entermodeofinstallation:

INITIALSETUP

Working,pleasewait...

Spoolingtofileddl_setup_spool.txt

UsingMACLEANasaGoldenGateschemaname,INITIALSETUPasamodeofinstallation.

RECYCLEBINmustbeempty.

ThisinstallationwillpurgeRECYCLEBINforallusers.

Toproceed,enteryes.Tostopinstallation,enterno.

Enteryesorno:

yes

DDLreplicationsetupscriptcomplete,runningverificationscript...

DDLORA_GETTABLESPACESIZESTATUS:

Line/posError

---------------------------------------------------------------------------

NoerrorsNoerrors

CLEAR_TRACESTATUS:

CREATE_TRACESTATUS:

TRACE_PUT_LINESTATUS:

INITIAL_SETUPSTATUS:

DDLVERSIONSPECIFICPACKAGESTATUS:

DDLREPLICATIONPACKAGESTATUS:

DDLREPLICATIONPACKAGEBODYSTATUS:

DDLHISTORYTABLE

-----------------------------------

DDLHISTORYTABLE

（1）

DDLDUMPTABLES

DDLDUMPCOLUMNS

DDLDUMPLOGGROUPS

DDLDUMPPARTITIONS

DDLDUMPPRIMARYKEYS

DDLSEQUENCE

GGS_TEMP_COLS

GGS_TEMP_UK

DDLTRIGGERCODESTATUS:

DDLTRIGGERINSTALLSTATUS

DDLTRIGGERRUNNINGSTATUS

ENABLED

STAYMETADATAINTRIGGER

OFF

DDLTRIGGERSQLTRACING

DDLTRIGGERTRACELEVEL

LOCATIONOFDDLTRACEFILE

--------------------------------------------------------------------------------

/s01/10gdb/admin/clinica/udump/ggs_ddl_trace.log

Analyzinginstallationstatus...

STATUSOFDDLREPLICATION

SUCCESSFULinstallationofDDLReplicationsoftwarecomponents

/*以INITIALSETUP选项运行ddl_setup.sql将在数据库中创建捕获DDL语句的Trigger等必要组件*/

@role_setup

GGSRolesetupscript

ThisscriptwilldropandrecreatetheroleGGS_GGSUSER_ROLE

Touseadifferentrolename,quitthisscriptandthenedittheparams.sqlscriptto

changethegg_roleparametertothepreferredname.（Donotrunthescript.）

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用户

grantGGS_GGSUSER_ROLEtomaclean;

Grantsucceeded.

@ddl_enable

Triggeraltered.

/*ddl_enable.sql将正式enableddl捕获触发器，即:

ALTERTRIGGERsys.&

ddl_trigger_nameENABLE;

*/

@?

/rdbms/admin/dbmspool

Packagecreated.

Viewcreated.

Packagebodycreated.

/*执行dbmspool包将在数据库中创建DBMS_SHARED_POOL包，之后需要用到*/

@ddl_pin

Entervaluefor1:

maclean

/*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