Log in
updated 11:29 AM UTC, May 4, 2016

Script for Health Check of Database

--run as DBA user or with SELECT_CATALOG_ROLE

spool health_check.log


prompt**---------------Database General Information-----------------------------**

col name for a70
col FILE_NAME for a70
select * from v$dbfile;

select name, value from v$parameter where name='pfile'


prompt**---------------Database General Information-----------------------------**
SELECT DBID "DATABASE_ID", NAME "DB_NAME", LOG_MODE, OPEN_MODE, RESETLOGS_TIME  FROM V$DATABASE;
SELECT instance_name, status, to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM   sys.v_$instance;
column "Host Name" format a15;
column "Host Address" format a15;
SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL;
SELECT BANNER "VERSION" FROM V$VERSION;
col "Database Size" format a15;
col "Free space" format a15;
select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"
from (select bytes from v$datafile
union all select bytes from v$tempfile
union all select bytes from v$log) used,
(select sum(bytes) as p from dba_free_space) free
group by free.p;

prompt**---------------Database SGA Component Size------------------------------**
set line 200;
select    pool, m_bytes from ( select     pool, to_char( trunc(sum(bytes)/1024/1024,2), '99999.99' ) as M_bytes
    from     v$sgastat
    where     pool is not null   group     by pool
    union
    select     name as pool, to_char( trunc(bytes/1024/1024,3), '99999.99' ) as M_bytes
    from     v$sgastat
    where    pool is null  order     by 2 desc
    ) UNION ALL
    select    'TOTAL' as pool, to_char( trunc(sum(bytes)/1024/1024,3), '99999.99' ) from v$sgastat;

prompt**---------------DB Characterset Information-------------------------------**
Select * from nls_database_parameters;
col name  format A60 heading "Control Files";
select name from   sys.v_$controlfile;
col member  format A40 heading "Redolog Files";
set line 200;
col archived format a15;
col status format a10;
col first_time format a20;
select a.group#, a.member, b.archived, b.status, b.first_time from v$logfile a, v$log b
where a.group# = b.group# order by a.group#;

prompt**---------------DB Profile and Default Information--------------------------**
set line 200;
col username format a25;
col profile format a20;
col default_tablespace format a25;
col temporary_tablespace format a25;
Select username, profile, default_tablespace, temporary_tablespace from dba_users;

prompt**---------------PGA_AGGREGATE_TARGET------------------------------------**
set line 200;
select  name, cnt, decode(total, 0, 0, round(cnt*100/total)) percentage
from (select name, value cnt, (sum(value) over()) total
from v$sysstat where name like 'workarea exec%'
);
Prompt--DBA increase this Parameter when "multipass" value are greater than ZERO and Reduce whenever the optimal executions are 100 percent.
select name, value from v$pgastat;

prompt**---------------Users Log on Information------------------------------------**
set line 200;
col OSUSER format a40;
col STATUS format a15
col MACHINE format a35;
Select to_char(logon_time,'dd/mm/yyyy hh24:mi:ss') "Logon_Time",osuser,status,machine from v$session where type !='BACKGROUND';

prompt**---------------Monitoring Schema Growth Rate---------------------------**
select    obj.owner "Owner",  obj_cnt "Objects",  decode(seg_size, NULL, 0, seg_size) "Size in MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj,
(select owner, ceil(sum(bytes)/1024/1024) seg_size
    from dba_segments group by owner) seg
where obj.owner  = seg.owner(+)
order by 3 desc ,2 desc, 1;

prompt**---------------Largest object in Database----------------------------------**
SET LINE 200;
col SEGMENT_NAME format a30;
col SEGMENT_TYPE format a30;
col BYTES format a30;
col TABLESPACE_NAME FORMAT A30;
SELECT * FROM (select SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024/1024 GB, TABLESPACE_NAME from dba_segments order by 3 desc ) WHERE ROWNUM <= 5;

prompt**--------------Monitoring Most resource usnig SQL statements-------------------**
set line 200;
SELECT * FROM   (SELECT Substr(a.sql_text,1,50) sql_text,
               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
               a.buffer_gets, a.disk_reads, a.executions, a.sorts, a.address
        FROM   v$sqlarea a
        ORDER BY 2 DESC)
WHERE  rownum <= 5;

prompt**--------------Monitoring Objects Created within 7 days---------------------**
select count(1) from user_objects where CREATED >= sysdate - 7;

prompt**--------------Counting Invalid object in Database---------------------------**
Select owner, object_type, count(*) from dba_objects where status='INVALID' group by  owner, object_type;

prompt**--------------Monitoring Current Running Long Job in DB--------------------**
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM   V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND    SOFAR != TOTALWORK order by 1;

prompt**----------------Monitoring DML Lock------------------------------------------**
set line 200;
col username format a30;
col lock_type format a20;
col osuser format a30;
col owner format a25;
col object_name format a50;
SELECT s.sid, s. serial#, s.username, l.lock_type, s.osuser, s.machine,
    o.owner, o.object_name, ROUND(w.seconds_in_wait/60, 2) "Wait_Time"                
