Oracle闪回区爆满问题也是经常会遇到的问题,最关键的是闪回设置大小以及归档被默认存放在了闪回目录,恰巧今天又遇到了这个问题,就记录下处理步骤,仅供遇到这类问题的人参考。
一、 错误现象描述
1)应用端错误信息
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)数据库端错误信息
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
二、 错误分析
从应用日志看,是由于不能归档导致的DB连接池不能被初始化打开,基本可以判断是由于数据库的归档文件所在磁盘空间满导致。
从数据库日志查看发现确实是由于闪回空间已经爆满,通过查看服务器磁盘空间以及闪回空间大小即可再次验证。
三、 详细处理过程
1)登陆数据库服务器,查看磁盘空间使用信息
[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)查看数据库错误日志,发现闪回空间爆满
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)登陆数据库,查看闪回路径以及闪回空间使用情况
查看闪回空间设置大小
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
----------------------- ------------------ ------------------------- --------------- ----------