自动化巡检脚本一（shell脚本） -m6米乐安卓版下载

用自动化手段生成报告，可以有多种方式：

第一种：系统可以直接链接用户的数据库，在线生成报告。但这种方式，在很多客户现场是不允许的。

第二种：生成离线的巡检日志，对巡检日志进行分析。

对于第二种方式，一般我们会写shell脚本来采集数据库的各项指标，形成离线log文件，然后利用python对log文件进行自动化分析。

这些脚本都是日常维护中不断总结出来的，有些读起来比较容易，有些晦涩难懂，大家可以根据echo 后面的信息，判断脚本做什么用的。

下面，贴出全量shell脚本（注：脚本有些地方会被网页编辑器变动，因此有的代码直接复制无法执行，需进行修正）：

value_os=`uname`

echo 'checktime#'
date

echo 'hostname#'
hostname_str=`hostname`
htm=`echo $hostname_str | cut -d "." -f1|tr '[:upper:]' '[:lower:]'|sed 's/[0-9]$//'`
#echo $htm
#htm=`$htms|tr '[:upper:]' '[:lower:]'|sed 's/[0-9]$//'`
#htm=`hostname|tr '[:upper:]' '[:lower:]'|sed 's/[0-9]$//'`
host_list=`cat /etc/hosts |grep ${htm}|awk '{print $2}'|grep -v '\-vip'|grep -v 'lpar'|grep -v '_per'|grep -v 'dr'|grep -v '\-pri'|grep -v '\-scan'`
array_host=${host_list[@]}
host_count=0
final=0
for host_l in ${array_host[@]}
do
host_count=$(expr $host_count 1)
done


if [ $host_count -lt 2 ]
then
host_list=`hostname`
array_host=$host_list
fi

for host_l in ${array_host[@]}
do
echo $host_l
done



############################################# os_db_info begin
echo 'host_ip#'
cat /etc/hosts |grep ${htm}|awk '{print $1}'

echo 'release#'
if [ $value_os == 'linux' ]
then
cat /etc/redhat-release
fi
if [ $value_os == 'aix' ]
then
oslevel -s
fi
echo 'release_end#'


echo 'db_release_begin#'
$oracle_home/opatch/opatch lsinv| grep "database"
echo 'db_release_end#'


echo 'os_type_begin#'
if [ $value_os == 'linux' ]
then
echo $value_os
cat /etc/redhat-release
fi
if [ $value_os == 'aix' ]
then
echo $value_os
oslevel -s
fi
echo 'os_type_end#'

## 获取内存及swap区
echo 'os_mem_begin#'
for host_l in ${array_host[@]}
do
if [ $host_count -ge 2 ]
then
host_name=`hostname`
if [ $host_name == $host_l ]
then
host_shell=''
echo $host_shell
else
host_shell="ssh $host_l"
fi
else
host_shell=''
fi

if [ $value_os == 'linux' ]
then

val_mem=`$host_shell cat /proc/meminfo | grep memtotal|awk '{print $2}'`;
echo $host_l'||mem||'$val_mem
val_swap=`$host_shell cat /proc/swaps|awk '{print $3}'|sed -n '2p'`;
echo $host_l'||swap||'$val_swap;
fi

if [ $value_os == 'aix' ]

#aix的要重写
then

val_mem=`$host_shell svmon -g -o unit=mb|sed -n '4p'| awk '{print $2}'`;
echo $host_l'||mem||'$val_mem
val_swap=`$host_shell svmon -g -o unit=mb|sed -n '5p'| awk '{print $3}'`;
echo $host_l'||swap||'$val_swap;

fi
done
echo 'os_mem_end#'

##get cpu count

echo 'os_cpucount_begin#'

sqlplus -silent / as sysdba <col value for a10
col inst_id for a10
col inst_id format 9
select inst_id,value from gv\$parameter where name = 'cpu_count';
exit;
eof
echo 'os_cpucount_end#'

echo 'instace_list#'
sqlplus -silent / as sysdba <select instance_name from gv\$instance;
exit;
eof
echo 'instace_list_end#'

#############################################os_db_info end


for host_l in ${array_host[@]}
do

#echo 'now,start to check the '$host_l

if [ $value_os == 'linux' ]
then
if [ $host_count -ge 2 ]
then
host_name=`hostname`
if [ $host_name == $host_l ]
then
host_shell=''
echo $host_shell
else
host_shell="ssh $host_l"
fi
else
host_shell=''
fi

echo 'check file system######### host is:'$host_l;
$host_shell df -h



echo 'check time system#########'$host_l;
$host_shell timedatectl status;
$host_shell ntpq -p;
$host_shell chronyc sources -v

echo 'check cpu &mem#########'$host_l;
$host_shell free -g
$host_shell vmstat 1 2


echo 'check eth#########'$host_l;
$host_shell ifconfig -a



echo 'check transparent_hugepage#########'$host_l;
$host_shell cat /sys/kernel/mm/transparent_hugepage/enabled

echo 'check nr_hugepages#########'$host_l;
$host_shell cat /etc/sysctl.conf|grep vm.nr_hugepages

echo 'check meminfo#########'$host_l;
$host_shell cat /proc/meminfo



echo 'check enforce#########'$host_l;
$host_shell getenforce



echo 'check syslimits#########'$host_l;
$host_shell cat /etc/security/limits.conf



echo 'check sysctl#########'$host_l;
$host_shell cat /etc/sysctl.conf





fi


if [ $value_os == 'aix' ]
then
echo 'os is:'$value_os
# delete os level, shang mian yi jing you le.
#echo 'release#'
# oslevel -s

echo 'check file system######### host is:'$host_l;
df -g
echo 'check cpu &mem#########'$host_l;
vmstat 1 10
mem_total=`svmon -g -o unit=mb|sed -n '4p'| awk '{print $2}''`
mem_use=`svmon -g -o unit=mb |sed -n '4p'| awk '{print $3}'`
mem_free=`svmon -g -o unit=mb |sed -n '4p'| awk '{print $4}'`
echo 'mem:'$mem_total' '$mem_use' '$mem_free

echo 'check eth#########'$host_l;
ifconfig -a
echo 'check error#########'$host_l;
errpt | tail -n 20
echo 'check no prarameter#########'$host_l;
cat /etc/tunables/nextboot

echo 'check syslimits#########'$host_l;
cat /etc/security/limits
fi





