[20160112]rman会备份空块吗2.txt-CSDN博客

[20160112]rman会备份空块吗2.txt

http://www.itpub.net/thread-2050864-1-1.html

--上午zergduan拿出oracle的文档:

Database Backup and Recovery Reference

http://docs.oracle.com/database/121/RCMRF/rcmsynta006.htm#RCMRF107

When unused block compression is applied, RMAN reads only the blocks that are currently allocated to a table. RMAN still
checks each of the blocks to see whether the header has marked the block as unused. If a block has been unused, it is
not written to the backup.

Unused block compression is turned on automatically when all of the following five conditions are true:

The COMPATIBLE initialization parameter is set to 10.2 or higher.

Note: If COMPATIBLE is set to 10.2, then only tablespaces created with 10.2 compatibility are optimized to exclude
blocks that do not currently contain data. If COMPATIBLE is set to 11.0.0 or higher, however, then the first backup that
produces backup sets after COMPATIBLE is set to 11.0.0 or higher updates the headers of all locally managed data files
so that all locally managed data files can be optimized.

There are currently no guaranteed restore points defined for the database.

The data file is locally managed

The data file is being backed up to a backup set as part of a full backup or a level 0 incremental backup

The backup set is created on disk or Oracle Secure Backup is the media manager.

Note: When backing up to a media manager that is not Oracle Secure Backup, RMAN copies all the blocks regardless of
whether they contain data or not.

--还是以测试为准!

1.测试环境:

SCOTT@book> @ &r/ver1

PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE TABLESPACE SUGAR DATAFILE
'/mnt/ramdisk/book/sugar01.dbf' SIZE 100M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
NOLOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

--上面没有任何对象.

2.开始测试:
SCOTT@book> drop table tt1 purge ;
Table dropped.

SCOTT@book> create table tt1 tablespace sugar as select rownum id,to_char(rownum,'000000')||'TeStAb' name from dual connect by level<=2e5;
Table created.

SCOTT@book> select count(*) from tt1 ;
COUNT(*)
----------
200000

backup database format '/home/oracle/backup/full1_%u' ;

$ strings full1_1kqr9arj | grep TeStAb|wc
200000 200000 2999541

SCOTT@book> truncate table tt1;
Table truncated.

SCOTT@book> alter system checkpoint;
System altered.

SCOTT@book> column PARTITION_NAME noprint
SCOTT@book> select * from dba_extents where owner=user and segment_name='TT1';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ---------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT TT1 TABLE SUGAR 0 6 128 65536 8 6

SCOTT@book> select * from V$RESTORE_POINT;
no rows selected

SCOTT@book> select flashback_on from v$database ;
FLASHBACK_ON
------------------
NO

RMAN> backup database format '/home/oracle/backup/full2_%u' ;

$ strings full2_1mqr9b0h | grep TeStAb|wc
17877 17877 268097

--与前面的帖子相互呼应,证明我的观点是对的.

3.看看增量备份的情况:
RMAN> backup incremental level 0 database format '/home/oracle/backup/full_inc0_%u';

$ ll -l /home/oracle/backup/full*
-rw-r----- 1 oracle oinstall 1569062912 2016-01-13 14:53:42 /home/oracle/backup/full1_1kqr9arj
-rw-r----- 1 oracle oinstall   10059776 2016-01-13 14:53:47 /home/oracle/backup/full1_1lqr9arq
-rw-r----- 1 oracle oinstall 1563312128 2016-01-13 14:56:21 /home/oracle/backup/full2_1mqr9b0h
-rw-r----- 1 oracle oinstall   10059776 2016-01-13 14:56:33 /home/oracle/backup/full2_1nqr9b10
-rw-r----- 1 oracle oinstall 1564147712 2016-01-13 15:03:28 /home/oracle/backup/full_inc0_1oqr9bds
-rw-r----- 1 oracle oinstall   10059776 2016-01-13 15:03:32 /home/oracle/backup/full_inc0_1pqr9be3

$ strings full_inc0_1oqr9bds | grep TeStAb|wc
17877 17877 268097

--依旧一样的.

4.加入full参数看看:
RMAN> backup full database format '/home/oracle/backup/full3_%u' ;

