Database health checks in Oracle

Performing Database health checks, when there is an issue reported by Application users.

1. Check the Database details
2. Monitor the consumption of resources
3. Check the Alert Log
4. Check Listener log
5. Check Filesystem space Usage
6. Generate AWR Report
7. Generate ADDM Report
8. Finding Locks,Blocker Session and Waiting sessions in a oracle database
9. Check for alerts in OEM

1. Check the Database details :-
=============================
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

For RAC:
-------
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;


2. Monitor the consumption of resources :-
=======================================
select * from v$resource_limit where resource_name in ('processes','sessions');

The v$session views shows current sessions (which change rapidly),
while the v$resource_limit shows the current and maximum global resource utilization for some system resources.


3. Check the Alert Log :-
======================
$locate alert_<ORACLE_SID>

--- OR ---

UNIX/Linux command to locate the alert log file
-----------------------------------------------

$ find / -name 'alert_*.log' 2> /dev/null

vi <alert_log_location_of_the_above_output>
shift+g
?ORA-   ---> press enter key
press 'n' to check backwards/up side and 'N' for forward/down side search.

:q! --and press enter, for exiting vi editor


--- OR ---

11G
===
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col NAME for a15
col VALUE for a60
select name, value from v$diag_info where name = 'Diag Trace';

On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/diag/$ORACLE_SID/trace directory

Before 11G
==========
$ sqlplus "/as sysdba"
set pages 9999 lines 300
show parameter BACKGROUND_DUMP_DEST;

On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/admin/$ORACLE_SID/bdump directory


4. Check Listener log :-
=====================
$locate listener.log

--- OR ---

UNIX/Linux command to locate the listener log file
--------------------------------------------------
$ find / -name 'listener.log' 2> /dev/null
vi <listener.log>
shift+g
?TNS-    ---> press enter key
press 'n' to check backwords and 'N' for forword search.

AND

shift+g
?error   ---> press enter key
press 'n' to check backwords and 'N' for forword search.

:q! --and press enter, for exiting vi editor

--- OR ---

$lsnrctl status

from the output you can get the listener log location (see the value for "Listener Log File" in the output).


5. Check Filesystem space Usage :-
===============================
df -h (Linux / UNIX)

df -g (AIX)

6. Generate AWR Report :-
======================
Generate AWR report for current and before to compare

SQL> @?/rdbms/admin/awrrpt.sql        (For RAC,  @?/rdbms/admin/awrrpti.sql - for each instance)

If Required,
SQL> @?/rdbms/admin/awrddrpt.sql ---->   Produces Workload Repository Compare Periods Report


7. Generate ADDM Report :-
=======================
Generate ADDM report for current and before to compare.

ADDM report provides Findings and Recommendations to fix the issue.

SQL> @?/rdbms/admin/addmrpt.sql     (For RAC,  @?/rdbms/admin/addmrpti.sql - for each instance)


8. Finding Locks,Blocker Session and Waiting sessions in a oracle database :-
========================================================================
Select * from v$lock;

Select * from gv_$lock;  (For RAC)

A fast way to check blocking/waiting situations
-----------------------------------------------
SELECT * FROM v$lock WHERE block > 0 OR request > 0;

set pages 50000 lines 32767
select object_name,s.inst_id,s.sid,s.serial#,p.spid,s.osuser,s.program,s.server,s.machine,s.status from gv$locked_object l,gv$session s,gv$process p,dba_objects o where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

set pages 50000 lines 32767
col OBJECT_NAME for a40
col USERNAME for a10
col LOCKED_MODE for a15
col OBJECT_OWNER for a15
col OS_USER_NAME for a12
SELECT b.inst_id,b.session_id AS sid,NVL(b.oracle_username, '(oracle)') AS username,a.owner AS object_owner,a.object_name,
Decode(b.locked_mode, 0, 'None',1, 'Null (NULL)',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share (S)',5, 'S/Row-X (SSX)',6, 'Exclusive (X)',
b.locked_mode) locked_mode,b.os_user_name FROM dba_objects a, gv$locked_object b WHERE a.object_id = b.object_id ORDER BY 1, 2, 3, 4;

