()

How to perform Oracle Database health-check

In this blog, we’ll be learning about how we can perform Oracle Database health-check. Many times we have seen that we got the request from Application user that their application is slow and they’re asking us to perform health checks on the specified database. We generally perform health checks just to see any abnormal condition where we are observing from the DB end. According to that, we have to take action immediately. These health checks come while we’re doing performance tuning.

For performing detailed analysis, we can generate AWR, ASH report. on basis of that, we can check the Database health for a specific timezone, if we want to know the status of the database for any specific timezone. The below-mentioned query can help you to fetch the AWR report for getting the desired result we have to set the report type as html. Rest as per requirements we can mention.

The below-mentioned query can help you to fetch the AWR report:-

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


Mainly for doing basic health checks, we’ll be performing the below-mentioned steps which applicable for both RAC and standalone database. 

  • DB uptime
  • CPU utilization, memory utilization, etc.
  • Listener status of the database
  • FRA utilization
  • Tablespaces utilization
  • Database status
  • Blocking sessions.
  • Longrunning session.
  1. Check the up-time of the server:-
[oracle@DBsGuru-lab1 ~]$ uptime
 13:36:12 up 9 min,  2 users,  load average: 0.10, 0.32, 0.26


2. Validate overall CPU, memory, etc. utilization using commands sar & top:

[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
    


3. Check the listener status:-

[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


4. FRA/DISKGROUP utilization if it’s ASM storage:-

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**$$$$$$$


5. Check the tablespace utilizations are under threshold or not:-

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**$$$$$$$


Click here for command to check tablespace size.


6. Check the database status:-

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**$$$$$$$


7. Check blocking session if there is any:-

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**$$$$$$$

8. Check Long running session if there is any:-

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;

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. 
Click here to understand more about our pursuit.

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Suryapriya Majumdar

Hello and welcome to DBsGuru,I'm Suryapriya Majumdar and currently working as an Oracle DBA in an MNC for the past 3 years. These 3 years gave me gather knowledge in Oracle Database for various OS like Linux, HPUX, AIX and also in windows servers.I've good hands-on experience in the mentioned topics- table, schema, and Database refresh using the data-pump utility, backup, and recovery using RMAN, tablespace management, Database installation, Database Patching and upgrade, Performance tuning, database cloning, GG replication and lag issues, storage management in both ASM and file system.Every day there is a new learning path waiting for all of us to enhance our knowledge. So kudos to learning.Thanks for the visits!Share Learn Grow!