ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle日常檢查腳本

Oracle日常檢查腳本

原创 Linux操作系统 作者:hwtong 时间:2011-04-19 10:17:18 0 删除 编辑
echo "please choose a oracle sid: 1):zz  2):bb default:zz"
  read ans
  case $ans in
1)
ORACLE_SID=zz; export ORACLE_SID;;
2)            
ORACLE_SID=bb; export ORACLE_SID;;
*)     
ORACLE_SID=zz; export ORACLE_SID;;
esac          
       
echo "uptime\CPU:----------->";uptime
echo "date:----------->";date
echo "MEM:----------->";free -m
echo "process:----------->";ps -ef|wc -l
echo "user:----------->";cat /etc/passwd|wc -l
echo "Cronlog:----------->";egrep "no|err|not|bad|unsuccessfully" /data/run/log/*|grep -v "synonyms"
echo "Oralog:----------->";tail -n 1000 /home/oracle/dba/bdump$ORACLE_SID/alert_*.log|egrep -B1 -A3 -n "ORA|cannot"
echo "/data free:----------->";df -h

echo "check-dblinks:----------->";
sqlplus -S '/as sysdba' <
col OWNER format a20;
col DB_LINK format a20;
col USERNAME format a20;
col HOST format a20;
select OWNER,DB_LINK,USERNAME,HOST from dba_db_links;
exit;
!
        
echo "check-invalid-objects:----------->";
sqlplus -S '/as sysdba' <col OWNER format a20;
col OBJECT_NAME format a20;
col OBJECT_TYPE format a20;
select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where status='INVALID' and object_type <> 'SYNONYM';
exit;                       
!                          

echo "check-jobs:----------->";
sqlplus -S '/as sysdba' <col SCHEMA_USER format a20;
col LAST_DATE format a20;
select JOB,SCHEMA_USER,LAST_DATE from dba_jobs;
exit;
!     

echo "check-sessions:----------->";
sqlplus -S '/as sysdba' <col RESOURCE_NAME format a10;
col INITIAL_ALLOCATION format a15;
col LIMIT_VALUE format a15; 
select * from gv\$RESOURCE_LIMIT where RESOURCE_NAME='sessions';
exit   
!


echo "Tablespace-free:----------->";
sqlplus -S '/as sysdba' <column ts format a15;
select  a.tablespace_name ts,
       round(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
       round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
        from dba_free_space f
        group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)
order by 3;
exit
!

echo "archived log:----------->";
sqlplus -S '/as sysdba' <archive log list;
exit
!

echo "Extend-count:----------->";
sqlplus -S '/as sysdba' <set linesize 131;
col object format a40;
col type format a10;
col tsname format a10;
select a.owner||'.'||a.segment_name object,
         a.segment_type  type            ,
         a.bytes/1024/1024    NG         ,
         a.next_extent/1024/1024 Next ,
         a.tablespace_name ts_name
    from sys.dba_segments a
where a. next_extent * &num > (
                           select max(b.bytes)
                             from dba_free_space b
                            where a.tablespace_name = b.tablespace_name)
order by 3 desc
exit
!

echo "TBS_COUNT:----------->";
sqlplus -S '/as sysdba' <select round(sum(a.bytes_alloc)/1024/1024/1024,2)  "TBS_ALLOCT_TOTAL(G)",
       round(   (sum(a.bytes_alloc)-sum(nvl(b.bytes_free, 0)))/1024/1024/1024,3)  "TBS_USED_TOTAL(G)"
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc
             from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
         from dba_free_space f
        group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+);
exit
!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16754036/viewspace-692825/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-07-06

  • 博文量
    116
  • 访问量
    286230