Oracle 数据库闪回功能设置出现ORA-19809和ORA-19804错误


--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备库上开通, 生产库上就不用开了.

相关内容