crontab -l

2018. 9. 19. 11:32

DBA/mon] crontab -l
#00 * * * * /home/test/DBA/analyze/analyze.sh anal >> /home/test/DBA/analyze/log/cron.log 2>&1
#00 * * * * /home/test/DBA/analyze/analyze.sh age >> /home/test/DBA/analyze/log/cron.log 2>&1
#*/5 * * * * /home/test/DBA/mon/mon.sh>/dev/null 2>&1

DBA/mon] cat mon.sh
#!/bin/bash
unset LANG
#GSQL='gsqlnet test test --dsn GOLDILOCKS --no-prompt'

doIt()
{
export GOLDILOCKS_HOME=/test/product/goldilocks_home
export PATH=${GOLDILOCKS_HOME}/bin:${PATH}
GSQL='gsqlnet test test --conn-string HOST=127.0.0.1;PORT=22581;UID=test;PWD=test --no-prompt'
MACHTYPE=`uname`
START_DATE=`date +"%Y/%m/%d %H:%M:%S"`

echo "=============================================="
echo "= CHECK DATE:" $START_DATE       "           ="
echo "=============================================="
echo ""

echo "#================================="
echo "# total cpu usage"
echo "#================================="
echo ""
if [ $MACHTYPE = "AIX" ]
then
    vmstat 1 10 |grep -v System
else
    sar 1 10 | grep -v Linux
fi
echo ""

echo "#================================="
echo "# total memory usage"
echo "#================================="
echo ""
if [ $MACHTYPE = "AIX" ]
then
    svmon -G
else
    free -m
fi
echo ""

echo "#================================="
echo "# total disk usage"
echo "#================================="
echo ""
if [ $MACHTYPE = "AIX" ]
then
    df -G
else
    df -h
fi
echo ""

echo "#================================="
echo "# TABLESPACE SIZE CHECK"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT * FROM TECH_TABLESPACE;
\q
EOF
echo ""

echo "#================================="
echo "# TABLE SIZE CHECK"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT * FROM TECH_TABLE;
\q
EOF
echo ""

echo "#================================="
echo "# INDEX SIZE CHECK"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT * FROM TECH_INDEX;
\q
EOF
echo ""
echo "#================================="
echo "# total session count"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT '['||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) ||']' "DATE", NVL(CLUSTER_MEMBER_NAME, 'STANDALONE') MEMBER_NAME, CONNECTION, STATUS, COUNT(*)
  FROM X\$SESSION@GLOBAL[IGNORE_INACTIVE_MEMBER] GROUP BY CLUSTER_MEMBER_NAME, CONNECTION, STATUS ORDER BY 2,3,4;
\q
EOF
echo ""

echo "#================================="
echo "# ager status check"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT '['||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) ||']' "DATE",
       to_number ( split_part ( agable_scn_gap, '.', 1 )) GLOBAL_GAP,
       to_number ( split_part ( agable_scn_gap, '.', 2 )) DOMAIN_GAP,
       to_number ( split_part ( agable_scn_gap, '.', 3 )) LOCAL_GAP
       FROM GV\$AGABLE_INFO ORDER BY 1;
\q
EOF
echo ""

echo "#================================="
echo "# undo segemnt status check"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT '['||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) ||']' "DATE", NVL(CLUSTER_MEMBER_NAME, 'STANDALONE') MEMBER_NAME,
       SEGMENT_ID, PHYSICAL_ID, ALLOC_PAGE_COUNT, AGABLE_PAGE_COUNT
  FROM X\$UNDO_SEGMENT@GLOBAL[IGNORE_INACTIVE_MEMBER]
WHERE ALLOC_PAGE_COUNT > 1280
GROUP BY CLUSTER_MEMBER_NAME, SEGMENT_ID, PHYSICAL_ID, ALLOC_PAGE_COUNT, AGABLE_PAGE_COUNT
ORDER BY ALLOC_PAGE_COUNT DESC LIMIT 5;
\q
EOF
echo ""

echo "#================================="
echo "# lock wait status check"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT '['||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) ||']' "DATE", XS.CLIENT_PROCESS "CLIENT PID", XS.SERVER_PROCESS "SERVER PID", XS.ID,
       XLW.GRANTED_TRANSACTION_SLOT_ID, XLW.REQUEST_TRANSACTION_SLOT_ID "REQUEST_TX_ID", XTT.BEGIN_TIME "BEGIN_TIME"
  FROM X\$LOCK_WAIT XLW, X\$TRANSACTION XTT, X\$SESSION XS
WHERE XLW.GRANTED_TRANSACTION_SLOT_ID = XTT.SLOT_ID AND XTT.LOGICAL_TRANS_ID = XS.TRANS_ID;
\q
EOF
echo ""


echo "#================================="
echo "# long run transaction status check"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT '['||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) ||']' "DATE", NVL(CLUSTER_MEMBER_NAME, 'STANDALONE') MEMBER_NAME, LOGICAL_TRANS_ID,
       DRIVER_TRANS_ID, STATE, BEGIN_TIME
  FROM X\$TRANSACTION@GLOBAL[IGNORE_INACTIVE_MEMBER]
WHERE DATEDIFF( SECOND, BEGIN_TIME, LOCALTIMESTAMP ) > 5
ORDER BY 4,2,5;
\q
EOF
echo ""

echo "#================================="
echo "# long run statement status check"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT DECODE( ST.EXECUTIONS, 0 , NULL, ROUND( ST.TOTAL_EXEC_TIME/ST.EXECUTIONS , 3 ) ) COST
     , ST.EXECUTIONS, ST.TOTAL_EXEC_TIME, ST.LAST_EXEC_TIME, ST.START_TIME
     , LENGTHB( ST.SQL_TEXT ) LEN
     , SUBSTRB( ST.SQL_TEXT, 1, 80 ) SQLN
     , SS.PROGRAM_NAME
  FROM V\$STATEMENT ST
     , V\$SESSION SS
WHERE SS.SESSION_ID = ST.SESSION_ID
ORDER BY ST.START_TIME, 1 DESC, 2 DESC
LIMIT 10;
\q
EOF
echo ""

echo "#================================="
echo "# redo logfile status check"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT '['||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) ||']' "DATE", NVL(CLUSTER_MEMBER_NAME, 'STANDALONE') MEMBER_NAME, A.*
  FROM X\$LOG_GROUP@GLOBAL[IGNORE_INACTIVE_MEMBER] A
  WHERE STATE NOT IN ( 'UNUSED', 'INACTIVE' )
ORDER BY 2;
\q
EOF
echo ""

echo "#================================="
echo "# redo log stream status check"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT '['||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) ||']' "DATE", NVL(CLUSTER_MEMBER_NAME, 'STANDALONE') MEMBER_NAME,
       WAIT_COUNT_BY_BUFFER_FULL, BLOCKED_LOGGING_COUNT
  FROM X\$LOG_STREAM@GLOBAL[IGNORE_INACTIVE_MEMBER]
ORDER BY 2,3,4;
\q
EOF
echo ""


echo "#================================="
echo "# system lsn count check"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT '['||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) ||']' "DATE", NVL(CLUSTER_MEMBER_NAME, 'STANDALONE') MEMBER_NAME,
       NAME, VALUE
  FROM X\$SM_SYSTEM_INFO@GLOBAL[IGNORE_INACTIVE_MEMBER]
WHERE NAME = 'SYSTEM_LSN'
ORDER BY 2;
\q
EOF
echo ""

echo "#================================="
echo "# cluster queue check"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT '['||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) ||']' "DATE", NVL(CLUSTER_MEMBER_NAME, 'STANDALONE') MEMBER_NAME,
       C.NAME, C.QUEUED, C.WAIT_COUNT
  FROM X\$CLUSTER_QUEUE@GLOBAL[IGNORE_INACTIVE_MEMBER] C
WHERE CLUSTER_MEMBER_NAME LIKE '%N1' ORDER BY 2;
\q
EOF
echo ""

echo "#================================="
echo "# cluster cm buffer check"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT NVL(CLUSTER_MEMBER_NAME, 'STANDALONE') MEMBER_NAME, A.*
  FROM X\$CL_SYSTEM_INFO A ORDER BY 1;
\q
EOF
echo ""

echo "#================================="
echo "# total statement count"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT '['||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) ||']' "DATE", NVL(CLUSTER_MEMBER_NAME, 'STANDALONE') MEMBER_NAME,
       STMT_TYPE, EXECUTE_COUNT
  FROM X\$SQL_SYSTEM_STAT_EXEC_STMT@GLOBAL[IGNORE_INACTIVE_MEMBER]
WHERE EXECUTE_COUNT > 10
ORDER BY 2,3,4 DESC;
\q
EOF
echo ""

echo "#================================="
echo "# processor session count"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT '['||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) ||']' "DATE", NVL(CLUSTER_MEMBER_NAME, 'STANDALONE') MEMBER_NAME,
       PROGRAM, CONNECTION, STATUS, COUNT(*)
  FROM X\$SESSION
