现象： 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"]
排查
- 查看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
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
网友评论