As an Oracle DBA, staying on top of both your database and the underlying Linux system is key to ensuring smooth and efficient performance. Below are a few of the most useful scripts I run daily to check on the health of the Oracle Database and the Linux system it’s running on!
🔹 Oracle Database Health Checks:
Check Database Status:
SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;
(Ensure your Oracle instance is running properly)
Check Tablespace Usage:
SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS MB_USED FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;
(Check for any tablespaces that are running out of space)
Monitor Active Sessions:
SELECT SID, SERIAL#, USERNAME, STATUS FROM V$SESSION WHERE USERNAME IS NOT NULL;
(Monitor and troubleshoot any active sessions)
Identify Long-Running Queries:
SELECT SID, SERIAL#, SQL_ID, ELAPSED_TIME/60 AS MINUTES, SQL_TEXT FROM V$SESSION LONG WHERE ELAPSED_TIME > 600;
(Identify queries running for over 10 minutes)
Check Redo Log Space Usage:
SELECT GROUP#, MEMBERS, BYTES/1024/1024 AS REDO_SIZE_MB FROM V$LOG;
(Monitor redo logs to ensure no bottlenecks)
Check Backup Status:
SELECT BACKUP_TYPE, BACKUP_TIME, STATUS FROM V$BACKUP_JOB_DETAILS;
(Ensure your backups are running smoothly)
🔹 Linux System Monitoring for Oracle DBAs:
Check CPU Usage:
top -n 1 | grep “Cpu(s)”
(Monitor overall CPU performance)
Monitor Disk Usage:
df -h
(Check disk space and ensure Oracle data files aren’t running out of space)
Monitor Memory Usage:
free -m
(Check memory usage on the system)
Check Processes:
ps aux | grep ora_
(Monitor Oracle processes and ensure there are no stuck processes)
Check Oracle Logs:
tail -f $ORACLE_BASE/diag/rdbms/*/*/trace/alert.log
(Monitor Oracle alert logs for errors or warnings)
💡 Pro Tip: Automating these scripts with cron jobs or Oracle Enterprise Manager can save you time and provide real-time alerts for any issues. Feel free to adapt these to your specific environment!