echo "##################check asm log##########"$host_l
crs_base1=`lsnrctl status | grep "listener log file" |cut -d " " -f12`;
crs_base=${crs_base1%diag/*}
echo $crs_base

if [ -z "$crs_base" ]; then
crs_base=/u01/app/grid
fi
echo $crs_base

asm_count=`ps -ef|grep asm_ |wc -l`
echo $asm_count
if [ $asm_count -gt 5 ]


then

if [ $host_count -ge 2 ]
then
host_name=`hostname`
if [ $host_name == $host_l ]
then
host_shell=''
$host_shell cat ${crs_base}/diag/asm/ asm/ asm*/trace/alert*.log | tail -n 200
else
host_shell="ssh $host_l"
asm=`ssh $host_l ps -ef|grep pmon_ |grep -v 'grep' |awk -f"_" '{ print $3 }'`
$host_shell cat ${crs_base}/diag/asm/ asm/${asm}/trace/alert*.log | tail -n 200
fi
else
host_shell=''
fi



else
echo "asm is not installed ! "
fi


if [ $host_count -ge 2 ]
then
if [ $asm_count -lt 5 ]
then
echo "asm instalce is not startup! "
fi

fi


echo "##################check clualert log##########"$host_l

asm_count=`ps -ef|grep asm_ |wc -l`
echo $asm_count
if [ $asm_count -gt 5 ]


then

if [ $host_count -ge 2 ]
then
host_name=`hostname`
if [ $host_name == $host_l ]
then
host_shell=''
echo $host_shell
else
host_shell="ssh $host_l"
fi
else
host_shell=''
fi

$host_shell cat ${crs_base}/diag/crs/`echo $host_l`/crs/trace/alert*.log | tail -n 200

else
echo "no crs installed ! "
fi


echo "####################check dbalert log#################"$host_l
ora_count=`ps -ef|grep ora_|wc -l`
echo $ora_count

if [ $ora_count -gt 5 ]
then

if [ $host_count -ge 2 ]
then
host_name=`hostname`
if [ $host_name == $host_l ]
then
host_shell=''
echo $host_shell
else
host_shell="ssh $host_l"
fi
else
host_shell=''
fi


final=$(expr $final 1)

#final=${host_l: -1}
#echo $final
#echo $host_count
#echo $host_shell

if [ -z "$host_shell" ]
then
#echo 'zhi xing le wo kong'
value_db=`sqlplus -silent / as sysdba <set heading off echo off
select value from v\\$diag_info where name='diag trace';
exit;
end`
elif [ $final -eq 1 ]
then
#echo 'zhi xing le wo 1'
value_db=`sqlplus -silent / as sysdba <set heading off echo off
select value from gv\\$diag_info where name='diag trace' and inst_id=1;
exit;
end`
elif [ $final -eq 2 ]
then
#echo 'zhi xing le wo 2'
value_db=`sqlplus -silent / as sysdba <set heading off echo off
select value from gv\\$diag_info where name='diag trace' and inst_id=2;
exit;
end`
# else
#value_db=`sqlplus -silent / as sysdba <#set heading off echo off
#select value from gv\\$diag_info where name='diag trace';
#exit;
#end`

fi

echo ${value_db}

result_no=$(echo ${value_db} | grep 'no rows' |wc -l)
if [ ${result_no} -le 0 ]
then
$host_shell cat ${value_db}/alert*.log | tail -n 1000

else
echo "database not startup!!!!! "
fi

else
echo "database not startup!!!!! "
fi


#loop done
done

echo "####################check crsstat#################"
asm_count=`ps -ef|grep asm_ |wc -l`
# echo $asm_count
if [ $asm_count -gt 5 ]


then

crs_home1=`lsnrctl status | grep "listener parameter file" |cut -d " " -f6`;

crs_home=${crs_home1%network/*}

echo $crs_home

if [ -z "$crs_base" ]; then
crs_home=u01/app/19c/grid
fi
#echo $crs_home
${crs_home}/bin/crsctl status res -t
else
echo "no crs installed ! "

fi



value_version=11
ora_count=`ps -ef|grep ora_|wc -l`

if [ $ora_count -gt 5 ]
then
value_version=`sqlplus -silent / as sysdba <set heading off echo off
select to_number(substr(version,1,2)) from v\\$instance;
exit;
end`

value_version_full=`sqlplus -silent / as sysdba <set heading off echo off
select version from v\\$instance;
exit;
end`

else
echo "database not startup!!!!! "
fi

echo "####################check dbversion start#################"
echo ${value_version_full}
echo "####################check dbversion end#################"

#echo "####################check pdbor not #################"
#if [ $ora_count -gt 5 ]
#then
#value_cdb=`sqlplus -silent / as sysdba <#set heading off echo off
#select count(1) from v\\$pdbs;
#exit;
#end`
#echo ${value_cdb}
#else
#echo "this database is not in cdb mode or not startup"
#fi


###if db_version higher 11
if [ $value_version -gt 11 ]
then
sqlplus / as sysdba <
set pagesize 49999
set linesize 300
col name for a20
col username for a30
col event for a100
col a2 for a30
col pid for a10
col group# for a10
col account_status for a30
col pct format 99.99
col pdb for a15
set pages 9999

prompt 'begin===check db name ####' checkjcsqldbname;

select a.name,to_char(a.created,'yyyy-mm-dd') from v\$database a;


prompt 'check db status ####' ;

col name for a30
select to_char(ins.startup_time,'yyyy-mm-dd hh24:mi:ss') startuptime,
ins.instance_name,
ins.status,
nvl(tt.name,'cdb') pdb,
tt.open_mode,
tt.restricted
from gv\$pdbs tt,
gv\$instance ins
where tt.inst_id( )=ins.inst_id
order by tt.inst_id, tt.con_id;

prompt 'check parameter ####' ;

col value_parameter for a200
select substr(a.inst_id||'||'||a.ksppinm||'||'||nvl(b.ksppstvl,'kong'),1,200) value_parameter
from x\$ksppi a,x\$ksppcv b
where a.indx = b.indx and a.ksppinm in('_gc_policy_time','_gc_undo_affinity','_px_use_large_pool','_undo_autotune','_optimizer_mjc_enabled','_optimizer_mjc_enabled')
union
select substr(a.inst_id||'||'||a.name||'||'||nvl(value,'kong'),1,200) value_parameter from gv\$parameter a
where a.name in('memory_target','open_cursors','open_links','open_links_per_instance','parallel_force_local','undo_retention','processes','db_files','sga_max_size','sga_target','shared_pool_size','pga_aggregate_target');


prompt 'check virus####' checkjcsqlvirus ;

select 'drop trigger '||owner||'."'||trigger_name||'";' from cdb_triggers where trigger_name like 'dbms_%_internal%'
union all
select 'drop procedure '||owner||'."'||a.object_name||'";' from cdb_procedures a where a.object_name like 'dbms_%_internal% '
union all
select 'drop procedure '||owner||'."'||object_name||'";' from cdb_objects where object_name ='dbms_support_dbmonitorp'
union all
select 'drop trigger '||owner||'."'||trigger_name||'";' from cdb_triggers where trigger_name ='dbms_support_dbmonitor';


prompt 'check asmgroup status ####' checkasmgroup ;
select group_number,name,round(((total_mb-free_mb)/total_mb)*100,2) dg_pent,total_mb,state,free_mb,type from v\$asm_diskgroup;


prompt 'check data_files ####' checkjcsqldatafiles ;

col file_name for a40
col tablespace_name for a20
col status for a10
col autoextensible for a10

select a.file_id,a.status,a.tablespace_name, a.autoextensible,a.con_id from cdb_data_files a ;

prompt 'checktabspacelogging_pdb ####' checkjcsqltabspacelogging ;
set linesize 300;
col status for a20;
col tablespdace_name for a20;

select a.status,a.logging,a.force_logging,a.tablespace_name,a.con_id from cdb_tablespaces a where a.contents <>'temporary';


prompt 'check ivalid object ####' checkjcsqlivalidobject ;
col owner for a10
col object_name for a50
col object_type for a20
col status for a10
select a.owner,a.object_name,a.object_type,a.status from cdb_objects a where a.status<>'valid';

prompt 'checkunusableindex ####' checkjcsqlunusableindex ;
col owner for a15
col index_name for a30
col status for a10
select owner,index_name,status from cdb_indexes where status ='unusable'
union
select index_owner,index_name,status from cdb_ind_partitions where status ='unusable';

prompt 'user check####'

select username,
trunc(expiry_date - sysdate) days,
replace(account_status, '&', 'and') as account_status
from cdb_users a
where a.username not in ('sys',
'system',
'xs$null',
'ojvmsys',
'lbacsys',
'outln',
'sys$umf',
'dbsnmp',
'appqossys',
'dbsfwuser',
'ggsys',
'anonymous',
'ctxsys',
'dvsys',
'dvf',
'gsmadmin_internal',
'mdsys',
'olapsys',
'xdb',
'wmsys',
'gsmcatuser',
'mddata',
'sysbackup',
'remote_scheduler_agent',
'gsmuser',
'sysrac',
'gsmrootuser',
'si_informtn_schema',
'audsys',
'dip',
'ordplugins',
'syskm',
'orddata',
'oracle_ocm',
'sysdg',
'ordsys',
'remote_scheduler_age',
'spatial_csw_admin_us',
'exfsys',
'apex_public_user',
'spatial_csw_admin_us',
'spatial_wfs_admin_us',
'apex_030200',
'owbsys_audit',
'owbsys',
'flows_files',
'scott',
'mgmt_view',
'sysman',
'apex_040200',
'olpaydb',
'sysaux',
'apex_050000',
'dmsys',
'tsmsys',
'spatial_csw_admin_usr',
'spatial_wfs_admin_usr')
and trunc(expiry_date - sysdate) <= 30;





prompt 'check db_cache hit####' checkjcsqldb_cachehit ;
select round(100-(phr.value-phrd.value-phrdl.value)/(cg.value dbbg.value)*100,2) prent
from v\$sysstat phr,v\$sysstat phrd,v\$sysstat phrdl,v\$sysstat cg,v\$sysstat dbbg
where phr.name='physical reads' and phrd.name='physical reads direct' and phrdl.name='physical reads direct (lob)'
and cg.name='consistent gets' and dbbg.name='db block gets';


prompt 'check libraty hit####' checkjcsqllibratyhit ;
select round(sum(pins -reloads)/sum(pins)*100,2) "library cache hit ratio"
from v\$librarycache;


prompt 'check latch hit####' checkjcsqllatchhit ;
select round((1-sum(misses immediate_misses)/sum(gets immediate_gets))*100,2) "latch hit ratio"
from v\$latch;


prompt 'histroye event####' checkjcsqlhistroyeevent ;
set linesize 300;
col event for a100;
select * from (
select event, count(1), con_id from cdb_hist_active_sess_history where
sample_time>=sysdate-7 and event is not null group by con_id,event
having count(*) > 0 order by count(*) desc
) a where rownum<=10;

prompt 'sga size####' checkjcsqlsga_size ;
select a.sga_size,
a.sga_size_factor,
a.estd_db_time,
a.estd_db_time_factor,
a.estd_physical_reads,
inst_id
from gv\$sga_target_advice a;

prompt 'pga size####' checkjcsqlpga_size ;
select pga_target_for_estimate / 1024 / 1024 "pga(mb)",
pga_target_factor,
estd_pga_cache_hit_percentage,
estd_overalloc_count,
inst_id
from gv\$pga_target_advice;



prompt 'check arch mode####' checkjcsqlarchmode ;
select a.name,a.log_mode from v\$database a;

prompt 'check onlinelog size####' checkjcsqlonlinelogsize ;
select thread#,count(1),(select distinct(bytes) from v\$log where rownum=1) logsize from v\$log group by thread#;


prompt 'check log change####' checkjcsqllogchange ;
set linesize 300
column h00 format 999
column h01 format 999
column h02 format 999
column h03 format 999
column h04 format 999
column h05 format 999
column h06 format 999
column h07 format 999
column h08 format 999
column h09 format 999
column h10 format 999
column h11 format 999
column h12 format 999
column h13 format 999
column h14 format 999
column h15 format 999
column h16 format 999
column h17 format 999
column h18 format 999
column h19 format 999
column h20 format 999
column h21 format 999
column h22 format 999
column h23 format 999
column avg format 999
column day format a6

select to_char(first_time, 'mm/dd') day,
sum(decode(to_char(first_time, 'hh24'), '00', 1, 0)) h00,
sum(decode(to_char(first_time, 'hh24'), '01', 1, 0)) h01,
sum(decode(to_char(first_time, 'hh24'), '02', 1, 0)) h02,
sum(decode(to_char(first_time, 'hh24'), '03', 1, 0)) h03,
sum(decode(to_char(first_time, 'hh24'), '04', 1, 0)) h04,
sum(decode(to_char(first_time, 'hh24'), '05', 1, 0)) h05,
sum(decode(to_char(first_time, 'hh24'), '06', 1, 0)) h06,
sum(decode(to_char(first_time, 'hh24'), '07', 1, 0)) h07,
sum(decode(to_char(first_time, 'hh24'), '08', 1, 0)) h08,
sum(decode(to_char(first_time, 'hh24'), '09', 1, 0)) h09,
sum(decode(to_char(first_time, 'hh24'), '10', 1, 0)) h10,
sum(decode(to_char(first_time, 'hh24'), '11', 1, 0)) h11,
sum(decode(to_char(first_time, 'hh24'), '12', 1, 0)) h12,
sum(decode(to_char(first_time, 'hh24'), '13', 1, 0)) h13,
sum(decode(to_char(first_time, 'hh24'), '14', 1, 0)) h14,
sum(decode(to_char(first_time, 'hh24'), '15', 1, 0)) h15,
sum(decode(to_char(first_time, 'hh24'), '16', 1, 0)) h16,
sum(decode(to_char(first_time, 'hh24'), '17', 1, 0)) h17,
sum(decode(to_char(first_time, 'hh24'), '18', 1, 0)) h18,
sum(decode(to_char(first_time, 'hh24'), '19', 1, 0)) h19,
sum(decode(to_char(first_time, 'hh24'), '20', 1, 0)) h20,
sum(decode(to_char(first_time, 'hh24'), '21', 1, 0)) h21,
sum(decode(to_char(first_time, 'hh24'), '22', 1, 0)) h22,
sum(decode(to_char(first_time, 'hh24'), '23', 1, 0)) h23,
count(*) total,
inst_id
from (select rownum rn, first_time,inst_id
from gv\$log_history
where first_time > sysdate - 7
and first_time > add_months(sysdate, -1)
order by inst_id,first_time)
group by inst_id,to_char(first_time, 'mm/dd')
order by inst_id,min(rn);


prompt 'session use####' checkjcsqlsessionuse ;
select inst_id,((select count(1) from gv\$session)/a.value)*100 huihua_prent from gv\$parameter a where a.name = 'processes';
col value for a30;
select a.value from v\$parameter a where a.name = 'processes';


prompt 'log mode####' checkjcsqlarchivelogcheck ;
select a.log_mode,a.force_logging from v\$database a;


prompt 'scn headroom####' checkjcsqlscnheadroom ;
select to_char(tim,'yyyy-mm-dd:hh24:mi:ss') tim,scn,round((chk16kscn-scn)/24/3600/16/1024,1) headroom
from (select tim, scn, ((((to_number(to_char(tim,'yyyy'))-1988)*12*31*24*60*60) ((to_number(to_char(tim,'mm'))-1)*31*24*60*60) (((to_number(to_char(tim,'dd'))-1))*24*60*60) (to_number(to_char(tim,'hh24'))*60*60) (to_number(to_char(tim,'mi'))*60) (to_number(to_char(tim,'ss'))) ) * (16*1024)) chk16kscn from (select sysdate tim,dbms_flashback.get_system_change_number scn from dual)) order by tim;

prompt 'backup qingkuang####' checkjcsqlbackupqingkuang ;
set linesize 300
col operation for a15
col status for a15;
col st for a30
col en for a30
select /* rule*/ a.sid,a.operation,a.status,a.object_type,to_char(a.start_time,'yyyy-mm-dd hh24:mi:ss') st,to_char(a.end_time,'yyyy-mm-dd hh24:mi:ss') et from v\$rman_status a where operation='backup' and a.start_time>sysdate-7 order by st desc;


prompt 'controlfile autuback####' checkjcsqlcontrolfileautuback ;
col name for a30
col value for a20
select name,value,inst_id from gv\$rman_configuration a where a.name='controlfile autobackup';

prompt 'controlfile twopiece####' checkjcsqlcontrolfiletwopiece ;
show parameter control_files;

prompt 'auto_job_check####' checkjcsqlautojob ;
column client_name format a35
select client_name,status from dba_autotask_client;


prompt 'adg or not####' checkjcsqladgornot ;
col name for a40;
col value for a20;
select a.name,a.value from v\$parameter a where a.name like 'log_archive%';


prompt 'adg processstatus####' checkjcsqladgprocessstatus;
select process,status,pid, group# , resetlog_id,thread#,sequence# from gv\$managed_standby;

prompt 'adg tongbu yanchi####' checkjcsqladgtongbuyanchi ;
set lines 300 pages 50
set heading on
set verify off
col name for a30
col value for a30
col time_computed for a20
col datum_time for a20 heading 'last_received_time'
col inst_id for 99 heading 'id'
break on inst_id
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select inst_id,name,value,time_computed,datum_time,sysdate from gv\$dataguard_stats order by inst_id;

prompt 'adg archive_dest####' checkjcsqladgarchive_dest ;
col dest_name for a40
set lines 300
set pages 900
select dest_name,status,error from v\$archive_dest;

prompt 'adg database status####' checkjcsqladdatabasestatus ;
set lines 200
col database_role for a18
col protection_mode for a20
col protection_level for a20
col flashback_on for a10
col name for a10
col switchover_status for a15
select name,database_role,protection_mode,protection_level,switchover_status,flashback_on,open_mode from v\$database;



prompt 'db size####';
select 'db_size',round(sum(a.bytes)/1024/1024/1024,2) dbsize from cdb_segments a
union
select 'db_file_size',round(sum(decode(t.maxbytes,0,t.bytes,t.maxbytes))/1024/1024/1024,2) dbsize from cdb_data_files t;
prompt 'db size end####';



prompt 'stattype_locked####' checkjcsqlstattype_locked ;

col owner for a20
col table_name for a30
select distinct a.owner,a.table_name,a.stattype_locked from dba_tab_statistics a where a.owner not in ('sys', 'system', 'xs$null', 'ojvmsys', 'lbacsys', 'outln', 'sys$umf', 'dbsnmp', 'appqossys',
'dbsfwuser', 'ggsys', 'anonymous', 'ctxsys',
'dvsys', 'dvf', 'gsmadmin_internal', 'mdsys', 'olapsys', 'xdb', 'wmsys', 'gsmcatuser', 'mddata',
'sysbackup', 'remote_scheduler_agent', 'gsmuser', 'sysrac',
'gsmrootuser', 'si_informtn_schema', 'audsys', 'dip', 'ordplugins', 'syskm', 'orddata',
'oracle_ocm', 'sysdg', 'ordsys', 'remote_scheduler_age', 'spatial_csw_admin_us', 'exfsys',
'apex_public_user', 'spatial_csw_admin_us',
'spatial_wfs_admin_us', 'apex_030200', 'owbsys_audit', 'owbsys', 'flows_files', 'scott', 'mgmt_view',
'sysman', 'apex_040200', 'olpaydb', 'sysaux', 'apex_050000',
'dmsys', 'tsmsys', 'spatial_csw_admin_usr', 'spatial_wfs_admin_usr') and a.stattype_locked is not null;

prompt 'table_index_degree####' checkjcsqlstattable_indexdegree;
select count(1),'table' as deg from dba_tables a where a.degree>'1' having count(1)>0
union
select count(1),'index' from dba_indexes b where b.degree>'1'having count(1)>0;



prompt 'parse hard count####' checkjcsqlparsehardcount;

select a.instance_number,
to_char(end_interval_time, 'yyyy-mm-dd-hh24:mi') start_time,
round((case
when round((b.value - a.value) /
round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2),
2) < 0 then
0
else
round((b.value - a.value) /
round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2),
2)
end) / (round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2)),
2) "parsehard/s",
a.snap_id,
round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2) time_inter
from cdb_hist_sysstat a,
cdb_hist_sysstat b,
cdb_hist_snapshot c
where a.stat_name = 'parse count (hard)'
and b.stat_name = 'parse count (hard)'
and a.dbid = b.dbid
and a.dbid = (select dbid from v\$database)
and a.instance_number = b.instance_number
and c.instance_number = b.instance_number
and a.snap_id = c.snap_id
and b.snap_id = c.snap_id 1
and c.end_interval_time >= sysdate - 7
order by a.instance_number, a.snap_id asc;

