mysql5.7.20巡检脚本_ORACLE数据库巡检脚本.sql

缺乏、安全感 2022-10-31 12:13 328阅读 0赞

set verify off feedback off serveroutput on size 1000000 pages

10000

set trimout on trimspool on linesize 2000

set heading on

col OBJECT\_NAME for a30

col event for a30

col script for a100

col aux\_name for a20

col member for a50

col 是否安装 for a30

col segment\_name for a30

col segment\_type for a15

col owner for a15

col 用户名 for a15

col username for a15

col 表空间名 for a10

col default\_tablespace for a15

col temporary\_tablespace for a15

col file\_name for a50

col 约束类型 for a10

col 自动扩展 for a8

col id for 99

col account\_status for a20

col group\# for 99

col archived for a8

col PARAMETER for a40

col 选件名称 for a40

COL 参数名称 FOR A30

col 参数值 for a55

spool D:\\whx123abc@163.com.txt

PROMPT

PROMPT 一、主机信息

\--Windows NTsysteminfo

\--host top

\--host vmstat 4 5

\--host iostat

\-- HP如果装了glance建议使用glance，IBM如果装了svmon建议使用svmon

\--主机名

\--$ uname -a

\--主机型号

\--HP\# model

\--内核版本

\--HP\# getconf KERNEL\_BITS

\--AIX\# /usr/sbin/bootinfo -K

\--Solaris\# isainfo -kv

\--操作系统版本

\--AIX$ oslevel -r

\--HP, Linux, Solaris$ uname -a

\--Tru64$ /usr/sbin/sizer -v

\--磁盘信息

\--HP$ bdf

\--others$ df -k

\--物理内存

\--AIX$ /usr/sbin/lsattr -E -l sys0 -a realmem, lsattr -El

mem0

\--Linux$ /usr/sbin/dmesg | grep "Physical:"

\--HP$ grep MemTotal /proc/meminfo

\--Solaris$ /usr/sbin/prtconf | grep "Memory size"

\--Tru64$ /bin/vmstat -P | grep "Total Physical Memory"

\--内存利用率

\--$ vmstat 20 5

\--交换区大小

\--AIX $ /usr/sbin/lsps -a

\--HP$ /usr/sbin/swapinfo -a, swapinfo -m

\--Linux, Tru64$ /sbin/swapon -s

\--Solaris$ /usr/sbin/swap -1

\--CPU 信息

\--Solaris\# psrinfo -v|grep "Status of processor"|wc -l

\--AIX\# lsdev -Cc processor, lsattr -El proc0

\--其他\# lsdev -C|grep Process|wc -l

\--CPU利用率

\--Solaris $ sar -u -M 20 5

\--Others$ sar -u 20 5

\--网络情况

\--AIX$ netstat -in, netstat -rn

create or replace procedure whx123\_print\_table( p\_query in

varchar2 )

AUTHID CURRENT\_USER

l\_theCursor  integer default dbms\_sql.open\_cursor;

l\_columnValue  varchar2(4000);

l\_status

integer;

l\_descTbl

dbms\_sql.desc\_tab;

l\_colCnt

number;

begin

execute

immediate

'alter

session set

nls\_date\_format=''yyyy-mm-dd

hh24:mi:ss'' ';

dbms\_sql.parse(  l\_theCursor,

p\_query, dbms\_sql.native );

dbms\_sql.describe\_columns

(

l\_theCursor, l\_colCnt, l\_descTbl );

for i in

1 .. l\_colCnt loop

dbms\_sql.define\_column

(l\_theCursor, i,

l\_columnValue, 4000);

end

loop;

l\_status

:= dbms\_sql.execute(l\_theCursor);

while (

dbms\_sql.fetch\_rows(l\_theCursor) > 0 ) loop

for i in 1 .. l\_colCnt

loop

dbms\_sql.column\_value

( l\_theCursor, i, l\_columnValue );

dbms\_output.put\_line

( rpad( l\_descTbl(i).col\_name, 30 )

|| ': '

l\_columnValue );

end loop;

dbms\_output.put\_line(

'-----------------' );

end

loop;

execute

immediate

'alter session set

nls\_date\_format=''dd-MON-rr'' ';

exception

when

others then

execute immediate

'alter

session set nls\_date\_format=''dd-MON-rr'' ';

raise;

end;

PROMPT

PROMPT 二、数据库版本信息

set heading off

select \* from v$version;

set heading on

PROMPT

PROMPT 三、数据库信息

exec whx123\_print\_table('select \* from v$database')

PROMPT

PROMPT 四、数据库实例信息

exec whx123\_print\_table('select \* from v$instance')

alter session set nls\_date\_format = 'yyyy-mm-dd hh24:mi:ss'

;

PROMPT

PROMPT 五、数据库选件信息

SELECT PARAMETER "选件名称",VALUE "是否安装"  FROM

