love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Oracle 透明数据加密（TDE）功能是10.2 引入的，至于TDE的用途，顾名思义就是
防止非认证用户或其它心怀叵测的人进行对敏感数据的偷窥。
TDE 加密跟必须依赖于oracle wallet，关于wallet的管理，请参考：第一篇
<a href="http://www.killdb.com/2011/10/13/oracle-tde%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-wallet-%E4%BD%BF%E7%94%A8%E7%AE%A1%E7%90%86.html" style="font-size: 12px; text-decoration: underline; color: #0000ff; font-family: monospace;">oracle TDE学习系列(1) - wallet 使用管理</a>
在10.2版本中，仅仅限于列的加密，从11gR1开始又引入了表空间级别的加密，
下面是简单的配置和相关测试。

1

2

3

4

5

6

7

8

Oracle 透明数据加密（TDE）功能是10.2 引入的，至于TDE的用途，顾名思义就是

防止非认证用户或其它心怀叵测的人进行对敏感数据的偷窥。

TDE 加密跟必须依赖于oracle wallet，关于wallet的管理，请参考：第一篇

<a href="http://www.killdb.com/2011/10/13/oracle-tde%E5%AD%A6%E4%B9%A0%E7%B3%BB%E5%88%971-wallet-%E4%BD%BF%E7%94%A8%E7%AE%A1%E7%90%86.html" style="font-size: 12px; text-decoration: underline; color: #0000ff; font-family: monospace;">oracle TDE学习系列(1) - wallet 使用管理</a>

在10.2版本中，仅仅限于列的加密，从11gR1开始又引入了表空间级别的加密，

下面是简单的配置和相关测试。

++++++ 首先配置 wallet，创建密钥 ++++++
==== 在sqlnet.ora中加入如下信息：==== 
NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/ora11g/admin/roger/wallet )))

1

2

3

4

5

6

++++++ 首先配置 wallet，创建密钥 ++++++

==== 在sqlnet.ora中加入如下信息：====

NAMES.DIRECTORY_PATH=(TNSNAMES,EZCONNECT)

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/ora11g/admin/roger/wallet )))

==== 创建wallet目录 ==== 
[ora11g@11gr2test admin]$ mkdir -p  /home/ora11g/admin/roger/wallet

1

2

3

==== 创建wallet目录 ====

[ora11g@11gr2test admin]$ mkdir -p /home/ora11g/admin/roger/wallet

==== 生成加密密钥（master key）==== 
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@";
System altered.

1

2

3

4

5

==== 生成加密密钥（master key）====

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "roger007~!@";

System altered.