prompt 'check db time####' checkjcsqldbtime


select a.instance_number,to_char(end_interval_time,'yyyy-mm-dd-hh24:mi') start_time,
round((case
when round((b.value-a.value)/1000000/
round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2),2)< 0 then
0
else
round((b.value-a.value)/1000000/
round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2),2)
end)/(round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
- to_date(to_char(c.begin_interval_time,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,2)),2)
"dbtime/s",
a.snap_id,
round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2) time_inter,
a.con_id
from cdb_hist_sys_time_model a,cdb_hist_sys_time_model b ,cdb_hist_snapshot c
where a.stat_name='db time' and b.stat_name='db time'
and a.dbid=b.dbid and a.dbid=(select dbid from v\$database)
and a.instance_number= b.instance_number and c.instance_number=b.instance_number
and a.snap_id=c.snap_id
and b.snap_id=c.snap_id 1
and c.end_interval_time>=sysdate-7
order by a.instance_number,a.snap_id asc;



prompt 'check logicalreads####'checkjcsqllogicalreads
select a.instance_number,
to_char(c.end_interval_time, 'yyyy-mm-dd-hh24:mi') start_time,
round((case
when round((b.value - a.value) * 8192 / 1024 / 1024 /
round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1),
1) < 0 then
0
else
round((b.value - a.value) * 8192 / 1024 / 1024 /
round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1),
1)
end) / (round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1)),
1) "logicalreads_m/s",
a.snap_id,
round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1) time_inter
from cdb_hist_sysstat a, cdb_hist_sysstat b, cdb_hist_snapshot c
where a.stat_name = 'session logical reads'
and b.stat_name = 'session logical reads'
and a.dbid = b.dbid
and a.dbid = (select dbid from v\$database)
and a.instance_number = b.instance_number
and c.instance_number = b.instance_number
and a.snap_id = c.snap_id
and b.snap_id = c.snap_id 1
and c.end_interval_time >= sysdate - 7
order by a.instance_number, a.snap_id asc;


