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, you must complete the following tasks:

• Verify the current archiving setting of the redo logs.

• Verify the supplemental logging of primary key data.

• For Oracle 10g, verify that the flashback feature is disabled.

• Create an Oracle user and granting Oracle permissions.

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

1. Use SQLPLUS to connect to Oracle as a system administrator.

2. Run this command from SQLPLUS:

   select log_mode from v$database;
   

   • If the archive log is on, the result should be:

     LOG_MODE
     --------
     ARCHIVELOG
     

3. To turn on log archiving, run these commands:

   shutdown;
   startup mount;
   alter database archivelog;
   alter database open;
   

For more information about Oracle redo logs and archiving, see the Replication Agent Primary Database Guide.

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

1. Use SQLPLUS to connect to Oracle as a system administrator.

2. • For Oracle 9, run this command from SQLPLUS:

     SELECT SUPPLEMENTAL_LOG_DATA_PK,
     SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
     

     For Oracle 9, if logging of primary key and unique index values is enabled, the return values should be:

     SUP SUP--- ---YES YES
     

   • For Oracle 10, run this command from SQLPLUS

     SELECT SUPPLEMENTAL_LOG_DATA_MIN,
     SUPPLEMENTAL_LOG_DATA_PK,
     

     SUPPLEMENTAL_LOG_DATA_UI
     FROM V$DATABASE;
     

     For Oracle 10, if logging of primary key and unique index values is enabled, the return values should be:

     SUP SUP SUP--- --- ---YES YES YES
     

3. If the result is different, turn on supplemental logging by executing these commands for Oracle 9 and Oracle 10:

   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY
   KEY, UNIQUE INDEX) COLUMNS;
   ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
   

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

2. View the current recycle bin configuration:

   select inst_id, value from v$parameter;
   

3. View the contents of the recycle bin:

   select * from dba_recyclebin;
   

4. Disable the recycle bin with the following commands:

   PURGE dba_recyclebin;
   

   ALTER SYSTEM SET recyclebin = OFF;
   

   If you are using Oracle RAC, you must disable the recycle bin for each instance in the cluster.

1. 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.USER$ TO "RA_USER";
   GRANT SELECT ON SYS.SEQ$ TO "RA_USER";
   GRANT SELECT ON SYS.IND$ TO "RA_USER";
   

1. 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.