ORA-19804: cannot reclaim 48156160 bytes disk space from 403

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

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,发现如下错误：

 client_id='' type='UNKNOWN' level='16'
 host_id='WO' host_addr='fe80::780d:d5a2:b794:f5cf%18' module='oradim.exe'
 pid='3628'>
 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'
 

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