prompt 'check physicalreads####'checkjcsqlphysicalreads

select a.instance_number,
to_char(c.end_interval_time, 'yyyy-mm-dd-hh24:mi') start_time,
round((case
when round((b.value - a.value)*8192/1024/1024/ round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1),
1) < 0 then
0
else
round((b.value - a.value)*8192/1024/1024/ round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1),
1)
end) / (round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1)),
1) "physicalreads_m/s",
a.snap_id,
round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1) time_inter
from cdb_hist_sysstat a, cdb_hist_sysstat b, cdb_hist_snapshot c
where a.stat_name = 'physical reads'
and b.stat_name = 'physical reads'
and a.dbid = b.dbid
and a.dbid = (select dbid from v\$database)
and a.instance_number = b.instance_number
and c.instance_number = b.instance_number
and a.snap_id = c.snap_id
and b.snap_id = c.snap_id 1
and c.end_interval_time >= sysdate - 7
order by a.instance_number, a.snap_id asc;


prompt 'check tablespace ####' checkjcsqltablespace ;
col status for a10
col tablespace_name for a15
col size(m) for a15
col used for a15
col max_pct for 99.99
col now_pct for 99.99

select ts.tablespace_name as tb_sp_name,
trunc(df.total/df.tb_sp_maxsize,4)*100 as max_pct,
trunc((df.total - free)/df.total*100,4) as now_pct,
trunc(df.tb_sp_maxsize,2)-trunc(df.total, 2) as "valid_extend_space(g)",
t1.name,
trunc(df.tb_sp_maxsize,2) as "tb_maxsize(g)" ,
trunc(df.total, 2) as "size(g)",
trunc(df.total - free, 2) as "used(g)",
trunc(fr.free, 2) as "freesize(g)",
df.online_status as tb_sp_status
from (select con_id, tablespace_name from cdb_tablespaces) ts,
(select con_id, tablespace_name, sum(bytes) / (1024 * 1024 * 1024) free
from cdb_free_space group by con_id, tablespace_name) fr,
(select con_id, tablespace_name, sum(bytes) / (1024 * 1024 * 1024) total,round(sum(decode(maxbytes,0,bytes,maxbytes))/1024/1024/1024,2) tb_sp_maxsize, online_status
from cdb_data_files group by con_id, tablespace_name, online_status) df,
v\$pdbs t1
where ts.tablespace_name = df.tablespace_name( )
and ts.tablespace_name = fr.tablespace_name( )
and ts.con_id = df.con_id
and ts.con_id = fr.con_id
and ts.con_id = t1.con_id( )
order by max_pct desc;

