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
596 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

758 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

14 Experts available now in Live!

Get 1:1 Help Now