在Oracle 10g中如何解决ORA-19815错误

　　问题：在备份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 >

　　另一个解决方法：

　　通过执行下面的命令将闪回区的内容备份到第三方介质：

　　RMAN> backup recovery area;