FROM
     v$session s, dba_locks l, dba_objects o, v$session_wait  w
WHERE   s.sid = l.session_id
  AND l.lock_type IN ('DML','DDL')
  AND l.lock_id1 = o.object_id
  AND l.session_id = w.sid
ORDER BY   s.sid;

prompt**-----------Monitor Non-Sys owned tables in SYSTEM Tablespace-----------------**
SELECT owner, table_name, tablespace_name FROM dba_tables WHERE tablespace_name = 'SYSTEM' AND owner NOT IN ('SYSTEM', 'SYS', 'OUTLN');

prompt**---------------Track Redolog Generation-------------------------------------**
select trunc(completion_time) logdate, count(*) logswitch, round((sum(blocks*block_size) / 1024 / 1024)) "REDO PER DAY(MB)"
from v$archived_log
group by trunc(completion_time)
order by 1;

prompt**--------------Monitor DB Corruption or Need of Recovery--------------------**
set line 200;
SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS,
    r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
    WHERE t.TS# = d.TS# AND d.FILE# = r.FILE#;

prompt**---------------Tablespace Information--------------------------------------**
col tablespace_name format a15 heading "Tablespace Name"
SELECT Total.name "Tablespace Name",
       nvl(Free_space, 0) Free_space,
       nvl(total_space-Free_space, 0) Used_space,
       total_space
FROM
  (select tablespace_name, sum(bytes/1024/1024) Free_Space
     from sys.dba_free_space
    group by tablespace_name
  ) Free,
  (select b.name,  sum(bytes/1024/1024) TOTAL_SPACE
     from sys.v_$datafile a, sys.v_$tablespace B
    where a.ts# = b.ts#
    group by b.name
  ) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name
/
prompt**---------------Shows Used/Free Space Per Datafile---------------------------**

set linesize 200
col file_name format a70 heading "Datafile Name"


SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
         ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
               used_mb,
         NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
    FROM v$datafile df, dba_free_space dfs
   WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

TTI off


prompt**---------------Report Tablespace < 10% free space-----------------------------**
set pagesize 300;
set linesize 100;
column tablespace_name format a15 heading Tablespace;
column sumb format 999,999,999;
column extents format 9999;
column bytes format 999,999,999,999;
column largest format 999,999,999,999;
column Tot_Size format 999,999 Heading "Total Size(Mb)";
column Tot_Free format 999,999,999 heading "Total Free(Kb)";
column Pct_Free format 999.99 heading "% Free";
column Max_Free format 999,999,999 heading "Max Free(Kb)";
column Min_Add format 999,999,999 heading "Min space add (MB)";
select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from sys.dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
sys.dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;

select file_id, tablespace_name, bytes/1024/1024 "bytes_MB", maxbytes/1024/1024 "maxbytes_MB", maxblocks, increment_by, file_name,autoextensible
from dba_data_files where autoextensible = 'YES';

prompt**-------------------File I/O statistics--------------------------------------**

prompt

set linesize 150
col name format a50 heading "Datafile Name"
select name,PHYRDS "Physical Reads",PHYWRTS "Physical Writes",READTIM "Read Time(ms)",WRITETIM "Write Time(ms)",AVGIOTIM "Avg Time" from v$filestat, v$datafile where v$filestat.file#=v$datafile.file#;
set feedback on
prompt

rem -----------------------------------------------------------------------
rem Filename:   sga_stat.sql
rem Purpose:    Display database SGA statistics
rem -----------------------------------------------------------------------
prompt Recommendations:
prompt =======================
prompt* SQL Cache Hit rate ratio should be above 90%, if not then increase the Shared Pool Size.
prompt* Dict Cache Hit rate ratio should be above 85%, if not then increase the Shared Pool Size.
prompt* Buffer Cache Hit rate ratio should be above 90%, if not then increase the DB Block Buffer value.
prompt* Redo Log space requests should be less than 0.5% of redo entries, if not then increase log buffer.
prompt* Redo Log space wait time should be near to 0.
prompt
set serveroutput ON
DECLARE
      libcac number(10,2);
      rowcac number(10,2);
      bufcac number(10,2);
      redlog number(10,2);
      redoent number;
      redowaittime number;
BEGIN
select value into redlog from v$sysstat where name = 'redo log space requests';
select value into redoent from v$sysstat where name = 'redo entries';
select value into redowaittime from v$sysstat where name = 'redo log space wait time';
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';
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('Redo Entries = '||redoent);
dbms_output.put_line('Redo log space wait time = '||redowaittime);
if
 libcac < 90  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 > 1000000 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/
spool off
exit


Thanks to
-- http://osamamustafa.blogspot.sg/2013/01/database-healthcheck.html
-- http://shahiddba.blogspot.sg/2012/03/database-health-check-scripts.html

Add a comment (1)

  • Written by Manjunatha Srinivas
  • Category: Database
  • Hits: 1370

