Oracle 11g: ORA-28547: connection to server failed (Windows server 2008 R2)

Hi colleagues,
We have just installed Oracle 11g in a Windows server 2008 R2 but it appears not to be working.
I turned off the firewall but nothing happens anyway.

The error message we are receiving is:

Connecting...ORA-28547: connection to server failed, probable Oracle Net admin error.

Please your help.
Thanks.
LVL 2
José PerezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Mark GeerlingsDatabase AdministratorCommented:
Where (or in which application) do you see this error reported?
José PerezAuthor Commented:
I see it in Oracle Net Configuration Asistant when 'Testing' on 'Local Net Service Name configuration'.
Mark GeerlingsDatabase AdministratorCommented:
Have you ever installed an Oracle database on a Windows server before?

In my experience setting up Oracle databases (on Windows or Linux) getting the SQL Net configuration correct is the most troublesome part of the install.  I often end up hand-editing the tnsnames.ora and/or listener.ora files to get them working.

The Oracle Net Configuration Assistant tool should be able to give you a working configuration.  But, if it doesn't, post your listener.ora and tnsnames.ora files here for us to review.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

José PerezAuthor Commented:
LISTENER.ORA
# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dcserver)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\app\Administrator

Open in new window


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

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dcserver)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Open in new window

José PerezAuthor Commented:
By the way, when trying to test, Oracle displays only 1 Net Service called "ORACLR_CONNECTION_DATA"
Mark GeerlingsDatabase AdministratorCommented:
Open a command prompt on your server, navigate to this directory:
C:\app\Administrator\product\11.2.0\dbhome_1\bin
then enter:
tnsping orcl [Enter]

What does that return?  It should give you a multi-line response with a very short last line that looks about like this:
OK (0 msec)

Here is a (slightly-edited) screen shot of testing that in one of our systems:

C:\Ora10gR1\BIN>tnsping ldb1

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 29-JUN-2015 09:13:03

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

Used parameter files:
C:\Ora10gR1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=tcp)(HOST=xxx-yyy-zzz-
db.gentex.com)(PORT=1541))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)

Open in new window

José PerezAuthor Commented:
This is the result:

C:\app\Administrator\product\11.2.0\dbhome_1\BIN>tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 29-JUN-2015 06:19:12

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

Used parameter files:
C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

TNS-03505: Failed to resolve name

C:\app\Administrator\product\11.2.0\dbhome_1\BIN>
Mark GeerlingsDatabase AdministratorCommented:
That is a problem.  Can you also post your sqlnet.ora file here?  
The full path to it is in what you just posted, under the
"Used parameter files:" line.
José PerezAuthor Commented:
It is located in:
C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN

SQLNET.ORA
# sqlnet.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_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)

Open in new window

Mark GeerlingsDatabase AdministratorCommented:
I think the problem is not having an explicit entry in your listener.ora file for your database instance.  (Yes, the latest Oracle versions are supposed to be able to do "dynamic registration" of the listener with the database, and vice versa, but I still find it helpful to have a static registration defined in this file.)

I suggest that you start by making a copy of these three files:
listener.ora
tnsnames.ora
sqlnet.ora

from your
C:\app\Administrator\product\11.2.0\dbhome_1\network\admin
 directory, and copy them somewhere else, so you can restore them here if necessary.  (Like if the change I suggest doesn't help, or makes things worse.)

Then edit your listener.ora file and add another "SID DESC" section, so it looks like this:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
      (ORACLE_HOME= C:\app\Administrator\product\11.2.0\dbhome_1)
      (SID_NAME = orcl)
    )

Open in new window


Then, stop and restart the listener and repeat your test, or repeat the "tnsping orcl" test.

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
José PerezAuthor Commented:
it now says: "Connecting...ORA-12541: TNS:no listener
The test did not succeed."
Mark GeerlingsDatabase AdministratorCommented:
Is your listener actually running?  I haven't used Oracle11 on Windows.  (I did run Oracle7, 8, 9 and 10 on Windows).  In earlier versions of Oracle for Windows, the listener ran as a separate service.  Is that still true in Oracle11?  If yes, have you started that service?
Mark GeerlingsDatabase AdministratorCommented:
Here's another command-line test to try.  (This works in Linux, I think it also works in Windows.)

Go to:
C:\app\Administrator\product\11.2.0\dbhome_1\bin

then run:
lsnrctl status [Enter]

If that indicates the listener is not running, try:
lsnrctl start

Then repeat the "tnsping orcl" test.
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.