Эта ошибка возникает, если соединение с серверным процессом оборвалось. Самая частая причина — серверный процесс завершился ненормально. Причин этого ненормального завершения может быть очень-очень много и тут без анализа лог-файлов не обойтись.
Узнать, куда серверный процесс пишет логи можно так:
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.
ORA-03114
ORA-03114: не связан с ORACLE
Причина:
Вызов в ORACLE был предпринят, когда никакой связи еще не было установлено. Обыкновенно, это происходит потому что программа написанная пользователем еще не загружена. Это может также произойти если нарушение связи вызывает рассоединение.
Действие:
Повторите операцию. Если ошибка происходит снова, то проверьте программу написанную пользователем.
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 silver badge2 bronze badges
4
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,7242 gold badges5 silver badges11 bronze badges
16 November 2009
I encountered these 2 errors this morning. I scoured the internet for the best solution and explanation of the problem. Initially, it looks like a connection issue; so an Oracle newbie’s IT instincts would tell you to check and eliminate the obvious. I looked into the physical connections, network settings, tnsnames.ora configuration on the server and client and even the listener settings. Only to realize later on that nothing’s changed and everything looks perfectly normal. Apparently, it is far more complicated than that.
Here is a post from metalink I got from the net.
Problem Explanation:
ORA-03113: “end-of-file on communication channel”
ORA-03114: “not connected to ORACLE”
Solution Description: ORA-03113 AND ORA-03114 ON DESKTOP PLATFORMS
What does ORA-03113 “end-of-file on communication channel” really mean?ORA-03113 is the most common catchall error. It basically means that communications were lost for an unexpected reason. It is usually followed by ORA-03114 “not connected to ORACLE”.1. As it turns out, the most common reason is that the Oracle shadow process on the server died unexpectedly. So, if a running process were to suddenly encounter an ORA-03113 and/or 3114, the first place to check is the alert.log on the server to see if any other Oracle errors occurred.
2. Next most common cause of ORA-03113 is that the SQL*Net driver was not linked into the Oracle executable on Unix. While the listener successfully received the connection and passed it to the Oracle shadow process, the shadow process would fail to respond in any way because it would not know how to. So the client will see an ORA-03113 at connect
time.
3. Third most common cause is a machine crash or network failure at the server side.
4. One less common cause has been observed is when there are two servers with the same node names on the same network.5. ORA-03113 has also been noticed where the a TOKEN RING card has the Shared RAM size set to 8KB rather than 16KB. If you are using a TOKEN RING card check the shared buffer size and try increasing it.6. ORA-03113 also occurs when INIT.ORA parameters CONTEXT_AREA and CONTEXT_INCR are set to a value of 4096. Increasing the value to 8192 resolves the 3113. (RDVMS V6 only)
7. ORA-03113 also occurs when there are duplicate IP addresses on the network. To find the duplicate addresses turn off the unit that is getting the 03113 and ping its IP address. If the ping responds then you have to find the offending unit.
8. If an ORA-03113 error occurs intermittentlly on comparatively large select statements through SQL*DBA for OS/2 or when performing a query through SQL*DBA, try setting the default buffer size to 4096, especially if issuing an order by causes no problems but going through Q&E or SQR hangs the machine.
9. If using FTP V2.11 and running a large SQL*Plus script with over 38 variables of mostly character data types, check the lan drivers. The dxmc driver loads addressing for token ring adapter has an option to save memory (through IBM lan suppport program).
r parameter (r: reduced)
t parameter (t: tiny)You must specify the r and t parameter for the dxmc drive.It all comes down to the fact that the client went out to read some information from the server and suddenly found out the connection was no longer there.
ORA-03113 is just a symptom of a larger problem that will require more diagnosis to track down. Hopefully, the above information will lead you in the right direction to find the solution.
To debug an ORA-03113, it is a good idea to attempt the same operation while doing a loopback, i.e can any tool on the server connect using the same connect string as they specify from the Desktop client? If the same problem occurs doing a loopback, then you know the problem resides on the server side and not on the Desktop client side.
To perform a loopback invoke SQLPLUS or SQLDBA from the server, and at the SQLPLUS or SQLDBA prompt on the server type:
CONNECT USERNAME/PASSWORD@t:<servername>/<portnum>:<sid>
For example, if you are getting an ORA-03113 issuing a certain query from SQL*Plus on DOS while connected via SQL*Net TCP/IP to a Unix server, try a loopback by invoking SQL*Plus on the Unix machine and use the same “t:<servername>:<sid>” connect string and then issue the same SELECT statement.
10. If you’re getting an ORA-03113 on long queries with SQL*Net SPX protocol adapter v2.0.14.x.x on Windows or any version of the adapter v2.0.15 up to 2.0.15.0.4 on Windows, you are strongly encouraged to upgrade to v2.0.15.0.5 of the adapter with SQL*Net v2.0.15.0.3. The ORA-03113 usually occurs after a 120-second delay in the query.
Note: If your server is running the Unixware-Oracleware configuration, you will need to get the v2.0.15.0.5 SPX Adapter for Windows patch to go with your v2.0.14 SQL*Net for Windows. This configuration is only supported for Unixware-Oracleware customers.
11. This error is sometimes received intermittently with applications written with Gupta SQLWindows. Apparently, the problem is due to some buffer being used by the Gupta product. This buffer is used to fetch data returned by a query on the client side. The default value for a parameter called FETCH ROW (in SQLWINDOWS) is 20. Lowering this to something like 15 seems to work fine.
12. If you are receiving an ORA-03113 error when running SQL*Net TCPIP v1 from Windows clients to a Netware v3.11 file server, and you are using the TCPIP.NLM V1.00, then you must obtain the updated TCPIP.NLM (V2.02M).
It is also recommended to obtain the latest library patches for the NetWare file server (currently available in the NOVFILES forum on CompuServe as LIBUP4.EXE).Many enhancements and bug fixes have been made to the later TCPIP.NLM that Novell is currently shipping in their TCP188.EXE patch file located on their FTP server or located in the NOVLIB forum on CompuServe.Starting with SQL*Net TCP/IP 1.1.6.20 and later, TCPIP.NLM V2.02M is needed for Oracle’s TCP/IP driver to properly set the keepalive timers.
The latest SQL*Net V1 TCP/IP for NetWare to Date is 1.1.6.29 and that is also recommended for any customer with ANY earlier release of the V1 listener for NetWare.
13. Check that your network router is not filtering the data.
Another idea worth noting is this:
The TCP/IP retransmission count on Win NT/95/00 has a default value of 5. This means that the send side retransmits the packet five times or until it gets an acknowledgment. The timeout for each retransmission is two times the timeout for the previous retransmission. With the default value of 5, the send side retransmits 5 times (approximately. 15 seconds) and if it does not get an acknowledgment. it assumes that the other side is down and closes the connection. If the link goes down for a minute or two the Net8 client receives this error.
So try Modify the retransmission count to say, 9.
HKLM/SYSTEM/Services/TCPIP/Parameters/Interfaces/{interface #}
Check out this Microsoft KB link to have an idea on how that’s done.
>rmd
November 16, 2009
Categories: Oracle Errors . . Author: Oracle Certified
3 Comments
Comments RSS
TrackBack Identifier URI