WHERE PROGRAM NOT LIKE '%gmaster%'
   AND PROGRAM NOT LIKE '%cdispatcher%'
   AND PROGRAM NOT LIKE '%server%'
GROUP BY CLUSTER_MEMBER_NAME, PROGRAM, CONNECTION, STATUS
ORDER BY 2,4;
\q
EOF
echo ""

echo "#================================="
echo "# total session count"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT '['||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) ||']' "DATE", NVL(CLUSTER_MEMBER_NAME, 'STANDALONE') MEMBER_NAME,
       COUNT(*)
  FROM X\$SESSION
WHERE PROGRAM NOT LIKE '%gmaster%'
   AND PROGRAM NOT LIKE '%cdispatcher%'
   AND PROGRAM NOT LIKE '%server%'
GROUP BY CLUSTER_MEMBER_NAME
ORDER BY 2;
\q
EOF
echo ""

echo "#================================="
echo "# transaction shard check"
echo "#================================="
$GSQL << EOF
\set linesize 2048
\set pagesize 1000
SELECT NVL(S.CLUSTER_MEMBER_NAME, 'STANDALONE') MEMBER_NAME, A.DRIVER_ID, A.DRIVER_COUNT,S.*
  FROM (
       SELECT DRIVER_TRANS_ID AS DRIVER_ID, COUNT(1) AS DRIVER_COUNT
         FROM X\$TRANSACTION
        WHERE DRIVER_TRANS_ID IN (
                                 SELECT DISTINCT(DRIVER_TRANS_ID)
                                   FROM X\$TRANSACTION
                                 )
       GROUP BY DRIVER_TRANS_ID
       ) A, X\$SESSION S
WHERE S.TRANS_ID = A.DRIVER_ID
   AND A.DRIVER_COUNT > 2;
\q
EOF
echo ""


echo "check complet"
}


TO_DATE=`date +%Y%m%d`
doIt >> /home/test/DBA/mon/mon_log/$TO_DATE.mon.log

DBA/analyze] cat analyze.sh
#!/bin/sh

GOLDILOCKS_HOME=/test/product/goldilocks_home
GOLDILOCKS_DATA=/test/product/goldilocks_data
PATH=${GOLDILOCKS_HOME}/bin:${PATH}
LD_LIBRARY_PATH==${GOLDILOCKS_HOME}/lib:$LD_LIBRARY_PATH

USER=test
PASS=test

ANAL_SCRIPT=/home/test/DBA/analyze/analyze_script.sql
AGING_SCRIPT=/home/test/DBA/analyze/aging_script.sql
IDX_DUMP_SCRIPT=/home/test/DBA/analyze/idx_dump.sql
LOGHOME=/home/test/DBA/analyze/log
LOGFILE=${LOGHOME}/analyze_`date '+%Y%m%d'`.log

SQL="gsqlnet ${USER} ${PASS} --no-prompt"

is_cluster()
{
    echo  "
    SELECT '#', IS_CLUSTER FROM X\$INSTANCE@LOCAL;
    quit;
    "
}

scan_tbl()
{
    echo "
    SELECT  '#', TABLE_SCHEMA||'.'||TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME;
    quit;
    "
}

scan_idx()
{
    echo "
    SELECT '#', INDEX_SCHEMA||'.'||INDEX_NAME FROM USER_INDEXES ORDER BY INDEX_NAME;
    quit;
    "
}

scan_gidx()
{
    echo "
    SELECT '#', TABLE_SCHEMA||'.'||TABLE_NAME FROM USER_GLOBAL_SECONDARY_INDEXES ORDER BY TABLE_NAME;
    quit;
    "
}

idx_header()
{
    echo "
    SELECT '# ['||SYSDATE||']'
    , IDX.INDEX_NAME
    , SUM(IDX_HE.EMPTY_NODE_COUNT) EMPTY_NODE_COUNT
    FROM X\$INDEX_HEADER@GLOBAL[IGNORE_INACTIVE_MEMBER] IDX_HD
    , INDEXES IDX
    WHERE IDX.IS_BUILTIN = FALSE
      AND IDX_HD.PHYSICAL_ID = IDX.PHYSICAL_ID
      AND IDX.CLUSTER_MEMBER_ID = IDX.HD.CLUSTER_MEMBER_ID
    GROUP BY IDX.INDEX_NAME
    ORDER BY 1,2;
    quit;
    "

}

