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. For more information on your specific operating system version and the patches required, see the Sybase Replication Agent release bulletin.

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 required to connect to Oracle
select_catalog_role required to select from the DBA_* views
alter system required to perform redo log archive operations
execute on DBMS_FLASHBACK required to execute DBMS_FLASHBACK.get_system_change_number
alter any procedure required to instrument procedures for replication
create table required to create tables in the primary database
create procedure required to create rs_marker and rs_dump proc procedures
create public synonym required to create synonyms for created tables in the primary database
drop public synonym required to drop created synonyms
select on SYS.OBJ$ required to process procedure DDL commands
select on SYS.LOB$ required to support LOB replication
select on SYS.COLLECTION$ required to support table replication
select on SYS.COL$ required to support table replication

In addition, the user who starts the Mirror Replication Agent for Oracle instance must have read access to the Oracle redo logs.

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 reads the Oracle redo logs to retrieve transaction information. To provide and maintain the necessary information, the following adjustments to Oracle redo logs are required:
- Archiving of redo logs must be enabled.
- “Automatic" archiving must be disabled.
- Supplemental logging of primary key and index data must be enabled.
Subsequent archiving of the Oracle redo logs is controlled by the Replication Agent as information is read from the logs.

Primary data server connectivity

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

See the Replication Agent Release Bulletin for the version of the JDBC driver that is supported.

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, drive:\<path_name>\ojdbc14.jar.
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_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_charset – character set used to communicate with Replication Agent. It must match the character set used by Replication Server. If rs_charset does not match, Replication Agent will not replicate anything.
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.

For more information on logging in to the Replication Agent instance and using the pdb_xlog command, see the Sybase Replication Agent Administration Guide.

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. 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 create replication definitions, 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.

For more information on the ltl_character_case parameter, see the Sybase Replication Agent Administration Guide.
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. For more information, see the Sybase Replication Agent Administration Guide.

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.

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

Oracle system management issues

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

Oracle does not support multiple databases within a single server instance as Adaptive Server Enterprise does.

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.

For more information about the sample scripts and their use, see the Sybase Replication Agent Administration Guide.