Hola!..
Os dejo un script que utilizo para comprobar el estado y consistencia general de una bbdd Oracle. Es compatible desde la versión 10g hasta la 12c.
Para ejecutar el script basta con utilizar un sqlplus con privilegios de DBA y poco más.
Ejemplo:
Sqlplus sys/sys@local @healthcheck.sql
Copiar el siguiente contenido a un fichero de texto y ponerle de nombre healthcheck.sql
spool c:\resultado_heathcheck.txt
prompt**-----------Database health check script for Oracle Database's-----------**
prompt**------Based on the Shahid Ahmed script - Updated by Eduardo Arana-------**
prompt**------Updates:----------------------------------------------------------**
prompt**------01/03/2014 - Check if there any sessions blocked | E.Arana--------**
prompt
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#;
--Added dual checking if there any sessions getting blocked right now. --E.Arana
prompt**--------------Check if there any sessions getting blocked right now--------**
-- WHAT ALL THE SESSIONS ARE GETTING BLOCKED
select 'SESSIONS BLOCKED',process,sid, blocking_session from v$session where blocking_session is not null;
prompt**--------------Check which session is blocking which sessions---------------**
-- WHICH SESSION IS BLOCKING WHICH SESSION
set lines 9999
set pages 9999
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**---------------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 a50 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;
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
El resultado de la ejecución generará un fichero de salida en c:\ llamado resultado_healthcheck.txt
Espero que os resulte útil.
Un saludo.
