ORA-19804: cannot reclaim 48156160 bytes disk space from 4039114752 limit

最新推荐文章于 2025-01-26 14:00:00 发布

rudy_gao

最新推荐文章于 2025-01-26 14:00:00 发布

阅读量2.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle_error oracle_backup

本文链接：https://blog.youkuaiyun.com/rudygao/article/details/21275041

oracle_error 同时被 2 个专栏收录

55 篇文章

订阅专栏

oracle_backup

12 篇文章

订阅专栏

当Oracle数据库遇到ORA-19804错误，表示无法回收磁盘空间。当闪回恢复区使用超过97%时，会触发严重告警。解决方案包括扩大恢复区大小并在数据库nomount状态下用RMAN删除过期文件。

1.　今天启动计算机时，发现如下错误：

SYS@orcl>select status from v$database;
select status from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

2.　查看log.xml,发现如下错误：

<msg time='2014-03-15T09:07:55.870+08:00' org_id='oracle' comp_id='rdbms'
 client_id='' type='UNKNOWN' level='16'
 host_id='WO' host_addr='fe80::780d:d5a2:b794:f5cf%18' module='oradim.exe'
 pid='3628'>
 <txt>Errors in file d:\study\oracle11g\oralce_basic_directory\diag\rdbms\orcl\orcl\trace\orcl_ora_3628.trc:
ORA-16038: log 1 sequence# 106 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'D:\STUDY\ORACLE11G\ORACLE_DATABASE\ORCL\REDO01.LOG'
 </txt>
</msg>

3.　找到trace文件，进一步确定问题根源：

ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from 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.
************************************************************************
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 48156160 bytes disk space from 4039114752 limit
*** 2014-03-15 09:07:55.780 4132 krsh.c
ARCH: Error 19809 Creating archive log file to 'D:\STUDY\ORACLE11G\ORALCE_BASIC_DIRECTORY\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_03_15\O1_MF_1_106_%U_.ARC'
*** 2014-03-15 09:07:55.780 2747 krsi.c

在trace 文件中可以看到问题的根源，闪回恢复已经满，oracle无法归档，而后oracle又给出解决方案。

5.　在闪回恢复区中的空间使用超过 85% 的时候，数据库将会向 alert 文件中写入告警信息。而当超过 97% 的时候将会写入严重告警信息。当闪回恢复区空间不够的时候，Oracle将报告如下类似的错误：
ORA-19809:limit exceeded for recovery files
ORA-19804:cannot reclaim 52428800 bytes disk space from 1258291200 limit

6.　解决方案，应该先把闪回恢复区扩大，打开数据库，而后用rman删除过期的备份文件和归档文件，注意在数据库未打开时，rman不能进行删除备份　文件和归档文件

6.1　启动数据库到nomount;

SYS@orcl>startup nomount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             662700496 bytes
Database Buffers          402653184 bytes
Redo Buffers                4603904 bytes

6.2 扩大闪回恢复区的大小

SYS@orcl>show parameter db_recovery

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_recovery_file_dest                string
D:\study\oracle11g\oralce_basi
c_directory\flash_recovery_are
a
db_recovery_file_dest_size           big integer
3852M
SYS@orcl>alter system set db_recovery_file_dest_size=5g scope=both;

System altered.

6.3　此时可以打开数据库了

SYS@orcl>alter database mount;

Database altered.

SYS@orcl>alter database open;

Database altered.

7.　此时可以用rman 删除过期的备份文件与归档文件(本人的是测试机可以删除所有备份文件与归档文件，在生产库则要权衡要删除的文件）

RMAN> delete backupset;

RMAN> delete archivelog all;

8.　检查一下flashrecovery area的使用情况：

SYS@orcl>select * from v$flash_recovery_area_usage;

FILE_TYPE                                                    PERCENT_SPACE_USED
------------------------------------------------------------ ------------------
PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------- ---------------
CONTROL FILE                                                                  0
                        0               0

REDO LOG                                                                      0
                        0               0

ARCHIVED LOG                                                                  0
                        0               0


FILE_TYPE                                                    PERCENT_SPACE_USED
------------------------------------------------------------ ------------------
PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------- ---------------
BACKUP PIECE                                                                  0
                        0               0

IMAGE COPY                                                                    0
                        0               0

FLASHBACK LOG                                                                 0
                        0               0


FILE_TYPE                                                    PERCENT_SPACE_USED
------------------------------------------------------------ ------------------
PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------------------- ---------------
FOREIGN ARCHIVED LOG                                                          0
                        0               0