V$OPTION ;

col 参数值 for a55

PROMPT

PROMPT 六、数据库非缺省初始化参数

SELECT name "参数名称",value "参数值" FROM V$PARAMETER where

ISDEFAULT='FALSE' order by name;

col value for a30

COL NAME FOR A60

PROMPT

PROMPT 七、控制文件

SELECT \* FROM V$controlfile;

PROMPT

PROMPT 八、日志组信息

col member for a30

select

a.thread\#,

a.group\#,

b.member,

b.status,

a.bytes/1024/1024

from v$Log a,

v$logfile b

where

a.group\#=b.group\#

order by thread\#

PROMPT

PROMPT 九、数据文件

SELECT FILE\_ID ID,FILE\_NAME,TABLESPACE\_NAME

"表空间名",BYTES,autoextensible "自动扩展"

FROM dba\_data\_files order by

tablespace\_name;

PROMPT

PROMPT 十、临时文件

SELECT FILE\_ID ID,FILE\_NAME,TABLESPACE\_NAME

"表空间名",BYTES,autoextensible "自动扩展"

FROM dba\_temp\_files;

PROMPT

PROMPT 十一、表空间信息

set heading on

col "表空间名" for a20

col "表空间使用率%" heading '使用率' for 999

col "表空间总量" heading '总量' for 999999999999.00

col "剩余空间大小M" heading '剩余' for 999999999999.00

col "管理模式" heading '管理模式' for a10

select

a.tablespace\_name "表空间名",a.total "表空间总量",

b.free "剩余空间大小M" ,

ceil((1-b.free/a.total)\*100)

"表空间使用率%",

c.extent\_management "管理模式"

from

(select

tablespace\_name,

sum(nvl(bytes,0))/1024/1024

total

from

dba\_data\_files

group by

tablespace\_name

) a,

(select

tablespace\_name,

sum(nvl(bytes,0))/1024/1024

free

from

dba\_free\_space

group by

tablespace\_name

) b ,

dba\_tablespaces c

where

a.tablespace\_name=b.tablespace\_name

and a.tablespace\_name=c.tablespace\_name

order by

(1-b.free/a.total)\*100 desc

prompt 十二、表空间空闲块情况

prompt -----查找到很高的值，使用合并命令仍有大量空闲块时，考虑转化为本地管理

select tablespace\_name

,count(\*) cnt

from

dba\_free\_space

group by tablespace\_name

order by cnt desc

PROMPT 十三、回滚段空间配置

col segment\_name heading 'Segment' for a8

col tablespace\_name heading 'TBS' for a5

col INITIAL\_EXTENT heading 'Initial' for 9999999999

col NEXT\_EXTENT  heading

'Next'  for 9999999999

col Min\_extents  heading

'Min'  for 99

col Max\_extentsheading 'Max'for 99999

col ds.bytes/1024/1024 heading 'Bytes(M)' for 9999.00

col status for a6

select

rb.segment\_name,

rb.INITIAL\_EXTENT,

rb.NEXT\_EXTENT,

rb.MIN\_EXTENTS,

rb.MAX\_EXTENTS,

rs.optsize,

rb.tablespace\_name,

rs.status,

ds.bytes/1024/1024

from dba\_rollback\_segs rb,

v$rollstat rs,

dba\_segments

where

rb.segment\_id=rs.usn

and rb.segment\_name=ds.segment\_name

PROMPT

PROMPT 十四、NLS参数设置

SELECT \* FROM NLS\_Database\_Parameters;

PROMPT

PROMPT 十五、数据库用户安全性

set pages 100 lines 132

column  ACCOUNT\_STATUS heading 'status' format

column usernameheading 'User'format a10

column default\_tablespaceheading 'Default'format a12

column temporary\_tablespaceheading 'Temporary'format a12

column granted\_roleheading 'Granted Roles'format a27

col  profile for a8

clear breaks

break on username on account\_status on default\_tablespace on

temporary\_tablespace on profile

select username,

ACCOUNT\_STATUS,

default\_tablespace,

temporary\_tablespace,

granted\_role

from dba\_users u,dba\_role\_privs r

where u.username = r.grantee

order by username

PROMPT 十六、最近三个月日志归档频繁度

select to\_char(first\_time,'yyyy-mm-dd'),thread\#,count(\*)

from v$log\_history

where first\_time>sysdate-90

group by to\_char(first\_time,'yyyy-mm-dd'),thread\#

having count(\*)>20

order by thread\#

PROMPT

PROMPT 十七、失效对象

SELECT OWNER "用户名",OBJECT\_NAME,OBJECT\_TYPE,CREATED,LAST\_DDL\_TIME

FROM DBA\_OBJECTS WHERE STATUS = 'INVALID'

ORDER BY OWNER,OBJECT\_NAME;

PROMPT

