Oracle primary data servers

This section describes the primary database issues and considerations specific to the Oracle data server in a Sybase replication system.

Replication Agent for Oracle

As a primary data server, Oracle interacts with Sybase Replication Agent. The Replication Agent identifies and transfers information about data-changing operations (or transactions) from an Oracle primary data server to a primary Replication Server.

The Replication Agent interacts with the primary Replication Server, and with the RSSD of the primary Replication Server, if so configured.

Sybase Replication Agent is a Java program and it requires a Java Runtime Environment (JRE) to run. Some operating systems require system patches to properly support Java. See the Sybase Replication Agent release bulletin for more information on your specific operating system version and the patches required.

Oracle primary database permissions

The Replication Agent requires an Oracle login ID that has permission to access data and create new objects in the primary database.

The Oracle login ID must have the following permissions:

CREATE SESSION
RESOURCE
SELECT on any table to be replicated
CREATE TABLE
DROP TABLE
CREATE TRIGGER on any trigger on any table to be replicated
DROP TRIGGER on any trigger on any table to be replicated
CREATE PROCEDURE on any procedure to be replicated
DROP PROCEDURE on any procedure to be replicated

Stored procedures that are created by the Replication Agent require access to two Oracle system views (dynamic performance views) that do not have SELECT authority granted by default. Any user that updates a table or procedure marked for replication must have SELECT authority on the following views:

V_$TRANSACTION
V_$SESSION

Only the Oracle SYS user (or a user with SYSDBA ROLE) can grant SELECT authority on these views. To simplify implementation, you can grant SELECT authority on these views to PUBLIC.

Replication intrusions and impacts in Oracle

The performance and operation of Oracle primary data servers in a Sybase replication system might be affected by the following:

The Sybase Replication Agent uses its own proprietary transaction log to capture and record transactions in the primary database. The Replication Agent transaction log consists of shadow tables, stored procedures, and triggers that are created as objects in the primary database.

For each table operation or procedure invocation to be replicated, a sequence of triggers and/or stored procedures is executed to copy the operational data or parameters into a copy (shadow) table. The shadow tables are read periodically by the Replication Agent, which sends the transaction data to the primary Replication Server.

The impact of making these copies and scanning them depends on the capacity of the host machine and data server at the time of execution. A server with low transaction volumes or generous system resources may experience very little or no noticeable impact on performance. For a heavily loaded system, or one with limited resources, the impact on performance could be significant. In addition, the need to keep copies of primary transaction data may require significant additional disk space for systems with large transaction volumes.

Primary data server connectivity

Sybase Replication Agent requires the following to connect to an Oracle primary data server:

A JDBC driver for the Oracle data server. You should ask your Oracle vendor for the most recent version of the JDBC driver that supports your Oracle database version.

Sybase Replication Agent version 12.1 or earlier requires a JDBC 1.0-compliant driver. Sybase Replication Agent version 12.5 or later requires a JDBC 2.0-compliant driver.

The JDBC driver must be installed and referenced in the CLASSPATH system variable of the Replication Agent host machine. Java uses the contents of the CLASSPATH system variable to identify the search locations for Java classes. For the Oracle JDBC driver, the full path and file name must be included in the CLASSPATH variable, for example: c:\oracle\jdbc\classes12.zip
For JDBC connectivity, the TNS Listener process for the Oracle primary data server must be running.
The values of the following Replication Agent configuration parameters must be set as described:
- pds_username – the Oracle user ID that the Replication Agent uses to log in to the primary database. This user ID must be granted permissions as described in “Oracle primary database permissions”.
- pds_password – the password for the Oracle user ID that the Replication Agent uses to log in to the primary database.
- pds_connection_type – must be set to ORAJDBC for connectivity to an Oracle primary database.
  
  The value of the pds_connection_type parameter is set automatically when the Replication Agent instance is created. Refer to the Sybase Replication Agent Administration Guide for more information about Replication Agent instances.
- pds_host_name – the name of the host machine on which the Oracle data server resides.
- pds_port_number – the client socket port number where the Oracle data server listens for connections.
- pds_server_name – the server name of the Oracle data server.
- pds_database_name – the name of the Oracle SID, from which transactions will be replicated.
  
  A separate Replication Agent instance is required for each Oracle instance from which transactions are replicated.

Replication Server connectivity

The values of the following Replication Agent configuration parameters must be set as described so that the Replication Agent can connect to the primary Replication Server:

