檢視每次增量備份每個資料檔案data blocks修改率(自從上次備份分後)

如果我們的備份策略是:

週日0級

週一，二 1 differential

週三 1 cumulative

週四,五 1 differential

週六 1 cumulative

我們可以透過查詢資料字典試圖v$backup_datafile瞭解到每天資料塊修改的情況.

[@more@]

v$backup_datafile : V$BACKUP_DATAFILE displays information about control files and datafiles in backup sets from the control file.
欄位解釋
FILE# : Datafile number; set to 0 for control file
DATAFILE_BLOCKS : Size of the datafile in blocks at backup time. This value is also the number of blocks taken by the datafile restarted from this backup.
BLOCKS : Size of the backup datafile (in blocks). Unused blocks are not copied to the backup.
BLOCKS_READ : Number of blocks that were scanned while taking this backup. If this was an incremental backup, and change tracking was used to optimize the backup, then the value of this column will be smaller than DATAFILE_BLOCKS. Otherwise, the value of this column will be the same as DATAFILE_BLOCKS. Even when change tracking data is used, the value of this column may be larger than BLOCKS, because the data read by change tracking is further refined during the process of creating an incremental backup.

select stamp,file#,blocks,datafile_blocks file_blks,
blocks/datafile_blocks blk_chg_rate,
blocks_read from v$backup_datafile
where incremental_level>0
/
SQL> /

STAMP FILE# BLOCKS FILE_BLKS BLK_CHG_RATE BLOCKS_READ
---------- ---------- ---------- ---------- ------------ -----------
634352807 1 21 60160 .000349069 60160
634352776 5 1 1280 .00078125 1280
634352843 3 3 29440 .000101902 29440
634352841 2 21 25600 .000820313 25600
634352816 4 1 2400 .000416667 2400
634353819 1 47 60160 .00078125 60160
634353789 5 1 1280 .00078125 1280
634353855 3 81 29440 .002751359 29440
634353853 2 124 25600 .00484375 25600
634353830 4 2079 3200 .6496875 3200
634354302 1 13 60160 .00021609 60160

STAMP FILE# BLOCKS FILE_BLKS BLK_CHG_RATE BLOCKS_READ
---------- ---------- ---------- ---------- ------------ -----------
634354271 5 1 1280 .00078125 1280
634354345 3 21 29440 .000713315 29440
634354343 2 141 25600 .005507813 25600
634354321 4 4363 5760 .757465278 5760

BLOCKS : 表示此次增量備份備份的資料塊數(也就是自上次備份後被修改的資料塊數)

FILE_BLKS: 備份時 此資料檔案所有的資料塊數

BLOCKS_READ: 單次備份需要讀取的資料塊數.10g可以透過tracking blocks技術大大縮短增量備份讀取資料塊的數量.