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

连接数据库查询：

$ 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;

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

是以为记。

历史上的今天...

>>

2013-03-28文章:

2012-03-28文章:

2011-03-28文章:

2009-03-28文章:

2008-03-28文章:

2006-03-28文章: