Эта ошибка возникает, если соединение с серверным процессом оборвалось. Самая частая причина — серверный процесс завершился ненормально. Причин этого ненормального завершения может быть очень-очень много и тут без анализа лог-файлов не обойтись.
Узнать, куда серверный процесс пишет логи можно так:
SQL> select name, value from v$diag_info;
По-умолчанию примерно такое:
Default Trace File
$ORACLE_BASE/diag/rdbms/<sid>/<sid>/trace/<sid>_ora_nnnnn.trc
Серверный процес обычно не пишет логи (только самые критические ошибки). Включить логгирование можно так:
alter session set sql_trace=true;
Ошибку как в вопросе я воспроизвести конечно же не могу, но как примерно искать причину подобных ошибок см. далее.
Запускаю сессию с любого клиента и нахожу к нему серверный процесс, в моём случае:
oracle 17558 1 0 16:35 ? 00:00:00 oracle<сsid> (LOCAL=NO)
Предлагаю ему закрыться (провоцирую ошибку):
$ kill -TERM 17558
В клиенте получаю ошибку, что соединения больше нет:
SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 17558
Session ID: 270 Serial number: 6857
В папке trace/, в файле тревог alert_<sid>.log нахожу причину:
2019-07-26T16:39:53.571956+02:00
PDB1(3):Process termination requested for pid 17558 [source = unknown], [info = -1871706712]
[request issued by pid: 7532, uid: 1004]
Где pid: 7532, uid: 1004 это оболочка и учётка с которых был выполнен kill.
The SQL statement being executed at the time of the error was: select To_Char(To_Date(Decode(Resubmit_Time,null ,’00:
Shutting down Concurrent Manager : 03-OCT-2016 09:11:42
ORA-03114: not connected to ORACLE
ORACLE error 3114 in AFPRSR-Resubmit_Time
Cause:
AFPRSR-Resubmit_Time failed due to ORA-03114: not connected to ORACLE
.The SQL statement being executed at the time of the error was: select To_Char(To_Date(Decode(Resubmit_Time,null ,’00:
List of errors encountered:
_ 1 _
Routine AFPCMT encountered an ORACLE error. ORA-03114: not connected
to ORACLE
.Review your error messages for the cause of the error. (=<POINTER>)
_ 2 _
Routine AFPPRD has encountered a fatal error. ORA-03114: not connected
to ORACLE
Solutions:
There are multiple scenarios that cause this error. Check the following list of solutions:
Lets devide it into two scenarios where we get this error.
1.DB Connection issue :-
2. Verify there is no firewall or router setting which terminates connections which are active for longer than x minutes.
3. Verify there is no firewall or router setting which terminates connections which are idle for longer than x minutes.
3. Verify the Oracle configuration. Let the database send a packet every x minutes, so that the firewall,
router, etc does not close the connection due to being ‘idle’.
Since the upgrade to the 19C Oracle DB (before 12C) I receive randomly those ORA-03114 error message’s.
Before the upgrade I never received it. Any idea’s what exactly why those error messages pops up suddenly and what changes needs to be done?
- Domain Win10 PC
- Not all PC’s in the domain having this issue
- only localy tnsnames.ora change
ORA-03114: not connected to ORACLE
asked Jan 9, 2021 at 7:43
UK01UK01
11 gold badge1 silver badge2 bronze badges
2
ORA-03114 not connected to ORACLE
Cause: A call to Oracle was attempted when no connection was established. Usually this happens because a user-written program has not logged on. It may also happen if communication trouble causes a disconnection.
Common causes for the ORA-03114 error include:
- Listener process is not running
- DB is shutting down or session has been forcibly disconnected
- Missing entry to the tnsnames.ora
- Network issues such as timeouts
- Problems with connection pooling (shared servers, MTS)
If — as you seem to suggest — your tnsnames.ora was modified recently, an incorrect configuration could be one possible reason for the error, especially if other clients are not experiencing any problems. Compare the tnsnames.ora and sqlnet.ora files between the client that fails and the clients that work. Also check the network path for routers or firewalls that could be enforcing idle timeouts, if that is applicable in your environment.
See here for further explanation:
- https://www.tekstream.com/resource-center/ora-03114-not-connected-to-oracle/
- http://www.dba-oracle.com/t_ora_03114.htm
answered Jan 9, 2021 at 15:11
pmdbapmdba
2,8792 gold badges5 silver badges11 bronze badges
ORA-03114
ORA-03114: не связан с ORACLE
Причина:
Вызов в ORACLE был предпринят, когда никакой связи еще не было установлено. Обыкновенно, это происходит потому что программа написанная пользователем еще не загружена. Это может также произойти если нарушение связи вызывает рассоединение.
Действие:
Повторите операцию. Если ошибка происходит снова, то проверьте программу написанную пользователем.
Problem
The customer has a load balancer that was configured to time out idle connections. API calls that experienced a ORA-03114: not connected to ORACLE error, would fail
Resolving The Problem
SQLNET.EXPIRE_TIME was set to less than 60 in the sqlnet.ora for the Oracle instance. This produced «»dead connection detection»» traffic that allowed the connections to remain open and not time out.
[{«Product»:{«code»:»SSLVY3″,»label»:»Initiate Master Data Service»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w\/o TPS»},»Component»:»Not Applicable»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»Version Independent»,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]