2. Once the ODBC Driver is installed we will need to configure a data source for the non-Oracle database. For Windows open the control panel and locate the Administrative Tools. Then locate the Data Sources (ODBC) and double click on the Data Sources.
Select the System DSN as for the HS service it will need to be a System DSN then Click on Add button.
Select the Driver for the non-Oracle data source. In this case we will be using the MySQL 3.51 Driver for a local MySQL database.
In this case we will give this data source the name of mysql configured on the localhost, we will be connecting with the root user. In this case we are on the default port therefore we done have to configure the connect options.
3. After configuring the connection settings we can test if the connection is working by pressing the test button near the bottom left of the Window. A window will indicate if the connection was successful.
4. Configure the init.ora for the HS service that well be used for the non-Oracle database connectivity.
initmysql.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
#HS_DB_NAME = mysql
HS_FDS_CONNECT_INFO = mysql
HS_FDS_TRACE_LEVEL = ON
#
# Environment variables required for the non-Oracle system
#
#set
5. Need to add the non-Oracle data source to the listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
)
(SID_DESC=
(SID_NAME=mysql)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
(ENVS=LD_LIBRARY_PATH = C:\oracle\product\10.2.0\db_1\lib32)
)
)
6. Reload the listener and check that we have a service for the non-Oracle data source.
C:\Oracle\product\10.2.0\db_1\BIN> lsnrctl reload
LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 10-AUG-2008 19:14:21
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
7. Check the listener services to make sure the mysql shows
C:\Oracle\product\10.2.0\db_1\BIN> lsnrctl services
LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 10-AUG-2008 19:14:33
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
Services Summary...
Service "ORCLXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxxxxxx)(PORT=2323))
Service "ORCL_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "mysql" has 1 instance(s).
Instance "mysql", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
8. Need to now add the non-Oracle data source to the tnsnames.ora
mysql =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx)(PORT = 1521))
(CONNECT_DATA =
(SID = MYSQL)
)
(HS=OK)
)
9. Check that the non-Oracle data source is reachable via a tnsping.
C:\Oracle\product\10.2.0\db_1\BIN>tnsping mysql
TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 14-DEC-2008 18:12:46
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxx)(PORT = 1521)) (CONNECT_DATA = (SID = MYSQL)) (HS=OK))OK (30 msec)
10. Create a public database link and check that it works.
C:\Oracle\product\10.2.0\db_1\BIN>sqlplus /@orcl
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 10 20:10:00 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create public database link mysql connect to "root" identified by "xxxxxxxxxx"
using 'mysql' ;
Database link created.
SQL> select count(*) from alfresco.alf_node@mysql ;
COUNT(*)
----------
1469
SQL>
