I'm getting confused with the steps involved in this because the Oracle documentation is sub-par ("HS_FDS_CONNECT_INFO=dns" is not an adequate example) and different blogs/forum posts saying different things.
I'm trying to connect from Oracle 220.127.116.11 on Linux(64) to a SQL Server 2008 R2 on x64 with a database named DBA_Housekeeping. Does anybody have a clear guide of the steps involved (and where)? Can anyone see what I'm doing wrong here?
Oracle hostname: gblabl76
SQL Server hostname: UKAHES066
All steps I've done are on the Oracle server. I haven't done anything on the SQL Server (am I supposed to install a client/drivers on there?):
1. created initDBA_Housekeeping.ora in $ORACLE_HOME/hs/admin/
HS_FDS_CONNECT_INFO = "UKAHES066\\UKAHES066.DBA_Housekeeping" HS_FDS_TRACE_LEVEL = OFF #HS_FDS_SHAREABLE_NAME =
2. Added something to listener.ora on the oracle server & then started the listener (said state UNKNOWN):
MSSQL = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = gblabl76)(PORT = 1523))) SID_LIST_MSSQL = (SID_LIST = (SID_DESC = (SID_NAME = DBA_Housekeeping) (ORACLE_HOME = /oracle/product/18.104.22.168/db_1) (PROGRAM = dg4odbc) ) )
3. Added a tnsnames entry:
DBA_Housekeeping = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (CONNECT_DATA = (SID = DBA_Housekeeping)) (HS=OK) ) )
4. Created a login on the SQL Server instance defaulting to this database. Then I created a database link in Oracle:
CREATE PUBLIC DATABASE LINK DBA_Housekeeping CONNECT TO "hkadm" IDENTIFIED BY "hkadm" USING 'DBA_Housekeeping';
But when I attempt to query the database over a db link I get the error listed. Can anybody see where I'm going wrong?
Do I need to install drivers on the Windows server (and does this require a reboot?)?
Do I need to create this DNS thing or is the string in the hs .ora enough?
Thanks for any guidance :s I'm hoping to connect to our SQL Server instances so they can be monitored in APEX.
as you're on Linux, did you install any foreign ODBC driver which is able to connect to the MS SQL Server?
DG4ODBC requires a 3rd party ODBC driver to work properly and the ODBC driver must be able to connect to the foreign database.
As you want to connect to MS SQL Server and use Dg4ODBC on Linux, please have a look at this web site:
Configure the ODBC according to the instructions from the MS web site (including the ODBC Driver Manager) and then you need to change the HS_FDS_CONNECT_INFO pointing to the ODBC DSN and make sure HS_FDS_SHAREABLE_NAME = refers to the unixODBC Driver Manager library (libodbc) which you will install when following the instructions on the MS Web site.
And now to the error - the 28545 error is a configuration issue. Let's assume you started the listener mssql after you configured it, then it listens on (HOST = gblabl76)(PORT = 1523) but in the tnsnames.ora you want to use the IPC protocol to connect to the listener although you configured TCP.
So you also have to change the tnsnames.ora to:
... but it will only work when you configure the ODBC driver correctly
Hi Klaus, I love you & Caroline together ^_^
Why does it require a driver manage before the driver? I had assumed DG4ODBC was all you needed :s
The driver that you linked to is license free then?
I'm thinking of doing a linked server in the opposite direction now since I believe it will be easier to set up. Have you tried it in the other direction (push with a linked server instead of pull from oracle)?
so let me provide you more background information now...
First ODBC: an ODBC driver is a tool that allows you to connect to a foreign database. It uses standard functions to do a connect, to query tables etc. and all these functions have to be implemented in the ODBC driver. Some of them like for example loading the odbc.ini file are always the same and it make sense to exclude them from the driver itself and to put it into a common tool - the ODBC Driver Manager.
Most of the 3rd party ODBC driver just contain their foreign database specific part and use an ODBC Driver Manager that gives you the generic ODBC functionality - an exception for example is the IBM DB2 ODBC driver => so when you want to connect using ODBC you commonly need an ODBC Driver and an ODBC Driver Manager.
Gateway: Oracle offers 2 gateways, a commercial gateway where you have to purchase a license for and a free gateway. The free Gateway is using ODBC (=DG4ODBC) and requires a working ODBC driver. The commercial Gateway for example is Database Gateway for MS SQL Server and contains everything you need to connect to a foreign MS SQL Server.
You selected DG4ODBC and installed it on Linux. So you need a working ODBC driver first and when you closely follow the instructions on the MS Web site you will get it to work. Another approach would be to install DG4ODBC on Windows and then connect from your Oracle database on Linux to DG4ODBC installed on Windows which then uses the ODBC driver of the MS SQL Server. That's the easiest way to configure DG4ODBC as on Windows you commonly have already a SQL Server ODBC driver installed.
Using MS linked Server nechanism works as well - you can for example install the instant client and use oledb and then link an Oracle database into the SQL Server.
Which solution would fit better? Well, it depends on the driving site. If you want to use the Oracle database as driving site to pull data from the SQL Server into Oracle or to push changes from the Oracle database to the SQL Server, the Oracle db needs to be the driving site. Same when the SQL Server will initiate to replicate the changes.
Anyone looking to connect Oracle & SQL Server I would like to give you the easiest solution to your problem.
Use a Linked Server from the SQL Server to Oracle (push instead of pull).
If you are just doing one server then you could do it directly. However, if you plan to use multiple servers then you should create a central SQL Server repository (it could be the free Express Edition) that will store the data from all your SQL Server databases and then forward them to Oracle.
The reason I made this initial post is because I want to store SQL Server monitoring data within our Oracle APEX system. I wasted a day trying to trawl through the net for accurate instructions for setting it up (before Klaus replied to me here). It took me less than an hour to select from & insert into an Oracle database in the opposite direction.
1. Install the Oracle client on the SQL Server instance
2. If the providers (under Linked Servers) doesn't list Oracle then reboot the server (ideally your new SQL Server repository); if it's there already then ignore this step.
3. Add a TNSNames entry for your new Oracle DB
4. Create a linked server with the oracle login details
That's all that is needed for a working solution. Yesterday I had nothing, today I have a link going:
Numerous MSSQL isntances -> MSSQL repository -> Oracle APEX repository