Blocker Session and Waiting sessions
====================================
column Username format A15 column Sid format 9990 heading SID
column Type format A4 column Lmode format 990 heading 'HELD'
column Request format 990 heading 'REQ' column Id1 format 9999990
column Id2 format 9999990 break on Id1 skip 1 dup
SELECT SN.Username, M.Sid, M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(Lmode,'990'))) Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1, M.Id2
FROM V$SESSION SN, V$LOCK M
WHERE (SN.Sid = M.Sid and M.Request ! = 0)
or (SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and (id1, id2)
in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1
= M.Id1 and S.Id2 = M.Id2) ) order by Id1, Id2, M.Request;

USERNAME SID TY LMODE REQUEST ID1 ID2
---------------- ------- -- ------------- ------------- ---------- --------
ORAPLAYERS 10 TX Exclusive None 123456 200
ORAPLAYERS 100 TX None Exclusive 123456 200

Session 10 is blocking(LMODE=Exclusive)

Session 100 is waiting(REQUEST=Exclusive)

The meaning of ID1 and ID2 depends on the lock TYPE.

• We can see situations where a session is both a Blocker and a Waiter.

• If there are only two sessions and both are Blockers and Waiters then we got a deadlock situation (which Oracle will solve automatically).


To find waiters:
---------------
set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10

select * from dba_waiters;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- --------- --------- -------------- -------- --------
                           
Blocking details:
----------------
set pages 50000 lines 32767
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' SID=' || s2.sid || ' ) ' as blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;

set pages 50000 lines 32767
col BLOCKER for a20
col BLOCKEE for a20
select (select username from v$session where sid = a.sid ) blocker,a.sid, 'is blocking ',(select username from v$session where sid =b.sid) blockee,b.sid from v$lock a, v$lock b where a.block =1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;

BLOCKER SID       'ISBLOCKING' BLOCKEE SID
------- ---------- ----------  ------- --------


set pages 50000 lines 32767
select blocking_session, sid, serial#, wait_class,seconds_in_wait, username, osuser, program, logon_time from v$session where blocking_session is not NULL order by 1;

9. Check for alerts in OEM :-
============================
Login to Oracle Enterprise Manager with valid username and password
click on "Alerts" tab
then select the below tabs one by one to see the alerts generated
Targets Down/Critical/Warning/Errors/

ORACLE Database Details

connect to Putty->enter the hostname->select load

Login : <userid>
password: <password>

ps -ef | grep pmon
ps -ef | grep tns
ps -ef | grep d.bin

Set the environment for the database:
------------------------------------
uname
cat /etc/oratab  OR cat /var/opt/oracle/oratab
export ORACLE_SID=<DBNAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$ORACLE_HOME/bin:$PATH

Database Details:
----------------
sqlplus "/as sysdba"

set pages 50000 lines 32767

col OPEN_MODE for a10
col HOST_NAME for a10
select NAME DB_NAME, INSTANCE_NAME, HOST_NAME, OPEN_MODE, version DB_VERSION,DATABASE_STATUS,DATABASE_ROLE, PROTECTION_LEVEL, CONTROLFILE_TYPE, LOGINS, to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;


 

RMAN Troubleshooting

RMAN Troubleshooting Queries at Database Level

SET PAGESIZE 20000
SET LINESIZE 1000
SET TRIMSPOOL ON
SET PAUSE OFF
SET SERVEROUTPUT ON
SET FEEDBACK ON
SET ECHO ON
SET NUMFORMAT 999999999999999
COL TABLESPACE_NAME FORMAT A50
COL FILE_NAME FORMAT A50
COL NAME FORMAT A50
COL MEMBER FORMAT A50
col DFILE_CHKP_CHANGE format a40
col DFILE_HED_CHKP_CHANGE format a40

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

ARCHIVE LOG LIST;