tbl_script()
{
    if [ $# -eq 0 ]
    then
        echo "Waring : can't get index list"
    fi
    for TBL_NAME in $*
    do
        echo "ANALYZE TABLE ${TBL_NAME};" >> ${ANAL_SCRIPT}
    done
}


idx_script()
{
    if [ $# -eq 0 ]
    then
        echo "Waring : can't get index list"
    fi
    for IDX_NAME in $*
    do
        echo "ALTER INDEX ${IDX_NAME} AGING;" >> ${AGING_SCRIPT}
    done
}

gidx_script()
{
    if [ $# -eq 0 ]
    then
        echo "Waring : can't get table list"
    fi
    for TBL_NAME in $*
    do
        echo "ALTER TABLE ${TBL_NAME} ALTER GLOBAL SECONDARY INDEX AGING;" >> ${AGING_SCRIPT}
    done
}


if [ -f ${ANAL_SCRIPT} ]
then
    rm -f ${ANAL_SCRIPT}
fi
if [ -f ${AGING_SCRIPT} ]
then
    rm -f ${AGING_SCRIPT}
fi
if [ -f ${IDX_DUMP_SCRIPT} ]
then
    rm -f ${IDX_DUMP_SCRIPT}
fi
if [ ! -d ${LOGHOME} -a ! -x ${LOGHOME} ]
then
    mkdir ${LOGHOME}
    if [ $? -ne 0 ]
    then
        echo "Error : mkdir $LOGHOME" >&2
        exit 1;
    fi
fi

ISCLUSTGER=`is_cluster | ${SQL} | grep "^#" |awk '{print $2}'`

idx_script `scan_idx | ${SQL}  |grep "^#" |awk '{print $2}'`
tbl_script `scan_tbl | ${SQL}  |grep "^#" |awk '{print $2}'`

if [ ${ISCLUSTER}"x" = "TRUEx" ]
then
    gidx_script `scan_gidx | ${SQL}  |grep "^#" |awk '{print $2}'`
fi

if [ $# -eq 0 ]
then
    echo "$0 [ANAL|AGE]"
    exit 1;
fi

MODE=`echo $* | tr "A-Z" "a-z"`
echo "DATA  : $MODE"

if [ ${MODE}"x" = "analx" ]
then
    echo "[`date '+Y-%m-%d %H:%M:%S'`] #### START ANALYZE ##########################################" >> ${LOGFILE}
    ${SQL} -i ${ANAL_SCRIPT} >> ${LOGFILE} 2>&1
    echo "[`date '+Y-%m-%d %H:%M:%S'`] #### END ANALYZE ##########################################" >> ${LOGFILE}
elif [ ${MODE}"x" = "agex" ]
then
    echo "[`date '+Y-%m-%d %H:%M:%S'`] #### START AGING ##########################################" >> ${LOGFILE}
    idx_header | ${SQL} |grep "^#" |cut -d" " -f2- >> ${LOGFILE} 2>&1
    ${SQL} -i ${AGING_SCRIPT} >> ${LOGFILE} 2>&1
    idx_header | ${SQL} |grep "^#" |cut -d" " -f2- >> ${LOGFILE} 2>&1
    echo "[`date '+Y-%m-%d %H:%M:%S'`] #### END AGING ##########################################" >> ${LOGFILE}
else
    echo "Error : invalid argument" >&2
    exit 1;
fi

DBA/analyze] cat aging_script.sql
ALTER INDEX TEST.공지사항_구분코드_IDX AGING;
ALTER INDEX TEST.뉴스정보_구분코드_IDX AGING;
ALTER INDEX TEST.이용문의_구분코드_IDX AGING;

DBA/analyze] cat analyze_script.sql
ANALYZE TABLE TEST.ADMIN;
ANALYZE TABLE TEST.ADMIN_CATEGORY;
ANALYZE TABLE TEST.ADMIN_HISTORY;
ANALYZE TABLE TEST.ADMIN_MENU;
ANALYZE TABLE TEST.ADMIN_MENU_RIGHT;
ANALYZE TABLE TEST.ADMIN_MENU_RIGHT_HISTORY;

저작자표시 비영리 변경금지

'IT Engineer > Database' 카테고리의 다른 글

메모 메모 identified colums value (0)	2018.09.19
DB 사이징 할때 내가작성한것 저장 (0)	2018.09.19
데이터베이스 비정상 종료 시, 클러스터 멤버를 통한 구동(복구) 구동 절차 (0)	2018.09.19
dd명령어를 통해 디스크 성능 측정 메모 (0)	2018.09.19
오라클 client 설정 (0)	2018.09.19

DB나라

crontab -l

'IT Engineer > Database' 카테고리의 다른 글

+ Recent posts

티스토리툴바