Oracle报错： java.sql.SQLException:

现象： kmb2b日志提示Oracle报错，归档程序错误。在释放之前仅限于内部连接

2018-09-30 10:08:51.608 [Druid-ConnectionPool-Create-2056893174] ERROR com.alibaba.druid.pool.DruidDataSource - create connection error, url: jdbc:oracle:thin:@172.20.28.26:1521:kmb2b, errorCode 257, state 64000
java.sql.SQLException: ORA-00257: 归档程序错误。在释放之前仅限于内部连接
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) ~[ojdbc-1.4.jar:Oracle JDBC Driver version - "10.2.0.1.0"]
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) ~[ojdbc-1.4.jar:Oracle JDBC Driver version - "10.2.0.1.0"]

• PLSQL工具连接也是提示：归档程序错误

排查

• 查看alter_log: tail -n 100 -f /u01/app/oracle/rdbms/diag/rdbms/kmb2b/kmb2b/trace/alert_kmb2b.log
  ○： 提示闪存空间占满

***********************************************************************
ARC0: Error 19809 Creating archive log file to '/u01/app/oracle/rdbms/archivelog/KMB2B/archivelog/2018_09_30/o1_mf_1_4582_%u_.arc'
Errors in file /u01/app/oracle/rdbms/diag/rdbms/kmb2b/kmb2b/trace/kmb2b_arc1_13613.trc:
ORA-19815: 警告: db_recovery_file_dest_size 字节 (共 107374182400 字节) 已使用 100.00%, 尚有 0 字节可用。
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************

• 查看目前闪存空间

SQL> show parameter db_recovery_file_dest_size;
NAME                            TYPE         VALUE
------------------------------  -----------  --------
db_recovery_file_dest_size      big integer  100G

解决

• 加大闪存

SQL> alter system set db_recovery_file_dest_size=200G;

• 删除过期的归档日志， 需要使用rman删除
  ○： crosscheck archivelog all; #此命令的含义是检查所有归档日志的状态
  ○： rman target /
  ○： delete archivelog all;
• 查看一下归档日志的空间大小
  ○： select * from v$recovery_file_dest;

SQL> select * from v$recovery_file_dest;
NAME                              SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------------------------- ----------- ---------- ----------------- ---------------
/u01/app/oracle/rdbms/archivelog/ 2.1475E+11  1.0749E+11 0                 2899

• 接着看一下log日志的状态
  ○： select * from v$log;

SQL> set linesize 200;
SQL> select * from v$log;
GROUP#  THREAD#   SEQUENCE#    BYTES    BLOCKSIZE MEMBERS    ARC  STATUS    FIRST_CHANGE#  FIRST_TIME           NEXT_CHANGE#  NEXT_TIME
------ --------- ----------- --------- ---------- -------    ---  --------  -------------  -------------------  ------------  ----------------
   1       1        4585     52428800     512        1       NO   CURRENT   115790946      2018-09-30 10:57:29  2.8147E+14
   2       1        4584     52428800     512        1       YES  INACTIVE  115731870      2018-09-30 10:33:20  115790946     2018-09-30 10:57:29
   3       1        4583     52428800     512        1       YES  INACTIVE  115668092      2018-09-30 08:28:49  115731870     2018-09-30 10:33:20

• 再看一下闪回日志使用状况
  ○： select * from v$flash_recovery_area_usage;

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                      50.04                         0            2898

• 查看log位置

SQL> show parameter dump_dest;
NAME                  TYPE        VALUE
--------------------  ----------- ------------------------------
background_dump_dest  string      /u01/app/oracle/rdbms/diag/rdbms/kmb2b/kmb2b/trace
core_dump_dest        string      /u01/app/oracle/rdbms/diag/rdbms/kmb2b/kmb2b/cdump
user_dump_dest        string      /u01/app/oracle/rdbms/diag/rdbms/kmb2b/kmb2b/trace