Configuring Oracle as a replication source

This section describes the tasks involved in configuring Oracle as a replication source.

If you have already configured the Oracle database in your replication environment, you must still perform the tasks specified in section “Configuring the Oracle instance,” “Configuring Replication Agent,” and “Adding primary database to the replication system,” to use ETL.

Installing Replication Agent (RAX) and creating a Replication Agent for Oracle (RAO) instance

Install Replication Agent.
Copy the downloaded license file to SYSAM-2_0\licenses in the installation folder.

Start a RAO instance. For example:

ra_admin -c rao_inst1 -p 1333 -t oracle

Include the Oracle JDBC driver jar files to CLASSPATH. For example:

set CLASSPATH=%CLASSPATH%;C:\oracle\product
\10.2.0\db_2\jdbc\lib\ojdbc14.jar

Configuring the Oracle instance

Connect to the Oracle instance as a system administrator, using SQLPLUS, and perform these configuration tasks:

Prepare Oracle to use redo logs

To verify the archive log mode, enter:

select log_mode from v$database;

If the archive log mode is on, this message displays:

LOG_MODE

-------

ARCHIVELOG

If the archive log mode is off, this message displays:

shutdown immediate;exit

Run the .sqlpus/nolog command to set the archive log mode to on and then enter:

connect sys/password as sysdba;

startup mount;

alter database archivelog;

alter database open;

alter system set recyclebin=off;

Enable supplemental logging for the source table.

ALTER TABLE T1 ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

It is a must to enable supplement logging for the source table in ETL.

Add primary key information to the Oracle redo log.

alter database add supplemental log data (primary key,
unique index) columns;
select SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK, 
SUPPLEMENTAL_LOG_DATA_UI from v$database;

If the primary key information is successfully added, this message displays:

SUPPLEME SUP SUP

-------- --- ---

YES      YES YES

Create Oracle users for Replication Agent and Replication Server, and then grant connect,resource,dba to them;

Do not use the Oracle user for Replication Server to perform any DML transactions, as Replication Server does not capture data changes made by this user.

Configure Replication Agent. See Configuring Replication Agent.

Configuring Replication Agent

To configure Replication Agent, start the RAO instance and connect to it using isql. Then, perform these configuration tasks:

Set the archive log file path of the source Oracle database. Enter:

ra_config pdb_include_archives, true
go
ra_config pdb_archive_path, <path-to-oracle-archive-directory>
go

Configure connection of Replication Agent to the primary database. Enter:

ra_config pds_host_name, <the host name of the source oracle>
go
ra_config pds_port_number <the port number of the source oracle>
go
ra_config pds_database_name,<the source oracle database name>
go
ra_config  pds_username, <the oracle user for Replication Agent>
go
ra_config pds_password, <password>
go
test_connection PDS
go

If the connection is established successfully, this message displays:

Type Connection
---- ----------
PDS  succeeded

Configure the Replication Agent connection to Replication Server. Enter:

ra_config rs_host_name, <the host name of the Replication Server>
go
ra_config rs_port_number, <the port number of the
Replication Server>
go
ra_config rs_username, <the Replication Server user for Replication Agent>
go
ra_config rs_password, <password>
go
ra_config rs_source_ds <the current RAO instance name> 
go
ra_config rs_source_db, <the source oracle database name>
go

It is a must to specify the RAO instance name for ETL.

Configuring the Replication Agent connection to ERSSD. Enter:

ra_config rssd_host_name <the host name of the ERSSD>
go
ra_config rssd_port_number, <the port number of the ERSSD>
go
ra_config rssd_username, <the ERSSD user for Replication Agent>
go
ra_config rssd_password, <password>
go
ra_config rssd_database_name, <the database name of the ERSSD>
go
test_connection RS
go

If the connection is established successfully, this message displays:

Type Connection
---- ----------
RS succeeded

If the charset of Replication Server is not the same as Replication Agent, update the charset. Enter:
```
ra_config rs_charset, <the charset of the Replication Server>
```
To handle update or delete transactions correctly, configure ltl_send_only_primary_keys as false. Enter:
```
ra_config ltl_send_only_primary_keys, false
```
ETL is unable to handle delete or update transactions if you do not perform this step.
Initialize Replication Agent. Enter:
```
pdb_xlog init
```

Adding primary database to the replication system

Using isql, connect to Replication Server, and enter:

create connection to  < rs_source_ds >.< rs_source_db >
set error class rs_sqlserver_error_class
set function string class rs_sqlserver_function_class
set username <the oracle user for Replication Server>
set password <password>
with log transfer on, dsi_suspended

where:

rs_source_ds – is identical to value of the parameter, rs_source_ds, in Replication Agent.
rs_source_db – is identical to value of the parameter, rs_source_db, in Replication Agent.

Resuming Replication Agent

Replication Agent is now ready to replicate transactions. Start the replication by executing the resume command in Replication Agent:

resume 
go

Verify if Replication Agent is replicating

ra_status
go

If Replication Agent is setup correctly, this message displays:

State        Action

------------ -------------------------

 REPLICATING  Ready to replicate data.