Hi Friends,
Below are some useful Database Health Check scripts we can use for monitoring purpose.
These SQL scripts assists us to monitor Database and diagnose in case of any issues.
1) fra_space_check.sql( Flash recovery area and DR sync)
set lines 200;
select (SPACE_LIMIT/1024/1024/1024)SPACE_LIMIT_gb,
(SPACE_USED/1024/1024/1024)SPACE_USED_gb,
(SPACE_RECLAIMABLE/1024/1024/1024)SPACE_RECLAIMABLE_gb,
((SPACE_LIMIT/1024/1024/1024)-(SPACE_USED/1024/1024/1024)+(SPACE_RECLAIMABLE/1024/1024/1024)) AVAILABLE_SPACE,
ROUND((SPACE_USED-SPACE_RECLAIMABLE)/SPACE_LIMIT*100,1)"PERCENT_FULL_OF_100%"
FROM V$RECOVERY_FILE_DEST;
select thread#,max(sequence#) from v$archived_log group by thread#;
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
2)max_tablespace_size.sql(Tablespace space check)
set lines 200 pages 300;
select TABLESPACE_NAME,sum(BYTES)/1024/1024/1024,sum(MAXBYTES)/1024/1024/1024,sum(BYTES)/1024/1024/1024*100/(sum
(MAXBYTES)/1024/1024/1024) "Used%" from dba_data_files where AUTOEXTENSIBLE='YES' group by TABLESPACE_NAME order by 4;
3)longops_session.sql( long running operations in Database)
set linesize 180
col opname for a30
col username for a15
SELECT SID, SERIAL#, username,to_char(start_time, 'dd Mon, yyyy hh24:mi:ss') as start_time, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;
4)temp_space_check.sql(For Temporary tablespace space check)
col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';
5)events_details.sql(Wait Events monitoring)
set pages 300;
set lines 200;
col event for a50;
col username for a10;
select s.sid ,username,status ,s.module,s.sql_id,w.event,w.SECONDS_IN_WAIT,w.STATE from v$session_wait w,v$session s
where w.sid=s.sid and w.EVENT <> 'SQL*Net message from client' and username is not null and w.event <> 'Streams AQ:
waiting for messages in the queue';
6)log_switches_info.sql(Log switches in a day)
set lines 250;
set pages 200;
select to_char(first_time,'DD-MON-YYYY') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" from v$log_history group by to_char(first_time,'DD-
MON-YYYY') order by 1;
7)logon_sesion.sql( Logon time for a session in Database)
select sid,serial#,to_char(logon_time,'DD-MON-YYYY:HH24:MI:SS'),program,module from v$session where sid=&1;
8)sessinfo_db.sql( Session information in Database)
select sid,serial#,program from v$session where sid=&1;
9)plan_query.sql( To get execution plan display of a query by providing sql_id from v$sql)
set lines 200;
set pages 1000;
select * from table(dbms_xplan.display_cursor('&1'));
10)sync_prod_db.sql(To check max sequence number in the primary database)
select thread#,max(sequence#) from v$archived_log group by thread#;
11)tablespace_space.sql( To get tablespace details type and space usage in Database)
set linesize 180
set pagesize 100
col "Name" for a30
SELECT d.tablespace_name "Name", d.contents "Type", d.status "Status",TO_CHAR(NVL(a.bytes / 1024 / 1024,
0),'99G999G990D90')
"Total Size (M)", TO_CHAR(NVL(NVL(f.bytes, 0),0)/1024/1024, '99G999G990D90')
"Free (MB)", TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00')
"Free %" 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_free_space group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) order by "Free %";
12)drsync_check.sql( Check the applied and unapplied archives on the Standby Database side)
select sequence# from v$archived_log where applied='NO';
select max(sequence#) from v$archived_log where applied='YES';
exit;
/
13)invalids.sql( To count the invalids objects in the Database)
select count(*) from dba_objects where status='INVALID';
14) lock_session.sql( To find the locking session details in the Database)
set linesize 180
set pagesize 10000
col sql_fulltext for a100
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 ;
15) Temp_usage.sql( To find the temporary tablespace usage details)
SELECT A.tablespace_name tablespace,
D.mb_total,
SUM(A.used_blocks * D.block_size) / 1024/1024 mb_used,
D.mb_total - SUM(A.used_blocks * D.block_size) / 1024/1024 mb_free
FROM v$sort_segment A,
(SELECT B.name, C.block_size, SUM(C.bytes) / 1024/1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts# = C.ts#
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
16) master_db_hc.sql( Master Health check script for Database,creates DBHEALTH.html report)
set feedback off;
set markup html on spool on;
spool DBHEALTH.html;
set termout off;
prompt ************ DAILY HEALTH CHECK SCRIPT DB************
prompt ************ SCRIPT FOR DB: RAFI ALVI ************
prompt
prompt**---------------Time-----------------------------**
select sysdate from dual;
prompt**---------------Database Details-----------------------------**
prompt
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**---------------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**---------------ASM SPACE DETAILS------------------------------**
select NAME,TOTAL_MB/1024 "Total",FREE_MB/1024 "Free",REQUIRED_MIRROR_FREE_MB/1024 "REQFORMIRROR",USABLE_FILE_MB/1024
"Usable" from v$asm_diskgroup;
prompt**---------------FRA USAGE and FREE SPACE------------------------------**
select (SPACE_LIMIT/1024/1024/1024)SPACE_LIMIT_gb,
(SPACE_USED/1024/1024/1024)SPACE_USED_gb,
(SPACE_RECLAIMABLE/1024/1024/1024)SPACE_RECLAIMABLE_gb,
((SPACE_LIMIT/1024/1024/1024)-(SPACE_USED/1024/1024/1024)+(SPACE_RECLAIMABLE/1024/1024/1024)) AVIALABLE_SPACE,
ROUND((SPACE_USED-SPACE_RECLAIMABLE)/SPACE_LIMIT*100,1)"PERCENT_FULL_OF_100%"
FROM V$RECOVERY_FILE_DEST;
prompt**--------------Monitoring Objects Created within 2 days---------------------**
select count(1) from user_objects where CREATED >= sysdate - 2;
prompt**--------------Counting Invalid object in Database---------------------------**
Select owner, object_type, count(*) from dba_objects where status='INVALID' group by owner, object_type;
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--------------------**
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--------------------------------------**
set pages 200;
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct.Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
order by "Pct.Free";
spool off;
set markup html off;
master_db_check.sh
#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export GRID_HOME=/u01/app/11.2.0/grid
export PATH=$PATH:$ORACLE_HOME/bin:.:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$GRID_HOME/bin
export TNS_ADMIN=$GRID_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data/9idata
export ORACLE_SID=TESTDB1
DBHEALTH="$(date +%d%m%y)"
mkdir /home/oracle/DBA/dailyreports/$DBHEALTH
cd /home/oracle/DBA/dailyreports/$DBHEALTH
sqlplus / as sysdba <<EOF
@/home/oracle/DBA/scripts/dailyhc.sql
exit;
EOF
rm -f $DBHEALTH.html
mv DBHEALTH.html $DBHEALTH.html
18)arch_del.sh( Archive log deletion script)
#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
ORACLE_HOME="/u01/app/oracle/db"
export ORACLE_HOME
export GRID_HOME=/u01/app/11.2.0/grid
ORACLE_SID="TESTDB"
export ORACLE_SID
ORA_NLS10="/u01/app/oracle/db/nls/data/9idata"
export ORA_NLS10
PATH="/u01/app/oracle/db/bin:/usr/ccs/bin:/usr/sbin:/u01/app/oracle/db/jdk/jre/bin:$PATH:."
export PATH
LINK_CNTRL=""
export LINK_CNTRL
TNS_ADMIN="/u01/app/oracle/db/network/admin/TESTDB_host01"
export TNS_ADMIN
cd home/oracle/DBA/logs/archdel
rman target / log="delarch_$(date +%d%m%y_%H%M%S).log" <<HERE
delete noprompt archivelog all completed before 'sysdate - 4';
crosscheck archivelog all;
list expired archivelog all;
HERE
19) locks.sql (locks on objects)
SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;
Enjoy Monitoring Databases..
Thanks,
Rafi
Below are some useful Database Health Check scripts we can use for monitoring purpose.
These SQL scripts assists us to monitor Database and diagnose in case of any issues.
1) fra_space_check.sql( Flash recovery area and DR sync)
set lines 200;
select (SPACE_LIMIT/1024/1024/1024)SPACE_LIMIT_gb,
(SPACE_USED/1024/1024/1024)SPACE_USED_gb,
(SPACE_RECLAIMABLE/1024/1024/1024)SPACE_RECLAIMABLE_gb,
((SPACE_LIMIT/1024/1024/1024)-(SPACE_USED/1024/1024/1024)+(SPACE_RECLAIMABLE/1024/1024/1024)) AVAILABLE_SPACE,
ROUND((SPACE_USED-SPACE_RECLAIMABLE)/SPACE_LIMIT*100,1)"PERCENT_FULL_OF_100%"
FROM V$RECOVERY_FILE_DEST;
select thread#,max(sequence#) from v$archived_log group by thread#;
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
2)max_tablespace_size.sql(Tablespace space check)
set lines 200 pages 300;
select TABLESPACE_NAME,sum(BYTES)/1024/1024/1024,sum(MAXBYTES)/1024/1024/1024,sum(BYTES)/1024/1024/1024*100/(sum
(MAXBYTES)/1024/1024/1024) "Used%" from dba_data_files where AUTOEXTENSIBLE='YES' group by TABLESPACE_NAME order by 4;
3)longops_session.sql( long running operations in Database)
set linesize 180
col opname for a30
col username for a15
SELECT SID, SERIAL#, username,to_char(start_time, 'dd Mon, yyyy hh24:mi:ss') as start_time, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;
4)temp_space_check.sql(For Temporary tablespace space check)
col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';
5)events_details.sql(Wait Events monitoring)
set pages 300;
set lines 200;
col event for a50;
col username for a10;
select s.sid ,username,status ,s.module,s.sql_id,w.event,w.SECONDS_IN_WAIT,w.STATE from v$session_wait w,v$session s
where w.sid=s.sid and w.EVENT <> 'SQL*Net message from client' and username is not null and w.event <> 'Streams AQ:
waiting for messages in the queue';
6)log_switches_info.sql(Log switches in a day)
set lines 250;
set pages 200;
select to_char(first_time,'DD-MON-YYYY') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" from v$log_history group by to_char(first_time,'DD-
MON-YYYY') order by 1;
7)logon_sesion.sql( Logon time for a session in Database)
select sid,serial#,to_char(logon_time,'DD-MON-YYYY:HH24:MI:SS'),program,module from v$session where sid=&1;
8)sessinfo_db.sql( Session information in Database)
select sid,serial#,program from v$session where sid=&1;
9)plan_query.sql( To get execution plan display of a query by providing sql_id from v$sql)
set lines 200;
set pages 1000;
select * from table(dbms_xplan.display_cursor('&1'));
10)sync_prod_db.sql(To check max sequence number in the primary database)
select thread#,max(sequence#) from v$archived_log group by thread#;
11)tablespace_space.sql( To get tablespace details type and space usage in Database)
set linesize 180
set pagesize 100
col "Name" for a30
SELECT d.tablespace_name "Name", d.contents "Type", d.status "Status",TO_CHAR(NVL(a.bytes / 1024 / 1024,
0),'99G999G990D90')
"Total Size (M)", TO_CHAR(NVL(NVL(f.bytes, 0),0)/1024/1024, '99G999G990D90')
"Free (MB)", TO_CHAR(NVL((NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00')
"Free %" 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_free_space group by tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) order by "Free %";
12)drsync_check.sql( Check the applied and unapplied archives on the Standby Database side)
select sequence# from v$archived_log where applied='NO';
select max(sequence#) from v$archived_log where applied='YES';
exit;
/
13)invalids.sql( To count the invalids objects in the Database)
select count(*) from dba_objects where status='INVALID';
14) lock_session.sql( To find the locking session details in the Database)
set linesize 180
set pagesize 10000
col sql_fulltext for a100
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 ;
15) Temp_usage.sql( To find the temporary tablespace usage details)
SELECT A.tablespace_name tablespace,
D.mb_total,
SUM(A.used_blocks * D.block_size) / 1024/1024 mb_used,
D.mb_total - SUM(A.used_blocks * D.block_size) / 1024/1024 mb_free
FROM v$sort_segment A,
(SELECT B.name, C.block_size, SUM(C.bytes) / 1024/1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts# = C.ts#
GROUP BY B.name, C.block_size) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
16) master_db_hc.sql( Master Health check script for Database,creates DBHEALTH.html report)
set feedback off;
set markup html on spool on;
spool DBHEALTH.html;
set termout off;
prompt ************ DAILY HEALTH CHECK SCRIPT DB************
prompt ************ SCRIPT FOR DB: RAFI ALVI ************
prompt
prompt**---------------Time-----------------------------**
select sysdate from dual;
prompt**---------------Database Details-----------------------------**
prompt
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**---------------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**---------------ASM SPACE DETAILS------------------------------**
select NAME,TOTAL_MB/1024 "Total",FREE_MB/1024 "Free",REQUIRED_MIRROR_FREE_MB/1024 "REQFORMIRROR",USABLE_FILE_MB/1024
"Usable" from v$asm_diskgroup;
prompt**---------------FRA USAGE and FREE SPACE------------------------------**
select (SPACE_LIMIT/1024/1024/1024)SPACE_LIMIT_gb,
(SPACE_USED/1024/1024/1024)SPACE_USED_gb,
(SPACE_RECLAIMABLE/1024/1024/1024)SPACE_RECLAIMABLE_gb,
((SPACE_LIMIT/1024/1024/1024)-(SPACE_USED/1024/1024/1024)+(SPACE_RECLAIMABLE/1024/1024/1024)) AVIALABLE_SPACE,
ROUND((SPACE_USED-SPACE_RECLAIMABLE)/SPACE_LIMIT*100,1)"PERCENT_FULL_OF_100%"
FROM V$RECOVERY_FILE_DEST;
prompt**--------------Monitoring Objects Created within 2 days---------------------**
select count(1) from user_objects where CREATED >= sysdate - 2;
prompt**--------------Counting Invalid object in Database---------------------------**
Select owner, object_type, count(*) from dba_objects where status='INVALID' group by owner, object_type;
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--------------------**
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--------------------------------------**
set pages 200;
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct.Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
order by "Pct.Free";
spool off;
set markup html off;
master_db_check.sh
#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export GRID_HOME=/u01/app/11.2.0/grid
export PATH=$PATH:$ORACLE_HOME/bin:.:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$GRID_HOME/bin
export TNS_ADMIN=$GRID_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data/9idata
export ORACLE_SID=TESTDB1
DBHEALTH="$(date +%d%m%y)"
mkdir /home/oracle/DBA/dailyreports/$DBHEALTH
cd /home/oracle/DBA/dailyreports/$DBHEALTH
sqlplus / as sysdba <<EOF
@/home/oracle/DBA/scripts/dailyhc.sql
exit;
EOF
rm -f $DBHEALTH.html
mv DBHEALTH.html $DBHEALTH.html
18)arch_del.sh( Archive log deletion script)
#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
ORACLE_HOME="/u01/app/oracle/db"
export ORACLE_HOME
export GRID_HOME=/u01/app/11.2.0/grid
ORACLE_SID="TESTDB"
export ORACLE_SID
ORA_NLS10="/u01/app/oracle/db/nls/data/9idata"
export ORA_NLS10
PATH="/u01/app/oracle/db/bin:/usr/ccs/bin:/usr/sbin:/u01/app/oracle/db/jdk/jre/bin:$PATH:."
export PATH
LINK_CNTRL=""
export LINK_CNTRL
TNS_ADMIN="/u01/app/oracle/db/network/admin/TESTDB_host01"
export TNS_ADMIN
cd home/oracle/DBA/logs/archdel
rman target / log="delarch_$(date +%d%m%y_%H%M%S).log" <<HERE
delete noprompt archivelog all completed before 'sysdate - 4';
crosscheck archivelog all;
list expired archivelog all;
HERE
19) locks.sql (locks on objects)
SELECT o.owner, o.object_name, o.object_type, o.last_ddl_time, o.status, l.session_id, l.oracle_username, l.locked_mode
FROM dba_objects o, gv$locked_object l
WHERE o.object_id = l.object_id;
Enjoy Monitoring Databases..
Thanks,
Rafi
