Oracle数据库10g中释放flash_recovery_area
来源:互联网 发布:淘宝管控记录宝贝上架 编辑:程序博客网 时间:2024/05/16 15:42
案例: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.
此时空间得到释放:
$ 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来解决此问题。
(注:将闪回区的内容备份到第三方介质,同样可以解决此问题)
- Oracle数据库10g中释放flash_recovery_area
- 如何在Oracle中释放flash_recovery_area(温柔的方法)
- 如何在Oracle中释放flash_recovery_area(粗鲁的方法)
- oracle 10g中dblink访问远程oracle数据库
- Oracle数据库10g中使用闪回数据库操作
- oracle 11G client 连接 oracle 10G 数据库时 C#中配置的连接字符串
- Linux下oracle 10g 中关闭数据库错误
- Oracle 10g数据库中如何分析响应时间
- ORACLE数据库10G标准版
- 卸载Oracle 10g数据库
- Oracle 10g数据库管理
- 卸载Oracle 10g数据库
- Oracle 数据库 10g系列产品
- Oracle 数据库 10g 下载
- oracle 10g数据库介绍
- oracle 10g 数据库迁移
- 查询Oracle数据库中所有隐藏参数及默认值(10g、11g、12c)
- 查询Oracle数据库中所有隐藏参数及默认值(10g、11g、12c)
- QT事件
- 深入理解Flash Player重绘
- 究竟什么是POJO
- 《Visual C++入门经典》学习笔记一(Windows编程的概念) (转)
- USACO 1.2-Milking Cows
- Oracle数据库10g中释放flash_recovery_area
- abap-标准程序调试一例
- 部署APS.NET网站(应用程序)
- Flash性能优化
- PHP服务端推送技术Long Polling
- 模式窗体缓存
- exec 的用法
- ant 编写的单元测试脚本
- SQL Server 2000 附加 SQL Server 2005 数据库具体方法