PROMPT 十八、禁用的触发器

SELECT owner "用户名",trigger\_name,status FROM dba\_triggers where

status = 'DISABLED';

PROMPT

PROMPT 十九、禁用的约束

SELECT owner

"用户名",constraint\_name,decode(constraint\_type,'P','主键','F','外键',constraint\_type)

"约束类型",status

FROM dba\_constraints where

status = 'DISABLED';

PROMPT

PROMPT 二十、无效的索引

SELECT owner "用户名",index\_name,status FROM dba\_indexes where

status = 'UNUSABLE';

prompt 二十一、表和索引在同一个空间的对象

col owner for a12

col tname for a26

col iname for a26

col tbsname for a15

clear breaks

break on owner on tbsname on tname

select a.owner,

a.tablespace\_name tbsname ,

a.table\_name tname,

b.index\_name iname

from

dba\_tables a

,dba\_indexes b

where

a.tablespace\_name=b.tablespace\_name

and

b.table\_name=a.table\_name

and

a.owner=b.owner

and

b.owner NOT in ('SYS','SYSTEM')

order  by owner

PROMPT

PROMPT 二十二、需要绑定变量的SQL(自数据库启动以来)

drop table t1 ;

create table whx123\_t1 as select sql\_text from v$sqlarea;

alter table whx123\_t1 add sql\_text\_wo\_constants

varchar2(1000);

create or replace function

whx123\_remove\_constants( p\_query in varchar2 ) return

varchar2

l\_query

long;

l\_char

varchar2(2);

l\_in\_quotes boolean default FALSE;

begin

for i in

1 .. length( p\_query )

loop

l\_char :=

substr(p\_query,i,1);

if ( l\_char = '''' and

l\_in\_quotes )

then

l\_in\_quotes := FALSE;

elsif ( l\_char = '''' and NOT

l\_in\_quotes )

then

l\_in\_quotes := TRUE;

