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.

Create cleanup script

1. On the database host, create the /opt/fivetran directory:

   sudo mkdir -p /opt/fivetran
   

2. 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 whenever the staging directory changes.
   
   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 -exec chmod ug+w {} + 2>/dev/null
   
       # Delete files older than 1 minute
       find "$STAGING_DIR" -type f -mmin +1 -delete
   fi
   

3. 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 and path units

1. Run the following command to create the oracle-staging-cleanup.service unit, which runs the cleanup script when triggered by the path unit. 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
   Wants=oracle-staging-cleanup.path
   
   [Service]
   Type=oneshot
   ExecStart=/opt/fivetran/oracle_staging_chmod.sh
   User=<oracle_os_user>
   Group=<oracle_os_group>
   TimeoutStartSec=300
   StartLimitInterval=10s
   StartLimitBurst=0
   
   [Install]
   WantedBy=multi-user.target
   EOF
   

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

2. 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
   

Enable and start service

Reload the systemd configuration, enable and start the path unit, and then verify its status:

sudo systemctl daemon-reload
sudo systemctl enable oracle-staging-cleanup.path
sudo systemctl start oracle-staging-cleanup.path

# Verify - should show "active (waiting)"
sudo systemctl status oracle-staging-cleanup.path

active (waiting) means the service is idle and will activate automatically the next time a file is written to the staging directory.

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.