ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle db_recovery_file_dest空间不足报ora-00257问题解决

Oracle db_recovery_file_dest空间不足报ora-00257问题解决

原创 Linux操作系统 作者:regonly1 时间:2010-02-12 11:03:16 0 删除 编辑


今天在虚拟机上用impdp导入数据,到index部分的时候,突然硬盘不转了
用plsql dev客户端登录报
ORA-00257: archiver error. Connect internal only, until freed
这样的错误,跟昨天客户现场遇到的问题一样。
[oracle@localhost data]$ oerr ora 00257
00257, 00000, "archiver error. Connect internal only, until freed."
// *Cause:  The archiver process received an error while trying to archive
//       a redo log.  If the problem is not resolved soon, the database
//       will stop executing transactions. The most likely cause of this
//       message is the destination device is out of space to store the
//       redo log file.
// *Action:  Check archiver trace file for a detailed description
//        of the problem. Also verify that the
//       device specified in the initialization parameter
//       ARCHIVE_LOG_DEST is set up properly for archiving.

很开心,没想到这么容易就重现了该问题。
于是赶紧查alert log,发现了如下信息:
Errors in file /oracle/admin/lyon/bdump/lyon_arc0_4348.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
Fri Feb 12 08:16:24 2010
************************************************************************
You have following choices to free up space from flash 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.
************************************************************************
Fri Feb 12 08:16:24 2010
Errors in file /oracle/admin/lyon/bdump/lyon_arc0_4348.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 47696384 bytes disk space from 2147483648 limit
ARC0: Error 19809 Creating archive log file to '/arch/flash_recovery_area/LYON/archivelog/2010_02_12/o1_mf_1_62_0_.arc'
ARC0: Failed to archive thread 1 sequence 62 (19809)
ARCH: Archival stopped, error occurred. Will continue retrying
Fri Feb 12 08:16:24 2010
ORACLE Instance lyon - Archival Error
Fri Feb 12 08:16:24 2010
ORA-16038: log 1 sequence# 62 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/data/lyon/redo01.log'
Fri Feb 12 08:16:24 2010
Errors in file /oracle/admin/lyon/bdump/lyon_arc0_4348.trc:
ORA-16038: log 1 sequence# 62 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/data/lyon/redo01.log'
Fri Feb 12 08:16:25 2010
ARC1: Archiving not possible: No primary destinations
Fri Feb 12 08:16:25 2010
ARC0: Archiving not possible: No primary destinations
Fri Feb 12 08:16:25 2010
ARC1: Failed to archive thread 1 sequence 62 (4)
ARCH: Archival stopped, error occurred. Will continue retrying
Fri Feb 12 08:16:25 2010
ORACLE Instance lyon - Archival Error
Fri Feb 12 08:16:25 2010
ORA-16014: log 1 sequence# 62 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/data/lyon/redo01.log'
Fri Feb 12 08:16:25 2010
Errors in file /oracle/admin/lyon/bdump/lyon_arc1_4350.trc:
ORA-16014: log 1 sequence# 62 not archived, no available destinations
ORA-00312: online log 1 thread 1: '/data/lyon/redo01.log'

看来是db_recovery_file_dest空间不够了,但是奇怪,我刚才为了防止这个空间不够,而特意扩充了4G的磁盘分区。
而且在归档日志增加的过程中我也一点点的把归档文件给删除掉了,查看剩余空间还有很多呢。
仔细看了下警告日志,看到有这么一行:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
然后下面还给出了处理信息:
************************************************************************
You have following choices to free up space from flash 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.
************************************************************************
第一种方法是改变rman retention policy。即文件保留策略。如果使用的是Data Guard,则改变rman 归档日志备份策略。
第二种方法是使用rman backup recovery area将备份归档日志到第三方设备
第三种是增加磁盘空间,然后增加db_recovery_file_dest_size参数的值。
第四种是用rman delete删除不必要的归档日志文件。如果使用操作系统命令删除的(如rm),则要用rman crosscheck命令进行交叉检查,
并使用delete expired命令删除失效的文件。

这里采用的是第三种和第四种结合的方式,增加了db_recovery_file_dest_size参数。
由于磁盘空间本来就是充足的,于是只要参数值即可:
alter system set db_recovery_file_dest_size=5g scope=memory;--只修改临时的大小,不影响以后的。
然后到rman中删除所有的归档日志文件(没有进行交叉检查):
delete archivelog all;
虽然这样太过草率,不过最重要的还是能够解决这个问题了。并且以后可以遵循从以上的几种标准的模式来解决问题了。
先用第三种方式扩大参数值的目的是尽快让客户可以登录(因为实际磁盘空间是够的)。然后再采用第四种方式删除没必要的归档日志。这样此次问题就很快地解决了。

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

下一篇: shell base
请登录后发表评论 登录
全部评论

注册时间:2008-05-10

  • 博文量
    257
  • 访问量
    1070630