Configuration setting
|
The server |
operating system |
IP address |
|
SQL SERVER |
Windows 2003 |
10.9.5.47 |
|
Oracle DB |
Linux 5.8 |
10.9.5.90 |
|
Oracle Gateways |
1. Install transparent gateway
After Oracle 11G, Gateways under linux appeared. Before, only windows version appeared!
1.1 decompress gateway software
unzip p13390677_112040_Linux-x86-64_5of7.zip
1.2 install gateway software
./runInstaller
Note: Instance name and database name can not be written. Even after writing, the configuration file needs to be modified
1.3 run the root.sh file
1.4 create listening
Note: do not modify the listening name, otherwise the service cannot appear in static listening
Note: as long as the port is not 1521
2. Configuration parameter file
2.1 modifying SID parameter file
[oracle@test gateways]$ cd /home/oracle/app/oracle/product/11.2.0/gw_1/dg4msql/admin/ [oracle@test admin]$ ll total 28 -rw-r--r-- 1 oracle oinstall 10976 Nov 30 dg4msql_cvw.sql -rw-r--r-- 1 oracle oinstall 746 Jun 8 dg4msql_tx.sql -rw-r--r-- 1 oracle oinstall 355 Jul 23 11:08 initdg4msql.ora -rw-r--r-- 1 oracle oinstall 415 Jul 23 11:08 listener.ora.sample -rw-r--r-- 1 oracle oinstall 244 Jul 23 11:08 tnsnames.ora.sample [oracle@test admin]$ cp initdg4msql.ora initMSDB.ora [oracle@test admin]$ vi initMSDB.ora # This is a customized agent init file that contains the HS parameters # that are needed for the Database Gateway for Microsoft SQL Server # # HS init parameters # HS_FDS_CONNECT_INFO=[10.9.5.47]:1433//MSSQLSERVER/test # alternate connect format is hostname/serverinstance/databasename HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER
Note: MSSQLSERVER
Comments: test
Add these information when installing Gateway. It is shown in the configuration file as an error. I don't know if there will be such an error under windows
Add instance name and database name here, and the file shows yes
HS_FDS_CONNECT_INFO=[10.9.5.47]:1433//MSSQLSERVER
Note: MSSQLSERVER here refers to database name
Be careful:
Follow the prompt "alternate connect format is hostname/serverinstance/databasename"
It's OK to say no to databasename on the Internet, but it will report an error after testing
ORA-28500
2.2 modify the listener.ora of gw
[oracle@test admin]$ pwd
/home/oracle/app/oracle/product/11.2.0/gw_1/dg4msql/admin
[oracle@test admin]$ cat listener.ora.sample
# This is a sample listener.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4msql)
(ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/gw_1)
(PROGRAM=dg4msql)
)
)
#CONNECT_TIMEOUT_LISTENER = 0
--Copy the red font file to listener.ora
[oracle@test admin]$ vi ../../network/admin/listener.ora
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/gw_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.9.5.90)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=MSDB)
(ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/gw_1)
(PROGRAM=dg4msql)
)
)
ADR_BASE_LISTENER = /home/oracle/app/oracle
--MSDB Refer to/home/oracle/app/oracle/product/11.2.0/gw_1/dg4msql/admin/initMSDB.ora
2.3 restart monitoring
[oracle@test admin]$ export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/gw_1 [oracle@test admin]$ lsnrctl stop LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 23-JUL-2014 14:35:01 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.9.5.90)(PORT=1522))) The command completed successfully [oracle@test admin]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 23-JUL-2014 14:35:07 Copyright (c) 1991, 2011, Oracle. All rights reserved. Starting /home/oracle/app/oracle/product/11.2.0/gw_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.4.0 - Production System parameter file is /home/oracle/app/oracle/product/11.2.0/gw_1/network/admin/listener.ora Log messages written to /home/oracle/app/oracle/diag/tnslsnr/test/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.9.5.90)(PORT=1522))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.9.5.90)(PORT=1522))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production Start Date 23-JUL-2014 14:35:08 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /home/oracle/app/oracle/product/11.2.0/gw_1/network/admin/listener.ora Listener Log File /home/oracle/app/oracle/diag/tnslsnr/test/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.9.5.90)(PORT=1522))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) Services Summary... Service "MSDB" has 1 instance(s). Instance "MSDB", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully --If changed listener Name, service Cannot display, create DBLINK After that, the connection will report: ORA-28545
2.4 modify tnsnames.ora of db
[oracle@test admin]$ pwd
/home/oracle/app/oracle/product/11.2.0/gw_1/dg4msql/admin
[oracle@test admin]$ cat tnsnames.ora.sample
# This is a sample tnsnames.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent
dg4msql =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=dg4msql))
(HS=OK)
)
[oracle@test admin]$
vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
msdb =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=10.9.5.90)(PORT=1522))
(CONNECT_DATA=(SID=MSDB))
(HS=OK)
)
--Red characters copied to oracle Under directory tnsnames.ora
--Port number and gateway Port changed to consistent
--SID And gateway in initMSDB.ora agreement
2.5 verification configuration
[oracle@test ~]$ export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1 [oracle@test ~]$ tnsping msdb TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 23-JUL-2014 14:44:04 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=10.9.5.90)(PORT=1522)) (CONNECT_DATA=(SID=MSDB)) (HS=OK)) OK (0 msec)
2.6 create dblink
[oracle@test ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 23 14:44:59 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create database link mslink connect to oracle identified by oracle using 'msdb';
Database link created.
SQL> select * from t1@mslink;
id
----------
1
T1 Must be on MSSQL Medium test Database, otherwise:
ORA-00942: table or view does not exist
[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]??? 'T2'
??? {42S02,NativeErr = 208}[Oracle][ODBC SQL Server Wire Protocol
driver][Microsoft SQL Server]??????? {HY000,NativeErr = 8180}
ORA-02063: preceding 2 lines from MSLINK
3. Appendix
ddl operation is not allowed on remote database
If you need to configure to access multiple sql server databases, you can configure multiple init.ora files in the directory tg4msql, which specifies that the corresponding databases are configured in listener.ora and tnsnames.ora at the same time, and then establish the corresponding db links.