这个是一个真实的oracle（ORA-19815解决方法）的案例，希望对大家有帮助。
今天朋友公司的平台出现了登陆缓慢、查询数据慢问题，并且通过spotlight监控oracle也出现登陆不成功现象，通过查看系统的内存、进程等，没有发现问题，最后找到了我，我先查看了一下平台的内存、进程，也没有发现问题，最后查看oracle的告警日志，发现问题如下：
- ARC0: Failed to archive thread 1 sequence 53 (19809)
- Sun Jun 10 23:12:12 2012
- Errors in file /home/oracle/admin/BGTP/bdump/bgtp_arc1_3906.trc:
- ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 100.00% used, and has 0 remaining bytes available.
- Sun Jun 10 23:12:12 2012
- ************************************************************************
- You have following choices to free up space from flash 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.
- ************************************************************************
是ORA-19815问题，通过metalink查询，是闪回区空间耗尽，解决的方法我使用增大闪回区的存储空间，来解决此问题
现在先查看一下v$recovery_file_dest试图
可以发现可以回收的空间为0，在查看闪回区的使用率
- 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
- ------------ ---------- ----------- ----------
- CONTROLFILE 0 0 0
- ONLINELOG 0 0 0
- ARCHIVELOG 98.65 0 51
- BACKUPPIECE 0 0 0
- IMAGECOPY 0 0 0
- FLASHBACKLOG 0 0 0
- 6 rows selected.
发现已经使用了98.65%了
下面为解决此问题的方法
登陆数据库
- [oracle@master ~]$ sqlplus / as sysdba
- SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jun 10 22:41:54 2012
- Copyright (c) 1982, 2005, Oracle. All rights reserved.
- Connected to:
- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
- With the Partitioning, OLAP and Data Mining options
先查看当前的闪回区大小
- SQL> show parameter db_recovery_file_dest
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- db_recovery_file_dest string /home/oracle/flash_recovery_ar
- ea
- db_recovery_file_dest_size big integer 2G
- SQL> archive log list;
- Database log mode Archive Mode
- Automatic archival Enabled
- Archive destination USE_DB_RECOVERY_FILE_DEST
- Oldest online log sequence 53
- Next log sequence to archive 53
- Current log sequence 55
可以看得闪回区的大小为2g，所以我把他扩展为10g
- SQL> alter system set db_recovery_file_dest_size=10G scope=both;
- System altered.
然后在查看闪回区的使用情况
- SQL> select * from v$recovery_file_dest;
- NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
- ---------------------------------------- ------------ ---------- ----------------- ---------------
- /home/oracle/flash_recovery_area 10737418240 2383963648 0 57
- SQL> show parameter db_recovery_file_dest
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- db_recovery_file_dest string /home/oracle/flash_recovery_ar
- ea
- db_recovery_file_dest_size big integer 10G
在查看一下闪回区的使用率
- 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
- ------------ ---------- ----------- ----------
- CONTROLFILE 0 0 0
- ONLINELOG 0 0 0
- ARCHIVELOG 22.2 0 57
- BACKUPPIECE 0 0 0
- IMAGECOPY 0 0 0
- FLASHBACKLOG 0 0 0
- 6 rows selected.
从之前的98.65%降到了22.2%，在查看一下告警日志
- db_recovery_file_dest_size of 10240 MB is 20.14% used. This is a
- user-specified limit on the amount of space that will be used by this
- database for recovery-related files, and does not reflect the amount of
- space available in the underlying filesystem or ASM diskgroup.
- Sun Jun 10 23:12:12 2012
- ALTER SYSTEM SET db_recovery_file_dest_size='10G' SCOPE=BOTH;
- Sun Jun 10 23:12:15 2012
- Archiver process freed from errors. No longer stopped
- Sun Jun 10 23:12:15 2012
- Thread 1 advanced to log sequence 56
- Current log# 1 seq# 56 mem# 0: /home/oracle/oradata/BGTP/redo01.log
- Thread 1 advanced to log sequence 57
- Current log# 2 seq# 57 mem# 0: /home/oracle/oradata/BGTP/redo02.log
- Sun Jun 10 23:12:27 2012
- Thread 1 cannot allocate new log, sequence 58
- Checkpoint not complete
- Current log# 2 seq# 57 mem# 0: /home/oracle/oradata/BGTP/redo02.log
- Thread 1 advanced to log sequence 58
- Current log# 3 seq# 58 mem# 0: /home/oracle/oradata/BGTP/redo03.log
- Sun Jun 10 23:12:52 2012
- Thread 1 advanced to log sequence 59
- Current log# 1 seq# 59 mem# 0: /home/oracle/oradata/BGTP/redo01.log
- Sun Jun 10 23:12:52 2012
- Trying to expand controlfile section 11 for Oracle Managed Files
- Expanded controlfile section 11 from 56 to 112 records
- Requested to grow by 56 records; added 2 blocks of records
已经不在重复的报ORA-19815错误了，现在闪回区空间使用爆满问题已经解决。
然后我的spotlight监控也已经可以监控oracle了，下面是spotlight监控oracle的图
解决ORA-19815的方法很多，我这个只是其中的一种，感谢oracle 的metalink
本文转自 reinxu 51CTO博客，原文链接：http://blog.51cto.com/dl528888/895168，如需转载请自行联系原作者
