• standby checking script 3则 V1 shell 脚本


    ---1

    #!/bin/sh

    export ORACLE_SID=hdb
    export ORACLE_BASE=/db/hdbdg/app/product/database
    export ORACLE_HOME=/db/hdbdg/app/product/database/11g
    export LANG=en_US
    export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
    export PATH=$PATH:$ORACLE_HOME/bin:.


    STATE_OK=0
    STATE_WARNING=1
    STATE_CRITICAL=2
    STATE_UNKNOWN=3
    #备库
    get_result=`sqlplus '/ as sysdba'<<EOF
    spool off;
    select 'stdcseq1=' || max(sequence#) from v\$archived_log where thread#=1 and applied='YES' group by thread#;
    quit;
    EOF`
    stdcseq1=`echo "$get_result"|grep stdcseq1|cut -d "=" -f2`
    echo $stdcseq1

    get_result=`sqlplus '/ as sysdba'<<EOF
    spool off;
    select 'stdcseq2=' || max(sequence#) from v\$archived_log where thread#=2 and applied='YES' group by thread#;
    quit;
    EOF`
    stdcseq2=`echo "$get_result"|grep stdcseq2|cut -d "=" -f2`
    echo $stdcseq2

    #主库
    get_result=`sqlplus dbmonopr/dbmonoprhdb11@hdb<<EOF
    spool off;
    select 'seq1=' || max(sequence#) from v\$archived_log where thread#=1 group by thread#;
    quit;
    EOF`
    echo "$get_result"
    seq1=`echo "$get_result"|grep seq1|cut -d "=" -f2`
    echo $seq1


    seqdiff1=`expr $seq1 - $stdcseq1`

    echo 'seqdiff1='$seqdiff1

    if [ $seqdiff1 -ge 10 ]
    then
    echo "CRITICAL - hdb database dataguard error large than 10."
    exit 2
    fi

    if [ $seqdiff1 -ge 6 ]
    then
    echo "WARNING - hdb database dataguard error large than 6."
    exit 1
    fi

    if [ $seqdiff1 -lt 6 ]
    then
    echo "OK - hdb database dataguard ok."
    exit 0
    fi

    ############2:

    version=`sqlplus -v|awk '{print $3}'|awk -F '.' '{print $1}'`

    process_status='select status from v$managed_standby where process!='"'ARCH' and process like '%MRP%';"
    status=`sqlplus -S / as sysdba<<EOF
    set heading off
    set feedback off
    $process_status
    exit
    EOF`
    echo mrp_status=$status

    synctime="select to_char(first_time,'""yyyy-mm-dd hh24:mi:ss'"') from v$log_history where recid in (select max(recid) from v$log_history b group by thread#);'
    time=`sqlplus -S / as sysdba<<EOF
    set heading off
    set feedback off
    $synctime
    exit
    EOF`
    echo synctime=$time


    system=`uname`
    case $system in
    AIX)
    disk_usage=`df -g|egrep -i "archive|fra" |awk '{print $(NF-3)}'`
    if [ -n "$disk_usage" ];
    then
    echo archivedisk_usage=$disk_usage
    fi
    ;;
    HP-UX)
    disk_usage=`bdf |egrep -i "archive|fra" |awk '{print $(NF-1)}'`
    if [ -n "$disk_usage" ];
    then
    echo archivedisk_usage=$disk_usage
    fi
    ;;
    Linux)
    disk_usage=`df -h|egrep -i "archive|fra" |awk '{print $(NF-1)}'`
    if [ -n "$disk_usage" ];
    then
    echo archivedisk_usage=$disk_usage
    fi
    ;;
    esac


    if [ -z "$disk_usage" ];
    then
    recovery_dest='select value from v$system_parameter where name = '"'db_recovery_file_dest';"
    dest=`sqlplus -S / as sysdba<<EOF
    set heading off
    set feedback off
    $recovery_dest
    exit
    EOF`
    dest=${dest#*+}

    asmdisk_usage_sql='select round((1-(free_mb/total_mb))*100,2) from v$asm_diskgroup where name='"'$dest';"
    asmdisk_usage=`sqlplus -S / as sysdba<<EOF
    set heading off
    set feedback off
    $asmdisk_usage_sql
    exit
    EOF`
    echo archivedisk_usage=${asmdisk_usage}%
    fi

    if [ $version -eq 10 ];
    then
    recovery_dest_usage='select PERCENT_SPACE_USED from v$flash_recovery_area_usage where FILE_TYPE like '"'%ARCHIVE%';"
    fi

    if [ $version -eq 11 ];
    then
    recovery_dest_usage='select PERCENT_SPACE_USED from v$flash_recovery_area_usage where FILE_TYPE = '"'ARCHIVED LOG';"
    fi

    usage=`sqlplus -S / as sysdba<<EOF
    set heading off
    set feedback off
    $recovery_dest_usage
    exit
    EOF`
    echo fra_usage=${usage}%

    ###########3

    #!/bin/bash
    #env
    PATH=/usr/local/bin:/usr/bin:$PATH:.
    source /home/oracle11g/.bash_profile


    f_getparameter(){
    if [ -z "$3" ]; then
    return
    fi
    PARAMETER=$1
    sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print $2}"
    ##sqlplus -s /nolog <<EOF
    set head off pagesize 0 feedback off linesize 200
    whenever sqlerror exit 1
    ##conn / as sysdba
    ##conn ${LOGIN_ID}@${db_name}
    conn $2@$3
    select 'a='||value from v$parameter where name = '$PARAMETER';
    EOF
    }

    f_getvalues(){
    if [ -z "$3" ]; then
    return
    fi
    PARAMETER=$1
    sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print $2}"
    ##sqlplus -s /nolog <<EOF
    set head off pagesize 0 feedback off linesize 200
    whenever sqlerror exit 1
    ##conn / as sysdba
    ##conn ${LOGIN_ID}@${db_name}
    conn $2@$3
    select 'a='||round(max_utilization/limit_value*100) from v$resource_limit where resource_name='$PARAMETER';
    EOF
    }


    f_getlist(){
    if [ -z "$1" ]; then
    return
    fi
    PARAMETER=$1
    # sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print $2}"
    sqlplus -s /nolog <<EOF
    set head off pagesize 0 feedack off linesize 50
    whenever sqlerror exit 1
    conn $2@$3
    select status from v$managed_standby where process!='ARCH' and process like '%MRP%';
    select to_char(first_time,'yyyy-mm-dd hh24:mi:ss') from v$log_history where recid in (select max(recid) from v$log_history b group by thread#);
    select PERCENT_SPACE_USED from v$flash_recovery_area_usage where FILE_TYPE like 'ARCHIVE%';
    EOF
    }


    db_list_checking(){

    db_list="ora11g_test sdbs"
    #db_list="sdbs"
    ##remove rdbs because 210.187 can't telnet 58.8.95.3 port 1528,need network check more
    for db_name in ${db_list}

    do
    LOGIN_ID=dbmgr/t1234DBA

    echo "###########$db_name###"

    #echo $list
    f_getlist ANY $LOGIN_ID $db_name

    done


    }
    main()
    {
    echo '----------------'`date`'------------------checing konw---------------------------'



    db_list_checking

    echo '----------------'`date`'------------------over---------------------------'

    }

    main

    ###sample 3:

    step 1 :create table tbspct 


    set heading on

    clear computes columns breaks

    column name new_value _dbname noprint
    column spool_time new_value _spool_time noprint
    column spooltime new_value _spooltime noprint

    select name,to_char(sysdate,'YYYYMMDD') as "spool_time",
    to_char(sysdate,'YYYY-MM-DD') as "spooltime"
    from v$database;

    define _rpt_db_name=&_dbname
    define _rpt_spool_time="&_spooltime"


    create table tbspct as
    SELECT '&_dbname' db_name,'&_spooltime' time,a.tablespace_name ,b.maxbytes/1024/1024/1024 "maxbyes_GB",total/1024/1024/1024 "bytes_GB",free/1024/1024/1024 "free_GB",(total-free) /1024/1024/1024 "use_GB",
    ROUND((total-free)/total,4)*100 "use_%",ROUND((total-free)/b.maxbytes,4)*100 "maxuse_%"
    FROM
    (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
    GROUP BY tablespace_name
    ) a,
    (SELECT tablespace_name,sum(case autoextensible when 'YES' then maxbytes else bytes end) maxbytes,SUM(bytes) total FROM DBA_DATA_FILES
    GROUP BY tablespace_name
    ) b
    WHERE a.tablespace_name=b.tablespace_name
    order by "maxuse_%" desc
    ;

    step 2:

    #!/bin/bash
    #env
    #the script is used for init dbmgr passwd every two month;

    PATH=/usr/local/bin:/usr/bin:$PATH:.
    export $PATH
    source /home/oracle11g/.bash_profile


    f_getparameter(){
    if [ -z "$3" ]; then
    return
    fi
    PARAMETER=$1
    sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print $2}"
    ##sqlplus -s /nolog <<EOF
    set head off pagesize 0 feedback off linesize 200
    whenever sqlerror exit 1
    ##conn / as sysdba
    ##conn ${LOGIN_ID}@${db_name}
    conn $2@$3
    select 'a='||value from v$parameter where name = '$PARAMETER';
    EOF
    }

    f_getvalues(){
    if [ -z "$3" ]; then
    return
    fi
    PARAMETER=$1
    sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print $2}"
    ##sqlplus -s /nolog <<EOF
    set head off pagesize 0 feedback off linesize 200
    whenever sqlerror exit 1
    ##conn / as sysdba
    ##conn ${LOGIN_ID}@${db_name}
    conn $2@$3
    select 'a='||round(max_utilization/limit_value*100) from v$resource_limit where resource_name='$PARAMETER';
    EOF
    }


    f_getlist(){
    if [ -z "$1" ]; then
    return
    fi
    PARAMETER=$1
    # sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print $2}"

    time=`date +"%Y-%m-%d"`
    db=$3

    QUERYRES=`
    sqlplus -s /nolog <<EOF
    set echo off feedback off heading off underline off linesize 300;
    conn $2@$3

    SELECT a.tablespace_name ,b.maxbytes/1024/1024/1024 "maxbyes_GB",total/1024/1024/1024 "bytes_GB",free/1024/1024/1024 "free_GB",(total-free) /1024/1024/1024 "use_GB",
    ROUND((total-free)/total,4)*100 "use_%",ROUND((total-free)/b.maxbytes,4)*100 "maxuse_%"
    FROM
    (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
    GROUP BY tablespace_name
    ) a,
    (SELECT tablespace_name,sum(case autoextensible when 'YES' then maxbytes else bytes end) maxbytes,SUM(bytes) total FROM DBA_DATA_FILES
    GROUP BY tablespace_name
    ) b
    WHERE a.tablespace_name=b.tablespace_name
    order by "maxuse_%" desc
    ;
    exit;
    EOF`

    echo 'echo variable_1: '${QUERYRES}


    while read -r tablespace_name maxbyes_GB bytes_GB free_GB used_GB use_percent maxuse_percent
    do
    echo "..${tablespace_name}..${maxbyes_GB}..${bytes_GB}..${free_GB}..${used_GB}..${use_percent}..${maxuse_percent}.."
    time=`date +"%Y-%m-%d"`
    db=$3
    sqlplus -S dbmgr/crbank1234DBA <<EOF
    insert into tabpct values ('${db}','${time}','${tablespace_name}',${maxbyes_GB},${bytes_GB},${free_GB},${used_GB},${use_percent},${maxuse_percent});
    commit;
    exit
    EOF
    done <<< "${QUERYRES}"
    echo "------------------------------------------------------"


    echo "------------------------------------------------------"
    }

    f_init_passwd(){
    if [ -z "$1" ]; then
    return
    fi
    PARAMETER=$1
    # sqlplus -s /nolog <<EOF | awk -F= "/^a=/ {print $2}"
    sqlplus -s /nolog <<EOF
    set head off pagesize 0 feedback off linesize 50
    whenever sqlerror exit 1
    conn $2@$3
    select '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    +++++++++++++++++++++++++++++++++++++++++++++++++' from dual;
    alter user dbmgr profile default;
    alter user dbmgr identified by crbank1234DBA;
    alter user dbmgr profile dba_profile;
    EOF
    }

    db_list_checking(){
    db_list="core1 core2 tbs cfront1 cfront2 otp ebank tler1 tler2 hsms NSP crbesb epcc afa dbs fpip igfs1 igfs2 epp meta ctl itos itsm pfss biee fms ods1 ods2 csmi crpt vas acp dssdb ofsaa amls osas pcis ecis imwf ccms scf eams aas etf2 crbp crbclyt fmp gcmp ems pas rrs tmap crma cafcs fams oivs pcms oid1 oid2 bpo1 bpo2 filnet kms cdbs frs rcfe bcds crcs ams dlpeds bdlp infa fibs ccmsrpt fcc edfe cifs mpcs cus oas crmo1 crmo2 ivms1 ivms2 nmbs ccfs ccif cccs cems moa bimp epcc bcss crss pcon imwf cop1 cop2 portal ilog omcp vats skdb imbs fcs abm bstv pisa cfms reb iqms ptsmdm moa esa osasv5 rbad um hdp ncdp ftms odshst htfb pcus grc mip opics virs ves ebcs cmms olp olprule"
    #db_list="opics ccms cems ccmsrpt afa eft2 fibs epcc skdb vats fms paca scf aas bcds bms ems pas rrs moa edfe edfe pisa frs portal meta infa ods crpt odshst ftms nsp"
    #db_list="ora11g"
    #db_list="afa_dg nsp_dg"

    for db_name in ${db_list}

    do
    LOGIN_ID=dbmgr/crbank1234DBA

    echo "###########$db_name###"


    #para=`f_getparameter processes $LOGIN_ID $db_name`
    ##echo $para
    #
    #if [ -z "$para" ]; then
    ### return "please check $db_name connect issue"
    # echo return "please check $db_name connect issue"
    # continue
    #fi
    #
    #
    #if [ $para -ge 151 ]
    #then
    # echo "OK"
    #else
    # echo "$db_name processes values is 150, please increase to 500;"
    #fi
    #
    #value=`f_getvalues processes $LOGIN_ID $db_name`
    ###echo $value
    #
    #if [ $value -ge 80 ]
    #then
    # echo "$db_name processes values is $para and not more , please increase to more;"
    #fi
    #

    #list=`f_getlist ANY $LOGIN_ID $db_name`

    #echo $list
    f_getlist ANY $LOGIN_ID $db_name
    #f_init_passwd ANY $LOGIN_ID $db_name

    done


    }
    main()
    {
    echo '----------------'`date`'------------------checing konw---------------------------'



    #/*每月27号清理alert_log{异常分析}、listener(监听log)*,udit_log(跟踪)、log_xml/
    ## date_=`date +%d`
    ## if [ $date_ -eq 27 ]
    ## then
    ## alert_log
    db_list_checking


    ##fi
    echo '----------------'`date`'------------------over---------------------------'

    }

    main

  • 相关阅读:
    将不确定变成确定~LINQ DBML模型可以对应多个数据库吗
    将不确定变成确定~frameset页面不能正确加载
    System.Web.Caching.Cache删除某键后,希望同时触发其它动作(关键时刻,还是事件靠的住)
    Linq实体类的设计(解决了复合查询的问题,同时解决了LINQ上下文缓存问题)
    将不确定变成确定~LINQ查询两种写法,性能没有影响,优化查询应该是“按需查询”
    ASP.NET的内置对象
    Templating with JSF 2.0 Facelets
    IOS设计模式学习(1)设计模式初窥
    20个强大的jQuery翻书插件【 jQuery flipbook】
    linux网络编程之socket(十四):基于UDP协议的网络程序
  • 原文地址:https://www.cnblogs.com/feiyun8616/p/8547778.html
Copyright © 2020-2023  润新知