Fast Recovery Area空間用滿後的自動清理機制
使用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
Deleted Oracle managed file /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp
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
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;
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才會被清理
準備資料：
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
Deleted Oracle managed file /oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp
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
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;
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才會被清理
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/53956/viewspace-1626615/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 處理歸檔滿了fast_recovery_area無剩餘空間的案例AST
- flash recovery area 空間滿了的處理辦法
- Oracle 11gR2 fast recovery area = flash recovery areaOracleAST
- Win10硬碟空間如何自動清理Win10硬碟
- 【RMAN】什麼是Fast Recovery Area(FRA)，如何配置FRAAST
- 10g 新特性 Recyclebin 空間的自動清理(zt)
- Flash Recovery Area空間不足導致資料庫不能開啟或hang住資料庫
- Linux空間不足怎麼辦？磁碟滿了如何清理？Linux
- Fail - Fast機制AIAST
- undo表空間佔用磁碟空間滿案例處理
- delete之後，快速清理表佔據的磁碟空間！delete
- win10系統怎麼設定自動清理磁碟空間Win10
- MySQL空間最佳化（空間清理）MySql
- 虛擬磁碟空間滿後的擴充套件方法套件
- 設定Flash Recovery Area
- 長事務強制重啟後一直處於 Fast Recovery 狀態AST
- 磁碟空間滿了之後MySQL會怎樣MySql
- 配置了Fast Recovery Area可能會導致snapshot controlfile被頻繁更新AST
- windows10磁碟空間不足怎麼清理_win10磁碟空間清理的方法WindowsWin10
- Oracle清理SYSAUX表空間OracleUX
- ORACLE臨時表空間的清理Oracle
- Linux空間不足怎麼辦?Linux磁碟滿了怎麼清理?Linux
- Fail-fast 機制分析AIAST
- mac硬碟空間怎麼清理？這樣也能清理出上10G的磁碟空間Mac硬碟
- 表空間滿的解決方法
- docker磁碟空間清理辦法Docker
- Docker 空間使用分析與清理Docker
- 清理oracle資料庫空間Oracle資料庫
- RAC中臨時空間使用機制
- flash_recovery_area故障處理
- OMF和flash_recovery_area的關係！
- RAC 恢復（備份後建立的表空間(leviton)恢復後會自動重建）
- 使用Vagrant 後發現虛擬機器磁碟空間爆滿的血淚填坑記虛擬機
- Fail-Fast機制詳解AIAST
- 自動undo表空間模式下切換新的undo表空間模式
- FAQ系列|磁碟空間滿了之後MySQL會怎樣MySql
- Oracle 自動段空間管理(ASSM)OracleSSM
- linux自動增加表空間Linux