/* Database Version */
select banner_full from v$version;/* Components Installed in database */
col comp_id format a12 col comp_name format a40 col schema format a20 col version format a12 select comp_id,comp_name,version,status,modified,schema from dba_registry order by comp_id;
/* Database Role */
select name, created, database_role,log_mode from v$database;/* Jobs Running More than a day */
SELECT count(*) from dba_scheduler_running_jobs where extract(day FROM elapsed_time) > 1;/* FAILED DBMS_SCHEDULER JOBS IN THE LAST 24 hours*/
SELECT INSTANCE_ID ID,
JOB_NAME,
OWNER,
LOG_DATE,
STATUS,
ERROR#,
RUN_DURATION
FROM DBA_SCHEDULER_JOB_RUN_DETAILS
WHERE LOG_DATE > SYSDATE - 1 AND STATUS = 'FAILED'
ORDER BY JOB_NAME, LOG_DATE;/* Database Size */
set lines 200
col "database size" format a30
col "used space" format a30
col "Free space" format a30
SELECT ROUND (SUM (used.bytes) / 1024 / 1024 / 1024) || ' GB'
"Database Size",
ROUND (SUM (used.bytes) / 1024 / 1024 / 1024)
- ROUND (free.p / 1024 / 1024 / 1024)
|| ' GB'
"Used space",
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;
/* Asm diskgroup size & usage details */
SELECT name group_name, sector_size sector_siz, block_size block_size, allocation_unit_size allocation_unit_size, state state, TYPE TYPE, ROUND (total_mb / 1024, 2) total_gb, ROUND ((total_mb - free_mb) / 1024, 2) Used_Gb, ROUND ((1 - (free_mb / total_mb)) * 100, 2) || ' %' "Used", ROUND (free_mb / 1024, 2) Free_space, 100 - ROUND ((1 - (free_mb / total_mb)) * 100, 2) || ' %' "Free" FROM gv$asm_diskgroup ORDER BY name;
/* FRA Utilization */
select * from v$flash_recovery_area_usage;
/* Growth of database Schema in the past days */
set feedback off
set pages 80
set linesize 150
ttitle "Total Disk Used"
select sum(space_used_delta) / 1024 / 1024 "Space used (M)", sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - &days_back
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner = '&schema_name'
and space_used_delta > 0;
/* Check if Dataguard is Configured */
set serveroutput on
declare
feature_boolean number;
aux_count number;
feature_info clob;
begin
dbms_feature_data_guard(feature_boolean, aux_count, feature_info);
dbms_output.put_line(feature_boolean);
dbms_output.put_line(feature_info);
end;
/
/* Tablespace Usage */
SET LINES 2000
SET PAGESIZE 100
COL status FOR a9
COL DBNAME FOR a10
COL TSNAME FOR a20
COL "Size (M)" FOR a20
COL "Used (M)" FOR a20
COL "Used (%)" FOR a10
COL "Free (M)" FOR a20
COL "Number" FOR a7
SELECT d.status,
db.name dbname,
d.tablespace_name tsname,
CASE
WHEN (d.contents = 'TEMPORARY')
THEN
TO_CHAR (NVL (t.bytes / 1024 / 1024, 0), '999999999990.90')
ELSE
TO_CHAR (NVL (a.bytes / 1024 / 1024, 0), '999999999990.90')
END AS "Size (M)",
CASE
WHEN (d.contents = 'TEMPORARY')
THEN
TO_CHAR (NVL (tp.bytes, 0), '99999990.99')
ELSE
TO_CHAR (NVL ((a.bytes - NVL (f.bytes, 0)) / 1024 / 1024, 0),
'99999990.90')
END AS "Used (M)",
CASE
WHEN (d.contents = 'TEMPORARY')
THEN
TO_CHAR (NVL (tp.free, 0), '99999990.99')
ELSE
TO_CHAR (ROUND (f.bytes / 1024 / 1024, 2), '9999999.90')
END AS "Free (M)",
CASE
WHEN (d.contents = 'TEMPORARY')
THEN
TO_CHAR (NVL (tp.percent, 0), '990.99')
ELSE
TO_CHAR (
NVL ((a.bytes - NVL (f.bytes, 0)) / a.bytes * 100, 0),
'990.90')
END AS "Used (%)",
TO_CHAR (ax.numberdf, '999') "Number"
FROM sys.dba_tablespaces d,
( SELECT tablespace_name, SUM (bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name, SUM (bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name) t,
( SELECT tablespace_name, SUM (bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) f,
( SELECT tablespace_name, COUNT (*) numberdf
FROM dba_data_files
WHERE autoextensible = 'YES'
GROUP BY tablespace_name) ax,
( SELECT tablespace_name,
SUM (bytes_used) / 1024 / 1024 bytes,
SUM (BYTES_FREE) / 1024 / 1024 free,
(SUM (bytes_used) / (SUM (bytes_used) + SUM (bytes_free)))
* 100 percent
FROM v$temp_space_header
GROUP BY tablespace_name) tp,
v$database db
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.tablespace_name = ax.tablespace_name(+)
AND d.tablespace_name = tp.tablespace_name(+)
ORDER BY 7 DESC;
/* Growth in Tablespace Size during Past 7 days */
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;
/* Top TEMP Space Consumers */
SELECT SUBSTR (
s.USERNAME
|| '|'
|| s.sid
|| ','
|| s.serial#
|| '|'
|| SUBSTR (s.MACHINE, 1, 20)
|| '|'
|| SUBSTR (s.MODULE, 1, 20),
1,
65) "USER|SID,SER|MACHINE|MODULE",
SUM (O.BLOCKS) * T.BLOCK_SIZE / 1024 / 1024 USED_MB,
COUNT (*) SORTS#,
S.SQL_ID,
O.TABLESPACE
FROM V$SORT_USAGE O, V$SESSION S, DBA_TABLESPACES T
WHERE O.SESSION_ADDR = S.SADDR AND O.TABLESPACE = T.TABLESPACE_NAME
GROUP BY S.SID,
S.SERIAL#,
S.USERNAME,
S.OSUSER,
S.MODULE,
S.MACHINE,
T.BLOCK_SIZE,
S.SQL_ID,
O.TABLESPACE
ORDER BY USED_MB DESC, S.USERNAME;
/* Check RMAN Backup Details */
SELECT DECODE (backup_type,
'L', 'Archived Logs',
'D', 'Datafile Full',
'I', 'Incremental')
backup_type,
bp.tag
"RMAN_BACKUP_TAG",
device_type
"DEVIC",
DECODE (bs.controlfile_included, 'NO', NULL, bs.controlfile_included)
controlfile,
(sp.spfile_included)
spfile,
SUM (bs.incremental_level)
"L",
TO_CHAR (bs.start_time, 'dd/mm/yyyy HH24:MI:SS')
start_time,
TO_CHAR (bs.completion_time, 'dd/mm/yyyy HH24:MI:SS')
completion_time,
SUM (bs.elapsed_seconds)
"FIN:SECONDS"
FROM v$backup_set bs,
(SELECT DISTINCT set_stamp,
set_count,
tag,
device_type
FROM v$backup_piece
WHERE status IN ('A', 'X')) bp,
(SELECT DISTINCT set_stamp, set_count, 'YES' spfile_included
FROM v$backup_spfile) sp
WHERE bs.start_time > SYSDATE - 1
AND bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bs.set_stamp = sp.set_stamp(+)
AND bs.set_count = sp.set_count(+)
GROUP BY backup_type,
bp.tag,
device_type,
bs.controlfile_included,
pieces,
sp.spfile_included,
start_time,
bs.completion_time
ORDER BY backup_type, bs.start_time DESC;
SELECT COMMAND_ID,
START_TIME,
END_TIME,
OUTPUT_DEVICE_TYPE,
STATUS,
INPUT_TYPE,
ELAPSED_SECONDS
FROM v$rman_backup_job_details
ORDER BY start_time DESC;
/* Datafile Size - usage */
SELECT SUBSTR (df.tablespace_name, 1, 20)
"Tablespace Name",
SUBSTR (df.file_name, 1, 80)
"File Name",
ROUND (df.bytes / 1024 / 1024 /1024, 0)
"Size (GB)",
DECODE (e.used_bytes, NULL, 0, ROUND (e.used_bytes / 1024 / 1024 / 1024 , 0))
"Used GB)",
DECODE (f.free_bytes, NULL, 0, ROUND (f.free_bytes / 1024 / 1024 /1024 , 0))
"Free (GB)",
DECODE (e.used_bytes,
NULL, 0,
ROUND ((e.used_bytes / df.bytes) * 100, 0))
"% Used", autoextensible, round(maxbytes/1024/1024/1024,2) "Extend upto GB"
FROM dba_data_files df,
( SELECT file_id, SUM (bytes) used_bytes
FROM dba_extents
GROUP BY file_id) E,
( SELECT MAX (bytes) free_bytes, file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id(+) = df.file_id AND df.file_id = f.file_id(+)
ORDER BY df.tablespace_name, df.file_name;/* RMAN Block Corruption */
select * from V$DATABASE_BLOCK_CORRUPTION;/* Archives Generation / Redo Log Switches in Past days */
SELECT TRUNC (first_time)
"Date",
TO_CHAR (TRUNC (first_time), 'Dy')
"Day",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 0, 1))
"00",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 1, 1))
"01",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 2, 1))
"02",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 3, 1))
"03",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 4, 1))
"04",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 5, 1))
"05",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 6, 1))
"06",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 7, 1))
"07",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 8, 1))
"08",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 9, 1))
"09",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 10, 1))
"10",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 11, 1))
"11",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 12, 1))
"12",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 13, 1))
"13",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 14, 1))
"14",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 15, 1))
"15",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 16, 1))
"16",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 17, 1))
"17",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 18, 1))
"18",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 19, 1))
"19",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 20, 1))
"20",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 21, 1))
"21",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 22, 1))
"22",
SUM (DECODE (TO_NUMBER (TO_CHAR (FIRST_TIME, 'HH24')), 23, 1))
"23"
FROM gv$log_history
WHERE TRUNC (first_time) >= (TRUNC (SYSDATE) - &a) -- last X days. 0 = today only. 1 = today and yesterday
GROUP BY TRUNC (first_time)
ORDER BY TRUNC (first_time) DESC;
/* Objects created in past 30 days */
select object_name,owner,object_type from dba_objects
where created > sysdate-30
and owner <> 'SYS';
/* Check Statistics */
select table_name, stale_stats, last_analyzed from dba_tab_statistics;select table_name, stale_stats, last_analyzed from dba_tab_statistics where stale_stats like 'YES';/* Status of Indexes */
/* Rebuild Unusable Indexes */
SELECT 'ALTER INDEX '
|| OWNER
|| '.'
|| INDEX_NAME
|| ' REBUILD '
|| ' TABLESPACE '
|| TABLESPACE_NAME
|| ';'
FROM DBA_INDEXES
WHERE STATUS = 'UNUSABLE'
UNION
SELECT 'ALTER INDEX '
|| INDEX_OWNER
|| '.'
|| INDEX_NAME
|| ' REBUILD PARTITION '
|| PARTITION_NAME
|| ' TABLESPACE '
|| TABLESPACE_NAME
|| ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS = 'UNUSABLE'
UNION
SELECT 'ALTER INDEX '
|| INDEX_OWNER
|| '.'
|| INDEX_NAME
|| ' REBUILD SUBPARTITION '
|| SUBPARTITION_NAME
|| ' TABLESPACE '
|| TABLESPACE_NAME
|| ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS = 'UNUSABLE';
/* AUTOTASK INTERNAL MAINTENANCE WINDOWS */
SELECT WINDOW_NAME,
TO_CHAR (WINDOW_NEXT_TIME, 'DD-MM-YYYY HH24:MI:SS') NEXT_RUN,
AUTOTASK_STATUS STATUS,
WINDOW_ACTIVE ACTIVE,
OPTIMIZER_STATS,
SEGMENT_ADVISOR,
SQL_TUNE_ADVISOR
FROM DBA_AUTOTASK_WINDOW_CLIENTS;
/* Blocking Sessions */
col module for a27
col event for a24
col MACHINE for a27
col "WA_ST|WAITD|ACT_SINC|LOG_T" for a38
col "INST|USER|SID,SERIAL#" for a30
col "INS|USER|SID,SER|MACHIN|MODUL" for a65
col "PREV|CURR SQLID" for a27
col "I|BLKD_BY" for a12select
substr(s.INST_ID||'|'||s.USERNAME||'| '||s.sid||','||s.serial#||' |'||substr(s.MACHINE,1,22)||'|'||substr(s.MODULE,1,18),1,65)"INS|USER|SID,SER|MACHIN|MODUL"
,substr(w.state||'|'||round(w.WAIT_TIME_MICRO/1000000)||'|'||LAST_CALL_ET||'|'||to_char(LOGON_TIME,'ddMon'),1,38) "WA_ST|WAITD|ACT_SINC|LOG_T"
,substr(w.event,1,24) "EVENT"
,s.FINAL_BLOCKING_INSTANCE||'|'||s.FINAL_BLOCKING_SESSION "I|BLKD_BY"
from gv$session s, gv$session_wait w
where s.USERNAME is not null
and s.FINAL_BLOCKING_SESSION is not null
and s.sid=w.sid
and s.STATUS='ACTIVE'
order by "I|BLKD_BY" desc,w.event,"INS|USER|SID,SER|MACHIN|MODUL","WA_ST|WAITD|ACT_SINC|LOG_T" desc;
/* INACTIVE SESSION More than 24 hours */
SELECT s.last_call_et,
s.username, s.*
FROM gv$session s
WHERE s.last_call_et > (60*60*24)
AND status = 'INACTIVE' and program not like '%ORACLE.EXE%'
ORDER BY 1 DESC;
/* Kill inactive session for more than 24 hours */
SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#|| ',@'||inst_id || ''''||' immediate;' "INACTIVE_SESSIONS"
FROM gv$session s
WHERE s.last_call_et > (60*60*24) and program not like '%ORACLE.EXE%'
AND status = 'INACTIVE';
/* Find Out Invalid Objects */
select count(*), owner from dba_invalid_objects group by owner;
select object_name, object_type, owner fro m dba_invalid_objects;/* Compile invalids using utlrp.sql or use the manual method */
SELECT 'alter package ' || owner || '.' || object_name || ' compile;'FROM dba_objectsWHERE status <> 'VALID' AND object_type LIKE '%PACKAGE%'UNIONSELECT 'alter type '|| owner|| '.'|| object_name|| ' compile specification;'FROM dba_objectsWHERE status <> 'VALID' AND object_type LIKE '%TYPE%'UNIONSELECT 'alter '|| object_type|| ' '|| owner|| '.'|| object_name|| ' compile;'FROM dba_objectsWHERE status <> 'VALID'AND object_type NOT IN ('PACKAGE','PACKAGE BODY','SYNONYM','TYPE','TYPE BODY')UNIONSELECT 'alter public synonym ' || object_name || ' compile;'FROM dba_objectsWHERE status <> 'VALID' AND object_type = 'SYNONYM';
/* Queries Running for more than 2/4 hours */
select username||'| '||sid ||','|| serial# "USERNAME| SID,SERIAL#",substr(MODULE,1,30) "MODULE", to_char(sysdate-last_call_et/24/60/60,'DD-MON HH24:MI') STARTED_AT,
last_call_et/60/60 "DURATION_HOURS"
,SQL_ID
from v$session where
username is not null
and module is not null
and last_call_et > 60*60*2
and status = 'ACTIVE';
/* Fragmented Tables */
set lines 170
set pages 10000
col owner format a30
col table_name format a30
col TOTAL_SIZE format 99999999999
col ACTUAL_SIZE format 999999999999
col FRAGMENTED_SPACE format 999999999999
select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),0) "TOTAL_SIZE", round((num_rows*avg_row_len
/1024/1024),0) "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),0) "FRAGMENTED_SPACE" from
dba_tables where owner not in ('SYS','SYSTEM','FDBA','PERFSTAT','DBMON') and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 100 order by 8 desc;
/* CURRENT OS / HARDWARE STATISTICS */
select * from v$resource_limit order by RESOURCE_NAME;/* Recycle bin */
select * from dba_recyclebin ;/* Auditing */
select count(*) from sys.aud$;/* Check if Unified auditing in enabled */
select * from v$option where PARAMETER = 'Unified Auditing'; /* Purge unified auditing data */
select count(*) from unified_audit_trail;
exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP ( -
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
LAST_ARCHIVE_TIME => sysdate-1);
exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( -
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, -
USE_LAST_ARCH_TIMESTAMP => TRUE);
/* List of Patches Applied */
/* 12c */
select patch_id,version,action,action_time, description,bundle_series from dba_registry_sqlpatch;
/* 19c */
select patch_id,action,action_time, description,patch_type from dba_registry_sqlpatch;
No comments:
Post a Comment