2014년 10월 13일 월요일

Oracle Alert Script(테이블, 테이블스페이스)[오라클개발자교육/오라클/ORACLE강좌/오라클교육잘하는곳/오라클교육추천/ORACLE실무교육/ORACLE/ORACLE교육/ORACLE학원/오라클실무교육]

Oracle Alert Script(테이블, 테이블스페이스) 

-------------------------------------------------------------------- 
oracheck.run – This is a Korn shell script that executes the oracheck.ksh script and e-mail you if a problem is detected. The idea behind this script is to allow the DBA to repair impending problems before that database crashes.  Note that the script below redirects the output to a file, checks the number of lines in the output file, and e-mails an alert message if problems are found. 
-------------------------------------------------------------------- 

#! /bin/ksh 
  
# Here we write a blank line to the log file . . . 
echo `date` > /oracle/my_sid/scripts/oracheck.log 
  
# Now we run the check, writing errors to the oracheck.log file 
/oracle/MY_SID/scripts/oracheck.ksh >> \ 
/oracle/MY_SID/scripts/oracheck.log 
  
# If errors messages exist (2 or more lines), then go on . . . 
if [ `cat /oracle/MY_SID/scripts/oracheck.log|wc -l` -gt 1 ] 
then 
    # Now, be sure that we don't clog the mailbox. 
    # the following statement checks to look for existing mail, 
    # and only sends mail when mailbox is empty . . .  
    if [ ! -s /var/spool/mail/oramy_sid ] 
    then 
      cat /oracle/MY_SID/scripts/oracheck.log | mail oramy_sid  
    fi 
fi 
  
  
-------------------------------------------------------------------- 
oracheck.ksh – This is a Korn shell script that does the queries against Oracle and the Oracle environment.  Note that this script loops through all of the databases on the server (using /etc/oratab) and reports any exceptional conditions for all databases. 
-------------------------------------------------------------------- 
  
  
#!/bin/ksh 
  
set PATH=$PATH:/oracle/MY_SID/bin 
  
ORACLE_SID=MY_SID; 
export ORACLE_SID; 
  
#ORAENV_ASK=NO 
#export ORAENV_ASK; 
#/oracle/MY_SID/bin/oraenv 
  
ORACLE_BASE=/oracle/MY_SID; export ORACLE_BASE; 
ORACLE_HOME=/oracle/MY_SID; export ORACLE_HOME; 
  
set $pwd=system/scion; export pwd; 
ALERT_DIR=/oracle/MY_SID/saptrace/background; export ALERT_DIR; 
ALERT_DIR_MOUNTPOINT=/oracle/MY_SID; export ALERT_DIR_MOUNTPOINT; 
REDO_LOG_FILESYSTEM=/oracle/MY_SID/saparch; export REDO_LOG_FILESYSTEM; 
  
#************************************************************** 
#  Check if checknet2 deamon is running . . . . 
#************************************************************** 
  
#oracle_up=`ps -ef|grep checknet2|grep -v grep|wc -l`; 
#oracle_num=`expr $oracle_up` 
  
#if [ $oracle_num -lt 1 ] 
# then echo "checknet2 deamon is NOT up." 
#fi 
  
#*********************************************************************** 
#  Here is where we loop through each SID in /etc/oratab . . . 
#*********************************************************************** 
  
#for ORACLE_SID in `cat /etc/oratab | grep :N|cut -d":" -f1` 
#do 
  
  
#************************************************************** 
#  Check if Oracle is up . . . . 
#************************************************************** 
  
oracle_up=`ps -ef|grep pmon_$ORACLE_SID|grep -v grep|wc -l`; 
oracle_num=`expr $oracle_up` 
  
if [ $oracle_num -lt 1 ] 
 then echo "$ORACLE_SID instance is NOT up." 
fi 
  
#*********************************************************** 
# Check alert log for ORA-600 
#*********************************************************** 
  
tail -400 $ALERT_DIR/alert_$ORACLE_SID.log|grep ORA-00600 
  
#*********************************************************** 
# Check redo log file-system > 90% full 
#*********************************************************** 
  
redo_log=`df -k|grep $REDO_LOG_FILESYSTEM|awk '{print $4}'|cut -d"%" -f1` 
oracle_num=`expr $redo_log` 
  
if [ $oracle_num -gt 90 ] 
 then logger "REV_ORA_030W  $ORACLE_SID redo log file system is > 90%.  Please " 
 echo "$ORACLE_SID redo log file system is > 90%." 
fi 
  
  
  # Invoke SQL*Plus to generate DB space info. 
  /oracle/MY_SID/bin/sqlplus -s << UNTIL_DONE 
  $pwd 
  
  
SET ECHO OFF; 
SET TERM OFF; 
SET TIMING OFF; 
SET HEAD OFF; 
SET FEED OFF; 
  
