oracle TDE学习系列 (3) — 如何备份？
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: oracle TDE学习系列 (3) — 如何备份？
|
1 2 3 4 5 6 7 |
对于oracle TDE 数据库的备份，这是TDE学习系列的第3篇，前两篇请参看如下链接： <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> <a href="http://www.killdb.com/2011/10/13/tde%E5%AD%A6%E4%B9%A0-%EF%BC%882%EF%BC%89-%E6%8E%A2%E7%A7%98%E5%88%97%E3%80%81%E8%A1%A8%E7%A9%BA%E9%97%B4%E5%8A%A0%E5%AF%86.html" style="font-size: 12px; text-decoration: underline; color: #0000ff; font-family: monospace;">oracle TDE学习系列(2) --- 探秘列、表空间加密</a> 对于备份，我们所熟知的都传统的逻辑导出备份和rman物理备份，这里我主要讲解如下 两种备份在TDE场景中的应用。 |
|
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 |
++++++ 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 |
|
1 2 3 4 5 |
ORA-39173: Encrypted data has been stored unencrypted in dump file set 从这个错误信息来看， 我们可以知道，对于加密的数据，使用expdp进行数据导出会是明文的，所以为了保证数据的安全性， 建议在进行expdp导出时，加上ENCRYPTION_PASSWORD 指定一个密码。 需要注意的是，这个ENCRYPTION_PASSWORD密码不是wallet的密码，也不是master key，仅仅是针对这个dmp文件而言。 |
|
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 |
++++++ 使用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 '%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 #### 默认的加密算法 #### |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
另外说明一点是，不同的加密算法，可能备份所花费的时间不同。 另外 AES256 比 AES128 需要更多的空间。 首先需要开启rman备份加密功能，另外rman的备份加密有如下3种默认： --> 透明加密（即仅使用oracle wallet） --> 密码加密模式（通过对备份集或备份片设置密码） --> 双重模式（即为透明模式+密码模式） 简单描述一下如上几个模式的应用场景： 1. 透明模式，通常仅用于本地模式，因为你wallet创建在本地，如果你用加密的rman备份 集进行异地恢复等等，那么是不行的。 2. 密码模式，适用于异机恢复场景，因为其仅仅是对备份集设置一个密码而已，在进行 恢复时，指定密码即可，不需要wallet的作用。 3. 双重模式，就不多说了，即是上面两种的组合。 我这里就拿双重模式进行备份恢复的测试： |
|
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 |
++++++ 首先确认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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
++++++ 进行表空间级别备份 ++++++ 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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
++++++ 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 |
|
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 |
++++++ 进行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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
++++++ 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 |
|
1 2 3 |
使用双重模式，需要注意一点的是，在进行restore时，必须打开wallet。 注意我这里仅仅是测试了表空间的备份，对于全库备份，也是一样的。 |
Leave a Reply
You must be logged in to post a comment.