Re: How do I trace hanging "connect / as sysdba"?

From: pcroft <pcroft_at_cix.co.uk>
Date: 18 Feb 2006 14:02:32 -0800
Message-ID: <1140298798.136042.26960@z14g2000cwz.googlegroups.com>

Oops, sorry about that - here are the correct results I hope!

c:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 18-FEB-2006 19:41:
50

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ash)(PORT=1521))) STATUS of the LISTENER

Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 9.2.0.1.0

- Produc
tion

Start Date                18-FEB-2006 17:25:29
Uptime                    0 days 2 hr. 16 min. 22 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   C:\oracle\ora92\network\admin\listener.ora
Listener Log File         C:\oracle\ora92\network\log\listener.log

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ash.tvs)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc))) Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

c:\>lsnrctl services

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 18-FEB-2006 21:07:
47

Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ash)(PORT=1521))) Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

Handler(s):

      "DEDICATED" established:0 refused:0
         LOCAL SERVER

The command completed successfully

There should be more services shouldn't there?

>sqlplus "/@<your_tns_service> as sysdba"

I don't know what to use for <your_tns_service>

>Add to sqlnet.ora the line
>sqlnet.authentication_services=(none)
I did that and this worked! -
SQL> connect sys/CHANGE_ON_INSTALL as sysdba Connected to an idle instance.

Great! So there is something in the authentication process which is causing the hang do you think?

This is sqlnet.ora:
# SQLNET.ORA Network Configuration File: C:\oracle\ora92\network\admin\sqlnet.ora # Generated by Oracle configuration tools.

#SQLNET.AUTHENTICATION_SERVICES= (NTS)
sqlnet.authentication_services=(none)

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on

NT.

NAMES.DIRECTORY_PATH= (TNSNAMES) sqlnet.trace_level_client=admin

Here is the bottom of listener.log:

System parameter file is C:\oracle\ora92\network\admin\listener.ora Log messages written to C:\oracle\ora92\network\log\listener.log Trace information written to C:\oracle\ora92\network\trace\listener.trc Trace level is currently 0

Started with pid=920
Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ash.tvs)(PORT=1521))) Listening on:
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
18-FEB-2006 19:41:50 *
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=peter))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153092352)) * status * 0
18-FEB-2006 21:07:47 *
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=peter))(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153092352)) * services * 0
18-FEB-2006 21:08:54 *
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=peter))(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153092352)) * services * 0

and this is the bottom of oradim.log corresponding to the same session:

Sat Feb 18 17:26:06 2006
ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file 'C:\ORACLE\ORA92\DATABASE\INITSTARTER3.ORA' Sat Feb 18 17:26:07 2006
ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file 'C:\ORACLE\ORA92\DATABASE\INITSTARTER1.ORA' Sat Feb 18 17:26:22 2006
ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file 'C:\ORACLE\ORA92\DATABASE\INITSTARTER5.ORA' Sat Feb 18 17:26:22 2006
ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file 'C:\ORACLE\ORA92\DATABASE\INITSTARTER2.ORA'

Regards,
Peter Received on Sat Feb 18 2006 - 16:02:32 CST