The following steps needs to be performed in order to configure a Heterogeneous connection in the Oracle Database, those steps are used in big firms as well as Oracle Consulting, Oracle Outsourcing Companies:
1. Preparing the Non-oracle environment from where in the data needs to be integrated into the Oracle database.
2. Creation of the ODBC connection.
3. Test the ODBC drivers to ensure that connectivity is made to the non-Oracle database.
4. Ensure the Global_names parameter in Oracle database is set to False.
5. Configure the Heterogeneous services. This is done with the help of creating an initodbc.ora.
7. Modify the Listener.ora and the TNSNAMES.ORA file so that to connect to the Database.
8. Restart the Listener or START the listener if a new one has been created specific for the new connection.
9. Creation of the Database Link to connect to the HS connection.
10. Test the connection using thee DB Link.
Let us see the above steps in a brief fashion. Here we can consider of having a MS-Access Database from where in the data needs to be accessed into Oracle database:
1. Create the MS-Access database or copy the same to the local server where in the Database is hosted.
2. Create the ODBC connection. This can be done as follows:
Click on START à Control Panel à Administrative Tools and then open Data Sources (ODBC)
Click on the SYSTEM DSN Tab and then click on the ADD button.
Select Microsoft Access Driver (*.mdb) and then click on FINISH.
Also you can find out more at the Software Outsourcing Company website: http://www.symbyo.com
After that, enter the Data source name using which you would like to connect to the MS-Access Database (here in our case it’s ChryslerMDB). Also, select the MS-Access file by clicking on SELECT button and then click on OK to complete the configuration.
1. Check for the connectivity and confirm whether the ODBC connection is working fine.
2. Once this is done, Check for the GLOBAL_NAMES parameter in the Oracle database and make sure that it is FALSE. The following query can be used for the same :
********************************************************************************
SQL> select name,value from v$parameter where name like 'global_names%';
NAME
----------------------------------------------------------------
VALUE
----------------------------------------------------------------------------
global_names
FALSE
SQL>
*******************************************************************************
1. Once this is done, we need to create the INIT.ORA file for the HSODBC connection that needs to be created. This needs to be done in %ORACLE_HOME% oracleora92hsadmin folder. (In UNIX, it will be $ ORACLE_HOME oracleora92hsadmin). Create a file saying init.ora file with the following contents : In our case the file should be named as initchryslermdb.ora
HS_FDS_CONNECT_INFO = chryslermdb
HS_FDS_TRACE_LEVEL = 0
In the above chryslermdb id the non-oracle database connection string or the DSN name that we created in Step 2.
1. Once this is done, the next step is to update the Listener.ORA file. We can use the same listener that is been used by the database to access the ODBC connection or a different one can also be configured. Here we will create a new listener for the ODBC connection.
Edit the Listener.ora file in ORACLE_HOMEora92networkadmin folder and add the following entries :
LISTENER_MSACCESS =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1522))
)
)
)
SID_LIST_LISTENER_MSACCESS =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:oracleora92)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = C:oracleora92)
(SID_NAME = chryslermdb)
(PROGRAM = HSODBC)
)
)
In the above SID_NAME ChryslerMDB is the SID name that we have given for non-oracle database (e.g. MS-ACCESS or MS-SQL). Once this is done start the Listener by executing the following command at the command line .
C:Documents and SettingsimpactadmDesktop>lsnrctl start listener_msaccess
LSNRCTL for 32-bit Windows: Version 9.2.0.7 - Production on 15-AUG-2007 13
55
(c) Copyright 1998 Oracle Corporation. All rights reserved.
Starting tnslsnr: please wait...
Then the following message will be seen :
STATUS of the LISTENER
------------------------
Alias listener_msaccess
Version TNSLSNR for 32-bit Windows: Version 9.2.0.7 - Pr
tion
Start Date 15-AUG-2007 13:05:56
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File C:oracleora92networkadmin listener.ora
Listener Log File C:oracleora92networkloglistener_msaccess.log
Services Summary...
DEV has 1 service handler(s)
IMPACT02 has 1 service handler(s)
IMPORT_access has 1 service handler(s)
PLSExtProc has 1 service handler(s)
The command completed successfully
C:Documents and SettingsimpactadmDesktop>
Note : If you are using the same listener, then just execute lsnrctl reload command at the command prompt. In case of windows machines, you can just go to SERVICES and restart the listener.
7. The next step is to update the TNSNAMES.ORA file.
Update the TNSNAMES.ORA file with the entries similar to the following :
#ACCESS DB for Chrysler
Chryslermdb.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = )
(Port = 1522)
)
)
(CONNECT_DATA = (service_name = chryslermdb)
)
(HS=ok)
)
1. Once the entire configuration is done, check the connectivity by doing a tnsping command.
2. The next step is to create a Database link to this ODBC connection. This can be done by as follows:
Login to the database and execute the following query :
CREATE DATABASE LINK TEST1 CONNECT USING ‘CHRYSLERMDB.WORLD’;
Here TEST1 is the DB Link name and CHRYSLERMDB.WORLD is the name of the DSN entry in the TNSNAMES.ORA file.
Once this is done, we can do any operations on the Non-oracle database by executing the commands from Oracle database. For e.g.
SELECT * FROM DUAL@TEST1;
Note : We have to make sure that the SID name in the TNSNAMES.ORA and LISTENER.ORA and the one in the USING clause of the Create Database Link matches, otherwise we will not be able to connect to the database and will get some errors. Also, the init.ora file at the %ORACLE_HOME% oracleora92hsadmin should be created with the correct SID failing which will lead to errors.
Source: ArticleBase.com
No comments:
Post a Comment