hong'an.lin

历程

日志

ORA-19809: limit exceeded for recovery files

Oracle10g如何释放flash_recovery_area，解决ORA-19815错误

2010-03-17 10:18:28| 分类：数据库 | 标签： |字号大中小订阅

今早备份Oracle10g数据库的时候，出现以下错误:

ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
*************************************************************
You have the following choices to free up space from
flash recovery area:
1. Consider changing your RMAN retention policy.
If you are using dataguard, then consider changing your
RMAN archivelog deletion policy.
2. Backup files to tertiary device such as tape using the
RMAN command BACKUP RECOVERY AREA.
3. Add disk space and increase the db_recovery_file_dest_size
parameter to reflect the new space.
4. Delete unncessary files using the RMAN DELETE command.

If an OS command was used to delete files, then use

转自：http://jingh3209.blog.163.com/blog/static/1569667200973145728870/

RMAN CROSSCHECK and DELETE EXPIRED commands.
*************************************************************

这时候实际上flash_recovery_area已经手工释放空间，甚至切换到一个全新的磁盘，仍然无法解决。
连接数据库查询：

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Mar 28 11:45:30 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SYS AS SYSDBA on 28-MAR-05 >set liesize 120
SP2-0158: unknown SET option "liesize"
SYS AS SYSDBA on 28-MAR-05 >set linesize 120
SYS AS SYSDBA on 28-MAR-05 >SELECT substr(name, 1, 30) name, space_limit AS quota,
2 space_used AS used,
3 space_reclaimable AS reclaimable,
4 number_of_files AS files
5 FROM v$recovery_file_dest ;

NAME QUOTA USED RECLAIMABLE FILES
---------------------------------- ---------- ---------- ----------- ----------
/data5/flash_recovery_area 2147483648 2144863232 0 227

发现仍然记录了227个文件，USED空间并未释放。

使用rman登录数据库进行crosscheck：

$ rman target /

Recovery Manager: Release 10.1.0.2.0 - 64bit Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to target database: EYGLE (DBID=1337390772)

RMAN> crosscheck archivelog all;

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
validation failed for archived log
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_05_17/o1_mf_1_790_0bjq36ps_.arc recid=1 stamp=526401126
validation failed for archived log
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_05_17/o1_mf_1_791_0bkbcy7x_.arc recid=2 stamp=526420862
validation failed for archived log
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_05_17/o1_mf_1_792_0bkkds4d_.arc recid=3 stamp=526428057
.......
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_07_16/o1_mf_1_1014_0hh3zsrp_.arc recid=225 stamp=531678074
validation failed for archived log
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_07_16/o1_mf_1_1015_0hh40qyp_.arc recid=226 stamp=531678104
validation failed for archived log
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_07_16/o1_mf_1_1016_0hh41jqq_.arc recid=227 stamp=531678129
Crosschecked 227 objects

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK

List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
1 1 790 X 17-MAY-04 /opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_05_17/o1_mf_1_790_0bjq36ps_.arc
2 1 791 X 17-MAY-04 /opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_05_17/o1_mf_1_791_0bkbcy7x_.arc
3 1 792 X 17-MAY-04 /opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_05_17/o1_mf_1_792_0bkkds4d_.arc
.......
225 1 1014 X 16-JUL-04 /opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_07_16/o1_mf_1_1014_0hh3zsrp_.arc
226 1 1015 X 16-JUL-04 /opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_07_16/o1_mf_1_1015_0hh40qyp_.arc
227 1 1016 X 16-JUL-04 /opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_07_16/o1_mf_1_1016_0hh41jqq_.arc

Do you really want to delete the above objects (enter YES or NO)? YES
deleted archive log
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_05_17/o1_mf_1_790_0bjq36ps_.arc recid=1 stamp=526401126
deleted archive log
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_05_17/o1_mf_1_791_0bkbcy7x_.arc recid=2 stamp=526420862
deleted archive log
......
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_07_16/o1_mf_1_1014_0hh3zsrp_.arc recid=225 stamp=531678074
deleted archive log
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_07_16/o1_mf_1_1015_0hh40qyp_.arc recid=226 stamp=531678104
deleted archive log
archive log filename=/opt/oracle/flash_recovery_area/EYGLE/
archivelog/2004_07_16/o1_mf_1_1016_0hh41jqq_.arc recid=227 stamp=531678129
Deleted 227 EXPIRED objects

