How do I trace hanging "connect / as sysdba"?
pcroft
server. The software alone installed OK but I can't create a database.
To cut a long story short it hangs on this:
sqlplus /nolog
connect / as sysdba .. hangs
Excuse my ignorance but can somone please tell me what happens
precisely on this connect so I know where to look, and how can I trace
the activity causing it to hang?
Thanks in advance for any help.
Peter Croft
Frank van Bortel
How long did you allow it to hang?
How do you try to create a database?
How experienced are you with Oracle?
If you have little to no experience, fire
up the Database Creation Assistant (dbca).
Go through the screens, and on the last screen (#12
if memory serves), make sure you also save the
scripts.
You can use the scripts to:
- study what it takes to create a database
- switch off options (comment out parts of the script)
- modify and create yet another database
- create a database one step at the time.
One thing you will notice, is that windows requires
the creation of services. One service is the oracle
engine itself, and initially that service is *not*
autostarting.
Check if you can find something called OracleserviceORCL;
the "ORCL" is actually the SID; you may have chosen
something different (how about TEST? :) ).
If found, make sure the service is started.
Then, open the command line interface ("DOS-box"), and:
c:\>set oracle_sid=ORCL (or what else you found as SID)
c:\>sqlplus "/ as sysdba"
That should work now - if not, post the exact error.
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
pcroft
Thank you for your reply.
>How long did you allow it to hang?
10 minutes
>How do you try to create a database?
Initially I tried doing that during the install using dbca but
as it hung there I undid the install, then re-did it and
just installed the software. I then tried the command-line route
to simplify things and found this problem occurred really early,
so I suspect something basic like a network problem.
>How experienced are you with Oracle?
My dba knowledge is patchy as I just tend to learn enough to get by.
However I have successfully created databases via scripts on Linux,
and previously installed Oracle 9.2 on a w2k server just like this.
>If you have little to no experience, fire
>up the Database Creation Assistant (dbca).
Do you mean the Database Configuration Assistant?
>Go through the screens, and on the last screen (#12
>if memory serves), make sure you also save the
>scripts.
It only gives me the option to save the parameter settings as an html
file; I can't see how to save the creation scripts.
Am I overlooking something?
>You can use the scripts to:
>- study what it takes to create a database
>- switch off options (comment out parts of the script)
>- modify and create yet another database
>- create a database one step at the time.
I think my problem is before you get to this stuff.
>One thing you will notice, is that windows requires
>the creation of services. One service is the oracle
>engine itself, and initially that service is *not*
>autostarting.
>Check if you can find something called OracleserviceORCL;
>the "ORCL" is actually the SID; you may have chosen
>something different (how about TEST? :) ).
>If found, make sure the service is started.
It is.
>Then, open the command line interface ("DOS-box"), and:
>c:\>set oracle_sid=ORCL (or what else you found as SID)
>c:\>sqlplus "/ as sysdba"
>That should work now - if not, post the exact error.
There is no error - it just hangs - which I why I ask
what happens at this point and how to trace it.
Thank you for your input but it doesn't address the problem I have.
Regards,
Peter
DR
> what happens at this point and how to trace it.
> Thank you for your input but it doesn't address the problem I have.
Could you please post the output from the following commands:
net start | findstr /I oracle
lsnrctl status <your_listener_name>
lsnrctl services <your_listener_name>
sqlplus "/@<your_tns_service> as sysdba" --> if it succeeds, if it doesn't,
just paste the error stack(if present)
Regards,
Dimitre
ianal Vista
news:43f70955$0$15786$1472...@news.sunsite.dk:
SQL*Net is NOT required to run an Oracle database.
DR
.. but in some cases it may be required if you access a particularly
configured oracle instance on a remote MS host via some type of
clients(terminal services?) :)
And ..., it's about connecting, not about running!
Regards,
Dimitre
Frank van Bortel
> Frank,
> Thank you for your reply.
>
>> How long did you allow it to hang?
> 10 minutes
>
Ok - that's a hang, for sure.
(Some folks allow it 10, 15 seconds max, and sometimes it
may take just that long for services to start)
[snip]
OK - as for what happens:
- the client (sql*plus) tries to open a session
to the instance, using OS authentication.
- that implies you should be logged on to the
system, with the same account you installed Oracle
with (that account "owns" the software, and thus the
processes)
- that again, implies a local account, as during
a reboot, when services start, network authentication
may not be available (even the network may be down).
Many windows sysadmins fail to see this point, and
will install using network accounts.
- you do need administrative right on the machine.
As you have done installations before, also on
Windows, this should not be unfamiliar to you.
The whole point is, there's not much to trace.
Usually, this fails with network errors (there's
no networking involved, but anyway), which indicate
services are not started.
Or with authorization failures, indicating you use
a different account, or you have sqlnet.ora wrong.
Just "hangs" are rare... Some thoughts:
- firewall?
- what happens when you try sqlplus /nolog
and then connect / as sysdba?
- you could add sqlnet.trace_level_client=admin
in sqlnet.ora and see if it results in any .trc files.
DR
> Usually, this fails with network errors (there's
> no networking involved, but anyway), which indicate
> services are not started.
> Or with authorization failures, indicating you use
> a different account, or you have sqlnet.ora wrong.
> Just "hangs" are rare... Some thoughts:
> - firewall?
"no networking involved, but anyway" -> "sqlnet.ora" -> "firewall"?
Could you elaborate?
Regards,
Dimitre
Frank van Bortel
The network error is a ORA-12560: TNS:protocol adapter error.
This indicates a networking error where
in fact no network substrate is in use (no tcp/ip).
I demonstrate in the (partial) trace below.
Some firewalls stop programs from acting as server. Check if
that's the case. Microsofts firewall just gets in, and starts
blocking without *any* conformation. Zonealarm will ask for
confomation and does block "outgoing" traffic.
sqlnet.ora trace_level_client will show the *attempt*
to set up a connection, in case of services not being started,
you will see this:
[18-FEB-2006 16:54:00:640] snlpcss: Unable to spawn Oracle oracle
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) db920, error 2.
[18-FEB-2006 16:54:00:640] sntpcall: exit
[18-FEB-2006 16:54:00:640] ntpcon: exit
[18-FEB-2006 16:54:00:640] nserror: nsres: id=0, op=65, ns=12560, ns2=0;
nt[0]=530, nt[1]=2, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[18-FEB-2006 16:54:00:640] nsopen: unable to open transport
[18-FEB-2006 16:54:00:640] nsmfr: 1632 bytes at 0x14eb5d0
[18-FEB-2006 16:54:00:640] nsmfr: 536 bytes at 0x14eb3a8
[18-FEB-2006 16:54:00:640] nladget: entry
[18-FEB-2006 16:54:00:640] nladget: exit
[18-FEB-2006 16:54:00:640] nsmfr: 212 bytes at 0x14ea098
[18-FEB-2006 16:54:00:640] nladtrm: entry
[18-FEB-2006 16:54:00:640] nladtrm: exit
[18-FEB-2006 16:54:00:640] nioqper: error from nscall
[18-FEB-2006 16:54:00:640] nioqper: ns main err code: 12560
[18-FEB-2006 16:54:00:640] nioqper: ns (2) err code: 0
[18-FEB-2006 16:54:00:640] nioqper: nt main err code: 530
[18-FEB-2006 16:54:00:640] nioqper: nt (2) err code: 2
[18-FEB-2006 16:54:00:640] nioqper: nt OS err code: 0
Does that answer your questions?
DR
> This indicates a networking error where
> in fact no network substrate is in use (no tcp/ip).
> I demonstrate in the (partial) trace below.
It says "protocol adapter error", reporting that an error occurred in the
transport layer, not network error!
> Some firewalls stop programs from acting as server. Check if
> that's the case. Microsofts firewall just gets in, and starts
> blocking without *any* conformation. Zonealarm will ask for
> confomation and does block "outgoing" traffic.
How could this be releated to your local bequeath/piped connection?
Regards,
Dimitre
pcroft
Thanks for your comments. I have just got back from a bookshop where I
was browsing the Oracle books!
I got some interesting results shown below:
C:\notes>net start | findstr /I oracle
OracleMTSRecoveryService
OracleOraHome92Agent
OracleOraHome92HTTPServer
OracleOraHome92TNSListener
OracleServiceSTARTER1
OracleServiceSTARTER2
OracleServiceSTARTER3
OracleServiceSTARTER5
C:\notes>lsnrctl status OracleOraHome92TNSListener
LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on
18-FEB-2006 17:30:26
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
TNS-01101: Could not find service name OracleOraHome92TNSListener
NNC-00405: network failure
C:\notes>lsnrctl services OracleOraHome92TNSListener
LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on
18-FEB-2006 17:31:
28
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
TNS-01101: Could not find service name OracleOraHome92TNSListener
NNC-00405: network failure
So it looks like there is a serious problem there which
I need to investigate.
Thanks for the explanation about what is going on with connect / as
sysdba.
The os user seems to be set up ok as an administrator member
of group OS_DBA.
I am running McAfee Personal Firewall but that is not reporting any
blocks.
> what happens when you try sqlplus /nolog
> and then connect / as sysdba?
That is what I did to get the hang.
>- you could add sqlnet.trace_level_client=admin
>in sqlnet.ora and see if it results in any .trc files.
I just tried this but get nothing.
Peter
DR
"pcroft" <pcr...@cix.co.uk> ha scritto nel messaggio
news:1140285229.9...@g47g2000cwa.googlegroups.com...
OracleOraHome92TNSListener is not your listener name!
Try with "lsnrctl status" (your listener name is "LISTENER").
Regards,
Dimitre
bdb...@gmail.com
w2k server was notorious for having issues with not supporting bequeath
connections via terminal services.
w2k3 server supports bequeathed connections via an RDP session.
We still don't know how the OP is connecting to the server:
- locally on the server console
- locally via ssh
- remotely via vnc
- remotely via terminal services
Until that is known, everything else is just speculation.
Posting a copy of the sqlnet.ora would certainly help, as well as the
oradim.log, the alert log (someone has already asked for the
listener.log above).
-bdbafh
Sybrand Bakker
On 18 Feb 2006 09:53:50 -0800, "pcroft" <pcr...@cix.co.uk> wrote:
>C:\notes>lsnrctl status OracleOraHome92TNSListener
>
>LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on
>18-FEB-2006 17:30:26
>
>Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
>
>TNS-01101: Could not find service name OracleOraHome92TNSListener
> NNC-00405: network failure
>
>
>C:\notes>lsnrctl services OracleOraHome92TNSListener
>
>LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on
>18-FEB-2006 17:31:
>28
>
>Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
>
>TNS-01101: Could not find service name OracleOraHome92TNSListener
> NNC-00405: network failure
>
>So it looks like there is a serious problem there which
>I need to investigate.
This is likely not a problem at all. The name of the listener is
defined in listener.ora The default is listener.
Your command is incorrect.
Just issue lsnrctl status and see what happens.
It will work.
>
>Thanks for the explanation about what is going on with connect / as
>sysdba.
>The os user seems to be set up ok as an administrator member
>of group OS_DBA.
>I am running McAfee Personal Firewall but that is not reporting any
>blocks.
>> what happens when you try sqlplus /nolog
>> and then connect / as sysdba?
>That is what I did to get the hang.
>
>>- you could add sqlnet.trace_level_client=admin
>>in sqlnet.ora and see if it results in any .trc files.
One other problem I can recall is Oracle being incapable to get in
touch with a PDC/BDC or a local SAM
In order to research you need to make the change that is most
frequently made to sqlnet.ora
Add the line
sqlnet.authentication_services=(none)
My bet is you will get in now. You may need to provide a password, as
you just disabled O/S authentication.
--
Sybrand Bakker, Senior Oracle DBA
Sybrand Bakker
>w2k server was notorious for having issues with not supporting bequeath
>connections via terminal services.
Agreed, but bequeath in that situation results in ora-12560.
Sybrand Bakker
sqlnet.ora is at least read always, even with bequeath connections.
firewall problems typically result in either ora-12535 or ora-12545.
Sybrand Bakker
Inaccurate.
Sql*net is NOT required for *LOCAL* connections.
DR
> sqlnet.ora is at least read always, even with bequeath connections.
> firewall problems typically result in either ora-12535 or ora-12545.
My point was that the firewall doesn't have anything to do with a bequeath
connection.
Regards,
Dimitre
pcroft
==========================
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?
==========================
>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:
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'
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
Sybrand Bakker
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.
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,
DR
>>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?
So "connect / as sysdba" without credentials works or no? :)
Dimitre
Frank van Bortel
>> The network error is a ORA-12560: TNS:protocol adapter error.
>> This indicates a networking error where
>> in fact no network substrate is in use (no tcp/ip).
>> I demonstrate in the (partial) trace below.
>
> It says "protocol adapter error", reporting that an error occurred in the
> transport layer, not network error!
>
Semantics.
Actually it says something about the adapter, nothing about layers.
>> Some firewalls stop programs from acting as server. Check if
>> that's the case. Microsofts firewall just gets in, and starts
>> blocking without *any* conformation. Zonealarm will ask for
>> confomation and does block "outgoing" traffic.
>
> How could this be releated to your local bequeath/piped connection?
>
How can you trace a bequeath connection (not piped, no named pipes,
nor IPC configured)? You can. Just shown it.
The oracle process acts as a server towards the "network". Even if that
"network" is just the local machine (hence the quotes).
And why don't you just try it, and see what happens?
Frank van Bortel
software, like ZoneAlarm.
A decent firewall works on the network, not just any service. But then
again, it would require a decent O/S.
ZoneAlarm can block programs from responding on *any* type of incoming
traffic.
In that respect, a firewall (as ZoneAlarm presents itself) could have
something to do with the problem at hand.
Frank van Bortel
the screen:
File->Run: cmd
c:\>set oracle_sid=starter1
c:\>net start OracleServiceStarter1
c:\>sqlplus /nolog
SQL> connect / as sysdba
Can you tell how the user, that installed Oracle, was set up?
Is it a domain user, or a local one?
DR
> nor IPC configured)? You can. Just shown it.
> The oracle process acts as a server towards the "network". Even if that
> "network" is just the local machine (hence the quotes).
Could you demonstrate it?
A firewall that blocks a local connection to an oracle instance via beq
protocol.
Regards,
Dimitre
DR
> software, like ZoneAlarm.
> A decent firewall works on the network, not just any service. But then
> again, it would require a decent O/S.
>
> ZoneAlarm can block programs from responding on *any* type of incoming
> traffic.
> In that respect, a firewall (as ZoneAlarm presents itself) could have
> something to do with the problem at hand.
See my reply on your "Semantics" post.
Regards,
Dimitre
pcroft
I expect things are in a mess. I tried to create a database several
times
and it hung each time, so I created several instances and used
a different name each time. This is not because I don't understand
the difference between a database and a schema.
And I do read manuals, and have been reading them on this problem
for over a week but got stuck as there were no obvious routes to follow
so I posted this query. I am grateful for the
advice received but I am not so keen on remarks like
your closing comments.
Dimitre:
SQL> connect / as sysdba
ERROR:
ORA-01031: insufficient privileges
??
The network seems ok for other tasks.
Sorry to be clueless - it's a pity it doesn't timeout and report what's
happening :)
Regards,
Peter
DR
> SQL> connect / as sysdba
> ERROR:
> ORA-01031: insufficient privileges
> ??
Add the OS user to the ora_dba group and retry.
Regards,
Dimitre
pcroft
Regards,
Peter
DR
> connections via terminal services.
> w2k3 server supports bequeathed connections via an RDP session.
>
> We still don't know how the OP is connecting to the server:
>
> - locally on the server console
> - locally via ssh
> - remotely via vnc
> - remotely via terminal services
>
> Until that is known, everything else is just speculation.
> Posting a copy of the sqlnet.ora would certainly help, as well as the
> oradim.log, the alert log (someone has already asked for the
> listener.log above).
Definitely agreed.
I stand corrected: "guessing" is not the right way and this thread proves it
again.
Peter(pcroft),
I'll suggest you(sorry if it's a little bit late:)), if you still want to
understand how the OS authentication on MS works,
to read the metalink notes 114384.1, 77665.1 etc first and only after that,
if the problem persist, to provide the details requested above.
Regards,
Dimitre
Sybrand Bakker
>Dimitre:
>SQL> connect / as sysdba
>ERROR:
>ORA-01031: insufficient privileges
>??
Your change of sqlnet.ora disables connect / as sysdba
Sybrand Bakker
>
>Add the OS user to the ora_dba group and retry.
>
>
>
>Regards,
>Dimitre
>
>
His change of sqlnet.ora disables connect / as sysdba
as that relies on O/S authentication, which is no longer used, due to
the change.
pcroft
created a database using dbca!
I didn't change anything new so I wonder if it is an intermittent
hardware error in a networking component. The pc is a new build so it's
quite plausible.
I'll let you know if I find out anything.
Regards,
Peter
DR
Correct,
and my question was meaningless.
> The authentication process tries to get in touch with your PDC.
> There is a problem in your network.
So,
if I'm not missing something again, if you set
sqlnet.authentication_services back to NTS and logon with a local(not
domain) user from the ora_dba group,
the OS authentication ("/ as sysdba" without credentials) should work.
Regards,
Dimitre
sybr...@yahoo.com
As far as I know, Oracle will contact the PDC, local or domain account.
I expect your suggestion is not going to work.
--
Sybrand Bakker
Senior Oracle DBA
EdStevens
That should be easy enuogh to test. If nothing blows up today, I
should have some time on my hands. I'll give it a whirl and report
back.
DR
> I expect your suggestion is not going to work.
Cannot test it right now, but this is what I read on MetaLink(Explanation,
1.)
Note:91210.1
.............................................................................................................................................................................................
Solution Description:
=====================
There a 2 solutions to connect to the database when no network is present:
1. You can log in as a NT local user.
2. You can disable NTS in sqlnet.ora by setting the following parameter:
SQLNET.AUTHENTICATION_SERVICES = (NONE)
Explanation:
============
1. When you log in as a local user, then the local users database is
contacted
and not the remote domain users database.
2. When NTS is disabled, even though you are logged in as a domain user, the
domain user's database will not be accessed.
Regards,
Dimitre
Joel Garry
>created a database using dbca!
Did you reboot the machine? Sometimes very odd things can happen with
multiple failed tries of installing or creating a db or service. You
might want to search this group for information about cleaning up all
traces of an oracle install. Part of the problem can be sticky dll's,
another part can be messy registry entries. Sometimes it's merely a
matter of using oradim to delete and create a service.
I've seen so many bizarro PC hardware problems, I'll believe anything
there. And I make a point of trying to avoid them as much as possible.
Symantec firewall on wife's XP just decided to block everything IE a
few days ago. Sheesh. Is there a shoot-foot=true registry entry?
jg
--
@home.com is bogus.
http://online.wsj.com/public/article/SB114047541194578433-i_f5CKcMhkn_DJU3LiXh4T67h74_20070220.html?mod=blogs
pcroft
The server is running W2k sp4, and I installed the software for Oracle
9.2
Active Directory is NOT installed.
I am logged on as a local OS user who is in groups Administrators and
ORA_DBA.
With SQLNET.AUTHENTICATION_SERVICES= (NTS) in sqlnet.ora
sqlplus "/ as sysdba" hangs,
but with sqlnet.authentication_services=(none)
sqlplus "sys/CHANGE_ON_INSTALL as sysdba" works.
When I put TRACE_LEVEL_SERVER=support into sqlnet.ora I got some
interesting output
in the trace file:
The Bequeath protocol is mentioned; I know this can be a problem with
w2k
but I can't find anything which seems to fit this scenario.
It looks like the process receives an error from a pipe but I am
not clear what is at the other end of the pipe. Here is the error
section:
[21-FEB-2006 12:11:35:890] nsprecv: reading from transport...
[21-FEB-2006 12:11:35:890] ntprd: entry
[21-FEB-2006 12:11:35:890] sntpread: entry
[21-FEB-2006 12:11:35:890] sntpread: Attempting to read 2064 bytes from
1060
[21-FEB-2006 12:12:30:015] sntpread: Read returned error 109 and 0
bytes
[21-FEB-2006 12:12:30:015] sntpread: Read returned 0 bytes
[21-FEB-2006 12:12:30:015] sntpread: exit
[21-FEB-2006 12:12:30:015] ntprd: exit
[21-FEB-2006 12:12:30:015] nsprecv: transport read error
....
[21-FEB-2006 12:12:30:015] nacomrc: failed with error 12637
....
[21-FEB-2006 12:12:30:015] nioqper: error from niotns: nsaccept
failed...
[21-FEB-2006 12:12:30:015] nioqper: nr err code: 0
[21-FEB-2006 12:12:30:015] nioqper: ns main err code: 12637
[21-FEB-2006 12:12:30:015] nioqper: ns (2) err code: 12537
[21-FEB-2006 12:12:30:015] nioqper: nt main err code: 0
[21-FEB-2006 12:12:30:015] nioqper: nt (2) err code: 0
[21-FEB-2006 12:12:30:015] nioqper: nt OS err code: 0
[21-FEB-2006 12:12:30:015] niotns: No broken-connection function
available.
[21-FEB-2006 12:12:30:015] niomapnserror: entry
[21-FEB-2006 12:12:30:031] niqme: entry
[21-FEB-2006 12:12:30:031] niqme: reporting NS-12637 error as ORA-12637
[21-FEB-2006 12:12:30:031] niqme: exit
[21-FEB-2006 12:12:30:031] niomapnserror: returning error 12637
[21-FEB-2006 12:12:30:031] niomapnserror: exit
[21-FEB-2006 12:12:30:031] niotns: Couldn't connect, returning 12637
It is interesting that it seems to trap the error ok but goes on to
hang
- is that because "No broken-connection function available"?
How could I fix that?
Is Oracle Advanced Security involved?
I haven't specifically installed it and it doesn't seem to appear in
the
Net Manager Console, but the trace file mentions it and
Oracle9i Security and Network Integration Guide Release 2 (9.2) for
Windows refers to Kerberos.
Regards,
Peter
Frank van Bortel
> Here's a summary of what I've found so far:
> The server is running W2k sp4, and I installed the software for Oracle
> 9.2
> Active Directory is NOT installed.
> I am logged on as a local OS user who is in groups Administrators and
> ORA_DBA.
> With SQLNET.AUTHENTICATION_SERVICES= (NTS) in sqlnet.ora
> sqlplus "/ as sysdba" hangs,
> but with sqlnet.authentication_services=(none)
> sqlplus "sys/CHANGE_ON_INSTALL as sysdba" works.
>
You change two things at once here. Try both "/ as sysdba"
and "sys/pswd as sysdba" with authentication_services=NTS.
Both ought to work.
Only "sys/pswd as sysdba" should work with with
authentication_services=none (or remotely - not the case).
It merely suggests the installation was done not as local admin,
and verification of the ORA_DBA group fails against some domain.
> When I put TRACE_LEVEL_SERVER=support into sqlnet.ora I got some
> interesting output
> in the trace file:
> The Bequeath protocol is mentioned; I know this can be a problem with
> w2k
What do you mean - there *no* problem with bequeath connecttions
on w2k.
> [21-FEB-2006 12:12:30:015] nioqper: error from niotns: nsaccept
> failed...
> [21-FEB-2006 12:12:30:015] nioqper: nr err code: 0
> [21-FEB-2006 12:12:30:015] nioqper: ns main err code: 12637
> [21-FEB-2006 12:12:30:015] nioqper: ns (2) err code: 12537
> [21-FEB-2006 12:12:30:015] nioqper: nt main err code: 0
> [21-FEB-2006 12:12:30:015] nioqper: nt (2) err code: 0
> [21-FEB-2006 12:12:30:015] nioqper: nt OS err code: 0
12637, 00000, "Packet receive failed"
// *Cause: A process was unable to receive a packet from another process.
// Possible causes are:
// 1. The other process was terminated.
// 2. The machine on which the other process is running went down.
// 3. Some other communications error occurred.
// *Action: If the cause is not obvious, contact Oracle Customer Support.
oerr tns 12537
12537, 00000, "TNS:connection closed"
// *Cause: "End of file" condition has been reached; partner has
disconnected.
// *Action: None needed; this is an information message.
But, as your problem is solved, we'll leave it here.
You can connect sys/pswd as sysdba, and startup, shutdown, migrate,
upgrade and create the database.
pcroft
Unfortunately the problem is not solved; we found a way round it
but I am afraid the hang might be symptomatic of a deeper
problem which could cause difficulties later if not resolved.
In answer to the points you raise:
1. With SQLNET.AUTHENTICATION_SERVICES= (NTS)
sqlplus "sys/pwd as sysdba"
hangs just like
sqlplus "/ as sysdba"
2. >It merely suggests the installation was done not as local admin,
>and verification of the ORA_DBA group fails against some domain.
I installed it as the same user I am using here. Besides the
group ORA_DBA was created and populated by the install so why
would that exist?
Regards,
Peter
Frank van Bortel
> Frank,
> Unfortunately the problem is not solved; we found a way round it
> but I am afraid the hang might be symptomatic of a deeper
> problem which could cause difficulties later if not resolved.
>
> In answer to the points you raise:
>
> 1. With SQLNET.AUTHENTICATION_SERVICES= (NTS)
> sqlplus "sys/pwd as sysdba"
> hangs just like
> sqlplus "/ as sysdba"
Which I expected. NT Security (NTS) on fails.
NTS off, and using sys/pswd uses passwordfile authentication; you
can change that using the orapwd utility.
Maybe you've already seen this, if not, good stuff on the matter:
http://asktom.oracle.com/pls/ask/f?p=4950:8:11268861732914726032::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:670117794561
> 2. >It merely suggests the installation was done not as local admin,
>> and verification of the ORA_DBA group fails against some domain.
> I installed it as the same user I am using here. Besides the
> group ORA_DBA was created and populated by the install so why
> would that exist?
>
> Regards,
> Peter
>
Because security is validated in the domain controller, not locally?
At least, when installed as domain user, not local admin.
pcroft
One interesting finding today: I cleaned Oracle off the server
completely (again) and re-installed it, and then found Oracle Advanced
Security had appeared in the Net Manager Console where it wasn't
visible before (I wonder why not?). This looked promising but
unfortunately it still hangs!
Regards,
Peter
pcroft
I discovered that the Kerberos service was disabled; when I tried to
enable and start it I got this error:
"error 1352: the security account manager (SAM) or local security
authority (LSA) server was in the wrong state to perform the security
operation".
I read some more about Kerberos and it appears tied up with Active
Directory - which wasn't installed. So I installed AD, and then the
Kerberos service ran and sqlplus /nolog, connect / as sysdba worked ok!
So with hindsight I guess the answers to my original questions are:
When sqlplus reads "connect / as sysdba" it looks to see what
authentication method
is in operation, as indicated by the parameter
SQLNET.AUTHENTICATION_SERVICES in sqlnet.ora.
If none it rejects the connect attempt with error ORA-01031:
insufficient privileges,
and needs a username and password in the password file instead
(sys/change_on_install).
If NTS it tries to do OS authentication using code which requires
Active Directory
and hangs if that isn't installed.
Tracing can be enabled by setting TRACE_LEVEL_SERVER=support in
sqlnet.ora.
Thanks to everyone who contributed to this thread.
Peter
bdb...@gmail.com
controller (or any type of domain controller, for that matter).
-bdbafh
pcroft
Thanks,
Peter
Joel Garry
>That's interesting - can you be more specific about why not?
One reason is one becomes dependent on the domain controller security,
which may change on a daily basis with windows security updates. There
is a divide between the administrator and system account privileges,
which get inherited by the processes they start up. So one winds up
with situations where it worked, then it doesn't, then one reboots, and
sometimes that helps and sometimes it doesn't, then one reinstalls, and
one doesn't have the same privileges so it doesn't work, so then one
comes whining to cdos when the problem is most certainly Bill Gates'
fault, and one wonders why one gets abused. Hypothetically.
jg
--
@home.com is bogus.
"If you have sufficient memory, you first need to curse Microsoft, then
visit Metalink. " - Sybrand
pcroft
Peter