Solved

problem connecting to oracle on server (1) tns not resolvable (2) ORACLE not available (3) shared memory realm does not exist

Posted on 2011-02-21
12
608 Views
Last Modified: 2012-05-11
Hi,

I am trying to connect to a couple of different oracle databases, but don't know much about this. It was working fine before but not anymore. We'll call them server1 and serve2. So, I have one tnsnames.ora file which looks like below (just altered names and ip address!).

BD.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = BC)
    )
  )

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

EXP_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EX))
    )
    (CONNECT_DATA =
      (SID = ExPr)
      (PRESENTATION = RO)
    )
  )

Open in new window


So,  for server BD.WORLD the errors I'm getting are: ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

for server ORCL the error I'm getting is ORA-12154: TNS:could not resolve service name.

So, I'm pretty sure TNS is correct. I've tried changing upper and lower case SERVICE_NAME = orcl as i read that could be the problem but didnt' seem to make a difference. So, I believe that all the info is correct, IP addresses, usernames, passwords and sid names. Any ideas what is wrong here?

Thanks Aiden
0
Comment
Question by:AidenA
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 34942958
try tnsping and see what the results are

> tnsping yourdbname

http://www.orafaq.com/wiki/Tnsping
0
 

Author Comment

by:AidenA
ID: 34943085
this is what i got below. Talked briefly with one of the oracle guys here and it all seemed fine to him. He looked at the tns file and said it was ok...

For the connection to BC

Used parameter files:
E:\oracle\ora92\network\admin\sqlnet.ora

Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SID=*)(SERVICE_NAME=bic))(ADDRESS=(PROTOCOL=TCP)(HOST=bic)(PORT=1521)))
OK (0 msec)


AND for the connection to orcl:

Used parameter files:
E:\oracle\ora92\network\admin\sqlnet.ora
TNS-03505: Failed to resolve name
0
 
LVL 18

Expert Comment

by:sventhan
ID: 34943369
<  Talked briefly with one of the oracle guys here and it all seemed fine to him. He looked at the tns file and said it was ok...

Are you still having issues?


http://www.stanford.edu/dept/itss/docs/oracle/9i/network.920/a96580/connect.htm
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:AidenA
ID: 34943388
yeah still having issues. the oracle guy just said the tnsnames.ora file seemed fine and he couldn't see the problem. i'll read through that link now see if i can learn something
0
 
LVL 18

Expert Comment

by:sventhan
ID: 34943586
0
 
LVL 47

Expert Comment

by:schwertner
ID: 34950771
There could be many explanations what happens:

1. There is firewall between you and ORCL database

    ping the IP/Domain name you use in the tnsnames.ora

2. The IP address of ORCL host is not correct OR the port is not correct:

     Ask to run:
     lsnrctl status
     lsnrctl services
     on that server

3. The listener is not up

    above will inform you

4. possibly the instance is not running
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 34952035
tnsping does not touch the database.  It only asks the listener if it knows about the database.

>>ORA-27101: shared memory realm does not exist

Means the listener knows about the database but the database is down so no connection cannot be made.

>>TNS-03505: Failed to resolve name

Something isn't correct in the tnsnames.ora file or you aren't looking at the tnsnames.ora file you think you are.

Do you have multiple oracle homes on the machine?  What does your sqlnet.ora file show for:  NAMES.DIRECTORY_PATH?
0
 

Accepted Solution

by:
AidenA earned 0 total points
ID: 34968630
Hi, well i had someone come on my computer and look at the issues. The problem appeared to be the sqlnet.ora file. So, he simply removed that and it worked. Don't know why that was a problem but it seems it's not needed so it was removed.

So not sure if anyone got that above? The tnsnames file itself was fine...
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34969818
>>So not sure if anyone got that above?

My last post right above your post (http:#a34952035):  "What does your sqlnet.ora file show for: NAMES.DIRECTORY_PATH?"

The sqlnet.ora file can easily be necessary but it depends on how your shop is set up.
0
 

Author Comment

by:AidenA
ID: 34970529
ok fair enough i'll give something for mentioning it... think i just saw the multiple oracle homes part and didn't have that so knew that wasn't the problem. don't really know what the sqlnet.ora file is for to be honest
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34971981
sqlnet.ora is the keeper for the Oracle client configurations.  I suggest you go to the docs for detailed information on everything it does:

http://download.oracle.com/docs/cd/E11882_01/install.112/e16774/glossary.htm#NTCLI1393
0
 

Author Closing Comment

by:AidenA
ID: 35005117
solution found by IT here
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now