本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: GoldenGate 学习系列5–ogg加密
Oracle GoldenGate主要用于数据同步，其功能和应用场景就不多少了，对于OGG的的使用场景，我们很多时候
可能需要关注数据的安全性问题，因此这里讲讲OGG的加密。
+++++ 加密用户密码 +++++++
源端操作： --获取key -bash-3.2$ ./keygen 128 1 0x74D61512EFA7E9412D42790094184D5B --创建秘钥问题 -bash-3.2$ vi ENCKEYS kasaur_key 0x74D61512EFA7E9412D42790094184D5B ~ ~ "ENCKEYS" [New] 1L, 47C written --登陆ggsci,产生加密值 -bash-3.2$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x86, 32bit (optimized), Oracle 11g on Apr 23 2012 08:09:25 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (11gr2test) 1> encrypt password ggs encryptkey kasaur_key Encrypted password: AADAAAAAAAAAAADAHJWBKENELJOJCDVAEEEDHJSEHDFHODSIZASAYDACSEHAMBIJKAFJNFFIDERBCHQI Algorithm used: AES128 GGSCI (11gr2test) 3> dblogin userid ggs@ROGER,password AADAAAAAAAAAAADAHJWBKENELJOJCDVAEEEDHJSEHDFHODSIZASAYDACSEHAMBIJKAFJNFFIDERBCHQI,encryptkey kasaur_key Successfully logged into database. GGSCI (11gr2test) 4> --编辑源端相关ogg进程的参数文件 GGSCI (11gr2test) 2> edit param ext2 extract ext2 userid ggs@ROGER,password AADAAAAAAAAAAADAHJWBKENELJOJCDVAEEEDHJSEHDFHODSIZASAYDACSEHAMBIJKAFJNFFIDERBCHQI,encryptkey kasaur_key TRANLOGOPTIONS DBLOGREADER ----TRANLOGOPTIONS ASMUSER sys@+ASM, ASMPASSWORD oracle discardfile ./dirrpt/ext2.dsc, append, megabytes 50 warnlongtrans 2h, checkinterval 3m EXTTRAIL ./dirdat/ex NUMFILES 3000 ----ALLOCFILES 200 table roger.tb1; table roger.tb10; 目标端操作： --获取key -bash-3.00$ ./keygen 128 1 0x1B618F3A5A037B1602791B3B9263BC2B --创建秘钥文件 -bash-3.00$ vi ENCKEYS kasaur_key 0x1B618F3A5A037B1602791B3B9263BC2B ~ ~ ~ "ENCKEYS" [New] 1L, 46C written --登陆ggsci控制台，产生秘钥 -bash-3.00$ ./ggsci ...... ...... GGSCI (killdb.com) 1> encrypt password ggs encryptkey kasaur_key Encrypted password: AADAAAAAAAAAAADAKHEJYIFGVAKDPFZBGDFJNEQBBJRISJAAOCHHZEWCEFTCRIRCJDSHUHAJZBFDZEWC Algorithm used: AES128 --编辑replicat进程参数 GGSCI (killdb.com) 2> edit param rep5 replicat rep5 userid ggs@Roger,password AADAAAAAAAAAAADAKHEJYIFGVAKDPFZBGDFJNEQBBJRISJAAOCHHZEWCEFTCRIRCJDSHUHAJZBFDZEWC,encryptkey kasaur_key reperror default, discard DISCARDROLLOVER AT 20:30 discardfile ./dirrpt/rep5.dsc, append, megabytes 50 assumetargetdefs allownoopupdates numfiles 3000 ---gettruncates map roger.tb1, target roger.tb1; map roger.tb10, target roger.tb10; ---map ggs.*,target ggs.* ~
++++++ 加密trail文件，包括extract，pump以及replicat +++++++++
源端操作：
---获取AES192 key -bash-3.2$ ./keygen 192 2 0x19CFE0756164E7249C3BFD1724E9F626DA720A507F26F74C 0xB82BDA73C8CD371BC0D57F583E1FD92C99F94562525FB30F ---将产生的key信息加入到秘钥文件 -bash-3.2$ vi ENCKEYS kasaur_key 0x74D61512EFA7E9412D42790094184D5B key_ext2 0x19CFE0756164E7249C3BFD1724E9F626DA720A507F26F74C key_dp1 0xB82BDA73C8CD371BC0D57F583E1FD92C99F94562525FB30F ~ ---编辑抽取进程和pump进程参数，添加秘钥信息: GGSCI (11gr2test) 4> view param ext2 extract ext2 userid ggs@ROGER,password AADAAAAAAAAAAADAHJWBKENELJOJCDVAEEEDHJSEHDFHODSIZASAYDACSEHAMBIJKAFJNFFIDERBCHQI,encryptkey kasaur_key TRANLOGOPTIONS DBLOGREADER ----TRANLOGOPTIONS ASMUSER sys@+ASM, ASMPASSWORD oracle discardfile ./dirrpt/ext2.dsc, append, megabytes 50 warnlongtrans 2h, checkinterval 3m ---Encrypt My trail with AES192 ENCRYPTTRAIL AES192 KEYNAME key_ext2 EXTTRAIL ./dirdat/ex NUMFILES 3000 ----ALLOCFILES 200 table roger.tb1; table roger.tb10; GGSCI (11gr2test) 5> view param dp1 EXTRACT dp1 RMTHOST 192.168.109.12, MGRPORT 7810 TCPBUFSIZE 5000000 PASSTHRU DECRYPTTRAIL AES192 KEYNAME key_ext2 ENCRYPTTRAIL AES192 KEYNAME key_dp1 RMTTRAIL ./dirdat/rn NUMFILES 3000 TABLE roger.tb1; table roger.tb10; --- 启动源端ogg进程 GGSCI (11gr2test) 6> start ext2 Sending START request to MANAGER ... EXTRACT EXT2 starting GGSCI (11gr2test) 7> start dp1 Sending START request to MANAGER ... EXTRACT DP1 starting GGSCI (11gr2test) 8> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING DP1 00:00:00 00:17:57 EXTRACT STOPPED EXT1 14:55:12 03:26:57 EXTRACT RUNNING EXT2 00:18:00 00:00:03 REPLICAT STOPPED REP1 00:00:00 8607:39:28
目标端配置：
—编辑秘钥文件
-bash-3.00$ vi ENCKEYS kasaur_key 0x1B618F3A5A037B1602791B3B9263BC2B key_dp1 0xB82BDA73C8CD371BC0D57F583E1FD92C99F94562525FB30F
在目标端的秘钥存放文件中添加解密的key信息. 注意这里的key_dp1和源端的pump进程的keyname 必须一致，否则replicat进程
无法解析pump进程传递过来的队列文件。
---编辑replicat进程参数文件并启动replicat进程 GGSCI (killdb.com) 4> edit param rep5 AES192 KEYNAME key_dp1 replicat rep5 userid ggs@Roger,password AADAAAAAAAAAAADAKHEJYIFGVAKDPFZBGDFJNEQBBJRISJAAOCHHZEWCEFTCRIRCJDSHUHAJZBFDZEWC,encryptkey kasaur_key reperror default, discard DISCARDROLLOVER AT 20:30 discardfile ./dirrpt/rep5.dsc, append, megabytes 50 assumetargetdefs allownoopupdates numfiles 3000 ---gettruncates DECRYPTTRAIL AES192 KEYNAME key_dp1 map roger.tb1, target roger.tb1; map roger.tb10, target roger.tb10; ---map ggs.*,target ggs.* ~ ~ "dirprm/rep5.prm" 15L, 452C written GGSCI (killdb.com) 5> start rep5 Sending START request to MANAGER ... REPLICAT REP5 starting GGSCI (killdb.com) 6> info rep5 REPLICAT REP5 Last Started 2014-11-22 22:36 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:02 ago) Log Read Checkpoint File ./dirdat/rn000091 2014-11-22 22:34:44.251981 RBA 1320
经过上面一系列的准备工作之后，我们可以来测试验证数据是否加密成功。
–源端插入测试数据
SQL> insert into tb10 values('baidu',9999);
1 row created.
SQL> commit;
Commit complete.
SQL>
GGSCI (11gr2test) 12> info ext2,detail
EXTRACT EXT2 Last Started 2014-11-22 22:34 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:11 ago)
Log Read Checkpoint Oracle Redo Logs
2014-11-22 22:41:24 Seqno 411, RBA 4060160
SCN 0.13637649 (13637649)
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
./dirdat/ex 8 1207 100
......
......
通过logdump 分析trail文件，我们发现看到具体的内容:
Logdump 54 >open ./dirdat/ex000008 Current LogTrail is /home/ggs/ggs_home/dirdat/ex000008 Logdump 55 >ghdr on Logdump 56 >detail on Logdump 57 >detail data Logdump 58 >usertoken on Logdump 59 >FILTER include filename ROGER.tb10 Logdump 60 >n Filtering suppressed 3 records Logdump 61 >n
目标端：
GGSCI (killdb.com) 8> info rep5 REPLICAT REP5 Last Started 2014-11-22 22:36 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:09 ago) Log Read Checkpoint File ./dirdat/rn000091 2014-11-22 22:41:14.236448 RBA 1459 Logdump 28 >open ./dirdat/rn000091 LogTrail /home/ggs/ggs_home/dirdat/rn000087 closed Current LogTrail is /home/ggs/ggs_home/dirdat/rn000091 Logdump 29 >ghdr on Logdump 30 >detail on Logdump 31 >detail data Logdump 32 >usertoken on Logdump 33 >FILTER include filename ROGER.tb10 Logdump 34 >n Filtering suppressed 4 records Logdump 35 >n
目标端的队列文件内容也是无法通过logdump工具进行查看的. 验证数据是否同步：
www.killdb.com>select * from tb10 where table_name='baidu'; TABLE_NAME BLOCKS ------------------------------ ---------- baidu 9999 www.killdb.com>
Related posts:
