Diagnose ORA-12170: TNS:Connect timeout occurred with succeeded

[benoit-schmid]

Hello,

It is the second time I see error message from our disp+work process, after our week end offline backups.

In the db alert log, we have:

Sat Jun 23 23:28:31 2012

QMNC started with pid=25, OS id=3866

Completed: ALTER DATABASE OPEN

ARC3: Archival started

ARC0: STARTING ARCH PROCESSES COMPLETE

Starting background process CJQ0

Sat Jun 23 23:28:32 2012

CJQ0 started with pid=29, OS id=3886

Setting Resource Manager plan SCHEDULER[0x3DCFF]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Therefore the db is opened correctly at 28.

In sm21, we have, for three work processes:

23:29:25 DIA 000 BY 0 > ORA-12170: TNS:Connect timeout occurred

...

23:34:10 DIA 000 Q0 1 Start Workproc 0, 17 times since system startup. PID 5247

23:34:10 DIA 005 Q0 1 Start Workproc 5, 17 times since system startup. PID 5248

23:34:10 DIA 007 Q0 1 Start Workproc 7, 17 times since system startup. PID 525

Just to avoid future questions, the db and the sap as are on the same host.

Therefore there is no network connection needed for these connections.

How can I diagnose why the work process can not connect to the db even though it is opened?

What are the most common causes for that kind of problem?

Thanks in advance for your help.

[former_member189725 · Accepted solution]

What is the value of the parameter SQLNET.INBOUND_CONNECT_TIMEOUT in the sqlnet.ora file under

$ORACLE_HOME/network/admin

Regards

Ratnajit

[KMK · Accepted solution]

Hi,

I am working with Benoit, that's why I am answering to your question on behalf of him.

Just verified sqlnet.ora , the parameter SQLNET.INBOUND_CONNECT_TIMEOUT is not set at all.

Rg,

Kamand

[former_member189725 · Accepted solution]

Can you set the value of SQLNET.INBOUND_CONNECT_TIMEOUT=360

and see if the error subsides.

Regards

Ratnajit

[Former Member · Accepted solution]

Hi,

Can you also check the sqlnet.log ( If older version of the DB ) for some additional information of error encountered. Please verify the enteries during the same time when the TNS error is encountered .

Also, the content of the Alert.log file should be verified.

If the error occurs because of slow network etc. then setting SQLNET.INBOUND_CONNECT_TIMEOUT could be useful.

Regards,

Faiz

[benoit-schmid · Accepted solution]

Hello,

I have change the following parameters.

# diff listener.ora listener.ora.20120625

27c27

< CONNECT_TIMEOUT_LISTENER_DEV = 120

---

> CONNECT_TIMEOUT_LISTENER_DEV = 10

# diff sqlnet.ora sqlnet.ora.20120625

13d12

< SQLNET.INBOUND_CONNECT_TIMEOUT=120

I will see if it is ok.

Anyway as soon as database is open, shouldn't it reply immediately?

What could cause this delay?

Have you experienced similar issues?

Thanks I advance for your answers.

[benoit-schmid · Accepted solution]

Hello,

Md Faizuddin Khan wrote:
Can you also check the sqlnet.log ( If older version of the DB ) for some additional information of error encountered. Please verify the enteries during the same time when the TNS error is encountered . 
Also, the content of the Alert.log file should be verified. 
If the error occurs because of slow network etc. then setting SQLNET.INBOUND_CONNECT_TIMEOUT could be useful.

There is no warning in alert.log as far as I have checked.

There is no network in between.

Concerning the log, there is the following error:

Fatal NI connect error 12170.

  VERSION INFORMATION:

        TNS for Solaris: Version 10.2.0.4.0 - Production

        TCP/IP NT Protocol Adapter for Solaris: Version 10.2.0.4.0 - Production

  Time: 23-JUN-2012 23:29:25

  Tracing not turned on.

  Tns error struct:

    ns main err code: 12535

    TNS-12535: TNS:operation timed out

    ns secondary err code: 12560

    nt main err code: 505

    TNS-00505: Operation timed out

    ns secondary err code: 12560

    nt main err code: 505

    TNS-00505: Operation timed out

    ns secondary err code: 12560

    nt main err code: 505

    TNS-00505: Operation timed out

    nt secondary err code: 145

    nt OS err code: 0

  Client address: <unknown>

    nt secondary err code: 145

    nt OS err code: 0

  Client address: <unknown>

    nt secondary err code: 145

    nt OS err code: 0

  Client address: <unknown>

What can you say, form this log?

Thanks in advance for your answer.

[hemanth2 · Accepted solution]

Dear Benoît,

Hope you are doing good.

Firstly, I recommend that you correct the database parameters settings as per note 1431798. In addition, install latest SBP for Oracle 11.2.0.1 as per note 1431795. Note that no new corrections are being written for Oracle 11.2.0.1, so to go to a newer patchset should be considered.

If the issue persists, execute the SQL script from note 1171650 for your Oracle release and provide the results ?

Thank you and have a nice day :).

_____________
Kind Regards,
Hemanth

[Former Member · Accepted solution]

After the clinet address in known from the error Client <> , The next option would be to look into the listener.log if the issue happens only for this client. If it is particluar to only one client then the analysis should

be done for the same client only for firewall settings , the oracle client configured on this server etc.

[benoit-schmid · Accepted solution]

Hello,

The firewall is not involved as it is on the same host.

Md Faizuddin Khan wrote:
After the clinet address in known from the error Client <> , The next option would be to look into the listener.log if the issue happens only for this client. If it is particluar to only one client then the analysis should 
be done for the same client only for firewall settings , the oracle client configured on this server etc.

[benoit-schmid · Accepted solution]

Hello Hemanth,

I have checked values as per note 1431798.

I am not going to blindly patch the DB without having identified the problem.

It is too easy to say patch the DB to solve the issue 🙂

I have run the check sql select of Note 1171650.

I would prefer not posting publicly it on the thread.

I can send it by email.

But everything seems ok and I have checked the suitable parameters.

I would appreciate if you could provide me the most common reasons for this problem?

Am I right when I say that when the DB is opened it should accept connections?

Hemanth Kumar wrote:
Dear Benoît,

Hope you are doing good.
Firstly, I recommend that you correct the database parameters settings as per note 1431798. In addition, install latest SBP for Oracle 11.2.0.1 as per note 1431795. Note that no new corrections are being written for Oracle 11.2.0.1, so to go to a newer patchset should be considered. 
If the issue persists, execute the SQL script from note 1171650 for your Oracle release and provide the results ?
Thank you and have a nice day :).
_____________
Kind Regards,
Hemanth 

Thanks in advance for your answers.

[benoit-schmid · Accepted solution]

Hello,

I have had the problem again this weekend.

After reading the thread http://scn.sap.com/thread/1349806,

I have changed the following parameter:

# diff listener.ora listener.ora.20120702

27c27

< INBOUND_CONNECT_TIMEOUT_LISTENER_DEV = 120

---

> CONNECT_TIMEOUT_LISTENER_DEV = 120

If someone has a better idea, I am interested.

Thanks in advance for your answers.

[benoit-schmid · Accepted solution]

Hello,

I have another question.

Is there a listener command to show what this current parameters values?

Thanks in advance for your answers.