Oracle 通過odu驗證rman backup對於truncate對象備份處理

通過odu挖rman備份前和備份後的資料檔案,得知rman backup備份的過程,對絕大多數truncate的表的未經處理資料未正常備份(為什麼是絕大多數,我無法給出解釋),這裡也可以看出rman backup並非是真正意義上的完全物理上複製(和rman copy還是有區別,copy不能完全被取代)

建立類比環境

代碼如下

複製代碼

SQL> select * from v$version;    BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod PL/SQL Release 10.2.0.4.0 - Production CORE    10.2.0.4.0      Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production   SQL> create tablespace xifenfei datafile '/u01/oracle/oradata/XFF/xifenfei01.dbf'    2 size 10m autoextend on maxsize 10g;   Tablespace created.   SQL> conn chf/xifenfei Connected.   SQL> create table t_xifenfei tablespace xifenfei   2  as   3  select * from dba_objects;   Table created.   SQL> insert into t_xifenfei   2  select * from dba_objects;   50055 rows created.   SQL> commit;   Commit complete.   SQL> select BYTES from dba_free_space where TABLESPACE_NAME='XIFENFEI';        BYTES ----------     983040   SQL> select BYTES from v$datafile where name='/u01/oracle/oradata/XFF/xifenfei01.dbf';        BYTES ----------   12582912   SQL> select 12582912-983040 from dual;   12582912-983040 ---------------        11599872   SQL> select object_id,data_object_id from dba_objects where object_name='T_XIFENFEI';    OBJECT_ID DATA_OBJECT_ID ---------- --------------      51833          51833   --這裡我們得到資訊有: --1.dataobj#=51833 --2.使用資料檔案空間為:11599872

rman備份no truncate table 資料檔案

代碼如下

複製代碼

[oracle@xifenfei ~]$ rman target /   Recovery Manager: Release 10.2.0.4.0 - Production on Thu Dec 15 06:00:05 2011   Copyright (c) 1982, 2007, Oracle.  All rights reserved.   connected to target database: XFF (DBID=3440302261)   RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/no_truncate_xifenfei';   Starting backup at 15-DEC-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=158 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00005 name=/u01/oracle/oradata/XFF/xifenfei01.dbf channel ORA_DISK_1: starting piece 1 at 15-DEC-11 channel ORA_DISK_1: finished piece 1 at 15-DEC-11 piece handle=/u01/oracle/oradata/tmp/no_truncate_xifenfei tag=TAG20111215T060343 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 15-DEC-11

truncate table 操作

代碼如下	複製代碼
[oracle@xifenfei ~]$ sqlplus / as sysdba   SQL*Plus: Release 10.2.0.4.0 - Production on Thu Dec 15 06:03:58 2011   Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.     Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> truncate table chf.t_xifenfei;   Table truncated.

rman備份truncate table 資料檔案

代碼如下

複製代碼

RMAN> backup tablespace xifenfei format '/u01/oracle/oradata/tmp/truncate_xifenfei';   Starting backup at 15-DEC-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=140 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00005 name=/u01/oracle/oradata/XFF/xifenfei01.dbf channel ORA_DISK_1: starting piece 1 at 15-DEC-11 channel ORA_DISK_1: finished piece 1 at 15-DEC-11 piece handle=/u01/oracle/oradata/tmp/truncate_xifenfei tag=TAG20111215T060445 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 15-DEC-11   [root@xifenfei ~]# ls -l /u01/oracle/oradata/tmp/*_xifenfei -rw-r----- 1 oracle oinstall 11640832 Dec 15 06:03 /u01/oracle/oradata/tmp/no_truncate_xifenfei -rw-r----- 1 oracle oinstall   630784 Dec 15 06:04 /u01/oracle/oradata/tmp/truncate_xifenfei

odu挖rman備份前資料檔案

代碼如下

複製代碼

ODU> unload dict CLUSTER C_USER# file_no: 1 block_no: 89 TABLE OBJ$ file_no: 1 block_no: 121 CLUSTER C_OBJ# file_no: 1 block_no: 25 CLUSTER C_OBJ# file_no: 1 block_no: 25 found IND$'s obj# 19 found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3 found TABPART$'s obj# 266 found TABPART$'s dataobj#:266,ts#:0,file#:1,block#:2121,tab#:0 found INDPART$'s obj# 271 found INDPART$'s dataobj#:271,ts#:0,file#:1,block#:2161,tab#:0 found TABSUBPART$'s obj# 278 found TABSUBPART$'s dataobj#:278,ts#:0,file#:1,block#:2217,tab#:0 found INDSUBPART$'s obj# 283 found INDSUBPART$'s dataobj#:283,ts#:0,file#:1,block#:2257,tab#:0 found IND$'s obj# 19 found IND$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:3 found LOB$'s obj# 151 found LOB$'s dataobj#:2,ts#:0,file#:1,block#:25,tab#:6 found LOBFRAG$'s obj# 299 found LOBFRAG$'s dataobj#:299,ts#:0,file#:1,block#:2393,tab#:0   ODU> scan extent tablespace 6   scan extent start: 2011-12-15 06:12:28 scanning extent... scanning extent finished. scan extent completed: 2011-12-15 06:12:28   ODU> unload table chf.t_xifenfei object 51833    Unloading table: T_XIFENFEI,object ID: 51833 Unloading segment,storage(Obj#=51833 DataObj#=51833 TS#=6 File#=5 Block#=11 Cluster=0) 100110 rows unloaded --這裡可以看到odu全部找到被truncate掉的記錄條數

使用rman 備份後資料檔案

代碼如下

複製代碼

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@xifenfei odu]$ rm /u01/oracle/oradata/XFF/xifenfei01.dbf [oracle@xifenfei odu]$ rman target /   Recovery Manager: Release 10.2.0.4.0 - Production on Thu Dec 15 06:14:00 2011   Copyright (c) 1982, 2007, Oracle.  All rights reserved.   connected to target database (not started)   RMAN> startup mount;   Oracle instance started database mounted   Total System Global Area     318767104 bytes   Fixed Size                     1267236 bytes Variable Size                104860124 bytes Database Buffers             205520896 bytes Redo Buffers                   7118848 bytes   RMAN> restore datafile 5;   Starting restore at 15-DEC-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=157 devtype=DISK   channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00005 to /u01/oracle/oradata/XFF/xifenfei01.dbf channel ORA_DISK_1: reading from backup piece /u01/oracle/oradata/tmp/truncate_xifenfei channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/oracle/oradata/tmp/truncate_xifenfei tag=TAG20111215T060445 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 15-DEC-11

odu挖rman還原後資料檔案

代碼如下	複製代碼
ODU> scan extent tablespace 6   scan extent start: 2011-12-15 06:14:43 scanning extent... scanning extent finished. scan extent completed: 2011-12-15 06:14:43   ODU>  unload table chf.t_xifenfei object 51833    Unloading table: T_XIFENFEI,object ID: 51833 Unloading segment,storage(Obj#=51833 DataObj#=51833 TS#=6 File#=5 Block#=11 Cluster=0) 4774 rows unloaded --odu只找到極少數資料4774/100110

rman backup 對於truncate和drop等相關操作的extent到底是怎麼處理的,這裡通過rman backup 結合odu證明出來,在較新版本的rman中,rman backup 並未完全的備份這些被認為不需要的extent.