prompt 'sql cost####' checkjcsqlcost ;

col operation for a20
col object_name for a50
col machine for a20
col operation for a20
col program for a50
set pages 9999

select *
from (select /* rule */
distinct a.sql_id,
a.object_name,
a.plan_hash_value,
c.sql_text,
(select max(cost)
from gv\$sql_plan
where sql_id = a.sql_id
and plan_hash_value = a.plan_hash_value) cost
from gv\$sql_plan a, dba_tables b, gv\$sql c
where a.object_name = b.table_name
and a.operation = 'table access'
and a.options = 'full'
and a.object_type = 'table'
and a.sql_id = c.sql_id
and b.owner not in ('sys',
'system',
'xs$null',
'ojvmsys',
'lbacsys',
'outln',
'sys$umf',
'dbsnmp',
'appqossys',
'dbsfwuser',
'ggsys',
'anonymous',
'ctxsys',
'dvsys',
'dvf',
'gsmadmin_internal',
'mdsys',
'olapsys',
'xdb',
'wmsys',
'gsmcatuser',
'mddata',
'sysbackup',
'remote_scheduler_agent',
'gsmuser',
'sysrac',
'gsmrootuser',
'si_informtn_schema',
'audsys',
'dip',
'ordplugins',
'syskm',
'orddata',
'oracle_ocm',
'sysdg',
'ordsys',
'remote_scheduler_age',
'spatial_csw_admin_us',
'exfsys',
'apex_public_user',
'spatial_csw_admin_us',
'spatial_wfs_admin_us',
'apex_030200',
'owbsys_audit',
'owbsys',
'flows_files',
'scott',
'mgmt_view',
'sysman',
'apex_040200',
'olpaydb',
'sysaux',
'apex_050000',
'dmsys',
'tsmsys',
'spatial_csw_admin_usr',
'spatial_wfs_admin_usr')
and a.cost >= 1000
order by cost desc
) where rownum <= 10;

prompt 'end===check db ####' checkjcsql ;

exit;
eof
fi


##verversion lower 11
if [ $value_version -le 11 ]
then
sqlplus / as sysdba <
set pagesize 9999
set linesize 300
col name for a20
col username for a20
col event for a60
col a2 for a30
col pid for a10
col group# for a10
col account_status for a20

prompt 'begin===check db name ####' checkjcsqldbname;
select a.name,to_char(a.created,'yyyy-mm-dd') dbname from v\$database a;
prompt 'check db status ####' ;
select to_char(a.startup_time,'yyyy-mm-dd hh24:mi:ss') startuptime,a.instance_name,a.status,a.version from gv\$instance a;


prompt 'check parameter ####' ;

col value_parameter for a200
select substr(a.inst_id||'||'||a.ksppinm||'||'||nvl(b.ksppstvl,'kong'),1,200) value_parameter
from x\$ksppi a,x\$ksppcv b
where a.indx = b.indx and a.ksppinm in('_gc_policy_time','_gc_undo_affinity','_px_use_large_pool','_undo_autotune','_optimizer_mjc_enabled','_optimizer_mjc_enabled')
union
select substr(a.inst_id||'||'||a.name||'||'||nvl(value,'kong'),1,200) value_parameter from gv\$parameter a
where a.name in('memory_target','open_cursors','open_links','open_links_per_instance','parallel_force_local','undo_retention','processes','db_files','sga_max_size','sga_target','shared_pool_size','pga_aggregate_target');


