Automating Archive Log Cleanup in DR – Oracle Standby Sync Script
In a DR (Disaster Recovery) Oracle environment, archive logs continuously flow from the primary to the standby database. Over time, these logs can accumulate and consume large amounts of disk space.
To manage this efficiently, I’ve implemented an automated shell script that monitors synchronization between Primary and Standby and removes archive logs older than 2 days — but only when both databases are fully in sync.
Script:
#!/bin/bash
. /home/oracle/.bash_profile
DIFF=$(sqlplus -s / as sysdba <<EOF
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF
SELECT (ARCH.SEQUENCE# - APPL.SEQUENCE#)
FROM (SELECT THREAD#, SEQUENCE# FROM V\$ARCHIVED_LOG WHERE (THREAD#, FIRST_TIME) IN
(SELECT THREAD#, MAX(FIRST_TIME) FROM V\$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD#, SEQUENCE# FROM V\$LOG_HISTORY WHERE (THREAD#, FIRST_TIME) IN
(SELECT THREAD#, MAX(FIRST_TIME) FROM V\$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#;
EXIT;
EOF
)
if [ "$DIFF" -eq 0 ]; then
echo "Standby in sync — deleting archive logs older than 2 days..."
rman target / <<EOF
crosscheck archivelog all;
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-2';
EXIT;
EOF
else
echo " Archive gap exists — skipping deletion."
fi
Scheduled in Cron in DR server to run every hour
0 * * * * /u01/scripts/delete_arch_if_synced.sh > /u01/scripts/logs/delete_arch.log 2>&1
In case of DR system as RAC, we can use below.
#!/bin/bash
# ------------------------------------------------------------------
# Script Name : check_archive_gap_and_cleanup.sh
# Purpose : Check archive apply lag and delete old archives if in sync
# Author : DBA Team
# ------------------------------------------------------------------
# Load Oracle environment
. /home/oracle/.bash_profile
MAX_DIFF=0
TMP_FILE=/tmp/archive_gap_diff.txt
# Clean old temp file
> $TMP_FILE
# Run SQL to get archive vs applied sequence gap
sqlplus -s / as sysdba <<EOF > $TMP_FILE
SET HEADING OFF FEEDBACK OFF PAGESIZE 0 LINESIZE 200
SELECT
arch.thread# || ' ' ||
arch.sequence# || ' ' ||
appl.sequence# || ' ' ||
(arch.sequence# - appl.sequence#)
FROM
(SELECT thread#, MAX(sequence#) sequence#
FROM gv\$archived_log
GROUP BY thread#) arch,
(SELECT thread#, MAX(sequence#) sequence#
FROM gv\$log_history
GROUP BY thread#) appl
WHERE arch.thread# = appl.thread#
ORDER BY arch.thread#;
EXIT;
EOF
# Process output
while read THREAD RECV_SEQ APPL_SEQ DIFF
do
# Validate numeric diff
if [[ "$DIFF" =~ ^[0-9]+$ ]]; then
echo "Thread $THREAD : Received=$RECV_SEQ Applied=$APPL_SEQ Diff=$DIFF"
if [ "$DIFF" -gt "$MAX_DIFF" ]; then
MAX_DIFF=$DIFF
fi
else
echo "Thread $THREAD : Unable to determine archive gap"
MAX_DIFF=999
fi
done < $TMP_FILE
echo "--------------------------------------------------"
echo "Maximum archive apply gap detected : $MAX_DIFF"
echo "--------------------------------------------------"
# Decision logic
if [ "$MAX_DIFF" -eq 0 ]; then
echo "Standby is fully synchronized."
echo "Proceeding with archive log cleanup (older than 2 days)..."
rman target / <<EOF
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-2';
EXIT;
EOF
else
echo "Archive gap exists (MAX_DIFF=$MAX_DIFF)."
echo "Skipping archive log deletion to avoid data loss."
fi
# Cleanup
rm -f $TMP_FILE
Post a Comment
Post a Comment