The basic health checks Performing the below-mentioned queries and commands which applicable for RAC,standalone database and windows.
Check free space on disk and DB uptime
$ df -k
DB uptime
control whether database Listener is running
$ lsnrctl status
control whether database is running
$ srvctl status database -d racdb
$ sqlplus / as sysdba
SQL> select name,open_mode from v$database;
pdbs
SQL> select name,open_mode from v$pdbs;
For rac environment
SQL> select inst_id,name,open_mode from gv$database;
Check the alert log for an error
For linux
$ cd /u01/app/oracle/diag/rdbms/racdb/racdb1/trace
$ vim alert_racdb1.log
–Then look for error by ‘/ORA-‘
$ grep -c ‘ORA-‘ alert_racdb1.log
For windows
C:\APP\ORA_C\DATA\diag\rdbms\orcl\orcl\trace\orcl_ora_15720.trc
You can see location of trace file
SQL> select * from v$diag_info;
Check free Space in the tablespace
SQL> col name format a25
col owner format a15
col “Used (GB)” format a15
col “Free (GB)” format a15
col “(Used) %” format a15
col “Size (M)” format a15
SELECT d.status “Status”, d.tablespace_name “Name”,
TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),’99,999,990.90′) “Size (GB)”,
TO_CHAR(NVL(a.bytes – NVL(f.bytes, 0), 0)/1024/1024 /1024,’99999999.99′) “Used (GB)”,
TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024, 0),’99,999,990.90′) “Free (GB)”,
TO_CHAR(NVL((a.bytes – NVL(f.bytes, 0)) / a.bytes * 100, 0), ‘990.00’) “(Used) %”
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like ‘LOCAL’ AND d.contents like ‘TEMPORARY’)
UNION ALL
SELECT d.status
“Status”, d.tablespace_name “Name”,
TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024, 0),’99,999,990.90′) “Size (GB)”,
TO_CHAR(NVL(t.bytes,0)/1024/1024 /1024,’99999999.99′) “Used (GB)”,
TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024 / 1024 /1024, 0),’99,999,990.90′) “Free (GB)”,
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), ‘990.00’) “(Used) %”
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND
d.extent_management like ‘LOCAL’ AND d.contents like ‘TEMPORARY’;
Check any session blocking the other session
SQL> select l.session_id,o.object_name,l.oracle_username,l.os_user_name
from v$locked_object l, dba_objects o
where o.object_id = l.object_id;
Check Long running sessions
SELECT SID, SERIAL#,OPNAME,CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) “%_COMPLETE” FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE ‘%aggregate%’ AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
For rac environment
SQL> column object_name format a15
column oracle_username format a10
column os_user_name format a10SQL> select l.session_id,o.object_name,l.oracle_username,l.os_user_name
from gv$locked_object l, dba_objects o
where o.object_id = l.object_id;
Check is there any dbms jobs running & check the status of the same
SQL> select * from dba_jobs;
SQL> select * from dba_jobs_running;
Check whether backups are completed
$ rman target /
RMAN> LIST BACKUP SUMMARY;
Set linesize 400;
col STATUS format a9
col hrs format 999.99
Col start_time for a30
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,
elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;