TDE加密表OGG数据同步

参考文档：文档 ID 1451327.1
一、DB补丁升级

Apply database 10395645  for oracle 10.2.0.5 or 11.2.0.2
二、dbms_internal_clkm授权

sqlplus / as sysdba
@?/rdbms/admin/prvtclkm.plb
grant execute on sys.dbms_internal_clkm to ogg;
三、wallet配置

1、wallet目录创建

mkdir -p /u01/app/oracle/admin/srcdb/wallet
2、sqlnet.ora文件参数配置

vi /u01/app/11.2.0/grid/network/admin/sqlnet.ora
// 添加内容如下：
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(	DIRECTORY=/u01/app/oracle/admin/srcdb/wallet)))
3、oracle用户mkstore创建wallet

cd /u01/app/oracle/admin/srcdb/wallet
mkstore -wrl . -create
## 输出信息如下:
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter password:       ogg4tdetb                    
Enter password again: ogg4tdetb  

mkstore -wrl . -list
## 输出结果：
Oracle Secret Store entries: 
ORACLE.SECURITY.DB.ENCRYPTION.AZae2dPhAE+vv+cfnok4GtgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
4、wallet钱包open密钥设置

sqlplus / as sysdba
alter system set encryption key identified by "ogg4tdetb";  
alter system set encryption wallet close identified by "ogg4tdetb";
alter system set encryption wallet open identified by "ogg4tdetb";
alter system switch logfile;
5、goldengate安装用户添加

mkstore -wrl . -createEntry ORACLE.SECURITY.CL.ENCRYPTION.OGG
## 输出信息如下：
Your secret/Password is missing in the command line 
Enter your secret/Password:      	ogg2sync                                                                                                                                      
Re-enter your secret/Password:   	ogg2sync                                                                                                                                   
Enter wallet password:           ogg4tdetb

mkstore -wrl . -list 
## 输出信息如下：
Oracle Secret Store entries: 
ORACLE.SECURITY.CL.ENCRYPTION.OGG
ORACLE.SECURITY.DB.ENCRYPTION.AYfP2r4HQ08Kv0tXdrzv1YAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.AZae2dPhAE+vv+cfnok4GtgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY
四、goldengate密码加密串生成

echo "ENCRYPT PASSWORD ogg2sync BLOWFISH ENCRYPTKEY DEFAULT"|/ggsrc/ggsci
-- 输出信息如下：
GGSCI (learn) 1> Using Blowfish encryption with DEFAULT key.
Encrypted password:  AACAAAAAAAAAAAIAPJYFCAAIDDPJRDSD
Algorithm used:  BLOWFISH
五、goldengate抽取进程配置

add extract e_srctde,tranlog,begin now
add exttrail ./dirdat/td extract e_srctde MEGABYTES 1024

edit params e_srctde
## 参数内容如下：
EXTRACT e_srctde
dynamicresolution
gettruncates
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
SETENV (ORACLE_SID=srcdb)
SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)
userid ogg, password AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, encryptkey default
DBOPTIONS DECRYPTPASSWORD AACAAAAAAAAAAAIAPJYFCAAIDDPJRDSD ENCRYPTKEY DEFAULT
DISCARDFILE ./dirrpt/e_srctde.dsc,APPEND,MEGABYTES 1024
DBOPTIONS LOBBUFSIZE 1048576
TRANLOGOPTIONS DBLOGREADER
EXTTRAIL ./dirdat/td
table test.tab_wallet;
六、TDE加密测试表创建

--> TDE加密表空间创建

create tablespace ts_wallet datafile '+DATA' size 1g autoextend off 	ENCRYPTION DEFAULT STORAGE(ENCRYPT);
--> TDE加密表创建

create table test.tab_wallet(id number(10),name varchar2(256),location 	varchar2(256),work varchar2(256)) tablespace ts_wallet;
insert into test.tab_wallet values(10,'huadajie','changsha','teacher');
insert into test.tab_wallet values(11,'jiaqiu','hengyang','student');
insert into test.tab_wallet values(12,'daolong','xiangxi','doctor');
select * from test.tab_wallet;
commit;
--> 加密表附加日志添加、抽取进程启动

./ggsci
dblogin userid ogg, password AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, encryptkey 	default
add trandata test.tab_wallet

start E_SRCTDE
--> TDE加密表增量数据同步测试

insert into test.tab_wallet values(99,'ailiangple','anhui','doctor');
commit;
--> 抽取进程数据同步信息

GGSCI (learn as ogg@srcdb) 39> stats E_SRCTDE

Sending STATS request to EXTRACT E_SRCTDE ...

Start of Statistics at 2019-09-24 15:41:12.

Output to ./dirdat/td:

Extracting from TEST.TAB_WALLET to TEST.TAB_WALLET:

*** Total statistics since 2019-09-24 15:39:38 ***
Total inserts                   	           4.00
Total updates                   	           0.00
Total deletes                   	           0.00
Total discards                  	           0.00
Total operations                	           4.00
--> trail数据logdump分析

[ogg@learn ggsrc]$ ./logdump

Oracle GoldenGate Log File Dump Utility for Oracle
Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359

Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.



Logdump 1 >open dirdat/td000000002
Current LogTrail is /ggsrc/dirdat/td000000002 
Logdump 2 >n

2019/09/24 15:39:34.875.578 FileHeader           Len  1368 RBA 0 
Name: *FileHeader* 
3000 02f3 3000 0008 4747 0d0a 544c 0a0d 3100 0002 | 0...0...GG..TL..1...  
0006 3200 0004 2000 0000 3300 0008 02f2 b97b 1804 | ..2... ...3......{..  
 25ba 3400 001b 0019 7572 693a 6c65 6172 6e3a 3a67 | %.4.....uri:learn::g  
 6773 7263 3a45 5f53 5243 5444 4536 0000 1600 142e | gsrc:E_SRCTDE6......  
 2f64 6972 6461 742f 7464 3030 3030 3030 3030 3237 | /dirdat/td0000000027  
 0000 0101 3800 0004 0000 0002 39ff 0008 0000 0000 | ....8.......9.......  
 0000 0000 3a00 0081 0130 0000 0000 0000 0000 0000 | ....:....0..........  

.............省略部分内容

Logdump 9 >n

2019/09/24 15:41:00.000.000 Insert               Len    59 RBA 2466 
Name: TEST.TAB_WALLET  (TDR Index: 1) 
After  Image:                                             Partition 12   G  s   
 0000 0a00 0000 0000 0000 0000 0063 0100 0e00 0000 | .............c......  
 0a00 6169 6c69 616e 6770 6c65 0200 0900 0000 0500 | ..ailiangple........  
 616e 6875 6903 000a 0000 0006 0064 6f63 746f 72   | anhui........doctor
七、trail文件数据同步至目标库

1、tgtdb目标库测试表创建

create table test.tab_wallet(id number(10),name varchar2(256),location 	varchar2(256),work varchar2(256)) tablespace USERS;
2、目标端复制进程配置

## 目标库复制进程添加、trail位置点指定
export ORACLE_SID=tgtdb
./ggsci
DBLOGIN USERID ogg, PASSWORD ogg
add replicat r_tde2tb, exttrail /ggsrc/dirdat/td,checkpointtable 	ogg.checkpoint
alter replicat r_tde2tb, extseqno 0, extrba 0

## 目标库复制进程参数配置
edit params r_tde2tb
## 参数内容如下：
replicat r_tde2tb
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
SETENV (ORACLE_SID=tgtdb)
SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)
userid ogg, password AACAAAAAAAAAAADAHBLDCCIIOIRFNEPB, encryptkey default
discardfile /ggtgt/dirrpt/r_tde2tb.dsc,append,megabytes 1024
gettruncates
MAP test.tab_wallets, target test.tab_wallet;

## 目标库复制进程启动
start r_tde2tb

## 复制进程状态检查
stats r_tde2tb
## 输出信息如下：
GGSCI (learn as ogg@tgtdb) 15> stats R_TDE2TB

Sending STATS request to REPLICAT R_TDE2TB ...

No active replication maps.
说明：复制进程默认配置方法，没办法应用TDE对应trail文件中的数据