-- ==========================================================
-- STATUS DAS INSTANCES
-- ==========================================================
SET ECHO OFF
ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YY HH24:MI';
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM SWITCH LOGFILE;
SET linesize 300
SELECT inst_id,
instance_number inst_no,
instance_name inst_name,
parallel,
status,
database_status db_status,
active_state state,
host_name host,
startup_time
FROM gv$instance
ORDER BY inst_id;
-- ==========================================================
-- VERIFICAR AS SESSÕES ATIVAS EM CADA NO
-- ==========================================================
break on instance_name skip 1
compute SUM of sessions on instance_name
SELECT
i.instance_name,
s.status,
COUNT(*) sessions
FROM
gv$instance i,
gv$session s
WHERE
i.inst_id = s.inst_id
AND s.username IS NOT NULL
GROUP BY
i.instance_name,
s.status
ORDER BY
i.instance_name,
s.status;
CLEAR break
CLEAR compute
-- ==========================================================
-- TABLESPACES
-- ==========================================================
CLEAR COLumns Computes Breaks
SET lines 200
SET pages 200
COL file_name for a50
COL tablespace_name for a30
COL status for a21
compute SUM of "Total(Mb)" on report
compute SUM of "Free(Mb)" on report
break on report
SELECT t.tablespace_name,
ts.CONTENTS,
ts.status,
Round(Nvl(t.bytes, 0) / 1024 / 1024, 1) "Total(Mb)",
Round(( Nvl(Nvl(f.free, ft.free), 0) / 1024 / 1024 ), 1) "Free(Mb)",
Round(( Nvl(Nvl(f.free, ft.free), 0) * 100 / t.bytes ), 1) "% Free",
Decode(( CASE
WHEN Round(( Nvl(Nvl(f.free, ft.free), 0) / 1024 / 1024 / 1024 )) >= 5
THEN 'OK'
ELSE 'NOK'
END ), 'OK', 'OK',
Decode(CONTENTS, 'UNDO', 'OK - UNDO TABLESPACE',
Decode(CONTENTS, 'TEMPORARY', 'OK - TEMP TABLESPACE',
Decode( Round( ( Nvl(Nvl(f.free, ft.free), 0) * 100 ) / t.bytes),
'0', 'CRITICAL',
'1', 'CRITICAL',
'2', 'CRITICAL',
'3', 'CRITICAL',
'4', 'CRITICAL',
'5', 'WARNING',
'6', 'WARNING',
'7', 'WARNING',
'8', 'WARNING',
'9', 'WARNING',
'OK')
)
)
) STATUS
FROM (SELECT d.tablespace_name,
SUM(d.bytes) bytes
FROM dba_data_files d
GROUP BY tablespace_name
UNION
SELECT d.tablespace_name,
SUM(d.bytes) bytes
FROM dba_temp_files d
GROUP BY tablespace_name) t,
(SELECT tablespace_name,
SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) f,
(SELECT tablespace_name,
SUM(bytes_free) free
FROM v$temp_space_header
GROUP BY tablespace_name) ft,
dba_tablespaces ts
WHERE t.tablespace_name = f.tablespace_name(+)
AND t.tablespace_name = ft.tablespace_name(+)
AND t.tablespace_name = ts.tablespace_name
ORDER BY 5;
-- ==========================================================
TAMANHO DO BANCO
-- ==========================================================
CLEAR COLumns
COL dados for a10
COL undo for a12
COL redo for a12
COL temp for a12
COL livre for a12
COL total for a12
SELECT to_char(SUM(dados) / 1048576, 'fm99g999g990') dados,
to_char(SUM(undo) / 1048576, 'fm99g999g990') undo,
to_char(SUM(redo) / 1048576, 'fm99g999g990') redo,
to_char(SUM(temp) / 1048576, 'fm99g999g990') temp,
to_char(SUM(free) / 1048576, 'fm99g999g990') livre,
to_char(SUM(dados + undo + redo + temp) / 1048576, 'fm99g999g990') total
FROM (
SELECT SUM(decode(substr(t.contents, 1, 1), 'P', bytes, 0)) dados,
SUM(decode(substr(t.contents, 1, 1), 'U', bytes, 0)) undo,
0 redo,
0 temp,
0 free
FROM dba_data_files f, dba_tablespaces t
WHERE f.tablespace_name = t.tablespace_name
UNION ALL
SELECT 0 dados,
0 undo,
0 redo,
SUM(bytes) temp,
0 free
FROM dba_temp_files f, dba_tablespaces t
WHERE f.tablespace_name = t.tablespace_name(+)
UNION ALL
SELECT 0 dados,
0 undo,
SUM(bytes * members) redo,
0 temp,
0 free
FROM v$log
UNION ALL
SELECT 0 dados,
0 undo,
0 redo,
0 temp,
SUM(bytes) free
FROM dba_free_space f, dba_tablespaces t
WHERE f.tablespace_name = t.tablespace_name AND
substr(t.contents, 1, 1) = 'P'
);
--
-- ==========================================================
-- STATUS DOS DATAFILES
-- ==========================================================
SET lines 1000
SET pages 2000
COL tablespace_name for a15
COL name for a60
COL status1 for a12
COL status2 for a12
COL status3 for a12
COL online_status for a12
COL status_backup for a12
COL recover for a7
COL error for a5
COL "FILE#" for 9999
SELECT df.tablespace_name,
d.FILE#,
d.NAME,
df.autoextensible,
df.bytes/1024/1024 "Total(Mb)",
d.status status1,
dh.STATUS status2,
df.status status3,
--df.online_status,
b.STATUS status_backup,
dh.RECOVER,
dh.ERROR
FROM v$datafile d,
dba_data_files df,
v$backup b,
v$datafile_header dh
WHERE d.FILE# = dh.FILE#(+)
AND d.FILE# = b.FILE#(+)
AND d.FILE# = df.file_id(+)
ORDER BY df.tablespace_name,d.NAME;
-- ==========================================================
-- CAMINHO DOS ARCHIVES / VALIDAR A AREA DE ARCHIVE
-- ==========================================================
archive log list;
-- ==========================================================
-- VERIFICAÇÃO DE OBJETOS INVÁLIDOS
-- ==========================================================
CLEAR COLumns
SELECT
owner,
object_type,
count(*) "QTD_INVALIDOS"
FROM dba_objects
WHERE status ='INVALID'
GROUP BY owner, object_type;
-- ==========================================================
-- ESTATISTICAS DAS TABELAS
-- ==========================================================
SELECT
owner,
trunc(last_analyzed) LAST_ANALYZED,
count(*)
FROM DBA_TABLES
GROUP BY owner, trunc(last_analyzed)
ORDER BY OWNER, LAST_ANALYZED;
-- ==========================================================
-- SESSOES WAIT
-- ==========================================================
CLEAR COLumns
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial#,
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM gv$session_wait sw,
gv$session s
WHERE s.sid = sw.sid AND sw.event not like 'SQL%' AND sw.event not like 'rdbms%'
ORDER BY sw.seconds_in_wait DESC;
-- ==========================================================
-- SESSOES COM MAIOR I/O
-- ==========================================================
CLEAR COLumns
COL sid for 9999
COL username for A16 heading 'Username'
COL osuser for A20 heading 'OS user'
COL logical_reads for 999,999,999,990 heading 'Logical reads'
COL physical_reads for 999,999,999,990 heading 'Physical reads'
COL hr for A6 heading 'HR'
SELECT s.sid,
s.username,
s.osuser,
i.block_gets + i.consistent_gets logical_reads,
i.physical_reads,
to_char(
(i.block_gets + i.consistent_gets - i.physical_reads) /
(i.block_gets + i.consistent_gets) * 100, '9990') || '%' hr
FROM gv$sess_io i, gv$session s
WHERE i.INST_ID = s.INST_ID AND
i.sid = s.sid AND
i.block_gets + i.consistent_gets > 200000
ORDER BY logical_reads;
-- ==========================================================
LISTA DE JOBS AGENDADOS NA DBA_JOBS BROKEN = N
-- ==========================================================
SET lines 100 pages 999
COL schema_user format a15
COL fails format 999
COL job for 999999
SELECT
job,
schema_user,
to_char(last_date, 'hh24:mi dd/mm/yy') last_run,
to_char(next_date, 'hh24:mi dd/mm/yy') next_run,
failures fails,
broken,
substr(what, 1, 15) what
FROM dba_jobs
WHERE BROKEN='N'
ORDER BY 4
/
-- ==========================================================
-- JOBS EM EXECUCAO AGENDADOS NA DBA_JOBS
-- ==========================================================
CLEAR COLUMNS
COL job FORM 999999
COL interval FORM a40
COL schema_user FORM a15
COL failures FORM 9999
ALTER SESSION SET nls_date_format='dd/mm/yyyy hh24:mi:ss';
SELECT sid,
job,
last_date,
last_sec,
this_date,
this_sec,
failures,
instance
FROM dba_jobs_running
ORDER BY job;
-- ==========================================================
LISTA DE JOBS AGENDADOS NA DBA_SCHEDULER_JOBS
-- ==========================================================
SELECT
JOB_NAME,
ENABLED,
TO_CHAR(NEXT_RUN_DATE,'DD-MM-YYHH24:MI:SS') PROXIMA_DATA,
RUN_COUNT,
FAILURE_COUNT
FROM DBA_SCHEDULER_JOBS ;
-- ==========================================================
JOBS EM EXECUCAO NA V$SCHEDULER_RUNNING_JOBS
-- ==========================================================
CLEAR COLUMNS
SET lines 1000 trims on
SELECT * FROM GV$SCHEDULER_RUNNING_JOBS ;
-- ==========================================================
-- ULTIMOS BACKUPS EXECUTADOS
-- ==========================================================
SELECT
substr(OPERATION, 1, 12) operation,
substr(STATUS, 1, 10) status,
MBYTES_PROCESSED,
INPUT_BYTES,
OUTPUT_BYTES,
substr(OPTIMIZED, 1, 10) optimized,
substr(OBJECT_TYPE, 1, 10) object_type,
substr(OUTPUT_DEVICE_TYPE, 1, 10) output_device_type,
START_TIME,
END_TIME,
rpad(round ((end_time - start_time) *1440 , 2), 8, ' ') Minutos,
lpad(round ((end_time - start_time) *1440 / 60 , 2) , 8, ' ') Horas
FROM
V$RMAN_STATUS
WHERE
start_time BETWEEN sysdate - 1 AND sysdate
ORDER BY
START_TIME;
-- ==========================================================
-- VERIFICACAO SE OS TABLESPACES ESTAO EM BEGIN BACLKUP
-- ==========================================================
SELECT DISTINCT status FROM v$backup;
-- ==========================================================
-- VERIFICAR SE EXISTE BACKUP EM EXECUCAO E O PROGRESSO
-- ==========================================================
SET lines 1000 trims on
SELECT
sid,
serial #,
sofar,
totalwork,
opname,
Round(sofar / totalwork * 100, 2) " % completado",
Round(time_remaining / 60) REMAINING,
Round(elapsed_seconds / 60) ELAPSED
FROM
gv$session_longops
WHERE
opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork;
-- ==========================================================
-- Processos da instancia
-- ==========================================================
SET LINESIZE 190
COLUMN spid FORMAT A10
COLUMN username FORMAT A14
COLUMN PROGRAM FORMAT A40
COLUMN PROGRAM MACHINE A40
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program, s.status, s.machine, to_char(s.LOGON_TIME,'dd-mm-yyyy HH24:MM') LOGON_TIME
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND' ORDER BY s.STATUS;
-- ==========================================================
-- TRANSACOES LONGAS
-- ==========================================================
SET term on
SET verify off
SET feed on
CLEAR COLumns
COL sid form 999
COL serial# form 9999999999
COL sofar form 999,999,999
COL time_remaining form 999,999,999
COL username form a30
SELECT sid,
serial#,
totalwork,
sofar,
to_char(start_time,'dd/mm/yyyy hh24:mi:ss') start_time,
time_remaining "TEMPO_RESTANTE(S)",
time_remaining/60 "TEMPO_RESTANTE(MIN)",
time_remaining/60/60 "TEMPO_RESTANTE(HS)",
substr(username,1,20) username,
sql_address,
sql_id,
sql_hash_value
FROM gv$session_longops
WHERE time_remaining > 0
ORDER BY 6 desc ;
-- ==========================================================
-- VERIFICA LOCKS NO BANCO DE DADOS RAC
-- ==========================================================
SET lines 300
SET pages 50000
COL username format a12
COL sid format 999999
COL inst_id format 99999
COL sql_text format a36
COL status format a8
SELECT
f.INST_ID,
f.sid,
f.username,
f.STATUS,
f.BLOCKING_INSTANCE,
f.blocking_session,
f.lockwait,
f.SECONDS_IN_WAIT,
f.LAST_CALL_ET,
s.SQL_TEXT,
s.hash_value
FROM
(
SELECT
INST_ID,
sid,
username,
STATUS,
BLOCKING_INSTANCE,
blocking_session,
lockwait,
SECONDS_IN_WAIT,
LAST_CALL_ET,
SQL_HASH_VALUE
FROM
gv$session
WHERE
blocking_session IS NOT NULL
AND SECONDS_IN_WAIT > 0
UNION ALL
SELECT
A.INST_ID,
A.sid,
A.username,
A.STATUS,
a.BLOCKING_INSTANCE,
A.blocking_session,
A.lockwait,
A.SECONDS_IN_WAIT,
A.LAST_CALL_ET,
A.SQL_HASH_VALUE
FROM
gv$session A,
gv$session B
WHERE
A.sid = B.blocking_session
AND A.INST_ID = b.BLOCKING_INSTANCE
)
f,
gv$sqlarea s
WHERE
f.inst_id = s.inst_id
AND
f.sql_hash_value = s.hash_value;
-- ==========================================================
-- TOP 10 SESSIONS PARA O NÓ 1
-- ==========================================================
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A30
COL osuser for a10
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
a.lockwait,
a.status,
a.machine,
a.program,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM Gv$session a,
v$sesstat c,
v$statname d
WHERE a.sid = c.sid
AND A.INST_ID=1
AND c.statistic# = d.statistic#
AND d.name = DECODE(UPPER('c.value'), 'READS', 'session logical reads',
'EXECS', 'execute count',
'CPU', 'CPU used by this session',
'CPU used by this session')
AND rownum < 11
ORDER BY c.value DESC;
-- ==========================================================
-- TOP 10 SESSIONS PARA O NÓ 2
-- ==========================================================
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A30
COL osuser for a10
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial#,
a.lockwait,
a.status,
a.machine,
a.program,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM Gv$session a,
v$sesstat c,
v$statname d
WHERE a.sid = c.sid
AND A.INST_ID=2
AND c.statistic# = d.statistic#
AND d.name = DECODE(UPPER('c.value'), 'READS', 'session logical reads',
'EXECS', 'execute count',
'CPU', 'CPU used by this session',
'CPU used by this session')
AND rownum < 11
ORDER BY c.value DESC;
-- ==========================================================
-- MEMORIA ALOCADA POR SESSAO PARA O NÓ 1
-- ==========================================================
SET LINESIZE 200
COLUMN username FORMAT A20
COLUMN module FORMAT A30
SELECT NVL(a.username,'(oracle)') AS username,
a.module,
A.OSUSER,
a.program,
Trunc(b.value/1024/1024) AS memory_MB
FROM gv$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = 'session pga memory'
AND a.program IS NOT NULL AND Trunc(b.value/1024/1024) > 0 AND a.inst_id=1
ORDER BY b.value DESC;
-- ==========================================================
-- MEMORIA ALOCADA POR SESSAO PARA O NÓ 2
-- ==========================================================
SET LINESIZE 200
COLUMN username FORMAT A20
COLUMN module FORMAT A30
SELECT NVL(a.username,'(oracle)') AS username,
a.module,
A.OSUSER,
a.program,
Trunc(b.value/1024/1024) AS memory_MB
FROM gv$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = 'session pga memory'
AND a.program IS NOT NULL AND Trunc(b.value/1024/1024) > 0 AND a.inst_id=2
ORDER BY b.value DESC;
-- ==========================================================
-- VERIFICACAO DOS LOGFILES PARA O NÓ 1
-- ==========================================================
CLEAR COLUMNS
COL member for a60
SET pagesize 5000
SET lines 1000 trims on
SELECT
f.MEMBER,
f.GROUP #,
l.members,
l.bytes,
l.SEQUENCE #,
f.status status_1,
l.status status_2,
f.IS_RECOVERY_DEST_FILE
FROM
gv $ logfile f
JOIN
gv $ log l
ON l.GROUP # = f.GROUP #
AND l.inst_id = 1
AND f.inst_id = 1
ORDER BY
GROUP # ;
-- ==========================================================
-- VERIFICACAO DOS LOGFILES PARA O NÓ 2
-- ==========================================================
CLEAR COLUMNS
COL member for a60
SET pagesize 5000
SET lines 1000 trims on
SELECT
f.MEMBER,
f.GROUP #,
l.members,
l.bytes,
l.SEQUENCE #,
f.status status_1,
l.status status_2,
f.IS_RECOVERY_DEST_FILE
FROM
gv $ logfile f
JOIN
gv $ log l
ON l.GROUP # = f.GROUP #
AND l.inst_id = 1
AND f.inst_id = 2
ORDER BY
GROUP # ;