$ ll -l /home/oracle/backup/full*
-rw-r----- 1 oracle oinstall 1569062912 2016-01-13 14:53:42 /home/oracle/backup/full1_1kqr9arj
-rw-r----- 1 oracle oinstall   10059776 2016-01-13 14:53:47 /home/oracle/backup/full1_1lqr9arq
-rw-r----- 1 oracle oinstall 1563312128 2016-01-13 14:56:21 /home/oracle/backup/full2_1mqr9b0h
-rw-r----- 1 oracle oinstall   10059776 2016-01-13 14:56:33 /home/oracle/backup/full2_1nqr9b10
-rw-r----- 1 oracle oinstall 1564172288 2016-01-13 15:09:54 /home/oracle/backup/full3_1qqr9bpu
-rw-r----- 1 oracle oinstall   10059776 2016-01-13 15:09:59 /home/oracle/backup/full3_1rqr9bq6
-rw-r----- 1 oracle oinstall 1564147712 2016-01-13 15:03:28 /home/oracle/backup/full_inc0_1oqr9bds
-rw-r----- 1 oracle oinstall   10059776 2016-01-13 15:03:32 /home/oracle/backup/full_inc0_1pqr9be3

$ strings full3_1qqr9bpu | grep TeStAb|wc
17877 17877 268097

--依旧一样的.

--补充一下10g的测试:

SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

CREATE TABLESPACE MSSM DATAFILE
'/mnt/ramdisk/test/mssm01.dbf' SIZE 16256K AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
--注意建立的表空间是EXTENT MANAGEMENT LOCAL AUTOALLOCATE.

SCOTT@test> select * from V$RESTORE_POINT;
no rows selected

SCOTT@test> select flashback_on from v$database ;
FLASHBACK_ON
------------------
NO

SCOTT@test> create table tt1 tablespace mssm as select rownum id,to_char(rownum,'000000')||'TeStAb' name from dual connect by level<=2e5;
Table created.

RMAN> backup tablespace mssm format '/home/oracle/mssm1/mssm1_%u';

$ ll -l mssm*
-rw-r----- 1 oracle oinstall 5570560 2016-01-13 15:38:09 mssm1_11qr9df1

$ strings mssm1_11qr9df1 | grep TeStAb|wc
200000 200000 2999626

SCOTT@test> truncate table tt1 ;
Table truncated.

$ ll -l mssm*
-rw-r----- 1 oracle oinstall 5570560 2016-01-13 15:38:09 mssm1_11qr9df1
-rw-r----- 1 oracle oinstall 581632 2016-01-13 15:39:25 mssm2_12qr9dhd

$ strings mssm2_12qr9dhd | grep TeStAb|wc
17003 17003 255031

$ strings mssm2_12qr9dhd | grep TeStAb | grep -v ',$'|wc
54 54 796

$ strings mssm2_12qr9dhd | grep TeStAb | grep -v ',$'> aaa.txt

$ cat aaa.txt
select
DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) N10
,DBMS_ROWID.ROWID_ROW_NUMBER (ROWID) N20
from tt1 where id in (
000001, 000320, 000635, 000950, 001265, 001580, 001895, 002210, 002525, 002840,
003155, 003470, 003785, 004100, 004415, 004730, 005045, 005360, 005675, 005990,
006305, 006620, 006935, 007250, 007565, 007880, 008510, 008825, 009140, 009455,
009770, 010081, 010382, 010683, 010984, 011285, 011586, 011887, 012188, 012489,
012790, 013091, 013392, 013693, 013994, 014295, 014596, 014897, 015198, 015499,
015800, 016101, 016402, 016703);

SCOTT@test> @ /home/oracle/mssm1/aaa.txt
N10        N20
---- ----------
18          0
19          0
20          0
21          0
22          0
23          0
24          0
25          0
26          0
27          0
28          0
29          0
30          0
31          0
32          0
33          0
34          0
35          0
36          0
37          0
38          0
39          0
40          0
41          0
42          0
43          0
45          0
46          0
47          0
48          0
49          0
50          0
51          0
52          0
53          0
54          0
55          0
56          0
57          0
58          0
59          0
60          0
61          0
62          0
63          0
64          0
65          0
66          0
67          0
68          0
69          0
70          0
71          0
72          0

54 rows selected.

WITH a
     AS (    SELECT 16 + ROWNUM x
               FROM DUAL
         CONNECT BY LEVEL <= 56)
