数据库进阶:在Oracle中释放flash_recovery_area

发表于:2008-05-06来源:作者:点击数: 标签:数据库oracleOracleORACLEarea
案例:Oracle 数据库 10g中释放flash_recovery_area解决ORA-19815错误。 错误现象:备份Oracle数据库10g时出现下面的错误: ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available. ***
案例:Oracle数据库10g中释放flash_recovery_area解决ORA-19815错误。

  错误现象:备份Oracle数据库10g时出现下面的错误:

  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.

原文转自:http://www.ltesting.net