oracle TDE学习系列 (3) --- 如何备份？

对于oracle TDE 数据库的备份，这是TDE学习系列的第3篇，前两篇请参看如下链接：

oracle TDE学习系列(1) --- wallet 使用管理
oracle TDE学习系列(2) --- 探秘列、表空间加密

对于备份，我们所熟知的都传统的逻辑导出备份和rman物理备份，这里我主要讲解如下
两种备份在TDE场景中的应用。
复制

++++++ exp/imp & expdp/impdp ++++++

[ora10g@killdb ~]$ exp system/oracle file=ht01.dmp tables=roger.ht01 log=exp.log

Export: Release 10.2.0.5.0 - Production on Wed Oct 12 20:19:07 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to ROGER
EXP-00107: Feature (COLUMN ENCRYPTION) of column ID in table ROGER.HT01 is not supported. The table will not be exported.
Export terminated successfully with warnings.

++++++ 对于加密列，传统的exp是无法导出数据的，而使用expdp是可以进行导出的，如下：++++++

[ora10g@killdb log]$ expdp roger/roger directory=DATA_PUMP_DIR tables=ht01 dumpfile=ht01.dmp logfile=ht01.log

Export: Release 10.2.0.5.0 - Production on Wednesday, 12 October, 2011 20:38:43
Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ROGER"."SYS_EXPORT_TABLE_01":  roger/******** directory=DATA_PUMP_DIR tables=ht01 dumpfile=ht01.dmp logfile=ht01.log 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "ROGER"."HT01"                              5.218 KB       1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "ROGER"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ROGER.SYS_EXPORT_TABLE_01 is:
  /home/ora10g/product/10.2/rdbms/log/ht01.dmp
Job "ROGER"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 20:38:48
复制

ORA-39173: Encrypted data has been stored unencrypted in dump file set 从这个错误信息来看，
我们可以知道，对于加密的数据，使用expdp进行数据导出会是明文的，所以为了保证数据的安全性，
建议在进行expdp导出时，加上ENCRYPTION_PASSWORD 指定一个密码。

需要注意的是，这个ENCRYPTION_PASSWORD密码不是wallet的密码，也不是master key，仅仅是针对这个dmp文件而言。
复制

++++++ 使用rman进行备份 ++++++

====== Rman的备份加密，有3种加密算法，如下查询：====== 

SQL> select ALGORITHM_ID,ALGORITHM_NAME from V$RMAN_ENCRYPTION_ALGORITHMS;

ALGORITHM_ID ALGORITHM_NAME
------------ ----------------------------------------------------------------
           1 AES128
           2 AES192
           3 AES256

SQL> show parameter comp

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction              string      ADAPTIVE
compatible                           string      11.2.0.0.0
nls_comp                             string      BINARY
plsql_v2_compatibility               boolean     FALSE

++++++ 需要注意的是compatible参数必须设置为10.2.0+版本，我这里是11.2.0.0. ++++++ 
++++++ rman的备份加密默认是关闭状态，如下：++++++ 

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ROGER are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '{39ecd679003247f2ed728ad9c7ed019a369dd84d0731b449c26bf628d3c1a20b}F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/home/ora11g/product/11.2/db/dbs/snapcf_roger.f'; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF;   #### 默认处于关闭状态 #### 
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default   #### 默认的加密算法 #### 
复制

另外说明一点是，不同的加密算法，可能备份所花费的时间不同。
另外 AES256 比 AES128 需要更多的空间。
首先需要开启rman备份加密功能，另外rman的备份加密有如下3种默认：

--> 透明加密（即仅使用oracle wallet）
--> 密码加密模式（通过对备份集或备份片设置密码）
--> 双重模式（即为透明模式+密码模式）

简单描述一下如上几个模式的应用场景：

1. 透明模式，通常仅用于本地模式，因为你wallet创建在本地，如果你用加密的rman备份
   集进行异地恢复等等，那么是不行的。
