Oracle透過gateway連線sqlserver2008
環境
oracle 10.2.0.1 ip:xxx.xxx.6.26 windows
sqlserver 2008 ip:xxx.xxx.6.49 DB:oracle
1.gateway11g 安裝在xxx.xxx.6.26上面,和oracle安裝在同一臺機器上
1).點選setup
2).輸入安裝路徑
3).選擇"Oracle Database Geteway for Microsoft Sql Server 11.2.0.1.0"
4).輸入SQL Server相關資訊
結束之後,配置資訊在E:\oracle\product\11.2.0\tg_1\dg4msql\admin\initdg4msql.ora
HS_FDS_CONNECT_INFO=xxx.xxx.6.49:1433//oracle
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_INFO的格式是:
HS_FDS_CONNECT_INFO=sqlserver ip:portal//sqlserverDB
2.配置gateway11g的listener
E:\oracle\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = E:\oracle\product\11.2.0\tg_1)
(PROGRAM = dg4msql)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.6.26)(PORT = 1621))
)
ADR_BASE_LISTENER = E:\oracle\product\11.2.0\tg_1
3.配置oracle的tnsnames.ora
MSSQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.6.26)(PORT = 1621))
(CONNECT_DATA =(SID = dg4msql))
(HS = OK)
)
C:\Users\is_long_li>tnsping mssql
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-12月-2015 13:22:07
Copyright (c) 1997, 2005, Oracle. All rights reserved.
已使用的引數檔案:
E:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.6.26)(PORT = 1621)) (CONNECT_DATA =(SID
= dg4msql)) (HS = OK))
OK (40 毫秒)
如股票出現 TNS-12557:協議介面卡不可載入
需要將環境變數中的E:\oracle\product\11.2.0\tg_1\BIN,刪除了就可以了。
4.建立db link
C:\Users\is_long_li>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 12月 28 13:10:37 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create database link mssql connect to "sa" identified by "sa123" using 'mssql';
資料庫連結已建立。
5.測試
在sqlserver中create table,insert data
create table tt(id int,name varchar(10))
insert tt values(1,'a');
insert tt values(2,'b');
insert tt values(3,'c');
insert tt values(4,'d');
insert tt values(5,'e');
在oracle查詢相關資料
SQL> select * from tt@mssql;
id name
---------- ------------------------
1 a
2 b
3 c
4 d
5 e
oracle 10.2.0.1 ip:xxx.xxx.6.26 windows
sqlserver 2008 ip:xxx.xxx.6.49 DB:oracle
1.gateway11g 安裝在xxx.xxx.6.26上面,和oracle安裝在同一臺機器上
1).點選setup
2).輸入安裝路徑
3).選擇"Oracle Database Geteway for Microsoft Sql Server 11.2.0.1.0"
4).輸入SQL Server相關資訊
結束之後,配置資訊在E:\oracle\product\11.2.0\tg_1\dg4msql\admin\initdg4msql.ora
HS_FDS_CONNECT_INFO=xxx.xxx.6.49:1433//oracle
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_CONNECT_INFO的格式是:
HS_FDS_CONNECT_INFO=sqlserver ip:portal//sqlserverDB
2.配置gateway11g的listener
E:\oracle\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = dg4msql)
(ORACLE_HOME = E:\oracle\product\11.2.0\tg_1)
(PROGRAM = dg4msql)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.6.26)(PORT = 1621))
)
ADR_BASE_LISTENER = E:\oracle\product\11.2.0\tg_1
3.配置oracle的tnsnames.ora
MSSQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.6.26)(PORT = 1621))
(CONNECT_DATA =(SID = dg4msql))
(HS = OK)
)
C:\Users\is_long_li>tnsping mssql
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 28-12月-2015 13:22:07
Copyright (c) 1997, 2005, Oracle. All rights reserved.
已使用的引數檔案:
E:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.6.26)(PORT = 1621)) (CONNECT_DATA =(SID
= dg4msql)) (HS = OK))
OK (40 毫秒)
如股票出現 TNS-12557:協議介面卡不可載入
需要將環境變數中的E:\oracle\product\11.2.0\tg_1\BIN,刪除了就可以了。
4.建立db link
C:\Users\is_long_li>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 12月 28 13:10:37 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create database link mssql connect to "sa" identified by "sa123" using 'mssql';
資料庫連結已建立。
5.測試
在sqlserver中create table,insert data
create table tt(id int,name varchar(10))
insert tt values(1,'a');
insert tt values(2,'b');
insert tt values(3,'c');
insert tt values(4,'d');
insert tt values(5,'e');
在oracle查詢相關資料
SQL> select * from tt@mssql;
id name
---------- ------------------------
1 a
2 b
3 c
4 d
5 e
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24237320/viewspace-1965861/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 19c透過SCAN連線ORA-12520Oracle
- C#/C++ 透過ODBC連線OceanBase Oracle租戶C#C++Oracle
- 如何透過DBeaver 連線 TDengine?
- oracle資料庫透過sqlplus連線的幾種方式介紹Oracle資料庫SQL
- Oracle透過ODBC連線SQL Server資料庫後ORA-12514OracleSQLServer資料庫
- adb透過wifi連線手機WiFi
- mac透過網線連線主機(fnOS)Mac
- [Oracle-> MySQL] Oracle通過dblink連線MySQL--Oracle 19c連線到MySQL 5.7OracleMySql
- JDBC無法用SSL連線SqlServer2008的解決方法JDBCSQLServer
- 樹莓派透過nmcli命令連線WIFI樹莓派WiFi
- SSH 透過跳板機連線目標機
- 如何設定透過TeamViewer連線群暉NASView
- gRPC(五)進階:透過TLS建立安全連線RPCTLS
- cx_Oracle 連線 OracleOracle
- 使用 Java 客戶端透過 HTTPS 連線到 EasysearchJava客戶端HTTP
- 【LISTENER】Oracle通過監聽連線緩慢分析Oracle
- Servlet連線OracleServletOracle
- PHP 連線 OraclePHPOracle
- thinkphp連線OraclePHPOracle
- PHP 連線oraclePHPOracle
- 釘釘如何透過AppLink快速連線倉儲系統APP
- 透過RestHighLevelClient相容模式連線elasticsearch8.x(支援https)RESTclient模式ElasticsearchHTTP
- OceanBase學習之路6|透過 Obclient 連線 OceanBase 租戶client
- Zabbix透過Orabbix監控OracleOracle
- oracle 連線查詢Oracle
- DBA ORACLE連線操作Oracle
- 3.2.2 python連線oraclePythonOracle
- pycharm透過ssh連線伺服器並上傳專案PyCharm伺服器
- Oracle DBLink連線數過多的問題(Ora-02020)Oracle
- 透過LinkExtractor類提取連結
- Oracle左外連線、右外連線、完全外連線以及(+)號用法Oracle
- Oracle連線Db2OracleDB2
- Fo-DICOM透過storescu.exe確認CT連線型別型別
- elastic學習-本地kibana透過安全證書連線遠端elasticsearchElasticsearch
- 解決線上Oracle連線耗時過長的問題現象RPYBOracle
- java操作Oracle 方式一 ( 連線-》操作-》斷開連線 )JavaOracle
- 【JDBC】java連線池模擬測試 連線oracleJDBCJavaOracle
- 【JDBC】使用OracleDataSource建立連線池用於連線OracleJDBCOracle
- 一文讀透HTTP的長連線和短連線HTTP