Problem: connecting to 2 databases 1 server 1

[Guest_imported]

I am new at this, so please bear with me. We have installed two different Oracle 8i databases on one server. I can connect to the database that was first created, but when I try to connect to the second database, I get a "TNS:could not resolve service name". My listener.ora contains both SIDs, as stated in the user guides, but I still can't connect.

************
...
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = sid1)
(SID_NAME = sid1)
)

(SID_DESC =
(GLOBAL_DBNAME = sid2)
(SID_NAME = sid2)
)
**************
Is there something I should do on the the listener.ora? My tnsnames.ora file does have an entry for both SIDs. I don't know what to do. Please help.

Sincerely,
Pedro Mercado

 

[carp]

Check the setting of the environmental parameter ORACLE_SID. If it is set to your first SID, change it to the second and try again.

 

[Guest_imported]

Thanks for your advice. I did what you stated in your email and the instance is pointing to the one that I cannot connect to. I am trying to find out if it is possible to connect to both SIDs on the server. We are installing two databases in that server so sometimes we will have to log on to db1 and other times to db2. What should I do to accomplish this?

 

[karluk]

Your error message sounds like a problem with the entry for sid2 in tnsnames.ora. I get this error when I pick an sid that doesn't exist on my system, and try to connect:

ORA-12154: TNS:could not resolve service name

On the other hand, if I make a tnsnames.ora entry for a database that doesn't exist on my server, I get a different error:

ORA-12505: TNS:listener could not resolve SID given in connect descriptor

Please post the contents of your tnsnames.ora file. Maybe someone in the forums can spot something that needs to be changed.

 

[Guest_imported]

My tnsnames.ora file has the following entries (with different names for security):

################
# Filename......: tnsnames.ora
# Name..........: LOCAL_REGION.world
# Date..........: 22-NOV-96 11:57:53
################


db1.domain =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db1.domain)
)
)


db2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1040))
)
(CONNECT_DATA =
(SERVICE_NAME = db2)
)
)


In this case, db1 works but not db2.

 

[THoey]

Well, the obvious difference between the two entries is the ".domain" in the first and seventh lines of the db2 entry.

Other than that, here is what mine looks like:

OLV_ORACLE.world = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
        (ADDRESS = 
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = 99.999.999.999)
          (Port = 1521)
        )
        (ADDRESS = 
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = 99.999.999.999)
          (Port = 1526)
        )
    )
    (CONNECT_DATA = (SID = ORCL)
    )
  )

Hope that helps... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[Guest_imported]

Thank you for your help. The first database was created using the db1.domain convention, while the second was just created using db2 without the domain. They were both created through the Oracle Database Assistant. Should both databases have been created with the domain extension? Is it possible to access two databases on 1 server? If so, what other settings should be applied to the server (e.g. listener, etc.)?

 

[karluk]

Does your sqlnet.ora contain an entry

NAMES.DEFAULT_DOMAIN = domain

If not, I think your problem can be corrected with the following tnsnames.ora entry.

db2.domain =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1040))
)
(CONNECT_DATA =
(SERVICE_NAME = db2)
)
)

I would also experiment with SERVICE_NAME = db2.domain, but my interpretation of Oracle's documentation is that this is unnecessary.

Please also provide the exact error number. It seems that there are two different error messages - ORA-12514 & ORA-12154 that both say "could not resolve service name". I'm interested in which one you are seeing.

 

[Guest_imported]

I always get the following error message:

ORA-12154: TNS:could not resolve service name

I tried your changes in tnsnames.ora, but I still get the same error. My SQLNET.ORA contains the following information:

TRACE_LEVEL_CLIENT = OFF
#sqlnet.authentication_services = (NONE)
names.directory_path = (TNSNAMES, HOSTNAME)
names.default_domain = world
name.default_zone = world
automatic_ipc = off

 

[carp]

In that case, I would change the TNS aliases from SID1.domain and SID2.domain to SID1.world and SID2.world.

You might also want to turn tracing on and try to TNSPING each alias from the command line prompt:

TNSPING SID1 3
TNSPING SID2 3

If either of these error out, look at the trace file and see if you can discern where the problem is (different tnsnames.ora file, mis-defined values, etc).

I think your tnsnames.ora entries should look like:

db1.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db1)
)
)


db2.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1040))
)
(CONNECT_DATA =
(SERVICE_NAME = db2)
)
)

 

[Guest_imported]

I use TNSPING on the server and I got 3 OKs for each SID. I can't run the same on the client side, since I do not have that utility installed.

 

[raygg]

If you are running on NT check the environemntal path in MS-DOS using the PATH command to make sure the oracle bin is the first entry on the path and it corresponds to the location of the listener you are using. If the two db's are not the same Oracle version, you have to use the more recent Oracle version listener to access both db's. Also go into the net8 assistant and verify that al the entries for the two different db's that should match do match. Sometimes also, you can delete both tnsnames enttries and recreate them - that sometimes works. Hope this helps

 

[Guest_imported]

I ran the PATH command and bin is the first entry on the path. The listener is in the same in the same drive, but not in the ./bin directory. Should I recreate the tnsnames on the client or server?

 

[raygg]

Sorry I misled you - I meant the listener would be in the same oracle home as the most recent version oracle you have installed (if more than one version is on the server). If oracle 8 the listener would probably be in ohome>net80>admin>listener.ora, but in oracle 8i it is probably in ohome>network>admin>listener.ora.

After you make a change to the tnsnames.ora you should try stopping and restarting the listener. Sometimes that makes all the difference.

 

[THoey]

Karluk,

Just finally got tired of typing the domain name and took your advice of setting the "NAMES.DEFAULT_DOMAIN = domain". Works like a charm. I have both 7.3.4 and 8.1.6, so I had to make sure I put it in both locations.

Thanks!! Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.