RMAN> exit

Recovery Manager complete.

此时空间得以释放:

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Mar 28 12:02:19 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SYS AS SYSDBA on 28-MAR-05 >SELECT substr(name, 1, 30) name, space_limit AS quota,
2 space_used AS used,
3 space_reclaimable AS reclaimable,
4 number_of_files AS files
5 FROM v$recovery_file_dest ;

NAME QUOTA USED RECLAIMABLE FILES
---------------------------------------- ---------- ---------- ----------- ----------
/data5/flash_recovery_area 2147483648 9959424 0 1

SYS AS SYSDBA on 28-MAR-05 >

Oracle指出，我们可以通过执行以下命令:

RMAN> backup recovery area;

将闪回区的内容备份到第三方介质，也同样可以解决这个问题。
是以为记。

评论这张

转发至微博

0人 | 分享到：

阅读(24)| 评论(0)| 引用 (0) |举报

ORA-19809: limit exceeded for recovery files

处理flash_recovery_area空间用完过程

历史上的今天

this.p={  m:2,
              b:2,
              id:'fks_080066092082082069082084082095093083081074080084081074',
              blogTitle:'Oracle10g如何释放flash_recovery_area，解决ORA-19815错误',
              blogAbstract:'今早备份Oracle10g数据库的时候，出现以下错误:<BR\><BR\>ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.<BR\>*************************************************************<BR\>You have the following choices to free up space from<BR\>flash recovery area:<BR\>1. Consider changing your RMAN retention policy.<BR\> If you are using dataguard, then consider changing your',
              blogTag:'',
              blogUrl:'blog/static/950942792010217101828140',
              isPublished:1,
              istop:false,
              type:0,
              modifyTime:0,
              publishTime:1268792308140,
              permalink:'blog/static/950942792010217101828140',
              commentCount:0,
              mainCommentCount:0,
              recommendCount:0,
              bsrk:-100,
              publisherId:0,
              recomBlogHome:false,
              attachmentsFileIds:[],
              vote:{},
              groupInfo:{},
              friendstatus:'none',
              followstatus:'unFollow',
              pubSucc:'',
              visitorProvince:'未设置',
              visitorCity:'未设置',
              postAddInfo:{"postAdSetting":{"title":"youdaonote","linkUrl":"http://g.163.com/a?CID=14253&Values=715242298&Redirect=http://e.cn.miaozhen.com/r.gif?k=1001788&p=3xjnR0&ae=1000240&vo=2ceafe9f2&vr=2&o=http%3A%2F%2Fphdi.qq.com","imageUrl":"http://img1.126.net/channel8/011811_5.21.swf","expireDay":"14","modifyTime":"1337569753119","flashUrl":"http://img1.126.net/channel8/011811_5.21.swf"},"isAdShow":true,"postAdContent":{"special":[{"title":"周国平：纪念<讲话>时我纪念什么","url":"http://blog.163.com/zhouguoping_vip/blog/static/1689663172012423200454/"},{"title":"舌尖上的乡愁和地沟里的中国","url":"http://spacebandit.blog.163.com/blog/static/39710562201242402519546/"},{"title":"周濂：永远都无法叫醒一个装睡的人","url":"http://westernphilosophy.blog.163.com/blog/static/187136009201242343652663/"}],"blog":[{"title":"实拍舌尖上的北京夜市","pic":"http://img2.cache.netease.com/cnews/2012/5/24/2012052408440699dca.jpg","url":"http://blog.163.com/"},{"title":"郎咸平：戳穿中国电力企业的谎言","url":"http://blog.163.com/"},{"title":"台湾作家：台湾并没韩寒写的那么好","url":"http://liaoxinzhong.blog.163.com/blog/static/206945029201242332256424/"},{"title":"《心术》导演：首轮落幕期待再相会","url":"http://yangyangvip2012.blog.163.com/blog/static/207281112201242314729798/"},{"title":"营养师：小笼包中'血脖肉馅'的危害","url":"http://xujinxi2012.blog.163.com/blog/static/20729214020124178370706/"},{"title":"张俊以：敢向赵本山说不的东北演员","url":"http://zhangjunyivip.blog.163.com/blog/static/1811722442012424745374/"}]}},
              mset:'000',
              mcon:'',
              srk:-100,
              remindgoodnightblog:false,
              isBlackVisitor:false,
              isShowYodaoAd:false,
              hostIntro:'',
              hmcon:'1',
              lofter_single:'<a hidefocus="true" target="_blank" href="http://www.lofter.com/specials/2012cmvfestival"><img onload="var nimg = new Image(); nimg.src=\'http://blog.163.com/newpage/images/analyse.png?lofter_single&t=\'+new Date().getTime()" border="0" src="http://b2.bst.126.net/newpage/images/lofter_single5.jpg"/></a>'
            }

