This article describes how Oracle uses odu to verify rman backup to process command instances for truncate object backup. If you need rman backup, you can refer to this article.
By Digging data files before and after rman backup through odu, we know the rman backup process, and the raw data of the vast majority of truncate tables is not properly backed up (why is the vast majority, I cannot explain it). here we can see that rman backup is not a real physical copy (it is different from rman copy, and copy cannot be completely replaced)
Create a simulated environment
| The Code is as follows: |
Copy code |
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 10 m autoextend on maxsize 10g;
Tablespace created.
SQL> conn chf/xifenfei
Connected.
SQL> create table t_xifenfei tablespace xifenfei
2
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 = 'ifenfei ';
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
-- Here we get the following information:
-- 1. dataobj # = 51833
-- 2. The data file space is 11599872 |
Rman backup no truncate table data file
| The Code is as follows: |
Copy code |
[Oracle @ xifenfei ~] $ Rman target/
Recovery Manager: Release 10.2.0.4.0-Production on Thu Dec 15 06:00:05 2011
Copyright (c) 1982,200 7, 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 = tag20151115t060343 comment = NONE
Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 15-DEC-11 |
Truncate table operation
| The Code is as follows: |
Copy code |
[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,200 7, Oracle. All Rights Reserved.
Connected:
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 backs up the truncate table data file
| The Code is as follows: |
Copy code |
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 = tag20151115t060445 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/u01/oracle/oradata/tmp/no_truncate_xifenfei
-Rw-r ----- 1 oracle oinstall 630784 Dec 15/u01/oracle/oradata/tmp/truncate_xifenfei
|
Odu dug the data file before rman backup
| The Code is as follows: |
Copy code |
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 $ 'sobj #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 $ 'sobj #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
-- Here we can see that odu finds all the records dropped by truncate.
|
Use rman to back up data files
| The Code is as follows: |
Copy code |
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,200 7, 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 = tag20151115t060445
Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 15-DEC-11
|
Data Files after rman restoration by odu
| The Code is as follows: |
Copy code |
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 only finds a very small number of data 4774/100110 |
How does rman backup deal with extent related operations such as truncate and drop? It is proved by rman backup combined with odu that in the later versions of rman, rman backup does not completely back up the extent.
