处理归档满了fast_recovery_area无剩余空间的案例

最新推荐文章于 2022-05-23 14:28:40 发布

转载最新推荐文章于 2022-05-23 14:28:40 发布 · 555 阅读

本文详细介绍了当Oracle数据库的归档日志区域空间不足时的处理方法，包括增加空间大小、手工删除不必要的归档日志文件以及利用RMAN命令进行归档日志清理。同时，文章还强调了在使用操作系统命令删除文件后，需要通过RMAN的crosscheck命令验证文件状态，并使用DELETEEXPIRED命令清理过期的日志和备份。最后，提供了数据库空间管理的总结和常见问题解决方案。

部署运行你感兴趣的模型镜像

今天早晨收到手机短信系统预警信息，登陆公司系统后检查发现是一台数据库系统。
查看系统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

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 进行处理。

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=68 device type=DISK

validation failed for archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/mf_1_5_7bxbhkof_.arc RECID=1 STAMP=766015219

validation failed for archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/f_1_6_7bxw2gpo_.arc RECID=2 STAMP=766033231

……………………………………

validation failed for archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_105_7gc3co97_.arc RECID=132 STAMP=770306728

validation failed for archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_106_7gc3cv1w_.arc RECID=123 STAMP=770306728

validation failed for archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_107_7gc3mbpr_.arc RECID=127 STAMP=770306728

validation succeeded for archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_1_7gxtrlnq_.arc RECID=134 STAMP=770312597

validation succeeded for archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_2_7gxtrloz_.arc RECID=135 STAMP=770312597

validation succeeded for archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_3_7gxtrodg_.arc RECID=136 STAMP=770312599

validation failed for archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_1_7gc3ojqw_.arc RECID=126 STAMP=770306728

……………………………………

validation succeeded for archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_20_7gxlq29k_.arc RECID=113 STAMP=770306728

validation succeeded for archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_21_7gxl3zdm_.arc RECID=114 STAMP=770306728

Crosschecked 136 objects

RMAN> DELETE EXPIRED archivelog all;

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=68 device type=DISK

List of Archived Log Copies for database with db_unique_name ORA11G

……………………………………

Do you really want to delete the above objects (enter YES or NO)? yes

deleted archived log

……………………………………

deleted archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_14_7gpood3n_.arc RECID=115 STAMP=770306728

deleted archived log

archived log file name=/app/oracle/fast_recovery_area/ATS/archivelog/2015_07_28/o1_mf_1_15_7gqhvvhh_.arc RECID=106 STAMP=770306727

Deleted 124 EXPIRED objects

总结：

1.使用os命令删除fast_recovery_area内容后，需要使用crosscheck检测(如:archivelog all,backup等)。

2.然后使用 DELETE EXPIRED命令删除(archivelog all,backup等)

3.指定备份策略，fast_recovery_area设置合适大小+合适的策略

4.resetlogs打开数据库后，做好备份

5.fast_recovery_area无剩余空间处理思路

   5.1)如果数据库不能登录：重启至mount，增大fast_recovery_area，open数据库，然后使用rman删除历史垃圾数据(备份集,日志,闪回日志等)

   5.2)如果数据库可以使用sys登录，增大fast_recovery_area(使其数据库可以正常工作)，然后使用rman处理垃圾数据。

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

转载于:http://blog.itpub.net/12798004/viewspace-1753761/

您可能感兴趣的与本文相关的镜像