FastRecoveryArea空间用满后的自动清理机制
使用Fast Recovery Area最大的好处在于oracle能够对于其中存放的备份恢复相关的对象进行自动管理，特别是在Fast Recovery Area空间利用率达到100%时能够按照保留策略对其中的transient files进行自动清理，及时腾挪出可用空间，很大程度上减少了数据库Hang的发生。下面模拟了FRA空间用满的场景，之后通过不同的处理方式使得FRA又能腾出可用的空间，从中体会一下在FRA用满的情况下oracle是如何进行自动清理的
准备数据：
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
backup database;
backup database;
backup database;
list backup of database by summary;
RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
23 B F A DISK 20150506 15:10:28 1 1 NO TAG20150506T150958
25 B F A DISK 20150506 15:11:29 1 1 NO TAG20150506T151059
27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252
SYS@tstdb1-SQL> select * from v$recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ----------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 4.07 3.38 56
BACKUP PIECE 24.22 0 6
IMAGE COPY 0 0 0
FLASHBACK LOG .78 0 2
FOREIGN ARCHIVED LOG 0 0 0
SYS@tstdb1-SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ---------------- ---------------- ----------------- ----------------
/oradata06/fra 34359738368 9990663168 1162465280 64
alter system set db_recovery_file_dest_size=10G scope=memory;
create table t0506_3 tablespace TS0422_1 as select * from dba_objects;
insert into t0506_3 select * from t0506_3; ---执行若干次
commit;
create table t0506_tpl as select * from t0506_3;
---循环delete->insert生成Archivelog填充FRA
declare
begin
while ( true ) loop
delete t0506_3;
commit;
insert into t0506_3 select * from t0506_tpl;
commit;
end loop;
end;
/
---不久FRA达到100%使用率：
SYS@tstdb1-SQL> select * from v$recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 13.01 .12 35
BACKUP PIECE 77.52 0 6
IMAGE COPY 0 0 0
FLASHBACK LOG 9.14 0 8
FOREIGN ARCHIVED LOG 0 0 0
---alert.log里不断有提示FRA满的信息输出
************************************************************************
ARC3: Error 19809 Creating archive log file to '/oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_63_%u_.arc'
Errors in file /oracle/app/oracle/diag/rdbms/tstdb1/tstdb1/trace/tstdb1_arc0_42205562.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
###1、测试超出retention policy规定的backup是否在空间用满的时候会被删除
RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
23 B F A DISK 20150506 15:10:28 1 1 NO TAG20150506T150958
25 B F A DISK 20150506 15:11:29 1 1 NO TAG20150506T151059
27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252
RMAN> show RETENTION POLICY;
RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
---将redundancy 3改成redundancy 2，看下有否一个版本的backup会被删除
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
ARC3: Error 19809 Creating archive log file to '/oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_14_%u_.arc'
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_3_1jQKKtsWK_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_2_1jQKKtmNK_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_1_1jQKKtU5p_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_1_1jQIqfg-3_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_2_1jQIqfl7C_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T150958_1jVEZMOAo_.bkp <---果然被删除
Archived Log entry 67 added for thread 1 sequence 14 ID 0x79f955eb dest 1:
Archiver process freed from errors. No longer stopped
Archived Log entry 68 added for thread 1 sequence 13 ID 0x79f955eb dest 1:
Wed May 06 15:32:11 2015
Thread 1 advanced to log sequence 16 (LGWR switch)
Current log# 1 seq# 16 mem# 0: /oradata06/testaaaaa/redo01a.log
Current log# 1 seq# 16 mem# 1: /oradata06/testaaaaa/redo01b.log
Archived Log entry 69 added for thread 1 sequence 15 ID 0x79f955eb dest 1:
RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
25 B F A DISK 20150506 15:11:29 1 1 NO TAG20150506T151059
27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252
从上面的结果观察到BS Key=23的backupset被清理掉了，但没过不久上面的存储过程不断生成的archivelog又将FRA空间占满了。
###2、测试将backupset backup到FRA以外的区域后是否FRA里的backupset会被删除
---我们接下来把剩下的25、27中的25备份到FRA以外的路径下，观察一下是否25备份之后会被清理掉
backup backupset 25 format '/oradata06/vlib/%U';
Starting backup at 20150506 15:44:50
using channel ORA_DISK_1
channel ORA_DISK_1: input backup set: count=3, stamp=879001859, piece=1
channel ORA_DISK_1: starting piece 1 at 20150506 15:44:51
channel ORA_DISK_1: backup piece /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp
piece handle=/oradata06/vlib/03q69083_1_2 comment=NONE
channel ORA_DISK_1: finished piece 1 at 20150506 15:45:26
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:35
Finished backup at 20150506 15:45:26
Deleted Oracle managed file /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_ncsnf_TAG20150506T150958_1jVEbWU3n_.bkp <---原BS key=24里包含controlfile&spfile的backup piece
Deleted Oracle managed file /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp <---原BS key=25里的backup piece
RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
25 B F A DISK 20150506 15:11:29 1 1 NO TAG20150506T151059
27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
25 Full 2.57G DISK 00:00:30 20150506 15:11:29
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20150506T151059
Piece Name: /oradata06/vlib/03q69083_1_2 <----指向了FRA以外的路径
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
1 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/system01.dbf
2 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/sysaux01.dbf
3 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/undotbs01.dbf
4 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/users01.dbf
5 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/ts0329_1.dbf
6 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/xdbts1.dbf
7 Full 12723361999437 20150503 15:11:55 /oradata06/testaaaaa/ts0212.dbf
8 Full 12723361999437 20150503 15:11:55 /oradata06/testaaaaa/ts0212_1.dbf
9 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/ts0422_1.dbf
10 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/ts0505_1.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
27 Full 2.57G DISK 00:00:32 20150506 15:13:24
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20150506T151252
Piece Name: /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151252_1jVEjjcey_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
1 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/system01.dbf
2 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/sysaux01.dbf
3 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/undotbs01.dbf
4 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/users01.dbf
5 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/ts0329_1.dbf
6 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/xdbts1.dbf
7 Full 12723361999437 20150503 15:11:55 /oradata06/testaaaaa/ts0212.dbf
8 Full 12723361999437 20150503 15:11:55 /oradata06/testaaaaa/ts0212_1.dbf
9 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/ts0422_1.dbf
10 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/ts0505_1.dbf
从以上结果可以看出在backup backupset 25 执行之后，原先FRA里的backup piece:/oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp已经被删除query代之的是/oradata06/vlib/03q69083_1_2这个不在FRA里的piece，同时看出retention policy 2的条件依然满足，因为我们做的只是将FRA里的backuppiece挪到了非FRA的目录/oradata06/vlib/下
RMAN> show RETENTION POLICY;
RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
RMAN> report need backup; <---没有输出表示REDUNDANCY 2的条件依然满足
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of files with less than 2 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
RMAN> list backup of datafile 2 summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
25 B F A DISK 20150506 15:11:29 1 1 NO TAG20150506T151059
27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252
没过一会儿，FRA目录又撑满了，这次我们尝试将sequence 1~10的archivedlog备份到/oradata06/vlib/目录下，看看这部分空间能否腾出来
###3、测试ARCHIVELOG DELETION POLICY TO NONE的情况下FRA里的archivelog是否会被删除
backup archivelog sequence between 1 and 10 format '/oradata06/vlib/arc_%U';
Starting backup at 20150506 16:10:30
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=38 STAMP=879000498
input archived log thread=1 sequence=2 RECID=2 STAMP=878999036
input archived log thread=1 sequence=3 RECID=57 STAMP=879002329
input archived log thread=1 sequence=4 RECID=58 STAMP=879002333
input archived log thread=1 sequence=5 RECID=59 STAMP=879002472
input archived log thread=1 sequence=6 RECID=60 STAMP=879002478
input archived log thread=1 sequence=7 RECID=61 STAMP=879002481
input archived log thread=1 sequence=8 RECID=62 STAMP=879002818
input archived log thread=1 sequence=9 RECID=63 STAMP=879002828
input archived log thread=1 sequence=10 RECID=64 STAMP=879002837
channel ORA_DISK_1: starting piece 1 at 20150506 16:10:30
channel ORA_DISK_1: finished piece 1 at 20150506 16:10:45
piece handle=/oradata06/vlib/arc_08q693nm_1_1 tag=TAG20150506T161030 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 20150506 16:10:45
---alert.log里观察到1~10的archivelog被删除了
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_1_1jVB-byQo_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_2_1jVB-hUR3_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_1_1jV0O3oPw_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_3_1jVF2uK7X_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_4_1jVF38mNH_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_5_1jVFBN_2Y_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_6_1jVFBlUyY_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_7_1jVFB_ZRX_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_8_1jVFW46iI_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_9_1jVFWfjwo_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_10_1jVFXC84I_.arc
---尽管我们为archivelog配置的策略是永久不删除，但是在FRA满的时候archivelog仍然会被删除
RMAN> show ARCHIVELOG DELETION POLICY;
RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
过不久archivelog再次撑满，这次我们将archivelog的delete policy配置成至少备份两次到disk
###4、测试ARCHIVELOG DELETION POLICY TO backed up X times 的情况下FRA里的archivelog在何种条件下才会被删除
CONFIGURE ARCHIVELOG DELETION POLICY TO backed up 2 times to device type disk;
RMAN> show ARCHIVELOG DELETION POLICY;
RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
---先备份一次sequence: 11~20的archivelog到/oradata06/vlib/，看这些archivelog会否直接被删除
backup archivelog sequence between 11 and 20 format '/oradata06/vlib/arc_%U';
Starting backup at 20150506 16:24:07
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=65 STAMP=879002846
input archived log thread=1 sequence=12 RECID=66 STAMP=879002855
input archived log thread=1 sequence=13 RECID=68 STAMP=879003131
input archived log thread=1 sequence=14 RECID=67 STAMP=879003130
input archived log thread=1 sequence=15 RECID=69 STAMP=879003132
input archived log thread=1 sequence=16 RECID=70 STAMP=879003141
input archived log thread=1 sequence=17 RECID=71 STAMP=879003149
input archived log thread=1 sequence=18 RECID=72 STAMP=879003153
input archived log thread=1 sequence=19 RECID=73 STAMP=879003159
input archived log thread=1 sequence=20 RECID=74 STAMP=879003168
channel ORA_DISK_1: starting piece 1 at 20150506 16:24:08
channel ORA_DISK_1: finished piece 1 at 20150506 16:24:23
piece handle=/oradata06/vlib/arc_09q694h8_1_1 tag=TAG20150506T162408 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 20150506 16:24:23
---备份完一次后我们没有看到alert.log里11~20这些archivelog被删除的信息，再备份一次
backup archivelog sequence between 11 and 20 format '/oradata06/vlib/arc_%U';
Starting backup at 20150506 16:25:41
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=65 STAMP=879002846
input archived log thread=1 sequence=12 RECID=66 STAMP=879002855
input archived log thread=1 sequence=13 RECID=68 STAMP=879003131
input archived log thread=1 sequence=14 RECID=67 STAMP=879003130
input archived log thread=1 sequence=15 RECID=69 STAMP=879003132
input archived log thread=1 sequence=16 RECID=70 STAMP=879003141
input archived log thread=1 sequence=17 RECID=71 STAMP=879003149
input archived log thread=1 sequence=18 RECID=72 STAMP=879003153
input archived log thread=1 sequence=19 RECID=73 STAMP=879003159
input archived log thread=1 sequence=20 RECID=74 STAMP=879003168
channel ORA_DISK_1: starting piece 1 at 20150506 16:25:42
channel ORA_DISK_1: finished piece 1 at 20150506 16:25:57
piece handle=/oradata06/vlib/arc_0aq694k6_1_1 tag=TAG20150506T162541 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 20150506 16:25:57
--alert.log显示11~20被删除
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_11_1jVFXkYDn_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_12_1jVFYGwBc_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_14_1jVFofwXZ_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_13_1jVFojazT_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_15_1jVFonFOt_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_16_1jVFpJLXI_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_17_1jVFpl8Vt_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_18_1jVFq1Lso_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_19_1jVFqOM2x_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_20_1jVFqxB1o_.arc
以上测试验证了在FRA满的情况下：
对于超出retention policy的backup会被自动清理;
把backupset备份到FRA以外的区域时，FRA里的backupset会被自动清理；
对于ARCHIVELOG DELETION POLICY设置为none的情况，只要FRA里的archivelog已经进行过了备份，FRA里的archivelog就会被清理
对于ARCHIVELOG DELETION POLICY设置为BACKED UP N TIMES TO DISK的情况，"至少备份N次到disk"这个前提必须被满足，FRA里的archivelog才会被清理
网站名称：FastRecoveryArea空间用满后的自动清理机制
网站链接：http://scjbc.cn/article/gcpisd.html
准备数据：
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
backup database;
backup database;
backup database;
list backup of database by summary;
RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
23 B F A DISK 20150506 15:10:28 1 1 NO TAG20150506T150958
25 B F A DISK 20150506 15:11:29 1 1 NO TAG20150506T151059
27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252
SYS@tstdb1-SQL> select * from v$recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ----------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 4.07 3.38 56
BACKUP PIECE 24.22 0 6
IMAGE COPY 0 0 0
FLASHBACK LOG .78 0 2
FOREIGN ARCHIVED LOG 0 0 0
SYS@tstdb1-SQL> select * from v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ---------------- ---------------- ----------------- ----------------
/oradata06/fra 34359738368 9990663168 1162465280 64
alter system set db_recovery_file_dest_size=10G scope=memory;
create table t0506_3 tablespace TS0422_1 as select * from dba_objects;
insert into t0506_3 select * from t0506_3; ---执行若干次
commit;
create table t0506_tpl as select * from t0506_3;
---循环delete->insert生成Archivelog填充FRA
declare
begin
while ( true ) loop
delete t0506_3;
commit;
insert into t0506_3 select * from t0506_tpl;
commit;
end loop;
end;
/
---不久FRA达到100%使用率：
SYS@tstdb1-SQL> select * from v$recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 13.01 .12 35
BACKUP PIECE 77.52 0 6
IMAGE COPY 0 0 0
FLASHBACK LOG 9.14 0 8
FOREIGN ARCHIVED LOG 0 0 0
---alert.log里不断有提示FRA满的信息输出
************************************************************************
ARC3: Error 19809 Creating archive log file to '/oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_63_%u_.arc'
Errors in file /oracle/app/oracle/diag/rdbms/tstdb1/tstdb1/trace/tstdb1_arc0_42205562.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
###1、测试超出retention policy规定的backup是否在空间用满的时候会被删除
RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
23 B F A DISK 20150506 15:10:28 1 1 NO TAG20150506T150958
25 B F A DISK 20150506 15:11:29 1 1 NO TAG20150506T151059
27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252
RMAN> show RETENTION POLICY;
RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
---将redundancy 3改成redundancy 2，看下有否一个版本的backup会被删除
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
ARC3: Error 19809 Creating archive log file to '/oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_14_%u_.arc'
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_3_1jQKKtsWK_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_2_1jQKKtmNK_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_1_1jQKKtU5p_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_1_1jQIqfg-3_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_02/o1_mf_1_2_1jQIqfl7C_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T150958_1jVEZMOAo_.bkp <---果然被删除
Archived Log entry 67 added for thread 1 sequence 14 ID 0x79f955eb dest 1:
Archiver process freed from errors. No longer stopped
Archived Log entry 68 added for thread 1 sequence 13 ID 0x79f955eb dest 1:
Wed May 06 15:32:11 2015
Thread 1 advanced to log sequence 16 (LGWR switch)
Current log# 1 seq# 16 mem# 0: /oradata06/testaaaaa/redo01a.log
Current log# 1 seq# 16 mem# 1: /oradata06/testaaaaa/redo01b.log
Archived Log entry 69 added for thread 1 sequence 15 ID 0x79f955eb dest 1:
RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
25 B F A DISK 20150506 15:11:29 1 1 NO TAG20150506T151059
27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252
从上面的结果观察到BS Key=23的backupset被清理掉了，但没过不久上面的存储过程不断生成的archivelog又将FRA空间占满了。
###2、测试将backupset backup到FRA以外的区域后是否FRA里的backupset会被删除
---我们接下来把剩下的25、27中的25备份到FRA以外的路径下，观察一下是否25备份之后会被清理掉
backup backupset 25 format '/oradata06/vlib/%U';
Starting backup at 20150506 15:44:50
using channel ORA_DISK_1
channel ORA_DISK_1: input backup set: count=3, stamp=879001859, piece=1
channel ORA_DISK_1: starting piece 1 at 20150506 15:44:51
channel ORA_DISK_1: backup piece /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp
piece handle=/oradata06/vlib/03q69083_1_2 comment=NONE
channel ORA_DISK_1: finished piece 1 at 20150506 15:45:26
channel ORA_DISK_1: backup piece complete, elapsed time: 00:00:35
Finished backup at 20150506 15:45:26
Deleted Oracle managed file /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_ncsnf_TAG20150506T150958_1jVEbWU3n_.bkp <---原BS key=24里包含controlfile&spfile的backup piece
Deleted Oracle managed file /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp <---原BS key=25里的backup piece
RMAN> list backup of database summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
25 B F A DISK 20150506 15:11:29 1 1 NO TAG20150506T151059
27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
25 Full 2.57G DISK 00:00:30 20150506 15:11:29
BP Key: 29 Status: AVAILABLE Compressed: NO Tag: TAG20150506T151059
Piece Name: /oradata06/vlib/03q69083_1_2 <----指向了FRA以外的路径
List of Datafiles in backup set 25
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
1 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/system01.dbf
2 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/sysaux01.dbf
3 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/undotbs01.dbf
4 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/users01.dbf
5 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/ts0329_1.dbf
6 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/xdbts1.dbf
7 Full 12723361999437 20150503 15:11:55 /oradata06/testaaaaa/ts0212.dbf
8 Full 12723361999437 20150503 15:11:55 /oradata06/testaaaaa/ts0212_1.dbf
9 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/ts0422_1.dbf
10 Full 12723362275569 20150506 15:11:00 /oradata06/testaaaaa/ts0505_1.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -----------------
27 Full 2.57G DISK 00:00:32 20150506 15:13:24
BP Key: 27 Status: AVAILABLE Compressed: NO Tag: TAG20150506T151252
Piece Name: /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151252_1jVEjjcey_.bkp
List of Datafiles in backup set 27
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ----------------- ----
1 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/system01.dbf
2 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/sysaux01.dbf
3 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/undotbs01.dbf
4 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/users01.dbf
5 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/ts0329_1.dbf
6 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/xdbts1.dbf
7 Full 12723361999437 20150503 15:11:55 /oradata06/testaaaaa/ts0212.dbf
8 Full 12723361999437 20150503 15:11:55 /oradata06/testaaaaa/ts0212_1.dbf
9 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/ts0422_1.dbf
10 Full 12723362275665 20150506 15:12:52 /oradata06/testaaaaa/ts0505_1.dbf
从以上结果可以看出在backup backupset 25 执行之后，原先FRA里的backup piece:/oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp已经被删除query代之的是/oradata06/vlib/03q69083_1_2这个不在FRA里的piece，同时看出retention policy 2的条件依然满足，因为我们做的只是将FRA里的backuppiece挪到了非FRA的目录/oradata06/vlib/下
RMAN> show RETENTION POLICY;
RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
RMAN> report need backup; <---没有输出表示REDUNDANCY 2的条件依然满足
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of files with less than 2 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
RMAN> list backup of datafile 2 summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
25 B F A DISK 20150506 15:11:29 1 1 NO TAG20150506T151059
27 B F A DISK 20150506 15:13:24 1 1 NO TAG20150506T151252
没过一会儿，FRA目录又撑满了，这次我们尝试将sequence 1~10的archivedlog备份到/oradata06/vlib/目录下，看看这部分空间能否腾出来
###3、测试ARCHIVELOG DELETION POLICY TO NONE的情况下FRA里的archivelog是否会被删除
backup archivelog sequence between 1 and 10 format '/oradata06/vlib/arc_%U';
Starting backup at 20150506 16:10:30
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=38 STAMP=879000498
input archived log thread=1 sequence=2 RECID=2 STAMP=878999036
input archived log thread=1 sequence=3 RECID=57 STAMP=879002329
input archived log thread=1 sequence=4 RECID=58 STAMP=879002333
input archived log thread=1 sequence=5 RECID=59 STAMP=879002472
input archived log thread=1 sequence=6 RECID=60 STAMP=879002478
input archived log thread=1 sequence=7 RECID=61 STAMP=879002481
input archived log thread=1 sequence=8 RECID=62 STAMP=879002818
input archived log thread=1 sequence=9 RECID=63 STAMP=879002828
input archived log thread=1 sequence=10 RECID=64 STAMP=879002837
channel ORA_DISK_1: starting piece 1 at 20150506 16:10:30
channel ORA_DISK_1: finished piece 1 at 20150506 16:10:45
piece handle=/oradata06/vlib/arc_08q693nm_1_1 tag=TAG20150506T161030 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 20150506 16:10:45
---alert.log里观察到1~10的archivelog被删除了
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_1_1jVB-byQo_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_2_1jVB-hUR3_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_1_1jV0O3oPw_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_3_1jVF2uK7X_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_4_1jVF38mNH_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_5_1jVFBN_2Y_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_6_1jVFBlUyY_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_7_1jVFB_ZRX_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_8_1jVFW46iI_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_9_1jVFWfjwo_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_10_1jVFXC84I_.arc
---尽管我们为archivelog配置的策略是永久不删除，但是在FRA满的时候archivelog仍然会被删除
RMAN> show ARCHIVELOG DELETION POLICY;
RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
过不久archivelog再次撑满，这次我们将archivelog的delete policy配置成至少备份两次到disk
###4、测试ARCHIVELOG DELETION POLICY TO backed up X times 的情况下FRA里的archivelog在何种条件下才会被删除
CONFIGURE ARCHIVELOG DELETION POLICY TO backed up 2 times to device type disk;
RMAN> show ARCHIVELOG DELETION POLICY;
RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
---先备份一次sequence: 11~20的archivelog到/oradata06/vlib/，看这些archivelog会否直接被删除
backup archivelog sequence between 11 and 20 format '/oradata06/vlib/arc_%U';
Starting backup at 20150506 16:24:07
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=65 STAMP=879002846
input archived log thread=1 sequence=12 RECID=66 STAMP=879002855
input archived log thread=1 sequence=13 RECID=68 STAMP=879003131
input archived log thread=1 sequence=14 RECID=67 STAMP=879003130
input archived log thread=1 sequence=15 RECID=69 STAMP=879003132
input archived log thread=1 sequence=16 RECID=70 STAMP=879003141
input archived log thread=1 sequence=17 RECID=71 STAMP=879003149
input archived log thread=1 sequence=18 RECID=72 STAMP=879003153
input archived log thread=1 sequence=19 RECID=73 STAMP=879003159
input archived log thread=1 sequence=20 RECID=74 STAMP=879003168
channel ORA_DISK_1: starting piece 1 at 20150506 16:24:08
channel ORA_DISK_1: finished piece 1 at 20150506 16:24:23
piece handle=/oradata06/vlib/arc_09q694h8_1_1 tag=TAG20150506T162408 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 20150506 16:24:23
---备份完一次后我们没有看到alert.log里11~20这些archivelog被删除的信息，再备份一次
backup archivelog sequence between 11 and 20 format '/oradata06/vlib/arc_%U';
Starting backup at 20150506 16:25:41
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=65 STAMP=879002846
input archived log thread=1 sequence=12 RECID=66 STAMP=879002855
input archived log thread=1 sequence=13 RECID=68 STAMP=879003131
input archived log thread=1 sequence=14 RECID=67 STAMP=879003130
input archived log thread=1 sequence=15 RECID=69 STAMP=879003132
input archived log thread=1 sequence=16 RECID=70 STAMP=879003141
input archived log thread=1 sequence=17 RECID=71 STAMP=879003149
input archived log thread=1 sequence=18 RECID=72 STAMP=879003153
input archived log thread=1 sequence=19 RECID=73 STAMP=879003159
input archived log thread=1 sequence=20 RECID=74 STAMP=879003168
channel ORA_DISK_1: starting piece 1 at 20150506 16:25:42
channel ORA_DISK_1: finished piece 1 at 20150506 16:25:57
piece handle=/oradata06/vlib/arc_0aq694k6_1_1 tag=TAG20150506T162541 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 20150506 16:25:57
--alert.log显示11~20被删除
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_11_1jVFXkYDn_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_12_1jVFYGwBc_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_14_1jVFofwXZ_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_13_1jVFojazT_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_15_1jVFonFOt_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_16_1jVFpJLXI_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_17_1jVFpl8Vt_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_18_1jVFq1Lso_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_19_1jVFqOM2x_.arc
Deleted Oracle managed file /oradata06/fra/TSTDB1/archivelog/2015_05_06/o1_mf_1_20_1jVFqxB1o_.arc
以上测试验证了在FRA满的情况下：
对于超出retention policy的backup会被自动清理;
把backupset备份到FRA以外的区域时，FRA里的backupset会被自动清理；
对于ARCHIVELOG DELETION POLICY设置为none的情况，只要FRA里的archivelog已经进行过了备份，FRA里的archivelog就会被清理
对于ARCHIVELOG DELETION POLICY设置为BACKED UP N TIMES TO DISK的情况，"至少备份N次到disk"这个前提必须被满足，FRA里的archivelog才会被清理
网站名称：FastRecoveryArea空间用满后的自动清理机制
网站链接：http://scjbc.cn/article/gcpisd.html
