[oracle@Prod]$ crontab -l
51 19 * * * /home/oracle/ORACLE_HEALTH_REPORT/Health_CHECK.sh
> /home/oracle/ORACLE_HEALTH_REPORT/Check_Health_`date
+\%d-\%b-\%Y_\%H_\%M_\%S`.log
[oracle@Prod]$
vi
/home/oracle/ORACLE_HEALTH_REPORT/Health_CHECK.sh
ORACLE_HOSTNAME=hostname;
export ORACLE_HOSTNAME
ORACLE_UNQNAME=oracle;
export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle;
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1;
export ORACLE_HOME
ORACLE_SID=oracle;
export ORACLE_SID
PATH=/usr/sbin:$PATH;
export PATH
PATH=$ORACLE_HOME/bin:$PATH;
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
export CLASSPATH
#!/bin/ksh
sqlplus
/nolog << EOF
CONNECT / as
sysdba
@/home/oracle/ORACLE_HEALTH_REPORT/DB_Health_Report.sql
EXIT;
EOF
echo
"TEST" | mail -s "Database Health Report" -a
/home/oracle/ORACLE_HEALTH_REPORT/`ls -lrt /home/oracle/ORACLE_HEALTH_REPORT/ |
grep "HEALTH_REPORT_ORACLE" | tail -1 | awk '{print $9}'` oracleforensic4u@gmail.com
[oracle@ashwanik ~]$ cat /home/oracle/ORACLE_HEALTH_REPORT/DB_Health_Report.sql
set pages 200 lines 172
col dat1 NEW_VALUE v_start_time
select to_char(sysdate,'YYYY-MM-DD_HH24MISS')
dat1 from dual;
select name||'_'||to_char(sysdate,'DD-MM-YYYY_HH24_MI_SS')
dat1 from v$database;
spool
/home/oracle/ORACLE_HEALTH_REPORT/HEALTH_REPORT_&v_start_time..txt
prompt ################### Database Status
#############################################
select dbid,name,open_mode,database_role,protection_mode
from v$database;
select
INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,DATABASE_STATUS,INSTANCE_ROLE,ACTIVE_STATE
from v$instance;
select status,count(1) from v$session group by
status order by 2 desc;
prompt ################### Database Size
################################################
select sum(bytes)/1024/1024||' MB' as Total_Size
from dba_data_files;
select sum(bytes)/1024/1024||' MB' as USED_Size
from dba_segments;
prompt ################### Count of Invalid
Objects ###########################################
select owner,object_type,count(1) from
dba_objects where status='INVALID' group by owner,object_type order by
owner,object_type;
prompt ####################### Tablespace Usage
Report ##############################################
col "NAME" format a30
set lines
140
set pages
1000
select
a.tablespace_name "NAME",
round((b.totalspace - a.freespace),1)"USED_SPACE_MB",
round(a.freespace,1) "FREE_SPACE_MB",
round(b.totalspace) "TOTAL_SPACE_MB",
round(100 * (a.freespace / b.totalspace)) "%_FREE"
from
( select
tablespace_name,
sum(bytes)/1024/1024 TotalSpace
from
dba_data_files
group
by tablespace_name) b,
( select
tablespace_name,
sum(bytes)/1024/1024 FreeSpace
from
dba_free_space
group
by tablespace_name) a
where
b.tablespace_name = a.tablespace_name(+)
order by
5;
prompt ################################
TEMPSPACE USAGE ###################################################
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;
prompt ################################# UNDO
USAGE ########################################################
select a.tablespace_name, SIZEMB, USAGEMB,
(SIZEMB - USAGEMB) FREEMB
from (select sum(bytes) / 1024 / 1024 SIZEMB,
b.tablespace_name
from dba_data_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.contents = 'UNDO'
group by b.tablespace_name) a,
(select c.tablespace_name, sum(bytes) / 1024 /
1024 USAGEMB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
group by c.tablespace_name) b
where a.tablespace_name = b.tablespace_name;
select status,
round(sum_bytes / (1024*1024), 0) as MB,
round((sum_bytes / undo_size) * 100, 0) as PERC
from
(
select
status, sum(bytes) sum_bytes
from
dba_undo_extents
group by
status
),
(
select
sum(a.bytes) undo_size
from
dba_tablespaces c
join
v$tablespace b on b.name = c.tablespace_name
join
v$datafile a on a.ts# = b.ts#
where
c.contents = 'UNDO'
and
c.status = 'ONLINE'
);
prompt ################################## RMAN
BACKUP JOB COMPLETION ##################################################
set pages 500
col INPUT_TYPE Format a15
col status format a15
col OPTIMIZED format a15
col start_time format a20
col end_time format a20
col SESSION_KEY Format 99999
col elapsed_seconds format 999
select
INPUT_TYPE,
SESSION_KEY,
To_char(START_TIME,'DD-MON-YYYY HH24:MI') START_TIME,
INPUT_BYTES/1024/1024 INPUT,
STATUS,
output_bytes/1024/1024 OUTPUT,
To_Char(END_TIME,'DD-MON-YYYY HH24:MI') END_TIME,
OPTIMIZED,
ELAPSED_SECONDS/60 ELAP_MIN
FROM V$RMAN_BACKUP_JOB_DETAILS order by 2;
prompt ###################################
INVALID OBJECTS ###########################################################
select blocking_session,sql_id , sid, serial#,
wait_class, seconds_in_wait from v$session where blocking_session is not NULL
order by sql_id, blocking_session;
prompt
prompt ############################ INVALID
INDEXES ###########################################################
select owner,index_name,table_name,status,degree
from dba_indexes where status='INVALID';
select 'ALTER INDEX '||owner||'.'||index_name||'
REBUILD ONLINE PARALLEL 4;' from dba_indexes where status='INVALID';
prompt #######################################
Datafile Status
#################################################################
col file_name format a50
set pages 500 lines 165
select
file_name,bytes/1024/1024,tablespace_name,status,online_status,autoextensible,maxbytes/1024/1024
from dba_data_files order by 3;
spool off
You have mail in /var/spool/mail/oracle
No comments:
Post a Comment