2. 密码模式，适用于异机恢复场景，因为其仅仅是对备份集设置一个密码而已，在进行
   恢复时，指定密码即可，不需要wallet的作用。
3. 双重模式，就不多说了，即是上面两种的组合。

我这里就拿双重模式进行备份恢复的测试：
复制

++++++ 首先确认wallet是否打开 ++++++ 

SQL> select * from V$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                                 STATUS
-------------------- --------------------------------------------- ------------------
file                 /home/ora11g/admin/roger/wallet               OPEN


++++++ 确认rman备份加密功能是否打开 ++++++ 

RMAN> show all;
... ...
... ...
... ... 省略部分内容
CONFIGURE ENCRYPTION FOR DATABASE ON;

++++++ 创建一个较大的测试表 ++++++ 

SQL> conn roger/roger
Connected.

SQL> create table killdb 
  2    as select owner,object_id,object_name,object_type
  3       from sys.dba_objects;

Table created.

SQL> set timing on
SQL> alter table killdb modify (object_name ENCRYPT);

Table altered.
Elapsed: 00:00:07.96

SQL> alter table killdb modify (object_id ENCRYPT NO SALT);

Table altered.
Elapsed: 00:00:08.37

SQL> begin
  2    for i in 1 .. 10000 loop
  3      insert /*+ append */
  4      into killdb
  5        select * from killdb;
  6      commit;
  7    end loop;
  8  end;
  9  /

begin
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
ORA-06512: at line 3

Elapsed: 00:02:45.54

SQL> select bytes/1024/1024 from sys.dba_segments where segment_name='KILLDB';

BYTES/1024/1024
---------------
            480

SQL> select count(*) from killdb;   

  COUNT(*)
----------
   2316416

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

OWNER          TABLE_NAME           TABLESPACE_NAME
-------------- -------------------- -----------------
ROGER          KILLDB               ROGER
复制

++++++ 进行表空间级别备份 ++++++ 

RMAN> set encryption on identified by hthorizon;

executing command: SET encryption

RMAN> backup tablespace roger format '/home/ora11g/backup/roger_bak.bak';

Starting backup at 13-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA1/roger/roger01.dbf
channel ORA_DISK_1: starting piece 1 at 13-OCT-11
channel ORA_DISK_1: finished piece 1 at 13-OCT-11
piece handle=/home/ora11g/backup/roger_bak.bak tag=TAG20111013T010550 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 13-OCT-11

[ora11g@11gr2test backup]$ ls -ltr

total 493276
-rw-r----- 1 ora11g oinstall 504610816 Oct 13 01:06 roger_bak.bak
复制

++++++ immediate offline tablespace ++++++ 

SQL> conn /as sysdba
Connected.

SQL> alter tablespace roger offline immediate;

Tablespace altered.
Elapsed: 00:00:01.65

SQL> alter tablespace roger online;

alter tablespace roger online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '+DATA1/roger/roger01.dbf'

Elapsed: 00:00:00.67
复制

++++++ 进行tablespace的restore和recover ++++++ 

RMAN> set encryption on identified by hthorizon;

executing command: SET encryption

RMAN> restore tablespace roger;

Starting restore at 13-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to +DATA1/roger/roger01.dbf
channel ORA_DISK_1: reading from backup piece /home/ora11g/backup/roger_bak.bak
channel ORA_DISK_1: piece handle=/home/ora11g/backup/roger_bak.bak tag=TAG20111013T010550
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 13-OCT-11

RMAN> recover tablespace roger;

Starting recover at 13-OCT-11
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 13-OCT-11
复制

++++++ online tablespace ++++++ 

SQL> conn /as sysdba
Connected.

SQL> alter tablespace roger online;

Tablespace altered.
Elapsed: 00:00:00.43

SQL> select count(*) from roger.killdb;

  COUNT(*)
----------
   2316416

Elapsed: 00:00:06.63
复制

使用双重模式，需要注意一点的是，在进行restore时，必须打开wallet。

注意我这里仅仅是测试了表空间的备份，对于全库备份，也是一样的。
    
复制