Not all is Oracle (sorry Larry). In many situations, the need to share data between different database platforms is unavoidable. For many versions now, Oracle has support cross-platform data sharing with Oracle Heterogeneous Services (HS) and Transparent Gateways (TG). Heterogeneous Services is a service that can be installed in an Oracle database that will translate Oracle SQL syntax to a specific "generic" syntax. Heterogeneous Services works in concert with Oracle Transparent Gateways. A Transparent Gateway client allows Oracle to make a connection to a Non-Oracle database via a database link. The Transparent Gateway can then accept the generic SQL syntax generated by Heterogeneous Services and translate it into the platform specific SQL syntax for its specific Non-Oracle database platform, and run the translated SQL at the Non-Oracle database. Heterogeneous Services handles generic translation for code that is common among the different Gateways. The Gateways handle translation for code that is specific to that particular Gateway Non-Oracle database platform (A list of the different Gateway platforms supported in 11g can be found in chapter 1). If the SQL returns data, the Gateway retrieves the data from the Non-Oracle database, and returns it to the Oracle session. Transparent Gateway connectivity is configured via Oracle Net (a.k.a SQLNet) as a database link.
The basic steps for Oracle to Non-Oracle communications is as follows:
- A distributed/remote SQL statement is issued to the Oracle Server (the SQL contains at database link).
- Oracle determines that the data is external by the database link.
- Oracle passes the distributed portion of the SQL to the Heterogeneous Services for generic translation.
- Heterogeneous Services performs the initial translation and the passes the generic SQL to the Gateway configured for the database link.
- The Gateway performs any platform specific translations.
- The Gateway executes the translated SQL at the Non-Oracle database.
- If the translated SQL returns data, the Gateway retrieves the data from the Non-Oracle Database.
- The Gateway then maps the Non-Oracle datatypes to Oracle datatypes.
- The Gateway then returns the data to the waiting Oracle session processing the SQL.
Streams can be configured to use HS and TG to replicate data to and from an Oracle database to a Non-Oracle database. The Apply process must be created on the Oracle database (because its an Oracle specific process) using the DBMS_APPLY_ADM.CREATE_APPLY procedure. The DBMS_APPLY_ADM.CREATE_APPLY procedure allows us to specify an apply_database_link. This database link used for a Non-Oracle database would be configured for Transparent Gateway connectivity. When the Apply process dequeues and processes an LCR, it constructs the SQL statement to accomplish the transaction contained in the LCR (as we discussed Chapter 1). This SQL statement is then passed through the HS and executed at the Non-Oracle database via the TG database link.
You can not alter an Apply process to add or remove an apply_database_link. It can only be added at Apply process creation, and only with the DBMS_APPLY_ADM.CREATE_APPLY procedure.
Capture and Propagation are created as normal. If the Capture is on the same database as the Apply process, propagation need not be configured. In this case, the Apply process can be configured to use the same queue as the resident Capture process.
The following code for setting up a Heterogeneous Apply process can be found in the HeterogeneousApply.sql code file.
- First, Configure Heterogeneous Services and the Appropriate Transparent Gateway to the Non-Oracle database.
Refer to the Oracle Database Heterogeneous Connectivity User's Guide and the Oracle Database Gateway for [platform] User's Guide for information on this step.
- Configure Oracle Net (listener.ora and tnsnames.ora) to use HS.
Reference the "Configuring Oracle Net Services for Oracle Heterogeneous Services" section of Chapter 13 in the Oracle Database Net Services Administrator's Guide.
listener.ora
SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=sybasegw) (ORACLE_HOME=/oracle11g) (PROGRAM=tg4sybs)))
tnsnames.ora alias
TG_Sybase= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sybase-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sybasegw) ) (HS=ok))) )
- Create the database link (dblink) for the Non-Oracle Gateway.
Note: for the USING clause, you can specify the gateway tnsalias in your tnsnames.ora or the "description" syntax for the alias. Also, make sure to specify a user that is configured at the Non-Oracle database that has the necessary DML privileges on target tables.
CREATE DATABASE LINK TG_SYBASE.oracle.com CONNECT TO "<sybase_user>" IDENTIFIED BY "&password" USING 'TG_Sybase'; or CREATE DATABASE LINK TG_SYBASE.oracle.com CONNECT TO "<sybase_user>" IDENTIFIED BY "&password" USING '(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sybase-server)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=sybasegw) ) (HS=ok))) )';
- Test the link by running a basic select statement using the dblink
select ... from "tablename"@tg_sybase.oracle.com;
- Create the Apply process using the
DBMS_APPLY_ADM.CREATE_APPLYprocedure settingBEGIN DBMS_APPLY_ADM.CREATE_APPLY( queue_name => 'strmadmin.CAPT_ALL', apply_name => 'APPLY_Sybase', apply_database_link => 'TG_SYBASE.oracle.com', apply_captured => TRUE); END; /
- Add an Apply Rule to the Apply process
BEGIN DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'hr.jobs', streams_type => 'apply', streams_name => 'APPLY_Sybase', queue_name => 'strmadmin.streams_queue', include_dml => TRUE, include_ddl => FALSE, source_database => 'Oracle_DB.oracle.com', inclusion_rule => TRUE); END; /
- Set any Apply Process parameters you wish
BEGIN DBMS_APPLY_ADM.SET_PARAMETER( apply_name => 'APPLY_Sybase', parameter => 'disable_on_error', value => 'N'), END; /
- Prepare the table for instantiation and set the Apply process SCN
DECLARE iscn NUMBER; BEGIN iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN( source_object_name => 'hr.jobs', source_database_name => 'Oracle_DB.oracle.com', instantiation_scn => iscn); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN( source_object_name => 'hr.jobs', source_database_name => 'Oracle_DB.oracle.com', instantiation_scn => iscn, apply_database_link => 'TG_SYBASE.oracle.com'), END; /
- Start the Apply Process
BEGIN DBMS_APPLY_ADM.START_APPLY( apply_name => 'APPLY_Sybase'), END; /
Note
For more information on setting up Heterogeneous Replication, please reference the Oracle Streams Concepts and Administration Guide, and the Oracle Streams Extended Examples manual.
Some basic rules to remember when configuring an Apply process for a Non-Oracle database.
- The Apply process and queue are configured at the Oracle database
- No queue is created on the non-Oracle database
- The replicated Oracle tables must be prepared for instantiation and the apply SCN set for the Apply process
- The tables at the Non-Oracle database must be created and populated with the necessary data manually
- To avoid data convergence, the Non-Oracle tables should have the same or "equivalent" data as their Oracle table counter-part
- Only basic DML operations are supported
- Captured DDL changes cannot be applied on the non-Oracle database
- Error handlers and conflict handlers are not supported
- Conflict detection is supported
If you wish to populate Oracle tables from Non-Oracle databases, you must create a client application that retrieves the data from the Non-Oracle database and inserts it into the appropriate Oracle tables. The client application can be coded to retrieve the data directly from the Non-Oracle database, or from an Oracle session using HS and TG database links.
You can also use client applications that are coded to use advanced queue messaging and LCR datatypes to enqueue data into a Streams queue to which an Apply process is assigned.