--alert.log出现很多如下警告日志:
Errors in file /u01/app/Oracle/admin/ESUITE/bdump/esuite_rvwr_23296.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 18055168 bytes disk space from 85899345920 limit
--查看dba_outstanding_alerts视图(oracle10g新增的):
SQL> col REASON format a40
col OBJECT_TYPE format a20
col CREATION_TIME format a35
col TIME_SUGGESTED format a35
col SUGGESTED_ACTION format a75
SQL> select object_type,message_type,reason,creation_time,time_suggested,suggested_action from dba_outstanding_alerts;
OBJECT_TYPE MESSAGE_TYPE REASON CREATION_TIME TIME_SUGGESTED SUGGESTED_ACTION
-------------------- ------------ ---------------------------------------- ----------------------------------- ----------------------------------- ---------------------------------------------------------------------------
RECOVERY AREA Warning db_recovery_file_dest_size of 8589934592 14-AUG-09 12.50.24.570360 PM +08:00 23-AUG-09 07.20.43.396224 AM +08:00 You have following choices to free up space from flash recovery area:
0 bytes is 97.01% used and has 257187020 1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
8 remaining bytes available. 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.
--查看v$flash_recovery_area_usage视图, 看看闪回区是存放了哪些文件, 以下输出说明数据库文件未存放在闪回区,
--仅仅存放FLASHBACKLOG用于闪回数据库用. v$database.flashback_on='ON'表明数据库开通了闪回功能.
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG 0 0 0
BACKUPPIECE 0 0 0
IMAGECOPY 0 0 0
FLASHBACKLOG 99.96 74.12 1009
6 rows selected.
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
--查看闪回区相关设置, db_flashback_retention_target=1440(单位是分钟)表明可闪回1天的数据库数据.
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u04/flash_recovery_area
db_recovery_file_dest_size big integer 80G
SQL> show parameter flash
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
--flashback听上去很美其实是块鸡肋. 其实单独闪回数据库没多大作用, 我们把闪回功能取消,
--数据库需在mount状态才能启停闪回功能
SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE FLASHBACK off;
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> ALTER DATABASE open;
--如果真要有闪回功能, 可以考虑在dataguard备库上开通, 生产库上就不用开了.