Health Check SQL Queries

set lines 999 pages 999;

set echo off;

prompt #########################Tablespace Monitoring:#########################

select a.tablespace_name, round(assigned_space) "ALLOCATED (MB)", round((nvl(free_space,0) + INCRM)) "FREE (MB)", round((assigned_space - nvl(free_space,0) - INCRM)) "USED (MB)",

round( ((assigned_space - nvl(free_space,0) - INCRM) / assigned_space)*100) "% Full"

from(select  tablespace_name, count(*) num1, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) assigned_space, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) - sum(bytes)/(1024*1024) INCRM from dba_data_files group by tablespace_name) A,

(select  tablespace_name, count(*) num2, (sum(bytes)/(1024*1024)) free_space

from dba_free_space a group by tablespace_name) B where b.tablespace_name(+)=a.tablespace_name

union all 

select a.tablespace_name, round(assigned_space) "ALLOCATED (MB)", round((nvl(free_space,0) + INCRM)) "FREE (MB)", round((assigned_space - nvl(free_space,0) - INCRM)) "USED (MB)",

round(((assigned_space - nvl(free_space,0) - INCRM ) / assigned_space)*100) "% Full"

from(select  tablespace_name, count(*) num1, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) assigned_space,

Sum(Decode(Sign(Maxbytes - Bytes), -1, Bytes, Maxbytes))/(1024*1024) - Sum(Bytes)/(1024*1024) Incrm From Dba_Temp_Files Group By Tablespace_Name) A,

(Select  Tablespace_Name, Count(*) Num2, (Sum(free_space)/(1024*1024)) Free_Space

FROM DBA_TEMP_FREE_SPACE GROUP BY TABLESPACE_NAME) B WHERE B.TABLESPACE_NAME(+)=A.TABLESPACE_NAME 

Order By "% Full" Desc;

prompt #########################Process Utilization:#########################

set echo on;

SELECT INST_ID,upper(resource_name) as resource_name,current_utilization,max_utilization,initial_allocation FROM gv$resource_limit WHERE resource_name in ('processes', 'sessions');

set echo off;

prompt #########################Check archive log usage FRA:#########################

col name for a25;

set echo on;

select name,(SPACE_LIMIT/1024/1024/1024), (SPACE_USED/1024/1024/1024), (SPACE_RECLAIMABLE/1024/1024/1024) from v$recovery_file_dest;

set echo off;

prompt #########################Instance Startup :#########################

set echo on;

SELECT inst_id,INSTANCE_NAME,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.gv_$instance;

set echo off;

prompt #########################ASM Disk usage:#########################

set echo on;

SELECT NAME,TOTAL_MB,FREE_MB,ROUND((1- (FREE_MB / TOTAL_MB))*100, 2)  "percentage used" FROM v$asm_diskgroup order by 4 desc;

set echo off;

prompt #########################Instance_name:#########################

set echo on;

select instance_name, host_name from gv$instance order by 1;

set echo off;

prompt #########################Open_mode:##########################

set echo on;

select name, open_mode from gv$database  order by 1;

set echo off;

prompt #########################Information About Each Container in a CDB:##########################

COLUMN NAME FORMAT A8

set echo on;

SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

set echo off;

prompt #########################Name and Open Mode of Each PDB:#########################

COLUMN NAME FORMAT A15

COLUMN RESTRICTED FORMAT A10

COLUMN OPEN_TIME FORMAT A40

set echo on;

SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

set echo off;

prompt #########################Locked Object:#########################

set echo on;

select object_name,os_user_name,session_id from all_objects A ,v$locked_object L where A.object_id=L.object_id group by object_name,os_user_name,session_id order by 1,3;

set echo off;

prompt #########################Block corruption:#########################

set echo on;

select count(*) from v$database_block_corruption;

set echo off;

prompt #########################Table_Stats:#########################

set echo on;

select distinct OWNER,TRUNC(LAST_ANALYZED) from DBA_TABLES where owner like '%AA%' group by owner,last_analyzed order by 1;

exit;

Copy all above queries in sql file and call from shell script for each schema in the database...

#!/bin/bash

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_2

fdate=`date '+%d-%b-%Y-%H_%M'`

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dba2025#@MERSIT2 @/orabackup/scripts/DTS_UAT_DB_MERCH_monitor.sql >/orabackup/scripts/logs/DTS_UAT_MERSIT_monitor-${fdate}-execution.log

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dba2025#@MERUAT2 @/orabackup/scripts/DTS_UAT_DB_MERCH_monitor.sql >/orabackup/scripts/logs/DTS_UAT_MERUAT_monitor-${fdate}-execution.log

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dba2025#@POSSIT2 @/orabackup/scripts/DTS_UAT_DB_POS_monitor.sql >/orabackup/scripts/logs/DTS_UAT_POSSIT_monitor-${fdate}-execution.log

/u01/app/oracle/product/12.1.0/dbhome_2/bin/sqlplus dba/dba2025#@POSUAT2 @/orabackup/scripts/DTS_UAT_DB_POS_monitor.sql >/orabackup/scripts/logs/DTS_UAT_POSUAT_monitor-${fdate}-execution.log

exit;