Automatically Archive and Compress Oracle Alert Logs

ADVERTISEMENT

Oracle alert logs grow continuously and can consume significant disk space if not managed regularly. This automated script helps keep the logs tidy by archiving and compressing the current alert log file. Once archived, Oracle will automatically generate a fresh log.

This is especially useful in production environments for both RAC and single-instance setups.

Shell Script: rotatealertlog.sh

################################################################################
# Title   : Oracle Alert Log Rotation Script
# Script  : rotatealertlog.sh
# Purpose : Automatically rotates and compresses Oracle alert logs.
#
# Features:
#   - Detects alert log location dynamically from v$parameter
#   - Supports argument-based ORACLE_SID input
#   - Backs up and compresses current alert log
#   - Ensures Oracle recreates a new log file
#   - Cron-friendly and RAC-compatible (run per instance)
#
# Author  : W3Buddy
# Version : 1.1
################################################################################

#!/bin/bash

# === Set Oracle Environment ===
ORACLE_SID=$1; export ORACLE_SID
ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
TO_DATE="20$(date +%y%m%d)"; export TO_DATE

# === Get Alert Log Directory ===
VAL_DUMP=$(${ORACLE_HOME}/bin/sqlplus -S /nolog <<EOF
conn / as sysdba
set pages 0 feedback off
SELECT value FROM v\\$parameter WHERE name='core_dump_dest';
exit;
EOF
)

LOCATION=$(echo ${VAL_DUMP} | awk '{print $1}')
ALERTDB=${LOCATION}/alert_${ORACLE_SID}.log
ELOG=$(echo ${ALERTDB} | sed s/cdump/trace/)

# === Archive and Compress Alert Log ===
if [ -e "$ELOG" ]; then
    mv ${ELOG} ${ELOG}_${TO_DATE}
    gzip ${ELOG}_${TO_DATE}
    > ${ELOG}
    echo "[$(date)] Alert log archived and compressed successfully: ${ELOG}_${TO_DATE}.gz"
else
    echo "[$(date)] Alert log not found: $ELOG"
fi

exit 0

⚙️ Setup Instructions

# ----------------------------------------------
# 1. Create the script file
# ----------------------------------------------
cd /u01/app/oracle/dbscripts
vi rotatealertlog.sh

# (Paste the full script above into the file and save)

# ----------------------------------------------
# 2. Make the script executable
# ----------------------------------------------
chmod +x rotatealertlog.sh

# ----------------------------------------------
# 3. Test the script manually
# ----------------------------------------------
./rotatealertlog.sh PRODDB

# Confirm alert log was archived and compressed:
ls -lh /u01/app/oracle/diag/rdbms/proddb/PRODDB/trace/alert_PRODDB.log*

# ----------------------------------------------
# 4. Schedule with cron (weekly on Friday 10:00 PM)
# ----------------------------------------------
crontab -e

# Add this line:
00 22 * * 5 /u01/app/oracle/dbscripts/rotatealertlog.sh PRODDB >> /tmp/rotate_alertlog.log 2>&1

# ----------------------------------------------
# 5. Confirm the cron entry
# ----------------------------------------------
crontab -l

ADVERTISEMENT