ITPub博客

首页 > 数据库 > Oracle > Oracle10g释放flash_recovery_area

Oracle10g释放flash_recovery_area

原创 Oracle 作者:m77m78 时间:2007-06-18 21:45:38 0 删除 编辑
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.[@more@]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;

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

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

上一篇: 监控索引的使用
下一篇: Oracle Tables
请登录后发表评论 登录
全部评论

注册时间:2008-04-25

  • 博文量
    168
  • 访问量
    732970