==== 进行加密列测试 ==== 
++++ 创建测试表 ++++ 
[ora11g@11gr2test ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 9 20:54:20 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> conn roger/roger
Connected.
SQL> create table ht1(name varchar2(10),salary number);
Table created.
SQL> insert into ht1 values('lizx','20000');
1 row created.
SQL> insert into ht1 values('yu','30000');
1 row created.
SQL> insert into ht1 values('hu','50000');
1 row created.
SQL> commit;
Commit complete.
++++++ modify column 进行列加密 ++++++ 
SQL> conn roger/roger
Connected.
SQL> desc ht1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NAME                                               VARCHAR2(10)
SALARY                                             NUMBER
SQL> alter table  ht1 modify (SALARY number ENCRYPT NO SALT);
Table altered.
SQL> select * from ht1;
NAME           SALARY
---------- ----------
lizx            20000
yu              30000
hu              50000
SQL> alter system set encryption wallet close identified by "roger007~!@";
System altered.
SQL> select * from roger.ht1;
select * from roger.ht1
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "roger007~!@";
System altered.
SQL> select * from roger.ht1;
NAME           SALARY
---------- ----------
lizx            20000
yu              30000
hu              50000
++++++ 直接创建加密列的新表 ++++++ 
SQL> create table ht3 (id number ENCRYPT,name varchar2(10));                
Table created.                                                              
SQL> insert into ht3 values(10,'lizx');                                     
1 row created.                                                              
SQL> insert into ht3 values(100,'google');                                  
1 row created.                                                              
SQL> commit;                                                                
Commit complete.                                                            
SQL> alter system set encryption wallet close identified by "roger007~!@";  
System altered.                                                             
SQL> select * from ht3;                                                     
select * from ht3                                                           
*                                                             
ERROR at line 1:                                                            
ORA-28365: wallet is not open                                               
SQL> alter system set encryption wallet open identified by "roger007~!@";   
System altered.                                                             
SQL> select * from ht3;                                                     
ID NAME                                                             
---------- ----------                                                       
10 lizx                                                             
100 google

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

==== 进行加密列测试 ====

++++ 创建测试表 ++++

[ora11g@11gr2test ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 9 20:54:20 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> conn roger/roger

Connected.

SQL> create table ht1(name varchar2(10),salary number);

Table created.

SQL> insert into ht1 values('lizx','20000');

1 row created.

SQL> insert into ht1 values('yu','30000');

1 row created.

SQL> insert into ht1 values('hu','50000');

1 row created.

SQL> commit;

Commit complete.

++++++ modify column 进行列加密 ++++++

SQL> conn roger/roger

Connected.

SQL> desc ht1

Name Null? Type

----------------------------------------- -------- ----------------------------

NAME VARCHAR2(10)

SALARY NUMBER

SQL> alter table ht1 modify (SALARY number ENCRYPT NO SALT);

Table altered.

SQL> select * from ht1;

NAME SALARY

---------- ----------

lizx 20000

yu 30000

hu 50000

SQL> alter system set encryption wallet close identified by "roger007~!@";

System altered.

SQL> select * from roger.ht1;

select * from roger.ht1

*

ERROR at line 1:

ORA-28365: wallet is not open

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "roger007~!@";

System altered.

SQL> select * from roger.ht1;

NAME SALARY

---------- ----------

lizx 20000

yu 30000

hu 50000

++++++ 直接创建加密列的新表 ++++++

SQL> create table ht3 (id number ENCRYPT,name varchar2(10));

Table created.

SQL> insert into ht3 values(10,'lizx');

1 row created.

SQL> insert into ht3 values(100,'google');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system set encryption wallet close identified by "roger007~!@";

System altered.

SQL> select * from ht3;

select * from ht3

*

ERROR at line 1:

ORA-28365: wallet is not open

SQL> alter system set encryption wallet open identified by "roger007~!@";

System altered.

SQL> select * from ht3;

ID NAME

---------- ----------

10 lizx

100 google

<strong>表空间级别的加密</strong>
对于表空间加密，是11gR1 就引入，11gR2中支持的更为广泛，这里需要说明一下的是
表空间的加密只能是对于新创建的表空间而言，对于已经存在的表空间是无法进行的。
对于已经存在的表空间，如果要将其中的表迁移到加密表空间中，我们可以采取如下几种方式：
--> create table tab_name as select ... (CATS)
--> alter table move / alter index rebuild tablespace tbs;
--> exp/imp(expdp/impdp)
--> 在线重定义
我这里就挑最为简单的move操作进行测试：

1

2

3

4

5

6

7

8

9

10

11

12

13

<strong>表空间级别的加密</strong>

对于表空间加密，是11gR1 就引入，11gR2中支持的更为广泛，这里需要说明一下的是

表空间的加密只能是对于新创建的表空间而言，对于已经存在的表空间是无法进行的。

对于已经存在的表空间，如果要将其中的表迁移到加密表空间中，我们可以采取如下几种方式：

--> create table tab_name as select ... (CATS)

--> alter table move / alter index rebuild tablespace tbs;

--> exp/imp(expdp/impdp)

--> 在线重定义

我这里就挑最为简单的move操作进行测试：

SQL> conn /as sysdba
Connected.
SQL> create tablespace tbs_ht
2  datafile '+DATA1/roger/ht01.dbf'
3  size 50m
4  encryption
5  default storage(encrypt);
Tablespace created.
SQL> conn roger/roger
Connected.
SQL> desc ht2  
Name               Null?    Type
------------------ -------- --------------------------------------------
NAME                        VARCHAR2(10)
SALARY                      NUMBER
SQL> alter table ht2 move tablespace tbs_ht;
Table altered.
SQL> desc ht2
Name               Null?    Type
------------------ -------- --------------------------------------------
NAME                        VARCHAR2(10)
SALARY                      NUMBER
SQL> select * from DBA_ENCRYPTED_COLUMNS;                                                                              
OWNER     TABLE_NAME    COLUMN_NAME   ENCRYPTION_ALG                SAL INTEGRITY_AL
--------- ------------- ------------- ----------------------------- --- ------------
ROGER     HT1           SALARY        AES 192 bits key              NO  SHA-1       
ROGER     HT3           ID            AES 192 bits key              YES SHA-1

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

SQL> conn /as sysdba

Connected.

SQL> create tablespace tbs_ht

2 datafile '+DATA1/roger/ht01.dbf'

3 size 50m

4 encryption

5 default storage(encrypt);

Tablespace created.

SQL> conn roger/roger

Connected.

SQL> desc ht2

Name Null? Type

------------------ -------- --------------------------------------------

NAME VARCHAR2(10)

SALARY NUMBER

SQL> alter table ht2 move tablespace tbs_ht;

Table altered.

SQL> desc ht2

Name Null? Type

------------------ -------- --------------------------------------------

NAME VARCHAR2(10)

SALARY NUMBER

SQL> select * from DBA_ENCRYPTED_COLUMNS;

OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL

--------- ------------- ------------- ----------------------------- --- ------------

ROGER HT1 SALARY AES 192 bits key NO SHA-1

ROGER HT3 ID AES 192 bits key YES SHA-1

我们可以发现，对于普通表，如果直接move到加密表空间的话，那么其加密列都是允许salt的，
如果需要对加密列进行创建index，那么我们还得手工进行修改salt属性，否则会遇到如下错误。

1 2	我们可以发现，对于普通表，如果直接move到加密表空间的话，那么其加密列都是允许salt的，如果需要对加密列进行创建index，那么我们还得手工进行修改salt属性，否则会遇到如下错误。

SQL> create index id_ht3 on ht3(id) tablespace tbs_ht;   
create index id_ht3 on ht3(id) tablespace tbs_ht                   
*                                       
ERROR at line 1:                                                   
ORA-28338: Column(s) cannot be both indexed and encrypted with salt
====== 因为对于salt列是不允许创建index的 ======

1

2

3

4

5

6

7

SQL> create index id_ht3 on ht3(id) tablespace tbs_ht;

create index id_ht3 on ht3(id) tablespace tbs_ht

*

ERROR at line 1:

ORA-28338: Column(s) cannot be both indexed and encrypted with salt

====== 因为对于salt列是不允许创建index的 ======

另外，我们这里还需要注意一点的是，对于普通表move到加密表空间以后，
其信息在dba_encrypted_columns中是查不到的。

1 2	另外，我们这里还需要注意一点的是，对于普通表move到加密表空间以后，其信息在dba_encrypted_columns中是查不到的。

SQL> select * from DBA_ENCRYPTED_COLUMNS; 
OWNER      TABLE_NAME   COLUMN_NAME    ENCRYPTION_ALG                SAL INTEGRITY_AL
---------- ------------ -------------- ----------------------------- --- ------------
ROGER      HT1          SALARY         AES 192 bits key              NO  SHA-1       
ROGER      HT3          ID             AES 192 bits key              YES SHA-1       
SQL>  alter system set encryption wallet close identified by "roger007~!@";                                            
System altered.                                                                                                        
SQL>  select * from HT3;                                                                                               
select * from HT3                                                                                                     
*                                                                                                       
ERROR at line 1:                                                                                                       
ORA-28365: wallet is not open                                                                                          
SQL> select * from HT2;                                                                                                
select * from HT2                                                                                                      
*                                                                                                        
ERROR at line 1:                                                                                                       
ORA-28365: wallet is not open    
SQL> select owner,table_name,tablespace_name from dba_tables where owner='ROGER';
OWNER                TABLE_NAME           TABLESPACE_NAME                        
-------------------- -------------------- ------------------------------         
ROGER                HT2                  TBS_HT                                 
ROGER                HT3                  ROGER                                  
ROGER                HT1                  ROGER       
SQL> select * from V$ENCRYPTED_TABLESPACES;  
TS# ENCRYPT ENC ENCRYTPEDKEY                                                      MASTERKEYID                      BLOCKS_ENCRYPTED BLOCKS_DECRYPTED  
---------- ------- --- ----------------------------------------------------------------- -------------------------------  ---------------- ---------------- -
7 AES128  YES 3330DB1B5FD56074D67AF7CC9061690D00000000000000000000000000000000 2F209A3E6D4F4F54BF35998B84DFA174  2                1

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

SQL> select * from DBA_ENCRYPTED_COLUMNS;

OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL

---------- ------------ -------------- ----------------------------- --- ------------

ROGER HT1 SALARY AES 192 bits key NO SHA-1

ROGER HT3 ID AES 192 bits key YES SHA-1

SQL> alter system set encryption wallet close identified by "roger007~!@";

System altered.

SQL> select * from HT3;

select * from HT3

*

ERROR at line 1:

ORA-28365: wallet is not open

SQL> select * from HT2;

select * from HT2

*

ERROR at line 1:

ORA-28365: wallet is not open

SQL> select owner,table_name,tablespace_name from dba_tables where owner='ROGER';

OWNER TABLE_NAME TABLESPACE_NAME

-------------------- -------------------- ------------------------------

ROGER HT2 TBS_HT

ROGER HT3 ROGER

ROGER HT1 ROGER

SQL> select * from V$ENCRYPTED_TABLESPACES;

TS# ENCRYPT ENC ENCRYTPEDKEY MASTERKEYID BLOCKS_ENCRYPTED BLOCKS_DECRYPTED

---------- ------- --- ----------------------------------------------------------------- ------------------------------- ---------------- ---------------- -

7 AES128 YES 3330DB1B5FD56074D67AF7CC9061690D00000000000000000000000000000000 2F209A3E6D4F4F54BF35998B84DFA174 2 1

我们可以发现，对于表空间加密，其默认的算法跟是AES128，跟列加密是不同的。
对于表空间加密，我认为这点就非常好，非常的方便，最近要实施的一个项目就准备实施表空间加密。
通常的做法，我们是将普通表move到加密表空间中，当然需要注意一点的是，对于索引列，在rebuild index
完成以后，我们需要手工去修改下索引列的salt属性，如下过程：

1

2

3

4

5

6

我们可以发现，对于表空间加密，其默认的算法跟是AES128，跟列加密是不同的。

对于表空间加密，我认为这点就非常好，非常的方便，最近要实施的一个项目就准备实施表空间加密。

通常的做法，我们是将普通表move到加密表空间中，当然需要注意一点的是，对于索引列，在rebuild index

完成以后，我们需要手工去修改下索引列的salt属性，如下过程：

SQL> create table  ht4(id number,sex varchar2(6));
Table created.
SQL> insert into ht4 values(1,'nan');                                                                                   
1 row created.                                                                                                          
SQL> insert into ht4 values(2,'nv');                                                                                    
1 row created.                                                                                                          
SQL> commit;                                                                                                            
Commit complete.                                                                                                        
SQL> select owner,table_name,tablespace_name 
2  from dba_tables
3  where table_name='HT4';                                                                                            
OWNER                TABLE_NAME           TABLESPACE_NAME                                                               
-------------------- -------------------- ------------------------------                                                
ROGER                HT4                  ROGER                                                                         
SQL> desc ht4       
Name         Null?    Type                                        
------------ -------- --------------------------------------------
ID                    NUMBER                                      
SEX                   VARCHAR2(6)                                 
SQL> create index idx_id_ht4 on ht4(id);                                                                                
Index created.                                                                                                          
SQL> alter table ht4 move tablespace  tbs_ht;                                                                           
alter table ht4 move tablespace  tbs_ht                                                                                 
*                                                                                                           
ERROR at line 1:                                                                                                        
ORA-28365: wallet is not open    
SQL> alter system set encryption wallet open identified by "roger007~!@";
System altered.
SQL> alter table ht4 move tablespace  tbs_ht;
Table altered.
SQL> alter index IDX_ID_HT4 rebuild tablespace  tbs_ht;
Index altered.
SQL> select owner,table_name,index_name,tablespace_name
2  from dba_indexes 
3  where table_name='HT4';
OWNER                TABLE_NAME           INDEX_NAME                     TABLESPACE_NAME
-------------------- -------------------- ------------------------------ ------------------------------
ROGER                HT4                  IDX_ID_HT4                     TBS_HT
SQL> alter table ht4 modify (id encrypt no salt);
Table altered.
最后总结下表空间加密的限制，如下：
==> 加密表空间密钥不能重建
==> 不能使用no salt选项
==> bfile和外部表不能被加密
==> temp和undo表空间不能被加密

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

SQL> create table ht4(id number,sex varchar2(6));

Table created.

SQL> insert into ht4 values(1,'nan');

1 row created.

SQL> insert into ht4 values(2,'nv');

1 row created.

SQL> commit;

Commit complete.

SQL> select owner,table_name,tablespace_name

2 from dba_tables

3 where table_name='HT4';

OWNER TABLE_NAME TABLESPACE_NAME

-------------------- -------------------- ------------------------------

ROGER HT4 ROGER

SQL> desc ht4

Name Null? Type

------------ -------- --------------------------------------------

ID NUMBER

SEX VARCHAR2(6)

SQL> create index idx_id_ht4 on ht4(id);

Index created.

SQL> alter table ht4 move tablespace tbs_ht;

alter table ht4 move tablespace tbs_ht

*

ERROR at line 1:

ORA-28365: wallet is not open

SQL> alter system set encryption wallet open identified by "roger007~!@";

System altered.

SQL> alter table ht4 move tablespace tbs_ht;

Table altered.

SQL> alter index IDX_ID_HT4 rebuild tablespace tbs_ht;

Index altered.

SQL> select owner,table_name,index_name,tablespace_name

2 from dba_indexes

3 where table_name='HT4';

OWNER TABLE_NAME INDEX_NAME TABLESPACE_NAME

-------------------- -------------------- ------------------------------ ------------------------------

ROGER HT4 IDX_ID_HT4 TBS_HT

SQL> alter table ht4 modify (id encrypt no salt);

Table altered.

最后总结下表空间加密的限制，如下：

==> 加密表空间密钥不能重建

==> 不能使用no salt选项

==> bfile和外部表不能被加密

==> temp和undo表空间不能被加密

<strong>探秘oracle TDE 加密机制</strong>
下面我们来研究下 TDE 到底是如何实现的？是直接对数据block加密吗？

1

2

3

<strong>探秘oracle TDE 加密机制</strong>

下面我们来研究下 TDE 到底是如何实现的？是直接对数据block加密吗？

SQL> select dump(SALARY),name from ht1;
DUMP(SALARY)                             NAME
---------------------------------------- ----------
Typ=2 Len=2: 195,3                       lizx
Typ=2 Len=2: 195,4                       yu
Typ=2 Len=2: 195,6                       hu
SQL> create table ht2 as select * from ht1;
Table created.
SQL> select name,dump(SALARY) from ht2;
NAME       DUMP(SALARY)
---------- ----------------------------------------
lizx       Typ=2 Len=2: 195,3
yu         Typ=2 Len=2: 195,4
hu         Typ=2 Len=2: 195,6
====== 直接查询是完全一样的 ====== 
++++++ 查询block分布 ++++++ 
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,
2         dbms_rowid.rowid_block_number(rowid) blk#            
3  from ht1;
FILE#       BLK#
---------- ----------
5        132
5        132
5        132
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,
2         dbms_rowid.rowid_block_number(rowid) blk#  
3  from ht2;
FILE#       BLK#
---------- ----------
5        139
5        139
5        139
SQL> alter system dump datafile 5 block 132;
System altered.
SQL> alter system dump datafile 5 block 139;
System altered.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

SQL> select dump(SALARY),name from ht1;

DUMP(SALARY) NAME

---------------------------------------- ----------

Typ=2 Len=2: 195,3 lizx

Typ=2 Len=2: 195,4 yu

Typ=2 Len=2: 195,6 hu

SQL> create table ht2 as select * from ht1;

Table created.

SQL> select name,dump(SALARY) from ht2;

NAME DUMP(SALARY)

---------- ----------------------------------------

lizx Typ=2 Len=2: 195,3

yu Typ=2 Len=2: 195,4

hu Typ=2 Len=2: 195,6

====== 直接查询是完全一样的 ======

++++++ 查询block分布 ++++++

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,

2 dbms_rowid.rowid_block_number(rowid) blk#

3 from ht1;

FILE# BLK#

---------- ----------

5 132

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,

2 dbms_rowid.rowid_block_number(rowid) blk#

3 from ht2;

FILE# BLK#

---------- ----------

5 139

SQL> alter system dump datafile 5 block 132;

System altered.

SQL> alter system dump datafile 5 block 139;

System altered.

****** block dump ******
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01a.000002b0  0x00c014c4.008f.2e  --U-    3  fsc 0x0000.000cbcfd
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01400084
data_block_dump,data header at 0x14aa264
===============
tsiz: 0x1f98  ---->8088
hsiz: 0x18    ---->24
pbl: 0x014aa264
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f7b
avsp=0x1f63   ---->8035   #### block 中的可用空间大小 ####
tosp=0x1f63   ---->8035   #### 所有事务都commit以后, 该block中的可用空间大小 ####
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f8d
0x14:pri[1]     offs=0x1f84
0x16:pri[2]     offs=0x1f7b
block_row_dump:
tab 0, row 0, @0x1f8d
tl: 11 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 4]  6c 69 7a 78
col  1: [ 2]  c3 03
tab 0, row 1, @0x1f84
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  79 75
col  1: [ 2]  c3 04
tab 0, row 2, @0x1f7b
tl: 9 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  68 75
col  1: [ 2]  c3 06
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 132 maxblk 132
Block header dump:  0x0140008b
Object id on Block? Y
seg/obj: 0x122d1  csc: 0x00.d62fd  itc: 3  flg: E  typ: 1 - DATA
brn: 0  bdba: 0x1400088 ver: 0x01 opc: 0
inc: 0  exflg: 0
Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.000d62fd
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0140008b
data_block_dump,data header at 0x14aa27c
===============
tsiz: 0x1f80
hsiz: 0x18
pbl: 0x014aa27c
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f63
avsp=0x1f4b
tosp=0x1f4b
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x1f75
0x14:pri[1]     offs=0x1f6c
0x16:pri[2]     offs=0x1f63
block_row_dump:
tab 0, row 0, @0x1f75
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 4]  6c 69 7a 78
col  1: [ 2]  c3 03
tab 0, row 1, @0x1f6c
tl: 9 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  79 75
col  1: [ 2]  c3 04
tab 0, row 2, @0x1f63
tl: 9 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  68 75
col  1: [ 2]  c3 06
end_of_block_dump
End dump data blocks tsn: 6 file#: 5 minblk 139 maxblk 139

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

