Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1595
  • Last Modified:

DBlink Oracle to SQL-Server

Hi.

It is possible to create a dblink from Oracle TO Sqlserver? there is any special attention required in this case? It's just putting a command in sqlplus and this is all? How many informations need I to do this?
Could someone give me an example and some tips for this?
0
Thiago_corporative
Asked:
Thiago_corporative
  • 5
  • 2
  • 2
  • +2
1 Solution
 
Eugene ZCommented:
yes, it is possible
see:

                 Database link from Oracle to SQL Server
http://www.dba-oracle.com/t_database_link_sql_server_oracle.htm
0
 
BobMcCommented:
You don't mention your OS, but here are some details from Windows. The method is the same as posted by EugeneZ, but with a few more diagrams

http://www.databasejournal.com/features/oracle/article.php/10893_3442661_1/Making-a-Connection-from-Oracle-to-SQL-Server.htm
0
 
Thiago_corporativeAuthor Commented:
Even after all the steps in that tutorial, I'm finding an error when try TNSPING string_name...

I followed all the tutorial steps. All that seems works fine. See:

On server:
The odbc source on the server that has sqlserver connects with this one successfully.
The ODBC initfile was configurated like told on tutorial.
Listener too.... and the listener goes up successfully.

On oracle client that will try to see sqlsever db:
TNSNAMES configurated like tutorial steps

Now, the problem:

when I put tnsping string_name on client, I cannot see the server, even with the listener configurated.

so, I went to server, to make a test.... then I've configurated tnsnames with an entry to it own odbc source. So, I put, again, tnsping string_name.

In both, message error:

TNS-12537: TNS: connection closed.

Please, any idea?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
gatorvipCommented:
Are both servers running on the same network? Is there a firewall between the two?

>>Listener too.... and the listener goes up successfully.

How do you know this is successful?

>>In both, message error:TNS-12537: TNS: connection closed.

Does any other remark follow the TNS-12537 error?
0
 
Thiago_corporativeAuthor Commented:
Yes, it are on the same network without a firewall.
I've try to start listener by command line and works fine.
This is the only message that appears
0
 
Mark GeerlingsDatabase AdministratorCommented:
In the hs\admin folder of your Oracle_home, did you copy the default "inithsodbc.ora" file to: "init[DNS_NAME].ora'?  And did you change these two lines in it from:
HS_FDS_CONNECT_INFO = <odbc data_source_name>
HS_FDS_TRACE_LEVEL = <trace_level>
to:
HS_FDS_CONNECT_INFO = [DNS_NAME]
HS_FDS_TRACE_LEVEL = 0


0
 
Thiago_corporativeAuthor Commented:
Yes, I did... but the HS_FDS_TRACE_LEVEL = 0 is set to OFF, not 0... I've readed tutorials on internet and all of them set this parm to OFF... is that wrong?
0
 
Mark GeerlingsDatabase AdministratorCommented:
I don't know.  We had it set to 0 (zero) in our system , and that worked for us.  Our link was not to SQL Server, it was actually to a non-SQL-based (VAX-VMS) system, but it did use an ODBC connection to a third-party gateway product (called CONNX) that supported links between different systems. We were able to create a database link in our Windows-based, Oracle9 database that worked wonderfully for us.
0
 
BobMcCommented:
Trace level 0 or OFF should both work - they both equate to 0.

OK, so the ODBC test from the server works. Thats a good start..
Without seeing your listener.ora, this is guesswork, but here are a couple of things I would look for:

Check your listener.log for any additional information. There may also be a sqlnet.log generated, but its location may vary.

Run the following from your server - if your listener is called LISTENER, you can omit the listenername
lsnrctl status <listenername>
- this should show a service referenced in the connect_data portion of the listener.ora

lsnrctl services <listenername>
- this should show some more details - check the "Connecting to (ADDRESS..." portion is what you have in your tnsnames.ora

Change the listener.ora to reference localhost or 127.0.0.1 rather than the servername. You could also do this in the tnsnames.ora on the server itself for testing purposes (but not obviously on your remote client)

You also also try changing the port number in your listener.ora for the hs service (and also in your tnsnames.ora)

If none of this throws up any new information that will assist in tracking down the problem, I would suggest a server side sqlnet trace by setting TRACE_LEVEL_CLIENT=ADMIN in your sqlnet.ora on the server. Be warned though, this could generate some pretty large files very quickly, and will slow all your connections down, as it has to write the details out to disk. Turn it off again as soon as you have ran the test, otherwise you'll never find the details in the log it generates!

0
 
Thiago_corporativeAuthor Commented:
Ok,

Let's suppose that my odbc connection name is SQLSERVER

this is how my tnsnames is configurated for the odbc:

SQLSERVER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1433))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SQLSERVER)
    )
    (HS=OK)
  )

And here is my listener, is that right???

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = SQLSERVER)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = hsodbc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1521))
    )
  )
0
 
Thiago_corporativeAuthor Commented:
This is how my connection is set on tnsnames

SQLSERVER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SQLSERVER)
    )
    (HS=OK)
  )

so, if I use port 1521, I can see the response on tnsping. But, if I change the port to 1433, and change on odbc too, the tnsping don't give me any response for this connection. The problem is that 1521 is the same port for oracle. So, when I set to this port, stop and restart listener, a problem occurs, of course, then I back to the old, 1433, but never work...
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now