Why Does the Validating Access to Redo Log Setup Test Fail for ASM-Based Oracle Databases?

Issue

While configuring a connection to use the Binary Log Reader incremental sync method with a source database that stores redo logs in ASM, the Validating access to redo log setup test fails.

Environment

Connector: Oracle
Incremental sync method: Binary Log Reader
Storage: ASM
Platform: Oracle Exadata, Oracle Cloud Infrastructure (OCI), or another affected ASM-based environment

Cause

The Validating access to redo log setup test copies a redo log file from ASM into your staging directory to confirm Fivetran can access it, then deletes the copy. On some ASM-based systems, such as Oracle Exadata and OCI, the underlying DBMS_FILE_TRANSFER procedure creates this copy with read-only permissions. Because the file is read-only, Binary Log Reader cannot delete it as part of the test, and the test fails.

Resolution

Set up a systemd path unit and service unit on each database host. The path unit watches the staging directory and starts the service when the directory changes. The service automatically adds write permissions to the staging files and removes stale files.

The path unit relies on the kernel's inotify file change notifications. These notifications aren't always reliable for directories mounted over NFS. If your staging directory is on an NFS volume, use a systemd timer unit instead of the path unit. The timer unit runs the cleanup script every 10 seconds instead of waiting for a file change event. See Create systemd timer unit: NFS staging directories below.

Create cleanup script

On the database host, create the /opt/fivetran directory:
```
sudo mkdir -p /opt/fivetran
```

Save the following script as /opt/fivetran/oracle_staging_chmod.sh on the database host. Replace <staging_directory_path> with the absolute path to your staging directory:

#!/usr/bin/env bash
# Adds owner and group write permissions to staging files and removes stale files.
# Run by the systemd path unit or timer unit, depending on which trigger you set up.

STAGING_DIR="<staging_directory_path>"

if [ -d "$STAGING_DIR" ]; then
    # Add write permission for the file owner and group
    find "$STAGING_DIR" -type f ! -perm -u+w -printf "enable write access %p\n" -exec chmod ug+w {} +

    # Delete files older than 1 minute
    find "$STAGING_DIR" -type f -mmin +60 -printf "deleting %p\n" -delete
fi

Set the required ownership and permissions for the script. Replace <oracle_os_user> and <oracle_os_group> with the operating system user and group used by your Oracle database processes:
```
sudo chmod 750 /opt/fivetran/oracle_staging_chmod.sh
sudo chown <oracle_os_user>:<oracle_os_group> /opt/fivetran/oracle_staging_chmod.sh
```

Create systemd service unit

Run the following command to create the oracle-staging-cleanup.service unit. This unit is shared by both trigger options below, so create it only once regardless of which trigger you use. Replace <oracle_os_user> and <oracle_os_group> with the operating system user and group used by your Oracle database processes:

sudo tee /etc/systemd/system/oracle-staging-cleanup.service > /dev/null << 'EOF'
[Unit]
Description=Oracle Staging File Permission Cleanup
After=network-online.target

[Service]
Type=oneshot
ExecStart=/opt/fivetran/oracle_staging_chmod.sh
User=<oracle_os_user>
Group=<oracle_os_group>
TimeoutStartSec=300
StartLimitInterval=10s
StartLimitBurst=0
EOF

TimeoutStartSec=300 stops the cleanup service if it runs for longer than 5 minutes, preventing a stuck process from lingering.
StartLimitBurst=0 disables the default systemd start-rate limiting, so the service can keep triggering without being throttled when many staging files arrive in quick succession. This is relevant to the path unit trigger below.

Configure trigger unit

Configure one of the following trigger units based on your staging directory:

If your staging directory is not on an NFS volume, create the systemd path unit.
If your staging directory is on an NFS volume, create the systemd timer unit.

Enable only one of the two trigger units on a given host, either the path unit or the timer unit. Do not enable both, because either unit is sufficient to keep the staging directory clean.

Create systemd path unit

If your staging directory is not on an NFS volume, use this option. If your staging directory is on an NFS volume, skip to Create systemd timer unit: NFS staging directories instead.

Run the following command to create the oracle-staging-cleanup.path unit, which monitors the staging directory and triggers the cleanup service when the directory changes. Replace <staging_directory_path> with the absolute path to your staging directory:

sudo tee /etc/systemd/system/oracle-staging-cleanup.path > /dev/null << 'EOF'
[Unit]
Description=Watch Oracle Staging Directory for File Changes

[Path]
PathModified=<staging_directory_path>
Unit=oracle-staging-cleanup.service

[Install]
WantedBy=multi-user.target
EOF

Reload the systemd configuration, enable and start the path unit, then verify its status:
```
sudo systemctl daemon-reload
sudo systemctl enable oracle-staging-cleanup.path
sudo systemctl start oracle-staging-cleanup.path
sudo systemctl status oracle-staging-cleanup.path
```
The expected status is active (waiting), which means the service is idle and will activate automatically the next time a file is written to the staging directory.

Create systemd timer unit

If your staging directory is on an NFS volume, use this option. The timer runs the cleanup script every 10 seconds instead of waiting for a file change notification, so it doesn't depend on inotify events for the NFS-mounted staging directory.

Run the following command to create the oracle-staging-cleanup.timer unit, which triggers the cleanup service every 10 seconds:

sudo tee /etc/systemd/system/oracle-staging-cleanup.timer > /dev/null << 'EOF'
[Unit]
Description=Periodically Run Oracle Staging File Permission Cleanup

[Timer]
OnBootSec=10s
OnUnitActiveSec=10s
AccuracySec=1s
Unit=oracle-staging-cleanup.service

[Install]
WantedBy=timers.target
EOF

Reload the systemd configuration, enable and start the timer unit, then verify its status:
```
sudo systemctl daemon-reload
sudo systemctl enable oracle-staging-cleanup.timer
sudo systemctl start oracle-staging-cleanup.timer
sudo systemctl status oracle-staging-cleanup.timer
```
The expected status is active (waiting), which means the timer is active and will trigger the cleanup service every 10 seconds.

Monitor the cleanup service's activity

You can monitor the cleanup service's activity, including which files it adds write permissions to and deletes, at any time by running:

sudo journalctl -u oracle-staging-cleanup.service

Re-run setup test

Once the service is running, re-run the Validating access to redo log setup test and verify that it passes. The cleanup service adds owner and group write permissions to the temporary file so that Fivetran can delete it.

If your database is a RAC cluster, repeat these steps on every RAC node that may write to the staging directory, using the identical staging directory path on each node.