****** block dump ******

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0009.01a.000002b0 0x00c014c4.008f.2e --U- 3 fsc 0x0000.000cbcfd

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bdba: 0x01400084

data_block_dump,data header at 0x14aa264

===============

tsiz: 0x1f98 ---->8088

hsiz: 0x18 ---->24

pbl: 0x014aa264

76543210

flag=--------

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f7b

avsp=0x1f63 ---->8035 #### block 中的可用空间大小 ####

tosp=0x1f63 ---->8035 #### 所有事务都commit以后, 该block中的可用空间大小 ####

0xe:pti[0] nrow=3 offs=0

0x12:pri[0] offs=0x1f8d

0x14:pri[1] offs=0x1f84

0x16:pri[2] offs=0x1f7b

block_row_dump:

tab 0, row 0, @0x1f8d

tl: 11 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 4] 6c 69 7a 78

col 1: [ 2] c3 03

tab 0, row 1, @0x1f84

tl: 9 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] 79 75

col 1: [ 2] c3 04

tab 0, row 2, @0x1f7b

tl: 9 fb: --H-FL-- lb: 0x1 cc: 2

col 0: [ 2] 68 75

col 1: [ 2] c3 06

end_of_block_dump

End dump data blocks tsn: 6 file#: 5 minblk 132 maxblk 132

