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?
 
sonicefuConnect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
sujith80Commented:
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
All Courses

From novice to tech pro — start learning today.