请教ORA-19815的问题

深圳－刀

环境：Windows 2008 + Oracle 10.2.0.4
ALERT中报：
ORA-19815: WARNING: db_recovery_file_dest_size of 322122547200 bytes is 99.91% used, and has 300687360 remaining bytes available.

问题1：
SPACE_RECLAIMABLE有135G，既然这部分空间是可以被回收利用的，是不是对于这个19815可以忽略，不需要做处理？
由于db_recovery_file_dest_size不能再扩大，因此我将db_flashback_retention_target减少了，减小后SPACE_RECLAIMABLE马上增大到200G。

问题2：
通过查询V$FLASH_RECOVERY_AREA_USAGE发现空间是被FLASHBACKLOG占用，如何通过RMAN备份和释放这个FLASHBACKLOG？BACK RECOVERY AREA是否必须备份到TAPE？ 
 

先谢谢了！

通过DROPBOX分享的图，似乎显示不正常，我用附件传到后面。

深圳－刀

刚用DROPBOX，顺便试试sharing功能而已，呵呵。

多谢解答。

Maclean Liu(刘相兵

1. 这种问题 直接贴 查询和结果就可以了 如果格式有问题请用code模式， 贴图无意义


2.


SQL> desc V$FLASH_RECOVERY_AREA_USAGE
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
FILE_TYPE                                          VARCHAR2(12)
PERCENT_SPACE_USED                                 NUMBER
PERCENT_SPACE_RECLAIMABLE                          NUMBER
NUMBER_OF_FILES                                    NUMBER


ODM FINDING 10.2.0.4上存在bug 可能引起该  ORA-19815



[oracle@vrh8 ~]$ oerr ora 19815
19815, 00000, "WARNING: %s of %s bytes is %s%% used, and has %s remaining bytes available."
// *Cause: DB_RECOVERY_FILE_DEST is running out of disk space.
// *Action: One of the following:
//          1. Add disk space and increase DB_RECOVERY_FILE_DEST_SIZE.
//          2. Backup files to tertiary device using RMAN.
//          3. Consider changing RMAN retention policy.
//          4. Consider changing RMAN archivelog deletion policy.
//          5. Delete files from recovery area using RMAN.


Hdr: 8260833 10.2.0.4.0 RDBMS 10.2.0.4.0 FLASHBACK_DB PRODID-5 PORTID-212 ORA-19815
Abstract: ORA-19815 REPORTED BY RWVR PROCESS



PROBLEM:
--------
The   FLASHBACK was  enabled  in their Standby DB.  The alert file  is
reporting the following warning many times by the RWVR process :

ORA-19815: WARNING: db_recovery_file_dest_size of 26214400 bytes is 93.75%
used,and has 1638400 remaining bytes available.

DIAGNOSTIC ANALYSIS:
--------------------
-  The flashback log files are the only files created in the FRA.

-  The customer  set up FRA sufficient enough based on the # of peak archive
logs generated for given retention (FRA=8G for 6 hours retention).

-  The customer  see warning in alert log that FRA used is above 85% used.

-  They changed FRA to 10G , after a while they still the same warning in
alert.log. But at this time used space by flashback log shows as 91.67 % and
reclaimanble space shows 91.19 %.

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              91.67                     91.19            1614

4. They again changed the FRA to 16GB. And they  still see this warning after
12-24 hours.
5. They  reduced retention from 6 hours to 2 hours and the warning still
shows up.
6. They reduced retention from 2 hours to 90 minutes the warning still shows
up.



FOR QUESTION 2:

Make the db_recovery_file_size to 55GB, same as the actual usage to make oracle delete older files.
SQL> alter system set db_recovery_file_dest_size=55G scope=memory;
System altered.
You will see following messages in the alert log

ALTER SYSTEM SET db_recovery_file_dest_size=’55G’ SCOPE=MEMORY;
Mon Jul 27 13:27:45 2009
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngo02h_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngookw_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngpd5j_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngq3l6_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngqw4j_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngrpfp_.flb
Deleted Oracle managed file /u01/flash_recovery_area/PRODDB/flashback/o1_mf_4zngsjpb_.flb