oracle闪回区存储空间耗尽解决方法（ORA-19815解决方法）

mahan

ORA-19815问题，是闪回区空间耗尽，解决的方法我使用增大闪回区的存储空间，来解决此问题
现在先查看一下v$recovery_file_dest视图，可以发现可以回收的空间为0，在查看闪回区的使用率

SQL> select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable, number_of_files 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
r /> Copyright © 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%