Change column width of show parameter in SQL Plus

column NAME_COL_PLUS_SHOW_PARAM format a30
column VALUE_COL_PLUS_SHOW_PARAM format a30 
 
SQL> show parameter memory

NAME_COL_PLUS_SHOW_PARAM       TYPE        VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
hi_shared_memory_address       integer     0
memory_max_target              big integer 5G
memory_target                  big integer 5G
shared_memory_address          integer     0

Add a comment (1)

  • Written by Manjunatha Srinivas
  • Category: Database
  • Hits: 1125

Identify Oracle Process ID and related SQL

--CPU used by session
SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
    FROM v$session ss, v$sesstat se, v$statname sn
   WHERE     se.STATISTIC# = sn.STATISTIC#
         AND NAME LIKE '%CPU used by this session%'
         AND se.SID = ss.SID
         AND ss.status = 'ACTIVE'
         AND ss.username IS NOT NULL
ORDER BY VALUE DESC;


--clientpid refers to PROCESS ID of the Client Process
SELECT b.sid Session_identifier,
       b.serial# Session_serial_number,
       a.spid OS_process_ID,
       b.process OS_client_process_ID
  FROM v$process a, v$session b
 WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');



       SID    SERIAL# PROCESSID                CLIENTPID
---------- ---------- ------------------------ ------------------------
        15       1391 18626                    18620

Elapsed: 00:00:00.04
10:08:29 SQL> !ps -ef|grep 18626
ora112   18184 18620  0 10:08 pts/6    00:00:00 /bin/bash -c ps -ef|grep 18626
ora112   18186 18184  0 10:08 pts/6    00:00:00 grep 18626
ora112   18626 18620  0 Oct03 ?        00:00:00 oracleORCL (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

10:08:56 SQL> !ps -ef|grep 18620
ora112   18189 18620  0 10:09 pts/6    00:00:00 /bin/bash -c ps -ef|grep 18620
ora112   18191 18189  0 10:09 pts/6    00:00:00 grep 18620
ora112   18620 18590  0 Oct03 pts/6    00:00:00 sqlplus   as sysdba
ora112   18626 18620  0 Oct03 ?        00:00:00 oracleORCL (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Add a comment (2)

  • Written by Manjunatha Srinivas
  • Category: Database
  • Hits: 1229

Monitoring CPU output on Solaris and report in excel

For any DBA to get the CPU utilization for the duration of the issue is very important.
 
For preparing the report to the management needs few non geeky reports.
 
Here is how I managed using AWK:-
 
  • Check the availability of sar files for previous days

 ls -l  /var/adm/sa/
total 163648
-rw-r--r--   1 sys      sys      2771712 Aug  1 23:55 sa01
-rw-r--r--   1 sys      sys      2771712 Aug  2 23:55 sa02
-rw-r--r--   1 sys      sys      2771712 Aug  3 23:55 sa03
-rw-r--r--   1 sys      sys      2771712 Aug  4 23:55 sa04
-rw-r--r--   1 sys      sys      2771712 Aug  5 23:55 sa05
-rw-r--r--   1 sys      sys      2771712 Aug  6 23:55 sa06
-rw-r--r--   1 sys      sys      2771712 Aug  7 23:55 sa07
-rw-r--r--   1 sys      sys      2771712 Aug  8 23:55 sa08
-rw-r--r--   1 sys      sys      2771712 Aug  9 23:55 sa09
-rw-r--r--   1 sys      sys      2771712 Aug 10 23:55 sa10
-rw-r--r--   1 sys      sys      2771712 Aug 11 23:55 sa11
 

  • take the SAR output for the required day and run the below AWK command
sar  -f /var/adm/sa/sa11 | grep -v Ave | awk '{if ($0 ~ /[0-9]/) { print $1","$2","$4","$5","$6; }  }'
 
  • This gives an output in CSV format  :- SunOS,rodez,Generic_141444-09,sun4u,08/11/2014
    00:00:00,%usr,%wio,%idle,
    00:05:00,15,0,84,
    00:10:01,15,0,84,
    00:15:00,15,0,84,
    00:20:00,15,0,84,
    00:25:00,15,0,84,
    00:30:01,15,0,84,
  • Save this in any file or copy the contents
  • Here are the steps in excel to convert to columns
Step 1. Copy the comma delimited text into your clipboard from your text editor or Microsoft Word.
Step 2. Fire up MS Excel and paste the comma separated text into a cell.
Step 3. Click on the Data Tab and then select Text to Columns.
Step 4. Now select Delimited as that is the type of Data we are working with.
Step 5. Select the Comma delimiter and clicked NEXT.
Step 6. Select your Data Type, mine is text so I left the default option of General selected and clicked Finish.
Step 7. All the words have now been separated into Columns.
 
 

 

Add a comment (1)

  • Written by Manjunatha Srinivas
  • Category: Database
  • Hits: 1160
Follow Us on Twitter
Find Us on Facebook
Follow Us on Google
Follow Us on Pinterest