Research on rman Based on Time-based backup retention policy

Time-based backup retention policy rman study 1 Experiment Preparation a) physical and logical checks [SQL] SQL> col file_name for a70 wrapped SQL> select file_name, status from dba_data_files where file_id = 4; FILE_NAME STATUS certificate ---------/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wvsw7hg _. dbf available SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'users '; TABLESPACE_NAME STATUS ------------------------------ --------- users online www.2cto.com B) large_pool [SQL] SQL> select * from v $ sgastat where pool like '% large % '; pool name bytes ------------ -------------------------- large pool PX pool 902160 large pool free memory 3292144 c) NLS_DATE_FORMAT [SQL]> vim. bash_profile> export NLS_DATE_FORMAT = 'yyyy-MM-DD: HH24: MI: ss'> .. bash_profile D) control_file_record_keep_time [SQL] SQL> show parameter control _ www.2cto.com name type value =----------- define control_file_record_keep_time integer 7 e) Clear the environment [SQL] RMAN> list backup; RMAN> list copy; specification does not match any archive log in the recovery catalog f) the configuration time window is 1 days [SQL] RMAN> CONFIGURE RETENTION POLICY TO recovery wi Ndow of 1 days; new RMAN configuration parameters: configure retention policy to recovery window of 1 DAYS; new RMAN configuration parameters are successfully stored RMAN> show all; RMAN configuration parameters are: configure retention policy to recovery window of 1 DAYS ;...... impact of www.2cto.com 2 on report obsolete [SQL] RMAN> backup tablespace users ;...... finished backup at 2012-10-09: 10: 14: 46 RM AN> backup tablespace users; // backup users twice. When the backup retention policy is exceeded, rman marks the old backup as obsolete ...... finished backup at 2012-10-09: 10: 17: 13 [SQL] [root @ think ~] # Date Tue 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 ~] # Date Tue Oct 9 23:59:03 CST 2012 Recovery Manager: Release 10.2.0.1.0-Production on Tue Oct 9 23:59:19 2012 RMAN> report obsolete ;... no obsolete backups found www.2cto.com [SQL] [root @ think ~] # Date Tue 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 ~] # Date Tue Oct 10 10:17:00 CST 2012 Recovery Manager: Release 10.2.0.1.0-Production on Tue Oct 10 2012 RMAN> report obsolete; using target database control file instead of recovery catrman RMAN retention policy will be applied to the command RMAN retention policy is set to recovery window of 1 days Report of obsolete backups and copies Type 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 www.2cto.com [SQL] [root @ think ~] # Export LANG = en_US [root @ think ~] # Date Mon 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 ~] # Date Tue 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 2012 Copyright (c) 1982,200 5, Oracle. all rights reserved. connected to target database: ORCL (DBID = 1312966189) RMAN> report obsolete; using target database control file instead of recovery catalog RMAN retention policy will be applied to the command RMAN retention policy is set to recovery window of 1 days no Impact of obsolete backups found www.2cto.com 3 on report need backup [SQL] Recovery Manager: Release 10.2.0.1.0-Production on Tue Oct 9 11:14:16 2012 RMAN> list backup; BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------------- 3 Full 13.09 m disk 00:00:01 2012-10-09: 10: 14: 46 ...... BS Key Type LV Size Device Type Elapsed Time Compl Etion Time ------- ---- -- ------------ ----------- ---------- ------------------- 4 Full 13.09 m disk 00:00:01 2012-10-09: 10: 17: 13 ...... RMAN> report need backup; RMAN retention policy will be applied to the command RMAN retention policy is set to recovery window of 1 days Report of files that must be backed up to satisfy 1 days recovery window File Days Name ----------------------------------- --------------------------- 1 2658/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7wvsw7b2 _. dbf 2 2658/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7wvsw7gf _. dbf 3 2658/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7wvsw7bd _. dbf 5 126/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7wvsxyqt _. dbf 6 122/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts_20177x5v2q05 _. dbf www.2cto.com [Root @ think ~] # Export LANG = en_US [root @ think ~] # Date Wed 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 ~] # Date Thu Oct 11 21:20:06 CST 2012 Recovery Manager: Release 10.2.0.1.0-Production on Thu Oct 11 21:20:54 2012 RMAN> report need backup; RMAN retention policy will be applied to the command RMAN retention policy is set to recovery window of 1 days Report of files that must be backed up to satisfy 1 days recovery window File Days Name ---- begin ------------------------------------------------------------ -- 1 2660/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7wvsw7b2 _. dbf 2 2660/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7wvsw7gf _. dbf 3 2660/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7wvsw7bd _. dbf 4 2/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wvsw7hg _. dbf 5 128/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7wvsxyqt _. dbf 6 124/u01/app/oracle/oradata/ORCL/data File/o1_mf_ts_rj7x5v2q05 _. dbf www.2cto.com then when report obsolete appears, the report need backup is reported immediately? [SQL] [root @ think ~] # Export LANG = en_US [root @ think ~] # Date Wed 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 ~] # Date Wed 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 2012 Copyright (c) 1982,200 5, Oracle. all rights reserved. connected to target database: ORCL (DBID = 1312966189) RMAN> report obsolete 2>; www.2cto.com using target database control file instead of recovery catrman RMAN retention policy will be applied to the command RMAN retention policy is set to recovery wi Ndow of 1 days Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------------------ reset 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 RMAN> report need backup; RMAN retention policy will be applied To the command RMAN retention policy is set to recovery window of 1 days Report of files that must be backed up to satisfy 1 days recovery window File Days Name ---- begin 1 2659/u01/app /oracle/oradata/ORCL/datafile/o1_mf_system_7wvsw7b2 _. dbf 2 2659/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7wvsw7gf _. dbf 3 2659/u01/app/oracle/orad Ata/ORCL/datafile/o1_mf_sysaux_7wvsw7bd _. dbf 5 127/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7wvsxyqt _. dbf 6 123/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts_20177x5v2q05 _. the impact of dbf www.2cto.com 4 on optimization backup optimization has nothing to do with speed, but whether to back up the read-only tablespace [SQL] 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 stored RMAN> backup database; // although users is offline, backup OPTIMIZATION is also enabled, however, for the first time, rman will still use users to back up www.2cto.com Starting backup at 2012-10-09: 12: 16: 15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid = 139 devtype = DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile (s) in backupset input datafile fno = 00001 Name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7wvsw7b2 _. dbf input datafile fno = 00003 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7wvsw7bd _. dbf input datafile fno = 00004 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wvsw7hg _. dbf input datafile fno = 00005 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7wvsxyqt _. dbf input datafile fno = 00006 name =/u01/app/L E/oradata/ORCL/datafile/o1_mf_ts_rj7x5v2q05 _. dbf input datafile fno = 00002 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7wvsw7gf _. dbf channel ORA_DISK_1: starting piece 1 at 2012-10-09: 12: 16: 16 channel ORA_DISK_1: finished piece 1 at 2012-10-09: 12: 17: 01 piece handle =/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_10_09/o1_mf_nnndf_tag20121009t1216_87798jhg _. bkp tag = TAG2012100 9T121616 comment = NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile (s) in backupset including current control file in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 2012-10-09: 12: 17: 03 channel ORA_DISK_1: finished piece 1 at 2012-10-09: 12: 17: 04 pie Ce handle =/u01/app/oracle/flash_recovery_area/ORCL/backupset/2012_10_09/o1_mf_ncsnf_TAG20121009T121616_87799z8h _. bkp tag = TAG20121009T121616 comment = NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 2012-10-09: 12: 17: 04 RMAN> backup database; // The second users is automatically kicked out by rman by Starting backup at 2012-10-09: 12: 17: 22 using channel ORA_DISK_1 skipping datafile 4; already backed Up 1 time (s) channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile (s) in backupset input datafile fno = 00001 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7wvsw7b2 _. dbf input datafile fno = 00003 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7wvsw7bd _. dbf input datafile fno = 00005 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7wvsx Yqt _. dbf input datafile fno = 00006 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts_1_7x5v2q05 _. dbf input datafile fno = 00002 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7wvsw7gf _. dbf channel ORA_DISK_1: starting piece 1 at 2012-10-09: 12: 17: 23 www.2cto.com [SQL] [root @ think ~] # Export LANG = en_US [root @ think ~] # Date Tue 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 ~] # Date Wed Oct 10 10:21:45 CST 2012 Recovery Manager: Release 10.2.0.1.0-Production on Wed Oct 10 10:21:59 2012 RMAN> backup database; Starting backup at 2012-10-10: 10: 22: 11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid = 145 devtype = DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile (s) in backupset input datafile fno = 00001 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_7wvsw7b2 _. dbf input datafile fno = 00003 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_7wvsw7bd _. dbf input datafile fno = 00004 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_7wvsw7hg _. dbf input datafile fno = 00005 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_7wvsxyqt _. dbf input datafile fno = 00006 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts_1_7x5v2q05 _. dbf input datafile fno = 00002 name =/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_7wvsw7gf _. dbf channel ORA_DISK_1: starting piece 1 at 2012-10-10: 10: 22: 12 www.2cto.com 5 experiment conclusion: under the assumption of recovery window of 1 days: (I) optimization and read only: half day (ii) report obsolete: one whole day (iii) report need backup: 1.5 days