{list a as x}
    {if !!x}
    <div class="iblock nbw-fce nbw-f40">
      <a class="fc03 noul" target="_blank" hidefocus="true" href="http://blog.163.com/${x.visitorName}/">
      {if x.visitorName==visitor.userName}
      <img alt="${x.visitorNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.visitorName)}&r=${visitor.imageUpdateTime}"/>
      {else}
      <img alt="${x.visitorNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.visitorName)}"/>
      {/if}
      </a>
      <div class="cwd vname thide">
        {if x.moveFrom=='wap'}
          <a class="noul pnt" target="_blank" href="http://blog.163.com/services/wapblog.html?frompersonalbloghome"><span title="来自网易手机博客" class="iblock wapIcon"> </span></a>
        {elseif x.moveFrom=='iphone'}
          <a class="noul pnt" target="_blank"><span title="来自iPhone客户端" class="iblock iphoneIcon"> </span></a>
        {elseif x.moveFrom=='android'}
          <a class="noul pnt" target="_blank"><span title="来自Android客户端" class="iblock androidIcon"> </span></a>
        {elseif x.moveFrom=='mobile'}
          <a class="noul pnt" target="_blank" href="http://blog.163.com/services/emsblog.html?frompersonalbloghome"><span title="来自网易短信写博" class="iblock wapIcon"> </span></a>
        {/if}
        <a class="fc03 m2a"  target="_blank" hidefocus="true" href="http://blog.163.com/${x.visitorName}/">
          ${fn(x.visitorNickname,8)|escape}
        </a>
      </div>
    </div>
    {/if}
    {/list}

<#--最新日志，群博日志--> <#--推荐日志-->

<p class="fc06">推荐过这篇日志的人：</p>
    <div>
      {list a as x}
      {if !!x}
      <div class="iblock nbw-fce nbw-f40">
        <a class="fc03 noul" target="_blank" hidefocus="true" href="http://blog.163.com/${x.recommenderName}/">
        <img alt="${x.recommenderNickname|escape}" onerror="this.src=location.f40" class="cwd bdwa bdc0" src="${fn1(x.recommenderName)}"/>
        </a>
        <div class="cwd thide">
          <a class="fc03 m2a" target="_blank" hidefocus="true" href="http://blog.163.com/${x.recommenderName}/">
            ${fn(x.recommenderNickname,6)|escape}
          </a>
        </div>
      </div>
      {/if}
      {/list}
    </div>
    {if !!b&&b.length>0}
    <p  class="fc06">他们还推荐了：</p>
    <ul>
    {list b as y}
      {if !!y}
        <li class="rrb"><span class="iblock">·</span><a class="fc03 m2a" target="_blank" href="http://blog.163.com/${y.recommendBlogPermalink}/?from=blog/static/950942792010217101828140">${y.recommendBlogTitle|escape}</a></li>
      {/if}
    {/list}
    </ul>
    {/if}

<#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--相关文章--> <#--历史上的今天--> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->

页脚

公司简介 - 联系方法 - 招聘信息 - 客户服务 - 隐私政策 - 博客风格 - 手机博客 - VIP博客 - 订阅此博客

hong'an.lin

导航

日志

Oracle10g如何释放flash_recovery_area，解决ORA-19815错误

历史上的今天

相关文章

最近读者

评论

页脚