oracle 闪回恢复区的空间管理
闪回恢复区中添加或删除文件等变化都将记录在数据库的?alert?日志中，Oracle 10g?也针对该新特性提供了一个新的视图，?DBA_OUTSTANDING_ALERTS，通过该视图可以得到相关的信息。
?
SQL>??DESC dba_outstanding_alerts
?名称??????????????????????????????????????是否为空??类型
?----------------------------------------- -------- ----------------------------
?
?SEQUENCE_ID????????????????????????????????????????NUMBER
?REASON_ID?????????????????????????????????NOT NULL NUMBER
?OWNER??????????????????????????????????????????????VARCHAR2(30)
?OBJECT_NAME????????????????????????????????????????VARCHAR2(513)
?SUBOBJECT_NAME?????????????????????????????????????VARCHAR2(30)
?OBJECT_TYPE????????????????????????????????????????VARCHAR2(64)
?REASON?????????????????????????????????????????????VARCHAR2(4000)
?TIME_SUGGESTED?????????????????????????????????????TIMESTAMP(6) WITH TIME ZONE
?CREATION_TIME??????????????????????????????????????TIMESTAMP(6) WITH TIME ZONE
?SUGGESTED_ACTION???????????????????????????????????VARCHAR2(4000)
?ADVISOR_NAME???????????????????????????????????????VARCHAR2(30)
?METRIC_VALUE???????????????????????????????????????NUMBER
?MESSAGE_TYPE???????????????????????????????????????VARCHAR2(12)
?MESSAGE_GROUP??????????????????????????????????????VARCHAR2(64)
?MESSAGE_LEVEL??????????????????????????????????????NUMBER
?HOSTING_CLIENT_ID??????????????????????????????????VARCHAR2(64)
?MODULE_ID??????????????????????????????????????????VARCHAR2(64)
?PROCESS_ID?????????????????????????????????????????VARCHAR2(128)
?HOST_ID????????????????????????????????????????????VARCHAR2(256)
?HOST_NW_ADDR???????????????????????????????????????VARCHAR2(256)
?INSTANCE_NAME??????????????????????????????????????VARCHAR2(16)
?INSTANCE_NUMBER????????????????????????????????????NUMBER
?USER_ID????????????????????????????????????????????VARCHAR2(30)
?EXECUTION_CONTEXT_ID???????????????????????????????VARCHAR2(128)
?ERROR_INSTANCE_ID??????????????????????????????????VARCHAR2(142)
?
?
在闪回恢复区中的空间使用超过?85%?的时候，数据库将会向?alert?文件中写入告警信息。而当超过?97%?的时候将会写入严重告警信息。当闪回恢复区空间不够的时候，Oracle将报告如下类似的错误：
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 1258291200 limit
?
这个时候查询?dba_outstanding_alerts：
SQL> select reason,object_type,suggested_action from dba_outstanding_alerts;
?
REASON?????????????????????????OBJECT_TYPE??????????SUGGESTED_ACTION
------------------------------ -------------------- ----------------------------------------
db_recovery_file_dest_size of??RECOVERY AREA????????Add disk space and increase db_recovery_
1258291200 bytes is 88.20% use??????????????????????file_dest_size, backup files to tertiary
d and has 148509184 remaining????????????????????????device, delete files from recovery area
bytes available.?????????????????????????????????????using RMAN, consider changing RMAN rete
????????????????????????????????????????????????????ntion policy or consider changing RMAN a
????????????????????????????????????????????????????rchivelog deletion policy.
?
?
同时，oracle在alert中还会给出解决该问题的建议
************************************************************************
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 RMANB ACKUP 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.
************************************************************************
?
V$RECOVERY_FILE_DEST视图?包含闪回恢复区的相关信息：
SQL> desc V$RECOVERY_FILE_DEST
?名称??????????????????????????????????????是否为空??类型
?----------------------------------------- -------- ---------------------
?
?NAME???????????????????????????????????????????????VARCHAR2(513)
?SPACE_LIMIT????????????????????????????????????????NUMBER
?SPACE_USED?????????????????????????????????????????NUMBER
?SPACE_RECLAIMABLE??????????????????????????????????NUMBER
?NUMBER_OF_FILES????????????????????????????????????NUMBER
?
SQL> select * from v$recovery_file_dest;
NAME??SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
---------- ----------- ---------- ----------------- ---------------
????4039114752??????????0?????????????????0???????????????0
?
通过查询视图v$flash_recovery_area_usage，可以获得当前闪回恢复区的空间使用情况，并且可以知道是哪些文件占中了空间，据此可以做出相应的处理，或者加大闪回恢复区，或者移走相应的文件。
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE?????PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
CONTROL FILE???????????0?????????????????????????0
REDO LOG???????????????0?????????????????????????0
ARCHIVED LOG??????????0?????????????????????????0
BACKUP PIECE???????????0?????????????????????????0
IMAGE COPY?????????????0?????????????????????????0
FLASHBACK LOG????????0?????????????????????????0
FOREIGN ARCHIVED LOG?????0?????????????????????????0
?
如果闪回恢复区空间耗尽，且归档路径设置到了闪回恢复区中，则由于日志无法归档，数据库会hang住。所以，对于生产库，如果将归档放到闪回恢复区中，需要密切关注闪回恢复区的空间使用情况，否则一旦闪回恢复区的空间用尽，将导致数据库无法提供服务。
?
