python - Django Oracle App 提供 ORA-03113 和 ORA-03135

Question

我有一个连接到 Oracle 的 Django 应用程序。每刷新 10 次左右，不管是哪个页面，都会报错。重新启动后的第一次 ORA-03113 和每个后续错误 ORA-03135。

我已经检查了数据库端的 alert.log 和 listener.log，但在日志文件中找不到有关此 session 终止的任何信息。

我已经编辑了 site-packages/django/db/初始化 .py 并在 close_if_unusable_or_obsolete 周围放置一个 try/except。该页面不再出现错误，并且在日志文件中没有 ORA 错误，但我仍然看到 SIGPIPE 错误。

我的猜测:Django 认为仍然存在连接并尝试关闭(close_if_unusable_or_obsolete)它，但在 Oracle 端连接不存在。第一个 ORA-03113 不会导致 listener.log 中的记录，因此它似乎甚至没有到达数据库服务器。在修改后初始化 .py 结果页是正确的。这样建页的查询就成功了。它似乎是出于某种管理目的的连接。

我迷路了，有没有人知道如何找到这个错误？

cx_oracle==5.1.3
Django==1.8.3
Oracle Client : instantclient-basic-linux.x64-12.1.0.2.0
Database version : 11.2.0.3.0

uwsgi_error.log

Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/django/core/handlers/wsgi.py", line 177, in __call__
    signals.request_started.send(sender=self.__class__, environ=environ)
  File "/usr/lib/python2.7/site-packages/django/dispatch/dispatcher.py", line 189, in send
    response = receiver(signal=self, sender=sender, **named)
  File "/usr/lib/python2.7/site-packages/django/db/__init__.py", line 64, in close_old_connections
    conn.close_if_unusable_or_obsolete()
  File "/usr/lib/python2.7/site-packages/django/db/backends/base/base.py", line 403, in close_if_unusable_or_obsolete
    self.close()
  File "/usr/lib/python2.7/site-packages/django/db/backends/base/base.py", line 198, in close
    self._close()
  File "/usr/lib/python2.7/site-packages/django/db/backends/base/base.py", line 152, in _close
    return self.connection.close()
  File "/usr/lib/python2.7/site-packages/django/db/utils.py", line 98, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/lib/python2.7/site-packages/django/db/backends/base/base.py", line 152, in _close
    return self.connection.close()
django.db.utils.OperationalError: ORA-03113: end-of-file on communication channel
Process ID: 10345
Session ID: 195 Serial number: 54225

[pid: 30|app: 0|req: 1/4] 123.123.123.123 () {46 vars in 1024 bytes} [Fri May 27 15:58:34 2016] GET /admin/ => generated 0 bytes in 8 msecs (HTTP/1.1 500) 0 headers in 0 bytes (0 switches on core 0)
[pid: 28|app: 0|req: 4/5] 123.123.123.123 () {46 vars in 1024 bytes} [Fri May 27 15:58:38 2016] GET /admin/ => generated 31712 bytes in 291 msecs (HTTP/1.1 200) 6 headers in 251 bytes (1 switches on core 0)
[pid: 28|app: 0|req: 5/6] 123.123.123.123 () {46 vars in 1024 bytes} [Fri May 27 15:58:39 2016] GET /admin/ => generated 31712 bytes in 276 msecs (HTTP/1.1 200) 6 headers in 251 bytes (1 switches on core 0)
[pid: 28|app: 0|req: 6/7] 123.123.123.123 () {46 vars in 1024 bytes} [Fri May 27 15:58:40 2016] GET /admin/ => generated 31712 bytes in 288 msecs (HTTP/1.1 200) 6 headers in 251 bytes (1 switches on core 0)
[pid: 30|app: 0|req: 2/8] 123.123.123.123 () {46 vars in 1024 bytes} [Fri May 27 15:58:40 2016] GET /admin/ => generated 31712 bytes in 608 msecs (HTTP/1.1 200) 6 headers in 251 bytes (1 switches on core 0)
Fri May 27 15:58:41 2016 - SIGPIPE: writing to a closed pipe/socket/fd (probably the client disconnected) on request /admin/ (ip 131.180.77.1) !!!
Traceback (most recent call last):
  File "/usr/lib/python2.7/site-packages/django/core/handlers/wsgi.py", line 177, in __call__
    signals.request_started.send(sender=self.__class__, environ=environ)
  File "/usr/lib/python2.7/site-packages/django/dispatch/dispatcher.py", line 189, in send
    response = receiver(signal=self, sender=sender, **named)
  File "/usr/lib/python2.7/site-packages/django/db/__init__.py", line 64, in close_old_connections
    conn.close_if_unusable_or_obsolete()
  File "/usr/lib/python2.7/site-packages/django/db/backends/base/base.py", line 403, in close_if_unusable_or_obsolete
    self.close()
  File "/usr/lib/python2.7/site-packages/django/db/backends/base/base.py", line 198, in close
    self._close()
  File "/usr/lib/python2.7/site-packages/django/db/backends/base/base.py", line 152, in _close
    return self.connection.close()
  File "/usr/lib/python2.7/site-packages/django/db/utils.py", line 98, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/lib/python2.7/site-packages/django/db/backends/base/base.py", line 152, in _close
    return self.connection.close()
