Oracle 错误“ORA-19809: limit exceeded for recovery files”处理办法

当运行archive命令的时候，数据库报错：

SQL> alter system archive log current;
alter system archive log current
*
ERROR at line 1:
ORA-16038: log 1 sequence# 1 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: '/u01/livedata/live/redo01.log'

这个错误的原因是recovery文件的大小超出限制了，解决方法有两个，一个是删除旧有的文件，另一个是扩大空间限制

1. 删除旧文件

这种方式是进入RMAN，把归档文件删掉一部分（或者全删）

$ rman

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Aug 12 10:35:46 2013

RMAN> connect target sys/<password>
RMAN> delete archivelog all;

或者是
[$ rman target /]
[RMAN>DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-2';]

RMAN> crosscheck archivelog all;

2. 扩大空间限制

首先找到archive文件的位置

$ sqlplus / as sysdba

SQL> show parameter background_dump_dest

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 572
Next log sequence to archive 572
Current log sequence 580

可以看到归档日志的位置是DB_RECOVERY_FILE_DEST，精确路径也可以查到

SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata2/flash_recovery_area
db_recovery_file_dest_size big integer 10G

查看空间使用情况

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/u01/testlog
 4322230272  3996808192                 0              28

然后就可以增加db_recovery_file_dest_size

SQL> alter system set db_recovery_file_dest_size=20G;
System altered.

SQL> alter database open;
Database altered.