SQL> ALTER SYSTEM SET db_recovery_file_dest_size=2g SCOPE=BOTH;
System altered.
SQL>
SQL> ALTER SYSTEM SET db_recovery_file_dest=
'/u01/app/oracle/flash_recovery_area'
2 SCOPE=BOTH;
System altered.
SQL>
之后查看所作的修改是否生效:
SQL> SHOW parameter db_recovery_file_dest
NAME TYPE VALUE
----------------------- --------- ---------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
SQL>
SQL> DESC dba_outstanding_alerts
Name Null? Type
------------------------------- ----------------------------
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(60)
ERROR_INSTANCE_ID VARCHAR2(142)
SQL>
10g 的新视图 V$RECOVERY_FILE_DEST 包含闪回恢复区的相关信息:
SQL> desc V$RECOVERY_FILE_DEST
Name Null? Type
------------------------------- ----------------------------
NAME VARCHAR2(513)
SPACE_LIMIT NUMBER
SPACE_USED NUMBER
SPACE_RECLAIMABLE NUMBER
NUMBER_OF_FILES NUMBER
SQL> SELECT NAME, space_limit, space_used, space_reclaimable space_rec,
2 number_of_files file_num
3 FROM v$recovery_file_dest;
NAME SPACE_LIMIT SPACE_USED SPACE_REC FILE_NUM
--------------------------- ----------- ---------- ---------- ----------
/u01/app/oracle/flash_recovery_area 2147483648 1106849280 602410496 30
SQL>
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.
1 rows selected.
SQL>