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.
For Oracle 10g and 11g, verify that the flashback feature is disabled.
Create an Oracle user and grant Oracle permissions.

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.

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;

Use SQLPLUS to connect to Oracle as a system administrator with sysdba privileges.

View the current recycle bin configuration:

select value from v$parameter

where name = ’recyclebin’;

View the contents of the recycle bin:
```
select * from dba_recyclebin;
```
Disable the recycle bin:
```
PURGE dba_recyclebin;
```
```
ALTER SYSTEM SET recyclebin = OFF;
```
If you are using Oracle RAC, disable the recycle bin for each instance in the cluster. For information about Replication Agent support for Oracle RAC, see the Replication Agent Primary Database Guide.

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” 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 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;

If you intend to replicate Oracle partitioned tables or partitioned large objects (LOBs), grant select privileges to the user ID specified by pds_username on certain system tables. See the Replication Agent Primary Database Guide.

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.