Block header dump: 0x0140008b

Object id on Block? Y

seg/obj: 0x122d1 csc: 0x00.d62fd itc: 3 flg: E typ: 1 - DATA

brn: 0 bdba: 0x1400088 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.000d62fd

0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

bdba: 0x0140008b

data_block_dump,data header at 0x14aa27c

===============

tsiz: 0x1f80

hsiz: 0x18

pbl: 0x014aa27c

76543210

flag=--------

ntab=1

nrow=3

frre=-1

fsbo=0x18

fseo=0x1f63

avsp=0x1f4b

tosp=0x1f4b

0xe:pti[0] nrow=3 offs=0

0x12:pri[0] offs=0x1f75

0x14:pri[1] offs=0x1f6c

0x16:pri[2] offs=0x1f63

block_row_dump:

tab 0, row 0, @0x1f75

tl: 11 fb: --H-FL-- lb: 0x0 cc: 2

col 0: [ 4] 6c 69 7a 78

col 1: [ 2] c3 03

tab 0, row 1, @0x1f6c

tl: 9 fb: --H-FL-- lb: 0x0 cc: 2

col 0: [ 2] 79 75

col 1: [ 2] c3 04

tab 0, row 2, @0x1f63

tl: 9 fb: --H-FL-- lb: 0x0 cc: 2

col 0: [ 2] 68 75

col 1: [ 2] c3 06

end_of_block_dump

End dump data blocks tsn: 6 file#: 5 minblk 139 maxblk 139