SELECT x
FROM a
WHERE x NOT IN ( select
DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) N10
--,DBMS_ROWID.ROWID_ROW_NUMBER (ROWID) N20
from tt1 where id in (
000001, 000320, 000635, 000950, 001265, 001580, 001895, 002210, 002525, 002840,
003155, 003470, 003785, 004100, 004415, 004730, 005045, 005360, 005675, 005990,
006305, 006620, 006935, 007250, 007565, 007880, 008510, 008825, 009140, 009455,
009770, 010081, 010382, 010683, 010984, 011285, 011586, 011887, 012188, 012489,
012790, 013091, 013392, 013693, 013994, 014295, 014596, 014897, 015198, 015499,
015800, 016101, 016402, 016703));

    X
-----
   17
   44

SCOTT@test> alter system dump datafile 6 block 17 ;
System altered.

SCOTT@test> alter system dump datafile 6 block 44 ;
System altered.

--我看了一下转储,块17 frmt: 0x02 chkval: 0x1ade type: 0x10=DATA SEGMENT HEADER - UNLIMITED
--块44 ,第1条记录是8195,尾部字符估计正好是2c(ASCII),导致漏掉.
--使用bvi看看备份文件:
..
0004FFD0 41 62 2C 00 02 03 C2 52 61 0D 20 30 30 38 31 39 Ab,....Ra. 00819
0004FFE0 36 54 65 53 74 41 62 2C 00 02 03 C2 52 60 0D 20 6TeStAb,....R`.
0004FFF0 30 30 38 31 39 35 54 65 53 74 41 62 2C 06 73 09 008195TeStAb,.s.
..
--可以确定非常不巧合,这样就掉1块.这样算下来数据文件是mssm,truncate后多备份56块.

SCOTT@test> column PARTITION_NAME noprint
SCOTT@test> select * from dba_extents where owner=user and segment_name='TT1';
OWNER SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME   EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ---------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT TT1                  TABLE              MSSM                      0          6         17      65536          8            6
SCOTT TT1                  TABLE              MSSM                      1          6         25      65536          8            6
SCOTT TT1                  TABLE              MSSM                      2          6         33      65536          8            6
SCOTT TT1                  TABLE              MSSM                      3          6         41      65536          8            6
SCOTT TT1                  TABLE              MSSM                      4          6         49      65536          8            6
SCOTT TT1                  TABLE              MSSM                      5          6         57      65536          8            6
SCOTT TT1                  TABLE              MSSM                      6          6         65      65536          8            6
SCOTT TT1                  TABLE              MSSM                      7          6         73      65536          8            6

SCOTT TT1                  TABLE              MSSM                      8          6         81      65536          8            6
SCOTT TT1                  TABLE              MSSM                      9          6         89      65536          8            6
SCOTT TT1                  TABLE              MSSM                     10          6         97      65536          8            6
SCOTT TT1                  TABLE              MSSM                     11          6        105      65536          8            6
SCOTT TT1                  TABLE              MSSM                     12          6        113      65536          8            6
SCOTT TT1                  TABLE              MSSM                     13          6        121      65536          8            6
SCOTT TT1                  TABLE              MSSM                     14          6        129      65536          8            6
SCOTT TT1                  TABLE              MSSM                     15          6        137      65536          8            6
SCOTT TT1                  TABLE              MSSM                     16          6        265    1048576        128            6
SCOTT TT1                  TABLE              MSSM                     17          6        393    1048576        128            6
SCOTT TT1                  TABLE              MSSM                     18          6        521    1048576        128            6
SCOTT TT1                  TABLE              MSSM                     19          6        649    1048576        128            6
SCOTT TT1                  TABLE              MSSM                     20          6        777    1048576        128            6
21 rows selected.

-- 验证看看.
SCOTT@test> drop table tt1 purge ;
Table dropped.

RMAN> backup tablespace mssm format '/home/oracle/mssm1/mssm3_%u';

$ ll -l mssm*
-rw-r----- 1 oracle oinstall 5570560 2016-01-13 15:38:09 mssm1_11qr9df1
-rw-r----- 1 oracle oinstall 581632 2016-01-13 15:39:25 mssm2_12qr9dhd
-rw-r----- 1 oracle oinstall 581632 2016-01-13 16:12:48 mssm3_13qr9fg0