Oracle database requirements

This page describes the requirements for the Oracle database which is the source of replication.

Version requirements

  • Supported versions: 11.2, 12.1, 12.2, 18c, 19c;
  • Supported editions: XE, SE, SE2, PE, EE;
  • Supported database 138 different (see article about internationalization);
  • Supported platforms: Linux64, Solaris x64, Solaris Sparc;
  • Supported storage: file system (ext4, btrfs, zfs, xfs, sshfs), ASM (experimental);
  • Supported database block sizes: 2k, 4k, 8k, 16k, 32k;
  • Required options: none;
  • Both singe-tenant and multi-tenant databases are supported;
  • Supported reading Standby Data Guard databases;
  • Database must be in single instance mode (non RAC).

Configuration requirements

The database must be working in ARCHIVELOG mode and having enabled MINIMAL SUPPLEMENTAL LOGGING. Example script to configure database:

SELECT SUPPLEMENTAL_LOG_DATA_MIN, LOG_MODE FROM V$DATABASE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT SUPPLEMENTAL_LOG_DATA_MIN, LOG_MODE FROM V$DATABASE;

If you would like to capture UPDATE operations with full PRIMARY KEY columns – you can set supplemental logging for tables. This is not a requirement for OpenLogReplicator to work. Please read the article about adding table supplemental logging for more details.

For OFFLINE mode it is required that Fast Recovery Area is configured. Example:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 50G;
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/opt/oracle/fra';

To capture all transactions from the database it is recommended to have also FORCE LOGGING turned on. But it is not a must have requirement. Having this option turned off results just that some transactions might not be replicated.

SELECT FORCE_LOGGING FROM V$DATABASE;
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM ARCHIVE LOG CURRENT;
SELECT FORCE_LOGGING FROM V$DATABASE;

Option FORCE LOGGING is not required for operation. You may use non-logged transactions, but those would not be replicated since they produce no enough information in the redo log to identify what has been changed.

User requirements

Requirements for ONLINE mode:

  • user in the source database granted with rights to read some system tables (full list below)
  • physical access to read redo log files (online and archived), when running from a remote host access through SSHFS is possible

Requirements for ASM mode:

  • Access to ASM instance – a user with SYSASM (sic!) grants; currently reading from ASM is very slow and generates a lot of warnings in the ASM trace directory

Requirements for OFFLINE mode:

  • physical access to read redo log files

It is not a recommended to run OpenLogReplicator using SYS or SYSTEM account or any account with high privileges to connect to the database. The database user should have JUST the following privileges and absolutely nothing more:

GRANT SELECT ON SYS.CCOL$ TO <USER>;
GRANT SELECT ON SYS.CDEF$ TO <USER>;
GRANT SELECT ON SYS.COL$ TO <USER>;
GRANT SELECT ON SYS.CON$ TO <USER>;
GRANT SELECT ON SYS.DEFERRED_STG$ TO <USER>;
GRANT SELECT ON SYS.ECOL$ TO <USER>;
GRANT SELECT ON SYS.ICOL$ TO <USER>;
GRANT SELECT ON SYS.IND$ TO <USER>;
GRANT SELECT ON SYS.OBJ$ TO <USER>;
GRANT SELECT ON SYS.SEG$ TO <USER>;
GRANT SELECT ON SYS.TAB$ TO <USER>;
GRANT SELECT ON SYS.TABCOMPART$ TO <USER>;
GRANT SELECT ON SYS.TABPART$ TO <USER>;
GRANT SELECT ON SYS.TABSUBPART$ TO <USER>;
GRANT SELECT ON SYS.USER$ TO <USER>;
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO <USER>;
GRANT SELECT ON SYS.V_$DATABASE TO <USER>;
GRANT SELECT ON SYS.V_$DATABASE_INCARNATION TO <USER>;
GRANT SELECT ON SYS.V_$LOG TO <USER>;
GRANT SELECT ON SYS.V_$STANDBY_LOG TO <USER>;
GRANT SELECT ON SYS.V_$LOGFILE TO <USER>;
GRANT SELECT ON SYS.V_$PARAMETER TO <USER>;
GRANT SELECT ON SYS.V_$TRANSPORTABLE_PLATFORM TO <USER>;

Grant script has to be run from SYS account. For multitenant databases the user must be created and given grants in PDB.

Database connection used in ONLINE mode is used for:

  • checking database configuration including location of redo logs;
  • reading table schema, verifying if schema contains supplemental log groups;
  • getting archived redo log locations when reading from archived redo logs.

During replication from online redo logs the connection to the database is idle. No queries are run. During startup no query is run that would access any data in user tables.