從上周開始,我花了大量的業余時間閱讀GoldenGate官方文檔,並根據文檔實踐和進一步學習了解GoldenGate,以下便是根據官方文檔理解總結的GoldenGate學習內容:
Oracle GoldenGate 一、介紹和安裝
Oracle GoldenGate 二、配置和使用
在[美] 莫提默·J.艾德勒,[美] 查爾斯·范多倫著的【How to Read a Book】一書中,作者強調如果你每天所學的知識你不能用你自己的語言去描述或記憶它,那說明你未真正學習和了解所學的知識,So 那以後我改變了我的學習方式,把生搬硬套的學習方式改變為用自己的語言用、自己的了解去描述和記憶知識,描述難免有錯,請諒解,也請指導,謝謝!
必須先產生密鑰並且保存在ENCKEYS LookUp文件中然後才能使用以下功能
產生密鑰
切換到GoldenGate home 目錄,使用KEYGEN工具產生密鑰;語法:
./keygen <key length> <n>
key length 表示加密密鑰的長度;
n 表示產生多少個key;
[oracle@sywu ogg_src]$ ./keygen 128 2
0xDEE44B0133536B0DA1B858620E4A240D
0x71DF8D01C352097FC76BBA31232DA95A
每一次產生的key都是不一樣的,產生key後為每一個key定義一個名稱(keyName)復制保存到GoldenGate home根目錄的ENCKEYS文件內。ENCKEYS文件是一個lookup file。
保存key到ENCKEYS文件
[oracle@sywu ogg_src]$ vim ENCKEYS
#GoldenGate encryption key
#keyName key
securekey1 0xDEE44B0133536B0DA1B858620E4A240D
securekey2 0x71DF8D01C352097FC76BBA31232DA95A
在GGSCI命令行通過key對密碼加密
GGSCI (sywu) 8> encrypt password ogg_owner aes128 encryptkey securekey1
Encrypted password: AADAAAAAAAAAAAJATJEEYELAGIQFZDWHQAMDMCCHGIVGFIPHOCABMAYCHGSCPHGILCPCLCXCCHUEFGOC
Algorithm used: AES128
GGSCI (sywu) 9> encrypt password ogg_owner aes128 encryptkey securekey1
Encrypted password: AADAAAAAAAAAAAJANJBHVDBAGCCBOIUCTJHJVIOCVGBFSGNJFFAAGIOHBJNBWAPANGWILCPFGIXBOIXB
Algorithm used: AES128
密碼加密後可以嘗試使用dblogin登錄數據庫,驗證密碼
GGSCI (sywu as ogg_owner@sydb) 11> dblogin userid ogg_owner,password AADAAAAAAAAAAAJANJBHVDBAGCCBOIUCTJHJVIOCVGBFSGNJFFAAGIOHBJNBWAPANGWILCPFGIXBOIXB aes128 encryptkey securekey1
Successfully logged into database.
注意:因為在創建key時指定了key的長度,所以使用時指定的加密類型密鑰長度也必須一致。
源端配置主抽取進程加密和Data Pump進程加密。
GGSCI (sywu as ogg_owner@sydb) 31> EDIT PaRAM ESYDB001
extract ESYDB001
SETENV(ORACLE_SID="sydb")
SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg_owner,password AADAAAAAAAAAAAJANJBHVDBAGCCBOIUCTJHJVIOCVGBFSGNJFFAAGIOHBJNBWAPANGWILCPFGIXBOIXB &
aes128,ENCRYPTKEY securekey1
ENCRYPTTRAIL aes128,KEYNAME securekey1
EXTTRAIL /u01/app/product/ogg_src/dirdat/es
table ogg_owner.togg;
userid 中將password由原來的明碼用加密後的密碼代替,後面指定加密類型和密鑰名稱(ENCRYPTKEY);
ENCRYPTTRAIL 指定trail文件的加密類型和密鑰名稱(KEYNAME);
GGSCI (sywu) 10> edit param PSYDB001
extract psydb001
SETENV(ORACLE_SID="sydb")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg_owner,password AADAAAAAAAAAAAJANJBHVDBAGCCBOIUCTJHJVIOCVGBFSGNJFFAAGIOHBJNBWAPANGWILCPFGIXBOIXB aes128 encryptkey securekey1
DECRYPTTRAIL aes128 KEYNAME securekey1
RMTHOST sywu,mgrport 7909
ENCRYPTTRAIL aes128 KEYNAME securekey1
RMTTRAIL /u01/app/product/ogg_trg/dirdat/ps
TABLE ogg_owner.togg;
因為Data Pump要讀取主抽取進程保存的trail文件數據並且提供了對數據的操作功能如實現過濾、運算等復雜的工作,所以在讀取後必須先對原數據進行解密再處理,最後再次加密發送到目標端;
DECRYPTTRAIL 定義將要解密的文件的加密類型和加密key(KEYNAME);
ENCRYPTTRAIL 定義最終處理後的數據加密類型和加密key(KEYNAME);
注意:解密類型和keyname必須和主進程配置的相同。
啟動進程
GGSCI (sywu as ogg_owner@sydb) 42> start *
Sending START request to MANAGER ...
EXTRACT ESYDB001 starting
EXTRACT PSYDB001 is already running.
GGSCI (sywu as ogg_owner@sydb) 43> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ESYDB001 00:01:31 00:00:08
EXTRACT RUNNING PSYDB001 00:00:00 116:02:26
目標端後台Collector進程在接受到源端的發送請求後將數據寫入到目標端trail文件中,再由replicat進程讀取、解密、重構dml或ddl語句應用到數據庫,so 如果源端配置了加密則目標端必須進行解密配置,並且解密的類型和key必須和源端相同,操作步驟:
發送源端ENCKEYS文件到目標端可以scp或復制粘貼到目標端,此處省略其操作。
[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> edit param RSYDB001
REPLICAT rsydb001
SETENV(ORACLE_SID="sydb")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg_trg,password AADAAAAAAAAAAAHABDQFVJMADCAFECACYEPIQEJCFGDGMDHBRJXCUBOBQJEGLBPEBDMCOAACDILGAJKA &
aes128,ENCRYPTKEY securekey1
DISCARDFILE /u01/app/product/ogg_trg/discrd/reptr.desc,append,megabytes 512
DECRYPTTRAIL AES128, KEYNAME securekey1
ALLOWNOOPUPDATES
ASSUMETARGETDEFS
MAP OGG_OWNER.TOGG,target OGG_TRG.TOGG;
DECRYPTTRAIL 定義將要解密的文件的加密類型和加密密鑰(KEYNAME),這裡和源端必須相同;
這些都配置好了,重啟replicat 進程。
[oracle@sywu ~]$ strings /u01/app/product/ogg_src/dirdat/es000004
*uri:sywu::u01:app:product:ogg_src:ESYDB0016
(/u01/app/product/ogg_src/dirdat/es0000047
575523
575169
,............................................
Linux1
sywu2
2.6.32-431.23.3.el6.x86_643
##1 SMP Thu Jul 31 17:20:51 UTC 20144
x86_642
SYDB2
sydb3
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
11.2.0.3.09
+08:003
ESYDB0011
AVersion 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO4
ESYDB001Z
OGG_OWNER.TOGG
1003
1900-01-01:00:00:00
1900-01-01:00:00:00T
AAADX2AAGAAAAA2AAA
5755236
2.138.127Z
OGG_OWNER.TOGG
1004
sywu
user
1900-01-01:00:00:00
1900-01-01:00:00:00T
AAADX2AAGAAAAA2AAB
以上的trail文件來自上次未加密的測試環境中,具體的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
So 在未加密情況下可以清楚的看到新增的id(1003,1004)和rowid(AAADX2AAGAAAAA2AAA,AAADX2AAGAAAAA2AAB);
在源端插入如下數據:
SYS@sydb>conn ogg_owner/ogg_owner
Connected.
OGG_OWNER@sydb>insert into togg(id,name,type)values(1005,'sywu','user');
1 row created.
Elapsed: 00:00:00.09
OGG_OWNER@sydb>insert into togg(id,name,type)values(1006,'sywu','user');
1 row created.
Elapsed: 00:00:00.01
OGG_OWNER@sydb>commit;
Commit complete.
Elapsed: 00:00:00.00
分析GoldenGate Data Pump進程
GGSCI (sywu) 11> stats PSYDB001
Sending STATS request to EXTRACT PSYDB001 ...
Start of Statistics at 2015-08-31 17:32:14.
Output to /u01/app/product/ogg_trg/dirdat/ps:
Extracting from OGG_OWNER.TOGG to OGG_OWNER.TOGG:
*** Total statistics since 2015-08-31 17:29:53 ***
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-31 17:29:53 ***
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-31 17:29:53 ***
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-31 17:29:53 ***
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) 12> info PSYDB001,detail
EXTRACT PSYDB001 Last Started 2015-08-31 17:17 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 31884
Log Read Checkpoint File /u01/app/product/ogg_src/dirdat/es000007
2015-08-31 17:29:49.000000 RBA 1865
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
/u01/app/product/ogg_trg/dirdat/ps 10 2043 100 RMTTRAIL
Extract Source Begin End
/u01/app/product/ogg_src/dirdat/es000007 2015-08-26 10:04 2015-08-31 17:29
/u01/app/product/ogg_src/dirdat/es000004 2015-08-26 10:04 2015-08-26 10:04
/u01/app/product/ogg_src/dirdat/es000004 2015-08-26 10:04 2015-08-26 10:04
/u01/app/product/ogg_src/dirdat/es000004 2015-08-26 10:04 2015-08-26 10:04
/u01/app/product/ogg_src/dirdat/es000004 2015-08-26 10:04 2015-08-26 10:04
/u01/app/product/ogg_src/dirdat/es000004 2015-08-26 10:04 2015-08-26 10:04
/u01/app/product/ogg_src/dirdat/es000004 * Initialized * 2015-08-26 10:04
/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
源端Data Pump進程從主抽取進程保存的trail文件中讀取到兩條插入信息,它讀取的trail文件是:/u01/app/product/ogg_src/dirdat/es000007;注意它們的工作原理,主抽取進程抽取數據加密並保存到trail文件,Data Pump進程讀取trail文件解密再進行數據操作,最後發送;
[oracle@sywu ~]$ strings /u01/app/product/ogg_src/dirdat/es000007
*uri:sywu::u01:app:product:ogg_src:ESYDB0016
(/u01/app/product/ogg_src/dirdat/es0000077
597066
,............................................
Linux1
sywu2
2.6.32-431.23.3.el6.x86_643
##1 SMP Thu Jul 31 17:20:51 UTC 20144
x86_642
SYDB2
sydb3
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
11.2.0.3.09
+08:003
ESYDB0011
AVersion 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO4
ESYDB001Z
OGG_OWNER.TOGG
AAADX2AAGAAAAA7AAA
5981816
1.86.100Z
OGG_OWNER.TOGG
uaDL
AAADX2AAGAAAAA7AAB
數據加密了,看不到了。
源端replicat進程信息
[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 all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RSYDB001 00:00:00 00:00:03
GGSCI (sywu) 4> stats RSYDB001
Sending STATS request to REPLICAT RSYDB001 ...
Start of Statistics at 2015-08-31 18:40:08.
Replicating from OGG_OWNER.TOGG to OGG_TRG.TOGG:
*** Total statistics since 2015-08-31 17:30:03 ***
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-31 17:30:03 ***
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-31 18:00:00 ***
No database operations have been performed.
*** Latest statistics since 2015-08-31 17:30:03 ***
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) 2> info RSYDB001,detail
REPLICAT RSYDB001 Last Started 2015-08-31 17:16 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Process ID 31615
Log Read Checkpoint File /u01/app/product/ogg_trg/dirdat/ps000010
2015-08-31 17:29:49.000465 RBA 2043
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/ps000010 2015-08-26 10:04 2015-08-31 17:29
/u01/app/product/ogg_trg/dirdat/ps000008 2015-08-26 09:52 2015-08-26 10:04
/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
目標端同樣插入兩條數據,replicate進程從/u01/app/product/ogg_trg/dirdat/ps000010文件中讀取了後台collector進程接受到的trail數據。
[oracle@sywu ~]$ strings /u01/app/product/ogg_trg/dirdat/ps000010
*uri:sywu::u01:app:product:ogg_src:PSYDB0015
*uri:sywu::u01:app:product:ogg_src:ESYDB0016
(/u01/app/product/ogg_trg/dirdat/ps0000107
598181
594135
,............................................
Linux1
sywu2
2.6.32-431.23.3.el6.x86_643
##1 SMP Thu Jul 31 17:20:51 UTC 20144
x86_642
SYDB2
sydb3
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
11.2.0.3.09
+08:003
ESYDB0011
AVersion 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO4
ESYDB001Z
ESYDB001Z
ESYDB001Z
OGG_OWNER.TOGG
AAADX2AAGAAAAA7AAA
5981816
1.86.100Z
OGG_OWNER.TOGG
uaDL
AAADX2AAGAAAAA7AAB
文件的信息同樣是加密的,replicate讀取後進行解密、重構、應用到目標庫。
GodlenGate的每個Extract、Replicat進程都需要配置數據庫連接,隨著進程的增加和各自分工不同或數據交換的目標地不同,如果每個都這樣配置,那當數據庫用戶密碼改變了,那豈不是要大動干戈的修改,有沒有什麼方式可以像面向對象設計一樣把公共的部分提取出來呢?肯定有了,GoldenGate提供了一個叫OBEY的參數,允許將公共常用的部分提取保存到獨立的文件中實現共享和重用,例如下面的進程配置:
GGSCI (sywu) 2> view param PSYDB001
extract psydb001
SETENV(ORACLE_SID="sydb")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg_owner,password AADAAAAAAAAAAAJANJBHVDBAGCCBOIUCTJHJVIOCVGBFSGNJFFAAGIOHBJNBWAPANGWILCPFGIXBOIXB aes128 encrypt
key securekey1
DECRYPTTRAIL aes128 KEYNAME securekey1
RMTHOST sywu,mgrport 7909
ENCRYPTTRAIL aes128 KEYNAME securekey1
RMTTRAIL /u01/app/product/ogg_trg/dirdat/ps
TABLE ogg_owner.togg;
數據庫連接、DECRYPTTRAIL、RMTHOST這些參數基本都是共用和不常變的,so 可以將其保存到獨立的文件中;
[oracle@sywu dirdef]$ vim /u01/app/product/ogg_src/dirdef/dbConnect.obey
userid ogg_owner,password AADAAAAAAAAAAAJANJBHVDBAGCCBOIUCTJHJVIOCVGBFSGNJFFAAGIOHBJNBWAPANGWILCPFGIXBOIXB aes128 encrypt
key securekey1
DECRYPTTRAIL aes128 KEYNAME securekey1
RMTHOST sywu,mgrport 7909
然後在進程參數配置文件中通過OBEY引用該文件;
extract psydb001
SETENV(ORACLE_SID="sydb")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
OBEY /u01/app/product/ogg_src/dirdef/dbConnect.obey
ENCRYPTTRAIL aes128 KEYNAME securekey1
RMTTRAIL /u01/app/product/ogg_trg/dirdat/ps
TABLE ogg_owner.togg;
這樣以後涉及數據庫連接的信息就只用更改該文件了,GoldenGate支持16級子文件遞歸調用,這也就意外著可以將更多的配置細化管理。
GoldenGate提供了AES128、AES192、AES256和BLOWFISH類型加密,BLOWFISH在keyname為default時可用,一般用於較早版本中,AES類型的加密更為安全。主抽取進程的加密是可選的,可以只在Data Pump進程中進行數據加密。進程的加密要考慮到加密的長度和密鑰名稱,必須先用GoldenGate提供的工具keygen或其它的工具生成密鑰保存在ENCKEYS lookUp文件中,然後在GGSCI命令行中根據密碼類型和ENCKEYS中的密鑰名稱生成加密密碼,最後在配置文件或GGSCI命令中使用加密密碼。目標端必須具有和源端相同的ENCKEYS lookUp文件並且在配置解密時,加密解密的類型和密鑰名稱要一致。
--The end(2015-08-31)