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 2G

idle> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------------- --------------------------- ---------------------------------- ---------------
CONTROLFILE                   0                         0               0
ONLINELOG                     0                         0               0
ARCHIVELOG                 99.6                         0              43
BACKUPPIECE                   0                         0               0
IMAGECOPY                     0                         0               0
FLASHBACKLOG                .38                       .19               2

6 rows selected.

idle> alter system set db_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空间的大小。