Scribd
Upload
6 views2 pages

Oracle Database Basic Health Check

The document provides a comprehensive guide for performing a basic health check on an Oracle Database. It includes SQL queries to check database uptime, instance status, listener status, tablespace usage, invalid objects, top wait events, CPU consuming sessions, backup status, and archive log usage. Additionally, it mentions checking the database alert log and trace files for recent errors or warnings.

Uploaded by

kiran
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views2 pages

Oracle Database Basic Health Check

The document provides a comprehensive guide for performing a basic health check on an Oracle Database. It includes SQL queries to check database uptime, instance status, listener status, tablespace usage, invalid objects, top wait events, CPU consuming sessions, backup status, and archive log usage. Additionally, it mentions checking the database alert log and trace files for recent errors or warnings.

Uploaded by

kiran
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Basic Oracle Database Health Check

1. Database Uptime
SELECT startup_time FROM v$instance;

2. Instance Status
SELECT instance_name, status, database_status FROM v$instance;

3. Listener Status

Check from OS:

lsnrctl status

4. Database Alert Log & Trace Files

Check for recent errors or warnings:

• Location: $ORACLE_BASE/diag/rdbms/<dbname>/<SID>/trace/alert_<SID>.log

You can also use ADRCI:

adrci> show alert -tail 50

5. Tablespace Usage
SELECT tablespace_name, ROUND(used_space_mb,2) AS used_mb,
ROUND(free_space_mb,2) AS free_mb
FROM (
SELECT df.tablespace_name,
SUM(df.bytes)/1024/1024 AS used_space_mb,
(SUM(df.bytes)/1024/1024 - SUM(f.free_space))/1024/1024 AS
free_space_mb
FROM dba_data_files df
LEFT JOIN (
SELECT tablespace_name, SUM(bytes) AS free_space
FROM dba_free_space
GROUP BY tablespace_name
) f ON df.tablespace_name = f.tablespace_name
GROUP BY df.tablespace_name
);

6. Invalid Objects
SELECT owner, object_type, COUNT(*) AS count
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type;
7. Top Wait Events
SELECT event, total_waits, time_waited, average_wait
FROM v$system_event
WHERE event NOT LIKE 'SQL*Net%'
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;

8. Top CPU Consuming Sessions


SELECT s.sid, s.serial#, p.spid, s.username, s.osuser, s.status,
ROUND(v.value/100, 2) AS cpu_seconds
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
JOIN v$sesstat v ON v.sid = s.sid
JOIN v$statname n ON v.statistic# = n.statistic#
WHERE n.name = 'CPU used by this session'
ORDER BY cpu_seconds DESC
FETCH FIRST 5 ROWS ONLY;

9. Backup Status (Last RMAN Backup)


SELECT start_time, end_time, status, input_type
FROM v$rman_backup_job_details
ORDER BY end_time DESC
FETCH FIRST 5 ROWS ONLY;

10. Archive Log Usage


SELECT name, sequence#, applied, archived
FROM v$archived_log
WHERE completion_time > SYSDATE - 1
ORDER BY sequence# DESC;

You might also like