oracle修改数据文件增量,查看每次增量备份每个数据文件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技术大大缩短增量备份读取数据块的数量.