l\_query := l\_query || '''\#';

end if;

if ( NOT l\_in\_quotes )

then

l\_query := l\_query || l\_char;

end if;

end

loop;

l\_query

:= translate( l\_query, '0123456789', '@@@@@@@@@@' );

for i in

0 .. 8 loop

l\_query := replace( l\_query,

lpad('@',10-i,'@'), '@' );

l\_query := replace( l\_query,

lpad(' ',10-i,' '), ' ' );

end

loop;

return

upper(l\_query);

end;

update whx123\_t1 set sql\_text\_wo\_constants =

whx123\_remove\_constants(sql\_text);

commit;

COL sql\_text\_wo\_constants FOR A60

COL sql\_text FOR A60

select count(\*) "硬解析次数",sql\_text\_wo\_constants

from whx123\_t1

group by sql\_text\_wo\_constants

having count(\*) > 100

order by 1 desc;

PROMPT 二十三 与应用相关

PROMPT

PROMPT 1、最消耗内存的十个SQL

select \* from (

select

trunc(t.BUFFER\_GETS/t.EXECUTIONS) "buffer/exes",

t.EXECUTIONS "执行次数",t.SQL\_TEXT

from

v$sql t  where t.EXECUTIONS > 0

order by 1 desc )

where rownum <= 10;

PROMPT

PROMPT 2、TOP 5 等待

col event for a28

select EVENT,TOTAL\_WAITS, TOTAL\_TIMEOUTS, TIME\_WAITED,

AVERAGE\_WAIT  from (

select \* from v$system\_event where event not like

'%rdbms%' and event not like '%message%' and

event not like 'SQL\*Net%' order by total\_waits desc

)

where rownum <=5 ;

PROMPT  3、磁盘读最多得SQL

prompt disk\_reads一般要小于300才好，这里这样的语句太多了所以设置的高

select

st.sql\_text

from

v$sql s,

v$sqlarea st

where

s.address=st.address

and

s.hash\_value=st.hash\_value

and s.disk\_reads >

300

order by s.disk\_reads

asc

PROMPT  4、磁盘排序最严重的SQL

select

sess.username,

sql.sql\_text,

sort1.blocks

from v$session sess,

v$sqlarea sql,

v$sort\_usage sort1

where

sess.serial\# = sort1.session\_num

and sort1.sqladdr = sql.address

and sort1.sqlhash = sql.hash\_value

and sort1.blocks > 200

order by sort1.blocks asc

PROMPT

PROMPT 5、SGA命中率

PROMPT cache hit要高于90%--library要高于95%--rowcache要高于85%

select \* from

(SELECT 1 - (phy.value-phyd.value)/( (cur.value +

con.value-phyd.value)) "数据缓冲区命中率"

FROM v$sysstat cur,

v$sysstat con, v$sysstat phy,v$sysstat phyd

WHERE cur.name = 'db block

gets' AND con.name = 'consistent gets'

AND

phy.name = 'physical reads' and phyd.NAME = 'physical reads direct'

(select sum(pinhits)/sum(pins) "SQL共享池命中率" from v$librarycache)

(select 1-SUM(GETMISSES)/SUM(GETS) "数据字典命中率" from

V$ROWCACHE)

;

prompt 6、内存中排序

select a.value "Disk Sorts",

b.value "Memory Sorts",

round((100 \* b.value) /

decode((a.value + b.value), 0, 1, (a.value +

b.value)),

2) "Pct Memory Sorts"

from v$sysstat a, v$sysstat

where a.name = 'sorts (disk)'

and b.name = 'sorts

(memory)'

PROMPT

PROMPT 二十四、数据库大小估算

select nvl(t.owner,'合计：') "用户名",

to\_char(sum(bytes),'999,999,999,999,999')

"占用的空间(字节)"

from dba\_segments

group by rollup(t.owner)

order by 2  ;

PROMPT 如果使用逻辑导出(exp备份)，其大小约等于合计大小。

PROMPT

PROMPT 二十五、意见和建议

COL 下列文件建议取消自动扩展 FOR A60

select file\_name "下列文件建议取消自动扩展",BYTES "文件大小"

from

(SELECT file\_name,BYTES,autoextensible FROM

dba\_data\_files

UNION ALL

SELECT file\_name,BYTES,autoextensible FROM DBA\_TEMP\_FILES)

t where

ORDER BY BYTES DESC

;

set heading off

select '参数timed\_statistics建议设置为true。当前值为FALSE。' from v$parameter

where name = 'timed\_statistics' and value = 'FALSE';

select '参数optimizer\_index\_caching建议设置为90。当前值为0。' from

v$parameter where name = 'optimizer\_index\_caching' and value =

'0';

select '参数optimizer\_index\_cost\_adj建议设置为10～50。当前值为100。' from

v$parameter where name = 'optimizer\_index\_cost\_adj' and value =

'100';

select '参数db\_file\_multiblock\_read\_count建议设置小一些。当前值为：'||value

from v$parameter

where name = 'db\_file\_multiblock\_read\_count'

and value > '16';

select '在Oracle9i中，建议PGA的管理方式为AUTO。' from v$parameter

where name = 'workarea\_size\_policy' and value <>

'AUTO';

select '在Oracle9i中，建议UNDO的管理方式为AUTO' from v$parameter

where name = 'undo\_management' and value <> 'AUTO';

set heading on

select username "默认表空间不应为SYSTEM的用户" from dba\_users

where default\_tablespace = 'SYSTEM' and

username not in ('SYS', 'OUTLN','SYSTEM');

select username "临时表空间不应为SYSTEM的用户" from dba\_users where

temporary\_tablespace = 'SYSTEM';

select username "临时表空间不应为PERFANET的用户" from dba\_users where

temporary\_tablespace in

(select tablespace\_name  from

dba\_tablespaces where contents = 'PERMANENT' );

PROMPT

PROMPT 未使用的索引(8i及以下使用，需sys用户运行)

select distinct b.owner, b.segment\_name

from x$bh a, dba\_extents

where

b.file\_id=a.dbarfil

and a.dbablk between

b.block\_id

and

b.block\_id+blocks-1

and

segment\_type='INDEX'

and b.owner not in

('SYS','SYSTEM')

and not exists (select

instr(BANNER,'9i') from v$version WHERE ROWNUM<=1);

PROMPT

PROMPT 系统表空间中非SYS的对象

select OWNER,SEGMENT\_NAME,SEGMENT\_TYPE--,

\--decode(segment\_type,

\--'TABLE','alter table

'||OWNER||'.'||SEGMENT\_NAME||' MOVE TABLESPACE

&'||'TABLESPACE;' ,

\--'INDEX','alter index

'||OWNER||'.'||SEGMENT\_NAME||' REBUILD TABLESPACE

&'||'TABLESPACE NOLOGGING;',

\--null) SCRIPT

from dba\_segments t

where t.tablespace\_name = 'SYSTEM'

AND  OWNER

NOT IN ('SYS','OUTLN','SYSTEM','WMSYS');

drop function whx123\_remove\_constants ;

drop table whx123\_t1;

drop procedure whx123\_print\_table;

PROMPT

spool off

\--ed &report\_name

mysql5.7.20巡检脚本_ORACLE数据库巡检脚本.sql

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关巡检机器人的实施步骤

相关 oracle巡检脚本

相关 mysql5.7.20巡检脚本_ORACLE数据库巡检脚本.sql

相关 oracle巡,ORACLE巡检表

相关 oracle数据库表空间巡检

相关 Oracle数据库巡检模版

相关 MySQL基本巡检操作

相关 Oracle JDE系统日常维护和巡检

相关 ERP系统健康巡检

相关 Oracle 数据库日常巡检