Connecting to heterogeneous data sources in Oracle databases

Due to historical reasons, many enterprises have multiple database platforms at the same time, and each database platform runs one or more related applications. As the business of an organization continues to expand, how can we quickly and effectively integrate the data distributed on different database platforms within the Organization without affecting the operation of existing applications, it is a problem that troubles CIOs. The existing solutions can be roughly divided into the following two types:

1. establish a data connection to different data sources on the application. In this way, the programmer is required to determine which connection corresponds to which database, in addition, if the stored procedure involved in the design needs to be written separately according to the requirements of different databases, the requirements of programmers are aggravated.

2. setting up snapshots in the database and regularly copying data from other data sources to the local database solves the problem of different data sources in the previous method. However, due to timed replication, data cannot be synchronized in real time, this method cannot be used in applications with high real-time requirements.

Because both methods have some disadvantages, this article describes a solution provided by Oracle to solve the connection problem between oracle databases and Heterogeneous data sources-Oracle Heterogeneous service Heterogeneous Services ).

Program running effect:

Heterogeneous services

"Heterogeneous service" is a function integrated into Oracle 8i database software. It provides common technologies for accessing other non-Oracle databases from Oracle databases. Readers familiar with Oracle know that Oracle provides a method to access non-local databases by establishing a DB Link, while heterogeneous services allow you to execute Oracle SQL queries by establishing a DB Link, transparently access data in other non-Oracle databases, just like accessing the Oracle remote database. There are two types of heterogeneous services:

1. Transaction Processing Service): the transaction processing Service enables users to support the transaction processing function in accessing non-Oracle databases.

2. SQL Service: Through the SQL Service, users can directly execute various SQL statements on non-Oracle databases in the Oracle database.

Based on different Heterogeneous service proxies, the "heterogeneous service" connection methods can be divided into transparent gateways and general connections.

Transparent Gateway Transparent Gateways) Transparent Gateway uses the specific Gateway Program provided by Oracle to set proxy. for example, to connect to SQL Server, you must have SQL Transparent Gateway for SQL Server.

General connection (Generic Connectivity) General connections are divided into ODBC connections and ole db connections. There is no essential difference between the connection method and transparent gateway, but General connections are functions provided together with databases, you do not need to purchase the relevant transparent gateway program from Oracle.

Connect to an instance

The application environment of this instance is Oracle 8.1.7, the English version of Windows 2000 Server, and the general connection ODBC for SQL Server is used to connect to the Chinese version of SQL Server 2000. The installation procedure is as follows:

1. Install the HS component.

By default, the HS service is installed with Oracle 8.1.7. You can check whether the HS_BASE_CAPS view exists in the SYS user to check whether the HS component is installed, if not, you can use the relevant installation disk for installation.

2. Configure the ODBC system connection word.

On the Control Panel, select "Data Sources (ODBC)" and configure the ODBC for SQL Server connection word (dnsora2sql) in "System DNS ).

3. Configure tnsnames. ora, which is located in ORACLE_HOME \ NETWORK \ ADMIN.

Add the following code to this file:

Lnk2sql = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = HOSTNAME) (PORT = 1521) (CONNECT_DATA = (SID = hs4sql) | <service SID Name, which must be the same as the sid configured in Listener) (HS = OK) | <enable the HS service option)

4. Configure listener. ora, which is located in ORACLE_HOME \ NETWORK \ ADMIN.

Add the following code to this file:

SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = hs4sql) | <service SID Name, corresponding to tnsname name (ORACLE_HOME = C: \ oracle \ ora9201) (PROGRAM = hsodbc) | <The HS service program to be used. If ole db is used, the program name is hsole ))

5. Restart Oracle listener.

6. Edit init. ora in ORACLE_HOME \ HS \ ADMIN. Here is iniths4sql.

Modify the following two lines of code:

HS_FDS_CONNECT_INFO = dnsora2sql | <ODBC system name HS_FDS_TRACE_LEVEL = 0

7. Create a database link.

create database link ‘ora2sql’ connect to sql1 identified by sql1 using ‘lnk2sql’;

8. Test the connection. For example:

SQL> select * from region@ora2sql;

Now we have completed the configuration of connecting to SQL SERVER using ODBC.

In general, heterogeneous services extend the Oracle database's ability to connect to heterogeneous data sources and enhance the integration of enterprise data. This is a quick, effective, and economical solution to integrate enterprises' internal heterogeneous data.

(