Oracle Shell Scripts Automation
Automate the noise. Protect the database. Sleep better.
Oracle databases rarely fail loudly at first. They whisper.
A GoldenGate extract falls behind by twenty minutes. A standby database starts drifting. Archive logs pile up because the cleanup job missed a mount point. A blocking session sits in production long enough to become a customer-impacting outage. A tablespace creeps toward 95 percent, but nobody sees it until the application starts throwing errors.
The best DBAs do not wait for the phone to ring.
They automate the boring checks, the risky gaps, and the repeatable recovery steps. Not because shell scripts are glamorous, but because shell scripts are simple, portable, transparent, and brutally effective when written well.
This article walks through practical Oracle shell script automation patterns for real-world DBA operations, including:
GoldenGate process monitoring
Standby lag detection
RMAN automated backups and archive cleanup
Blocking session alerts
ASM and tablespace monitoring
ORA error tracking with ADRCI
Invalid login auditing
Alert log rotation
Filesystem threshold monitoring
Parallel Data Pump operations
And more production-ready automation ideas
The goal is not to build a giant framework. The goal is to build reliable operational guardrails that catch problems early, create clear alerts, and reduce manual DBA toil.
Why Shell Scripts Still Matter in Oracle Operations
Modern platforms have monitoring stacks, dashboards, exporters, agents, event buses, and cloud-native observability tools. Use them where they make sense.
But shell scripts still matter because they are:
Easy to run from cron
Easy to read during an incident
Easy to move between servers
Easy to version in Git
Easy to combine with SQL*Plus, RMAN, Data Pump, ADRCI, and GoldenGate commands
Easy to debug when a production system is under pressure
A good shell script is not a replacement for enterprise monitoring. It is a sharp local probe that knows exactly what to check and how to report it.
Think of these scripts as small production sentries. Each one watches a specific failure mode.
A Simple Standard Script Template
Before writing many automation scripts, start with a standard pattern. Every script should have consistent logging, environment loading, exit handling, and alerting.
#!/bin/bash
# =====================================================================
# Script Name : oracle_check_template.sh
# Purpose : Standard Oracle automation script template
# Author : DBA Team
# =====================================================================
set -o pipefail
HOSTNAME=$(hostname)
SCRIPT_NAME=$(basename "$0")
RUN_DATE=$(date '+%Y-%m-%d %H:%M:%S')
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
LOG_FILE="${LOG_DIR}/${SCRIPT_NAME%.sh}_$(date '+%Y%m%d').log"
MAIL_TO="dba-team@example.com"
mkdir -p "$LOG_DIR"
log_msg() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}
send_alert() {
SUBJECT="$1"
BODY="$2"
echo -e "$BODY" | mailx -s "$SUBJECT" "$MAIL_TO"
}
load_oracle_env() {
export ORACLE_SID="$1"
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
}
log_msg "Starting ${SCRIPT_NAME} on ${HOSTNAME}"
# Main logic goes here
log_msg "Completed ${SCRIPT_NAME}"
exit 0
Takeaway
Do not let every script invent its own logging and alerting style. Standardize early. During a production incident, consistency is worth more than cleverness.
1. GoldenGate Process Monitoring
GoldenGate is powerful, but it is also unforgiving. A stopped extract, abended replicat, or growing lag can quickly become a business problem.
The script below checks GoldenGate processes using ggsci and alerts when a process is stopped, abended, or lagging beyond a defined threshold.
GoldenGate Monitoring Script
#!/bin/bash
GG_HOME="/u01/app/ogg"
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
LOG_FILE="$LOG_DIR/gg_monitor_$(date '+%Y%m%d').log"
MAIL_TO="dba-team@example.com"
LAG_THRESHOLD_MIN=15
mkdir -p "$LOG_DIR"
log_msg() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}
send_alert() {
SUBJECT="$1"
BODY="$2"
echo -e "$BODY" | mailx -s "$SUBJECT" "$MAIL_TO"
}
cd "$GG_HOME" || exit 1
GG_STATUS=$(./ggsci <<EOF
info all
exit
EOF
)
echo "$GG_STATUS" >> "$LOG_FILE"
ABENDED=$(echo "$GG_STATUS" | egrep -i "ABENDED|STOPPED")
if [ -n "$ABENDED" ]; then
send_alert "CRITICAL: GoldenGate process issue on $(hostname)" "GoldenGate issue detected:\n\n$ABENDED\n\nFull status:\n$GG_STATUS"
log_msg "GoldenGate stopped or abended process detected."
fi
# Optional lag check for specific processes
GG_LAG=$(./ggsci <<EOF
lag *
exit
EOF
)
echo "$GG_LAG" >> "$LOG_FILE"
LAG_ALERT=$(echo "$GG_LAG" | egrep -i "Lag at Chkpt" | awk -F':' '{print $NF}' | egrep '[0-9]+')
# This basic example logs lag output. In production, parse your GoldenGate version format carefully.
log_msg "GoldenGate lag check completed."
Better GoldenGate Checks
A stronger GoldenGate monitor should check:
Extract status
Replicat status
Manager status
Checkpoint lag
Trail file growth
Oldest unprocessed trail
Abended process reports
Discard file growth
For example, a replicat may still be running but falling behind. That is not healthy. Your script should alert on both failure and degraded performance.
Takeaway
GoldenGate monitoring should not only ask, “Is it running?” It should ask, “Is it keeping up?”
2. Standby Lag Detection
A Data Guard standby database is only useful if it is reasonably current. Lag can hide quietly until a failover is needed.
This script checks apply lag and transport lag from v$dataguard_stats.
Standby Lag Script
#!/bin/bash
ORACLE_SID="STBYDB"
MAIL_TO="dba-team@example.com"
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
LOG_FILE="$LOG_DIR/standby_lag_$(date '+%Y%m%d').log"
LAG_LIMIT_MIN=10
mkdir -p "$LOG_DIR"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
RESULT=$(sqlplus -s / as sysdba <<EOF
set pages 0 feedback off verify off heading off echo off
select name || '=' || value
from v\$dataguard_stats
where name in ('transport lag','apply lag');
exit
EOF
)
echo "[$(date)]" >> "$LOG_FILE"
echo "$RESULT" >> "$LOG_FILE"
APPLY_LAG=$(echo "$RESULT" | grep "apply lag" | awk -F'+' '{print $2}' | awk '{print $1}')
TRANSPORT_LAG=$(echo "$RESULT" | grep "transport lag" | awk -F'+' '{print $2}' | awk '{print $1}')
# Simple string-based alert. Adjust parser for your exact lag format.
if echo "$RESULT" | egrep -q "[0-9]{2}:[0-9]{2}:[0-9]{2}"; then
echo -e "Standby lag detected on $(hostname):\n\n$RESULT" | \
mailx -s "WARNING: Data Guard lag on $(hostname)" "$MAIL_TO"
fi
More Reliable SQL-Based Lag Check
A cleaner method is to convert lag into minutes inside SQL.
set pages 0 feedback off verify off heading off
select round((sysdate - max(next_time)) * 24 * 60) as archive_lag_minutes
from v$archived_log
where applied = 'YES';
You can then compare the numeric result in shell.
LAG_MIN=$(sqlplus -s / as sysdba <<EOF
set pages 0 feedback off verify off heading off
select round((sysdate - max(next_time)) * 24 * 60)
from v\$archived_log
where applied = 'YES';
exit
EOF
)
LAG_MIN=$(echo "$LAG_MIN" | xargs)
if [ "$LAG_MIN" -gt "$LAG_LIMIT_MIN" ]; then
echo "Standby apply lag is ${LAG_MIN} minutes" | \
mailx -s "WARNING: Standby lag ${LAG_MIN} minutes on $(hostname)" "$MAIL_TO"
fi
Takeaway
A standby database is not protected just because managed recovery is running. Measure the lag. Alert before the gap becomes painful.
3. RMAN Automated Backups and Archive Cleanup
Backups are only useful if they run, complete successfully, and are recoverable. A silent backup failure is one of the most dangerous operational failures in Oracle administration.
This example runs a compressed RMAN backup, backs up archive logs, deletes archive logs after backup, and logs output.
RMAN Backup Shell Script
#!/bin/bash
ORACLE_SID="PRODDB"
BACKUP_BASE="/backup/oracle/PRODDB"
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
LOG_FILE="$LOG_DIR/rman_backup_${ORACLE_SID}_$(date '+%Y%m%d_%H%M%S').log"
MAIL_TO="dba-team@example.com"
mkdir -p "$LOG_DIR"
mkdir -p "$BACKUP_BASE"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
rman target / log="$LOG_FILE" <<EOF
run {
allocate channel c1 device type disk format '${BACKUP_BASE}/db_%d_%T_%U.bkp';
allocate channel c2 device type disk format '${BACKUP_BASE}/db_%d_%T_%U.bkp';
backup as compressed backupset database plus archivelog;
delete noprompt archivelog all backed up 1 times to disk;
delete noprompt obsolete;
release channel c1;
release channel c2;
}
EOF
RMAN_STATUS=$?
if [ $RMAN_STATUS -ne 0 ] || grep -q "RMAN-\|ORA-" "$LOG_FILE"; then
mailx -s "CRITICAL: RMAN backup failed for ${ORACLE_SID} on $(hostname)" "$MAIL_TO" < "$LOG_FILE"
exit 1
else
echo "RMAN backup completed successfully for ${ORACLE_SID}" | \
mailx -s "SUCCESS: RMAN backup completed for ${ORACLE_SID}" "$MAIL_TO"
fi
RMAN Archive-Only Cleanup Script
Sometimes archive logs need a dedicated cleanup job. This should be done carefully. Never delete archive logs blindly unless you understand your recovery and standby requirements.
#!/bin/bash
ORACLE_SID="PRODDB"
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
LOG_FILE="$LOG_DIR/rman_arch_cleanup_${ORACLE_SID}_$(date '+%Y%m%d_%H%M%S').log"
MAIL_TO="dba-team@example.com"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
rman target / log="$LOG_FILE" <<EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog all backed up 1 times to disk completed before 'sysdate-1';
EOF
if grep -q "RMAN-\|ORA-" "$LOG_FILE"; then
mailx -s "WARNING: Archive cleanup issue on $(hostname)" "$MAIL_TO" < "$LOG_FILE"
fi
Backup Automation Rules
A production RMAN script should:
Capture logs with timestamps
Alert on RMAN and ORA errors
Use multiple channels where appropriate
Crosscheck before deleting expired backups
Avoid deleting archive logs needed by standby databases
Run
restore validateon a scheduleTrack backup duration and size
Keep backup logs long enough for audit and troubleshooting
Takeaway
The backup job is not finished when RMAN exits. It is finished when you know it completed cleanly, logs were reviewed, and recovery assumptions are still valid.
4. Blocking Session Alerts
Blocking sessions create some of the most frustrating database incidents because the database may still be “up,” but the application is effectively stuck.
The script below checks for blocking sessions and emails a summary.
Blocking Session Script
#!/bin/bash
ORACLE_SID="PRODDB"
MAIL_TO="dba-team@example.com"
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
LOG_FILE="$LOG_DIR/blocking_sessions_$(date '+%Y%m%d_%H%M').log"
mkdir -p "$LOG_DIR"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
sqlplus -s / as sysdba <<EOF > "$LOG_FILE"
set lines 220 pages 100
col blocker format a20
col waiter format a20
col username format a20
col machine format a35
col event format a45
select
s1.sid || ',' || s1.serial# as blocker,
s1.username,
s1.machine,
s2.sid || ',' || s2.serial# as waiter,
s2.event,
s2.seconds_in_wait
from v\$session s1
join v\$session s2
on s1.sid = s2.blocking_session
where s2.blocking_session is not null
order by s2.seconds_in_wait desc;
EOF
if [ $(grep -v '^$' "$LOG_FILE" | wc -l) -gt 3 ]; then
mailx -s "WARNING: Blocking sessions detected in ${ORACLE_SID} on $(hostname)" "$MAIL_TO" < "$LOG_FILE"
fi
Stronger Blocking SQL
For deeper troubleshooting, include SQL ID, module, and blocking object.
set lines 220 pages 100
col blocker format a18
col waiter format a18
col username format a18
col module format a35
col event format a45
col sql_id format a15
select
bs.sid || ',' || bs.serial# as blocker,
bs.username,
bs.module,
bs.sql_id,
ws.sid || ',' || ws.serial# as waiter,
ws.event,
ws.seconds_in_wait
from v$session ws
join v$session bs
on ws.blocking_session = bs.sid
where ws.blocking_session is not null
order by ws.seconds_in_wait desc;
Takeaway
Blocking session alerts should include enough context for action. A message that says “blocking exists” is weak. A message that names the blocker, waiter, module, SQL ID, and wait time is useful.
5. ASM Disk Group Monitoring
ASM disk groups can fill quickly, especially during backup, rebalance, batch, or archive generation spikes.
This script checks ASM disk group usage and sends an alert when usage crosses a threshold.
ASM Monitoring Script
#!/bin/bash
ORACLE_SID="+ASM"
MAIL_TO="dba-team@example.com"
THRESHOLD=85
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
LOG_FILE="$LOG_DIR/asm_usage_$(date '+%Y%m%d_%H%M').log"
mkdir -p "$LOG_DIR"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
sqlplus -s / as sysasm <<EOF > "$LOG_FILE"
set pages 100 lines 200 feedback off
col name format a20
select
name,
total_mb,
free_mb,
round((1 - free_mb / total_mb) * 100, 2) used_pct
from v\$asm_diskgroup
order by used_pct desc;
EOF
ALERTS=$(sqlplus -s / as sysasm <<EOF
set pages 0 feedback off heading off verify off
select name || ':' || round((1 - free_mb / total_mb) * 100, 2)
from v\$asm_diskgroup
where round((1 - free_mb / total_mb) * 100, 2) >= $THRESHOLD;
EOF
)
if [ -n "$(echo "$ALERTS" | xargs)" ]; then
mailx -s "WARNING: ASM disk group threshold exceeded on $(hostname)" "$MAIL_TO" < "$LOG_FILE"
fi
Takeaway
ASM monitoring should track both current usage and growth trend. A disk group at 82 percent and growing fast may be more dangerous than one at 90 percent and stable.
6. Tablespace Monitoring
Tablespace exhaustion can stop applications cold. Monitoring should account for autoextend, max size, and free space.
Tablespace Usage Script
#!/bin/bash
ORACLE_SID="PRODDB"
MAIL_TO="dba-team@example.com"
THRESHOLD=85
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
LOG_FILE="$LOG_DIR/tablespace_usage_$(date '+%Y%m%d_%H%M').log"
mkdir -p "$LOG_DIR"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
sqlplus -s / as sysdba <<EOF > "$LOG_FILE"
set lines 220 pages 100
col tablespace_name format a30
select
df.tablespace_name,
round(df.used_mb, 2) used_mb,
round(df.max_mb, 2) max_mb,
round((df.used_mb / df.max_mb) * 100, 2) used_pct
from (
select
d.tablespace_name,
sum(d.bytes - nvl(f.free_bytes, 0)) / 1024 / 1024 used_mb,
sum(case when d.autoextensible = 'YES' then d.maxbytes else d.bytes end) / 1024 / 1024 max_mb
from dba_data_files d
left join (
select file_id, sum(bytes) free_bytes
from dba_free_space
group by file_id
) f on d.file_id = f.file_id
group by d.tablespace_name
) df
order by used_pct desc;
EOF
ALERTS=$(sqlplus -s / as sysdba <<EOF
set pages 0 feedback off heading off verify off
select tablespace_name
from (
select
d.tablespace_name,
sum(d.bytes - nvl(f.free_bytes, 0)) used_bytes,
sum(case when d.autoextensible = 'YES' then d.maxbytes else d.bytes end) max_bytes
from dba_data_files d
left join (
select file_id, sum(bytes) free_bytes
from dba_free_space
group by file_id
) f on d.file_id = f.file_id
group by d.tablespace_name
)
where round((used_bytes / max_bytes) * 100, 2) >= $THRESHOLD;
EOF
)
if [ -n "$(echo "$ALERTS" | xargs)" ]; then
mailx -s "WARNING: Tablespace threshold exceeded in ${ORACLE_SID}" "$MAIL_TO" < "$LOG_FILE"
fi
Temporary Tablespace Check
Temp usage can spike during sorts, reports, ETL, and bad execution plans.
select
tablespace_name,
round(used_blocks * block_size / 1024 / 1024) used_mb,
round(free_blocks * block_size / 1024 / 1024) free_mb,
round((used_blocks / total_blocks) * 100, 2) used_pct
from v$temp_space_header;
Takeaway
Tablespace monitoring must understand max capacity, not just current file size. Autoextend can delay a problem, but it cannot save you from the filesystem filling up.
7. ORA Error Tracking With ADRCI
Oracle diagnostic logs contain signals that often appear before users report issues. ADRCI helps query the Automatic Diagnostic Repository from scripts.
ADRCI ORA Error Script
#!/bin/bash
MAIL_TO="dba-team@example.com"
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
OUT_FILE="$LOG_DIR/adrci_ora_errors_$(date '+%Y%m%d_%H%M').log"
ADR_HOME="diag/rdbms/proddb/PRODDB"
mkdir -p "$LOG_DIR"
adrci exec="set home $ADR_HOME; show alert -p \"message_text like '%ORA-%' and originating_timestamp > systimestamp-1/24\"" > "$OUT_FILE"
if grep -q "ORA-" "$OUT_FILE"; then
mailx -s "WARNING: Recent ORA errors found on $(hostname)" "$MAIL_TO" < "$OUT_FILE"
fi
Common ORA Patterns Worth Watching
Watch especially for recurring or business-impacting errors such as:
ORA-00600 internal errors
ORA-07445 exception errors
ORA-01555 snapshot too old
ORA-01652 unable to extend temp segment
ORA-01653 unable to extend table
ORA-00257 archiver stuck
ORA-01017 invalid username/password
ORA-03113 end-of-file on communication channel
ORA-04031 shared pool memory errors
Takeaway
Alert logs are not archives for later. They are live telemetry. Query them continuously and alert on patterns that predict pain.
8. Invalid Login Auditing
Repeated invalid login attempts may indicate an application password issue, an expired account, a misconfigured batch job, or a security event.
Invalid Login Audit Script
#!/bin/bash
ORACLE_SID="PRODDB"
MAIL_TO="security-team@example.com,dba-team@example.com"
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
LOG_FILE="$LOG_DIR/invalid_logins_$(date '+%Y%m%d_%H%M').log"
THRESHOLD=10
mkdir -p "$LOG_DIR"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
sqlplus -s / as sysdba <<EOF > "$LOG_FILE"
set lines 220 pages 100
col username format a25
col userhost format a40
col timestamp format a30
select
username,
userhost,
returncode,
count(*) attempts,
min(timestamp) first_attempt,
max(timestamp) last_attempt
from dba_audit_session
where returncode = 1017
and timestamp > sysdate - 1/24
group by username, userhost, returncode
order by attempts desc;
EOF
COUNT=$(sqlplus -s / as sysdba <<EOF
set pages 0 feedback off heading off verify off
select count(*)
from dba_audit_session
where returncode = 1017
and timestamp > sysdate - 1/24;
EOF
)
COUNT=$(echo "$COUNT" | xargs)
if [ "$COUNT" -ge "$THRESHOLD" ]; then
mailx -s "SECURITY: High invalid login count in ${ORACLE_SID}" "$MAIL_TO" < "$LOG_FILE"
fi
Unified Auditing Example
For newer environments using unified auditing, query UNIFIED_AUDIT_TRAIL.
select
dbusername,
userhost,
action_name,
return_code,
count(*) attempts,
min(event_timestamp) first_attempt,
max(event_timestamp) last_attempt
from unified_audit_trail
where return_code = 1017
and event_timestamp > systimestamp - interval '1' hour
group by dbusername, userhost, action_name, return_code
order by attempts desc;
Takeaway
Invalid logins are not just noise. They often reveal broken deployments, stale credentials, or hostile probing.
9. Alert Log Rotation
Oracle alert logs can grow large, especially on busy systems or systems with repeated warnings. Rotation keeps logs manageable without deleting useful history.
Alert Log Rotation Script
#!/bin/bash
ORACLE_SID="PRODDB"
DIAG_DIR="/u01/app/oracle/diag/rdbms/proddb/PRODDB/trace"
ALERT_LOG="$DIAG_DIR/alert_${ORACLE_SID}.log"
ARCHIVE_DIR="$DIAG_DIR/archive"
RETENTION_DAYS=30
mkdir -p "$ARCHIVE_DIR"
if [ -f "$ALERT_LOG" ]; then
SIZE_MB=$(du -m "$ALERT_LOG" | awk '{print $1}')
if [ "$SIZE_MB" -gt 100 ]; then
TS=$(date '+%Y%m%d_%H%M%S')
cp "$ALERT_LOG" "$ARCHIVE_DIR/alert_${ORACLE_SID}_${TS}.log"
: > "$ALERT_LOG"
gzip "$ARCHIVE_DIR/alert_${ORACLE_SID}_${TS}.log"
fi
fi
find "$ARCHIVE_DIR" -name "alert_${ORACLE_SID}_*.log.gz" -mtime +$RETENTION_DAYS -delete
Safer Rotation Notes
The truncation command below clears a file while preserving permissions and inode:
: > "$ALERT_LOG"
That can be useful when a process expects the same file path to exist.
Takeaway
Rotating alert logs is housekeeping, but good housekeeping prevents diagnostic chaos during outages.
10. Filesystem Threshold Monitoring
Oracle problems are often Linux problems wearing database clothing. Full filesystems can break archive generation, backups, audit writes, Data Pump jobs, and diagnostic logging.
Filesystem Monitoring Script
#!/bin/bash
MAIL_TO="dba-team@example.com"
THRESHOLD=85
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
LOG_FILE="$LOG_DIR/filesystem_usage_$(date '+%Y%m%d_%H%M').log"
mkdir -p "$LOG_DIR"
df -hP > "$LOG_FILE"
ALERTS=$(df -P | awk -v threshold="$THRESHOLD" '
NR>1 {
gsub("%", "", $5);
if ($5 >= threshold) print $0;
}')
if [ -n "$ALERTS" ]; then
echo -e "Filesystem threshold exceeded on $(hostname):\n\n$ALERTS\n\nFull df output:\n$(cat $LOG_FILE)" | \
mailx -s "WARNING: Filesystem usage above ${THRESHOLD}% on $(hostname)" "$MAIL_TO"
fi
Add Inode Monitoring
A filesystem can fail even when it has free space if it runs out of inodes.
df -ihP
Add inode checks for directories that hold many small files, such as trace files, audit files, application logs, and dump directories.
Takeaway
Never monitor only the database. Monitor the ground it stands on: filesystems, inodes, mount points, permissions, and growth trends.
11. Parallel Data Pump Operations
Data Pump is often used for refreshes, migrations, object-level exports, and emergency data movement. Automation makes exports repeatable and less error-prone.
Parallel Export Script
#!/bin/bash
ORACLE_SID="PRODDB"
EXPORT_DIR_NAME="DATA_PUMP_DIR"
DUMP_PREFIX="prod_schema_exp"
SCHEMAS="APP_OWNER"
PARALLEL=4
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
RUN_TS=$(date '+%Y%m%d_%H%M%S')
MAIL_TO="dba-team@example.com"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
expdp system/YourSecurePassword \
schemas=${SCHEMAS} \
directory=${EXPORT_DIR_NAME} \
dumpfile=${DUMP_PREFIX}_${RUN_TS}_%U.dmp \
logfile=${DUMP_PREFIX}_${RUN_TS}.log \
parallel=${PARALLEL} \
compression=all \
metrics=y \
logtime=all
STATUS=$?
if [ $STATUS -ne 0 ]; then
echo "Data Pump export failed for ${SCHEMAS}" | \
mailx -s "CRITICAL: Data Pump export failed on $(hostname)" "$MAIL_TO"
else
echo "Data Pump export completed for ${SCHEMAS}" | \
mailx -s "SUCCESS: Data Pump export completed on $(hostname)" "$MAIL_TO"
fi
Parallel Import Script
#!/bin/bash
ORACLE_SID="TESTDB"
IMPORT_DIR_NAME="DATA_PUMP_DIR"
DUMPFILE="prod_schema_exp_20260101_010000_%U.dmp"
LOGFILE="imp_refresh_$(date '+%Y%m%d_%H%M%S').log"
PARALLEL=4
SCHEMAS="APP_OWNER"
REMAP_SCHEMA="APP_OWNER:APP_OWNER_TEST"
TABLE_EXISTS_ACTION="replace"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
impdp system/YourSecurePassword \
schemas=${SCHEMAS} \
directory=${IMPORT_DIR_NAME} \
dumpfile=${DUMPFILE} \
logfile=${LOGFILE} \
parallel=${PARALLEL} \
remap_schema=${REMAP_SCHEMA} \
table_exists_action=${TABLE_EXISTS_ACTION} \
metrics=y \
logtime=all
Data Pump Password Warning
Avoid hardcoding passwords in scripts. Better options include:
Oracle Wallet
Secure external password store
OS authentication where appropriate
Restricted script permissions
Runtime secret injection from a controlled secrets platform
Takeaway
Parallel Data Pump is fast, but speed without repeatability is risky. Script the operation, log the result, and make refreshes boring.
12. Listener Status Monitoring
A healthy database is not enough if clients cannot connect. Listener monitoring should be part of every Oracle automation kit.
Listener Check Script
#!/bin/bash
LISTENER_NAME="LISTENER"
MAIL_TO="dba-team@example.com"
LOG_DIR="/u01/app/oracle/admin/scripts/logs"
LOG_FILE="$LOG_DIR/listener_status_$(date '+%Y%m%d_%H%M').log"
mkdir -p "$LOG_DIR"
lsnrctl status "$LISTENER_NAME" > "$LOG_FILE" 2>&1
if grep -qi "TNS-\|no listener\|failed" "$LOG_FILE"; then
mailx -s "CRITICAL: Listener issue on $(hostname)" "$MAIL_TO" < "$LOG_FILE"
fi
Optional Auto-Restart
Be careful with automatic restarts. They can hide recurring issues. For some environments, alert-only is safer.
lsnrctl start LISTENER
Takeaway
Monitor the access path, not just the database instance. If the listener is down, users experience an outage.
13. Database Instance Health Check
A basic instance health check can verify that the database is open, sessions are available, and core background processes exist.
Instance Status Script
#!/bin/bash
ORACLE_SID="PRODDB"
MAIL_TO="dba-team@example.com"
LOG_FILE="/u01/app/oracle/admin/scripts/logs/db_status_${ORACLE_SID}_$(date '+%Y%m%d_%H%M').log"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
sqlplus -s / as sysdba <<EOF > "$LOG_FILE"
set lines 200 pages 100
select instance_name, status, database_status from v\$instance;
select name, open_mode, database_role from v\$database;
EOF
if ! grep -q "OPEN" "$LOG_FILE"; then
mailx -s "CRITICAL: Database ${ORACLE_SID} may not be open" "$MAIL_TO" < "$LOG_FILE"
fi
Takeaway
Simple checks still matter. A quick database-open validation catches obvious failures before deeper scripts waste time.
14. FRA Usage Monitoring
The Fast Recovery Area is one of the most important areas to monitor. If FRA fills up, archive generation may stop, backups may fail, and the database may halt transactional progress.
FRA Usage Script
#!/bin/bash
ORACLE_SID="PRODDB"
MAIL_TO="dba-team@example.com"
THRESHOLD=85
LOG_FILE="/u01/app/oracle/admin/scripts/logs/fra_usage_${ORACLE_SID}_$(date '+%Y%m%d_%H%M').log"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
sqlplus -s / as sysdba <<EOF > "$LOG_FILE"
set lines 200 pages 100
select
name,
space_limit / 1024 / 1024 / 1024 space_limit_gb,
space_used / 1024 / 1024 / 1024 space_used_gb,
round(space_used / space_limit * 100, 2) used_pct,
space_reclaimable / 1024 / 1024 / 1024 reclaimable_gb
from v\$recovery_file_dest;
EOF
USED=$(sqlplus -s / as sysdba <<EOF
set pages 0 feedback off heading off verify off
select round(space_used / space_limit * 100, 2)
from v\$recovery_file_dest;
EOF
)
USED=$(echo "$USED" | xargs | cut -d'.' -f1)
if [ "$USED" -ge "$THRESHOLD" ]; then
mailx -s "WARNING: FRA usage ${USED}% in ${ORACLE_SID}" "$MAIL_TO" < "$LOG_FILE"
fi
Takeaway
FRA pressure is an early warning. Treat it seriously before it becomes ORA-00257.
15. Archive Generation Rate Tracking
Archive log generation rate tells you how busy the database is and whether backup/archive destinations are sized correctly.
Archive Rate SQL
set lines 200 pages 100
select
trunc(first_time, 'HH24') hour,
count(*) archive_count,
round(sum(blocks * block_size) / 1024 / 1024 / 1024, 2) gb_generated
from v$archived_log
where first_time > sysdate - 1
group by trunc(first_time, 'HH24')
order by hour;
Shell Wrapper
#!/bin/bash
ORACLE_SID="PRODDB"
LOG_FILE="/u01/app/oracle/admin/scripts/logs/archive_rate_${ORACLE_SID}_$(date '+%Y%m%d').log"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
sqlplus -s / as sysdba <<EOF > "$LOG_FILE"
set lines 200 pages 100
select
trunc(first_time, 'HH24') hour,
count(*) archive_count,
round(sum(blocks * block_size) / 1024 / 1024 / 1024, 2) gb_generated
from v\$archived_log
where first_time > sysdate - 1
group by trunc(first_time, 'HH24')
order by hour;
EOF
Takeaway
Archive rate tracking helps with capacity planning. It also exposes unusual workload spikes before they become storage incidents.
16. Scheduler Job Failure Monitoring
Oracle Scheduler failures are easy to miss if nobody checks them. A failed DBMS_SCHEDULER job may mean stale reports, missed loads, broken maintenance, or incomplete integrations.
Scheduler Failure Script
#!/bin/bash
ORACLE_SID="PRODDB"
MAIL_TO="dba-team@example.com"
LOG_FILE="/u01/app/oracle/admin/scripts/logs/scheduler_failures_${ORACLE_SID}_$(date '+%Y%m%d_%H%M').log"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
sqlplus -s / as sysdba <<EOF > "$LOG_FILE"
set lines 220 pages 100
col owner format a20
col job_name format a40
col status format a15
col error# format 999999
col additional_info format a80
select
owner,
job_name,
status,
error#,
actual_start_date,
run_duration,
additional_info
from dba_scheduler_job_run_details
where log_date > systimestamp - interval '1' hour
and status <> 'SUCCEEDED'
order by log_date desc;
EOF
if grep -E "FAILED|STOPPED|BROKEN" "$LOG_FILE" >/dev/null; then
mailx -s "WARNING: Scheduler job failure in ${ORACLE_SID}" "$MAIL_TO" < "$LOG_FILE"
fi
Takeaway
Scheduler jobs are production workflows. Monitor them like production workflows.
17. User Account Expiry Monitoring
Expired database accounts can break applications, reports, ETL jobs, and integrations.
Account Expiry Script
#!/bin/bash
ORACLE_SID="PRODDB"
MAIL_TO="dba-team@example.com"
DAYS_AHEAD=14
LOG_FILE="/u01/app/oracle/admin/scripts/logs/account_expiry_${ORACLE_SID}_$(date '+%Y%m%d').log"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
sqlplus -s / as sysdba <<EOF > "$LOG_FILE"
set lines 200 pages 100
col username format a30
col account_status format a30
col expiry_date format a20
select username, account_status, expiry_date
from dba_users
where expiry_date is not null
and expiry_date < sysdate + $DAYS_AHEAD
order by expiry_date;
EOF
if grep -q "EXPIRED\|LOCKED\|OPEN" "$LOG_FILE"; then
mailx -s "INFO: Oracle accounts expiring within ${DAYS_AHEAD} days" "$MAIL_TO" < "$LOG_FILE"
fi
Takeaway
Account expiry alerts prevent avoidable outages caused by predictable credential lifecycle events.
18. Audit File Cleanup
Audit files can fill filesystems quietly. Clean them using retention rules approved by your security and compliance teams.
Audit Cleanup Script
#!/bin/bash
AUDIT_DIR="/u01/app/oracle/admin/PRODDB/adump"
RETENTION_DAYS=30
LOG_FILE="/u01/app/oracle/admin/scripts/logs/audit_cleanup_$(date '+%Y%m%d').log"
find "$AUDIT_DIR" -name "*.aud" -mtime +$RETENTION_DAYS -print -delete > "$LOG_FILE" 2>&1
Takeaway
Audit cleanup is not just storage maintenance. It must match compliance, security, and retention requirements.
19. Long-Running Session Detection
Long-running sessions are not always bad, but unexpected ones can signal runaway queries, application defects, locking chains, or batch failures.
Long-Running Session Script
#!/bin/bash
ORACLE_SID="PRODDB"
MAIL_TO="dba-team@example.com"
MINUTES=60
LOG_FILE="/u01/app/oracle/admin/scripts/logs/long_running_sessions_${ORACLE_SID}_$(date '+%Y%m%d_%H%M').log"
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv >/dev/null 2>&1
sqlplus -s / as sysdba <<EOF > "$LOG_FILE"
set lines 220 pages 100
col username format a20
col machine format a35
col module format a35
col event format a45
select
sid,
serial#,
username,
machine,
module,
status,
sql_id,
event,
last_call_et / 60 minutes_active
from v\$session
where username is not null
and status = 'ACTIVE'
and last_call_et > $((MINUTES * 60))
order by last_call_et desc;
EOF
if [ $(grep -v '^$' "$LOG_FILE" | wc -l) -gt 3 ]; then
mailx -s "INFO: Long-running sessions in ${ORACLE_SID}" "$MAIL_TO" < "$LOG_FILE"
fi
Takeaway
Do not alert on every long session. Alert on long sessions that are unexpected, blocking, resource-heavy, or outside normal batch windows.
20. Cron Scheduling Examples
Shell scripts become automation only when they run consistently.
Example Crontab
# GoldenGate monitoring every 5 minutes
*/5 * * * * /u01/app/oracle/admin/scripts/gg_monitor.sh >/dev/null 2>&1
# Standby lag every 10 minutes
*/10 * * * * /u01/app/oracle/admin/scripts/standby_lag.sh >/dev/null 2>&1
# Filesystem monitoring every 15 minutes
*/15 * * * * /u01/app/oracle/admin/scripts/filesystem_monitor.sh >/dev/null 2>&1
# Tablespace monitoring every 30 minutes
*/30 * * * * /u01/app/oracle/admin/scripts/tablespace_monitor.sh >/dev/null 2>&1
# RMAN backup nightly at 1 AM
0 1 * * * /u01/app/oracle/admin/scripts/rman_backup.sh >/dev/null 2>&1
# Alert log rotation daily at 2 AM
0 2 * * * /u01/app/oracle/admin/scripts/alert_log_rotate.sh >/dev/null 2>&1
# Audit cleanup weekly on Sunday at 3 AM
0 3 * * 0 /u01/app/oracle/admin/scripts/audit_cleanup.sh >/dev/null 2>&1
Cron Rules
Use full paths inside scripts
Redirect output deliberately
Keep script logs separate from cron logs
Avoid overlapping jobs
Use lock files for long-running operations
Run scripts under the correct OS account
Protect scripts containing credentials
Lock File Example
LOCK_FILE="/tmp/rman_backup.lock"
if [ -f "$LOCK_FILE" ]; then
echo "Backup already running. Exiting."
exit 1
fi
trap 'rm -f "$LOCK_FILE"' EXIT
touch "$LOCK_FILE"
# Run backup logic here
Takeaway
Cron is simple, but production cron needs discipline. Prevent overlap, log everything, and make failures visible.
21. Building a DBA Automation Toolkit
A mature Oracle automation toolkit should be organized clearly.
Suggested Directory Layout
/u01/app/oracle/admin/scripts/
├── bin/
│ ├── gg_monitor.sh
│ ├── standby_lag.sh
│ ├── rman_backup.sh
│ ├── tablespace_monitor.sh
│ ├── asm_monitor.sh
│ ├── blocking_sessions.sh
│ └── filesystem_monitor.sh
├── conf/
│ ├── prod.env
│ ├── test.env
│ └── mail.conf
├── sql/
│ ├── tablespace_usage.sql
│ ├── blocking_sessions.sql
│ └── scheduler_failures.sql
├── logs/
└── README.md
Configuration File Example
# prod.env
export ORACLE_SID=PRODDB
export MAIL_TO=dba-team@example.com
export LOG_DIR=/u01/app/oracle/admin/scripts/logs
export TABLESPACE_THRESHOLD=85
export ASM_THRESHOLD=85
export FRA_THRESHOLD=85
Then source it from scripts:
. /u01/app/oracle/admin/scripts/conf/prod.env
Takeaway
Treat DBA scripts like production code. Use structure, version control, naming standards, and repeatable configuration.
22. Alert Quality: The Difference Between Noise and Signal
Automation can become a new problem if it floods inboxes with low-value alerts.
Good alerts are:
Specific
Actionable
Timely
Deduplicated
Severity-based
Rich with context
Bad alerts say:
Something is wrong.
Good alerts say:
PRODDB tablespace USERS is 92.4 percent used. Max size is 500 GB. Current used is 462 GB. Growth in the last 24 hours is 28 GB. Host is dbserver01. Suggested action: add datafile or investigate growth.
Alert Levels
Use clear severity levels:
INFO: Useful, no immediate action
WARNING: Needs attention soon
CRITICAL: Service risk or active impact
SECURITY: Authentication, privilege, or suspicious activity
Takeaway
The goal is not more alerts. The goal is earlier decisions.
23. Security Practices for Oracle Shell Scripts
Shell scripts can become security risks if handled casually.
Avoid These Mistakes
Hardcoded database passwords
World-readable script files
Logs that expose credentials
Running everything as root
Overly broad sudo permissions
Unvalidated input variables
Blind deletes with wildcards
Sending sensitive data in plain email
Safer Practices
Use OS authentication where appropriate
Use Oracle Wallet for credentials
Restrict file permissions with
chmod 700Keep secrets out of Git
Use service accounts with least privilege
Log enough for troubleshooting, but not secrets
Review scripts before production deployment
Takeaway
Automation should reduce risk, not encode it permanently into a shell file.
24. Testing Oracle Automation Safely
Never drop a new automation script straight into production cron without testing.
Testing Checklist
Run manually in a non-production environment
Confirm Oracle environment variables load correctly
Validate SQL output formatting
Test alert delivery
Test failure paths, not just success paths
Confirm permissions
Confirm logs rotate or age out
Confirm scripts do not overlap
Confirm commands behave correctly when no rows return
Confirm cleanup jobs do not delete required files
Dry-Run Pattern
For cleanup scripts, add a dry-run mode.
DRY_RUN=true
if [ "$DRY_RUN" = true ]; then
find "$AUDIT_DIR" -name "*.aud" -mtime +30 -print
else
find "$AUDIT_DIR" -name "*.aud" -mtime +30 -print -delete
fi
Takeaway
The most dangerous automation is cleanup automation that was never tested against edge cases.
25. More Oracle Automation Ideas
Once the basics are stable, expand your automation coverage.
Additional Checks Worth Automating
Database role validation after switchover
Invalid objects count
Failed login spikes
Password expiry
Object growth trends
Segment growth by day
Top SQL by elapsed time
Top wait events
AWR snapshot health
Flashback status
Restore point age
Datafile autoextend risk
Temp usage spikes
Undo pressure
Sequence exhaustion
Listener service registration
Broken database links
Materialized view refresh failures
GoldenGate discard file growth
GoldenGate trail file age
RMAN backup age
RMAN restore validation
Archive destination errors
ASM rebalance status
RAC node availability
Clusterware resource status
Takeaway
Start with outages you have already experienced. The best automation backlog is your incident history.
Production-Ready Improvements
The examples in this article are intentionally readable. For production use, improve them with:
Central config files
Secure credential handling
Lock files
JSON or structured logs
Integration with ticketing systems
Integration with Slack, Teams, PagerDuty, or ServiceNow
Retry logic where safe
Alert suppression windows
Maintenance mode support
Unit-style shell checks
Git version control
Peer review before deployment
A script that works once is a utility. A script that works safely every day is operational engineering.
Final Thoughts
Oracle automation does not need to start big.
Start with one painful manual check. Script it. Log it. Alert on it. Test it. Put it in cron. Then move to the next one.
Over time, these small scripts become a defensive layer around the database. They catch lag before failover. They catch storage pressure before archive hangs. They catch blocking sessions before customers complain. They catch failed backups before recovery day.
The best DBA automation is not flashy. It is quiet, boring, and dependable.
And that is exactly the point.
Summary
Oracle shell scripting remains one of the most practical tools for DBA automation. With a small set of focused scripts, DBAs can monitor GoldenGate, Data Guard, RMAN backups, archive cleanup, tablespaces, ASM, alert logs, invalid logins, blocking sessions, filesystem usage, Data Pump operations, and more.
The highest-value automation does three things well:
It detects problems early.
It sends useful, actionable alerts.
It reduces repetitive manual checks.
Strong automation is not just about writing scripts. It is about writing safe, tested, consistent operational controls.
Key Takeaways
Monitor GoldenGate for lag, not just process status.
Measure standby apply and transport lag before failover day.
Treat RMAN logs as production evidence, not background noise.
Alert on blocking sessions with enough detail to act quickly.
Track ASM, FRA, tablespace, filesystem, and inode pressure.
Use ADRCI to catch ORA errors early.
Audit invalid logins for both operational and security signals.
Rotate alert logs before they become diagnostic clutter.
Automate Data Pump operations for repeatable exports and imports.
Treat shell scripts like production code: tested, secured, logged, reviewed, and versioned.
Finally
The strongest Oracle environments are not the ones where DBAs work the hardest. They are the ones where the right checks run before anyone has to ask.
A final word from me…
I hope you found this information insightful and helpful.
If you appreciate my articles, consider buying me a coffee, a simple yet impactful gesture that enables me to continue creating and sharing knowledge in the tech community.
Thank you for considering to supporting me :) ☕️https://buymeacoffee.com/waynetech ☕️
#OracleDBA #OracleDatabase #ShellScripting #DatabaseAutomation #OracleAutomation #RMAN #GoldenGate #OracleGoldenGate #DataGuard #StandbyDatabase #ASM #TablespaceMonitoring #OracleMonitoring #DBAJobs #LinuxAdmin #DevOpsForDBA #DatabaseReliability #SRE #ADRCI #OracleScripts #DataPump #DatabaseBackups #AlertLog #OracleSecurity #InvalidLoginAudit