-
Oracle to SQL Server Connection
Hi,
A little while ago I followed the article below to successfully create an Oracle to SQL Server connection.
http://www.dbasupport.com/oracle/ora...ection01.shtml
I know have to create a second connection (on the same server). I followed the steps again, but am not able to select records.
The error I get is:
ora-12154: TNS: could not resolve connect identifier
I have checked my Listener.ora and tnsnames.ora files and they are correct. I can start the new listener and tnsping to the new connection.
I also have 2 init......ora files (initSQLSERV.ora & initNEW.ora)
Can there be 2 init.....ora files?
I also have 2 database links.
My DSN are also set up as recommeneded by the article. And get a successful connection.
Any help on having multiple Oracle to SQL Server connections would be helpful.
Many thanks in advance.
-
Best practice
Originally Posted by Rikesh_p
Blah, blah, blah...
I also have 2 init......ora files (initSQLSERV.ora & initNEW.ora)
Can there be 2 init.....ora files?
Only if you start the database with the CORRECT one, for example: STARTUP PFILE=init????.ora
It is best practice to start the database using either the default init{SID}.ora or spfile{SID}.ora files.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Thanks for your response.
I believe that the Oracle database uses the normal init.ora file, however the initSQLSERV and initNEW files I have created are not related as they are situated in an entire different location.
Otherwise the issue has now been resolved. There was a error in one of my database links. It was not connecting to the correct user that was stated in the System DSN.
-
Originally Posted by Rikesh_p
ora-12154: TNS: could not resolve connect identifier
It looks to me this is a TNSNAMES.ora issue.
Be sure you have a single TNSNAMES.ora file - rename all others - and be sure you have a valid tnsnames entry for target Oracle database.
To test IP connectivity to target host do this...
ping machine_name used in tnsnames.ora
If it doesn't work try actual IP Address then use IP Address in tnsnames entry.
To test tns connectivity to target instance do this...
1- Set ORACLE_HOME
2- Set ORACLE_SID
3- tnsping ALIAS name used in tnsnames.ora file
Hope this helps.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Connectivity from Sql server side easier
I noticed that using "Linked Server" which connects from the Microsoft end is an easier to setup and use link. However, in this case the link is from the Sql server end and you will be able to query oracle tables there and not vice versa.
I am an Oracle fan, but think about it, why make all this fuss of manual changes in listener/tnsnames and activation of HS (Heterogenous Services) features instead of doing it at the click of a button. My guess is that they should be including this in the latest OEM version in lines similar to the Linked Server in Microsoft.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|