The idea is to create a Link between a ‘source’ database and a ‘target’ database so that, the client is able to query from the target, without having to configure the additional network connectivity. This is especially useful when there are many ‘targets’, plus many clients involved which would be a hassle to for example, edit the tnsnames for each client with individual entries for each target.
Instead, The tnsnames for all targets can be added in the source database and then a link may be created between the source and each of the targets. The client can then run a query normally, and by simply appending an extra word (dblink name) is able to query the target for which that link had been created!
Linking from an Oracle db to a non oracle db essentially involves using Heterogenous services, which is included with the database installation. For 10g and below, the program is ‘hsodbc’, and for 11g it’s ‘dg4odbc’. It can be found at $ORACLE_HOME/dg4odbc.
Firstly, the ODBC driver for that target db needs to installed on the source machine.
In this case, the source Oracle db ois on a unix machine. The host OS of the target dbs is … irrelevant.
1. Data Direct is the source from which the ODBC drivers can be obtained. Note that the trial is free but can only be used for 15 days. Also, registration is required before getting access to the download link🙂
2. Create a new directory on the ‘source’ machine (ODBCHOME for example). This would be the primary location of the software files, including odbc.ini
3. Download the driver package to the source system. The package will usually be in a compressed .gz form. Uncompress it and run unixmi.ksh
4. There are a number of prompts, most of which can be defaulted. At the software installation directory however, specify the path for the ODBCHOME directory.
5. When prompted for a Key or a Serial Number, enter ‘EVAL’ for the trial version.
6. Once the installation is successful, navigate to the ODBCHOME directory and open the file ‘odbc.ini’
7. This file is where the connection information is configured for each target. For starters, there’s two headers – [odbc data sources] and [Name of the data source]. There’s one example data source under the first header, and the list of parameters under the second.
8. To add a new data source, enter a name and specify the driver. For example,
[ODBC data sources]
dblink_orcl2mssql = Datadirect 7.1 SQL Server legacy wire protocol
9. Note the second header i.e. parameters for the data source. A separate header is defined for each target, with the header name being the data source name as defined earlier.
10. Edit the parameters for the target databases, specifically the database name, ip address, port, username and password.
11. Once the target data source(s) are configured, save and exit
That was only the tip of the iceberg. For the next part, the listener.ora and tnsnames.ora will be edited to include these newly created data sources. To do so, dg4odbc needs to be invoked.
12. Edit listener.ora to include the data source(s) as below:
(SID_NAME = dblink_orcl2mssql)
(ORACLE_HOME = ...)
(ENVS = ....)
(PROGRAM = dg4odbc)
13. Reload the listener for the changes to take effect.
$ lsnrctl reload LISTENER_NAME
14. Likewise, edit tnsnames.ora to include the data sources
(ADDRESS = (PROTOCOL = TCP) (HOST = ...) (PORT = ...))
(CONNECT_DATA = (SID = dblink_orcl2mssql))
Note the (HS=OK), which is required to enable heterogeneous services.
15. Once the tnsnames file is saved, verify the connectivity using TNSPING
$ tnsping DBLINK01
Next, creating the parameter file init<SID>.ora for each data source / dblink
16. In the $ORACLE_HOME/hs/admin directory, a few sample init files are included, such as initDG4ODBC.ora. For each data source, an init<SID>.ora file needs to be created and configured.
17. In this case, create a new file init<SID>.ora or copy an existing init.ora file and rename it.
18. Edit the parameters, specifically the first one i.e. HS_FDS_CONNECT_INFO. This parameter must be assigned the Data source name as the value.
19. Optionally, add any environment variables in the same init file.
20. Save and exit.
Finally, creating the database link itself.
21. Logon to SQLPlus with the sysdba privilege and execute the command to create the link
SQL> CREATE PUBLIC DATABASE LINK dblink_orcl2mssql CONNECT TO '<target_db_username>' IDENTIFIED BY '<target_db_password>' using 'DBLINK01';
Database link created
22. Run a query to test the link
SQL> SELECT COUNT(*) FROM SC01.EMP@dblink_orcl2mssql;
Presto! Works like a charm.