Configuring and verifying the primary Oracle database for replication

Before you install Replication Agent, configure the primary Oracle database for replication.

To ensure that the Oracle redo log data is not overwritten before it is read by the Replication Agent:

Verify the current archive setting of the redo logs.
Verify the supplemental logging of primary key data.
Create an Oracle user and grant Oracle permissions.
Verify the Oracle user roles created by the Replication Agent

Verifying the current archive setting of the redo logs

Redo logs are used by Oracle to maintain a log infrastructure. The steps described below verify that the feature is turned on and, if not, indicate how to switch on the redo logging. Turn this feature on only in the primary database.

Use SQLPLUS to connect to Oracle as a system administrator.
From SQLPLUS, run this command:
```
select log_mode from v$database;
```
- If the archive log is on, the result should be:
```
LOG_MODE
```
```
--------
```
```
ARCHIVELOG
```

To turn on log archiving:

shutdown;

startup mount;

alter database archivelog;

alter database open;

See the Replication Agent Primary Database Guide.

Verifying the supplemental logging of primary key data

By default, Oracle does not log primary keys and unique indexes to its logging infrastructure. For successful replication of all table values, you must log these values. To verify whether this information is currently being recorded in the logging system:

Use SQLPLUS to connect to Oracle as a system administrator by running the following command from SQLPLUS:
```
SELECT SUPPLEMENTAL_LOG_DATA_MIN,
```
```
SUPPLEMENTAL_LOG_DATA_PK,
```
```
SUPPLEMENTAL_LOG_DATA_UI
```
```
FROM V$DATABASE;
```
If logging of primary key and unique index values is enabled, the return values are:
```
SUP SUP SUP
```
```
--- --- ---
```
```
YES YES YES
```

If the result is different, turn on supplemental logging by executing these commands:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY

KEY, UNIQUE INDEX) COLUMNS;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Creating an Oracle user and grant permissions

An Oracle database user must be created for use by Replication Agent when connected to the primary database.

Permission to grant access to objects owned by “SYS” may require the command to be executed by an Oracle user with sysdba privileges.

Use SQLPLUS to connect to the primary database as a system administrator and run the following commands to create an Oracle user named “RA_USER,” specified by pds_username, with the password “sybase,” and grant permissions to the user:

CREATE USER RA_USER PROFILE "DEFAULT" IDENTIFIED

BY "sybase" DEFAULT TABLESPACE "USERS" ACCOUNT

UNLOCK;

GRANT "CONNECT" TO RA_USER;

GRANT "RESOURCE" TO RA_USER;

GRANT "SELECT_CATALOG_ROLE" TO RA_USER;

GRANT ALTER SESSION TO RA_USER;

GRANT ALTER SYSTEM TO RA_USER;

GRANT EXECUTE ON "SYS"."DBMS_FLASHBACK" To RA_USER;

GRANT EXECUTE ON "SYS"."DBMS_LOCK" TO RA_USER;

GRANT ALTER ANY PROCEDURE TO RA_USER;

GRANT CREATE SESSION TO RA_USER;

GRANT CREATE TABLE TO RA_USER;

GRANT ALTER ANY TABLE TO RA_USER;

GRANT DROP ANY TABLE TO RA_USER;

GRANT CREATE PROCEDURE TO RA_USER;

GRANT DROP ANY PROCEDURE TO RA_USER;

GRANT CREATE PUBLIC SYNONYM TO RA_USER;

GRANT DROP PUBLIC SYNONYM TO RA_USER;

GRANT SELECT ON SYS.OBJ$ TO RA_USER;

GRANT SELECT ON SYS.LOB$ TO RA_USER;

GRANT SELECT ON SYS.COLLECTION$ TO RA_USER;

GRANT SELECT ON SYS.CON$ TO RA_USER;

GRANT SELECT ON SYS.COLTYPE$ TO RA_USER;

GRANT SELECT ON SYS.COL$ TO RA_USER;

GRANT SELECT ON SYS.CDEF$ TO RA_USER;

GRANT SELECT ON SYS.SNAP$ TO RA_USER;

GRANT SELECT ON SYS.TS$ TO RA_USER;

GRANT SELECT ON SYS.USER$ TO RA_USER;

GRANT SELECT ON SYS.SEQ$ TO RA_USER;

GRANT SELECT ON SYS.IND$ TO RA_USER;

GRANT SELECT ON SYS.TAB$ to RA_USER;

GRANT SELECT ON SYS.TABPART$ to RA_USER;

GRANT SELECT ON SYS.TABCOMPART$ to RA_USER;

GRANT SELECT ON SYS.TABSUBPART$ to RA_USER;

GRANT SELECT ON SYS.NTAB$ to RA_USER;

GRANT SELECT ON SYS.INDPART$ to RA_USER;

GRANT SELECT ON SYS.INDCOMPART$ to RA_USER;

GRANT SELECT ON SYS.INDSUBPART$ to RA_USER;

GRANT SELECT ON SYS.LOBCOMPPART$ to RA_USER;

GRANT SELECT ON SYS.LOBFRAG$ to RA_USER;

GRANT SELECT ON SYS.MLOG$ to RA_USER;

GRANT SELECT ON SYS.PROCEDUREINFO$ to RA_USER;

GRANT SELECT ON SYS.ARGUMENT$ to RA_USER;

GRANT SELECT ON SYS.TYPE$ to RA_USER;

GRANT SELECT ON SYS.ATTRIBUTE$ to RA_USER;

GRANT SELECT ON SYS.CCOL$ to RA_USER;

GRANT SELECT ON "SYS"."RECYCLEBIN$" TO RA_USER;

GRANT SELECT ON "SYS"."OPQTYPE$" TO RA_USER;

See the Replication Agent Primary Database Guide.

Verifying the Oracle user roles created by the Replication Agent

As described in the previous section, the following Oracle commands can be issued to validate the required Oracle settings.

Use SQLPLUS to run the following command as the new Oracle user:
```
select GRANTED_ROLE from USER_ROLE_PRIVS;
```
```
GRANTED_ROLE
```
```
--------------------
```
```
CONNECT
```
```
RESOURCE
```
```
SELECT_CATALOG_ROLE
```
In addition, the user who starts the Replication Agent instance must have read access to the Oracle redo log files and the Oracle archive directory that contains the archive log files to be accessed for replication. If the Replication Agent is configured to remove old archive files, the user must have update authority to the directory and the archive log files.