Skip to Main Content
  • Questions

  • Connect as sysdba from client using host string

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Abhi.

Asked: April 12, 2004 - 7:54 am UTC

Last updated: January 31, 2006 - 2:14 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

Hello Sir,
we are using Linux AS 2.1 and oracle 9.2.0

I want to connect as sysdba from client machine running on window.
I have setup my tnsnames.ora and able to connect with any other user
except SYS ...



db_name = db1
sid = siddb1


[oracle@localhost dbs]$ orapwd file=orapwsiddb1 password=test
[oracle@localhost dbs]$ ll
total 12
-rw-r--r-- 1 oracle dba 417 Apr 6 11:46 initsiddb1.ora
-rw-r--r-- 1 oracle dba 543 Apr 7 12:26 initsiddb2.ora
-rwSr----- 1 oracle dba 1536 Apr 12 17:06 orapwsiddb1

path change. ..

[oracle@localhost admin]$ cat sqlnet.ora

sqlnet.authentication_services(ALL)

ON SERVER
-----------
ORACLE instance started.

Total System Global Area 269553824 bytes
Fixed Size 450720 bytes
Variable Size 184549376 bytes
Database Buffers 83886080 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter remote

NAME TYPE VALUE
------------------------------------ ----------- ---------------------remote_archive_enable string true
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
SQL>

FROM CLIENT
----------------

SQL> conn test/test@db1
Connected.


SQL> conn sys/test@db1 as sysdba
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.
SQL>
SQL>
SQL> conn sys@db1 as sysdba
Enter password: ****
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn "sys@db1 as sysdba"
Enter password: ****
ERROR:
ORA-12560: TNS:protocol adapter error




I got three differ. errors. .

Could you pls. tell me .. how can we setup this ...

Thanks,
Abhi ..





and Tom said...

I cannot reproduce. Please make sure your password file did not change AFTER database startup. bounce your database and if it doesn't persist, the password file was changed after startup.

Consider:


C:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 12 09:55:48 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/test@ora9ir2.us.oracle.com as sysdba;
Connected.
SQL> connect sys@ora9ir2.us.oracle.com as sysdba
Enter password:
Connected.
SQL> connect "sys@ora9ir2.us.oracle.com as sysdba"
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error


Warning: You are no longer connected to ORACLE.
SQL>


the third should fail, it is "wrong". The first two should succeed. If I simply recreate my password file:


[ora9ir2@tkyte-pc dbs]$ rm orapw$ORACLE_SID
[ora9ir2@tkyte-pc dbs]$ orapwd file=orapw$ORACLE_SID password=test

I immediately get:

SQL> connect sys@ora9ir2.us.oracle.com as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges


on the windows client. so, i think you did things in a different order than you say above perhaps...




Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Working ... :)

Abhi, April 12, 2004 - 11:26 pm UTC

Yes Sir,
My database was up when I created password file.
Now it is working ..



Thanks Sir ..

Abhi ..



ORA-12560: TNS:protocol adapter error

Puja, January 27, 2006 - 1:28 am UTC 

Hi

I am trying to create a database manually on 10g, but receiving ORA-12560 error. Can you please help in identifying the cause of this error...

I used the following steps:

1. Created initORCL.ora

(with remote_password_login_file= exclusive)

2. created the service using ORADIM

D:\PUJA>ORADIM -NEW -SID ORCL
Instance created.


3. Created the password file
D:\PUJA>ORAPWD FILE=E:\ora10g\database\pwdORCL.ora password=sys entries=5

4. set ORACLE_HOME and ORACLE_SID

D:\PUJA>set ORACLE_HOME=E:\ora10g
D:\PUJA>set ORACLE_SID=ORCL

5. 
D:\PUJA>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Jan 27 11:23:44 2006

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

SQL> conn sys/sys as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error



I checked out the trace file and found the following :

