Shulou(Shulou.com)05/31 Report--

The purpose of this article is to share with you what to do about the ORA-19815 problem of the flash area storage space in Oracle. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Query the alert log of Oracle to find the ORA-19815 alarm log. The specific phenomena are as follows:

[phenomenon]

ORA-19815: WARNING: db_recovery_file_dest_size of 214748364800 bytes is 85.22% used, and has 31746169856 remaining bytes available

[analysis and processing ideas]

1. Query related parameters and views

(1) query the flash area storage space of db_recovery_file_dest_size and db_recovery_file_dest parameters to determine the location and size.

(2) query v$recovery_file_dest view to determine the usage of db_recovery_file_dest_size.

(3) query the v$flash_recovery_area_usage view to determine which files take up space.

2. The solution mainly considers the following three aspects:

(1) if the db_recovery_file_dest_size is small, modify the db_recovery_file_dest_size to a larger value.

(2) if the directory space specified by db_recovery_file_dest is insufficient, the solution:

A. you can specify another space to copy the files in the source db_recovery_file_dest to this space.

B. add disk space to the directory

C, back up the files in this directory space to other directories and compress them.

(3) delete invalid files through RMAN.

[actual operation]

1. View the parameters related to db_recovery_file

SQL > show parameter db_recovery NAME TYPE VALUE-- db_recovery_file_dest String / home/erp_db_rec/db_recovery_d estdb_recovery_file_dest_size big integer 200G [oraprod@erpdb erp_db_rec] $pwd/home/erp_db_ [oraprod @ erpdb erp_db_rec] $du-sh db_recovery_dest/17G db_recovery_dest/

You can judge from the parameters that the directory specified by db_recovery_file_dest is large enough.

2. Check the utilization of recovery file

SQL > set linesize 2000SQL > col name form a40SQL > col SPACE_LIMIT form 99999999999999SQL > col SPACE_USED form 99999999999999SQL > col NUMBER_OF_FILES form 9999SQL > 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

The query found that 85.21% had been used.

3. Query what kind of files take up space

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 0REDO LOG 0 0 0ARCHIVED LOG 0 0 0BACKUP PIECE 85.22 0 736IMAGE COPY 0 0 0FLASHBACK LOG 0 0 0FOREIGN ARCHIVED LOG 0 0 0AUXILIARY DATAFILE COPY 0 0 0

8 rows selected.

Through the query, we can know that BACKUP PIECE uses the space of recovery_file_dest statistics.

As can be seen from the above query: files in db_recovery_file_dest and backup space are deleted regularly, but at the database level, cleaning up causes the v$recovery_file_dest view to count deleted files, and it is judged that deleting invalid backup sets through RMAN can be solved.

4. The way to solve this problem

(1) Cross-check the 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=959468407crosschecked 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=959641208crosschecked 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=959641208crosschecked 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=959641208crosschecked 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=971478752crosschecked 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=971478754Crosschecked 1510 objects

(2) Delete invalid backup sets

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=968540425deleted backup piecebackup piece handle=/backup/rman_backup/20180219/level1backup/PROD_2217_1_59srlg89.20180219 RECID=2574 STAMP=968540425deleted backup piecebackup piece handle=/backup/rman_backup/20180219/level1backup/PROD_2219_1_5bsrlg89.20180219 RECID=2575 STAMP=968540426Deleted 1125 EXPIRED objects

(3) use v$recovery_file_dest to verify usage

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,

After deleting an invalid backup set, the space usage becomes 8.39%.

(4) query the usage of 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 0REDO LOG 0 0 0ARCHIVED LOG 0 0 0BACKUP PIECE 8 . 39 0 162IMAGE COPY0 0 0FLASHBACK LOG 0 0 0FOREIGN ARCHIVED LOG 0 0 0AUXILIARY DATAFILE COPY0 0 0

8 rows selected.

Solve the ORA-19815 problem completely.

Thank you for reading! This is the end of the article on "how to report ORA-19815 problems in the flash area of Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.