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.
- 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.