SELECT * FROM gv$instance;
SELECT * FROM v$recover_file;
SELECT * FROM v$recovery_log;
select distinct status from v$backup;
select distinct(status) from v$datafile;
select distinct (to_char(checkpoint_change#)) from v$datafile;
select distinct (to_char(checkpoint_change#)) from v$datafile_header;

select * from v$backup;

SELECT dbid,
name,
TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') created,
open_mode,
log_mode,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
controlfile_type,
TO_CHAR(controlfile_change#, '999999999999999') as controlfile_change#,
TO_CHAR(controlfile_time, 'DD-MON-YYYY HH24:MI:SS') controlfile_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') resetlogs_time
FROM v$database;

SELECT f.name, b.status, b.change#, b.time
FROM v$backup b,
v$datafile f
WHERE b.file# = f.file#
AND b.status = 'ACTIVE';

SELECT name,
file#,
status,
enabled,
creation_change#,
TO_CHAR(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(offline_change#, '999999999999999') as offline_change#,
TO_CHAR(online_change#, '999999999999999') as online_change#,
TO_CHAR(online_time, 'DD-MON-YYYY HH24:MI:SS') as online_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile
where status <> 'ONLINE'
OR checkpoint_change# <> (SELECT checkpoint_change# FROM v$database);

SELECT name,
file#,
status,
error,
creation_change#,
TO_CHAR(creation_time, 'DD-MON-YYYY HH24:MI:SS') as creation_time,
TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
TO_CHAR(resetlogs_time, 'DD-MON-YYYY HH24:MI:SS') as resetlogs_time,
TO_CHAR(bytes, '9,999,999,999,990') as bytes
FROM v$datafile_header
WHERE status <> 'ONLINE'
OR checkpoint_change# <> (SELECT checkpoint_change# FROM v$database);

SELECT status,
checkpoint_change#,
TO_CHAR(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,
count(*)
FROM v$datafile_header
GROUP BY status, checkpoint_change#, checkpoint_time
ORDER BY status, checkpoint_change#, checkpoint_time;

SELECT dd.FILE#,
dd.NAME,
dd.STATUS,
to_char(dd.checkpoint_change#,'999999999999999') dfile_chkp_change,
to_char(dh.checkpoint_change#,'999999999999999') dfile_hed_chkp_change,
dh.recover,
dh.fuzzy
FROM v$datafile dd,
v$datafile_header dh
WHERE dd.FILE#=dh.FILE#;

SELECT name file_name,
recover,
fuzzy,
checkpoint_change#
FROM v$datafile_header
ORDER BY checkpoint_change#;

SELECT hxfil file_num,
hxfnm file_name,
fhtyp type,
hxerr validity,
fhscn scn,
fhtnm tablespace_name,
fhsta status ,
fhrba_seq sequence
FROM x$kcvfh;

SELECT group#,
thread#,
sequence#,
members,
archived,
status,
TO_CHAR(first_change#, '999999999999999') as first_change#
FROM v$log;

SELECT group#,member
FROM v$logfile;

SELECT a.recid,
a.thread#,
a.sequence#,
a.name,
a.archived,
a.deleted,
TO_DATE(a.completion_time, 'DD-MON-YYYY HH24:MI:SS') as completed
FROM v$archived_log a, v$log l
WHERE a.thread# = l.thread#
AND a.sequence# = l.sequence#;

Database Health Report

Database Health Report (HTML)


vi Database_Health_Report.sql
:wq

sqlplus "/ as sysdba"

@Database_Health_Report.sql

cat Database_Health_Report.sql


SET MARKUP HTML ON

set feedback off
set serverout on
set wrap off
set pages 300
set lines 150
col file_name for a50
col name for a50
col member for a50
col file_id for a5
col "Percent Used" for a20
col segment_name for a30
col tablespace_name for a30
col STATUS for a16
col owner for a20
col table_name for a35
col index_name for a35
col username format a25
col default_tablespace format a25
col temporary_tablespace format a25

SPOOL Database_Health_Report.html

PROMPT ================================================================
PROMPT DATABASE HEALTH CHECK REPORT
PROMPT ================================================================

PROMPT
PROMPT
PROMPT DATABASE STATUS
PROMPT =================
select INSTANCE_NAME,STATUS,DATABASE_STATUS,ACTIVE_STATE,STARTUP_TIME from v$instance;

PROMPT
PROMPT
PROMPT DATABASE UPTIME
PROMPT =================

select
   'Hostname : ' || host_name
   ,'Instance Name : ' || instance_name
   ,'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime
   ,'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' ||
   trunc( 24*((sysdate-startup_time) -
   trunc(sysdate-startup_time))) || ' hour(s) ' ||
   mod(trunc(1440*((sysdate-startup_time) -
   trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||
   mod(trunc(86400*((sysdate-startup_time) -
   trunc(sysdate-startup_time))), 60) ||' seconds' uptime
from
   sys.v_$instance;


PROMPT
PROMPT
PROMPT TOTAL DATABASE USAGE
PROMPT =====================
select (select sum(bytes/1048576) from dba_data_files) "Data Mb",
(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",
(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",
(select sum(bytes/1048576) from dba_data_files) +
(select NVL(sum(bytes/1048576),0) from dba_temp_files) +
(select sum(bytes/1048576)*max(members) from v$log) "Total Mb"
from dual;


PROMPT
PROMPT
PROMPT DB PHYSICAL SIZE
PROMPT ==================
select sum(bytes/1024/1024) "DB Physical Size(MB)" from dba_data_files;


PROMPT
PROMPT
PROMPT DB ACUTAL SIZE
PROMPT ================
select sum(bytes/1024/1024) "DB Actual Size(MB)" from dba_segments;


PROMPT
PROMPT
PROMPT DATABASE NAME AND MODE
PROMPT ========================
select name, open_mode, log_mode from v$database;



PROMPT
PROMPT
PROMPT COUNT OF TABLESPACES
PROMPT =====================
select count(*) AS "No. of tablespaces" from v$tablespace;


PROMPT
PROMPT
PROMPT COUNT OF DATAFILES
PROMPT ===================
select count(*) AS "No. of Datafiles" from dba_data_files;


PROMPT
PROMPT
PROMPT COUNT OF INVALID OBJECTS
PROMPT ==========================
select count(*) from dba_objects where status='INVALID';


PROMPT
PROMPT
PROMPT COUNT OF ARCHIVED GENERATED LAST DAY
PROMPT =====================================
Select count(*) "No. of Archive Logs generated" from v$log_history  where to_char(first_time,'dd-mon-rrrr') in (to_char(sysdate-1,'dd-mon-rrrr'));


PROMPT
PROMPT
PROMPT LIBRARY CACHE HIT RATIO. THIS VALUE SHOULD BE GREATER 90%
PROMPT =========================================================
select (sum(pins)/(sum(pins)+sum(reloads))) * 100 "Library Cache Hit Ratio" from v$librarycache;


PROMPT
PROMPT
PROMPT SGA STATISTICS
PROMPT ===============
set serveroutput on;
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
spsize number;
blkbuf number;
logbuf number;
BEGIN
select value into redlog from v$sysstat
where name = 'redo log space requests';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
select value into spsize from v$parameter where name = 'shared_pool_size';
select value into blkbuf from v$parameter where name = 'db_block_buffers';
select value into logbuf from v$parameter where name = 'log_buffer';
dbms_output.put_line('> SGA CACHE STATISTICS');
dbms_output.put_line('> ********************');
dbms_output.put_line('> SQL Cache Hit rate = '||libcac);
dbms_output.put_line('> Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('> Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('> Redo Log space requests = '||redlog);
dbms_output.put_line('> ');
dbms_output.put_line('> INIT.ORA SETTING');
dbms_output.put_line('> ****************');
dbms_output.put_line('> Shared Pool Size = '||spsize||' Bytes');
dbms_output.put_line('> DB Block Buffer = '||blkbuf||' Blocks');
dbms_output.put_line('> Log Buffer = '||logbuf||' Bytes');
dbms_output.put_line('> ');
if
libcac < 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
redlog > 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/



PROMPT
PROMPT
PROMPT LOCKS
PROMPT =======
SELECT
   inst_id,
   sid sess,
   (select sql_hash_value from gV$session s where s.sid=lk.sid and s.inst_id = lk.inst_id) SQL_HASH_VALUE,
   ctime, id1, id2, trim(lmode) lmode, request, type,
   (select status from gv$session vs where vs.sid = lk.sid and vs.inst_id = lk.inst_id) SessStatus,
   (select object_name from dba_objects where object_id = id1) obj_locked,
   DECODE(request,0,'Holder: ','Waiter: ') position
FROM gV$LOCK lk
WHERE id1 IN (SELECT id1 FROM gv$LOCK WHERE lmode = 0)
ORDER BY id1,request


PROMPT
PROMPT
PROMPT DATAFILE USED  FREE SPAPCE
PROMPT ===========================
SELECT df.tablespace_name,
       df.file_name,
       df.size_mb,
       f.free_mb,
       df.max_size_mb,
       f.free_mb + (df.max_size_mb - df.size_mb) AS max_free_mb
FROM   (SELECT file_id,
               file_name,
               tablespace_name,
               TRUNC(bytes/1024/1024) AS size_mb,
               TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb
        FROM   dba_data_files) df,
       (SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE  df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name;


PROMPT
PROMPT
PROMPT TEMP TABLESPACE USED FREE
PROMPT ==========================
SELECT tf.tablespace_name,
       tf.file_name,
       tf.size_mb,
       f.free_mb,
       tf.size_mb - f.free_mb as USED,
       tf.max_size_mb,
       f.free_mb + (tf.max_size_mb - tf.size_mb) AS max_free_mb
FROM   (SELECT file_id,
               file_name,
               tablespace_name,
               TRUNC(bytes/1024/1024) AS size_mb,
               TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb
        FROM   dba_temp_files) tf,
       (SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE  tf.file_id = f.file_id (+)
ORDER BY tf.tablespace_name,
         tf.file_name;


PROMPT
PROMPT
PROMPT TABLESPACE USED  FREE SPAPCE
PROMPT ==============================
select
   fs.tablespace_name                          "Tablespace",
   (df.totalspace - fs.freespace)              "Used MB",
   fs.freespace                                "Free MB",
   df.totalspace                               "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;


PROMPT
PROMPT
PROMPT TABLESPACE AVERAGE INCREASE PER DAY
PROMPT ====================================
SELECT b.tsname tablespace_name
, MAX(b.used_size_mb) cur_used_size_mb
, round(AVG(inc_used_size_mb),2)avg_increas_mb
FROM (
  SELECT a.days, a.tsname, used_size_mb
  , used_size_mb - LAG (used_size_mb,1)  OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM (
      SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days
       ,ts.tsname
       ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
      FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts
       ,DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dt
      WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
       AND ts.tsname = dt.tablespace_name  AND sp.begin_interval_time > sysdate-7
      GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), ts.tsname
      ORDER BY ts.tsname, days
  ) A
) b GROUP BY b.tsname ORDER BY b.tsname;


PROMPT
PROMPT
PROMPT TABLESPACE GROWTH
PROMPT ===================
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
 , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
 , max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
 FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
 , DBA_HIST_TABLESPACE_STAT ts
 , DBA_HIST_SNAPSHOT sp
 , DBA_TABLESPACES dt
 WHERE tsu.tablespace_id= ts.ts#
 AND tsu.snap_id = sp.snap_id
 AND ts.tsname = dt.tablespace_name
 AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
 GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
 ORDER BY ts.tsname desc, days desc;


PROMPT
PROMPT
PROMPT TABLESPACE STATISTICS
PROMPT ======================
COL file_name FORMAT A45
select tablespace_name ,  file_name, autoextensible , bytes/1024/1024 "USED SPACE(MB)", maxbytes/1024/1024 " MAX SIZE(MB) " from dba_data_files order by tablespace_name,file_name;


PROMPT
PROMPT
PROMPT REDOLOG GROWTH RATE PER DAY
PROMPT ============================
SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
   SELECT
   To_Char(First_Time,'YYYY-MM-DD') DAY,
   Count(1) Count#,
   Min(RECID) Min#,
   Max(RECID) Max#
FROM
   v$log_history
GROUP BY
   To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(
SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log
) B
;


PROMPT
PROMPT
PROMPT NUMBER OF CONNECTED SESSIONS
PROMPT =============================
select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       status,
       TO_CHAR(logon_time,'DD-Mon-YYYY HH24:MI:SS'),
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by status;



PROMPT
PROMPT
PROMPT IO ACTIVITIES
PROMPT ==============
select tablespace_name, regexp_replace(file_name,'^.*.\/.*.\/', '') file_name,sum(phyrds) reads, sum(phywrts) writes, sum(phyrds)+sum(phywrts) total from dba_data_files, v$filestat where file_id=file# group by tablespace_name, file_name order by tablespace_name, file_name;



PROMPT
PROMPT
PROMPT DATAFILE PHYSICAL READS AND WRITES
PROMPT ===================================
COL datafile FORMAT A45
select name datafile, phyrds reads, phywrts writes, phyrds+phywrts total from v$datafile a, v$filestat b where a.file# = b.file# order by total desc;


PROMPT
PROMPT
PROMPT BLOCKING QUERY
PROMPT ===============
select s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' )  is blocking '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2;

PROMPT
PROMPT
PROMPT BLOCKER AND WAITER
PROMPT ===================
Select sid , decode(block,0,'NO','YES') Blocker , decode (request ,0,'NO','YES')WAITER from v$lock where request>0 or block>0 order by block desc;


PROMPT
PROMPT
PROMPT NO of USER CONNECTED
PROMPT =====================
select count(distinct username) "No. of users Connected" from v$session where username is not null;



PROMPT
PROMPT
PROMPT NO of SESSIONS CONNECTED
PROMPT =========================
Select count(*) AS "No of Sessions connected" from v$session where username is not null;


PROMPT
PROMPT
PROMPT DISTINCT USERNAME CONNECTED
PROMPT ============================
Select distinct(username) AS "USERNAME" from v$session;



PROMPT
PROMPT
PROMPT INVALID OBJECT LIST
PROMPT ====================
COL object_name FORMAT A40
select owner , object_name , object_type , status from dba_objects where status='INVALID' order by owner , object_type , object_name;


PROMPT
PROMPT
PROMPT SCHEDULED JOBS
PROMPT ===============
set lines 100
col owner for a8
col job_name for a10
col job_action for a10
col start_date for a10
col repeat_interval for a13
col state for a10
col last_start_date for a10
col next_run_date for a10
select OWNER,JOB_NAME,JOB_ACTION,START_DATE,REPEAT_INTERVAL,STATE,LAST_START_DATE,NEXT_RUN_DATE from dba_scheduler_jobs;


PROMPT
PROMPT
PROMPT DATAFILE IO
PROMPT =============
SELECT Substr(d.name,1,50) "File Name",
       f.phyblkrd "Blocks Read",
       f.phyblkwrt "Blocks Writen",
       f.phyblkrd + f.phyblkwrt "Total I/O"
FROM   v$filestat f,
       v$datafile d
WHERE  d.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;


PROMPT
PROMPT
PROMPT DISPLAY LONG RUNNING OPERATIONS
PROMPT ================================
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#;

Spool off

Database Health Check

@OS level
---------

ORACLE_SID=`ps -ef | grep asm_smon | grep -v 'grep' | grep -v 'sed' | awk '{printf $8}' | awk 'BEGIN{FS="_"}

{printf $3}'`

date
env
uptime
who -b
last | grep -i boot
ps -ef | grep pmon
ps -ef | grep tns
ps -ef | grep d.bin
df -h or df -g
uname
/etc/oratab or /etc/var/oracle/oratab
sqlplus
alert log errors
tnsping
load average
top

@DB LEVEL
---------
export ORACLE_SID

sqlplus "/as sysdba"

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
select

name,INSTANCE_NAME,HOST_NAME,OPEN_MODE,DATABASE_STATUS,DATABASE_ROLE,PROTECTION_LEVEL,CONTROLFILE_TYPE,LOGINS,to_

char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME"from v$database,gv$instance;

select * from v$recover_file;

select distinct status from v$datafile;

select distinct status from v$datafile_header;

alter system checkpoint;

alter system switch logfile;

ASM
---
1.ACFS Filesystem Mount Status
2.crsctl stat res -t

DATAGUARD
---------
1.DR Data SYNC Status.

OTHER
-----
select value from v$parameter where name='log_archive_dest_1';
alert log errors
blocking sessions
sessions/process count

Database health check

ps -ef|grep pmon

ps -ef|grep tns

ORACLE_SID=`ps -ef | grep asm_smon | grep -v 'grep' | grep -v 'sed' | awk '{printf $8}' | awk 'BEGIN{FS="_"} {printf $3}'`

export ORACLE_SID

sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10

select name,INSTANCE_NAME,HOST_NAME,OPEN_MODE,DATABASE_STATUS,DATABASE_ROLE, PROTECTION_LEVEL,CONTROLFILE_TYPE,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME"from v$database,Gv$instance;

alter system checkpoint;

alter system switch logfile;