ogg 配置複習(dml>ddl>sequnce)，dmlsequnce

ogg 配置複習(dml>ddl>sequnce)，dmlsequnce

<strong><span style="color:#ff6666;">dml 配置過程與測試概要</span></strong>在源端建立專用的資料表空間、schema、並授權。create tablespace goldengate datafile 'goldengate01.dbf' size 100m autoextend on;create user goldengate identified by goldengate default tablespace goldengate temporary tablespace temp;grant connect to goldengate;grant alter any table to goldengate;grant alter session to goldengate;grant create session to goldengate;grant flashback any table to goldengate;grant select any dictionary to goldengate;grant select any table to goldengate;grant resource to goldengate;grant select any transaction to goldengate;檢查源端資料庫是否在歸檔模式，強烈建議在歸檔模式。SQL> archive log listDatabase log mode              Archive ModeAutomatic archival             EnabledArchive destination            /archOldest online log sequence     1Next log sequence to archive   3Current log sequence           3端：添加附加日誌來唯一標識一行記錄，要在資料庫層級開啟最小開關。為了減少整個資料庫添加附加日誌，以及減少歸檔量，goldengate建議複製哪些對象，就添加哪些表的附加日誌（我們到時候是拿hr使用者下的表來實驗）。檢查：SQL> select supplemental_log_data_min from v$database;SUPPLEME--------NO開啟：SQL> alter database add supplemental log data;Database altered.再次查看：SQL> <span style="color:#ff6666;">select supplemental_log_data_min from v$database;</span>SUPPLEME--------YES切換日誌組，使附加日誌開關生效。SQL> alter system switch logfile;System altered.解釋：在正常情況下，oracle是用rowid來唯一標示一行記錄的，但goldengate這裡不夠，需要開啟附加日誌。安裝與配置GGSCI (gg2) 2><span style="color:#ff6666;"> create subdirs</span>GGSCI (oratest) 80> view params mgrport 7839 DYNAMICPORTLIST 7840-7850AUTOSTART EXTRACT *AUTORESTART EXTRACT *PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45啟動配置的管理進程GGSCI (gg2) 4> start mgrManager started.select owner||'.'||table_name table_name,logging                from dba_tableswhere owner='SENDER';SQL> alter table SENDER.A1 logging;源端：配置hr下面表的表級附加日誌。配置登陸使用者。並檢查日誌是否添加成功。以goldengate這個schema登陸資料庫。GGSCI (gg1) 10><span style="color:#ff6666;"> dblogin userid goldengate, password goldengate</span>Successfully logged into database.查看：GGSCI (gg1) 11> info trandata hr.*add extract ext_demo, tranlog, begin now, threads 1參數分析：tranlog：表示資料抓取的來源是資料庫的redo資料。begin now：表示我們在啟動這個抓取進程的就去抓取資料。threads 1：表示我們資料庫有多少個redo threads，單一實例基本上是1或者不設，rac就自己設定了，配置過RAC的基本都瞭解添加源端的隊列檔案。GGSCI (gg1) 15> <span style="color:#3333ff;">add extract ext_demo,<span style="background-color: rgb(102, 102, 204);">tranlog</span>, begin now, threads 1</span>GGSCI (gg1) 15> <span style="color:#ff0000;">add EXTTRAIL ./dirdat/r1, extract ext_demo,megabytes 100</span>EXTTRAIL added./dirdat：表示trail檔案的目錄r1：trail檔案的首碼extract ext_demo：值指定給那個進程用的（ext_demo）。megabytes 100：檔案大小是100m編輯我們剛剛在源端配置的抓取進程的參數：GGSCI (gg1) 16><span style="color:#ff6666;"> edit param ext_demo</span>EXTRACT EXT_DEMOuserid goldengate,password goldengateREPORTCOUNT EVERY 1 MINUTES, RATEnumfiles 5000DISCARDFILE ./dirrpt/ext_demo.dsc,APPEND,MEGABYTES 1000DISCARDROLLOVER AT 3:00exttrail ./dirdat/r1,megabytes 100dynamicresolutionTRANLOGOPTIONS EXCLUDEUSER goldengateTRANLOGOPTIONS convertucs2clobs  TRANLOGOPTIONS DBLOGREADERTABLE SENDER.*;setenv (ORACLE_SID=ogg)：指定oracle的sid，單機環境下可以不設或者bashprofile檔案中設定了預設的，也可以不設，但是叢集環境就要指定了，這裡我們強烈建議指定。setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)：源端資料庫的字元集，一定要跟資料庫中匹配。userid goldengate,password goldengate：告訴抓取進程是通過這個使用者登陸資料庫的。exttrail ./dirdat/r1,megabytes 100：與我們上面配置抓取進程的設定要一致，路徑和大小嘛。TABLE HR.*：最重要的，告訴抓取進程抓取的是哪些使用者哪些表的資料配置datapump進程，將抓取資料傳到目標主機。負責TCPIP通訊。GGSCI (gg1) 31> <span style="color:#ff0000;">add extract dpe_demo, exttrailsource ./dirdat/r1</span>EXTRACT added.輸出：目標主機怎麼寫，也是定義datapumo進程的輸出。GGSCI (gg1) 33><span style="color:#ff6666;"> add rmttrail ./dirdat/t1,EXTRACT dpe_demo,MEGABYTES 100</span>RMTTRAIL added.分析：exttrailsource：抓取進程的來源，因為這個進程不否則到資料庫中取抓取，所以抓取的來源是通過抓取進程已經產生好的trail檔案中的內容。17、配置datapump進程參數：GGSCI (oratest) 83> <span style="color:#ff0000;">view params dpe_demo</span>extract dpe_demodynamicresolutionpassthrurmthost 192.168.175.139, mgrport 7840, compressrmttrail ./dirdat/t1numfiles 5000TABLE SENDER.*;GGSCI (oratest) 84> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     DPE_DEMO    00:00:00      00:00:00    EXTRACT     RUNNING     EXT_DEMO    00:00:00      00:00:06    GGSCI (oratest) 85> passthru：上面已經說過不需要串連資料庫，所以用這個指令。rmthost：目標主機的IP地址，就是說我們的rmttrail進程發送到的主機。mgrport：目標主機管理進程的連接埠號碼，我們上面已經配置了。compress：在傳輸的過程中啟用壓縮。rmttrail：路徑和檔案與上面配置的一致。TABLE SENDER.*：上面說過，哪些表的redo資訊傳輸。目標端：建立golengate軟體使用的schema，並授權一些必要的特殊許可權，注意與源端有所區別，保證我們能DML。create tablespace goldengate datafile 'goldengate01.dbf' size 100m autoextend on;create user goldengate identified by goldengate default tablespace goldengate temporary tablespace temp;grant connect to goldengate;grant alter any table to goldengate;grant alter session to goldengate;grant create session to goldengate;grant flashback any table to goldengate;grant select any dictionary to goldengate;grant select any table to goldengate;grant resource to goldengate;grant insert any table to goldengate;grant update any table to goldengate;grant delete any table to goldengate;grant create any index to goldengate;grant select any transaction to goldengate;目標端：為replicat進程建立checkpoint表：GGSCI (gg2) 1> <span style="color:#ff0000;">dblogin userid goldengate,password goldengate</span>Successfully logged into database.GGSCI (gg2) 2> <span style="color:#ff0000;">add checkpointtable goldengate.rep_demo_ckpt</span>Successfully created checkpoint table goldengate.rep_demo_ckpt.21、配置目標端replicate進程：GGSCI (gg2) 3> <span style="color:#ff6666;">add replicat rep_demo,exttrail ./dirdat/t1,checkpointtable goldengate.rep_demo_ckpt</span>REPLICAT added.分析：exttrail：表示這個進程擷取資料的來源是什麼地方，是我們源端rmttrail所設定過的。22、配置目標端replicate參數：GGSCI (oratest) 13> <span style="color:#ff6666;">view params rep_demo</span>REPLICAT rep_demoUSERID goldengate,PASSWORD goldengateREPORTCOUNT EVERY 30 MINUTES, RATEREPERROR DEFAULT, ABENDnumfiles 5000--HANDLECOLLISIONSassumetargetdefsDISCARDFILE ./dirrpt/rep_demo.dsc, APPEND, MEGABYTES 1000ALLOWNOOPUPDATESMAP SENDER.*,TARGET SENDER.*;分析：重要的地方與上面基本一致，有興趣可以查查其他參數的資訊。MAP：源端和目標端的對應。添加表格的時候 先在目標添加，再元端，資料庫可以自動同步！<strong><span style="color:#ff6666;">1.4 啟用DDL</span></strong>SQL>@marker_setup.sql    --提示輸入目標schemaSQL>@ddl_setup.sql       --提示輸入目標schema,輸入initialsetup最後輸入yesSQL>@role_setup.sqlSQL>grant GGS_GGSUSER_ROLE to ddw; --不進行該步賦權後面起進程會報錯SQL>@ddl_enable.sql                --使觸發器生效1.5 清除DDL 同步設定如果因為DDL 同步出現很多問題，最簡單的方法就是卸載之後重建。安裝目錄下只提供了清除對象的指令碼，可以如下操作：首先要求把所有的GG進程停掉，包括mgr進程SQL>@ddl_disable.sql  --首先使DDL觸發器失效SQL>@ddl_remove.sqlSQL>@marker_remove.sqlalter table OGG1.A1 logging;info trandata OGG1.*add extract ext2,tranlog, begin now, threads 1add EXTTRAIL ./dirdat/r3, extract ext2,megabytes 100EXTRACT ext2userid goldengate,password goldengateREPORTCOUNT EVERY 1 MINUTES, RATEnumfiles 5000DISCARDFILE ./dirrpt/ext_demo1.dsc,APPEND,MEGABYTES 1000DISCARDROLLOVER AT 3:00exttrail ./dirdat/r2,megabytes 100ddl include allTABLE OGG1.*;add extract dpe_ext2, exttrailsource ./dirdat/r3add rmttrail ./dirdat/t3,EXTRACT dpe_ext2,MEGABYTES 100extract dpe_ext2dynamicresolutionpassthrurmthost 192.168.175.139, mgrport 7840, compressrmttrail ./dirdat/t3numfiles 5000TABLE OGG1.*;add replicat rep_ext2,exttrail ./dirdat/t3,checkpointtable goldengate.rep_demo_ckptREPLICAT rep_ext2USERID goldengate,PASSWORD goldengateREPORTCOUNT EVERY 30 MINUTES, RATEREPERROR DEFAULT, ABENDnumfiles 5000--HANDLECOLLISIONSassumetargetdefsDISCARDFILE ./dirrpt/rep_rep2.dsc, APPEND, MEGABYTES 1000ALLOWNOOPUPDATESMAP OGG1.*,TARGET OGG1.*;