prompt 'check virus####' checkjcsqlvirus ;

select 'drop trigger '||owner||'."'||trigger_name||'";' from dba_triggers where trigger_name like 'dbms_%_internal%'
union all
select 'drop procedure '||owner||'."'||a.object_name||'";' from dba_procedures a where a.object_name like 'dbms_%_internal% '
union all
select 'drop procedure '||owner||'."'||object_name||'";' from dba_objects where object_name ='dbms_support_dbmonitorp'
union all
select 'drop trigger '||owner||'."'||trigger_name||'";' from dba_triggers where trigger_name ='dbms_support_dbmonitor';


prompt 'check asmgroup status ####' checkasmgroup ;

select group_number,name,round(((total_mb-free_mb)/total_mb)*100,2) dg_pent,total_mb,state,free_mb,type from v\$asm_diskgroup;

prompt 'check data_files ####' checkjcsqldatafiles ;

col file_name for a60
col tablespace_name for a20
col status for a10
col autoextensible for a10
select file_id,a.status,a.tablespace_name, a.autoextensible from dba_data_files a ;


prompt 'checktabspacelogging ####' checkjcsqltabspacelogging ;
set linesize 300;
col status for a20;
col tablespdace_name for a20;

select a.status,a.logging,a.force_logging,a.tablespace_name from dba_tablespaces a where a.contents <>'temporary';


prompt 'check ivalid object ####' checkjcsqlivalidobject ;
col owner for a10
col object_name for a50
col object_type for a20
col status for a10
select a.owner,a.object_name,a.object_type,a.status from dba_objects a where a.status<>'valid';



prompt 'checkunusableindex ####' checkjcsqlunusableindex ;
col owner for a15
col index_name for a30
col status for a10

select owner,index_name,status from dba_indexes where status ='unusable'
union
select index_owner,index_name,status from dba_ind_partitions where status ='unusable';


prompt 'user check####'

select username,
trunc(expiry_date - sysdate) days,
replace(account_status, '&', 'and') as account_status
from dba_users a
where a.username not in ('sys',
'system',
'xs$null',
'ojvmsys',
'lbacsys',
'outln',
'sys$umf',
'dbsnmp',
'appqossys',
'dbsfwuser',
'ggsys',
'anonymous',
'ctxsys',
'dvsys',
'dvf',
'gsmadmin_internal',
'mdsys',
'olapsys',
'xdb',
'wmsys',
'gsmcatuser',
'mddata',
'sysbackup',
'remote_scheduler_agent',
'gsmuser',
'sysrac',
'gsmrootuser',
'si_informtn_schema',
'audsys',
'dip',
'ordplugins',
'syskm',
'orddata',
'oracle_ocm',
'sysdg',
'ordsys',
'remote_scheduler_age',
'spatial_csw_admin_us',
'exfsys',
'apex_public_user',
'spatial_csw_admin_us',
'spatial_wfs_admin_us',
'apex_030200',
'owbsys_audit',
'owbsys',
'flows_files',
'scott',
'mgmt_view',
'sysman',
'apex_040200',
'olpaydb',
'sysaux',
'apex_050000',
'dmsys',
'tsmsys',
'spatial_csw_admin_usr',
'spatial_wfs_admin_usr')
and trunc(expiry_date - sysdate) <= 30;



prompt 'check db_cache hit####' checkjcsqldb_cachehit ;
select round(100-(phr.value-phrd.value-phrdl.value)/(cg.value dbbg.value)*100,2) prent
from v\$sysstat phr,v\$sysstat phrd,v\$sysstat phrdl,v\$sysstat cg,v\$sysstat dbbg
where phr.name='physical reads' and phrd.name='physical reads direct' and phrdl.name='physical reads direct (lob)'
and cg.name='consistent gets' and dbbg.name='db block gets';


prompt 'check libraty hit####' checkjcsqllibratyhit ;
select round(sum(pins -reloads)/sum(pins)*100,2) "library cache hit ratio"
from v\$librarycache;


prompt 'check latch hit####' checkjcsqllatchhit ;
select round((1-sum(misses immediate_misses)/sum(gets immediate_gets))*100,2) "latch hit ratio"
from v\$latch;


prompt 'histroye event####' checkjcsqlhistroyeevent ;
set linesize 300;
col event for a30;
select * from (
select
event,
count(1)
from dba_hist_active_sess_history
where sample_time>=sysdate-7
and event is not null
group by
event
having count(*) > 0
order by count(*) desc
) a where rownum<=10;

prompt 'sga size####' checkjcsqlsga_size ;
select a.sga_size,
a.sga_size_factor,
a.estd_db_time,
a.estd_db_time_factor,
a.estd_physical_reads,
inst_id
from gv\$sga_target_advice a;

prompt 'pga size####' checkjcsqlpga_size ;
select pga_target_for_estimate / 1024 / 1024 "pga(mb)",
pga_target_factor,
estd_pga_cache_hit_percentage,
estd_overalloc_count,
inst_id
from gv\$pga_target_advice;



prompt 'check arch mode####' checkjcsqlarchmode ;
select a.name,a.log_mode from v\$database a;

prompt 'check onlinelog size####' checkjcsqlonlinelogsize ;
select thread#,count(1),(select distinct(bytes) from v\$log where rownum=1) logsize from v\$log group by thread#;

prompt 'check log change####' checkjcsqllogchange ;
set linesize 300
column h00 format 999
column h01 format 999
column h02 format 999
column h03 format 999
column h04 format 999
column h05 format 999
column h06 format 999
column h07 format 999
column h08 format 999
column h09 format 999
column h10 format 999
column h11 format 999
column h12 format 999
column h13 format 999
column h14 format 999
column h15 format 999
column h16 format 999
column h17 format 999
column h18 format 999
column h19 format 999
column h20 format 999
column h21 format 999
column h22 format 999
column h23 format 999
column avg format 999
column day format a6

