oracle数据库维护-巡检脚本

最新推荐文章于 2024-08-07 08:45:00 发布

执笔画情ora

最新推荐文章于 2024-08-07 08:45:00 发布

阅读量536

点赞数

分类专栏： ORACLE常用脚本

本文链接：https://blog.csdn.net/oradbm/article/details/103290245

版权

ORACLE常用脚本专栏收录该内容

10 篇文章 1 订阅

订阅专栏

###################################crontab.cr######################################
crontab -e
0 5,16 * * * ksh /orcl/oracle/oraclecheck/checkdb.sh
###################################checkdb.sh######################################
./export/home/oracle1/.profile
hour=`date +'%y.%m.%d.%H00.dat'`
hostname=`hostname`
ipaddr="192.168.1.1"
sid=$ORACLE_SID
/orcl/oracle/oraclecheck/oracle10g_check.sh >> /orcl/oracle/oraclecheck/checkresult/${hostname}_${ipaddr}_${sid}_$hour
FTP_SERVER="10.2.7.5 2121"
FTP_USER=day
FTP_PASS=PASSW
FTP_DIR=OracleCheck

FTP_RESPONSE=`ftp -n -v<<EOF 2>&1
open $FTP_SERVER
user $FTP_USER $FTP_PASS
lcd /orcl/oracle/oraclecheck/checkresult
put ${hostname}_${ipaddr}_${sid}_$hour
bye
EOF`
#################################oracle10g_check.sh####################################
df -h
du -sh *
sar 1 5
vmstat 1 5
echo "Monitoring dir size"

cd /orcl/oracle/admin/orcl/adump

rm orcl_*.aud
cd /orcl/oracle/diag/rdbms/tjorcl/tjorcl
du -sh *
cd /orcl/oracle/diag/tnslsnr/tjwgytm8k1d4/listener_tjorcl/trace
echo "du -sh *"
du -sh *
cd $ORACLE_BASE/admin
cd /orcl/oracle/diag/rdbms/tjorcl/tjorcl/trace
du -sh alert_$ORACLE_SID.log
tail -600 alert_$ORACLE_SID.log|grep ORA-
echo "TRC count"
ls -lrt|grep "tjorcl_"|wc -l
sqlplus "/as sysdba" <<EOF
col status format a25
@/orcl/oracle/oraclecheck/oracle10g_check.sql
exit;
EOF

cd /ogg/ggs
ggsci <<eof
info all
info dpe_a
info dpe_b
info dpe_c
exit
eof

############################################ oracle10g_check.sql ##########################
--spool /tmp/teamsun/oracle_check.log
--Oracleʵ̬
set linesize 200
--Oracleʵ̬
select 'Oracleʵ̬' from dual;
SELECT instance_name,version,startup_time,status FROM v$instance;
--
select 'Oracle' from dual;
select owner,table_name,index_name,status from dba_indexes where status='UNUSABLE';
select '---PARTITION INDEX STATUS' from dual;
select index_owner,index_name,partition_name,status from dba_ind_partitions where status='UNUSABLE';
--Oracle
select 'Oracle' from dual;
select 'process message' from dual;
col Para_process for a30
set lines 100
col machine for a20
col client_info for a20
select (select value from v$parameter where name='processes') Para_process,(select count(1) from v$process) P_VALUE FROM DUAL
;
select * from (
select machine,client_info,status,count(1) from v$session where status='INACTIVE' group by machine,client_info,status order
by count(1) desc ) where rownum<11
union all
select * from (
select machine,client_info,status,count(1) from v$session where status='ACTIVE' group by machine,client_info,status order by
count(1) desc ) where rownum<11;
select 'Event-status' from dual;
set lines 120
col event for a30
select event,count(1) from v$session where wait_class#<>6 group by event order by count(1) asc;
--Oracle
select 'Oracle' from dual;
set lines 300
col Status format a10
col "Name" format a15
col "Type" format a10
col "Used %" format a10
col "Size (G)" format a15
col "Used (G)" format a15
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024/1024, 0), '99,999,990.900') "Size (G)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024/1024,
'99999999.999') || '/' ||
TO_CHAR(NVL(f.bytes / 1024 / 1024/1024, 0), '99999999.999') "Used (G) Free(G)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),
'990.00') "Used %"
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(+)
AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024/1024, 0), '99,999,990.900') "Size (G)",
TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024/1024, '99999999.999') || '/' ||
TO_CHAR(NVL(a.bytes / 1024 / 1024/1024, 0), '99999999.999') "Used (G)",
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'
order by "Used %" desc;
--
select 'Oracle Schema' from dual;
col username format a15
col account_status format a20
col lock_date format a25
col expiry_date format a25
select username,account_status,lock_date,expiry_date from dba_users where account_status='EXPIRED(GRACE)';
--RMAN
select 'RMAN' from dual;
col backup_name format a30
col start_time format a20
col elapsed_time format a20
col status format a10
col input_type format a10
col output_device_type format a10
col input_size format a10
col output_size format a10
col output_rate_per_sec format a10
SELECT r.command_id as "backup_name",
TO_CHAR(r.start_time, 'YYYY-MM-DD HH24:MI:SS') as "start_time",
r.time_taken_display as "elapsed_time",
DECODE(r.status,
'COMPLETED',
r.status,
'RUNNING',
r.status,
'FAILED',
r.status,
r.status) as "status",
r.input_type as "input_type",
r.output_device_type as "output_device_type",
r.input_bytes_display as "input_size",
r.output_bytes_display as "output_size",
r.output_bytes_per_sec_display as "output_rate_per_sec"
FROM (select command_id,
start_time,
time_taken_display,
status,
input_type,
output_device_type,
input_bytes_display,
output_bytes_display,
output_bytes_per_sec_display
from v$rman_backup_job_details
order by start_time DESC) r
WHERE to_char(r.start_time, 'yyyymmdd') >
to_char(sysdate - 2, 'yyyymmdd');
--spool off
bash-3.00$