Oracl11g SQL Plus. Error after entering User-Name and Password

Hi,

I'm very new to Oracle so please be gentle?!
Here goes..

Installed Oracle 11g on windows XP with fixed IP address. No errors reported.
Can connect from client using browser to Enterprise Manager.
Installed client software on my pc.
Have run Net Configuration Assistant and can connect via ODBC.
SQL Plus does not want to connect.

Below is an extract from the sqlnet.log off the 'Server'

Fatal NI connect error 12505, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLE-11G.DQGlobal.local)(PORT=1521))(CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=C:\app\Administrator\product\11.1.0\db_1\bin\emagent.exe)(HOST=ORACLE-11G)(USER=OraUser))))

  VERSION INFORMATION:
      TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
      Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 11.1.0.6.0 - Production
  Time: 30-JAN-2008 16:15:09
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12564
    TNS-12564: TNS:connection refused
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
naexpertAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SmilingPixieCommented:
One thing to check....

a) Is the TNS_ADMIN environment variable set in the registry?  By default it should point to your ORACLE_HOME/network/admin directory

Otherwise if you could post what's in your sqlnet.ora and tnsnames.ora on the cilent machine - that might give more information
0
sonicefuCommented:
Suppose you created a connect string, named ora11g in  tnsnames.ora file
go to the command prompt of at your client machine and type the following command


tnsping ora11g

Open in new window

0
sonicefuCommented:
replace in the following code snippet

<ip-11gServer or Name>  with oracle 11g server ip address or server name for example 10.0.0.10
<orcl.oracle.com>             replace it with global name if you know orcl.oracle.com

then paste in the ORACLE_HOME/network/admin/tnsnames.ora file and save the file
-------------------
go to command prompt, and test the following step by step

tnsping ora11g

tnsping ora11g2

tnsping ora11g3
---------------------
If the problem still persists then send the contents of the following

tnsnames.ora
sqlnet.ora
listener.ora

ora11g=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<ip-11gServer or Name>)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=<orcl.oracle.com>)
(INSTANCE_NAME=orcl)))
 
ora11g2=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<ip-11gServer or Name>)(PORT=1521))
(CONNECT_DATA=
(SID=orcl)))
 
ora11g3=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=tcp)(HOST=<ip-11gServer or Name>)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ORCL)))

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sonicefuCommented:
what is the result of the following, when you type at command prompt


sqlplus username@<ip-11gServer or Name>:1521/ORCL
 
sqlplus username@ora11g

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
naexpertAuthor Commented:
FINDINGS SO FAR

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SmilingPixie
a) Is the TNS_ADMIN environment variable set in the registry?  By default it should point to your ORACLE_HOME/network/admin directory

Otherwise if you could post what's in your sqlnet.ora and tnsnames.ora on the cilent machine - that might give more information.

FINDINGS
Searched registry for TNS_ADMIN but not found.

SQLNET.ORA (client copy)
# sqlnet.ora Network Configuration File: c:\app\Andycw\product\11.1.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# 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.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TNSNAMES.ORA (client copy)
# tnsnames.ora Network Configuration File: c:\app\Andycw\product\11.1.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLE-11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE-11G.DQGlobal.local)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Orcl)
    )
  )
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

sonicefu
Suppose you created a connect string, named ora11g in  tnsnames.ora file
go to the command prompt of at your client machine and type the following command
1:      tnsping ora11g

FINDINGS
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Andycw>cd \

C:\>tnsping oracle-11g

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 01-FEB-2
008 10:14:58

Copyright (c) 1997, 2007, Oracle.  All rights reserved.

Used parameter files:
c:\app\Andycw\product\11.1.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = ORACLE-11G.DQGlobal.local)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME
= Orcl)))
OK (50 msec)

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SONICEFU

replace in the following code snippet

<ip-11gServer or Name>  with oracle 11g server ip address or server name for example 10.0.0.10
<orcl.oracle.com>             replace it with global name if you know orcl.oracle.com

then paste in the ORACLE_HOME/network/admin/tnsnames.ora file and save the file
-------------------
go to command prompt, and test the following step by step

tnsping ora11g

tnsping ora11g2

tnsping ora11g3
---------------------
If the problem still persists then send the contents of the following

tnsnames.ora
sqlnet.ora
listener.ora
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:      ora11g=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<ip-11gServer or Name>)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=<orcl.oracle.com>)
(INSTANCE_NAME=orcl)))
 
ora11g2=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<ip-11gServer or Name>)(PORT=1521))
(CONNECT_DATA=
(SID=orcl)))
 