<strong><span style="color:#ff6666;">啟用序列：</span><span style="color:#ff6666;">目標端：</span></strong>

SQL> @sequence.sqlPlease enter the name of a schema for the GoldenGate database objects:goldengateSetting schema name to GOLDENGATEUPDATE_SEQUENCE STATUS:Line/pos   Error---------- -----------------------------------------------------------------No errors  No errorsGETSEQFLUSHLine/pos   Error---------- -----------------------------------------------------------------No errors  No errorsSEQTRACELine/pos   Error---------- -----------------------------------------------------------------No errors  No errorsREPLICATE_SEQUENCE STATUS:Line/pos   Error---------- -----------------------------------------------------------------No errors  No errorsSTATUS OF SEQUENCE SUPPORT--------------------------------------------------------------SUCCESSFUL installation of Oracle Sequence Replication supportSQL> GRANT EXECUTE on goldengate.replicateSequence to ogg1;Grant succeeded.SQL> 目標端：SQL> @sequence.sqlPlease enter the name of a schema for the GoldenGate database objects:goldengateSetting schema name to GOLDENGATEUPDATE_SEQUENCE STATUS:Line/pos   Error---------- -----------------------------------------------------------------No errors  No errorsGETSEQFLUSHLine/pos   Error---------- -----------------------------------------------------------------No errors  No errorsSEQTRACELine/pos   Error---------- -----------------------------------------------------------------No errors  No errorsREPLICATE_SEQUENCE STATUS:Line/pos   Error---------- -----------------------------------------------------------------No errors  No errorsSTATUS OF SEQUENCE SUPPORT--------------------------------------------------------------SUCCESSFUL installation of Oracle Sequence Replication supportSQL> GRANT EXECUTE on goldengate.replicateSequence to ogg1;Grant succeeded.SQL> 複製進程：傳輸進程：GGSCI (oratest) 41> edit params DPE_EXT2extract dpe_ext2dynamicresolutionpassthrurmthost 192.168.175.139, mgrport 7840, compressrmttrail ./dirdat/t3numfiles 5000TABLE OGG1.*;SEQUENCE OGG1.*;~"dirprm/dpe_ext2.prm" 8L, 158C writtenGGSCI (oratest) 42> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     DPE_DEMO    00:00:00      00:00:00    EXTRACT     RUNNING     DPE_EXT2    00:00:00      00:00:04    EXTRACT     RUNNING     EXT1        00:00:00      00:00:09    EXTRACT     RUNNING     EXT2        00:00:00      00:00:06    EXTRACT     RUNNING     EXT_DEMO    00:00:00      00:00:09    GGSCI (oratest) 43> stop DPE_EXT2Sending STOP request to EXTRACT DPE_EXT2 ...Request processed.GGSCI (oratest) 44> start DPE_EXT2Sending START request to MANAGER ...EXTRACT DPE_EXT2 startingGGSCI (oratest) 45> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     DPE_DEMO    00:00:00      00:00:00    EXTRACT     RUNNING     DPE_EXT2    00:00:00      00:00:05    EXTRACT     RUNNING     EXT1        00:00:00      00:00:00    EXTRACT     RUNNING     EXT2        00:00:00      00:00:03    EXTRACT     RUNNING     EXT_DEMO    00:00:00      00:00:09    GGSCI (oratest) 46> 抽取進程：GGSCI (oratest) 48> edit params EXT2EXTRACT ext2userid goldengate,password goldengateREPORTCOUNT EVERY 1 MINUTES, RATEnumfiles 5000DISCARDFILE ./dirrpt/ext2.dsc,APPEND,MEGABYTES 1000DISCARDROLLOVER AT 3:00exttrail ./dirdat/r3,megabytes 100ddl include allTRANLOGOPTIONS DBLOGREADERTABLE OGG1.*;SEQUENCE OGG1.*;~"dirprm/ext2.prm" 11L, 284C writtenGGSCI (oratest) 49> stop ext2Sending STOP request to EXTRACT EXT2 ...Request processed.GGSCI (oratest) 50> start ext2Sending START request to MANAGER ...EXTRACT EXT2 startingGGSCI (oratest) 51> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     DPE_DEMO    00:00:00      00:00:07    EXTRACT     RUNNING     DPE_EXT2    00:00:00      00:00:02    EXTRACT     RUNNING     EXT1        00:00:00      00:00:06    EXTRACT     RUNNING     EXT2        00:00:03      00:00:07    EXTRACT     RUNNING     EXT_DEMO    00:00:00      00:00:05    GGSCI (oratest) 52> 提前修改該檔案：修改檔案：GGSCI (oratest) 79> EDIT PARAMS ./GLOBALSGGSCHEMA goldengate~FLUSH SEQUENCE ogg1.*<strong><span style="color:#ff0000;">重啟更加有效果！</span></strong>