生活随笔
收集整理的這篇文章主要介紹了
GoldenGate单向复制配置(支持DDL复制)
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
在上一篇文件中的OGG單向復制配置不支持DLL的同步，只支持DML，因而本文在之前的基礎上增加對DDL語句的復制，下面是簡要配置過程記錄！
一：驗證之前的配置不支持DDL復制，這里在source端，新建一張表，發現無法復制到target端！target端也新建相同的表后，DML操作可以成功復制
[root@db1?~]#?su?-?oracle ?[oracle@db1?~]$?sqlplus?hr/hr ? SQL>?create?table?t2?(id?number?primary?key,name?varchar2(20)); ? Table?created. ? SQL>?conn?hr/hr@db2 ? Connected. ? SQL>?select?tname?from?tab?where?tname='T2'; ? no?rows?selected ?? SQL>?create?table?t2?(id?number?primary?key,name?varchar2(20)); ? Table?created. ?? SQL>?conn?hr/hr ? Connected. ? SQL>?insert?into?t2?values?(1,'one'); ? 1?row?created. ?? SQL>?commit; ? Commit?complete. ?? SQL>?conn?hr/hr@db2 ? Connected. ? SQL>?select?*?from?t2; ? ?????????ID?NAME ?----------?-------------------- ??????????1?one?二：開始配置OGG支持DDL復制(在source端操作)
1：賦予ogg用戶相應的權限，修改全局配置文件添加ggschema參數
SQL>?conn?/as?sysdba ?Connected. ? SQL>?grant?execute?on?utl_file?to?ogg; ? Grant?succeeded. ??[oracle@db1?~]$?cd?$GGATE? ?[oracle@db1?ogg]$?ggsci ? GGSCI?(db1)?1>?edit?param?./GLOBALS ? ? GGSCI?(db1)?2>?view?param?./GLOBALS ? ggschema?ogg?2：運行相關的sql腳本
[oracle@db1?~]$?cd?$GGATE? ?[oracle@db1?ogg]$?sqlplus?/nolog ? SQL>?conn?/as?sysdba ? Connected. ? SQL>?@marker_setup.sql ? ?Marker?setup?script ?You?will?be?prompted?for?the?name?of?a?schema?for?the?Oracle?GoldenGate?database?objects. ?NOTE:?The?schema?must?be?created?prior?to?running?this?script. ?NOTE:?Stop?all?DDL?replication?before?starting?this?installation. ??Enter?Oracle?GoldenGate?schema?name:ogg ??Marker?setup?table?script?complete,?running?verification?script... ?Please?enter?the?name?of?a?schema?for?the?GoldenGate?database?objects: ?Setting?schema?name?to?OGG ??MARKER?TABLE ?------------------------------- ?OK ??MARKER?SEQUENCE ?------------------------------- ?OK ??Script?complete. ?? SQL>?show?parameter?recyclebin; ? ?NAME?????????????????????????????????TYPE????????VALUE ?------------------------------------?-----------?------------------------------ ?recyclebin???????????????????????????string??????on ?? SQL>?alter?system?set?recyclebin=off; ? System?altered. ?? SQL>?show?parameter?recyclebin; ? ?NAME?????????????????????????????????TYPE????????VALUE ?------------------------------------?-----------?------------------------------ ?recyclebin???????????????????????????string??????OFF ?? SQL>?@ddl_setup.sql ? Oracle?GoldenGate?DDL?Replication?setup?script ?Verifying?that?current?user?has?privileges?to?install?DDL?Replication... ?You?will?be?prompted?for?the?name?of?a?schema?for?the?Oracle?GoldenGate?database?objects. ?NOTE:?For?an?Oracle?10g?source,?the?system?recycle?bin?must?be?disabled.?For?Oracle?11g?and?later,?it?can?be?enabled. ?NOTE:?The?schema?must?be?created?prior?to?running?this?script. ?NOTE:?Stop?all?DDL?replication?before?starting?this?installation. ?Enter?Oracle?GoldenGate?schema?name:ogg ??Working,?please?wait?... ?Spooling?to?file?ddl_setup_spool.txt ??Checking?for?sessions?that?are?holding?locks?on?Oracle?Golden?Gate?metadata?tables?... ?Check?complete. ?Using?OGG?as?a?Oracle?GoldenGate?schema?name. ?Working,?please?wait?... ?RECYCLEBIN?must?be?empty. ?This?installation?will?purge?RECYCLEBIN?for?all?users. ?To?proceed,?enter?yes.?To?stop?installation,?enter?no. ??Enter?yes?or?no:yes ?————————其他輸出省略———————— ??STATUS?OF?DDL?REPLICATION ?--------------------------------------------------------------------------------------- ?SUCCESSFUL?installation?of?DDL?Replication?software?components ??Script?complete. ?? SQL>?@role_setup.sql ? GGS?Role?setup?script ?This?script?will?drop?and?recreate?the?role?GGS_GGSUSER_ROLE ?To?use?a?different?role?name,?quit?this?script?and?then?edit?the?params.sql?script?to?change?the?gg_role?parameter?to?the?preferred?name.?(Do?not?run?the?script.) ??You?will?be?prompted?for?the?name?of?a?schema?for?the?GoldenGate?database?objects. ?NOTE:?The?schema?must?be?created?prior?to?running?this?script. ?NOTE:?Stop?all?DDL?replication?before?starting?this?installation. ??Enter?GoldenGate?schema?name:ogg ?Wrote?file?role_setup_set.txt ?PL/SQL?procedure?successfully?completed. ??Role?setup?script?complete ??Grant?this?role?to?each?user?assigned?to?the?Extract,?GGSCI,?and?Manager?processes,?by?using?the?following?SQL?command: ? GRANT?GGS_GGSUSER_ROLE?TO?<loggedUser>? where?<loggedUser>?is?the?user?assigned?to?the?GoldenGate?processes. ? ? SQL>?grant?ggs_ggsuser_role?to?ogg; ? Grant?succeeded. ?? SQL>?@ddl_enable.sql ? Trigger?altered. ?? SQL>?@?/rdbms/admin/dbmspool.sql? ? Package?created. ?Grant?succeeded. ?View?created. ?Package?body?created. ?? SQL>?@ddl_pin.sql?ogg ? PL/SQL?procedure?successfully?completed. ?PL/SQL?procedure?successfully?completed. ?PL/SQL?procedure?successfully?completed.?3：source端修改extract進程的params文件，添加"ddl include all"參數，重啟extract進程
GGSCI?(db1)?1>?view?params?eora_t1 ?extract?eora_t1 ? setenv?(NLS_LANG=AMERICAN_AMERICA.AL32UTF8) ? ddl?include?all ?userid?ogg,password?ogg ?exttrail?./dirdat/aa ?table?hr.*; ?? GGSCI?(db1)?2>?stop?extract?eora_t1 ? Sending?STOP?request?to?EXTRACT?EORA_T1?... ?Request?processed. ?? GGSCI?(db1)?3>?start?extract?eora_t1 ? Sending?START?request?to?MANAGER?... ?EXTRACT?EORA_T1?starting ?? GGSCI?(db1)?4>?info?extract?eora_t1 ? EXTRACT????EORA_T1???Last?Started?2012-06-20?15:42???Status?RUNNING ?Checkpoint?Lag???????00:00:00?(updated?00:00:10?ago) ?Log?Read?Checkpoint??Oracle?Redo?Logs ??????????????????????2012-06-20?15:42:58??Seqno?3,?RBA?50044416 ??????????????????????SCN?0.567478?(567478)?4：target端修改replicat進程的params文件，添加"ddl include all"和"ddlerror default ignore retryop maxretries 3 retrydelay 5" 參數，重啟replicat進程
[root@db2?~]#?su?-?oracle ?[oracle@db2?~]$?cd?$GGATE? ?[oracle@db2?ogg]$?ggsci ? GGSCI?(db2)?1>?edit?params?rora_t1 ? GGSCI?(db2)?2>?view?params?rora_t1 ? replicat?rora_t1 ? setenv?(NLS_LANG=AMERICAN_AMERICA.AL32UTF8) ? ddl?include?all ?ddlerror?default?ignore?retryop?maxretries?3?retrydelay?5 ?userid?ogg,password?ogg ?handlecollisions ?assumetargetdefs ?discardfile?./dirrpt/rora_t1.dsc,purge ?map?hr.*?,target?hr.*; ?? GGSCI?(db2)?3>?stop?replicat?rora_t1 ? Sending?STOP?request?to?REPLICAT?RORA_T1?... ?Request?processed. ?? GGSCI?(db2)?4>?start?replicat?rora_t1 ? Sending?START?request?to?MANAGER?... ?REPLICAT?RORA_T1?starting ?? GGSCI?(db2)?5>?info?replicat?rora_t1 ? REPLICAT???RORA_T1???Last?Started?2012-06-20?15:50???Status?RUNNING ?Checkpoint?Lag???????00:00:00?(updated?00:00:00?ago) ?Log?Read?Checkpoint??File?./dirdat/pa000000 ??????????????????????First?Record??RBA?4780973?三：測試
[oracle@db1?ogg]$?sqlplus?hr/hr ? SQL>?alter?table?t2?add?location?varchar2(200); ? Table?altered. ?? SQL>?conn?hr/hr@db2 ? Connected. ? SQL>?desc?t2 ? ?Name??????????????????????????????????????Null?????Type ??-----------------------------------------?--------?---------------------------- ??ID????????????????????????????????????????NOT?NULL?NUMBER ??NAME???????????????????????????????????????????????VARCHAR2(20) ??LOCATION???????????????????????????????????????????VARCHAR2(200) ?? SQL>?conn?hr/hr ? Connected. ? SQL>?create?table?t3?as?select?object_id,object_name?from?dba_objects; ? ?Table?created. ?? SQL>?conn?hr/hr@db2 ? Connected. ? SQL>?select?tname?from?tab?where?tname='T3'; ? ?TNAME ?------------------------------ ?T3 ?? SQL>?desc?t3; ? ?Name??????????????????????????????????????Null?????Type ??-----------------------------------------?--------?---------------------------- ??OBJECT_ID??????????????????????????????????????????NUMBER ??OBJECT_NAME????????????????????????????????????????VARCHAR2(128)??本文轉自斬月博客51CTO博客，原文鏈接http://blog.51cto.com/ylw6006/904373如需轉載請自行聯系原作者
ylw6006
總結
以上是生活随笔為你收集整理的GoldenGate单向复制配置(支持DDL复制)的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
