oracle@test01:~> sqlplus -S -M "HTML ON" test/test@test01 @object.sql> object1.html
oracle@test01:~> cat object.sql
select count(*),object_type,status from dba_objects where owner='test' group by status,object_type;
select distinct tablespace_name from dba_segmENts where owner='test';
SET MARKUP HTML OFF
exit
oracle@test01:~> sqlplus -M "HTML ON" test/test@test01 @object.sql> object1.html | mail -a object.html -s "html logs" TEST@example.com
oracle@test01:~> sqlplus -M "HTML ON" test/test@test01 @object.sql | mail -s "html logs" TEST@example.com < object.html
oracle@test01:~>
oracle@test01:~> sqlplus -M "HTML ON" test/test@test01 @object.sql> object1.html | mail -a object.html -s "html logs" varun.yadav@examl
SET MARKUP HTML ON SPOOL ON
SET NULL 'NO ROWS SELECTED'
set tab off
-- SET HEADING ON
-- SET PAGESIZE 1000
-- SET WRAP OFF
SET LINESIZE 1000
SET FEEDBACK OFF
SET NEWPAGE NONE
SET TRIMS OFF
SET NUMWIDTH 50
-- SET TIMING ON
SET AUTOCOMMIT OFF
SPOOL Tablespace.html
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;
SET MARKUP HTML OFF
SPOOL OFF
exit;
######################
oracle@test01:~> cat /u04/MasterDB/scripts/weekly_health_check/Weekly_healthcheck.sh
. /home/oracle/.bash_profile
DR_log=/u04/MasterDB/scripts/weekly_health_check/SMRWeeklyreport.log
printf "to:TEST@example.com\n" > $DR_log
\n" >> $DR_log
printf "subject: Weekly SMRPRODB Database Healthcheck Details " >> $DR_log
#sqlplus -s "/as sysdba" <<EOF >> $DR_log
sqlplus -s "/as sysdba" <<EOF >> $DR_log
set serverout on
set wrap off
set pages 300
set lines 200
col file_name for a50
col name for a50
col member for a50
col file_id for a5
col "Percent Used" for a20
col segment_name for a30
col tablespace_name for a60
col STATUS for a16
col owner for a20
col table_name for a35
col index_name for a35
col owner for a20
col object_type for a20
col object_name for a30
#column date_column new_value today_var
/
select to_char(sysdate,'yyyy-Mon-dd_hh-miAM')date_column from dual
/
spool /u04/MasterDB/scripts/daily_health_check/SMRPROD_&today_var.txt
PROMPT =============================================================
PROMPT DATABASE HEALTH CHECK REPORT
PROMPT =============================================================
PROMPT CURRENT DATE and TIME
PROMPT ======================
Select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "Current Date/Time" from dual;
PROMPT
PROMPT DATABASE NAME
PROMPT =================
set lines 200
select name, instance_name,db_unique_name, open_mode, database_role, flashback_on current_scn,to_char(standby_became_primary_scn) failover_scn from v$database,v$instance;
PROMPT
PROMPT Users logged information
PROMPT ===============================
select status,count(*) from v$session
--where username not in ('SYS','SYSTEM')
group by status;
PROMPT
PROMPT Previous day archive logs generation
PROMPT ======================================
select count(*) from v$archived_log
where to_char(COMPLETION_TIME,'DD-Mon-YY')=to_char(sysdate-1,'DD-Mon-YY');
PROMPT
PROMPT latest Archive No.
PROMPT ======================================
archive log list
PROMPT
PROMPT CONTROL FILES
PROMPT ==================
select * from v$controlfile;
PROMPT
PROMPT LOG FILE GROUPS
PROMPT ==================
set lines 200
col status for a7
col type for a7
col member for a60
col IS_RECOVERY_DEST_FILE for a3
select GROUP#,STATUS,TYPE,MEMBER,IS_RECOVERY_DEST_FILE from v$logfile;
PROMPT
PROMPT
PROMPT LOG FILE MEMBERS
PROMPT ==================
select * from v$log;
PROMPT
PROMPT SIZE OF DATABASE Daily
PROMPT ==================
SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a17
COL "Used in %" FORMAT a17
COL "Free in %" FORMAT a17
COL "Database Name" FORMAT a17
COL "Free Space" FORMAT a17
COL "Growth DAY" FORMAT a17
COL "Growth WEEK" FORMAT a17
COL "Growth DAY in %" FORMAT a17
COL "Growth WEEK in %" FORMAT a17
SELECT
(select min(creation_time) from v\$datafile) "Created",
(select name from v$database) "DB Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "F
ree in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3)
|| '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v\$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*
7,3) || '% MB' "Growth WEEK in %"
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
PROMPT SIZE OF DATABASE
PROMPT ==================
select 'Data files',sum(bytes)/1024/1024/1024 from dba_data_files
union
select 'Temp Files',sum(bytes)/1024/1024/1024 from dba_temp_files
union
select 'Log files',sum(bytes*members)/1024/1024/1024 from v\$log
union
select 'segments',sum(bytes)/1024/1024/1024 from dba_segments
union
select 'free space',sum(bytes)/1024/1024/1024 from dba_free_space;
PROMPT
PROMPT TABLESPACES AND DATAFILES
PROMPT ============================
col TABLESPACE_NAME for a20
col file_id for a7
col TABLESPACE_NAME for a30
col file_name for a60
col STATUS for a9
select TABLESPACE_NAME, to_char(file_id, '999') "File_id", FILE_NAME, BYTES/1024/1024 "Size in MB" , STATUS
from dba_data_files
order by TABLESPACE_NAME, file_name;
PROMPT
PROMPT DATAFILES Details in AutoExtend Mode
PROMPT ====================================
set lines 200
col FILE_NAME for a60
col tablespace_name for a30
col AUTOEXTENSIBLE for a3
col bytes for a10
select file_name,tablespace_name,AUTOEXTENSIBLE,bytes/1024/1024/1024 "SIZE IN GB " from dba_data_files
where AUTOEXTENSIBLE='YES' order by tablespace_name;
PROMPT
PROMPT UTILIZATION OF TABLESPACES
PROMPT ============================
set lines 200
COLUMN TABLESPACE FORMAT A15
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
order by t.tablespace;
PROMPT
PROMPT TABLESPACES utl >85
PROMPT ============================
COLUMN TABLESPACE FORMAT A15
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
and round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) >=85
order by t.tablespace
/
PROMPT OBJECTS WHOSE STATUS ARE INVALID
PROMPT ===================================
Rem Check object status
select OBJECT_NAME, owner, object_type, STATUS from all_objects where
object_type in
('FUNCTION','INDEX', 'LIBRARY','PACKAGE','PACKAGE BODY',
'PROCEDURE', 'SEQUENCE','SYNONYM','TABLE','TRIGGER',
'TYPE','UNDEFINED','VIEW')
and status = 'INVALID'
and OWNER not in ('SYS','SYSTEM')
and status is not null ;
PROMPT FILES THAT NEEDS RECOVERY
PROMPT =============================
Rem Check wether there is any data file that needs media recovery
select * from v$recover_file;
PROMPT
PROMPT
PROMPT
PROMPT Database users detail
PROMPT =====================
set lines 200
col username for a25
col account_status for a10
col default_tablespace for a20
select username,account_status,default_tablespace from dba_users;
PROMPT
PROMPT
PROMPT Last Modified Tables Details
PROMPT =====================
set lines 500;
col TABLE_OWNER for a30
col TABLE_NAME for a40
col TIMESTAMP for a70
select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES,
to_char(TIMESTAMP,'YYYY-MON-DD HH24:MI:SS')
from all_tab_modifications
where table_owner<>'SYS' and
EXTRACT(YEAR FROM TO_DATE(TIMESTAMP, 'DD-MON-RR')) > 2017
order by 6;
PROMPT
PROMPT
PROMPT Total Objects
PROMPT =========================
set lines 140
col owner for a30
select owner as "SCHEMA",count(*) from dba_objects
group by owner;
PROMPT
PROMPT Archive Log generation History for Last one month
PROMPT ==================================================
set lines 200
SELECT to_date(first_time) DAY, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from v$log_history where to_date(first_time) > sysdate -30 GROUP by to_char(first_time,'YYYY-MON-DD'),
to_date(first_time) order by to_date(first_time)
;
PROMPT
PROMPT
PROMPT dba_jobs assinged
PROMPT ==================================================
set lines 100 pages 999
col schema_user format a15
col fails format 999
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
order by 4
/
PROMPT
PROMPT
PROMPT List all materialized view logs
PROMPT ==================================================
select log_owner,log_table from dba_mview_logs
/
PROMPT
PROMPT
PROMPT Show all materialized and resfresh times
PROMPT ==================================================
set lines 100 pages 999
col last_refresh format a20
select owner,mview_name,to_char(last_refresh_date,'dd/mm/yy hh24:mi') last_refresh
from dba_mviews
order by owner, last_refresh
/
PROMPT
PROMPT
PROMPT Show materialized view tables and masters
PROMPT ==================================================
set lines 100
col mview format a40
col master format a40
select owner || '.' || name mview
, master_owner || '.' || master master
from dba_mview_refresh_times
/
PROMPT
PROMPT
PROMPT This is useful for spotting failures
PROMPT ==================================================
set lines 100
col job format 9999
col log_user format a15
col last format a15
col next format a15
col fail format 9999
col what format a20
select job
, log_user
, to_char(last_date, 'dd/mm/yy hh24:mi') last
, to_char(next_date, 'dd/mm/yy hh24:mi') next
, failures fail
, replace(what, '"') what
from dba_jobs
where what like '%dbms_refresh.refresh%'
/
prompt ========================================================================
prompt Rman Backup details
prompt ========================================================================
set lines 200
col START_TIME for a15
col END_TIME for a15
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY heading "DATA SIZE" for a10
col OUTPUT_BYTES_DISPLAY heading "Backup Size" for a11
col OUTPUT_BYTES_PER_SEC_DISPLAY heading "Speed/s" for a10
col output_device_type heading "Device_TYPE" for a11
SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME, to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,
input_type, output_device_type,input_bytes_display, output_bytes_display, output_bytes_per_sec_display ,COMPRESSION_RATIO
FROM v$rman_backup_job_details
WHERE end_time > sysdate -3;
prompt ========================================================================
prompt MOUNTPOINT DETAILS
prompt ========================================================================
!df -h /u01
!df -h /u02
!df -h /u03
!df -h /u04
prompt ========================================================================
prompt Archive Mountpoint space details
prompt ========================================================================
!df -h /u04/MasterDB/oradata/SMRPRODB/ArchiveLog
prompt ========================================================================
prompt Total size of archive Today
prompt ========================================================================
!du -sh /u04/MasterDB/oradata/SMRPRODB/ArchiveLog
prompt ========================================================================
prompt DR SYNC STATUS
prompt ========================================================================
set heading off trimspool on
select maxSCN AS PRIMARY
from (select max(sequence#) almax
from v\$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database where THREAD#=1)
) al,
(select max(sequence#) maxSCN
from v\$log_history
where first_time=(select max(first_time) from v$log_history where THREAD#=1)
) lh;
/
EOF
/usr/sbin/sendmail -t < $DR_log
oracle@test01:~>
No comments:
Post a Comment