ora11g3=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=tcp)(HOST=<ip-11gServer or Name>)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ORCL)))


FINDINGS


I replaced my local tnsnames.ora with your code snippet as shown below, not sure if I got this correct:

ora11g=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.16.205)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ORACLE-11G.DQGlobal.local)
(INSTANCE_NAME=orcl)))
 
ora11g2=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.16.205)(PORT=1521))
(CONNECT_DATA=
(SID=orcl)))
 
ora11g3=
(DESCRIPTION=
(ADDRESS= (PROTOCOL=tcp)(HOST=192.168.16.205)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=ORCL)))


When I TNSPINGed got these results:


C:\>tnsping ora11g

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 01-FEB-2
008 10:34:16

Copyright (c) 1997, 2007, Oracle.  All rights reserved.

Used parameter files:
c:\app\Andycw\product\11.1.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=
TNS-12533: TNS:illegal ADDRESS parameters


C:\>tnsping ora11g2

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 01-FEB-2
008 10:34:52

Copyright (c) 1997, 2007, Oracle.  All rights reserved.

Used parameter files:
c:\app\Andycw\product\11.1.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=
TNS-12533: TNS:illegal ADDRESS parameters

C:\>tnsping ora11g3

TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on 01-FEB-2
008 10:35:32

Copyright (c) 1997, 2007, Oracle.  All rights reserved.

Used parameter files:
c:\app\Andycw\product\11.1.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=
TNS-12533: TNS:illegal ADDRESS parameters

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SONICEFU

what is the result of the following, when you type at command prompt
1:
2:
3:      sqlplus username@<ip-11gServer or Name>:1521/ORCL
 
sqlplus username@ora11g

RESULTS OF 1ST TRY

C:\>sqlplus username@oracle-11g:1521/ORCL

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 1 10:44:02 2008

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

ERROR:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA


Enter user-name:

****************************************
SUCCESS!!!! RESULTS OF 2ND TRY
****************************************
C:\>sqlplus SYSTEM@ORACLE-11G

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 1 10:46:20 2008

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

Enter password:

Connected to:
Oracle Database 11g Release 11.1.0.6.0  Production

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Please find below contents of client and server files, any advice on any changes needed would be appreciated.

CLIENT FILES


SQLNET.ORA
# sqlnet.ora Network Configuration File: c:\app\Andycw\product\11.1.0\client_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# 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.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TNSNAMES.ORA
# tnsnames.ora Network Configuration File: c:\app\Andycw\product\11.1.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLE-11G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE-11G.DQGlobal.local)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Orcl)
    )
  )



SERVER FILES


SQLNET.ORA
# sqlnet.ora Network Configuration File: C:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\sqlnet.ora
# Generated by Oracle configuration tools.

# 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.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (HOSTNAME, TNSNAMES, EZCONNECT)

TNSNAMES.ORA
# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.1.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE-11G.DQGlobal.local)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

LISTENER.ORA
# listener.ora Network Configuration File: C:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

TRACE_FILE_LISTENER = LISTENER.trc

TRACE_DIRECTORY_LISTENER = C:\app\Administrator\product\11.1.0\db_1\NETWORK\trace

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE-11G.DQGlobal.local)(PORT = 1521))
    )
  )

TRACE_LEVEL_LISTENER = USER
0
SujithData ArchitectCommented:
From the client machine, are you able to ping the server?
I.e. from the command line
ping ORACLE-11G.DQGlobal.local

If your host name is not recognized then change it with the IP address in the tnsnames.ORA file.
0
naexpertAuthor Commented:
Hi sujith80

I can ping using the syntax supplied.

I can now also connect via SQLPLUS using the sysntax below

C:\>sqlplus SYSTEM@ORACLE-11G
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 1 10:46:20 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Release 11.1.0.6.0  Production

BUT when using the syntax below I get an error. Can you or anyone help me? I have included all the appropriate files from th eclient and server in this posting.

C:\>sqlplus
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 1 16:30:42 2008
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Enter user-name: system
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error
0
sonicefuCommented:
<<I can now also connect via SQLPLUS using the sysntax below>>

naexpert !
Congratulations, your problems have been solved.
------------------------------------------------
ORA-12560: TNS:protocol adapter error

You are connecting from client so you must have to provide your Database information, which is written in tnsnames.ora file under ORACLE-11G
0
sonicefuCommented:
<<Please find below contents of client and server files, any advice on any changes needed would be appreciated.>>

You don't need to change any settings in any file.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.