6
Data Replication and Gateways

In a distributed database system, Oracle replication is a process that maintains multiple copies of the same data in separate Oracle databases. Replication captures and stores changes made to the data at one location before forwarding and applying them at each remote location. Users at each location see the same consistent view of the data. They need not access data remotely, as they would in a standard distributed database system. This chapter contains:

• Oracle Replication, an Overview

• Data Access Gateways

• Uses of Oracle Replication and Gateways

Oracle Replication, an Overview

Replication copies and maintains database objects, such as tables, in the multiple databases that make up a distributed database system. Oracle replication is a fully integrated feature of the Oracle database server; it is not a separate server.

Replication uses distributed database technology to share data between multiple sites, but a replicated database and a distributed database are not the same. In a distributed database, data is available at many locations, but a particular table resides at only one location.

For example, the emp table resides only at the DB1 database in a distributed database system that also includes the DB2 and DB3 databases. Replication means that the same data is available at multiple locations. For example, the emp table is available at DB1, DB2, and DB3.

This section contains:

• Advantages of Replication

• Uses of Replication

• Types of Replication

Advantages of Replication

Some common reasons for using replication are:

• Availability: Replication improves the availability of applications because it provides them with alternative data access options. If one site becomes unavailable, users can continue to query or even update the remaining locations. In other words, replication provides excellent failover protection.

• Performance: Replication provides fast, local access to shared data because it balances activity over multiple sites. Some users can access one server while other users access other servers, reducing the load at all servers. Also, users can access data from the replication site that has the lowest access cost, which is typically the site that is geographically closest to them.

• Disconnected Computing: A snapshot is a complete or partial copy (replica) of a target master table from a single point in time. Snapshots enable users to work on a subset of a database while they are disconnected from the central database server. Later, when a connection is established, users can synchronize (refresh) snapshots on demand. When users refresh snapshots, they update the central database with all of their changes, and they receive any changes that may have happened while they were disconnected.

• Network Load Reduction: Replication can be used to distribute data over multiple regional locations. Applications access various regional servers instead of accessing one central server. This configuration dramatically reduces network load.

• Mass Deployment: Increasingly, organizations must deploy many applications that require the ability to use and manipulate data. With Oracle replication, deployment templates enable you to create multiple snapshot environments quickly. You can use variables to customize each snapshot environment for its individual needs. For example, use deployment templates for sales force automation; the template contains variables for various sales regions and salespersons.

Replication is ideally suited to synchronize data between systems in different locations so that each has a consistent view of the data without the necessity of accessing the data remotely.

Uses of Replication

Replication enables you to replicate tables and other supporting objects such as views, database triggers, packages, indexes, and synonyms by organizing logically related objects into replication groups. A replication object is a database object existing on multiple servers in a distributed database system. In a replication environment, any updates made to a replication object at one site are applied to the copies at all other sites. Use replication to:

• Replicate changes to data and schema from one master group to a number of snapshot groups

• Replicate changes to data and schema between two or more master groups

• Replicate changes to data from a snapshot group back to the master group

• Create read-only materialized views of data on another database

• Automatically resolve conflicting updates between master groups using pre-built methods

• Replicate and synchronize the running of replica procedures to update copies of datasets

You can ensure transactional integrity by choosing particular replication features and setting up the replication to occur synchronously. (Asynchronous replication is the norm.) Replication has its own set of data dictionary tables and views called the replication catalog. A replication management API consists of PL/SQL packages that can be used to administer the replication objects.

Types of Replication

The Oracle database supports three types of replication:

• Multimaster

• Snapshot

• Hybrid Configurations

Multimaster Replication

Multimaster replication (also called peer-to-peer or n-way replication) enables multiple sites, acting as equal peers, to manage groups of replicated database objects. Each site in a multimaster replication environment is a master site.

Applications can update any replicated table at any site in a multimaster configuration. Oracle database servers operating as master sites in a multimaster environment automatically converge the data of all table replicas and ensure global transaction consistency and data integrity.

Snapshot Replication

A snapshot contains a complete or partial copy of a target master table from a single point in time. A snapshot may be read-only or updatable. All snapshots:

• Enable local access, which improves response times and availability

• Offload queries from the master site, because you can query the local snapshot instead

• Increase data security by enabling you to replicate only a selected subset of the data set of the target master table

Hybrid Configurations

Multimaster replication and snapshots can be combined in hybrid or mixed configurations to meet different application requirements. Mixed configurations can have any number of master sites and multiple snapshot sites for each master.

For example, multimaster (or n-way) replication between two masters can support full-table replication between the databases that support two geographic regions. Snapshots can be defined on the masters to replicate full tables or table subsets to sites within each region.

See Also: Oracle8i Concepts

Data Access Gateways

Oracle Corporation offers a number of products that you can use to facilitate communication with non-Oracle products and technologies. These are grouped under Data Access Gateways and fall into three broad categories: Oracle Transparent Gateways, Oracle Procedural Gateways, and Oracle Access Managers. Both the Transparent Gateways and the Procedural Gateways enable you to access non-Oracle technology from an Oracle environment. The Oracle Access Managers enable you to access Oracle technology from a non-Oracle environment.

This section contains:

• Oracle Transparent Gateways