我们可以发现，block dump是有一些差异的，分析block ，我喜欢用bbed，如下：
首先, 把datafile从asm中复制到文件系统，通过rman进行

1

2

3

我们可以发现，block dump是有一些差异的，分析block ，我喜欢用bbed，如下：

首先, 把datafile从asm中复制到文件系统，通过rman进行

SQL> select file_id,file_name,bytes from dba_data_files order by 1;
FILE_ID FILE_NAME                                               BYTES
---------- -------------------------------------------------- ----------
1 +DATA1/roger/system01.dbf                           723517440
2 +DATA1/roger/sysaux01.dbf                           534773760
3 +DATA1/roger/undotbs01.dbf                           47185920
4 +DATA1/roger/users01.dbf                              5242880
5 +DATA1/roger/roger01.dbf                            209715200

1

2

3

4

5

6

7

8

9

SQL> select file_id,file_name,bytes from dba_data_files order by 1;

FILE_ID FILE_NAME BYTES

---------- -------------------------------------------------- ----------

1 +DATA1/roger/system01.dbf 723517440

2 +DATA1/roger/sysaux01.dbf 534773760

3 +DATA1/roger/undotbs01.dbf 47185920

4 +DATA1/roger/users01.dbf 5242880

5 +DATA1/roger/roger01.dbf 209715200

[ora11g@11gr2test ~]$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Oct 12 00:33:36 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ROGER (DBID=2468313792)
RMAN> copy datafile 5 to '/home/ora11g/cheshi_bbed/roger01.dbf';
Starting backup at 12-OCT-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA1/roger/roger01.dbf
output file name=/home/ora11g/cheshi_bbed/roger01.dbf tag=TAG20111012T003345 RECID=1 STAMP=764296437
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
Finished backup at 12-OCT-11

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

