Replication Agent Permissions

Replication Agent for Oracle uses the pds_username to connect to Oracle and must have certain Oracle permissions.

These permissions are required:

  • GRANT ALTER ANY PROCEDURE – required to manage procedures for replication.

  • GRANT ALTER DATABASE – required for Replication Agent to read from a Data Guard standby database transaction log.

  • GRANT ALTER ON TABLE_NAME – required to replicate user-defined datatypes if table-level supplemental logging has not been enabled for the specified TABLE_NAME.

  • GRANT ALTER SYSTEM – required to perform redo log archive operations.

  • GRANT CREATE ANY PROCEDURE – required to mark procedures for replication.

  • GRANT CREATE PROCEDURE – required to create rs_marker and rs_dump proc procedures.

  • GRANT CREATE PUBLIC SYNONYM – required to create synonyms for created tables in the primary database.

  • GRANT CREATE SEQUENCE – required to support replication.

  • GRANT CREATE SESSION – required to connect to Oracle.

  • GRANT CREATE TABLE – required to create tables in the primary database.

  • GRANT DROP PUBLIC SYNONYM – required to drop created synonyms.

  • GRANT EXECUTE_CATALOG_ROLE – required to use Oracle LogMiner.

  • GRANT EXECUTE ON DBMS_FLASHBACK – required to execute DBMS_FLASHBACK.get_system_change_number.

  • GRANT EXECUTE ON SYS.DBMS_LOCK – required to generate commit log records at the primary database.

  • GRANT SELECT ANY TRANSACTION – required to use Oracle LogMiner.

  • GRANT SELECT_CATALOG_ROLE – required to select from the DBA_* views.

  • GRANT SELECT ON SYS.OPQTYPE$ – required for DDL replication and XMLTYPE data replication.

  • GRANT SELECT ON SYS.RECYCLEBIN$ – required to use Oracle Flashback with Replication Agent.

  • GRANT SELECT ON SYS.ARGUMENT$ – required to process procedure DDL commands.

  • GRANT SELECT ON SYS.CCOL$ – required to support table replication (column constraint information).

  • GRANT SELECT ON SYS.CDEF$ – required to support table replication (constraint information).

  • GRANT SELECT ON SYS.COL$ – required to support table replication (column information).

  • GRANT SELECT ON SYS.COLLECTION$ – required to support table replication.

  • GRANT SELECT ON SYS.COLTYPE$ – required to support table replication.

  • GRANT SELECT ON SYS.CON$ – required to support table replication (constraint information).

  • GRANT SELECT ON SYS.DEFERRED_STG$ – required to suppress replication of compressed tables on Oracle 11g Release 2, on which LogMiner does not support compressed tables.

  • GRANT SELECT ON SYS.IND$ – required to identify indexes.

  • GRANT SELECT ON SYS.INDCOMPART$ – required to identify indexes.

  • GRANT SELECT ON SYS.INDPART$ – required to identify indexes.

  • GRANT SELECT ON SYS.INDSUBPART$ – required to identify indexes.

  • GRANT SELECT ON SYS.LOB$ – required for LOB replication support.

  • GRANT SELECT ON SYS.LOBCOMPPART$ – required to support partitioned LOB replication.

  • GRANT SELECT ON SYS.LOBFRAG$ – required to support partitioned LOB replication.

  • GRANT SELECT ON SYS.MLOG$ – required to filter materialized view log tables.

  • GRANT SELECT ON SYS.NTAB$ – required to support table replication.

  • GRANT SELECT ON SYS.OBJ$ – required for processing procedure DDL commands in the repository.

  • GRANT SELECT ON SYS.PROCEDUREINFO$ – required for procedure replication support.

  • GRANT SELECT ON SYS.SEG$ – required to suppress replication of compressed tables on versions of Oracle where LogMiner does not support compressed tables.

  • GRANT SELECT ON SYS.SEQ$ – required to support sequence replication.

  • GRANT SELECT ON SYS.SNAP$ – required to filter out materialized view tables.

  • GRANT SELECT ON SYS.TAB$ – required to support table replication.

  • GRANT SELECT ON SYS.TABCOMPART$ – required to support partitioned table replication.

  • GRANT SELECT ON SYS.TABPART$ – required to support partitioned table replication.

  • GRANT SELECT ON SYS.TABSUBPART$ – required to support partitioned table replication.

  • GRANT SELECT ON SYS.TS$ – required to identify tablespace encryption in Oracle 11g.

  • GRANT SELECT ON SYS.TYPE$ – required to process Oracle predefined and user-defined types.

  • GRANT SELECT ON SYS.USER$ – required for Oracle user identification.

  • GRANT SELECT ON SYS.ATTRIBUTE$ – required to process Oracle types.

  • GRANT SELECT ON V_$LOGMNR_CONTENTS – required to use Oracle LogMiner.

  • GRANT SELECT ON V_$LOGMNR_LOGS – required to use Oracle LogMiner.

  • GRANT SELECT ON SYS.PARTOBJ$ – required to support partitioned table replication.

  • GRANT SELECT ON SYS.ICOL$ – required to support the use of a unique index on columns as the primary key of the replication definition when there is no primary key defined for that table.

For supplemental logging at table level, the script generated from either ra_admin prepare for a new instance, or from ra_admin supplemental_logging_level, table, turns on table-level supplemental logging of these Oracle system tables:
  • SYS.ARGUMENT$
  • SYS.ATTRIBUTE$
  • SYS.COLLECTION$
  • SYS.COLTYPE$
  • SYS.DEFERRED_STG$
  • SYS.INDCOMPART$
  • SYS.INDPART$
  • SYS.INDSUBPART$
  • SYS.LOB$
  • SYS.LOBCOMPPART$
  • SYS.LOBFRAG$
  • SYS.MLOG$
  • SYS.NTAB$
  • SYS.OPQTYPE$
  • SYS.PROCEDUREINFO$
  • SYS.RECYCLEBIN$
  • SYS.SEQ$
  • SYS.SNAP$
  • SYS.TABPART$
  • SYS.TABCOMPART$
  • SYS.TABSUBPART$
  • SYS.TYPE$

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.

Replication Agent for Oracle requires the ALTER SYSTEM privilege to issue the ALTER SYSTEM ARCHIVE LOG command. If Replication Agent is configured to access only online Oracle redo logs, Replication Agent issues the ALTER SYSTEM ARCHIVE LOG SEQUENCE command when the online redo log is no longer needed for replication (as when all data from the log has been replicated). Regardless of online or archive log processing, Replication Agent uses the ALTER SYSTEM privilege to issue the ALTER SYSTEM ARCHIVE LOG CURRENT command when Replication Agent is instructed to move processing to the end of the Oracle log. By issuing the ALTER SYSTEM ARCHIVE LOG CURRENT command, Replication Agent ensures that the current redo log file does not contain old data. Replication Agent moves processing to the end of the Oracle redo log when requested by the move_truncpt option of the ra_locator command. Replication Agent may also move processing to the end of the Oracle redo log during migration from one version of Replication Agent to another.

Oracle 10g, 11g, and12c Privileges for the ra_admin_owner User

If you configure the ra_admin_owner user, these permissions are required:
  • GRANT CREATE SESSION
  • GRANT CREATE TABLE
  • GRANT CREATE SEQUENCE
  • GRANT CREATE ANY PROCEDURE
  • GRANT SELECT_CATALOG_ROLE
Parent topic: Oracle-Specific Considerations