oracle 归档日志满处理



网站无法访问，tomcat重启无效，检查错误日志error.log发现如下错误：
sed by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (ORA-00257: archiver error. Connect internal only, until freed.
)
 at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
 at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
 at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
 at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
 ... 57 more
Caused by: java.sql.SQLException: ORA-00257: archiver error. Connect internal only, until freed.

原来是归档日志满导致的

SQL> show parameter recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\app\Administrator\flash_rec
                                                 overy_area
db_recovery_file_dest_size           big integer 30G
recovery_parallelism                 integer     0

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
NUMBER_OF_FILES
---------------
CONTROL FILE                          0                         0
              0

REDO LOG                              0                         0
              0

ARCHIVED LOG                      99.96                         0
            829

果然空间都99.96%了。使用rman 把3天前的归档日志全部删除掉，网站正常。

rman>crosscheck archivelog all

rman> delete expired archivelog all;

rman>delete archivelog until time 'sysdate-3' ;

后来干脆把db_recovery_file_dest_size 扩大

SQL> alter system set db_recovery_file_dest_size=40G scope=spfile;