Usage Scenario: When you need to access data from an Oracle database on another SQL Server database, Oracle provides a tool: gateways. With this tool, you can create dblink to connect to SQL Server or a different company's database----depending on your installation options.
After installing gateways, use the following 2 ways to create Dblink
Mode A:
Create DATABASE link Bslink
Connect to ' username ' identified by "password"
Using ' (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = Remoteip) (PORT = 1433))
(Connect_data = (
SID = SQL Server database name) (Hs=ok)) '
This approach is not required to configure Initdg4msql.ora and TNSNames.
mode B:
1. In the $oracle_home/dg4msql/admin/initdg4msql----This step can be omitted using mode a instead
Configure Initdg4msql.ora to my example, as follows: ******************************
# this isA Customized agent InitfileThatcontainsThe HS parameters# that is needed forTheDatabaseGateway forMicrosoft SQL server## HS init parameters#hs_fds_connect_info=[192.168.101.4]//Bsdata----only need to modify this ip//database nameHs_fds_trace_level=OFFHs_fds_recovery_account=recoverhs_fds_recovery_pwd=RECOVER
2. Configure Listener.ora under the Network\admin directory in the Oracle home directory. In my case, the following:
# LISTENER. ORA Network configuration file:c:\oracle\ora92\network\admin\listener.ora# Generated by Oracle configuration tools. LISTENER = ----Here LISTENER is named LISTENER (description_list = (DESCRIPTION = ( address_list = ( ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.180) (PORT = 1521)))) Sid_list_listener = -----here sid_list _lintener name Ibid. listener (sid_list = (Sid_desc = (sid_name = dg4msql) (oracle_home = D:\app\oracle\) Product\11.2.0\dbhome_1) (program = dg4msql) ----Here program corresponds to Dg4msql in TNSNames ) )
3. Configure the Tnsnames.ora in the Network\admin directory under the Oracle home directory, in my case, as follows:
Dg4msql = ---Ibid correspondence (description= (address= (protocol=tcp) (host= localhost) (port=1521)) (Connect_data= (Sid=dg4msql)) ( Hs=ok) ) ******************************
Oracle uses Dblink to connect to SQL Server