OraFAQ Forum: Networking and Gateways » ORA-12170 TNS: Time-out of connection

Home » RDBMS Server » Networking and Gateways » ORA-12170 TNS: Time-out of connection (Oracle 11.2 Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-12170 TNS: Time-out of connection [message #640185]

Thu, 23 July 2015 08:11

Caroline_789
Messages: 11
Registered: July 2015

Junior Member

Hi,

Since this morning our database instances are no longer available. When we connect to the databaseserver we can execute 'lsnrctl start' and this is what we get.
There should be an instance oracle11 and qaora11.


C:\Users\administrator>lsnrctl start

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 23-JUL-2015 15:00
:32

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

TNS-01106: Listener met de naam LISTENER is al gestart.

C:\Users\administrator>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 23-JUL-2015 15:00
:36

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Verbinding wordt gemaakt met (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SD-ORA11-
01.infohos.be)(PORT=1522))).
STATUS van LISTENER
------------------------
Alias                            LISTENER
Versie                           TNSLSNR for 64-bit Windows: Version 11.2.0.1.0
- Production
Startdatum                       23-JUL-2015 13:24:26
Ingeschakeld                    0 dagen 1 uren 36 minuten 15 seconden
Traceerniveau                    off
Beveiliging                      ON: Local OS Authentication
SNMP                      OFF
Parameterbestand van listener   D:\app\dba\product\11.2.0\dbhome_1\network\admin
\listener.ora.
Log van listener            d:\app\dba\diag\tnslsnr\SD-ORA11-01\listener\alert\l
og.xml
Overzicht beluisteren eindpunten...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SD-ORA11-01.infohos.be)(PORT=1522)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Overzicht van services...
Service "oracle11.world" heeft 1 instance(s).
  Instance "oracle11", toestand UNKNOWN, heeft 1 handler(s) voor deze service...

Uitvoeren van opdracht is geslaagd.

C:\Users\administrator>

Can anybody help? We haven't changed a thing.

Thanks in advance!

Joy

Report message to a moderator