django.db.utils.OperationalError: ORA-03135: connection lost contact
Process ID: 10345
Session ID: 195 Serial number: 54225

SQL*Net 跟踪文件包含:

(4043057216) [31-MAY-2016 15:18:51:303] nioqsn: entry
(4043057216) [31-MAY-2016 15:18:51:304] nioqsn: exit
(4043057216) [31-MAY-2016 15:18:51:304] nioqrc: entry
(4043057216) [31-MAY-2016 15:18:51:304] nsbasic_bsd: entry
(4043057216) [31-MAY-2016 15:18:51:304] nsbasic_bsd: tot=0, plen=13.
(4043057216) [31-MAY-2016 15:18:51:304] nttfpwr: entry
(4043057216) [31-MAY-2016 15:18:51:304] nttfpwr: socket 29 had bytes written=13
(4043057216) [31-MAY-2016 15:18:51:304] nttfpwr: exit
(4043057216) [31-MAY-2016 15:18:51:304] nsbasic_bsd: packet dump
(4043057216) [31-MAY-2016 15:18:51:304] nsbasic_bsd: 00 0D 00 00 06 00 00 00  |........|
(4043057216) [31-MAY-2016 15:18:51:304] nsbasic_bsd: 00 00 03 0F 0F           |.....   |
(4043057216) [31-MAY-2016 15:18:51:304] nsbasic_bsd: exit (0)
(4043057216) [31-MAY-2016 15:18:51:304] nsbasic_brc: entry: oln/tot=0
(4043057216) [31-MAY-2016 15:18:51:304] nttfprd: entry
(4043057216) [31-MAY-2016 15:18:51:304] ntt2err: entry
(4043057216) [31-MAY-2016 15:18:51:304] ntt2err: Read unexpected EOF ERROR on 29
(4043057216) [31-MAY-2016 15:18:51:304] ntt2err: exit
(4043057216) [31-MAY-2016 15:18:51:304] nttfprd: exit
(4043057216) [31-MAY-2016 15:18:51:304] nserror: entry
(4043057216) [31-MAY-2016 15:18:51:304] nserror: nsres: id=0, op=68, ns=12537, ns2=12560; nt[0]=507, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
(4043057216) [31-MAY-2016 15:18:51:304] nsbasic_brc: exit: oln=0, dln=0, tot=0, rc=-1
(4043057216) [31-MAY-2016 15:18:51:304] nioqer: entry
(4043057216) [31-MAY-2016 15:18:51:304] nioqer:  incoming err = 12151
(4043057216) [31-MAY-2016 15:18:51:304] nioqce: entry
(4043057216) [31-MAY-2016 15:18:51:304] nioqce: exit
(4043057216) [31-MAY-2016 15:18:51:304] nioqer:  returning err = 3113
(4043057216) [31-MAY-2016 15:18:51:304] nioqer: exit
(4043057216) [31-MAY-2016 15:18:51:304] nioqrc: exit
(4043057216) [31-MAY-2016 15:18:51:304] nioqds: entry
(4043057216) [31-MAY-2016 15:18:51:304] nioqds:  disconnecting...

传入错误 ORA-12151:TNS:收到来自网络层的错误数据包类型

编辑对 Titus 的回应

SELECT PROFILE, LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME = 'IDLE_TIME'; and: 
DEFAULT UNLIMITED

SELECT PROFILE FROM DBA_USERS WHERE USERNAME = 'DJANGO_USER';
DEFAULT

我不使用 TNSNAMES.ORA 或 SQLNET.ORA

Answer 1

这是对我有用的解决方法:

# wsgi.py
from django.db import connection
from uwsgidecorators import postfork

@postfork
def reconnect_to_db():
    print "RECONNECT TO DB!"
    connection.connect()

出于某种原因，uwsgi worker 需要手动连接 postfork , 否则每个 worker 的第一个查询都会失败。