处理归档满了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 进行处理。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12798004/viewspace-1753761/，如需转载，请注明出处，否则将追究法律责任。