處理歸檔滿了fast_recovery_area無剩餘空間的案例

今天早晨收到手機簡訊系統預警資訊，登陸公司系統後檢查發現是一臺資料庫系統。
檢視系統alter日誌發現如下資訊：
************************************************************************
ARC1: Error 19809 Creating archive log file to '/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_189_%u_.arc'
Errors in file /app/oracle/diag/rdbms/ats/ats/trace/ats_arc0_29634.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 5218762752 bytes is 100.00% used, and has 0 remaining bytes available.
Errors in file /app/oracle/diag/rdbms/ats/ats/trace/ats_arc3_29640.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 5218762752 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
************************************************************************
You have following choices to free up space from recovery area:
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
   DELETE EXPIRED commands.
ARC0: Error 19809 Creating archive log file to '/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_188_%u_.arc'
************************************************************************
ARC3: Error 19809 Creating archive log file to '/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_187_%u_.arc'
Errors in file /app/oracle/diag/rdbms/ats/ats/trace/ats_arc1_29636.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 5218762752 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.
************************************************************************
ARC1: Error 19809 Creating archive log file to '/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_189_%u_.arc'


原來是系統歸檔滿了,我們從預警資訊中已經看到，oracle給出了4種處理方案。現在oracle是越來越智慧了。
我採用的是第3種方案，修改歸檔日誌區的大小。
先看看目前的大小

SQL> show parameter db_reco    

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest       string  /app/oracle/fast_recovery_area
db_recovery_file_dest_size      big integer 4G

SQL> Alter system set db_recovery_file_dest_size=20G scope=both;

System altered.

SQL> show parameter db_reco    

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest       string  /app/oracle/fast_recovery_area
db_recovery_file_dest_size      big integer 20G

SQL> select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,      number_of_files as "number" from v$flash_recovery_area_usage;

FILE_TYPE     USED RECLAIMABLE number
-------------------- ---------- ----------- ----------
CONTROL FILE        0    0      0
REDO LOG        0    0      0
ARCHIVED LOG    40.47    0     32
BACKUP PIECE        0    0      0
IMAGE COPY        0    0      0
FLASHBACK LOG        0    0      0
FOREIGN ARCHIVED LOG       0    0      0

7 rows selected.

我們也可以手工刪除歸檔日誌資訊。
cd /app/oracle/fast_recovery_area
rm -rf *
登陸到rman 進行處理。