Errors in file /home/oracle/diag/rdbms/orarpt/orarpt/trace/orarpt_mmon_22508.trc: ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 98.55% used, and has 31102976 remaining bytes available. ************************************************************************ You have following choices to free up space from flash recovery area: 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard, then consider changing RMAN ARCHIVELOG DELETION POLICY. 2. Back up files to tertiary device such as tape using RMAN BACKUP RECOVERY AREA command. 3. Add disk space and increase db_recovery_file_dest_size parameter to reflect the new space. 4. Delete unnecessary files using RMAN DELETE command. If an operating system command was used to delete files, then use RMAN CROSSCHECK and DELETE EXPIRED commands. ************************************************************************
今天数据库重启时，查看alert日志发现上面的错误，虽然现在数据库可以正常启动运行，但当这个db_recovery_file_dest_size使用100%的时候，
将会报错ORA-19809: limit exceeded for recovery files，数据库将会因无法归档而挂起。
查看闪回区的信息
select * from v$recovery_file_dest; NAME /home/oracle/flash_recovery_area SPACE_LIMIT 2147483648 SPACE_USED 2116380672 SPACE_RECLAIMABLE 0 NUMBER_OF_FILES 1
查看alert告警，以及oracle给出的处理建议。
select reason,object_type,suggested_action from dba_outstanding_alerts;
REASON db_recovery_file_dest_size 字节 (共 2147483648 字节) 已使用 98.55%, 尚有 31102976 字节可用。
OBJECT_TYPE RECOVERY AREA
SUGGESTED_ACTION "可以选择以下操作之一从快速恢复区释放空间:
1. 考虑更改 RMAN RETENTION POLICY。如果使用的是 Data Guard,
则考虑更改 RMAN ARCHIVELOG DELETION POLICY。
2. 使用 RMAN
BACKUP RECOVERY AREA 命令将文件备份到磁带之类的三级存储。
3. 添加磁盘空间并增大 db_recovery_file_dest_size 参数以反映
这个新空间。
4. 使用 RMAN DELETE 命令删除不需要的文件。如果使用了操作系统
命令来删除文件, 则使用 RMAN CROSSCHECK 和
DELETE EXPIRED 命令。"
这里我只试了第3、4种方法
第3种修改方法，扩展闪回区
SQL> show parameter db_recovery_file_dest_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 2G SQL> alter system set db_recovery_file_dest_size=4G scope=both; 系统已更改。 SQL> show parameter db_recovery_file_dest_size; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest_size big integer 4G
现在已经将闪回区空间扩充到了4G,也就是新增了2G。
第4种修改方法，清空闪回区
注意，刚才直接将db_recovery_file_dest目录下的文件删除，SPACE_USED也不会释放。
oracle@linux-35:~> rman target / Recovery Manager: Release 11.1.0.6.0 - Production on Wed Oct 12 12:35:09 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: ORARPT (DBID=2356255833) RMAN> crosscheck archivelog all; using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1138 device type=DISK validation failed for archived log archived log file name=/home/oracle/flash_recovery_area/ORARPT/archivelog/2011_06_08/o1_mf_1_11_6yz1mw4l_.arc RECID=2 STAMP=753316072 validation failed for archived log archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_12_750871897.dbf RECID=3 STAMP=753397594 validation failed for archived log archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_13_750871897.dbf RECID=4 STAMP=753404604 validation failed for archived log archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_14_750871897.dbf RECID=5 STAMP=753490060 validation failed for archived log archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_15_750871897.dbf RECID=6 STAMP=753530695 validation failed for archived log archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_16_750871897.dbf RECID=7 STAMP=753599698 validation failed for archived log archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_17_750871897.dbf RECID=8 STAMP=753646377 validation failed for archived log archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_18_750871897.dbf RECID=9 STAMP=753747854 validation failed for archived log archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_19_750871897.dbf RECID=10 STAMP=753809369 validation failed for archived log ……
更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1138 device type=DISK
List of Archived Log Copies for database with db_unique_name ORARPT
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
2 1 11 X 07-JUN-11
Name: /home/oracle/flash_recovery_area/ORARPT/archivelog/2011_06_08/o1_mf_1_11_6yz1mw4l_.arc
3 1 12 X 08-JUN-11
Name: /home/oracle/product/11.1.0/db_1/dbs/arch1_12_750871897.dbf
4 1 13 X 09-JUN-11
Name: /home/oracle/product/11.1.0/db_1/dbs/arch1_13_750871897.dbf
5 1 14 X 09-JUN-11
Name: /home/oracle/product/11.1.0/db_1/dbs/arch1_14_750871897.dbf
6 1 15 X 10-JUN-11
Name: /home/oracle/product/11.1.0/db_1/dbs/arch1_15_750871897.dbf
7 1 16 X 11-JUN-11
Name: /home/oracle/product/11.1.0/db_1/dbs/arch1_16_750871897.dbf
8 1 17 X 12-JUN-11
Name: /home/oracle/product/11.1.0/db_1/dbs/arch1_17_750871897.dbf
……
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/home/oracle/flash_recovery_area/ORARPT/archivelog/2011_06_08/o1_mf_1_11_6yz1mw4l_.arc RECID=2 STAMP=753316072
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_12_750871897.dbf RECID=3 STAMP=753397594
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_13_750871897.dbf RECID=4 STAMP=753404604
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_14_750871897.dbf RECID=5 STAMP=753490060
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_15_750871897.dbf RECID=6 STAMP=753530695
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_16_750871897.dbf RECID=7 STAMP=753599698
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_17_750871897.dbf RECID=8 STAMP=753646377
deleted archived log
archived log file name=/home/oracle/product/11.1.0/db_1/dbs/arch1_18_750871897.dbf RECID=9 STAMP=753747854
RMAN> exit
Recovery Manager complete.
再查看闪回区的信息，SPACE_USED已经释放。
select * from v$recovery_file_dest; NAME /home/oracle/flash_recovery_area SPACE_LIMIT 2147483648 SPACE_USED 0 SPACE_RECLAIMABLE 0 NUMBER_OF_FILES 0
本文出自 “richard的笔记-积微成著” 博客，请务必保留此出处http://zxf261.blog.51cto.com/701797/722307
以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索oracle
， log
， home
， arch
， valid file
， oracle_11g_ora_12505
__FILE__
oracle 闪回区、oracle查看闪回区大小、oracle 闪回区大小、oracle 闪回恢复区、oracle 闪回区满，以便于您获取更多的相关知识。
时间： 2024-10-30 21:06:01