[27-JAN-2006 11:23:45:702] --- TRACE CONFIGURATION INFORMATION FOLLOWS ---
[27-JAN-2006 11:23:45:702] New trace stream is E:\ora10g\NETWORK\trace\cli_1996.trc
[27-JAN-2006 11:23:45:702] New trace level is 6
[27-JAN-2006 11:23:45:702] --- TRACE CONFIGURATION INFORMATION ENDS ---
[27-JAN-2006 11:23:45:702] --- PARAMETER SOURCE INFORMATION FOLLOWS ---
[27-JAN-2006 11:23:45:702] Attempted load of system pfile source E:\ORA10G\NETWORK\ADMIN\sqlnet.ora
[27-JAN-2006 11:23:45:702] Parameter source loaded successfully
[27-JAN-2006 11:23:45:702] 
[27-JAN-2006 11:23:45:702] Attempted load of local pfile source C:\WINNT\system32\sqlnet.ora
[27-JAN-2006 11:23:45:702] Parameter source was not loaded
[27-JAN-2006 11:23:45:702] 
[27-JAN-2006 11:23:45:718]  -> PARAMETER TABLE LOAD RESULTS FOLLOW <-
[27-JAN-2006 11:23:45:718] Successful parameter table load
[27-JAN-2006 11:23:45:718]  -> PARAMETER TABLE HAS THE FOLLOWING CONTENTS <-
[27-JAN-2006 11:23:45:718]   TRACE_LEVEL_SERVER = ADMIN
[27-JAN-2006 11:23:45:718]   TRACE_LEVEL_CLIENT = ADMIN
[27-JAN-2006 11:23:45:718]   NAMES.DIRECTORY_PATH = (TNSNAMES)
[27-JAN-2006 11:23:45:718]   TRACE_DIRECTORY_CLIENT = E:\ora10g\NETWORK\trace
[27-JAN-2006 11:23:45:718]   TRACE_DIRECTORY_SERVER = E:\ora10g\NETWORK\trace
[27-JAN-2006 11:23:45:718]   SQLNET.AUTHENTICATION_SERVICES = (NONE)
[27-JAN-2006 11:23:45:718]   TRACE_UNIQUE_CLIENT = on
[27-JAN-2006 11:23:45:718] --- PARAMETER SOURCE INFORMATION ENDS ---
[27-JAN-2006 11:23:45:718] --- LOG CONFIGURATION INFORMATION FOLLOWS ---
[27-JAN-2006 11:23:45:718] Log stream will be "C:\WINNT\system32\sqlnet.log"
[27-JAN-2006 11:23:45:718] Log stream validation not requested
[27-JAN-2006 11:23:45:718] --- LOG CONFIGURATION INFORMATION ENDS ---