select to_char(first_time, 'mm/dd') day,
sum(decode(to_char(first_time, 'hh24'), '00', 1, 0)) h00,
sum(decode(to_char(first_time, 'hh24'), '01', 1, 0)) h01,
sum(decode(to_char(first_time, 'hh24'), '02', 1, 0)) h02,
sum(decode(to_char(first_time, 'hh24'), '03', 1, 0)) h03,
sum(decode(to_char(first_time, 'hh24'), '04', 1, 0)) h04,
sum(decode(to_char(first_time, 'hh24'), '05', 1, 0)) h05,
sum(decode(to_char(first_time, 'hh24'), '06', 1, 0)) h06,
sum(decode(to_char(first_time, 'hh24'), '07', 1, 0)) h07,
sum(decode(to_char(first_time, 'hh24'), '08', 1, 0)) h08,
sum(decode(to_char(first_time, 'hh24'), '09', 1, 0)) h09,
sum(decode(to_char(first_time, 'hh24'), '10', 1, 0)) h10,
sum(decode(to_char(first_time, 'hh24'), '11', 1, 0)) h11,
sum(decode(to_char(first_time, 'hh24'), '12', 1, 0)) h12,
sum(decode(to_char(first_time, 'hh24'), '13', 1, 0)) h13,
sum(decode(to_char(first_time, 'hh24'), '14', 1, 0)) h14,
sum(decode(to_char(first_time, 'hh24'), '15', 1, 0)) h15,
sum(decode(to_char(first_time, 'hh24'), '16', 1, 0)) h16,
sum(decode(to_char(first_time, 'hh24'), '17', 1, 0)) h17,
sum(decode(to_char(first_time, 'hh24'), '18', 1, 0)) h18,
sum(decode(to_char(first_time, 'hh24'), '19', 1, 0)) h19,
sum(decode(to_char(first_time, 'hh24'), '20', 1, 0)) h20,
sum(decode(to_char(first_time, 'hh24'), '21', 1, 0)) h21,
sum(decode(to_char(first_time, 'hh24'), '22', 1, 0)) h22,
sum(decode(to_char(first_time, 'hh24'), '23', 1, 0)) h23,
count(*) total,
inst_id
from (select rownum rn, first_time,inst_id
from gv\$log_history
where first_time > sysdate - 7
and first_time > add_months(sysdate, -1)
order by inst_id,first_time)
group by inst_id,to_char(first_time, 'mm/dd')
order by inst_id,min(rn);


prompt 'session use####' checkjcsqlsessionuse ;
select inst_id,((select count(1) from gv\$session)/a.value)*100 huihua_prent from gv\$parameter a where a.name = 'processes';
col value for a30;
select a.value from v\$parameter a where a.name = 'processes';


prompt 'scn headroom####' checkjcsqlscnheadroom ;
select to_char(tim,'yyyy-mm-dd:hh24:mi:ss') tim,scn,round((chk16kscn-scn)/24/3600/16/1024,1) headroom
from (select tim, scn, ((((to_number(to_char(tim,'yyyy'))-1988)*12*31*24*60*60) ((to_number(to_char(tim,'mm'))-1)*31*24*60*60) (((to_number(to_char(tim,'dd'))-1))*24*60*60) (to_number(to_char(tim,'hh24'))*60*60) (to_number(to_char(tim,'mi'))*60) (to_number(to_char(tim,'ss'))) ) * (16*1024)) chk16kscn from (select sysdate tim,dbms_flashback.get_system_change_number scn from dual)) order by tim;


prompt 'log mode####' checkjcsqlarchivelogcheck ;
select a.log_mode,a.force_logging from v\$database a;



prompt 'backup qingkuang####' checkjcsqlbackupqingkuang ;
set linesize 300
col operation for a15
col status for a15;
col st for a30
col en for a30
select /* rule*/ a.sid,a.operation,a.status,a.object_type,to_char(a.start_time,'yyyy-mm-dd hh24:mi:ss') st,to_char(a.end_time,'yyyy-mm-dd hh24:mi:ss') et from v\$rman_status a where operation='backup' and a.start_time>sysdate-7 order by st desc;


prompt 'controlfile autuback####' checkjcsqlcontrolfileautuback ;
col name for a30
col value for a20
select name,value,inst_id from gv\$rman_configuration a where a.name='controlfile autobackup';

prompt 'controlfile twopiece####' checkjcsqlcontrolfiletwopiece ;
show parameter control_files;

prompt 'auto_job_check####' checkjcsqlautojob ;
column client_name format a35
select client_name,status from dba_autotask_client;

prompt 'adg or not####' checkjcsqladgornot ;
col name for a40;
col value for a20;
select a.name,a.value from v\$parameter a where a.name like 'log_archive%';

prompt 'adg processstatus####' checkjcsqladgprocessstatus;
select process,status,pid, group# , resetlog_id,thread#,sequence# from gv\$managed_standby;

prompt 'adg tongbu yanchi####' checkjcsqladgtongbuyanchi ;
set lines 300 pages 50
set heading on
set verify off
col name for a30
col value for a30
col time_computed for a20
col datum_time for a20 heading 'last_received_time'
col inst_id for 99 heading 'id'
break on inst_id
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select inst_id,name,value,time_computed,datum_time,sysdate from gv\$dataguard_stats order by inst_id;



prompt 'adg archive_dest####' checkjcsqladgarchive_dest ;
col dest_name for a40
set lines 300
set pages 900
select dest_name,status,error from v\$archive_dest;

prompt 'adg database status####' checkjcsqladdatabasestatus ;
set lines 200
col database_role for a18
col protection_mode for a20
col protection_level for a20
col flashback_on for a10
col name for a10
col switchover_status for a15
select name,database_role,protection_mode,protection_level,switchover_status,flashback_on,open_mode from v\$database;


prompt 'db size####';
select 'db_size',round(sum(a.bytes)/1024/1024/1024,2) dbsize from dba_segments a
union
select 'db_file_size',round(sum(decode(t.maxbytes,0,t.bytes,t.maxbytes))/1024/1024/1024,2) dbsize from dba_data_files t;
prompt 'db size end####';


prompt 'stattype_locked####' checkjcsqlstattype_locked ;

col owner for a20
col table_name for a30
select distinct a.owner,a.table_name,a.stattype_locked from dba_tab_statistics a where a.owner not in (
'sys',
'system',
'xs$null',
'ojvmsys',
'lbacsys',
'outln',
'sys$umf',
'dbsnmp',
'appqossys',
'dbsfwuser',
'ggsys',
'anonymous',
'ctxsys',
'dvsys',
'dvf',
'gsmadmin_internal',
'mdsys',
'olapsys',
'xdb',
'wmsys',
'gsmcatuser',
'mddata',
'sysbackup',
'remote_scheduler_agent',
'gsmuser',
'sysrac',
'gsmrootuser',
'si_informtn_schema',
'audsys',
'dip',
'ordplugins',
'syskm',
'orddata',
'oracle_ocm',
'sysdg',
'ordsys',
'remote_scheduler_age',
'spatial_csw_admin_us',
'exfsys',
'apex_public_user',
'spatial_csw_admin_us',
'spatial_wfs_admin_us',
'apex_030200',
'owbsys_audit',
'owbsys',
'flows_files',
'scott',
'mgmt_view',
'sysman',
'apex_040200',
'olpaydb',
'sysaux',
'apex_050000',
'dmsys',
'tsmsys',
'spatial_csw_admin_usr',
'spatial_wfs_admin_usr')
and a.stattype_locked is not null;


prompt 'table_index_degree####' checkjcsqlstattable_indexdegree;
select count(1),'table' as deg from dba_tables a where a.degree>'1' having count(1)>0
union
select count(1),'index' from dba_indexes b where b.degree>'1'having count(1)>0;

prompt 'parse hard count####' checkjcsqlparsehardcount;

