Oracle GoldenGate 學習教程二、配置和使用
閱讀導航
- 配置和使用GoldenGate的步驟
- 1 配置資料庫支援GoldenGate
- 1.1 OGG使用者和許可權分配
- 1.2 資料庫附加日誌
- 1.2.1 資料庫級別的附加日誌
- 1.2.2 使用者級別的附加日誌
- 1.2.3 表級別的附加日誌
- 2 配置GoldenGate
- 2.1 GoldenGate MGR程式
- 2.1.1 配置GoldenGate MGR程式
- 2.1.2 啟動mgr程式
- 2.2 在源端建立和配置Extract程式
- 2.2.1 建立主抽取程式(Primary Extract)
- 2.2.1.1 配置主抽取程式(Primary Extract)引數
- 2.2.2 建立和配置Data Pump程式(Secondly Extract)
- 2.2.2.1 建立Data Pump Extract程式
- 2.2.2.2 配置Data Pump Extract程式
- 2.2.2.3 將目標端trail檔案新增到佇列中
- 2.3 在目標端配置Replicat
- 2.3.1 建立和配置Checkpoint Table
- 2.3.2 建立和配置Replicat程式
- 3 初始化資料
- 4 使用GoldenGate
- 5 總結
配置和使用GoldenGate的步驟
- 1 在源端和目標端配置資料庫支援GoldenGate
- 2 在源端和目標端建立和配置GoldenGate例項
- 3 在源端建立和配置主抽取程式(Primary Extract)
- 4 在源端建立和配置Data Pump程式(Secondly Extract)
- 5 在目標端建立和配置Replicat程式
1 配置資料庫支援GoldenGate
1.1 OGG使用者和許可權分配
GoldenGate需要從線上日子或歸檔日誌抽取捕獲系統的變更資料資訊，這些資訊可能來源於業務使用者，可能來源於系統使用者，為了使GoldenGate能夠抽取這些資料應為GoldenGate建立獨立的使用者和分配必要的許可權以滿足系統執行需求，這些許可權包括讀取業務使用者表資料的許可權、讀取系統表的許可權、執行某個系統包的許可權等，以下腳步建立GoldenGate使用者ogg_owner(源使用者)、ogg_trg(目標使用者)和GoldenGate角色ogg_role:
[@sywu ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 21 14:11:04 2015 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SYS@sydb>create tablespace tbs01 datafile '+oradata' size 10m autoextend on uniform size 2m / create user ogg_owner identified by ogg_owner default tablespace tbs01 quota unlimited on tbs01 / create user ogg_trg identified by ogg_trg default tablespace tbs01 quota unlimited on tbs01 / create role ogg_role /
為易管理和維護統一將許可權賦予角色ogg_role:
grant CREATE SESSION, ALTER SESSION, ALTER SYSTEM, RESOURCE, SELECT ANY DICTIONARY, FLASHBACK ANY TABLE, SELECT ANY TABLE, SELECT ANY TRANSACTION, insert any table, update any table, drop any table, CREATE TABLE to ogg_role; grant SELECT on dba_clusters to ogg_role; grant SELECT on V_$DATABASE to ogg_role; grant select on sys.logmnr_buildlog to ogg_role; grant EXECUTE on DBMS_FLASHBACK to ogg_role; grant execute on DBMS_CAPTURE_ADM to ogg_role; grant execute on DBMS_STREAMS to ogg_role; grant EXECUTE_CATALOG_ROLE to ogg_role;
然後再將ogg_role 角色賦予ogg_owner(源使用者)和ogg_trg(目標使用者):
grant ogg_role to ogg_owner; grant ogg_role to ogg_trg;
源使用者測試表和資料:
SYS@sydb>create table ogg_owner.togg(id primary key,name,type,CREATED,update_date)
2 as
3 select object_id,object_name,object_type,CREATED,sysdate from dba_objects
4 where rownum<1001 5 / Table created. Elapsed: 00:00:00.28
1.2 資料庫附加日誌
附加日誌級別分為:
- 資料庫級別的附加日誌(必須附加日誌)
- 使用者級別的附加日誌(當使用GoldenGate DDL抽取功能時要啟用該級別)
- 表級別的附加日誌(必須附加日誌，除非你啟用了使用者級別的附加日誌)
1.2.1 資料庫級別的附加日誌
因為GoldenGate需要抽取捕獲變更資料資訊和後設資料資訊，這些資訊需要記錄日誌，並且因為日誌總是持續增長的，這些日誌增長的資訊對於GoldenGate是必須記錄的，所以必須在GoldenGate程式啟動前開啟資料庫級別的附加日誌；
檢查資料庫是否開啟附加日誌:
SYS@sydb>SELECT supplemental_log_data_min, force_logging FROM v$database; SUPPLEME FOR -------- --- NO NO
開啟資料庫級別的附加日誌:
SYS@sydb>alter database add supplemental log data; SYS@sydb>ALTER DATABASE FORCE LOGGING; SYS@sydb>SELECT supplemental_log_data_min, force_logging FROM v$database; SUPPLEME FOR -------- --- YES YES Elapsed: 00:00:00.00 SYS@sydb>alter system switch logfile; System altered. Elapsed: 00:00:00.09
1.2.2 使用者級別的附加日誌
開啟使用者級別的附加日誌的管理使用者必須具有執行DBMS_CAPTURE_ADM包的許可權；可以在GoldenGate GGSCI命令列下登入到資料庫管理使用者為其它的使用者開啟附加日誌；
在GGSCI命令列下使用DBLOGIN命令登入到資料庫管理使用者
GGSCI (sywu) 1> dblogin userid ogg_owner,password ogg_owner Successfully logged into database.
為使用者啟用附加日誌
GGSCI (sywu as ogg_owner@sydb) 2> add schematrandata sywu 2015-08-24 14:56:26 INFO OGG-01788 SCHEMATRANDATA has been added on schema sywu. 2015-08-24 14:56:27 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema sywu.
取消使用者附加日誌
GGSCI (sywu as ogg_owner@sydb) 6> delete schematrandata sywu 2015-08-25 20:14:49 INFO OGG-01792 SCHEMATRANDATA has been deleted on schema sywu. 2015-08-25 20:14:49 INFO OGG-01979 SCHEMATRANDATA for scheduling columns has been deleted on schema sywu.
1.2.3 表級別的附加日誌
在沒有啟用使用者級別的附加日誌的情況下表級別的主鍵或唯一索引附加日誌對於GoldenGate是必須的；在某些情況下即使你啟用了使用者級別的附加日誌，你也可以啟用表級別的附加日誌使主鍵附加日誌替代每一個在使用者級別為GoldenGate指定的鍵；必須在表沒有主鍵或唯一索引的情況下啟用表級別的附加日誌。
在GGSCI命令列下使用dblogin登入到資料庫管理使用者
GGSCI (sywu) 1> dblogin userid ogg_owner,password ogg_owner Successfully logged into database.
為表啟用附加日誌
GGSCI (sywu as ogg_owner@sydb) 3> add trandata ogg_owner.togg Logging of supplemental redo data enabled for table OGG_OWNER.TOGG. TRANDATA for scheduling columns has been added on table 'OGG_OWNER.TOGG'.
注意:僅當該表有主鍵或唯一索引時使用上面的命令啟用表級別的附加日誌，如果表中沒有主鍵或唯一索引時則必須指定一個或多個或者全部列做為主鍵，在GoldenGate這些鍵的作用是過濾重複的資料。
SYS@sydb>create table ogg_owner.togg_nokey(id,name,type,CREATED,update_date)
2 as
3 select object_id,object_name,object_type,CREATED,sysdate from dba_objects
where rownum<1001 5 / Table created. Elapsed: 00:00:00.11
錯誤的啟用沒有主鍵的表級別附加日誌:
GGSCI (sywu as ogg_owner@sydb) 4> add trandata ogg_owner.togg_nokey 2015-08-24 16:05:01 WARNING OGG-06439 No unique key is defined for table TOGG_NOKEY. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. Logging of supplemental redo data enabled for table OGG_OWNER.TOGG_NOKEY. TRANDATA for scheduling columns has been added on table 'OGG_OWNER.TOGG_NOKEY'.
正確的啟用沒有主鍵的表級別附加日誌:
GGSCI (sywu as ogg_owner@sydb) 26> add trandata ogg_owner.togg_nokey,cols (ID),nokey 2015-08-24 16:33:19 WARNING OGG-06439 No unique key is defined for table TOGG_NOKEY. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. Logging of supplemental redo data enabled for table OGG_OWNER.TOGG_NOKEY. TRANDATA for scheduling columns has been added on table 'OGG_OWNER.TOGG_NOKEY'.
2 配置GoldenGate
2.1 GoldenGate MGR程式
MGR程式管理啟動Oracle GoldenGate程式、啟動動態程式、分配埠給GoldenGate程式、管理trail file、建立事件，錯誤和診斷報告工作，必須在第一時間啟動；當某些原因導致GoldenGate崩潰或重啟機器時，預設情況MGR是沒有啟動的
[oracle@sywu ogg_src]$ tggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 09:14:25 Operating system character set identified as UTF-8. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (sywu) 1> info mgr Manager is DOWN!
2.1.1 配置GoldenGate MGR程式
可以透過直接編輯GoldenGate_home/dirprm/mgr.prm檔案或進入GGSCI命令列後鍵入edit param mgr命令回車進入MGR配置檔案vi編輯介面;
GGSCI (sywu as ogg_owner@sydb) 10> edit param mgr PORT 7809 DYNAMICPORTLIST 7810-7820 AUTOSTART ER E* AUTORESTART ER P*,RETRIES 4, WAITMINUTES 4 STARTUPVALIDATIONDELAY 5
| 引數 | 說明 |
|---|---|
| PORT | 表示MGR程式埠號 |
| DYNAMICPORTLIST | 表示MGR進???動態為其它程式如Extract程式、Replicat程式分配的埠 |
| AUTOSTART、AUTORESTART | 表示當MGR程式啟動後失敗時自動啟動或重啟的GoldenGate程式 |
2.1.2 啟動mgr程式
GGSCI (sywu) 2> start mgr Manager started. GGSCI (sywu) 3> info mgr Manager is running (IP port sywu.7909, Process ID 17400).
啟動原理:透過讀取GoldenGate_home/dirprm/mgr.prm檔案，然後根據該檔案的配置資訊啟動程式分配埠號，如果該程式啟動失敗，首先請檢查預使用的埠是否被佔用:
netstat -lntup|grep 7809 tcp 0 0 :::7809 :::* LISTEN 32426/./mgr
然後檢查相關的配置檔案或重新配置MGR。
2.2 在源端建立和配置Extract程式
建立和配置Extract程式的工作有:
- 建立和配置主抽取程式(Primary Extract)
- 建立和配置Data Pump程式(Secondly Extract)
2.2.1 建立主抽取程式(Primary Extract)
進入GGSCI命令列使用add extract 命令建立主抽取程式
[oracle@sywu ~]$ sggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 09:14:25 Operating system character set identified as UTF-8. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (sywu as ogg_owner@sydb) 13> add extract esydb001,tranlog,begin now EXTRACT added.
因為主程式的作用是抽取捕獲系統變更資料並將這些資料儲存到trail檔案裡，所以必須為其配置trail檔案目錄和trail檔名的兩個字元名，trail檔名共8個字元，其餘6個字元由GoldenGate系列填充;
GGSCI (sywu as ogg_owner@sydb) 14> ADD EXTTRAIL /u01/app/product/ogg_src/dirdat/es, EXTRACT esydb001 EXTTRAIL added.
2.2.1.1 配置主抽取程式(Primary Extract)引數
GGSCI (sywu as ogg_owner@sydb) 15> edit param esydb001 extract esydb001 SETENV(ORACLE_SID="sydb") SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid ogg_owner, password ogg_owner EXTTRAIL /u01/app/product/ogg_src/dirdat/es table ogg_owner.togg;
userid指定GoldenGate 抽取使用者的使用者名稱和密碼；
SETENV 設定環境變數，如例項名、資料庫字符集；
table 指定抽取的使用者和表名，如果指定多個以相同字元開頭或結尾的表名，可以使用“字首* ”的方式代替，這裡僅以ogg_owner.togg表為測試案例;
2.2.2 建立和配置Data Pump程式(Secondly Extract)
新增Data Pump Extract程式時要注意，如果源端和目標端OGG物理路徑都相同，可以按照常規方式處理，但是如果不相同，就像我本例的一樣源端和目標端都在同一臺機器上，但GoldenGate物理路徑不相同，請按如下方式處理
2.2.2.1 建立Data Pump Extract程式
GGSCI (sywu) 26> ADD EXTRACT PSYDB001, EXTTRAILSOURCE /u01/app/product/ogg_src/dirdat/es EXTRACT added.
EXTTRAILSOUCE 指定源端的trail路徑，必須包含兩個字元，這個路徑和主抽取程式(Primary Extract)中指定的trail目錄和trail檔案命名必須相同，因為Data Pump程式要讀取主抽取程式生成的trail檔案;
2.2.2.2 配置Data Pump Extract程式
edit param psydb001 extract psydb001 SETENV(ORACLE_SID="sydb") SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid ogg_owner,password ogg_owner RMTHOST sywu,mgrport 7909 RMTTRAIL /u01/app/product/ogg_trg/dirdat/ps --這裡指定目標端trail檔案位置，同樣包含兩個字元 table ogg_owner.togg;
RMTHOST 指定目標端地址和埠等資訊；
RMTTRAIL 指定目標端儲存trail檔案的目錄和兩個字元檔名；
TABLE 指定同步的表，配置的方式同在主抽取程式(Primary Extract)的配置一樣，但這裡多了許多額外的功能，比如實現資料過濾和其它複雜操作；
2.2.2.3 將目標端trail檔案新增到佇列中
源端可以配置多個主抽取程式，也可以配置多個Data Pump程式，但必須為每個要同步的目標端配置一個Data Pump程式；
GGSCI (sywu) 10> ADD RMTTRAIL /u01/app/product/ogg_trg/dirdat/ps, EXTRACT PSYDB001 RMTTRAIL added.
2.3 在目標端配置Replicat
目標端需要做的工作:
- 建立和配置Checkpoint Table
- 建立和配置Replicat程式
2.3.1 建立和配置Checkpoint Table
目標端Replicat程式監控Checkpoint
Table，每一條接收到的checkpoint資料由Collector後臺程式寫入到Checkpoint
Table，因為對於事務來說每一條checkpoint資料要麼執行成功要麼執行失敗，所以Replicat程式確保每一條checkpoint事務資料只應用一次，即使其它程式錯誤或者資料庫錯誤。
在目標端GSSCI命令列下使用dblogin登入到目標端OGG管理使用者
[oracle@sywu ogg_src]$ tggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 09:14:25 Operating system character set identified as UTF-8. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (sywu) 1> dblogin userid ogg_trg,password ogg_trg Successfully logged into database.
建立checkpoint table
GGSCI (sywu as ogg_trg@sydb) 2> add checkpointtable ogg_trg.ogg_chk Successfully created checkpoint table ogg_trg.ogg_chk.
將checkpoint table 定義在GoldenGate配置檔案裡
GGSCI (sywu) 1> edit param ./GLOBALS GGSCHEMA OGG_TRG CHECKPOINTTABLE OGG_TRG.OGG_CHK
首次編輯GoldenGate配置檔案時，GLOBALS檔案以大寫命名並且沒有副檔名建立在GoldenGate_home根目錄。
2.3.2 建立和配置Replicat程式
當GoldenGate執行時會產生一些與同步資料不相關的檔案，這些檔案記錄了GoldenGate程式錯誤、資料庫錯誤、GoldenGate操作等資訊，這個檔案叫做Discard
file，配置Replicat程式時由DISCARDFILE引數定義，這個引數是可選的，但推薦建立相應的目錄並配置，方便診斷GoldenGate錯誤問題；
建立Discard file目錄
[oracle@sywu ogg_trg]$ mkdir /u01/app/product/ogg_trg/discrd [oracle@sywu ogg_trg]$ chmod 775 /u01/app/product/ogg_trg/discrd/
目標端建立Replicat程式
GGSCI (sywu) 27> add replicat rsydb001,exttrail /u01/app/product/ogg_trg/dirdat/ps,checkpointtable OGG_TRG.OGG_CHK REPLICAT added.
編輯Replicat程式配置檔案
REPLICAT rsydb001 SETENV(ORACLE_SID="sydb") SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) USERID ogg_trg,password ogg_trg DISCARDFILE /u01/app/product/ogg_trg/discrd/reptr.desc,append,megabytes 512 ALLOWNOOPUPDATES ASSUMETARGETDEFS MAP OGG_OWNER.TOGG,target OGG_TRG.TOGG;
到這裡簡單的源端和目標端的MGR、Extract、Replicat程式就配置完了，測試配置檔案是否錯誤，程式啟動是否正常，如果程式有問題就使用“view report 程式名”檢視錯誤；不要將所有程式啟動，因為有些工作還沒有完成。
3 初始化資料
為什麼要初始化資料？
因為DML操作包括INSERT、UPDATE、DELETE、SELECT操作，而在這些操作中UPDATE、DELETE操作Redo只記錄了變更的資料列以及行ID(ROWID)，GoldenGate抽取資料後將其轉換為自己的格式傳送都目標端，假設源端有100條資料，從08:00
AM開始同步到目標端，而在同步開始前目標端沒有初始化資料(目標端為空資料)，那麼08:00 AM後事物產生的UPDATE、DELETE
DML操作傳送到目標端，目標端GoldenGate
Replicat程式會因為找不到資料而報錯從而導致Replicat程式崩潰停止(ABENDED)，所以這就需要我們在同步前初始化資料，初始化完後再同步，這樣大大降低錯誤率。同步資料的方式可以透過DBLINK、EXP/IMP、SQLLDR或者表空間遷移等方式同步；我的測試都在同一個庫所以就直接建立表了。
SYS@sydb>create table ogg_trg.togg as select * from ogg_owner.togg 2 / Table created. Elapsed: 00:00:00.24
4 使用GoldenGate
資料初始化後，分別啟動源端MGR程式、主抽取程式(Primary Extract)、Data Pump程式(Secondly Extract)以及目標端MGR程式、Replicat程式；
啟動源端GoldenGate程式
[oracle@sywu ~]$ sggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 09:14:25 Operating system character set identified as UTF-8. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (sywu) 1> start mgr Manager started. GGSCI (sywu) 2> start PSYDB001 Sending START request to MANAGER ... EXTRACT PSYDB001 starting GGSCI (sywu) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING ESYDB001 00:00:00 00:00:02 EXTRACT RUNNING PSYDB001 00:00:00 12:41:34
啟動目標端GoldenGate程式
[oracle@sywu ~]$ tggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2014 09:14:25 Operating system character set identified as UTF-8. Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved. GGSCI (sywu) 1> start mgr Manager started. GGSCI (sywu) 2> start RSYDB001 Sending START request to MANAGER ... REPLICAT RSYDB001 starting GGSCI (sywu) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RSYDB001 00:00:00 00:00:07
程式都啟動完了，此時觀察源端和目標端trail檔案生成情況
[oracle@sywu ~]$ ls -ltr /u01/app/product/ogg_src/dirdat total 324 -rw-r-----. 1 oracle oinstall 1418 Aug 26 09:42 es000000 -rw-r-----. 1 oracle oinstall 1481 Aug 26 09:48 es000001 -rw-r-----. 1 oracle oinstall 1481 Aug 26 09:50 es000002 -rw-r-----. 1 oracle oinstall 1481 Aug 26 09:52 es000003 -rw-r-----. 1 oracle oinstall 1481 Aug 26 09:52 es000004 [oracle@sywu ~]$ ls -ltr /u01/app/product/ogg_trg/dirdat total 324 -rw-r-----. 1 oracle oinstall 193469 Aug 25 13:00 ps000000 -rw-r-----. 1 oracle oinstall 891 Aug 25 13:00 ps000001 -rw-r-----. 1 oracle oinstall 1652 Aug 25 13:07 ps000002 -rw-r-----. 1 oracle oinstall 891 Aug 25 13:07 ps000003 -rw-r-----. 1 oracle oinstall 105884 Aug 25 13:49 ps000004 -rw-r-----. 1 oracle oinstall 891 Aug 25 13:49 ps000005 -rw-r-----. 1 oracle oinstall 2095 Aug 26 09:54 ps000006 -rw-r-----. 1 oracle oinstall 1201 Aug 26 09:54 ps000007 -rw-r-----. 1 oracle oinstall 2032 Aug 26 09:54 ps000008
源端程式資訊
GGSCI (sywu) 4> info ESYDB001,detail
EXTRACT ESYDB001 Last Started 2015-08-26 09:52 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:10 ago)
Process ID 14619
Log Read Checkpoint Oracle Redo Logs
2015-08-26 09:58:11 Seqno 13, RBA 1942016
SCN 0.575310 (575310)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/u01/app/product/ogg_src/dirdat/es 4 1481 100 EXTTRAIL
Extract Source Begin End
/u01/oradata/sydb/redo01.log 2015-08-26 09:52 2015-08-26 09:58
/u01/oradata/sydb/redo01.log 2015-08-26 09:49 2015-08-26 09:52
/u01/oradata/sydb/redo01.log 2015-08-26 09:43 2015-08-26 09:49
/u01/oradata/sydb/redo01.log 2015-08-26 09:40 2015-08-26 09:43
/u01/oradata/sydb/redo01.log 2015-08-25 20:43 2015-08-26 09:40
Not Available * Initialized * 2015-08-25 20:43
Not Available * Initialized * 2015-08-25 20:43
Current directory /u01/app/product/ogg_src
Report file /u01/app/product/ogg_src/dirrpt/ESYDB001.rpt
Parameter file /u01/app/product/ogg_src/dirprm/esydb001.prm
Checkpoint file /u01/app/product/ogg_src/dirchk/ESYDB001.cpe
Process file /u01/app/product/ogg_src/dirpcs/ESYDB001.pce
Error log /u01/app/product/ogg_src/ggserr.log
GGSCI (sywu) 5> info PSYDB001,detail
EXTRACT PSYDB001 Last Started 2015-08-26 09:52 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Process ID 14648
Log Read Checkpoint File /u01/app/product/ogg_src/dirdat/es000004
2015-08-26 09:52:50.035063 RBA 1481
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/u01/app/product/ogg_trg/dirdat/ps 0 0 100 EXTTRAIL
Extract Source Begin End
/u01/app/product/ogg_src/dirdat/es000004 * Initialized * 2015-08-26 09:52
/u01/app/product/ogg_src/dirdat/es000000 * Initialized * First Record
/u01/app/product/ogg_src/dirdat/es000000 * Initialized * First Record
/u01/app/product/ogg_src/dirdat/es000000 * Initialized * First Record
/u01/app/product/ogg_src/dirdat/es000000 * Initialized * First Record
Current directory /u01/app/product/ogg_src
Report file /u01/app/product/ogg_src/dirrpt/PSYDB001.rpt
Parameter file /u01/app/product/ogg_src/dirprm/psydb001.prm
Checkpoint file /u01/app/product/ogg_src/dirchk/PSYDB001.cpe
Process file /u01/app/product/ogg_src/dirpcs/PSYDB001.pce
Error log /u01/app/product/ogg_src/ggserr.log
GGSCI (sywu) 6> stats ESYDB001
Sending STATS request to EXTRACT ESYDB001 ...
No active extraction maps.
GGSCI (sywu) 7> stats PSYDB001
Sending STATS request to EXTRACT PSYDB001 ...
No active extraction maps.
目標端程式資訊
GGSCI (sywu) 5> info RSYDB001,detail
REPLICAT RSYDB001 Last Started 2015-08-26 09:55 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:08 ago)
Process ID 15105
Log Read Checkpoint File /u01/app/product/ogg_trg/dirdat/ps000008
First Record RBA 2032
Current Log BSN value: (requires database login)
Last Committed Transaction CSN value: (requires database login)
Extract Source Begin End
/u01/app/product/ogg_trg/dirdat/ps000008 2015-08-26 09:52 First Record
/u01/app/product/ogg_trg/dirdat/ps000008 2015-08-25 13:58 2015-08-26 09:52
/u01/app/product/ogg_trg/dirdat/ps000006 2015-08-25 13:49 2015-08-25 13:58
/u01/app/product/ogg_trg/dirdat/ps000006 2015-08-25 13:49 2015-08-25 13:49
/u01/app/product/ogg_trg/dirdat/ps000006 2015-08-25 13:49 2015-08-25 13:49
/u01/app/product/ogg_trg/dirdat/ps000006 * Initialized * 2015-08-25 13:49
/u01/app/product/ogg_trg/dirdat/ps000006 * Initialized * First Record
/u01/app/product/ogg_trg/dirdat/ps000000 * Initialized * First Record
/u01/app/product/ogg_trg/dirdat/ps000000 * Initialized * First Record
/u01/app/product/ogg_trg/dirdat/ps000000 * Initialized * First Record
/u01/app/product/ogg_trg/dirdat/ps000000 * Initialized * First Record
Current directory /u01/app/product/ogg_trg
Report file /u01/app/product/ogg_trg/dirrpt/RSYDB001.rpt
Parameter file /u01/app/product/ogg_trg/dirprm/rsydb001.prm
Checkpoint file /u01/app/product/ogg_trg/dirchk/RSYDB001.cpr
Checkpoint table OGG_TRG.OGG_CHK
Process file /u01/app/product/ogg_trg/dirpcs/RSYDB001.pcr
Error log /u01/app/product/ogg_trg/ggserr.log
GGSCI (sywu) 6> stats RSYDB001
Sending STATS request to REPLICAT RSYDB001 ...
No active replication maps.
在源端產生DML操作
OGG_OWNER@sydb>insert into togg(id,name)values(1003,'tt') ; 1 row created. Elapsed: 00:00:00.00 OGG_OWNER@sydb>insert into togg(id,name,type)values(1004,'sywu','user'); 1 row created. Elapsed: 00:00:00.01 OGG_OWNER@sydb>commit; Commit complete. Elapsed: 00:00:00.01
再次檢視源端程式狀態
GGSCI (sywu) 10> stats ESYDB001
Sending STATS request to EXTRACT ESYDB001 ...
Start of Statistics at 2015-08-26 10:04:54.
Output to /u01/app/product/ogg_src/dirdat/es:
Extracting from OGG_OWNER.TOGG to OGG_OWNER.TOGG:
*** Total statistics since 2015-08-26 10:04:33 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2015-08-26 10:04:33 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2015-08-26 10:04:33 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2015-08-26 10:04:33 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
GGSCI (sywu) 11> stats PSYDB001
Sending STATS request to EXTRACT PSYDB001 ...
Start of Statistics at 2015-08-26 10:04:58.
Output to /u01/app/product/ogg_trg/dirdat/ps:
Extracting from OGG_OWNER.TOGG to OGG_OWNER.TOGG:
*** Total statistics since 2015-08-26 10:04:35 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2015-08-26 10:04:35 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2015-08-26 10:04:35 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2015-08-26 10:04:35 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
再次檢視目標端程式狀態
GGSCI (sywu) 7> stats RSYDB001
Sending STATS request to REPLICAT RSYDB001 ...
Start of Statistics at 2015-08-26 10:05:03.
Replicating from OGG_OWNER.TOGG to OGG_TRG.TOGG:
*** Total statistics since 2015-08-26 10:04:45 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Daily statistics since 2015-08-26 10:04:45 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Hourly statistics since 2015-08-26 10:04:45 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
*** Latest statistics since 2015-08-26 10:04:45 ***
Total inserts 2.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 2.00
End of Statistics.
資料稽查
SYS@sydb>select count(*) from ogg_owner.togg;
COUNT(*)
----------
1002
Elapsed: 00:00:00.00
SYS@sydb>select count(*) from ogg_trg.togg;
COUNT(*)
----------
1002
Elapsed: 00:00:00.05
SYS@sydb>column name format a10
SYS@sydb>select id,name,type from ogg_owner.togg where name='sywu';
ID NAME TYPE
---------- ---------- -------------------
1004 sywu user
Elapsed: 00:00:00.00
SYS@sydb>select id,name,type from ogg_trg.togg where name='sywu';
ID NAME TYPE
---------- ---------- -------------------
1004 sywu user
Elapsed: 00:00:00.01
5 總結
GoldenGate透過自身的資料加工、處理、傳送、應用以及借用RDBMS資料庫系統的優勢保證資料的可同步性和準確性，並在錯誤和故障發生的情況下以RDBMS系統特有的特性(如Oracle 內部同步變更號(SCN)和檢查點)保障了資料的可恢復性和一致性，為同步的資料實時性和準確性提供了準確性和可稽查性。
--The end(2015-08-26)
GoldenGate更新丟失問題
GoldenGate單向表DML同步
Oracle GoldenGate 系列：Extract 程式的恢復原理
Oracle GoldenGate安裝配置
Oracle goldengate的OGG-01004 OGG-1296錯誤
Oracle GoldenGate快速入門教程：基本概念和配置
搭建一個Oracle到Oracle的GoldenGate雙向複製環境
GoldenGate的安全配置
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29209863/viewspace-2138983/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【GoldenGate】Oracle GoldenGate(二) 雙向同步配置GoOracle
- Oracle GoldenGate學習總結OracleGo
- oracle goldengate引數學習OracleGo
- oracle goldengate 配置OracleGo
- goldengate for oracle 10g學習GoOracle 10g
- 【GoldenGate】Oracle GoldenGate(三) DDL同步配置GoOracle
- Oracle學習筆記(一)---oracle安裝和配置Oracle筆記
- GoldenGate學習筆記(4)_程式配置與管理Go筆記
- Oracle goldengate 安裝配置OracleGo
- oracle GoldenGate Veridata配置OracleGo
- Oracle GoldenGate安裝（二）OracleGo
- 【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 單向同步配置 上OracleGo
- 【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 單向同步配置 下OracleGo
- GoldenGate配置（二）之雙向複製配置Go
- 天天學習ORACLE(二）Oracle
- GoldenGate學習筆記(6)_配置例程之InitialLoadGo筆記
- Oracle GoldenGate Director配置手冊OracleGo
- Snmp學習總結(二)——WinXP安裝和配置SNMP
- Oracle GoldenGate: 使用巨集OracleGo
- oracle資料庫配置goldengate同步Oracle資料庫Go
- oracle goldengate 雙向複製配置OracleGo
- 【GoldenGate】Oracle GoldenGate Veridata 安裝配置與應用GoOracle
- GoldenGate學習筆記(5)_配置例程之單向複製Go筆記
- webpack學習（二）初識打包配置Web
- MSSQL配置學習筆記之二SQL筆記
- oracle分割槽表學習（二）Oracle
- Oracle學習筆記之二Oracle筆記
- Oracle GoldenGate Veridata 12.2.1.4安裝配置使用全手冊OracleGo
- 【GoldenGate】Oracle GoldenGate(一) 安裝與DML單向同步配置GoOracle
- goldengate學習-安裝篇Go
- 軟體測試學習教程——JDBC配置JDBC
- oracle goldengate 配置DML&DDL實驗OracleGo
- 配置Oracle GoldenGate for DB2(雙向)OracleGoDB2
- GOLDENGATE安裝和配置手冊Go
- nginx學習（二）：初識配置檔案Nginx
- Oracle Data Integrator和GoldenGate整合OracleGo
- 【goldengate】官方文件筆記二 Oracle GoldenGate 處理介面（GGSCI）Go筆記Oracle
- 【Vue學習（二）元件和插槽】Vue元件