使用Fast Recovery Area最大的好處在于oracle能夠?qū)τ谄渲写娣诺膫浞莼謴?fù)相關(guān)的對(duì)象進(jìn)行自動(dòng)管理，特別是在Fast Recovery Area空間利用率達(dá)到100%時(shí)能夠按照保留策略對(duì)其中的transient files進(jìn)行自動(dòng)清理，及時(shí)騰挪出可用空間，很大程度上減少了數(shù)據(jù)庫(kù)Hang的發(fā)生。下面模擬了FRA空間用滿的場(chǎng)景，之后通過(guò)不同的處理方式使得FRA又能騰出可用的空間，從中體會(huì)一下在FRA用滿的情況下oracle是如何進(jìn)行自動(dòng)清理的準(zhǔn)備數(shù)據(jù)：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; ---執(zhí)行若干次
commit;
create table t0506_tpl as select * from t0506_3;
---循環(huán)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達(dá)到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、測(cè)試超出retention policy規(guī)定的backup是否在空間用滿的時(shí)候會(huì)被刪除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，看下有否一個(gè)版本的backup會(huì)被刪除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
從上面的結(jié)果觀察到BS Key=23的backupset被清理掉了，但沒(méi)過(guò)不久上面的存儲(chǔ)過(guò)程不斷生成的archivelog又將FRA空間占滿了。
###2、測(cè)試將backupset backup到FRA以外的區(qū)域后是否FRA里的backupset會(huì)被刪除---我們接下來(lái)把剩下的25、27中的25備份到FRA以外的路徑下，觀察一下是否25備份之后會(huì)被清理掉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
從以上結(jié)果可以看出在backup backupset 25 執(zhí)行之后，原先FRA里的backup piece:/oradata06/fra/TSTDB1/backupset/2015_05_06/o1_mf_nnndf_TAG20150506T151059_1jVEc_bTM_.bkp已經(jīng)被刪除query代之的是/oradata06/vlib/03q69083_1_2這個(gè)不在FRA里的piece，同時(shí)看出retention policy 2的條件依然滿足，因?yàn)槲覀冏龅闹皇菍RA里的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; <---沒(méi)有輸出表示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
沒(méi)過(guò)一會(huì)兒，F(xiàn)RA目錄又撐滿了，這次我們嘗試將sequence 1~10的archivedlog備份到/oradata06/vlib/目錄下，看看這部分空間能否騰出來(lái)
###3、測(cè)試ARCHIVELOG DELETION POLICY TO NONE的情況下FRA里的archivelog是否會(huì)被刪除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
---盡管我們?yōu)閍rchivelog配置的策略是永久不刪除，但是在FRA滿的時(shí)候archivelog仍然會(huì)被刪除RMAN> show ARCHIVELOG DELETION POLICY;
RMAN configuration parameters for database with db_unique_name TSTDB1 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
過(guò)不久archivelog再次撐滿，這次我們將archivelog的delete policy配置成至少備份兩次到disk###4、測(cè)試ARCHIVELOG DELETION POLICY TO backed up X times 的情況下FRA里的archivelog在何種條件下才會(huì)被刪除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會(huì)否直接被刪除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
---備份完一次后我們沒(méi)有看到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
以上測(cè)試驗(yàn)證了在FRA滿的情況下：對(duì)于超出retention policy的backup會(huì)被自動(dòng)清理;把backupset備份到FRA以外的區(qū)域時(shí)，F(xiàn)RA里的backupset會(huì)被自動(dòng)清理；對(duì)于ARCHIVELOG DELETION POLICY設(shè)置為none的情況，只要FRA里的archivelog已經(jīng)進(jìn)行過(guò)了備份，F(xiàn)RA里的archivelog就會(huì)被清理對(duì)于ARCHIVELOG DELETION POLICY設(shè)置為BACKED UP N TIMES TO DISK的情況，"至少備份N次到disk"這個(gè)前提必須被滿足，F(xiàn)RA里的archivelog才會(huì)被清理
名稱(chēng)欄目：FastRecoveryArea空間用滿后的自動(dòng)清理機(jī)制
當(dāng)前地址：
http://weahome.cn/article/gcpisd.html
重慶分公司
重慶分公司
