基于ORA-19815閃回空間爆滿問題的處理方法

更新時間：2017年12月15日 15:21:30 作者：yepkeepmoving

下面小編就為大家分享一篇基于ORA-19815閃回空間爆滿問題的處理方法，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧

閃回區(qū)爆滿問題也是經(jīng)常會遇到的問題，最關(guān)鍵的是閃回設(shè)置大小以及歸檔被默認存放在了閃回目錄，恰巧今天又遇到了這個問題，就記錄下處理步驟，僅供遇到這類問題的人參考。

一、錯誤現(xiàn)象描述

1）應(yīng)用端錯誤信息

Error: 2016-11-26 11:45:25 ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.
Error: 2016-11-26 11:45:25 ORA-00257: Archiver error. Connect AS SYSDBA only until resolved.
Error: 2016-11-26 11:45:25 init connpool:one or more conn open error.

2）數(shù)據(jù)庫端錯誤信息

Sat Nov 26 12:13:14 2016
Errors in file /home/U01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc2_929.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 42949672960 bytes is 100.00% used, and has 0 remaining bytes available.
Sat Nov 26 12:13:14 2016
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
  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.
************************************************************************
Sat Nov 26 12:13:14 2016
Errors in file /home/U01/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_arc2_929.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 524288000 bytes disk space from 42949672960 limit

二、錯誤分析

從應(yīng)用日志看，是由于不能歸檔導(dǎo)致的DB連接池不能被初始化打開，基本可以判斷是由于數(shù)據(jù)庫的歸檔文件所在磁盤空間滿導(dǎo)致。

從數(shù)據(jù)庫日志查看發(fā)現(xiàn)確實是由于閃回空間已經(jīng)爆滿，通過查看服務(wù)器磁盤空間以及閃回空間大小即可再次驗證。

三、詳細處理過程

1）登陸數(shù)據(jù)庫服務(wù)器，查看磁盤空間使用信息

[oracle@teststd trace]$ df -h
Filesystem   Size Used Avail Use% Mounted on
/dev/sda5    9.9G 2.6G 6.9G 28% /
tmpfs      32G  18M  32G  1% /dev/shm
/dev/sda1    388M  62M 307M 17% /boot
/dev/sda6    1.6T 506G 1017G 34% /home
/dev/sda2    20G 508M  19G  3% /var

2）查看數(shù)據(jù)庫錯誤日志，發(fā)現(xiàn)閃回空間爆滿

cd /home/U01/app/oracle/diag/rdbms/testdb/testdb/trace
tail -n 35 alert_testdb.log

ORA-19815: WARNING: db_recovery_file_dest_size of 42949672960 bytes is 100.00% used, and has 0 remaining bytes available.

3）登陸數(shù)據(jù)庫，查看閃回路徑以及閃回空間使用情況

查看閃回空間設(shè)置大小

SQL> show parameter recover;
 
NAME         TYPE    VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest   string   /home/U01/app/oracle/fast_recovery_area
db_recovery_file_dest_size      big integer 40G
db_unrecoverable_scn_tracking    boolean   TRUE
recovery_parallelism         integer   0

或者利用SQL查看閃回路徑

Col name for a60;
Set line 200;
SQL> select * from v$recovery_file_dest ;
 
NAME                  SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES   CON_ID
------------------------------------------------------- ----------- ---------- ----------------- --------------- ----------
/home/U01/app/oracle/fast_recovery_area         4.2950E+10 2.1538E+10         0       89     0

查看閃回空間所在磁盤大小

SQL> !df -h /home/U01/app/oracle/fast_recovery_area
Filesystem   Size Used Avail Use% Mounted on
/dev/sda6    1.6T 504G 1019G 34% /home

查看閃回目錄使用大小

SQL> !du -hs /home/U01/app/oracle/fast_recovery_area
40G /home/U01/app/oracle/fast_recovery_area

查看閃回空間使用情況

SQL> select * from V$RECOVERY_AREA_USAGE;
 
FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES   CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE               0             0        0     0
REDO LOG           7.45             0        7     0
ARCHIVED LOG             100             0       76     0
BACKUP PIECE               0             0        0     0
IMAGE COPY                0             0        0     0
FLASHBACK LOG              0             0        0     0
FOREIGN ARCHIVED LOG           0             0        0     0
AUXILIARY DATAFILE COPY         0             0        0     0

4）刪除歸檔、調(diào)整閃回區(qū)大小

從上面看確實是閃回空間滿了，而占用閃回區(qū)滿的罪魁禍首就是歸檔日志。要解決的方法有兩個：

方案1:刪除多余的歸檔

最佳刪除歸檔的途徑是通過rman工具做，如果直接刪除文件數(shù)據(jù)庫是識別不到閃回區(qū)釋放的。

[oracle@teststd trace]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sat Nov 26 13:00:28 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESTDB (DBID=2708971821)
RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;
RMAN> DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7' ;

或者利用下面的語句刪除七天前的歸檔日志

RMAN> DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-7' ;

注意：（一般刪除歸檔騰出足夠的閃回區(qū)后，建議調(diào)整歸檔路徑或者調(diào)整足夠閃回區(qū)大?。?/p>

方案2：調(diào)整閃回區(qū)大小

SQL> alter system set db_recovery_file_dest_size=100G scope=both;
alter system set db_recovery_file_dest_size=100G scope=both
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
###數(shù)據(jù)庫為12c，不允許在pdb下操作，需要切換到sys下操作
SQL> conn /as sysdba;
Connected.
SQL> alter system set db_recovery_file_dest_size=100G scope=both;
 
System altered.

5）查看閃回區(qū)使用情況和其大小

[oracle@teststd trace]$ du -hs /home/U01/app/oracle/fast_recovery_area
27G   /home/U01/app/oracle/fast_recovery_area
 
SQL> select * from V$RECOVERY_AREA_USAGE;
FILE_TYPE    PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES   CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE           0       0         0     0
REDO LOG          2.98        0         7     0
ARCHIVED LOG         17.08       0         82     0
BACKUP PIECE           0       0         0     0
IMAGE COPY            0       0         0     0
FLASHBACK LOG          0       0         0     0
FOREIGN ARCHIVED LOG       0       0         0     0
AUXILIARY DATAFILE COPY         0     0        0     0
 
8 rows selected.

6）通知系統(tǒng)管理員啟動應(yīng)用，正常啟動

至此問題得以徹底解決

以上這篇基于ORA-19815閃回空間爆滿問題的處理方法就是小編分享給大家的全部內(nèi)容了，希望能給大家一個參考，也希望大家多多支持腳本之家。