基于时间的备份保留策略的rman研究
1 实验准备
a）物理和逻辑上检查
SQL> col file_name for a70 wrappedSQL> select file_name,status from dba_data_files where file_id=4;FILE_NAME STATUS---------------------------------- ---------/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wvsw7hg_.dbf AVAILABLESQL> select tablespace_name,status from dba_tablespaces where tablespace_name='USERS';TABLESPACE_NAME STATUS------------------------------ ---------USERS ONLINE
b）large_pool
SQL> select * from v$sgastat where pool like '%large%';POOL NAME BYTES------------ -------------------------- ----------large pool PX msg pool 902160large pool free memory 3292144
c）NLS_DATE_FORMAT
>vim .bash_profile >export NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS' >. .bash_profile
d）control_file_record_keep_time
SQL> show parameter control_NAME TYPE VALUE------------------------------------ ----------- ------------------------------control_file_record_keep_time integer 7
e）清空环境
RMAN> list backup;RMAN> list copy;specification does not match any archive log in the recovery catalog
f）配置时间窗口为1 days
RMAN> CONFIGURE RETENTION POLICY TO recovery window of 1 days;new RMAN configuration parameters:CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;new RMAN configuration parameters are successfully storedRMAN> show all;RMAN configuration parameters are:CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;......
RMAN> backup tablespace users;......Finished backup at 2012-10-09:10:14:46RMAN> backup tablespace users; //对users备份两次，当超过备份保留策略时，rman会将老的备份标志为obsolete......Finished backup at 2012-10-09:10:17:13
[root@think ~]# dateTue Oct 9 10:31:03 CST 2012[root@think ~]# date -s "Tue Oct 9 23:59:00 CST 2012"Tue Oct 9 23:59:00 CST 2012[root@think ~]# dateTue Oct 9 23:59:03 CST 2012Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 9 23:59:19 2012RMAN> report obsolete;...no obsolete backups found
[root@think ~]# dateTue Oct 9 10:31:03 CST 2012[root@think ~]# date -s "Tue Oct 10 10:17:00 CST 2012"Tue Oct 10 10:17:00 CST 2012[root@think ~]# dateTue Oct 10 10:17:00 CST 2012Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 10 10:17:5 2012RMAN> report obsolete;using target database control file instead of recovery catalogRMAN retention policy will be applied to the commandRMAN retention policy is set to recovery window of 1 daysReport of obsolete backups and copiesType Key Completion Time Filename/Handle-------------------- ------ ------------------ --------------------Backup Set 3 2012-10-09:10:14:46 Backup Piece 3 2012-10-09:10:14:46 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_10_09/o1_mf_nnndf_TAG20121009T101445_87724os6_.bkp
[root@think ~]# export LANG=en_US[root@think ~]# dateMon Oct 15 10:28:03 CST 2012[root@think ~]# date -s "Mon Oct 9 10:28:03 CST 2012"Tue Oct 9 10:28:03 CST 2012[root@think ~]# dateTue Oct 9 10:28:06 CST 2012[root@think ~]# su - oracle[oracle@think ~]$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 9 10:28:20 2012Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: ORCL (DBID=1312966189)RMAN> report obsolete;using target database control file instead of recovery catalogRMAN retention policy will be applied to the commandRMAN retention policy is set to recovery window of 1 daysno obsolete backups found
3 对report need backup的影响
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Oct 9 11:14:16 2012RMAN> list backup;BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------3 Full 13.09M DISK 00:00:01 2012-10-09:10:14:46......BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------4 Full 13.09M DISK 00:00:01 2012-10-09:10:17:13......RMAN> report need backup;RMAN retention policy will be applied to the commandRMAN retention policy is set to recovery window of 1 daysReport of files that must be backed up to satisfy 1 days recovery windowFile Days Name---- ----- -----------------------------------------------------1 2658 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7wvsw7b2_.dbf2 2658 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7wvsw7gf_.dbf3 2658 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7wvsw7bd_.dbf5 126 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7wvsxyqt_.dbf6 122 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts_1_7x5v2q05_.dbf[root@think ~]# export LANG=en_US[root@think ~]# dateWed Oct 10 21:20:03 CST 2012[root@think ~]# date -s "Wed Oct 11 21:20:03 CST 2012"Thu Oct 11 21:20:03 CST 2012[root@think ~]# dateThu Oct 11 21:20:06 CST 2012Recovery Manager: Release 10.2.0.1.0 - Production on Thu Oct 11 21:20:54 2012RMAN> report need backup;RMAN retention policy will be applied to the commandRMAN retention policy is set to recovery window of 1 daysReport of files that must be backed up to satisfy 1 days recovery windowFile Days Name---- ----- -----------------------------------------------------1 2660 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7wvsw7b2_.dbf2 2660 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7wvsw7gf_.dbf3 2660 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7wvsw7bd_.dbf4 2 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wvsw7hg_.dbf5 128 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7wvsxyqt_.dbf6 124 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts_1_7x5v2q05_.dbf
那么当出现report obsolete时，会立即报告report need backup?
[root@think ~]# export LANG=en_US[root@think ~]# dateWed Oct 10 10:19:16 CST 2012[root@think ~]# date -s "Tue Oct 10 10:17:00 CST 2012"Wed Oct 10 10:17:00 CST 2012[root@think ~]# dateWed Oct 10 10:17:03 CST 2012[root@think ~]# su - oracle[oracle@think ~]$ rman target /Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 10 10:17:14 2012Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to target database: ORCL (DBID=1312966189)RMAN> report obsolete2> ;using target database control file instead of recovery catalogRMAN retention policy will be applied to the commandRMAN retention policy is set to recovery window of 1 daysReport of obsolete backups and copiesType Key Completion Time Filename/Handle-------------------- ------ ------------------ --------------------Backup Set 3 2012-10-09:10:14:46 Backup Piece 3 2012-10-09:10:14:46 /u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_10_09/o1_mf_nnndf_TAG20121009T101445_87724os6_.bkpRMAN> report need backup;RMAN retention policy will be applied to the commandRMAN retention policy is set to recovery window of 1 daysReport of files that must be backed up to satisfy 1 days recovery windowFile Days Name---- ----- -----------------------------------------------------1 2659 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7wvsw7b2_.dbf2 2659 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7wvsw7gf_.dbf3 2659 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7wvsw7bd_.dbf5 127 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7wvsxyqt_.dbf6 123 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts_1_7x5v2q05_.dbf
4 对optimization的影响
备份优化与速度无关，而是针对是否对只读表空间进行备份
SQL> alter tablespace users read only;Tablespace altered.RMAN> CONFIGURE BACKUP OPTIMIZATION on;new RMAN configuration parameters:CONFIGURE BACKUP OPTIMIZATION ON;new RMAN configuration parameters are successfully storedRMAN> backup database; //虽然users下线，而且备份优化也开了，但是第一次rman仍然会把users拿去备份Starting backup at 2012-10-09:12:16:15allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=139 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7wvsw7b2_.dbfinput datafile fno=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7wvsw7bd_.dbfinput datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wvsw7hg_.dbfinput datafile fno=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7wvsxyqt_.dbfinput datafile fno=00006 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts_1_7x5v2q05_.dbfinput datafile fno=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7wvsw7gf_.dbfchannel ORA_DISK_1: starting piece 1 at 2012-10-09:12:16:16channel ORA_DISK_1: finished piece 1 at 2012-10-09:12:17:01piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_10_09/o1_mf_nnndf_TAG20121009T121616_87798jhg_.bkp tag=TAG20121009T121616 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:45channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetincluding current control file in backupsetincluding current SPFILE in backupsetchannel ORA_DISK_1: starting piece 1 at 2012-10-09:12:17:03channel ORA_DISK_1: finished piece 1 at 2012-10-09:12:17:04piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_10_09/o1_mf_ncsnf_TAG20121009T121616_87799z8h_.bkp tag=TAG20121009T121616 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03Finished backup at 2012-10-09:12:17:04RMAN> backup database; //第二次users就被rman自动踢掉了Starting backup at 2012-10-09:12:17:22using channel ORA_DISK_1skipping datafile 4; already backed up 1 time(s)channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7wvsw7b2_.dbfinput datafile fno=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7wvsw7bd_.dbfinput datafile fno=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7wvsxyqt_.dbfinput datafile fno=00006 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts_1_7x5v2q05_.dbfinput datafile fno=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7wvsw7gf_.dbfchannel ORA_DISK_1: starting piece 1 at 2012-10-09:12:17:23
[root@think ~]# export LANG=en_US[root@think ~]# dateTue Oct 9 12:21:44 CST 2012[root@think ~]# date -s "Tue Oct 10 10:21:44 CST 2012"Wed Oct 10 10:21:44 CST 2012[root@think ~]# dateWed Oct 10 10:21:45 CST 2012Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 10 10:21:59 2012RMAN> backup database;Starting backup at 2012-10-10:10:22:11using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=145 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7wvsw7b2_.dbfinput datafile fno=00003 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7wvsw7bd_.dbfinput datafile fno=00004 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wvsw7hg_.dbfinput datafile fno=00005 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7wvsxyqt_.dbfinput datafile fno=00006 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts_1_7x5v2q05_.dbfinput datafile fno=00002 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7wvsw7gf_.dbfchannel ORA_DISK_1: starting piece 1 at 2012-10-10:10:22:12
在recovery window of 1 days的假设下：
㈠ optimization和read only下：半天
㈡ report obsolete：一整天
㈢ report need backup：1.5天
