To enable oracle to directly call tables and views in sql server, oracle 11g transparent gateway can implement this function.
1、 Download oracleGetway
Download address: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
2、 The transparent gateway can be installed on the server where the sql server is located by decompressing and installing it directly on the Windows machine.
3、 Modify the configuration file after installation
First profile location: D:\product\11.2.0\tg_1\dg4msql\admin\initdg4msql.ora
The path is found according to the path you installed
Key configurations:
HS_FDS_CONNECT_INFO=[10.0.3.152]//xmgxy #The SQL server database IP address and database name will be configured during installation HS_FDS_TRACE_LEVEL=OFF #Log tracking, which can be set to ON, will generate log tracking HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER
Second profile location: D:\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora
Add the following:
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME=xmgxy) # sqlserver database name (PROGRAM=dg4msql) # dg4msql This is D: product 11.2.0 tg_1 dg4msql This directory name is generally unchanged (SID_NAME=dg4msql) # SID_NAME is D: product 11.2.0 tg_1 dg4msql admin initdg4msql.ora The file removes the symbol of init and is also the name of the connected instance (ORACLE_HOME = D:\product\11.2.0\tg_1) ) )
4、 Start listening
Start listening location: D:\product\11.2.0\tg_1\BIN\LSNRCTL. EXE
Directly open, stop first and then open
stop
start
that will do
You can see the instance dg4msql
Started
5、 Open the windows firewall, the default port is 1521, and the port is released in the inbound rules
6、 Oracle server operation
--The dblink statement for creating tables is as follows: the dblink connection name is ykt_dblink, the account number is sa, and the password is 123456. You can also write this file into the oracle listening file
create public database link ykt_dblink connect to sa identified by "123456" using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.0.3.152)(PORT = 1521)) (CONNECT_DATA=(SID=dg4msql)) # This SID is the SID_NAME of the configuration file, that is, the instance name (HS=OK) )'
--View the dblink statement
select t.* from dba_objects t where object_type='DATABASE LINK';
--Verify connection:
Select * from sqlserverXXX table @ dblink connection name
For example: select * from m_ykt_xfjl@ykt_dblink
Note: You must use "" to query a field, or an error "invalid identifier" will be reported
Reference article:
https://www.cnblogs.com/xqzt/p/5688659.html
https://jingyan.baidu.com/article/dca1fa6f42a662f1a44052a4.html