How to Perform a Health Check of an Oracle Database

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

[oracle@DBsGuru-lab1 ~]$ uptime
 13:36:12 up 9 min,  2 users,  load average: 0.10, 0.32, 0.26

[oracle@DBsGuru-lab1 ~]$ sar 5 5
Linux 2.6.39-200.24.1.el6uek.x86_64 (ol6-112-lab1.localdomain)  03/01/2021      _x86_64_        (2 CPU)

01:51:02 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
01:51:07 PM     all      0.20      0.00      0.20      0.00      0.00     99.59
01:51:12 PM     all      0.21      0.00      0.10      0.00      0.00     99.69
01:51:17 PM     all     23.54      0.00      1.74      0.10      0.00     74.62
01:51:22 PM     all     30.66      0.00      2.16      0.00      0.00     67.18
01:51:27 PM     all      0.21      0.00      0.21      0.00      0.00     99.57
Average:        all     11.06      0.00      0.89      0.02      0.00     88.03
[oracle@DBsGuru-lab1 ~]$ top 
top - 13:53:48 up 27 min,  2 users,  load average: 0.01, 0.06, 0.13
Tasks: 176 total,   1 running, 175 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.5%us,  0.3%sy,  0.0%ni, 99.0%id,  0.0%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:   4055360k total,  1949340k used,  2106020k free,    42196k buffers
Swap:  4194300k total,        0k used,  4194300k free,  1445912k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    9 root      20   0     0    0    0 S  0.3  0.0   0:01.67 kworker/1:0
 1315 haldaemo  20   0 26212 4888 4044 S  0.3  0.1   0:00.33 hald
 2271 oracle    20   0 97872 2432 1468 S  0.3  0.1   0:00.19 sshd
 2327 oracle    20   0 1141m  19m  17m S  0.3  0.5   0:02.89 ora_psp0_labdb0
 3976 oracle    20   0 15080 1196  848 R  0.3  0.0   0:00.14 top
    

[oracle@DBsGuru-lab1 ~]$  ps -eaf | grep tns | egrep -v grep
oracle    2006     1  0 13:27 ?        00:00:00 /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr LISTENER12C -inherit
[oracle@DBsGuru-lab1 ~]$ lsnrctl status LISTENER12C

SQL> SET LINES 200
COLUMN GROUP_NAME             FORMAT A25           HEAD 'DISK GROUP|NAME'
COLUMN SECTOR_SIZE            FORMAT 99,999        HEAD 'SECTOR|SIZE'
COLUMN BLOCK_SIZE             FORMAT 99,999        HEAD 'BLOCK|SIZE'
COLUMN ALLOCATION_UNIT_SIZE   FORMAT 999,999,999   HEAD 'ALLOCATION|UNIT SIZE'
COLUMN STATE                  FORMAT A11           HEAD 'STATE'
COLUMN TYPE                   FORMAT A6            HEAD 'TYPE'
COLUMN TOTAL_MB               FORMAT 999,999,999   HEAD 'TOTAL SIZE (MB)'
COLUMN USED_MB                FORMAT 999,999,999   HEAD 'USED SIZE (MB)'
COLUMN PCT_USED               FORMAT 999.99        HEAD 'PCT. USED'

BREAK ON REPORT ON DISK_GROUP_NAME SKIP 1

COMPUTE SUM LABEL "GRAND TOTAL: " OF TOTAL_MB USED_MB ON REPORT

SELECT
    NAME                                     GROUP_NAME
  , SECTOR_SIZE                              SECTOR_SIZE
  , BLOCK_SIZE                               BLOCK_SIZE
  , ALLOCATION_UNIT_SIZE                     ALLOCATION_UNIT_SIZE
  , STATE                                    STATE
  , TYPE                                     TYPE
  , TOTAL_MB                                 TOTAL_MB
  , (TOTAL_MB - FREE_MB)                     USED_MB
  , ROUND((1- (FREE_MB / TOTAL_MB))*100, 2)  PCT_USED
FROM  V$ASM_DISKGROUP
WHERE  TOTAL_MB != 0
ORDER BY NAME
/ 
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

SQL> SET LINESIZE 200 PAGESIZE 1000
COLUMN TOTAL_BYTES FORMAT 999999999999
COLUMN FREE_BYTES FORMAT 999999999999
SELECT A.TABLESPACE_NAME AS "TABLESPACE",
100-ROUND(DECODE(A.AUTEXT,'YES',(A.MBYTES-A.ABYTES+B.FBYTES)/A.MBYTES*100,
'NO',B.FBYTES/A.ABYTES*100),0) AS "PCT_USED" FROM
(SELECT TABLESPACE_NAME , COUNT(DISTINCT FILE_ID) NUM_FILES,
MAX(AUTOEXTENSIBLE) AUTEXT ,SUM(DECODE(SIGN(MAXBYTES-BYTES), -1, BYTES, MAXBYTES)) MBYTES, SUM(BYTES) ABYTES
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) FBYTES
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME(+)
AND 100-ROUND(DECODE(A.AUTEXT,'YES',(A.MBYTES-A.ABYTES+B.FBYTES)/A.MBYTES*100,
'NO',B.FBYTES/A.ABYTES*100),0) >= 1
ORDER BY "PCT_USED" DESC;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

SQL> SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

For RAC databases

SQL> SELECT INST_ID, NAME, OPEN_MODE, DATABASE_ROLE FROM GV$DATABASE;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

SQL> SELECT BLOCKING_SESSION, SID, SERIAL#, WAIT_CLASS, SECONDS_IN_WAIT FROM V$SESSION
WHERE BLOCKING_SESSION IS NOT NULL ORDER BY BLOCKING_SESSION;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

For RAC databases

SQL> SELECT INST_ID, BLOCKING_SESSION, SID, SERIAL#, WAIT_CLASS, SECONDS_IN_WAIT FROM GV$SESSION
WHERE BLOCKING_SESSION IS NOT NULL ORDER BY BLOCKING_SESSION;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

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 databases

SELECT INST_ID,SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM GV$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;