No question at this time
DBA Top 10
1 A. Kavsek 12400
2 M. Cadot 7300
3 B. Vroman 6400
4 P. Wisse 5600
5 J. Péran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 A. Chavan 300
9 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48458
Total active users1487
Act. users last 24h2
Act. users last hour1
Registered user hits last week190
Registered user hits last month829
Go up

Issue connecting Oracle 11g to SQL Server 2012 — ORA-12523: TNS:listener could not find instance app
Next thread: migrate database from rhel6 to solaris 10 sparc
Prev thread: Fetching Recording of between Dates

Message Score Author Date
I have Oracle 11g and SQL Server 2012 installed o...... Shahzaib Khan Apr 25, 2017, 09:33
Dear Shahzaib, please check following links. ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Mirza Hidayathullah Baig Apr 25, 2017, 11:24
Hi, based on an old note (<i>from a previous de...... Ales Kavsek Apr 25, 2017, 17:20

Follow up by mail Click here


Subject: Issue connecting Oracle 11g to SQL Server 2012 — ORA-12523: TNS:listener could not find instance app
Author: Shahzaib Khan, Pakistan
Date: Apr 25, 2017, 09:33, 957 days ago
Os info: Windows Server 2012
Oracle info: Oracle 11g
Error info: ORA-12523: TNS:listener could not find instance appropriate for the client connection
Message: I have Oracle 11g and SQL Server 2012 installed on the same virtual machine. OS is Windows Server 2012. I want to access SQL Server 2012 from Oracle. I created the machine a few days back to test the connection process before applying it on server.

I had come through several issues but now I am stuck with this one.

ORA-12523: TNS:listener could not find instance appropriate for the client connection

I have already created ODBC Connection by the name of sqldb.

kshaz is my machine name.

Following are my configuration details.

listener.ora file

# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
(SID_DESC =
(SID_NAME = sqldb)
(ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = hsodbc)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = kshaz)(PORT = 1521))
)
)

ADR_BASE_LISTENER = C:\app\Administrator

DIRECT_HANDOFF_TTC_LISTENER = OFF

tnsnames.ora file

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
SQLDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sqldb)
)
)

sqlnet.ora file

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

initsqldb.ora file

HS_FDS_CONNECT_INFO = sqldb
HS_FDS_TRACE_LEVEL = off

I am running the following query.

select * from emptest@sqld

emptest is the table created at SQL Server 2012 and SQLD is the public database link.

Where have I gone wrong with the configuration? Or if there is any step that I have missed?

Thank you.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Issue connecting Oracle 11g to SQL Server 2012 — ORA-12523: TNS:listener could not find instance app
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Apr 25, 2017, 11:24, 957 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Dear Shahzaib,

please check following links.

http://www.databasejournal.com/features/oracle/article.php/3442661/Making-a-Connection-from-Oracle-to-SQL-Server.htm

http://docs.oracle.com/cd/E11882_01/server.112/e17766/net12500.htm

http://dbaclass.com/article/create-database-link-from-oracle-to-sql-server/

Regards
Mirza
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Issue connecting Oracle 11g to SQL Server 2012 — ORA-12523: TNS:listener could not find instance app
Author: Ales Kavsek, Slovenia
Date: Apr 25, 2017, 17:20, 956 days ago
Message: Hi,

based on an old note (from a previous decade;-) I think you followed some 10g recipe and you're using 11g....


In listener.ora replace

(PROGRAM = hsodbc)

with

(PROGRAM = dg4odbc)


From >=11g dg4odbc replaced hsodbc. SQLDB specification in tnsnames.ora doesn't look good either....try with:

SQLDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID_NAME = sqldb))
(HS=OK)
)

Note the SID_NAME instead of service_name and added HS=OK parameter. Our configuration was a bit different so I'm not sure if this will fix the your problem. Good luck!

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here