CREATE TABLE oracheck_fs_temp (tablespace_name,total_bytes,free_bytes,max_chunk) 
  AS 
  SELECT tablespace_name, NVL(SUM(bytes), 1), 1, 1 
  FROM dba_data_files 
  GROUP BY tablespace_name; 
  
  UPDATE oracheck_fs_temp a 
  SET a.free_bytes = (SELECT NVL(SUM(b.bytes), 1) 
                      FROM dba_free_space b 
                      WHERE b.tablespace_name = a.tablespace_name); 
  COMMIT; 
  UPDATE oracheck_fs_temp a 
  SET a.max_chunk = (SELECT NVL(MAX(b.bytes), 1) 
                    FROM dba_free_space b 
                    WHERE b.tablespace_name = a.tablespace_name); 
  COMMIT; 
  
  REM **************************************************************** 
  REM Tablespaces more than 95% full. 
  REM **************************************************************** 
  
  SELECT 
        tablespace_name                                    || 
        ' is '                                              || 
        TO_CHAR(ROUND(100-(free_bytes*100/total_bytes), 2)) || 
        '% full.' T 
  FROM oracheck_fs_temp 
  WHERE 95 < 100-(free_bytes*100/total_bytes) 
  ORDER BY tablespace_name; 
  
  REM **************************************************************** 
  REM Tablespaces/objects with insufficient space for NEXT extent. 
  REM **************************************************************** 
  
  SELECT  
        SUBSTR(a.tablespace_name, 1, 21) tablespace , 
        SUBSTR(a.owner          , 1, 16) owner      , 
        SUBSTR(a.segment_name  , 1, 30) object_name, 
        SUBSTR(a.segment_type  , 1, 8 ) what 
  FROM dba_segments a 
  WHERE a.segment_type IN ('TABLE', 'INDEX', 'ROLLBACK') 
  AND  NVL(a.next_extent, 1) > (SELECT b.max_chunk 
                                FROM oracheck_fs_temp b 
                                WHERE b.tablespace_name = a.tablespace_name) 
  ORDER BY 1,2,4,3; 
  
  DROP TABLE oracheck_fs_temp; 
  
  REM ***************************************************************** 
  REM Objects with more than 600 extents. 
  REM ***************************************************************** 
  REM Applied NVL function purposely to extents column. 
  SELECT SUBSTR(owner      , 1, 22) owner      , 
        SUBSTR(segment_name, 1, 30) object_name, 
        SUBSTR(segment_type, 1, 8 ) type      , 
        extents 
  FROM dba_segments 
  WHERE 600 < NVL(extents, 1) 
  AND  segment_type IN ('TABLE', 'INDEX', 'ROLLBACK') 
  ORDER BY 1,3,4 DESC,2; 
  
  EXIT; 
UNTIL_DONE 
  
#done 
출처 : www.remote_dba.cc 


 기업100%환급/오라클/자바/스프링/안드로이드/닷넷C#/웹퍼블리싱… 오라클자바…12-272156
 [채용예정교육]오라클자바개발잘하는신입뽑기2개월과정,교육전취…오라클자바…12-111556
53 [평일주간]100%환급,안드로이드,자바,C#,스프링3.2,SQL,힌트/튜… 오라클자바…03-151347
52 [주말]C#,ASP.NET마스터 오라클자바…01-311463
51 [기업100%환급,평일주간]SQL기초에서스키마오브젝트,PLSQL,힌트… 오라클자바…01-311532
50 [기업100%환급]Spring ,MyBatis,Hibernate실무과정(스프링개발자… 오라클자바…01-311167
49 [평일주간,평일야간,주말]Spring,MyBatis,Hibernate개발자과정 오라클자바…01-191471
48 [평일주간,평일야간,주말]안드로이드개발자과정 오라클자바…01-111297
47 [주말주간,주말야간]JAVA,Network&JSP&Spring,MyBatis,Hibernate 오라클자바…01-031799
46 기업100%환급/오라클/자바/스프링/안드로이드/닷넷C#/웹퍼블리싱… 오라클자바…12-272156
45 [기업100%환급]자바웹개발기초과정(JAVA,JDBC,JSP,Servlet,Aajx,… 오라클자바…12-191561
44 [평일야간, 주말]웹퍼블리싱 마스터(HTML5,CSS3,jQUERY,AJAX,Jav… 오라클자바…12-141537
43 [채용예정교육]오라클자바개발잘하는신입뽑기2개월과정,교육전취… 오라클자바…12-111556
42 [주말, 평일야간]자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBati… 오라클자바…12-091247
41 [평일야간]닷넷(C#,Network,ADO.NET,ASP.NET)마스터 오라클자바…12-011470
40 [기업100%환급]자바기초&안드로이드개발자과정(Android전액환급… 오라클자바…12-011606
39 [평일야간,주말]SQL기초에서실무까지(SQL기초,PLSQL,힌트,튜닝) 오라클자바…12-011119
작성자: 시간:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)