Oracle数据库常用脚本（三）

archive log list;

set pagesize 100select a_date,a_count from (select to_char(first_time,'YYYY-MM-DD') a_date,count(*) a_count from gv$log_historygroup by to_char(first_time,'YYYY-MM-DD')order by 1 desc) where rownum<=31;

set line 300set pagesize 1000SELECT TRUNC(first_time) "Date",TO_CHAR(first_time, 'Dy') "Day",COUNT(1) "Total",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '00', 1, 0)) "h0",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '01', 1, 0)) "h1",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '02', 1, 0)) "h2",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '03', 1, 0)) "h3",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '04', 1, 0)) "h4",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '05', 1, 0)) "h5",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '06', 1, 0)) "h6",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '07', 1, 0)) "h7",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '08', 1, 0)) "h8",SUM(DECODE(TO_CHAR(first_time, 'hh24'), '09', 1, 0)) "h9",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",ROUND(COUNT(1) / 24, 2) "Avg"FROM gv$log_historyWHERE thread# = inst_idGROUP BY TRUNC(first_time), TO_CHAR(first_time, 'Dy')ORDER BY 1 desc;

lsnrctl statuslsnrctl status listener_scan1

ls -lrth $ORACLE_HOME/network/log/listener.log

cd /oracle/grid/diag/tnslsnr/cjc-db-01/listener/trace

grep "HOST=.*establish.*\* 0" listener.log | awk -F'*' '{match($3,/[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/); ip = substr($3,RSTART,RLENGTH);cnt[ip]+=1;last[ip]=$1;}END {for (i in cnt) printf "%-16s %9s %19s\n",i,cnt[i],last[i];}' | sort -k 1

set line 300col name for a10col compatibility for a10select group_number,name,block_size,total_mb,free_mb,(1-(free_mb/total_mb))*100 used,type,compatibility,voting_filesfrom v$asm_diskgroup;

set line 300col CREATE_DATE for a10col name for a15col path for a20set pagesize 300select GROUP_NUMBER,DISK_NUMBER,STATE,OS_MB,TOTAL_MB,FREE_MB,NAME,PATH,CREATE_DATE,VOTING_FILEfrom v$asm_disk order by 1,2;

ocrcheck -configocrcheck

ocrcheck -local

crsctl query css votedisk

oifcfg getif

srvctl config database -d dah