[27-JAN-2006 11:23:45:718] nigini: entry
[27-JAN-2006 11:23:45:718] nigini: Count in the NL global area is now 1
[27-JAN-2006 11:23:45:718] nigini: Count in NI global area now: 1
[27-JAN-2006 11:23:45:718] nrigbi: entry
[27-JAN-2006 11:23:45:718] nrigbni: entry
[27-JAN-2006 11:23:45:718] nrigbni: Unable to get data from navigation file tnsnav.ora
[27-JAN-2006 11:23:45:718] nrigbni: exit
[27-JAN-2006 11:23:45:718] nrigbi: exit
[27-JAN-2006 11:23:45:718] nigini: exit
[27-JAN-2006 11:23:45:718] niqname: Hst is already an NVstring.
[27-JAN-2006 11:23:45:718] niqname: Inserting CID.
[27-JAN-2006 11:23:45:718] niotns: entry
[27-JAN-2006 11:23:45:718] niotns: niotns: setting up interrupt handler...
[27-JAN-2006 11:23:45:718] niotns: Not trying to enable dead connection detection.
[27-JAN-2006 11:23:45:718] niotns: Calling address: (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))')))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=e:\ora10g\bin\sqlplus.exe)(HOST=PUJA-AUDHYA)(USER=puja.audhya))))
[27-JAN-2006 11:23:45:718] nsmal: 212 bytes at 0x14d9080
[27-JAN-2006 11:23:45:718] nscall: connecting...
[27-JAN-2006 11:23:45:718] nladini: entry
[27-JAN-2006 11:23:45:718] nladini: exit
[27-JAN-2006 11:23:45:718] nladget: entry
[27-JAN-2006 11:23:45:718] nladget: exit
[27-JAN-2006 11:23:45:718] nsc2addr: (DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=e:\ora10g\bin\sqlplus.exe)(HOST=PUJA-AUDHYA)(USER=puja.audhya))))
[27-JAN-2006 11:23:45:718] nlpcaini: entry
[27-JAN-2006 11:23:45:718] nlpcaini: prg = oracle
[27-JAN-2006 11:23:45:718] nlpcaini: arg[0] = oracleORCL
[27-JAN-2006 11:23:45:718] nlpcaini: arg[1] = (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
[27-JAN-2006 11:23:45:718] nlpcaini: exit
[27-JAN-2006 11:23:45:718] nsmal: 536 bytes at 0x14da350
[27-JAN-2006 11:23:45:718] nsmal: 1632 bytes at 0x14da578
[27-JAN-2006 11:23:45:718] nsmal: 84 bytes at 0x14d9ec8
[27-JAN-2006 11:23:45:718] nsopen: opening transport...
[27-JAN-2006 11:23:45:718] ntpcon: entry
[27-JAN-2006 11:23:45:718] ntpcon: toc = 1
[27-JAN-2006 11:23:45:718] sntpcall: entry
[27-JAN-2006 11:23:45:718] snlpcss: entry
[27-JAN-2006 11:23:45:718] snlpcss: Spawn Oracle completed oracle     (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) ORCL.
[27-JAN-2006 11:23:45:718] snlpcss: exit
[27-JAN-2006 11:23:45:718] sntpcall: Attempting to open pipe \\.\PIPE\ORANTPC50.954

Tom Kyte
January 27, 2006 - 8:37 am UTC

you did not start the service, service must be started in order to "connect" to anything on windows

service is already started

Puja, January 29, 2006 - 11:19 pm UTC 

HI Tom

The service is already started. When I go to the service.msc pane, it shows the service as started.

I also tried to do it through the command prompt:
C:\WINNT\system32>ORADIM -SHUTDOWN -SID ORCL
ORA-12560: TNS:protocol adapter error


C:\WINNT\system32>ORADIM -STARTUP -SID ORCL
ORA-12560: TNS:protocol adapter error

But I am able to restart the service through services.msc

And again it gave me the same error:
C:\WINNT\system32>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Jan 30 09:40:16 2006

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

SQL> conn sys/sys as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error

I dont know what is missing over here.

(As mentioned earlier, ORACLE_HOME and ORACLE_SID has already been set)

Regards,

Puja

Tom Kyte
January 30, 2006 - 1:56 am UTC

show us the service is running using the net command. show us the oracle home and oracle sid are set.

Not that I don't believe you - but - this is caused by the service NOT being running in virtually all cases.

something like this (my sid was XE, where you see XE, you would use ORCL)

C:\Documents and Settings\tkyte>set ORACLE_SID=XE

C:\Documents and Settings\tkyte>echo %ORACLE_SID%
XE

C:\Documents and Settings\tkyte>net start | find "Oracle"
OracleMTSRecoveryService
OracleXEClrAgent
OracleXETNSListener

C:\Documents and Settings\tkyte>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Beta on Mon Jan 30 01:45:30 2006

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

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Documents and Settings\tkyte>net start oracleserviceXE
The OracleServiceXE service is starting.......
The OracleServiceXE service was started successfully.


C:\Documents and Settings\tkyte>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Beta on Mon Jan 30 01:46:06 2006

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


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta

sys@XE>

checing the Service

Puja, January 31, 2006 - 12:15 am UTC

HI Tom

Here is the information that you had asked to verify:


C:\WINNT\system32>echo %ORACLE_HOME%
E:\ora10g
C:\WINNT\system32>echo %ORACLE_SID%
ORCL

C:\WINNT\system32>net start | find "Oracle"
OracleOra9iAgent
OracleOra9iTNSListener
OracleServiceORCL

C:\WINNT\system32>net start oracleserviceORCL
The requested service has already been started.

More help is available by typing NET HELPMSG 2182.


C:\WINNT\system32>net stop oracleserviceORCL
The OracleServiceORCL service is stopping..
The OracleServiceORCL service was stopped successfully.


C:\WINNT\system32>net start oracleserviceORCL
The OracleServiceORCL service is starting.
The OracleServiceORCL service was started successfully.


C:\WINNT\system32>sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Jan 31 10:32:36 2006

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

ERROR:
ORA-12560: TNS:protocol adapter error


As you can see, both ORACLE_SID and ORACLE_HOME are set, also, the OracleserviceORCL is started (I restarted it).

Regards,

Puja

Tom Kyte
January 31, 2006 - 2:14 am UTC

please utilize support, i don't see anything obviously wrong.

Thanks

Puja, January 31, 2006 - 4:05 am UTC

HI Tom.

ORCL is just a practice database for me...And I am using downloaded version of Oracle10g, so I am not sure if I can utilize support.
Anyway, thanks for your time..

Regards,
Puja

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.