Script para BAU diario

scripts: daily chek BAU.sh

#!/bin/bash
export ORACLE_SID=mxapps1
export ORACLE_HOME=/u001/MXDB/oracle/product/11.2.0/db
export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/etc/opt/emcpower/bin:/u001/MXDB/oracle/product/11.2.0/db:/u001/MXDB/oracle/product/11.2.0/db/bin
export FECHA=`date '+%d%m%Y_%H%M%S'`

$ORACLE_HOME/bin/sqlplus -s "/as sysdba" <<EOF

SET PAGES 2000 LINES 200
SET SERVEROUTPUT ON
SPOOL /home/oracle/dbascripts/daily_check/logs/dbstatus_report_mxapps1.txt;


select sysdate from dual;

PROMPT
PROMPT Checking database name and archive mode, dbid
PROMPT

column NAME format A9
column LOG_MODE format A12

SELECT NAME,CREATED, LOG_MODE, DBID FROM V\$DATABASE;



PROMPT
PROMPT ———————————————————————-|
PROMPT



PROMPT
PROMPT Checking alert log
PROMPT

set long 1000
set pagesize 0
col message_text format a400

select distinct
   originating_timestamp,
   message_text
from
   x\$dbgalertext
where
   originating_timestamp > sysdate-3
and
(
   message_text like '%ORA-%'
   or
   message_text like '%Fatal%'
);


PROMPT
PROMPT ———————————————————————-|
PROMPT




PROMPT
PROMPT Checking Tablespace name and status
PROMPT

column TABLESPACE_NAME format a30
column STATUS format a10
set pagesize 400

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking free space in tablespaces
PROMPT

column Tablespace format a30

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 Order by 4;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking free space in datafiles
PROMPT

set lines 200
col file_name format a60
col tablespace_name format a30
 
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking freespace by tablespace
PROMPT

column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a21 heading "Tablespace Name"
column bytes format 9,999,999,999,999 heading "Total Bytes"
column used format 9,999,999,999,999 heading "Used"
column free format 9,999,999,999,999 heading "Free"
break on report
compute sum of bytes on report
compute sum of free on report
compute sum of used on report

set linesize 132
set termout off
select a.tablespace_name name,
b.tablespace_name dummy,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) bytes,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ) used,
sum(a.bytes)/count( distinct b.file_id ) free,
100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking Size and usage in GB of Flash Recovery Area
PROMPT

SELECT
ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,
ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,
ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
SUM(B.PERCENT_SPACE_USED) AS PERCENT_OF_SPACE_USED
FROM
V\$RECOVERY_FILE_DEST A,
V\$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
SPACE_LIMIT,
SPACE_USED ,
SPACE_RECLAIMABLE ;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT ————————————————————————|
PROMPT


PROMPT
PROMPT Checking for 20 Largest items in Database
PROMPT

column SEGMENT_NAME format A50

SELECT * FROM (
SELECT
OWNER, SEGMENT_NAME, BYTES/1024/1024 SIZE_MB
FROM
DBA_SEGMENTS
ORDER BY
BYTES/1024/1024 DESC ) WHERE ROWNUM <= 20;

PROMPT
PROMPT ————————————————————————|
PROMPT


PROMPT
PROMPT Checking for invalid objects
PROMPT

column owner format A15
column object_name format A30 heading 'Object'
column object_id format 9999999 heading "Id#"
column object_type format A30
column status format A8

select owner, object_name, object_id, object_type, status
from dba_objects where status != 'VALID' and object_type != 'SYNONYM';

PROMPT
PROMPT ————————————————————————|

PROMPT
PROMPT How large is the database
PROMPT

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
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;

PROMPT
PROMPT ————————————————————————|
PROMPT



PROMPT
PROMPT Checking the recycle Bin
PROMPT

SELECT
OWNER, SUM(SPACE) AS TOTAL_BLOCKS
FROM
DBA_RECYCLEBIN GROUP BY OWNER
ORDER BY OWNER;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking database versions
PROMPT

column BANNER format A64
select * from v\$version;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking control file(s)
PROMPT

column STATUS format a7
column NAME format a68
column IS_RECOVERY_DEST_FILE format a3
set linesize 132

SELECT * FROM V\$CONTROLFILE;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT Checking redo logs and group(s)
PROMPT

column member format a90

SELECT group#, member FROM v\$logfile;

PROMPT
PROMPT ————————————————————————|
PROMPT

PROMPT
PROMPT rman backups check
PROMPT

set lines 200
set pages 500
col STATUS form a20
col START_TIME form a20
col END_TIME form a20
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
round((end_time-start_time)*1440,2) "Minutes"
from V\$RMAN_BACKUP_JOB_DETAILS
order by session_key desc;

SPOOL OFF

exit

EOF

mailx -s "BAU MXAPPS lmatdb01" [email protected] </home/oracle/dbascripts/daily_check/logs/dbstatus_report_mxapps1.txt

mv /home/oracle/dbascripts/daily_check/logs/dbstatus_report_mxapps1.txt /home/oracle/dbascripts/daily_check/logs/dbstatus_report_mxapps1_${FECHA}.txt