rs_host_name – the name of the host machine on which the primary Replication Server resides.
rs_port_number – the client socket port number where the primary Replication Server listens for connections.
rs_username – the user ID that the Replication Agent uses to log in to the primary Replication Server. This user ID must be defined and granted connect source permission in the Replication Server.
rs_password – the password for the user ID that the Replication Agent uses to log in to the primary Replication Server.
rs_source_ds – the server name of the primary data server specified in the Replication Server database connection.
rs_source_db – the database name of the primary database specified in the Replication Server database connection.

Sybase Replication Agent uses TCP/IP and the Sybase JDBC driver (jConnect for JDBC, which is included in Replication Agent installation) to communicate with other Sybase servers. The Replication Agent does not rely on the Sybase interfaces file for connectivity information.

RSSD connectivity

The values of the following Replication Agent configuration parameters must be set as described so that the Replication Agent can connect to the RSSD of the primary Replication Server:

rssd_host_name – the name of the host machine for the data server that contains the RSSD.
rssd_port_number – the client socket port number where the RSSD data server listens for connections.
rssd_username – the user ID that the Replication Agent uses to log in to the RSSD. This user ID must be defined and granted select permission in the RSSD.
rssd_password – the password for the user ID that the Replication Agent uses to log in to the RSSD.
rssd_database_name – the database name of the RSSD. When logging in to the data server specified in the rssd_host_name parameter, the Replication Agent invokes a use for the database name specified in the rssd_database_name parameter.

Primary database limitations in Oracle

Sybase Replication Agent imposes the following limitations on an Oracle primary data server:

No primary table marked for replication can have a selective update trigger on it. (A selective update trigger is an update trigger that acts based only on operations in specific columns.) This type of trigger can prevent the Replication Agent from capturing transaction data on updates.
Oracle allows multiple AFTER row triggers that fire for the same statement on the same table, however, the order in which these triggers fire is indeterminate. Multiple AFTER row triggers on a table can prevent the Replication Agent from capturing transaction data on updates.
The maximum number of columns allowed in a primary table marked for replication is three columns less than the maximum number of columns allowed by the Oracle data server instance. The shadow table that stores captured transaction data uses the primary table schema, plus three additional columns for Replication Agent system information. If three more columns cannot be added to the number of columns in a primary table, the shadow table creation fails.

Replication Server Manager has the following limitations with an Oracle primary data server:

Replication Server Manager cannot start or stop a Sybase Replication Agent instance.

See the Sybase Replication Agent Administration Guide for more information on starting and stopping the Replication Agent instance.
Replication Server Manager does not provide a method to invoke the Replication Agent pdb_xlog command. You must log in to the Replication Agent instance to execute this command.

See the Sybase Replication Agent Administration Guide for more information on logging in to the Replication Agent instance and using the pdb_xlog command.

Oracle primary database configuration issues

All the installation issues and configuration parameter details for an Oracle primary data server are provided in the Sybase Replication Agent Installation Guide. The following are a few items that may need additional attention:

When you install Sybase Replication Agent, a Java Runtime Environment (JRE) that is compatible with the Replication Agent may be installed for you. For each operating system, you should download and install the most recent recommended patches specified by your operating system vendor for Java compatibility.
Sybase Replication Agent requires a JDBC driver for connectivity to the primary data server. Sybase does not provide a JDBC driver for Oracle data servers. You must contact your database vendor for more information about JDBC drivers for Oracle data servers.
All configuration parameter values in the Replication Agent configuration file are case sensitive. Be careful when specifying the values of the rs_source_ds and rs_source_db parameters, as Replication Server is also case sensitive. If the same case is not used in both Replication Agent and Replication Server parameters, no connection occurs.
The Replication Agent filter_maint_userid configuration parameter controls whether the Replication Agent forwards transactions performed by the Maintenance User to the primary Replication Server. The Maintenance User name is defined in the Replication Server create connection command for the primary database.

In a bidirectional replication environment (replicating both into and out of the same database), the value of the filter_maint_userid parameter should be set to true. If it is not, transactions replicated to another site could return to be applied at the originating site, creating an endless loop.
The Replication Agent ltl_character_case configuration parameter controls the case in which the Replication Agent sends database object names to the primary Replication Server.

For example, if a replication definition is created for all tables named testtab, the table name sent by the Replication Agent must be testtab, or no match occurs. Because Replication Server is case sensitive, a value of TESTTAB does not match a value of testtab.