select a.instance_number,
to_char(end_interval_time, 'yyyy-mm-dd-hh24:mi') start_time,
round((case
when round((b.value - a.value) /
round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2),
2) < 0 then
0
else
round((b.value - a.value) /
round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2),
2)
end) / (round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2)),
2) "parsehard/s",
a.snap_id,
round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2) time_inter
from dba_hist_sysstat a,
dba_hist_sysstat b,
dba_hist_snapshot c
where a.stat_name = 'parse count (hard)'
and b.stat_name = 'parse count (hard)'
and a.dbid = b.dbid
and a.dbid = (select dbid from v\$database)
and a.instance_number = b.instance_number
and c.instance_number = b.instance_number
and a.snap_id = c.snap_id
and b.snap_id = c.snap_id 1
and c.end_interval_time >= sysdate - 7
order by a.instance_number, a.snap_id asc;


prompt 'check db time####' checkjcsqldbtime

select a.instance_number,to_char(end_interval_time,'yyyy-mm-dd-hh24:mi') start_time,
round((case
when round((b.value-a.value)/1000000/
round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2),2)< 0 then
0
else
round((b.value-a.value)/1000000/
round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2),2)
end)/(round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
- to_date(to_char(c.begin_interval_time,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,2)),2)
"dbtime/s",
a.snap_id,
round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
2) time_inter
from dba_hist_sys_time_model a,dba_hist_sys_time_model b ,dba_hist_snapshot c
where a.stat_name='db time' and b.stat_name='db time'
and a.dbid=b.dbid and a.dbid=(select dbid from v\$database)
and a.instance_number= b.instance_number and c.instance_number=b.instance_number
and a.snap_id=c.snap_id
and b.snap_id=c.snap_id 1
and c.end_interval_time>=sysdate-7
order by a.instance_number,a.snap_id asc;



prompt 'check logicalreads####'checkjcsqllogicalreads
select a.instance_number,
to_char(c.end_interval_time, 'yyyy-mm-dd-hh24:mi') start_time,
round((case
when round((b.value - a.value)*8192/1024/1024/ round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1),
1) < 0 then
0
else
round((b.value - a.value)*8192/1024/1024/ round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1),
1)
end) / (round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1)),
1) "logicalreads_m/s",
a.snap_id,
round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1) time_inter
from dba_hist_sysstat a, dba_hist_sysstat b, dba_hist_snapshot c
where a.stat_name = 'session logical reads'
and b.stat_name = 'session logical reads'
and a.dbid = b.dbid
and a.dbid = (select dbid from v\$database)
and a.instance_number = b.instance_number
and c.instance_number = b.instance_number
and a.snap_id = c.snap_id
and b.snap_id = c.snap_id 1
and c.end_interval_time >= sysdate - 7
order by a.instance_number, a.snap_id asc;



prompt 'check physicalreads####'checkjcsqlphysicalreads

select a.instance_number,
to_char(c.end_interval_time, 'yyyy-mm-dd-hh24:mi') start_time,
round((case
when round((b.value - a.value)*8192/1024/1024/ round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1),
1) < 0 then
0
else
round((b.value - a.value)*8192/1024/1024/ round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1),
1)
end) / (round((to_date(to_char(c.end_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1)),
1) "physicalreads_m/s",
a.snap_id,
round((to_date(to_char(c.end_interval_time, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') -
to_date(to_char(c.begin_interval_time,
'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss')) * 24 * 60,
1) time_inter
from dba_hist_sysstat a, dba_hist_sysstat b, dba_hist_snapshot c
where a.stat_name = 'physical reads'
and b.stat_name = 'physical reads'
and a.dbid = b.dbid
and a.dbid = (select dbid from v\$database)
and a.instance_number = b.instance_number
and c.instance_number = b.instance_number
and a.snap_id = c.snap_id
and b.snap_id = c.snap_id 1
and c.end_interval_time >= sysdate - 7
order by a.instance_number, a.snap_id asc;

prompt 'check tablespace ####' checkjcsqltablespace ;
col status for a10
col tablespace_name for a15
col size(m) for a15
col used for a15
col max_pct for 99.99
col pct for 99.99

select d.tablespace_name,
trunc(a.bytes / a.tb_sp_maxsize * 100, 2) max_pct,
trunc((a.bytes - f.bytes) / a.bytes * 100, 4) now_pct2,
trunc(a.tb_sp_maxsize-a.bytes, 2) "valid_extend_space(g)",
trunc(a.tb_sp_maxsize, 2) tb_maxsize,
trunc(a.bytes, 2) "size(g)",
trunc((a.bytes - f.bytes) , 2) "used(g)",
trunc(f.bytes , 2) "freesize(g)",
d.status
from sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) / 1024 / 1024 / 1024 bytes,
round(sum(decode(maxbytes, 0, bytes, maxbytes)) / 1024 / 1024 / 1024,
4) tb_sp_maxsize
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) / (1024 * 1024 * 1024) 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 d.tablespace_name not in ('temp')
order by max_pct desc, tablespace_name;

prompt 'sql cost####' checkjcsqlcost ;

col operation for a20
col object_name for a50
col machine for a20
col operation for a20
col program for a50
set pages 9999
col sql_text for a100
col object_name for a30


select *
from ( select /* rule */
distinct a.sql_id,
a.object_name,
a.plan_hash_value,
c.sql_text,
(select max(cost)
from gv\$sql_plan
where sql_id = a.sql_id
and plan_hash_value = a.plan_hash_value) cost
from gv\$sql_plan a, dba_tables b, gv\$sql c
where a.object_name = b.table_name
and a.operation = 'table access'
and a.options = 'full'
and a.object_type = 'table'
and a.sql_id = c.sql_id
and b.owner not in ('sys',
'system',
'xs$null',
'ojvmsys',
'lbacsys',
'outln',
'sys$umf',
'dbsnmp',
'appqossys',
'dbsfwuser',
'ggsys',
'anonymous',
'ctxsys',
'dvsys',
'dvf',
'gsmadmin_internal',
'mdsys',
'olapsys',
'xdb',
'wmsys',
'gsmcatuser',
'mddata',
'sysbackup',
'remote_scheduler_agent',
'gsmuser',
'sysrac',
'gsmrootuser',
'si_informtn_schema',
'audsys',
'dip',
'ordplugins',
'syskm',
'orddata',
'oracle_ocm',
'sysdg',
'ordsys',
'remote_scheduler_age',
'spatial_csw_admin_us',
'exfsys',
'apex_public_user',
'spatial_csw_admin_us',
'spatial_wfs_admin_us',
'apex_030200',
'owbsys_audit',
'owbsys',
'flows_files',
'scott',
'mgmt_view',
'sysman',
'apex_040200',
'olpaydb',
'sysaux',
'apex_050000',
'dmsys',
'tsmsys',
'spatial_csw_admin_usr',
'spatial_wfs_admin_usr')
and a.cost >= 10000
order by cost desc
) where rownum <= 10;


prompt 'end===check db ####' checkjcsql ;

exit;
eof

fi

由于上述脚本也是本人十多年的经验总结，但毕竟可能存在使用方法不当的地方，如有错误，请各位大侠在评论区指出，便于进一步修改完善后公布。

四、这个文件执行后，会输入日志，从客户现场拿到日志后，才能用自动化巡检工具进行离线报告编写。