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>
复制