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

« 那些花儿 | Blog首页 | 使用Oracle10g新特性简化数据库镜像COPY »

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

作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2005/03/oracle10gecieif.html

站内相关文章|Related Articles

一个TB级数据库的备份时间及备份记录

使用RMAN进行排除表空间备份

Oracle10g备份集压缩新特性(Backupset Compression)

使用Oracle10g新特性简化数据库镜像COPY

今早备份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
   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;

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

历史上的今天...
>> 2009-03-28文章:

结束索尼爱立信的数据库培训项目

>> 2008-03-28文章:

NVIDIA GeForce4 MX 440与液晶电视

>> 2006-03-28文章:

Oracle的前尘旧版

临时文件file#和db_files的关系

By eygle on 2005-03-28 12:02 | Comments (2) | Posted to Backup&Recovery | Edit |

相关文章	随机文章
一个TB级数据库的备份时间及备份记录使用RMAN进行排除表空间备份 Oracle10g备份集压缩新特性(Backupset Compression) 使用Oracle10g新特性简化数据库镜像COPY	CBO对于Oracle SQL执行计划的影响(之二) 为何而心跳-Oracle Heartbeat研究控制文件的SECTION 11是什么? DBA警世录:Oracle的共享内存段 Oracle10g新进程 MMON 和 MMNL

搜索本站:

留言 (2)

Is there any other solution except RMAN?

Posted by: Eddy at May 30, 2008 5:09 PM

decrease the retention time for the files.
This is a test in oca.

Posted by: mrd at August 19, 2008 2:28 PM

www.eygle.com

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

留言 (2)

发表留言: