HI guys, recently, I have to read the data in MS SQL2008 into the oracle11gr2. At the first glance, I thought about the GateWay.
there are two servers in my team(run on vmware), which are:
server A: 192.168.184.130, winserver2003, 64 edition, MS server2008 installed, SP2 installed
server B: 192.168.184.134, winserver2003, 64 edition, oracle11gr2 and gateway installed, SP1.
on server A, the table of msdb.dbo.user_sqlserver2008 is the one the Oracle plans to read.
on server B, the ORACLE_HOME is C:\app\Administrator\product\11.2.0\dbhome_1, and the GATEWAY_HOME is C:\product\11.2.0\tg_1, both are default directories.
after the gateway component was installed, I configured the following files:
1. created C:\product\11.2.0\tg_1\dg4msql\admin\initmsdb.ora, the contents in this file are:
# 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=192.168.184.130//msdb
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
2. compiled the file of C:\product\11.2.0\tg_1\NETWORK\ADMIN\listener.ora, and the contents are:
# listener.ora Network Configuration File: C:\product\11.2.0\tg_1\network\admin\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = msdb)
(ORACLE_HOME = C:\product\11.2.0\tg_1)
(PROGRAM = dg4msql)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.184.134)(PORT = 1523))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
)
)
ADR_BASE_LISTENER = C:\product\11.2.0\tg_1
3. modified the tns file in C:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN, namely tnsnames.ora, the contents are:
# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.184.134)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
msdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.184.134)(PORT = 1523))
)
(CONNECT_DATA =
(SID = msdb)
)
(HS = OK)
)
after that, restarted the listeners for gateway and for orcl instance and the orcl instance as well, then on server B, logon as the user msdb possessing the resource and dba role,(what's more, there is another user id msdb on sqlserver2008, who is the owner of msdb database)
ran the following sql script via plsql:
1. create database link msdb connect to msdbadmin identified by "oracle_4u" using 'msdb';
2. select * from dbo.user_sqlserver2008@msdb
the script No.1 was successfully completed, while an error message was got in script 2. the message is:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][[ODBC SQL Server Driver][DBNETLIB]SQL Server dose not exist or access denied. {08001, NativeErr = 17}[Oracle][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()). {01000, NativeErr = 2}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute{01S000}
ORA-02063: preceding 2 lines from MSDB.
Could anyone familiar with this help me figure this out? Thanks, it made me crazy.