ORA-19809: 超出了恢复文件数的限制
os:win7databse version :11.2.0.1
情况描述：
今天本机折腾的时候，FRA满了，日志无法归档，先增加了FRA size 之前为4G 增加为5G。依然无法回收空间。然后就手动删除了FRA里面的归档日志，备份集。然后crosscheck backupset， crosscheck archivelog all 。delete expired backupset delete expired archive log all； 日志依然无法归档！ 无奈 更改alter system set log_archivedest_1='LOCATION=D:/DUMP';完成归档。
但是现在仍然无法在FRA备份，查看alert.log 和相关参数设置如下：
参数：
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\app\admin\flash_recovery_area
db_recovery_file_dest_size big integer 5196
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=D:/dump
SQL> spool off;
SQL> select file_type,percent_space_used from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED
-------------------- ------------------
CONTROL FILE 0
REDO LOG 0
ARCHIVED LOG 0
BACKUP PIECE 0
IMAGE COPY 0
FLASHBACK LOG 0
FOREIGN ARCHIVED LOG 0
已选择7行。
alert.log
Errors in file d:\app\admin\diag\rdbms\orcl\orcl\trace\orcl_ora_4152.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 5196 字节) 已使用 100.00%, 尚有 0 字节可用。
************************************************************************
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.
************************************************************************
Thu Dec 05 21:39:18 2013
db_recovery_file_dest_size of 0 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Thu Dec 05 21:48:47 2013
Errors in file d:\app\admin\diag\rdbms\orcl\orcl\trace\orcl_ora_3816.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 5196 字节) 已使用 100.00%, 尚有 0 字节可用。
************************************************************************
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.
************************************************************************
orcl_ora_3816.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 5196 字节) 已使用 100.00%, 尚有 0 字节可用。
************************************************************************
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.
************************************************************************
疑惑：我已经把FRA 增大，甚至都清空了FRA，
SQL> select file_type,percent_space_used from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED
-------------------- ------------------
CONTROL FILE 0
REDO LOG 0
ARCHIVED LOG 0
BACKUP PIECE 0
IMAGE COPY 0
FLASHBACK LOG 0
FOREIGN ARCHIVED LOG 0
显示都没有占用，为什么还会出现这样的情况呢？
补充：清空FRA之前，备份，归档的保留策略都是默认值 db_recovery_file_dest_size big integer 5196 这是字节啊，哪里的5G啊。 这才是G的节奏：
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------
db_recovery_file_dest string E:\flash_recovery_area
db_recovery_file_dest_size big integer 3852M csp12345 发表于 2013-12-6 09:56 static/image/common/back.gif
这才是G的节奏：
SQL> show parameter db_recovery_file_dest
啊，骚瑞，手残 眼残了。太不仔细了
页:
[1]