For more information about the format variables, refer to [LOG_ARCHIVE_FORMAT](https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams103.htm#REFRN10089) in [Oracle documentation](https://docs.oracle.com).

When this location field is not defined, then by `default` DSS will query the database for Oracle's initialization field **LOG_ARCHIVE_FORMAT**.

**Example 1:** <br />
Suppose an archive log file is named '**o1_mf_1\_41744_234324343.arc**' according to a certain Oracle archive log format pattern '**o1_mf\_\<thread\>\_\<sequence\>\_\<some_number\>.arc**'. The archive file is copied to some custom directory and compressed to '**o1_mf_1\_41744_234324343.arc.gz**' with the **.gz** extension added to its name. In such a case, specify the following pattern '**o1_mf\_%t\_%s\_%z.arc.gz**' in **LOG ARCHIVE FORMAT** field.

**Example 2:** <br />
Suppose the compressed archive log file is named **CQ1arch1_142657_1019376160.dbf.Z** with the **.Z** extension added to its name. In such a case, specify the following pattern '**CQ1arch%t\_%s\_%r.dbf.Z'** in **LOG ARCHIVE FORMAT** field.

The following are used by DSS if **LOG ARCHIVE FORMAT** field is not defined,

- For Oracle ASM system, the default name pattern used is '**thread\_%t_seq\_%s.%d.%d**'.

- Non-ASM system,
    
  - if Fast Recovery Area (FRA) is used, then the default name pattern used is '**o1_mf\_%t\_%s\_%z\_.arc**'
    
  - if FRA is not used, then DSS uses the following SQL query:
    
      ```shell
      SELECT value
      FROM v$parameter
      WHERE name = 'log_archive_format';
      ```
    
      DSS picks up the first valid archive destination and then finds the format as described above.
    
    <div class="callout callout-note">

To determine whether the destination uses FRA, DSS uses the following query:

    ```shell
    SELECT destination
    FROM v$archive_dest
    WHERE dest_name='LOG_ARCHIVE_DEST_[n]';
    ```

    For example, for destination 1, the query is as follows:

    ```shell
    SELECT destination
    FROM v$archive_dest
    WHERE dest_name='LOG_ARCHIVE_DEST_1';
    ```

    If the query returns **USE_DB_RECOVERY_FILE_DEST**, it indicates the destination uses FRA.

#### Capturing from Compressed Archive Log Files

DSS supports capturing changes from compressed archive log files that are moved from a 'primary' archive log directory to a custom directory. DSS automatically detects the compressed files, decompresses them, and reads data from them.

This feature is activated when the directory defined in **ARCHIVE WATCH DIRECTORY** is set to the custom directory.

Only **gzip** compressed files are supported.

#### Compressed Archive Log File Format

If the names of the compressed archive log files differ from the original names of the archive log files, then the relevant naming format should be defined in [**LOG ARCHIVE FORMAT**](#logarchiveformat) field.

Grants for Log-Based Capture

This section lists the grants required for capturing changes from Oracle database.

• The DSS database user must be granted the <b>create session</b> privilege:

  grant create session to <em>username</em>;
  

• To improve the performance of Activate Replication (dssactivate) for channels with a large number of tables (more than 150), DSS creates a temporary table (dss_sys_table) within a schema. The DSS database user must be granted the <b>create table</b> privilege so that DSS can automatically create this temporary table:

If you do not wish to provide <b>create table</b> privilege to the DSS database user, the alternative method is to manually create this temporary table using the SQL statement:

```shell
create global temporary table dss_sys_table (table_name varchar(128), table_owner varchar(128));
```

This temporary table is not used when capturing from a Data Guard standby database.

• The DSS database user must be granted the <b>select any table</b> privilege to replicate tables that are owned by other schemas (using the parameter Schema in action TableProperties). Else, the DSS database user must be granted the individual table-level <b>select</b> privileges.

  grant select any table to <em>username</em>;
  

• The DSS database user must be granted the <b>select any dictionary</b> privilege to read the data dictionaries in Oracle's SYS schema.

  grant select any dictionary to <em>username</em>;
  

  Alternatively, the DSS database user may be granted <b>select</b> privilege only for the required data dictionary objects:

  Click here for the specific grants

  grant select on sys.v_$archive_dest to <em>username</em>;
  grant select on sys.v_$archived_log to <em>username</em>;
  grant select on sys.v_$database to <em>username</em>;
  grant select on sys.v_$database_incarnation to <em>username</em>;
  
  /* The following grant (sys.v_$dnfs_files) is required for identifying the redo files located on DirectNFS */
  
  grant select on sys.v_$dnfs_files to <em>username</em>;
  
  /* The following grant (sys.v_$encryption_wallet) is required for decryption */
  
  grant select on sys.v_$encryption_wallet to <em>username</em>;
  
  grant select on sys.v_$log to <em>username</em>;
  grant select on sys.v_$logfile to <em>username</em>;
  
  
  /* The following grant (sys.v_$logmnr_contents) is required for Oracle LogMiner capture method. */
  
  grant select on sys.v_$logmnr_contents to <em>username</em>;
  
  grant select on sys.v_$nls_parameters to <em>username</em>;
  grant select on sys.v_$parameter to <em>username</em>;
  grant select on sys.v_$pdbs to <em>username</em>;
  
  /* The following grant (sys.v_$system_parameter) is required for reading the value of 'filesystemio_options' parameter which in turn is used for reading the redo logs */
  
  grant select on sys.v_$system_parameter to <em>username</em>;
  
  grant select on sys.all_cons_columns to <em>username</em>;
  grant select on sys.all_constraints to <em>username</em>;
  grant select on sys.all_ind_columns to <em>username</em>;
  grant select on sys.all_indexes to <em>username</em>;
  grant select on sys.all_lobs to <em>username</em>;
  grant select on sys.all_log_groups to <em>username</em>;
  grant select on sys.all_objects to <em>username</em>;
  grant select on sys.all_tab_cols to <em>username</em>;
  grant select on sys.all_tables to <em>username</em>;
  grant select on sys.all_triggers to <em>username</em>;
  grant select on sys.all_encrypted_columns to <em>username</em>;
  grant select on sys.dba_tablespaces to <em>username</em>;
  grant select on sys.obj$ to <em>username</em>;
  
  /* The following grant (sys.ecol$) is required for Oracle Database 11.2 and above since default values for added columns are stored differently. */
  
  grant select on sys.ecol$ to <em>username</em>;
  
  grant select on sys.user$ to <em>username</em>;
  grant select on sys.col$ to <em>username</em>;
  grant select on sys.enc$ to <em>username</em>;
  grant select on sys.tabpart$ to <em>username</em>;
  grant select on sys.tabsubpart$ to <em>username</em>;
  
  /* The following three grants are required for Refreshing Data (<b>dssrefresh</b> with option <b>-qrw</b>) and action <b>AdaptDDL</b> */
  
  grant select on sys.v_$locked_object to <em>username</em>;
  grant select on sys.v_$transaction to <em>username</em>;
  grant select on sys.dba_objects to <em>username</em>;
  

• The DSS database user must be granted the following privilege to capture <b>create table</b> statements and add supplemental logging to the newly created table(s):

  grant alter any table to <em>username</em>;
  

• The DSS database user must be granted the following privileges to use action DbSequence:

  grant select any sequence to <em>username</em>;
  
  grant select on sys.seq$ to <em>username</em>;
  

Supplemental Logging

DSS needs the Oracle supplemental logging feature enabled on replicate tables that it replicates. Otherwise, when an <b>update</b> is done, Oracle will only log the columns which are changed. But DSS also needs other data (e.g. the key columns) so that it can generate a full <b>update</b> statement on the target database. The Oracle supplemental logging can be set at the database level and on specific tables. In certain cases, this requirement can be dropped by defining action ColumnProperties with parameter CaptureFromRowId, for more information, see section Capturing from Oracle ROWID.

The very first time that Activate Replication (dssactivate) runs, it will check if the database allows any supplemental logging at all. If it is not, then Activate Replication will attempt statement <b>alter database add supplemental log data</b> (see Extra Grants for Supplemental Logging to execute this statement). Note that this statement will hang if other users are changing tables. This is called 'minimal supplemental logging'; it does not actually cause extra logging; that only happens once supplemental logging is also enabled on a specific table.

• To see the status of supplemental logging, use the following query:

  select log_group_type from all_log_groups where table_name='<em>mytable</em>'
  

• To check the current state of supplemental logging, use the following query:

   select supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all from v$database
  

  This query should return at least ['YES', 'NO', 'NO']

• To disable supplemental logging, use the following query:

  alter database drop supplemental log data
  

Activate Replication will normally only enable supplemental logging for the key columns of each replicated table, using statement <b>alter table</b> <em>table1</em> <b>add supplemental log data (primary key) columns</b>. But in some cases, Activate Replication will instead perform <b>alter table</b> <em>table1</em> <b>add supplemental log data (all) columns</b>, this will happen if either of the following condition is met:

• the key defined in the replication channel differs from the Oracle table's primary key or
• a table has any type of compression enabled or
• no key is defined in the replication channel and parameter NoDuplicateRows of action TableProperties is not defined on the table or
• one of the following actions with the mentioned parameters is defined on the table:

  • On the capture location:

    Location	Action	Parameter
    Source	ColumnProperties	CaptureExpression
    Source	Restrict	CaptureCondition, HorizColumn

  • On the target location:

    Location	Action	Parameter
    Target	FileFormat	BeforeUpdateColumns

  • On any location:

    Location	Action	Parameter
    *	CollisionDetect
    *	ColumnProperties	IntegrateExpression, Key or TimeKey
    *	Integrate	DbProc or Resilient
    *	Integrate	RenameExpression
    *	Restrict	AddressTo or IntegrateCondition

Supplemental Log Data Subset Database Replication

DSS does not support the 'Supplemental Log Data Subset Database Replication' option on Oracle 19c and higher versions. This feature must be disabled for your Oracle database when using DSS for replication.

To verify whether the database is enabled for subset database replication ('YES' or 'NO'), use the following query:

select supplemental_log_data_sr from v$database;

To disable this option, use the following query:

alter database drop supplemental log data subset database replication;

Capturing from Oracle ROWID

If none of the above requirements force DSS to enable supplemental logging on all columns, the requirement for supplemental logging on key columns can be removed by defining action ColumnProperties with parameters CaptureFromRowId and SurrogateKey to the channel. By doing so, DSS will treat the Oracle rowid column as part of the table and use it as the key column during replication, seamlessly integrating it into the target database. To ensure the capture of rowid values and their use as surrogate replication keys, the following two actions must be added to the channel prior to adding tables:

Grants for Supplemental Logging

The DSS database user must be granted the privileges mentioned in section Grants for Log-Based Capture and additionally the following grants for using supplemental logging:

• To execute <b>alter database add supplemental log data</b>, the DSS database user must have the sysdba privilege. Otherwise, DSS will generate an error message, requesting that a different user with this privilege execute this statement.

• If DSS needs to replicate tables that are owned by other schemas, then optionally the DSS user can also be granted <b>alter any table</b> privilege, so that Activate Replication (dssactivate) can enable supplemental logging on each of the replicated tables. If this privilege is not granted then Activate Replication will not be able to execute the <b>alter table…add supplemental log data</b> statements itself; instead, it will write all the statements that it needs to execute into a file and then write an error message which requests that a different user (who does have this privilege) execute these <b>alter table</b> statements.

Accessing Redo and Archive

The Oracle instance must have archiving enabled. If archiving is not enabled, DSS will lose changes if it falls behind the redo logs or it is suspended for a time.

Archiving of redo log files can be enabled by running the following statement as sysdba against a mounted but unopened database:

alter database archivelog

The current state of archiving can be checked using query:

select log_mode from v$database

The current archive destination can be checked with the query <b>select destination, status from v$archive_dest</b>. By default, this query will return values USE_DB_RECOVERY_FILE_DEST, VALID, which means that DSS will read changes from within the flashback recovery area. Alternatively, an archive destination can be defined with the following statement: <b>alter system set log_archive_dest_1='location=/disk1/arch'</b> and then restart the instance.

Often Oracle's RMAN will be configured to delete archive files after a certain time. But if they are deleted too quickly then DSS may fail if it falls behind or it is suspended for a time. This can be resolved by re-configuring RMAN so that archive files are guaranteed to be available for a specific longer period (e.g. 2 days).

Managing Archive or Redo Log files

If log-based capture is configured for an Oracle database then DSS may need to revert to reading the Oracle archive/redo files. However, it is important to note that each site has an existing backup/recovery regime (normal RMAN) that periodically deletes these Oracle archive files. To ensure that these archive files remain available for the DSS capture job(s), it is necessary to configure RMAN in such a way that the archive files are retained for a sufficient amount of time. The length of this time will vary depending on the replication environment, and factors such as how long could replication be interrupted, and after what period of time would the system be reset using a Refresh.