ORA-12516

 

客户发来消息（传了好几个人，最后传到我）：数据库时断时续，要我过去看看。

牢骚一下：前面听话的大哥们，什么叫时断时续？应该把问题说明一点，至少给个错误号。

调查：

于问题发生的时间不规律，先让客户继续使用，问题再现时马上通知；与此同时观察数据库的日志，以下是日志的最后片段：

 Tue Apr 10 12:35:43 2012
DISTRIB TRAN JTP.04c20398.6.21.520230
  is local tran 6.21.520230 (hex=06.15.7f026)
  insert pending collecting tran, scn=45117317234 (hex=a.8133a072)
Tue Apr 10 12:47:43 2012
Thread 1 advanced to log sequence 8057
  Current log# 3 seq# 8057 mem# 0: /oradata/jtp/redo31.log
  Current log# 3 seq# 8057 mem# 1: /oradata/jtp/redo32.log
Tue Apr 10 12:47:43 2012
ARC1: Evaluating archive   log 2 thread 1 sequence 8056
ARC1: Beginning to archive log 2 thread 1 sequence 8056
Creating archive destination LOG_ARCHIVE_DEST_2: 'jtpstd'
Creating archive destination LOG_ARCHIVE_DEST_1: '/arch/1_8056.dbf'
ARC1: Completed archiving  log 2 thread 1 sequence 8056
Tue Apr 10 13:17:13 2012
DISTRIB TRAN JTP.04c20398.6.21.520230
  is local tran 6.21.520230 (hex=06.15.7f026))
  delete pending collecting tran, scn=45117317234 (hex=a.8133a072)
Tue Apr 10 13:34:53 2012
Error 2068 trapped in 2PC on transaction 10.34.515208. Cleaning up.
Error stack returned to user:
ORA-02068: following severe error from JTCS
ORA-03113: end-of-file on communication channel
Tue Apr 10 13:45:12 2012
Thread 1 advanced to log sequence 8058
  Current log# 4 seq# 8058 mem# 0: /oradata/jtp/redo41.log
  Current log# 4 seq# 8058 mem# 1: /oradata/jtp/redo42.log
Tue Apr 10 13:45:12 2012
ARC1: Evaluating archive   log 3 thread 1 sequence 8057
ARC1: Beginning to archive log 3 thread 1 sequence 8057
Creating archive destination LOG_ARCHIVE_DEST_2: 'jtpstd'
Creating archive destination LOG_ARCHIVE_DEST_1: '/arch/1_8057.dbf'
ARC1: Completed archiving  log 3 thread 1 sequence 8057
Tue Apr 10 14:12:18 2012
Error 2068 trapped in 2PC on transaction 25.18.22741. Cleaning up.
Error stack returned to user:
ORA-02068: following severe error from JTCS
ORA-03113: end-of-file on communication channel
Tue Apr 10 14:19:38 2012
ORA-00060: Deadlock detected. More info in file /oracle/admin/jtp/udump/jtp_ora_925786.trc.
Tue Apr 10 14:25:51 2012
Error 2068 trapped in 2PC on transaction 12.16.51583. Cleaning up.
Error stack returned to user:
ORA-02068: following severe error from JTCS
ORA-03113: end-of-file on communication channel

针对ORA-02068，ORA-03113的调查：

查询DBA_2PC_PENDING视图没有数据，没有未解决的分布式事务，ORACLE会自动处理分布式事务，不需要人工干预，考虑不是这个原因。

 

之后客户带来了应用软件的报错信息：ORA-12516

分析可能数据库连接数有关，查询当前数据库连接

Select count(*) from v$session;

当前连接480，而数据库的连接数限制为500，客户说近期业务扩展造成客户端连接数增加，需要增加数据库的连接限制，把process参数从500调整为1500

Alter system set processes=1500 scope=spfile;

同时修改pfile，由于9i的process是静态参数，重启数据库后该参数生效。