Configure and verify the primary Oracle database for replication

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

To ensure that the Oracle redo log data is not overwritten before it is read by the Mirror Replication Agent, you must complete these tasks:

Verify the current archiving setting of the redo logs
Verify the supplemental logging of primary key data
Create an Oracle user and grant Oracle permissions

To verify the current archiving setting of the redo logs

Use SQLPLUS and connect to Oracle as a system administrator.
Run this command from SQLPLUS:
```
select log_mode from v$database; 
```
- If the archive log is on, the result should be:
```
LOG_MODE-------ARCHIVELOG
```
- To turn on log archiving:
  - Use SQLPLUS to connect to Oracle as a system administrator.
  - Run these commands from SQLPLUS:
```
shutdown;
startup mount;
alter database archivelog;
alter database open;
```

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

To verify the supplemental logging of primary key data

By default, Oracle does not log data from primary keys and unique indexes to the redo log. You must include the logging of these values for a successful replication of all table values.

Use SQLPLUS to connect to Oracle as a system administrator.
Run this command from SQLPLUS:
```
SELECT 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 should be:
```
SUP SUP--- ---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;

For more information about supplemental logging, see the Mirror Replication Agent Primary Database Guide.

To create an Oracle user and grant permissions

An Oracle database user needs to be created for use by Mirror 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.

From the following list of Oracle commands, create an Oracle user named “MA_USER” with the password “sybase,” and grant permissions to the user:

CREATE USER "MA_USER" PROFILE "DEFAULT" IDENTIFIED BY "sybase" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;GRANT "CONNECT" TO "MA_USER";GRANT "RESOURCE" TO "MA_USER";GRANT "SELECT_CATALOG_ROLE" TO "MA_USER";GRANT ALTER SESSION TO "MA_USER";GRANT ALTER SYSTEM TO "MA_USER";GRANT EXECUTE ON "SYS"."DBMS_FLASHBACK" TO "MA_USER";GRANT ALTER ANY PROCEDURE TO "MA_USER";

GRANT CREATE SESSION TO "MA_USER";GRANT CREATE TABLE TO "MA_USER";GRANT CREATE PROCEDURE TO "MA_USER";GRANT CREATE PUBLIC SYNONYM TO "MA_USER";GRANT DROP PUBLIC SYNONYM TO "MA_USER";GRANT SELECT ON SYS.OBJ$ TO "MA_USER";GRANT SELECT ON SYS.LOB$ TO "MA_USER";GRANT SELECT ON SYS.COLLECTION$ TO "MA_USER";GRANT CREATE SEQUENCE TO "MA_USER";GRANT SELECT ON SYS.CON$ TO "MA_USER";GRANT SELECT ON SYS.COLTYPE$ TO "MA_USER";

GRANT SELECT ON SYS.COL$ TO "MA_USER";

GRANT SELECT ON SYS.CDEF$ TO "MA_USER";GRANT SELECT ON SYS.USER$ TO "MA_USER";GRANT SELECT ON SYS.SEQ$ TO "MA_USER";

GRANT SELECT ON SYS.IND$ TO "MA_USER";

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

To verify the Oracle user roles created by the Mirror Replication Agent

Using SQLPLUS, run this command as the new Oracle user:

sqlplus MA_USER/sybaseSQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 20 22:16:12 2007Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

select GRANTED_ROLE from USER_ROLE_PRIVS;GRANTED_ROLE--------------------
CONNECT
RESOURCE
SELECT_CATALOG_ROLE

In addition, the user who starts the Mirror Replication Agent for Oracle 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 Mirror Replication Agent is configured to remove old archive files, the user must have update authority to the directory and the archive log files.