转载

Oracle GoldenGate 三、加密

写在开始前

从上周开始，我花了大量的业余时间阅读GoldenGate官方文档，并根据文档实践和进一步学习了解GoldenGate，以下便是根据官方文档理解总结的GoldenGate学习内容:

Oracle GoldenGate 一、介绍和安装

Oracle GoldenGate 二、配置和使用

在[美] 莫提默·J.艾德勒，[美] 查尔斯·范多伦著的【 How to Read a Book 】一书中，作者强调如果你每天所学的知识你不能用你自己的语言去描述或记忆它，那说明你未真正学习和了解所学的知识，So 那以后我改变了我的学习方式，把生搬硬套的学习方式改变为用自己的语言用、自己的了解去描述和记忆知识，描述难免有错，请谅解，也请指导，谢谢！

1 加密的使用场景

GoldenGate参数文件
在每个Extract进程和Replicat进程的USERID、TRANLOGOPTIONS、DDLOPTIONS、DBOPTIONS参数中都要指定密码，密码是比较敏感又比较重要的信息，因为GoldenGate用户具有比较多的数据库权限，所以有效的保护密码是GoldenGate关于安全的首要之一。
trail文件
主抽取进程(Primary Extract)从数据库中抽取变更信息并加密写入到trail文件，然后再由Data Pump(Secondly Extract)进程解密实现复杂操作，加密成最终trail文件通过网络发送到目标端，目标端GoldenGate后台collector进程将trail文件保存到目标端trail文件，replicat进程读取文件内容并将其解密、重构、应用到目标库。
GGSCI命令行
使用DBLOGIN登录数据库时提供的密码。

2 GoldenGate提供的加密算法

AES128
使用AES 128加密，具有128位的密钥大小
AES192
使用AES 192加密，具有192位的密钥大小
AES256
使用AES 256加密，具有256位的密钥大小
BLOWFISH
使用64位块大小和从32位到128位的可变长度密钥的Blowfish加密，建议只在GoldenGate较早版本中使用，仅在ENCRYPTKEY为DEFAULT时可使用此种加密方式。

3 产生密钥

必须先产生密钥并且保存在ENCKEYS LookUp文件中然后才能使用以下功能

使用ENCRYPT PASSWORD PWD ENCRYPTKEY 生成加密密码
在配置文件参数ENCRYPTTRAIL KEYNAME 中指定trail文件加密方式和密钥名
在配置文件参数RMHOST或RMTHOSTOPTIONS ENCRYPT 中指定加密方式和密钥名

产生密钥

切换到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的长度，所以使用时指定的加密类型密钥长度也必须一致。

4 源端加密配置

源端配置主抽取进程加密和Data Pump进程加密。

4.1 配置主抽取进程

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)；

4.2 配置Data Pump进程

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

5 目标端解密配置

目标端后台Collector进程在接受到源端的发送请求后将数据写入到目标端trail文件中，再由replicat进程读取、解密、重构dml或ddl语句应用到数据库，so 如果源端配置了加密则目标端必须进行解密配置，并且解密的类型和key必须和源端相同，操作步骤:

1 将源端创建的ENCKEYS文件发送到目标端GoldenGate根目录
2 Replicat进程配置解密操作
3 重启replicat进程

发送源端ENCKEYS文件到目标端可以scp或复制粘贴到目标端，此处省略其操作。

5.1 Replicat进程配置解密操作

[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 进程。

6 未加密和加密trail文件对比分析

6.1 未加密trail文件分析

[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)；

6.2 加密trail文件分析

在源端插入如下数据:

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读取后进行解密、重构、应用到目标库。

7 OBEY文件

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级子文件递归调用，这也就意外着可以将更多的配置细化管理。

8 总结

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)

正文到此结束