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

是ORA-19815问题，是闪回区空间耗尽，解决的方法我使用增大闪回区的存储空间，来解决此问题

现在先查看一下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

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%，在查看一下告警日志

db_recovery_file_dest_size of 10240MB 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错误了，现在闪回区空间使用爆满问题已经解决