[ora11g@11gr2test ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Oct 12 00:33:36 2011

connected to target database: ROGER (DBID=2468313792)

RMAN> copy datafile 5 to '/home/ora11g/cheshi_bbed/roger01.dbf';

Starting backup at 12-OCT-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=43 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=+DATA1/roger/roger01.dbf

output file name=/home/ora11g/cheshi_bbed/roger01.dbf tag=TAG20111012T003345 RECID=1 STAMP=764296437

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16

Finished backup at 12-OCT-11

++++++ 配置bbed，如下：++++++ 
[ora11g@11gr2test cheshi_bbed]$ ls -ltr
total 205044
-rw-r--r-- 1 ora11g oinstall        54 Oct 12 00:34 par.txt
-rw-r--r-- 1 ora11g oinstall        76 Oct 12 00:35 a.txt
-rw-r----- 1 ora11g oinstall 209723392 Oct 12 00:40 roger01.dbf
-rw-r--r-- 1 ora11g oinstall      5824 Oct 12 00:40 log.bbd
[ora11g@11gr2test cheshi_bbed]$ cat a.txt
5 /home/ora11g/cheshi_bbed/roger01.dbf                            209715200
SQL> select 209723392 - 209715200 from dual;
209723392-209715200
-------------------
8192
++++++ 多余的一个block是 datafile header ++++++

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

++++++ 配置bbed，如下：++++++

[ora11g@11gr2test cheshi_bbed]$ ls -ltr

total 205044

-rw-r--r-- 1 ora11g oinstall 54 Oct 12 00:34 par.txt

-rw-r--r-- 1 ora11g oinstall 76 Oct 12 00:35 a.txt

-rw-r----- 1 ora11g oinstall 209723392 Oct 12 00:40 roger01.dbf

-rw-r--r-- 1 ora11g oinstall 5824 Oct 12 00:40 log.bbd

[ora11g@11gr2test cheshi_bbed]$ cat a.txt

5 /home/ora11g/cheshi_bbed/roger01.dbf 209715200

SQL> select 209723392 - 209715200 from dual;

209723392-209715200

-------------------

8192

++++++ 多余的一个block是 datafile header ++++++

下面用bbed分别来比较下这几个block的差异：
1. 加密列的block

1

2

3

下面用bbed分别来比较下这几个block的差异：

1. 加密列的block

BBED> set file 5 block 132
FILE#           5
BLOCK#          132
BBED> map /v
File: /home/ora11g/cheshi_bbed/roger01.dbf (5)
Block: 132                                   Dba:0x01400084
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes                      @0       
ub1 type_kcbh                           @0       
ub1 frmt_kcbh                           @1       
ub1 spare1_kcbh                         @2       
ub1 spare2_kcbh                         @3       
ub4 rdba_kcbh                           @4       
ub4 bas_kcbh                            @8       
ub2 wrp_kcbh                            @12      
ub1 seq_kcbh                            @14      
ub1 flg_kcbh                            @15      
ub2 chkval_kcbh                         @16      
ub2 spare3_kcbh                         @18      
struct ktbbh, 72 bytes                     @20  #### ktbbh占据72 byte #### 
ub1 ktbbhtyp                            @20      
union ktbbhsid, 4 bytes                 @24      
struct ktbbhcsc, 8 bytes                @28      
sb2 ktbbhict                            @36      
ub1 ktbbhflg                            @38      
ub1 ktbbhfsl                            @39      
ub4 ktbbhfnx                            @40      
struct ktbbhitl[2], 48 bytes            @44      
struct kdbh, 14 bytes                      @100   
ub1 kdbhflag                            @100     
sb1 kdbhntab                            @101     
sb2 kdbhnrow                            @102     
sb2 kdbhfrre                            @104     
sb2 kdbhfsbo                            @106     
sb2 kdbhfseo                            @108     
sb2 kdbhavsp                            @110     
sb2 kdbhtosp                            @112     
struct kdbt[1], 4 bytes                    @114     
sb2 kdbtoffs                            @114     
sb2 kdbtnrow                            @116     
sb2 kdbr[3]                                @118     
ub1 freespace[7904]                        @124     
ub1 rowdata[160]                           @8028    
ub4 tailchk                                @8188    
BBED> p ktbbh
struct ktbbh, 72 bytes                      @20      
ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes                  @24      
ub4 ktbbhsg1                          @24       0x000122c5
ub4 ktbbhod1                          @24       0x000122c5
struct ktbbhcsc, 8 bytes                 @28      
ub4 kscnbas                           @28       0x000d62cc
ub2 kscnwrp                           @32       0x0000
sb2 ktbbhict                             @36       2
ub1 ktbbhflg                             @38       0x32 (NONE)
ub1 ktbbhfsl                             @39       0x00
ub4 ktbbhfnx                             @40       0x01400080
struct ktbbhitl[0], 24 bytes             @44      
struct ktbitxid, 8 bytes              @44      
ub2 kxidusn                        @44       0x0009
ub2 kxidslt                        @46       0x001a
ub4 kxidsqn                        @48       0x000002b0
struct ktbituba, 8 bytes              @52      
ub4 kubadba                        @52       0x00c014c4
ub2 kubaseq                        @56       0x008f
ub1 kubarec                        @58       0x2e
ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
union _ktbitun, 2 bytes               @62      
sb2 _ktbitfsc                      @62       0
ub2 _ktbitwrp                      @62       0x0000
ub4 ktbitbas                          @64       0x000cbcfd
struct ktbbhitl[1], 24 bytes             @68      
struct ktbitxid, 8 bytes              @68      
ub2 kxidusn                        @68       0x0009
ub2 kxidslt                        @70       0x0013
ub4 kxidsqn                        @72       0x000002cb
struct ktbituba, 8 bytes              @76      
ub4 kubadba                        @76       0x00c014a2
ub2 kubaseq                        @80       0x00a2
ub1 kubarec                        @82       0x07
ub2 ktbitflg                          @84       0x2003 (KTBFUPB)
union _ktbitun, 2 bytes               @86      
sb2 _ktbitfsc                      @86       0
ub2 _ktbitwrp                      @86       0x0000
ub4 ktbitbas                          @88       0x000d62d1
-- 2个itl
BBED> p kdbr
sb2 kdbr[0]                                 @118      8014
sb2 kdbr[1]                                 @120      7971
sb2 kdbr[2]                                 @122      7928
BBED> p *kdbr[0]
rowdata[86]
-----------
ub1 rowdata[86]                             @8114     0x2c
BBED> x /1rnnnnnnnn
rowdata[86]                                 @8114    
-----------
flag@8114: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8115: 0x02
cols@8116:    2
col    0[4] @8117: ######################################### 
col   1[36] @8122:  0xc1  0xfa  0xcf  0x10  0xcb  0x61  0x10  0xc4  0x26  0x00 
0xec  0x18  0x6c  0x65  0x69  0x3b  0xf2  0xff  0x4f  0x60  0x1e  0xaf  0x16 
0xcd  0xb3  0x96  0xcc  0x2c  0xf4  0xbe  0x1a  0x81  0xfc  0xb0  0xce  0x96 
我们可用看到，对于加密block，该列数据显示的是这么多的十六进制，从上看应该是AES192算法。
上面的这一长串的16进制看不懂是如何弄出来的，总之，我们知道该值是oracle根据AES192算法，
集合我们的密钥进行计算出来的值。
#################################################################################

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

BBED> set file 5 block 132

FILE# 5

BLOCK# 132

BBED> map /v

File: /home/ora11g/cheshi_bbed/roger01.dbf (5)

Block: 132 Dba:0x01400084

------------------------------------------------------------

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

ub1 type_kcbh @0

ub1 frmt_kcbh @1

ub1 spare1_kcbh @2

ub1 spare2_kcbh @3

ub4 rdba_kcbh @4

ub4 bas_kcbh @8

ub2 wrp_kcbh @12

ub1 seq_kcbh @14

ub1 flg_kcbh @15

ub2 chkval_kcbh @16

ub2 spare3_kcbh @18

struct ktbbh, 72 bytes @20 #### ktbbh占据72 byte ####

ub1 ktbbhtyp @20

union ktbbhsid, 4 bytes @24

struct ktbbhcsc, 8 bytes @28

sb2 ktbbhict @36

ub1 ktbbhflg @38

ub1 ktbbhfsl @39

ub4 ktbbhfnx @40

struct ktbbhitl[2], 48 bytes @44

struct kdbh, 14 bytes @100

ub1 kdbhflag @100

sb1 kdbhntab @101

sb2 kdbhnrow @102

sb2 kdbhfrre @104

sb2 kdbhfsbo @106

sb2 kdbhfseo @108

sb2 kdbhavsp @110

sb2 kdbhtosp @112

struct kdbt[1], 4 bytes @114

sb2 kdbtoffs @114

sb2 kdbtnrow @116

sb2 kdbr[3] @118

ub1 freespace[7904] @124

ub1 rowdata[160] @8028

ub4 tailchk @8188

BBED> p ktbbh

struct ktbbh, 72 bytes @20

ub1 ktbbhtyp @20 0x01 (KDDBTDATA)

union ktbbhsid, 4 bytes @24

ub4 ktbbhsg1 @24 0x000122c5

ub4 ktbbhod1 @24 0x000122c5

struct ktbbhcsc, 8 bytes @28

ub4 kscnbas @28 0x000d62cc

ub2 kscnwrp @32 0x0000

sb2 ktbbhict @36 2

ub1 ktbbhflg @38 0x32 (NONE)

ub1 ktbbhfsl @39 0x00

ub4 ktbbhfnx @40 0x01400080

struct ktbbhitl[0], 24 bytes @44

struct ktbitxid, 8 bytes @44

ub2 kxidusn @44 0x0009

ub2 kxidslt @46 0x001a

ub4 kxidsqn @48 0x000002b0

struct ktbituba, 8 bytes @52

ub4 kubadba @52 0x00c014c4

ub2 kubaseq @56 0x008f

ub1 kubarec @58 0x2e

ub2 ktbitflg @60 0x8000 (KTBFCOM)

union _ktbitun, 2 bytes @62

sb2 _ktbitfsc @62 0

ub2 _ktbitwrp @62 0x0000

ub4 ktbitbas @64 0x000cbcfd

struct ktbbhitl[1], 24 bytes @68

struct ktbitxid, 8 bytes @68

ub2 kxidusn @68 0x0009

ub2 kxidslt @70 0x0013

ub4 kxidsqn @72 0x000002cb

struct ktbituba, 8 bytes @76

ub4 kubadba @76 0x00c014a2

ub2 kubaseq @80 0x00a2

ub1 kubarec @82 0x07

ub2 ktbitflg @84 0x2003 (KTBFUPB)

union _ktbitun, 2 bytes @86

sb2 _ktbitfsc @86 0

ub2 _ktbitwrp @86 0x0000

ub4 ktbitbas @88 0x000d62d1

-- 2个itl

BBED> p kdbr

sb2 kdbr[0] @118 8014

sb2 kdbr[1] @120 7971

sb2 kdbr[2] @122 7928

BBED> p *kdbr[0]

rowdata[86]

-----------

ub1 rowdata[86] @8114 0x2c

BBED> x /1rnnnnnnnn

rowdata[86] @8114

-----------

flag@8114: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8115: 0x02

cols@8116: 2

col 0[4] @8117: #########################################

col 1[36] @8122: 0xc1 0xfa 0xcf 0x10 0xcb 0x61 0x10 0xc4 0x26 0x00

0xec 0x18 0x6c 0x65 0x69 0x3b 0xf2 0xff 0x4f 0x60 0x1e 0xaf 0x16

0xcd 0xb3 0x96 0xcc 0x2c 0xf4 0xbe 0x1a 0x81 0xfc 0xb0 0xce 0x96

我们可用看到，对于加密block，该列数据显示的是这么多的十六进制，从上看应该是AES192算法。

上面的这一长串的16进制看不懂是如何弄出来的，总之，我们知道该值是oracle根据AES192算法，

集合我们的密钥进行计算出来的值。

#################################################################################

2. 普通的block

1	2. 普通的block

BBED> set file 5 block 139
FILE#           5
BLOCK#          139
BBED> map /v
File: /home/ora11g/cheshi_bbed/roger01.dbf (5)
Block: 139                                   Dba:0x0140008b
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes                      @0       
ub1 type_kcbh                           @0       
ub1 frmt_kcbh                           @1       
ub1 spare1_kcbh                         @2       
ub1 spare2_kcbh                         @3       
ub4 rdba_kcbh                           @4       
ub4 bas_kcbh                            @8       
ub2 wrp_kcbh                            @12      
ub1 seq_kcbh                            @14      
ub1 flg_kcbh                            @15      
ub2 chkval_kcbh                         @16      
ub2 spare3_kcbh                         @18      
struct ktbbh, 96 bytes                     @20  #### ktbbh占据96 byte #### 
ub1 ktbbhtyp                            @20      
union ktbbhsid, 4 bytes                 @24      
struct ktbbhcsc, 8 bytes                @28      
sb2 ktbbhict                            @36      
ub1 ktbbhflg                            @38      
ub1 ktbbhfsl                            @39      
ub4 ktbbhfnx                            @40      
struct ktbbhitl[3], 72 bytes            @44      
struct kdbh, 14 bytes                      @124     
ub1 kdbhflag                            @124     
sb1 kdbhntab                            @125     
sb2 kdbhnrow                            @126     
sb2 kdbhfrre                            @128     
sb2 kdbhfsbo                            @130     
sb2 kdbhfseo                            @132     
sb2 kdbhavsp                            @134     
sb2 kdbhtosp                            @136     
struct kdbt[1], 4 bytes                    @138     
sb2 kdbtoffs                            @138     
sb2 kdbtnrow                            @140     
sb2 kdbr[3]                                @142     
ub1 freespace[8011]                        @148     
ub1 rowdata[29]                            @8159    
ub4 tailchk                                @8188    
BBED> p ktbbh
struct ktbbh, 96 bytes                      @20      
ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
union ktbbhsid, 4 bytes                  @24      
ub4 ktbbhsg1                          @24       0x000122d1
ub4 ktbbhod1                          @24       0x000122d1
struct ktbbhcsc, 8 bytes                 @28      
ub4 kscnbas                           @28       0x000d62fd
ub2 kscnwrp                           @32       0x0000
sb2 ktbbhict                             @36       3
ub1 ktbbhflg                             @38       0x32 (NONE)
ub1 ktbbhfsl                             @39       0x00
ub4 ktbbhfnx                             @40       0x01400088
struct ktbbhitl[0], 24 bytes             @44      
struct ktbitxid, 8 bytes              @44      
ub2 kxidusn                        @44       0xffff
ub2 kxidslt                        @46       0x0000
ub4 kxidsqn                        @48       0x00000000
struct ktbituba, 8 bytes              @52      
ub4 kubadba                        @52       0x00000000
ub2 kubaseq                        @56       0x0000
ub1 kubarec                        @58       0x00
ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
union _ktbitun, 2 bytes               @62      
sb2 _ktbitfsc                      @62       0
ub2 _ktbitwrp                      @62       0x0000
ub4 ktbitbas                          @64       0x000d62fd
struct ktbbhitl[1], 24 bytes             @68      
struct ktbitxid, 8 bytes              @68      
ub2 kxidusn                        @68       0x0000
ub2 kxidslt                        @70       0x0000
ub4 kxidsqn                        @72       0x00000000
struct ktbituba, 8 bytes              @76      
ub4 kubadba                        @76       0x00000000
ub2 kubaseq                        @80       0x0000
ub1 kubarec                        @82       0x00
ub2 ktbitflg                          @84       0x0000 (NONE)
union _ktbitun, 2 bytes               @86      
sb2 _ktbitfsc                      @86       0
ub2 _ktbitwrp                      @86       0x0000
ub4 ktbitbas                          @88       0x00000000
struct ktbbhitl[2], 24 bytes             @92      
struct ktbitxid, 8 bytes              @92      
ub2 kxidusn                        @92       0x0000
ub2 kxidslt                        @94       0x0000
ub4 kxidsqn                        @96       0x00000000
struct ktbituba, 8 bytes              @100     
ub4 kubadba                        @100      0x00000000
ub2 kubaseq                        @104      0x0000
ub1 kubarec                        @106      0x00
ub2 ktbitflg                          @108      0x0000 (NONE)
union _ktbitun, 2 bytes               @110     
sb2 _ktbitfsc                      @110      0
ub2 _ktbitwrp                      @110      0x0000
ub4 ktbitbas                          @112      0x00000000
-- 3个itl（每个itl 占据24个byte，所以加密block相对正常block这里就少了24个byte）
BBED> p kdbr
sb2 kdbr[0]                                 @142      8053
sb2 kdbr[1]                                 @144      8044
sb2 kdbr[2]                                 @146      8035
BBED> p *kdbr[0]  
rowdata[18]
-----------
ub1 rowdata[18]                             @8177     0x2c
BBED> x /1rnnnnnnnnnnn
rowdata[18]                                 @8177    
-----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179:    2
col    0[4] @8180: ######################################### 
col    1[2] @8185: 20000  --正常block这里显示的是明文20000的数值。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

BBED> set file 5 block 139

FILE# 5

BLOCK# 139

BBED> map /v

File: /home/ora11g/cheshi_bbed/roger01.dbf (5)

Block: 139 Dba:0x0140008b

------------------------------------------------------------

KTB Data Block (Table/Cluster)

struct kcbh, 20 bytes @0

ub1 type_kcbh @0

ub1 frmt_kcbh @1

ub1 spare1_kcbh @2

ub1 spare2_kcbh @3

ub4 rdba_kcbh @4

ub4 bas_kcbh @8

ub2 wrp_kcbh @12

ub1 seq_kcbh @14

ub1 flg_kcbh @15

ub2 chkval_kcbh @16

ub2 spare3_kcbh @18

struct ktbbh, 96 bytes @20 #### ktbbh占据96 byte ####

ub1 ktbbhtyp @20

union ktbbhsid, 4 bytes @24

struct ktbbhcsc, 8 bytes @28

sb2 ktbbhict @36

ub1 ktbbhflg @38

ub1 ktbbhfsl @39

ub4 ktbbhfnx @40

struct ktbbhitl[3], 72 bytes @44

struct kdbh, 14 bytes @124

ub1 kdbhflag @124

sb1 kdbhntab @125

sb2 kdbhnrow @126

sb2 kdbhfrre @128

sb2 kdbhfsbo @130

sb2 kdbhfseo @132

sb2 kdbhavsp @134

sb2 kdbhtosp @136

struct kdbt[1], 4 bytes @138

sb2 kdbtoffs @138

sb2 kdbtnrow @140

sb2 kdbr[3] @142

ub1 freespace[8011] @148

ub1 rowdata[29] @8159

ub4 tailchk @8188

BBED> p ktbbh

struct ktbbh, 96 bytes @20

ub1 ktbbhtyp @20 0x01 (KDDBTDATA)

union ktbbhsid, 4 bytes @24

ub4 ktbbhsg1 @24 0x000122d1

ub4 ktbbhod1 @24 0x000122d1

struct ktbbhcsc, 8 bytes @28

ub4 kscnbas @28 0x000d62fd

ub2 kscnwrp @32 0x0000

sb2 ktbbhict @36 3

ub1 ktbbhflg @38 0x32 (NONE)

ub1 ktbbhfsl @39 0x00

ub4 ktbbhfnx @40 0x01400088

struct ktbbhitl[0], 24 bytes @44

struct ktbitxid, 8 bytes @44

ub2 kxidusn @44 0xffff

ub2 kxidslt @46 0x0000

ub4 kxidsqn @48 0x00000000

struct ktbituba, 8 bytes @52

ub4 kubadba @52 0x00000000

ub2 kubaseq @56 0x0000

ub1 kubarec @58 0x00

ub2 ktbitflg @60 0x8000 (KTBFCOM)

union _ktbitun, 2 bytes @62

sb2 _ktbitfsc @62 0

ub2 _ktbitwrp @62 0x0000

ub4 ktbitbas @64 0x000d62fd

struct ktbbhitl[1], 24 bytes @68

struct ktbitxid, 8 bytes @68

ub2 kxidusn @68 0x0000

ub2 kxidslt @70 0x0000

ub4 kxidsqn @72 0x00000000

struct ktbituba, 8 bytes @76

ub4 kubadba @76 0x00000000

ub2 kubaseq @80 0x0000

ub1 kubarec @82 0x00

ub2 ktbitflg @84 0x0000 (NONE)

union _ktbitun, 2 bytes @86

sb2 _ktbitfsc @86 0

ub2 _ktbitwrp @86 0x0000

ub4 ktbitbas @88 0x00000000

struct ktbbhitl[2], 24 bytes @92

struct ktbitxid, 8 bytes @92

ub2 kxidusn @92 0x0000

ub2 kxidslt @94 0x0000

ub4 kxidsqn @96 0x00000000

struct ktbituba, 8 bytes @100

ub4 kubadba @100 0x00000000

ub2 kubaseq @104 0x0000

ub1 kubarec @106 0x00

ub2 ktbitflg @108 0x0000 (NONE)

union _ktbitun, 2 bytes @110

sb2 _ktbitfsc @110 0

ub2 _ktbitwrp @110 0x0000

ub4 ktbitbas @112 0x00000000

-- 3个itl（每个itl 占据24个byte，所以加密block相对正常block这里就少了24个byte）

BBED> p kdbr

sb2 kdbr[0] @142 8053

sb2 kdbr[1] @144 8044

sb2 kdbr[2] @146 8035

BBED> p *kdbr[0]

rowdata[18]

-----------

ub1 rowdata[18] @8177 0x2c

BBED> x /1rnnnnnnnnnnn

rowdata[18] @8177

-----------

flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@8178: 0x00

cols@8179: 2

col 0[4] @8180: #########################################

col 1[2] @8185: 20000 --正常block这里显示的是明文20000的数值。

从上面的分析可用看出，TDE其实是加密的数据block，换句话说，如果我不给你密钥，
那么即使数据文件给你，你也看不到具体的数据。
最后，我们需要来补充下oracle引入的几个相关的试图：

1

2

3

4

从上面的分析可用看出，TDE其实是加密的数据block，换句话说，如果我不给你密钥，

那么即使数据文件给你，你也看不到具体的数据。

最后，我们需要来补充下oracle引入的几个相关的试图：

++++++ 查询walet的状态以及路径 ++++++ 
SQL> select * from V$ENCRYPTION_WALLET;
WRL_TYPE             WRL_PARAMETER                            STATUS
-------------------- ---------------------------------------- ------------------
file                 /home/ora11g/admin/roger/wallet          OPEN
++++++ 查看加密的表列 ++++++ 
SQL> select * from DBA_ENCRYPTED_COLUMNS;
OWNER      TABLE_NAME           COLUMN_NAME    ENCRYPTION_ALG      SAL INTEGRITY_AL
---------- -------------------- -------------- ------------------- --- ------------
ROGER      HT1                  SALARY         AES 192 bits key    NO  SHA-1

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

++++++ 查询walet的状态以及路径 ++++++

SQL> select * from V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS

-------------------- ---------------------------------------- ------------------

file /home/ora11g/admin/roger/wallet OPEN

++++++ 查看加密的表列 ++++++

SQL> select * from DBA_ENCRYPTED_COLUMNS;

OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL

---------- -------------------- -------------- ------------------- --- ------------

ROGER HT1 SALARY AES 192 bits key NO SHA-1

从上面我们可用看到，加密的列为 SALARY，其中使用的加密算法是AES192，
这也印证了前面我们bbed的分析是的对的。

1 2	从上面我们可用看到，加密的列为 SALARY，其中使用的加密算法是AES192，这也印证了前面我们bbed的分析是的对的。

Categories

Archives

最新评论

国内圈子

oracle security

oracle TDE学习系列 (2) — 探秘列、表空间加密

One Response to “oracle TDE学习系列 (2) — 探秘列、表空间加密”

Leave a Reply