Method 1) Oracle connect to Sql server
Using Oracle Gateway in 11g, if the database version is 10g, patch 5965763 should be installed on it.
Step1: Install Oracle gateway at an existing Oracle 11g home or a new home, and configure listener, oracle gateway for Sql server initial file:
In our example, using new ORACLE_HOME /u01/app/oracle/product/11.1.0/db_1
1) listener.
/u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
LISTENER_11G =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = g1u0872.wordpress.com)(PORT = 1526))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1526))
)
)
SID_LIST_LISTENER_11G=
(SID_LIST=
(SID_DESC=
(SID_NAME=dg4msql)
(ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1)
(ENVS=LD_LIBRARY_PATH=/u01/app/oracle/product/11.1.0/db_1/dg4msql/driver/lib:/u01/app/oracle/product/11.1.0/db_1/lib)
(PROGRAM=dg4msql)
)
)
2) init<SID>.ora of the hs subsystem
/u01/app/oracle/product/11.1.0/db_1/dg4msql/admin/initdg4msql.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=cceap0016.wordpress.com:2048//Crystal
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=ODBC
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_SUPPORT_STATISTICS=FALSE
#Added for performance improvment
HS_RPC_FETCH_SIZE=50000
HS_ROWID_CACHE_SIZE=10000
HS_LONG_PIECE_TRANSFER_SIZE=1MB
3) Start listener
lsnrctl start LISTENER_11G
Step2. Patch existing oracle 10g database home with patch 5965763.
Step3. Configure tnsname and create db link in oracle 10g
1) Add tnsname entry.
/u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
CRYSTAL_ITG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = g1u0872.wordpress.com)(PORT = 1526))
(CONNECT_DATA =
(SID = dg4msql))
(HS=OK)
)
2) Create db link
Sql> Create public database link CRYSTAL_ITG
connect to PRISM identified by passwd
using ‘crystal_itg’;
Step4. Test
SQL> select count(*) from “Book”@CRYSTAL_ITG;
COUNT(*)
———-
562
Method 2) Sql server connect to Oracle
Using Sql server linked server in version 2005.
Step1. Install Oracle client in windows and configure TNS names
D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
PROVT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = g1u0854.wordpress.com)(PORT = 1525))
)
(CONNECT_DATA =
(SID = PROVT)
)
)
Step2. Configure Linked server in Sql server
Follow these click steps, Connect to Sql server in Microsoft Sql Server Management Studio => Server Objects => Linked Servers => right click, New Linked Server =.>
Add default login name and password.
Step3. Test connection.
Filed under: Configuration Management