Re: ORA-12170 TNS: Time-out of connection [message #640186 is a reply to message #640185]

Thu, 23 July 2015 08:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post last 100 or so lines from alert_qaora11.log file

It appear that QAORA11 database is down.

Report message to a moderator

Re: ORA-12170 TNS: Time-out of connection [message #640187 is a reply to message #640186]

Thu, 23 July 2015 08:17

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Check the alert log. There is no qaora in the status, probably database is down.

Report message to a moderator

Re: ORA-12170 TNS: Time-out of connection [message #640188 is a reply to message #640187]

Thu, 23 July 2015 08:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/m/639969/#msg_639969

is above the same database that is NOT available?

Report message to a moderator

Re: ORA-12170 TNS: Time-out of connection [message #640189 is a reply to message #640188]

Thu, 23 July 2015 08:35

Caroline_789
Messages: 11
Registered: July 2015

Junior Member

@BlackSwan: yes, same server but different instance. Where as in the first forumrequest the oracle11-instance was down, the qaora was still available. And we knew what was changed.
This time we haven't changed a thing (it worked perfectly yesterdag) and today none of the 2 work.

Report message to a moderator

Re: ORA-12170 TNS: Time-out of connection [message #640190 is a reply to message #640189]

Thu, 23 July 2015 08:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post content of D:\app\dba\product\11.2.0\dbhome_1\network\admin\listener.ora file.
post last 100 or so lines from alert_qaora11.log file

Report message to a moderator

Re: ORA-12170 TNS: Time-out of connection [message #640192 is a reply to message #640190]

Thu, 23 July 2015 08:46

Caroline_789
Messages: 11
Registered: July 2015

Junior Member

Hi,

Here is the listener.ora

# listener.ora Network Configuration File: D:\app\dba\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oracle11.world)
      (SID_NAME = oracle11)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SD-ORA11-01.infohos.be)(PORT = 1522))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = D:\app\dba

Here is the part of the alert_qaora11.log

Thu Jul 23 02:00:00 2015
Clearing Resource Manager plan via parameter
Thu Jul 23 03:36:56 2015
AUD: OS Error = 1717 encountered while writing audit record
AUD: OS Error = 1717 encountered while writing audit record
AUD: OS Error = 1717 encountered while writing audit record
AUD: OS Error = 1717 encountered while writing audit record
AUD: OS Error = 1717 encountered while writing audit record
AUD: OS Error = 1717 encountered while writing audit record
Thu Jul 23 03:40:06 2015
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as D:\app\dba\product\11.2.0\dbhome_1\RDBMS
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production.
Using parameter settings in server-side spfile D:\APP\DBA\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEQAORA11.ORA
System parameters with non-default values:
  processes                = 150
  nls_language             = "DUTCH"
  nls_territory            = "BELGIUM"
  memory_target            = 1232M
  control_files            = "D:\APP\DBA\ORADATA\QAORA11\CONTROL01.CTL"
  control_files            = "D:\APP\DBA\ORADATA\QAORA11\CONTROL02.CTL"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=QAORA11XDB)"
  audit_file_dest          = "D:\APP\DBA\ADMIN\QAORA11\ADUMP"
  audit_trail              = "DB"
  db_name                  = "QAORA11"
  open_cursors             = 300
  diagnostic_dest          = "D:\APP\DBA"
Thu Jul 23 03:40:13 2015
PMON started with pid=2, OS id=2904 
Thu Jul 23 03:40:13 2015
VKTM started with pid=3, OS id=2916 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Thu Jul 23 03:40:13 2015
GEN0 started with pid=4, OS id=2920 
Thu Jul 23 03:40:13 2015
DIAG started with pid=5, OS id=2984 
Thu Jul 23 03:40:13 2015
DBRM started with pid=6, OS id=2988 
Thu Jul 23 03:40:13 2015
PSP0 started with pid=7, OS id=3000 
Thu Jul 23 03:40:13 2015
DIA0 started with pid=8, OS id=3008 
Thu Jul 23 03:40:13 2015
MMAN started with pid=9, OS id=3016 
Thu Jul 23 03:40:13 2015
DBW0 started with pid=10, OS id=3028 
Thu Jul 23 03:40:13 2015
LGWR started with pid=11, OS id=3044 
Thu Jul 23 03:40:13 2015
CKPT started with pid=12, OS id=3060 
Thu Jul 23 03:40:13 2015
SMON started with pid=13, OS id=3064 
Thu Jul 23 03:40:13 2015
RECO started with pid=14, OS id=3068 
Thu Jul 23 03:40:13 2015
MMON started with pid=15, OS id=1544 
Thu Jul 23 03:40:13 2015
MMNL started with pid=16, OS id=1812 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\app\dba
Thu Jul 23 03:40:15 2015
alter database mount exclusive
Successful mount of redo thread 1, with mount id 3056281311
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 90 KB redo, 39 data blocks need recovery
Started redo application at
 Thread 1: logseq 13011, block 42217
Recovery of Online Redo Log: Thread 1 Group 3 Seq 13011 Reading mem 0
  Mem# 0: D:\APP\DBA\ORADATA\QAORA11\REDO03.LOG
Completed redo application of 0.03MB
Completed crash recovery at
 Thread 1: logseq 13011, block 42397, scn 431218664
 39 data blocks read, 39 data blocks written, 90 redo k-bytes read
Thu Jul 23 03:40:25 2015
Thread 1 advanced to log sequence 13012 (thread open)
Thread 1 opened at log sequence 13012
  Current log# 1 seq# 13012 mem# 0: D:\APP\DBA\ORADATA\QAORA11\REDO01.LOG
Successful open of redo thread 1
Thu Jul 23 03:40:25 2015
SMON: enabling cache recovery
Thu Jul 23 03:40:28 2015
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8ISO8859P15
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Jul 23 03:40:33 2015
QMNC started with pid=20, OS id=3712 
Completed: alter database open
Thu Jul 23 03:40:43 2015
Starting background process CJQ0
Thu Jul 23 03:40:43 2015
CJQ0 started with pid=23, OS id=4048 
Thu Jul 23 03:45:34 2015
Starting background process SMCO
Thu Jul 23 03:45:34 2015
SMCO started with pid=19, OS id=4872 
Thu Jul 23 13:23:16 2015
AUD: OS Error = 1717 encountered while writing audit record
AUD: OS Error = 1717 encountered while writing audit record
AUD: OS Error = 1717 encountered while writing audit record
AUD: OS Error = 1717 encountered while writing audit record
AUD: OS Error = 1717 encountered while writing audit record
AUD: OS Error = 1717 encountered while writing audit record
Thu Jul 23 13:24:27 2015
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as D:\app\dba\product\11.2.0\dbhome_1\RDBMS
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production.
Using parameter settings in server-side spfile D:\APP\DBA\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEQAORA11.ORA
System parameters with non-default values:
  processes                = 150
  nls_language             = "DUTCH"
  nls_territory            = "BELGIUM"
  memory_target            = 1232M
  control_files            = "D:\APP\DBA\ORADATA\QAORA11\CONTROL01.CTL"
  control_files            = "D:\APP\DBA\ORADATA\QAORA11\CONTROL02.CTL"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=QAORA11XDB)"
  audit_file_dest          = "D:\APP\DBA\ADMIN\QAORA11\ADUMP"
  audit_trail              = "DB"
  db_name                  = "QAORA11"
  open_cursors             = 300
  diagnostic_dest          = "D:\APP\DBA"
Thu Jul 23 13:24:35 2015
PMON started with pid=2, OS id=2892 
Thu Jul 23 13:24:35 2015
VKTM started with pid=3, OS id=2900 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Thu Jul 23 13:24:35 2015
GEN0 started with pid=4, OS id=2980 
Thu Jul 23 13:24:35 2015
DIAG started with pid=5, OS id=3060 
Thu Jul 23 13:24:35 2015
DBRM started with pid=6, OS id=1660 
Thu Jul 23 13:24:35 2015
PSP0 started with pid=7, OS id=1488 
Thu Jul 23 13:24:35 2015
DIA0 started with pid=8, OS id=1600 
Thu Jul 23 13:24:35 2015
MMAN started with pid=9, OS id=2176 
Thu Jul 23 13:24:35 2015
DBW0 started with pid=10, OS id=2312 
Thu Jul 23 13:24:35 2015
LGWR started with pid=11, OS id=2324 
Thu Jul 23 13:24:35 2015
CKPT started with pid=12, OS id=2316 
Thu Jul 23 13:24:35 2015
SMON started with pid=13, OS id=2212 
Thu Jul 23 13:24:35 2015
RECO started with pid=14, OS id=2220 
Thu Jul 23 13:24:35 2015
MMON started with pid=15, OS id=2224 
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Thu Jul 23 13:24:35 2015
MMNL started with pid=16, OS id=2276 
starting up 1 shared server(s) ...
ORACLE_BASE from environment = D:\app\dba
Thu Jul 23 13:24:37 2015
alter database mount exclusive
Successful mount of redo thread 1, with mount id 3056311253
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 319 KB redo, 92 data blocks need recovery
Started redo application at
 Thread 1: logseq 13012, block 9107
Recovery of Online Redo Log: Thread 1 Group 1 Seq 13012 Reading mem 0
  Mem# 0: D:\APP\DBA\ORADATA\QAORA11\REDO01.LOG
Completed redo application of 0.26MB
Completed crash recovery at
 Thread 1: logseq 13012, block 9746, scn 431253922
 92 data blocks read, 92 data blocks written, 319 redo k-bytes read
Thu Jul 23 13:24:46 2015
Thread 1 advanced to log sequence 13013 (thread open)
Thread 1 opened at log sequence 13013
  Current log# 2 seq# 13013 mem# 0: D:\APP\DBA\ORADATA\QAORA11\REDO02.LOG
Successful open of redo thread 1
Thu Jul 23 13:24:46 2015
SMON: enabling cache recovery
Thu Jul 23 13:24:49 2015
Successfully onlined Undo Tablespace 2.
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8ISO8859P15
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Jul 23 13:24:54 2015
QMNC started with pid=20, OS id=3608 
Completed: alter database open
Thu Jul 23 13:25:02 2015
Starting background process CJQ0
Thu Jul 23 13:25:02 2015
CJQ0 started with pid=23, OS id=3912 
Thu Jul 23 13:29:55 2015
Starting background process SMCO
Thu Jul 23 13:29:55 2015
SMCO started with pid=22, OS id=4872

Thanks in advance!

Report message to a moderator

Re: ORA-12170 TNS: Time-out of connection [message #640193 is a reply to message #640192]

Thu, 23 July 2015 08:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

https://www.google.com/search?q=AUD%3A+OS+Error+%3D+1717+encountered+while+writing+audit+record

QAORA11 is down due to OS Error = 1717

Report message to a moderator

Re: ORA-12170 TNS: Time-out of connection [message #640195 is a reply to message #640193]

Thu, 23 July 2015 09:00

Caroline_789
Messages: 11
Registered: July 2015

Junior Member

I'm not sure which actions should be taken now.
As for a previous question that qaora11 would be down:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\administrator>sqlplus sys/QA as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Do Jul 23 15:57:44 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Verbonden met:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> startup
ORA-01081: Kan ORACLE niet starten, ORACLE is al actief - eerst afsluiten.
SQL>

Report message to a moderator

Re: ORA-12170 TNS: Time-out of connection [message #640196 is a reply to message #640195]

Thu, 23 July 2015 09:03

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I'm not sure which actions should be taken now.

You can choose between options below.

1) go watch some YOUTUBE videos while waiting for Oracle to heal itself.
2) make necessary changes so that OS Error no longer occurs

Report message to a moderator

Re: ORA-12170 TNS: Time-out of connection [message #640197 is a reply to message #640195]

Thu, 23 July 2015 09:03

Caroline_789
Messages: 11
Registered: July 2015

Junior Member

@BlackSwan: thanks for the quick reply about error 1717. I'm just not sure about the actions that should be taken.

Report message to a moderator

Re: ORA-12170 TNS: Time-out of connection [message #640198 is a reply to message #640197]

Thu, 23 July 2015 09:13

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

you could do what others who had same problem did

Report message to a moderator

Re: ORA-12170 TNS: Time-out of connection [message #640212 is a reply to message #640192]

Thu, 23 July 2015 10:41

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

It looks as though your Oracle instance can't write to the operating system audit trail, which is the Windows application log. That is a mandatory audit, you can't stop it. Talk to your sys admin.

Report message to a moderator

Previous Topic:	Dedicated servers and "handoff"
Next Topic:	TNS adapter error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 03:54:47 CDT 2024