本文是关于如何处理Oracle中闪存区域存储空间报告中ORA-19815的问题。我觉得边肖很实用，就和大家分享一下作为参考。让我们跟着边肖看一看。
查询Oracle的预警日志就是找到ORA-19815预警日志，具体现象如下：
[现象]
Ora-198153360警告3360 db _ recovery _ file _ dest _ sizeof 214748364800字节已使用85.22%，剩余31746169856字节可保存。
1.查询相关参数和视图。
(1)查询db_recovery_file_dest_size和db_recovery_file_dest参数闪存区存储空间，确定位置和大小。
(2)查询v$recovery_file_dest视图，确定db_recovery_file_dest_size的使用情况。
(3)查询v$flash_recovery_area_usage视图，确定哪个文件占用空间。
2.解决方案主要考虑以下三个方面：
(1)如果db_recovery_file_dest_size较小，请将db_recovery_file_dest_size修改为较大的值。
(2)如果db_recovery_file_dest指定的目录空间不足，解决方案：
a、可以指定另一个空间将源db_recovery_file_dest中的文件复制到这个空间；
b、为目录增加磁盘空间；
c、将这个目录空间中的文件备份到其他目录中并压缩。
(3)通过RMAN删除无效文件。
[实际操作]
1.检查db_recovery_file的相关参数。
SQLshowparameterdb_recovery
NAMETYPEVALUE
-
db _ recovery _ file _ dest string/home/ERP _ db _ rec/db _ recovery _ d
自我提高训练法
db _ recovery _ file _ dest _ sizebigniterger 200g[ora prod @ erpdberp _ db _ rec]$ pwd
/home/erp_db_rec
[ora prod @ erpdberp _ db _ rec]$ du-shdb _ recovery _ dest/
1 gdb _ recovery _ dest/根据参数可以判断db_recovery_file_dest指定的目录足够大。
2.检查恢复文件的利用率
lass="brush:sql;toolbar:false">SQL> set linesize 2000
SQL> col name form a40
SQL> col SPACE_LIMIT form 99999999999999
SQL> col SPACE_USED form 99999999999999
SQL> col NUMBER_OF_FILES form 9999
SQL> select rfd.NAME,rfd.SPACE_LIMIT,rfd.SPACE_USED,rfd.SPACE_USED/rfd.SPACE_LIMIT*100 as "Rate(%)",rfd.NUMBER_OF_FILES from v$recovery_file_dest rfd;
NAME SPACE_LIMIT SPACE_USED Rate(%) NUMBER_OF_FILES
---------------------------------------- --------------- --------------- ---------- ---------------
/home/erp_db_rec/db_recovery_dest 214748364800 183002194944 85.2170377 736
查询发现已经使用了85.21%了。
3、查询是哪类文件占用空间
SQL> select file_type, percent_space_used as used, percent_space_reclaimable as reclaimable, number_of_files as "number" from v$flash_recovery_area_usage; FILE_TYPE USED RECLAIMABLE number ----------------------- ---------- ----------- ---------- CONTROL FILE 0 0 0 REDO LOG 0 0 0 ARCHIVED LOG 0 0 0 BACKUP PIECE 85.22 0 736 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 AUXILIARY DATAFILE COPY 0 0 0
8 rows selected.
通过查询可知是BACKUP PIECE使用了recovery_file_dest统计的空间。
由上述查询可知：对db_recovery_file_dest和备份空间中的文件定期进行删除，但是在数据库层面为进行清理，导致v$recovery_file_dest视图将已删除的文件统计在内导致，判断通过RMAN删除无效的备份集可解决。
4、解决此问题的方法
(1)对backupset进行交叉检查。
RMAN> crosscheck backupset; using channel ORA_DISK_1 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/erp_db_rec/db_recovery_dest/PROD/backupset/2017_11_07/o1_mf_annnn_TAG20171107T230006_f03lmq9w_.bkp RECID=1405 STAMP=959468407 crosschecked backup piece: found to be 'EXPIRED' ...... ...... backup piece handle=/home/erp_db_rec/db_recovery_dest/PROD/backupset/2017_11_09/o1_mf_annnn_TAG20171109T230007_f08vcrc8_.bkp RECID=1420 STAMP=959641208 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/erp_db_rec/db_recovery_dest/PROD/backupset/2017_11_09/o1_mf_annnn_TAG20171109T230007_f08vcrf1_.bkp RECID=1421 STAMP=959641208 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/erp_db_rec/db_recovery_dest/PROD/backupset/2017_11_09/o1_mf_annnn_TAG20171109T230007_f08vcrhd_.bkp RECID=1422 STAMP=959641208 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/home/erp_db_rec/db_recovery_dest/PROD/backupset/2017_11_09/o1_mf_annnn_TAG20171109T230932_f08vxdxq_.bkp RECID=1426 STAMP=959641772 ....... backup piece handle=/home/erp_db_rec/db_recovery_dest/PROD/backupset/2018_03_22/o1_mf_annnn_TAG20180322T231231_fc7kz019_.bkp RECID=2963 STAMP=971478752 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/home/erp_db_rec/db_recovery_dest/PROD/autobackup/2018_03_22/o1_mf_s_971478753_fc7kz217_.bkp RECID=2964 STAMP=971478754 Crosschecked 1510 objects
（2）删除无效的备份集
RMAN> delete noprompt expired backupset; using channel ORA_DISK_1 ...... backup piece handle=/backup/rman_backup/20180219/level1backup/PROD_2218_1_5asrlg89.20180219 RECID=2573 STAMP=968540425 deleted backup piece backup piece handle=/backup/rman_backup/20180219/level1backup/PROD_2217_1_59srlg89.20180219 RECID=2574 STAMP=968540425 deleted backup piece backup piece handle=/backup/rman_backup/20180219/level1backup/PROD_2219_1_5bsrlg89.20180219 RECID=2575 STAMP=968540426 Deleted 1125 EXPIRED objects
（3）使用v$recovery_file_dest验证使用率
SQL> select rfd.NAME,rfd.SPACE_LIMIT,rfd.SPACE_USED,rfd.SPACE_USED/rfd.SPACE_LIMIT*100 as "Rate(%)",rfd.NUMBER_OF_FILES from v$recovery_file_dest rfd; NAME SPACE_LIMIT SPACE_USED Rate(%) NUMBER_OF_FILES ---------------------------------------- --------------- --------------- ---------- --------------- /home/erp_db_rec/db_recovery_dest 214748364800 18025081856 8.39358282 162
删除无效的备份集后，空间使用率变成8.39%。
（4）查询BACKUP PIECE 的使用情况
SQL> select file_type, 2 percent_space_used as used, 3 percent_space_reclaimable as reclaimable, 4 number_of_files as "number" 5 from v$flash_recovery_area_usage; FILE_TYPE USED RECLAIMABLE number ----------------------- ---------- ----------- ---------- CONTROL FILE 0 0 0 REDO LOG 0 0 0 ARCHIVED LOG 0 0 0 BACKUP PIECE 8.39 0 162 IMAGE COPY 0 0 0 FLASHBACK LOG 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 AUXILIARY DATAFILE COPY0 0 0
8 rows selected.
完整解决ORA-19815问题。
感谢各位的阅读！关于“Oracle中闪回区存储空间报ORA-19815问题怎么办”这篇文章就分享到这里了，希望
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/81852.html