ORA-19815: WARNING: db_recovery_file_dest_size is 100%

最新推荐文章于 2024-10-09 08:00:00 发布

tianyihuyidao9

最新推荐文章于 2024-10-09 08:00:00 发布

阅读量388

点赞数

CC 4.0 BY-SA版权

分类专栏： ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used 文章标签：数据库操作系统

本文链接：https://blog.youkuaiyun.com/tianyihuyidao9/article/details/84212736

ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了当Oracle数据库的闪回区空间不足时可能出现的警告信息，并提供了四种解决方法：调整RMAN保留策略、将文件备份到磁带、增加db_recovery_file_dest_size参数分配更多空间及删除不必要的文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[size=large]测试系统上检查警告日志时发现以下警告：

Wed May 26 14:55:16 2010
Errors in file /oracle/admin/infodb/bdump/infodb_arc1_18774.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
Wed May 26 14:55:16 2010
************************************************************************
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.
************************************************************************

Oracle给了4中解决办法：

1. 修改RMAN的备份保持策略，如果使用了Data Guard 改变archivelog 删除策略。

2. 使用Rman 把文件备份到磁带上。（这里好像只能把recovery area 备份到磁带上，备份到磁盘上
会报错，有待进一步确认。）

3. 修改db_recovery_file_dest_size 参数增加更多的空间。

4. 使用操作系统或RMAN DELETE 命令删除过期的命令。如果使用操作系统命令删除文件，要用rman执行
crosscheck archivelog all;

以下是我的解决步骤：
SQL> conn / as sysdba
SQL> show parameter db_recovery_file_dest_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 2G
SQL> col name for a30
SQL> 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
------------------------------ ---------- ---------- ----------- ----------
/oracle/flash_recovery_area 2147483648 2146841600 0 52

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

SQL> 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
------------------------------ ---------- ---------- ----------- ----------
/oracle/flash_recovery_area 2147483648 15521792 0 3

问题解决。

也可以通过修改过db_recovery_file_dest_size，把其值改大些。

SQL> alter system set db_recovery_file_dest_size=4G scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 184549376 bytes
Fixed Size 1266464 bytes
Variable Size 125832416 bytes
Database Buffers 50331648 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> show parameter db_recovery_file_dest_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 4g[/size]