Oracle - ODBC connection using MS Access error (ORA-12154)

Question

I am trying to use MS access to connect to a Oracle database. I keep on getting the following error message:

ORA-12154: TSN- could not resolve the connect identifier secified

The Oracle Drivers OracleClient10g can verify that the database server exists.

I have a section in my tsnnames.ora file that looks like this: UBASEP10G = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = bxxx-xxx.yyyy.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = UBASE) ) )

per my attempts to get this error resolves I added this to the sqlnet.ora file:

NAMES.DIRECTORY_PATH= (HOSTNAME, ONAMES, TNSNAMES,LDAP,EZCONNECT)

When using the Windows ODBC driver configuration utility it asks for the following following information DATA SOURCE NAME : MYSOURCE NAME TSN SERVICE NAME:UBASEP10G USERID:MYUSERID

any suggestions ?????

Answer 1

I don't have Access, but using Excel 2007, I had to do the following:

1. Open ODBC Administrator (in the Administrator Control Panel)
2. For either User DSN or System DSN, click Add...
3. Select "Oracle in OraDb10g_home1" as the driver
4. In the Oracle ODBC Driver Configuration, I entered:
   • Data Source Name: myOracleDsn
   • Description: This is my DSN for my Oracle Database
   • TNS Service Name: oratns
   • User ID: scott
5. Click Test Connection, and enter "tiger" for the password. Obviously, I'm connecting to my scott/tiger sample Oracle database. Also, when I installed Oracle, I picked oratns as my TNS name. The other two values above are arbitrary -- you'll see them in dialogs in Excel.
6. If the test works, click OK.
7. Go to Excel and make a new connection to a DSN. The dialog will show "myOracleDsn" as an option. Select it, enter "tiger" for the password again, and you should get a working connection.

Answer 2

Try changing (CONNECT_DATA = (SERVICE_NAME = UBASE) ) to (CONNECT_DATA = (SID = UBASE) ) in your TNSNAMES.ora file.

ServiceName and SID aren't necessarily the same and consequently aren't always interchangeable.

The SERVICENAME parameter refers to a particular GLOBAL_DBNAME specified in the listener.ora file on the database server. It's an alias for an instance on the server. You can have multiple servicenames on a server referring to the same SID. The SID parameter refers to a particular instance on that server.

The advantage of using servicename on the client side is that the DBA can change the actual instance being referenced by a servicename transparently to the clients using that name. I can have this on the server listener.ora file:

(SID_DESC =
  (GLOBAL_DBNAME = THESERVICE)
  (ORACLE_HOME = d:\oracle\10.2.0_DB)
  (SID_NAME = SID1)

Later, I can change the actual database being referenced by switching the listener.ora configuration:

(SID_DESC =
  (GLOBAL_DBNAME = THESERVICE)
  (ORACLE_HOME = d:\oracle\10.2.0_DB)
  (SID_NAME = SID2)

and nobody's the wiser on the client side. No changes were necessary in the tnsnames.ora files on the clients.

Answer 3

Can you log in to the database in question via SQL*Plus? Doing this from another machine with a working connection (or the DB server itself) is fine also.

If so, run this:

select value from v$parameter where name='service_names';

In your TNSNAMES.ORA, use one of the values listed there for the SERVICE_NAME.

In you ODBC connection, all you'll need is to set the TNS Service Name to the name you used above, "UBASEP10G"

Answer 4

Let's back up to square one. Open a command window and connect to your database:

sqlplus myuserid/mypassword@UBASEP10G

Does this connect successfully?

Since the answer is no, is there a way you CAN connect successfully to this database? BQ is correct, your problem is with the servicename of UBASE. You need to determine what the listener on the server thinks the name of that database is. Do you have access to the server? Can you execute the command "lsnrctl status" on the server? This will tell you the services that are registered with the listener, and look something like this:

Services Summary...
Service "UBASE" has 1 instance(s).
  Instance "UBASE", status READY, has 1 handler(s) for this service...

Answer 5

Try tnsping and report your results.

Bad:

C:\>tnsping notreal.world

TNS Ping Utility for 32-bit Windows: Version 9.2.0.5.0 - Production on 29-OCT-2008 15:56:47

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

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

TNS-03505: Failed to resolve name

Good:

O:\>tnsping real.world

TNS Ping Utility for 32-bit Windows: Version 9.2.0.5.0 - Production on 29-OCT-2008 15:57:42

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:

C:\oracle\ora92\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = DBSERVER.DOMAIN.COM)(PORT = 1521)) (LOAD_BALANCE = YES) (FAILOVER = YES))
(CONNECT_DATA = (SERVICE_NAME = REAL.WORLD) 
(FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 10) (DELAY = 3))))
OK (40 msec)