If you use Replication Server Manager to create replication definitions, set the value of the Replication Agent ltl_character_case configuration parameter to asis. This allows the Replication Agent to send database object names in the same case as they appear in the primary database, which is the way RSM creates replication definitions, using the case returned by the primary data server. If you create replication definitions outside RSM, it may be easier to choose a default case (for example, create all replication definitions in either all uppercase or all lowercase), and change the value of the Replication Agent ltl_character_case parameter to match.

In an Oracle database, object names are stored in all uppercase by default, if no case was forced when the object was created. That means the Replication Agent sends object names in uppercase to the primary Replication Server, unless configured to do otherwise.

See the Sybase Replication Agent Administration Guide for more information on the ltl_character_case parameter.
No Open Client interface application (such as isql) is provided as part of the Sybase Replication Agent installation. Use the Replication Server Manager, or use an Open Client application provided with another Sybase product (for example, the isql installed with Adaptive Server or Replication Server).

Replication definitions for primary tables in Oracle

The Replication Agent use_rssd configuration parameter controls whether the Replication Agent sends Log Transfer Language (LTL) that contains only the columns specified in a replication definition, or all of the columns in the primary table.

When the value of the use_rssd parameter is set to false, the Replication Agent sends LTL with data for all of the columns in the primary table. When the value of the use_rssd parameter is set to true, the Replication Agent sends LTL with data for only the columns specified in the replication definition for each primary table.

By sending data for only the columns specified in the replication definition, network traffic is reduced, which can improve performance.

In addition, column names and parameter names are removed from the LTL because the Replication Agent can send information in the order identified by the replication definition. The LTL minimal columns and structured tokens options are also available when the value of the use_rssd parameter is set to true. See the Sybase Replication Agent Administration Guide for more information.

Oracle primary datatype translation issues

The Sybase Replication Agent allows you to control how it sends Oracle DATE column values to the Replication Server. There are two options:

Send the value as a character string (the default)
Send the value as a Sybase datetime value

The value of the Replication Agent pdb_convert_datetime configuration parameter determines how the Replication Agent handles temporal datatypes.

If you set the pdb_convert_datetime parameter to true, all corresponding datatypes in a replication definition for DATE columns are converted to the Sybase datetime datatype.

If you set the pdb_convert_datetime parameter to false, the datatype in a replication definition for a DATE column must be either:

A Replication Server user-defined datatype (UDD), or
A character datatype

The character (char or varchar) datatype specified in a replication definition for for a DATE column must have sufficient length to accommodate the column’s default display length.

See the Sybase Replication Agent Administration Guide for a full description of the pdb_convert_datetime parameter and a complete list of datatype mapping for Oracle datatypes. See the Replication Server Administration Guide for more information on UDDs and their use.

Oracle system management issues

The Sybase Replication Agent provides a number of commands that return metadata information about the primary database (database names, table names, procedure names, column names, and so forth). It does this by issuing specific JDBC calls designed to return this information, or by querying the Oracle system tables directly.

Replication Server Manager (RSM) can be configured to use either the Replication Agent or a DirectConnect database gateway to return information about a primary database. Typically, RSM uses a Replication Agent to obtain information from a primary database, and uses a DirectConnect gateway to obtain information from a replicate database. You can specify which product is used to return metadata information to RSM when you add a non-Sybase data server using the RSM Add Server wizard.)

Further considerations of using RSM are:

RSM cannot create a Sybase Replication Agent instance, nor can it start and stop a Replication Agent instance.
RSM can fully configure a Replication Agent instance, as well as invoke most Replication Agent administrative commands.
RSM can monitor and display the contents of Replication Agent instance system logs.
For RSM latency calculations to work correctly, the value of the Replication Agent ltl_origin_time_required parameter must be true.

Oracle does not support multiple databases within a single server instance as Adaptive Server Enterprise does. Because RSM is designed primarily to work with Adaptive Server, an Oracle database represented in RSM must have a database name as well as a server name. To accommodate this, RSM uses the data server and database name specified in the Replication Server primary database connection to identify the Oracle data server.

Other primary database issues for Oracle

The Sybase Replication Agent provides a set of sample scripts which can be used to set up a simple, single-table replication from Oracle to Adaptive Server. These scripts can be found in the $SYBASE/rax-12_6/scripts directory of the Sybase Replication Agent installation.

See the Sybase Replication Agent Administration Guide for more information about the sample scripts and their use.