• Oracle Procedural Gateways

• Oracle Procedural Gateway for APPC

• Oracle Access Managers

• Interoperability

Oracle Transparent Gateways

Oracle Transparent Gateways gives applications access through transactional SQL to non-Oracle data stores such as DB2, IMS, and DB2/400.

Oracle Transparent Gateways provides:

• Access to data as if it resided in a single, local, relational database

• Dynamic SQL access to the data

• The ability to join tables from different sources with a single SQL statement

OTG provides two major IBM-oriented transparent gateways for DRDA and EDA/SQL, a transparent gateway for ODBC, and transparent gateways for Sybase, Infomix, Ingres, Microsoft SQL Server, HP IMAGESQL, Digital RMS, and Rdb.

The Oracle Transparent Gateway for IBM DRDA is the Oracle implementation of the IBM DRDA architecture. It provides Oracle applications with read and write access to DRDA server databases, including DB2 OS/390, SQL/DS, DB2/400, and DB2/UDB.

With the Transparent Gateway for EDA/SQL running on OS/390, you can access the majority of non-Oracle databases and file systems, including VSAM, IMS, ISAM, Teradata, ADABAS and DB2.

Oracle Procedural Gateways

The Procedural Gateways offer programmatic access to non-Oracle transactions from within the context of an Oracle transaction. They are IBM-oriented and fall into two types: Procedural Gateway for APPC and Procedural Gateway for MQSeries.

Because the Procedural Gateway for MQSeries is message-oriented, we cover it in Chapter #, "Advanced Queueing" (hotlink).

Oracle Procedural Gateway for APPC

The Oracle Procedural Gateway for APPC uses native IBM APPC / LU6.2 mechanisms to execute mainframe transactions through a transaction monitor such as IBM CICS, IBM IMS/TM, or CA-IDMS/DC.

These transactions access a variety of databases and file systems, including:

• IMS

• DB2

• VSAM

• CA-IDMS/DB

• ADABAS

• Model 204

Procedural Gateway for APPC enables:

• Sharing of transactions between Oracle and non-Oracle applications

• Support for multiple transaction monitors and multiple operating systems from a single UNIX-based technology:
  • MVS: CICS, IMS/TM, CA-IDMS/DC, and APPC/MVS

  • CICS/VM

  • DOS/VSE: CICS

  • AS/400: CICS/400

Oracle Access Managers

Used in combination with the Transparent Gateways, Access Managers enable mainframe applications to access virtually any data store. The Access Managers let you use existing OS/390 and AS/400 applications by providing them with access through SQL to data in the Oracle Server. The Access Manager for AS/400 enables AS/400 applications written in RPG, COBOL, and C to access data stored in Oracle.

The Oracle Access Managers for CICS and IMS/TM are part of the Oracle for MVS Client Solution, which enables you to access data in Oracle from TSO, batch, CICS, and IMS/TM programs:

• You can start a TSO session and have direct interactive access to any Oracle database server anywhere in the enterprise.

• Batch processes update remote Oracle servers directly, eliminating the need to set up complicated file-transfer mechanisms.

• CICS and IMS/TM transactions update Oracle databases and other recoverable resources. The transaction is protected by a two-phase commit using SYNCPOINT processing.

Uses of Oracle Replication and Gateways

An original method of integrating applications was to use data level integration to maintain multiple copies of the data in the different applications and to periodically synchronize the copies. If the requirements for integration are relatively basic, then data level integration provides a simple, cost-effective, and manageable solution.

Solutions that successfully employ data level integration have most or all of the following characteristics:

• All applications and data are owned and controlled by a single organization.

• There are a relatively small number of application instances (less than 20).

• Applications are mostly based on relational technology.

• Applications are predominantly based on the Oracle database.

• Data Structures (customer/account/transaction tables) are broadly similar in all applications.

• Each data item is created and changed by only one application.

• Ratio of Read Access to Data Change Access is relatively high.

• Extensive real-time or near-real-time synchronization of data is not required.

• The architecture and applications to be integrated are not continually changing.

Classic solutions that use this style of integration include:

• Mobile applications that can be synchronized infrequently with back-end systems

• Web-based applications requiring read-only data to be cached in the middle tier

• Centrally-managed List-of-Values reference data that must be distributed to OLTP applications

The Oracle products best suited to providing this level of integration are Oracle Replication and Oracle Data Access Gateways.

Oracle Replication enables asynchronous synchronization of data across distributed Oracle databases using journal logs to capture both changes made to the master copy of table structures and row-level data changes. The logs then apply the changes to the snapshot copies of the tables.

Oracle Replication is a mature product that contains numerous advanced features that extend this basic functionality to:

• Manage and synchronize multiple master copies

• Resolve conflicting data changes

• Provide real-time replication of data changes as part of the instigating transaction

• Synchronize the running of replicated stored procedures

In a data-level integration solution, Oracle Replication is the mainstay of integration between applications based on the Oracle database.

Interoperability

Oracle Corporation provides a number of transparent gateways that enable seamless integration to other databases in Oracle-controlled transactions. For transactions that are not controlled by the Oracle transaction manager, use the Access Managers to enable data in Oracle databases to be accessed by non-Oracle-controlled transactions.