oracle ora 16014,ORA-16014错误一例

[oracle@centos ~]$ sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 2 17:19:06 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

idle> startup;

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1267068 bytes

Variable Size             146803332 bytes

Database Buffers          134217728 bytes

Redo Buffers                2924544 bytes

Database mounted.

ORA-16014: log 1 sequence# 79 not archived,no available destinations

ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/orcl/redo01.log'

idle> select status from v$instance;

STATUS

------------

MOUNTED

idle> show parameter db_recovery_file_dest;

NAME_COL_PLUS_SHOW_PARAM   TYPE        VALUE_COL_PLUS_SHOW_PARAM

-------------------------- ----------- ------------------------------------

db_recovery_file_dest      string      /u01/app/oracle/flash_recovery_area

db_recovery_file_dest_size big integer 2Gidle> select * fromv$flash_recovery_area_usage;

FILE_TYPEPERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

---------------------------------------------------------------------------------------------------

CONTROLFILE                   0                         0               0

ONLINELOG                     0                         0               0

ARCHIVELOG                 99.60              43

BACKUPPIECE                   0                         0               0

IMAGECOPY                     0                         0               0

FLASHBACKLOG                .38.19               2

6 rows selected.

idle>alter system setdb_recovery_file_dest_size=3g scope=both;

System altered.

idle> alter database open;

Database altered.

idle> select status from v$instance;

STATUS

------------

OPEN

idle> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@centos ~]$ sqlplus / as sysdba;

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 2 18:02:39 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

sys@ORCL> select status from v$instance;

STATUS

------------

OPEN

ORA-16014错误的主要原因是db_recovery_file_dest 目录(flash recovery area)的空间不够了。本例中为2G，绝大部分都被 redo log 占据。

前段时间进行了一些性能测试，创建了一个4000000行的表，所以导致redo log量很大。

解决方法当然是增加flash recovery area空间的大小。