Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How do I trace hanging "connect / as sysdba"?

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

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sun, 19 Feb 2006 00:17:19 +0100
Message-ID: <fbafv11p7ngv43lcv9rckt6vnq56scgt1s@4ax.com>


Comments embedded.

On 18 Feb 2006 14:02:32 -0800, "pcroft" <pcroft_at_cix.co.uk> wrote:

>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
>
>Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
>
>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
>
>Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
>
>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?

Those services are service_names provided by your database. But as your database doesn't start properly, you don't have extra services.

>==========================
>>sqlplus "/@<your_tns_service> as sysdba"
>
>I don't know what to use for <your_tns_service>

For local, O/S authenticated connections, *nothing*. For other connections, the tns_service_name specified in tnsnames.ora

>
>==========================
>>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?
>

The authentication process tries to get in touch with your PDC. There is a problem in your network.

>==========================
>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:
>
>TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production on
>18-FEB-2006 17:25:27
>
>Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
>
>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'
>

So your initstarter3.ora is incorrect and the database doesn't start.

>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'
Ditto for the starter1 database.

>
>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'
>

Ditto for the starter5 database.
>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'
>

And the starter2 database.

>==========================
>Regards,
>Peter

It appears we are looking at a real mess. Five instances on a single server is usually sheer madness.
In Oracle a *schema* is what a *database* is in Mickeysoft Sqlserver, which you obviously have been using.
Unlearn every bad habit from the Sqlserver era, above all *not* reading manuals.

Regards,

--
Sybrand Bakker, Senior Oracle DBA
Received on Sat Feb 18 2006 - 17:17:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US