One of the daily task for oracle DBA to measure the health of databases.
This includes the database status,instance name, database open mode, start up time,DR status,Archive log generation etc.
The following script can be used to generate the oracle Database health Report. Below script is very basic script written to check database status, additions can be made to script as per the requirements.
This report generates the following basic checks.
1. DATABASE STATUS
2. DATABASE NAME AND MODE
3. DR DATABASE STATUS
4. ARCHIVE LOG GENERATION LAST WEEK
5. DB PHYSICAL SIZE, DB ACTUAL SIZE
6. TABLESPACE USAGES
7. NO of USER/SESSIONS CONNECTED
8. DICTIONARY/LIBRARY HIT RATIO
9 BLOCKING QUERIES,BLOCKER AND WAITER
10. INVALID OBJECT COUNT LIST
Scripts Start.
*****************************************************************************
*****************************************************************************
set feedback off
set serverout on
set wrap off
set pages 300
set lines 200
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 NAME AND MODE
PROMPT ========================
select name, open_mode, log_mode from v$database;
PROMPT
PROMPT
PROMPT DR DATABASE STATUS
PROMPT ===========================================================
Col APPLIED_TIME format a20
Col destination format a20
Col Status format a20
SELECT DB_NAME,destination, APPLIED_TIME, LOG_APPLIED,LOG_ARCHIVED,
(
CASE
WHEN ((APPLIED_TIME IS NOT NULL AND (LOG_ARCHIVED-LOG_APPLIED) IS NULL)
OR (APPLIED_TIME IS NULL AND (LOG_ARCHIVED-LOG_APPLIED) IS NOT NULL)
OR ((LOG_ARCHIVED-LOG_APPLIED) > 1))
THEN 'Error! Log Gap is '
ELSE 'OK!'
END) Status,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
( SELECT INSTANCE_NAME DB_NAME FROM GV$INSTANCE WHERE INST_ID = 1 ),
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 ),
(select applied_seq# as LOG_APPLIED,destination as destination from v$archive_dest_status WHERE DEST_ID=3 ),
(SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=1 );
PROMPT
PROMPT
PROMPT ARCHIVE LOG GENERATION LAST WEEK
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#,MIN(sequence#),MAX(sequence#)
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 where rownum < 8;
PROMPT
PROMPT
PROMPT DB PHYSICAL SIZE
PROMPT =====================================
select sum(bytes/1024/1024/1024) "DB Physical Size(GB)" from dba_data_files;
PROMPT
PROMPT
PROMPT DB ACTUAL SIZE
PROMPT =====================================
select sum(bytes/1024/1024/1024) "DB Actual Size(GB)" from dba_segments;
PROMPT
PROMPT
PROMPT TABLESPACE USAGES
PROMPT ===========================================================
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (Mb)",
SUM(fs.bytes) / (1024 * 1024) "Free (Mb)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 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 DICTIONARY HIT RATIO. THIS VALUE SHOULD BE GREATER 85%
PROMPT ==========================================================
select ( 1 - ( sum (decode (name, 'physical reads', value, 0)) / ( sum (decode (name, 'db block gets',value, 0)) + sum (decode (name, 'consistent gets', value, 0))))) * 100 "Buffer Hit Ratio" from v$sysstat;
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 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 INVALID OBJECT COUNT LIST
PROMPT ===========================================================
select count(*),owner , object_type from dba_objects where status='INVALID' group by owner , object_type order by owner;
*****************************************************************************
*****************************************************************************
Script End.
0 comments:
Post a Comment