巡检小脚本 - 墨天轮文档

巡检小脚本

韩啸

654

3页

44次

2021-07-26

免费下载

#!/bin/bash

#README

#本脚本用于巡检时生成 insert 语句保存表空间、磁盘空间信息（ASM 待开发）。

#执行脚本前，提前使用 touch 创建 tbs.sql、disk.sql。

#Usage:./insert.sh [项目<tbs/disk/#asm>] [DBNAME] [HOSTS]

################################################################################

#################

DBNAME=$2

HOSTS=$3

tbsfile='tbs.sql'

diskfile='disk.sql'

IFS=','

###输出表空间信息 insert 语句

if [ "$1" = "tbs" ]; then

#提取表空间信息

sqlplus -S '/AS SYSDBA' <<EOF

SET ECHO OFF

SET HEADING OFF

SET LINESIZE 180

SET PAGESIZE 50000

SET TIMING OFF

SET TRIMOUT ON

SET TRIMSPOOL ON

SET VERIFY OFF

CLEAR COLUMNS

CLEAR BREAKS

CLEAR COMPUTES

COLUMN status FORMAT a9

COLUMN name FORMAT a25

COLUMN type FORMAT a15

COLUMN extent_mgt FORMAT a10

COLUMN segment_mgt FORMAT a10

COLUMN ts_size FORMAT 9,999,999

COLUMN used FORMAT 9,999,999

COLUMN free FORMAT 9,999,999

COLUMN pct_used FORMAT 999

COLUMN PCT_FREE FORMAT 999

SPOOL oratbs.csv

SELECT

D.STATUS STATUS

, D.TABLESPACE_NAME NAME

, D.CONTENTS TYPE

, D.EXTENT_MANAGEMENT EXTENT_MGT

, D.SEGMENT_SPACE_MANAGEMENT SEGMENT_MGT

, NVL(A.BYTES, 0)/1024/1024 TS_SIZE

, ROUND(A.MAXBYTES/1048576) MAX_MB

, ROUND(NVL(A.BYTES - NVL(F.BYTES, 0), 0)/1024/1024,2) USED

, ROUND(F.BYTES/1048576) FREE_MB

, NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0) PCT_USED

, ROUND(F.BYTES/A.BYTES * 100 ,2) PCT_FREE

, ROUND((A.MAXBYTES-A.BYTES+F.BYTES)/ A.MAXBYTES * 100,2) MAX_PCT_FREE

FROM

SYS.DBA_TABLESPACES D

, ( SELECT TABLESPACE_NAME, SUM(BYTES) BYTES,SUM(DECODE(MAXBYTES, 0, BYTES,

MAXBYTES)) MAXBYTES

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME

) A

, ( SELECT TABLESPACE_NAME, SUM(BYTES) 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 NOT (

D.EXTENT_MANAGEMENT LIKE 'LOCAL'

AND

D.CONTENTS LIKE 'TEMPORARY'

)

UNION ALL

SELECT

D.STATUS STATUS

, D.TABLESPACE_NAME NAME

, D.CONTENTS TYPE

, D.EXTENT_MANAGEMENT EXTENT_MGT

, D.SEGMENT_SPACE_MANAGEMENT SEGMENT_MGT

, NVL(A.BYTES, 0)/1024/1024 TS_SIZE

, ROUND(A.MAXBYTES/1048576) MAX_MB

, ROUND(NVL(T.BYTES, 0)/1024/1024,2) USED

, ROUND((A.BYTES-NVL(T.BYTES,0))/1048576) FREE_MB

, NVL(T.BYTES / A.BYTES * 100, 0) PCT_USED

, ROUND((A.BYTES-NVL(T.BYTES,0)) / A.BYTES * 100,2) PCT_FREE

, ROUND((A.MAXBYTES-NVL(T.BYTES,0)) / A.MAXBYTES * 100,2) MAX_PCT_FREE

FROM

SYS.DBA_TABLESPACES D

, ( SELECT TABLESPACE_NAME, SUM(BYTES) BYTES,SUM(DECODE(MAXBYTES, 0, BYTES,

MAXBYTES)) MAXBYTES

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME

) A

, ( SELECT TABLESPACE_NAME, SUM(BYTES_CACHED) BYTES

FROM V\$TEMP_EXTENT_POOL

GROUP BY TABLESPACE_NAME

) T

WHERE

D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)

AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+)

AND D.EXTENT_MANAGEMENT LIKE 'LOCAL'

AND D.CONTENTS LIKE 'TEMPORARY'

ORDER BY PCT_USED

SPOOL OFF

exit;

EOF

#替换多余空格、空行

cat oratbs.csv | sed 's/[ ][ ]*/,/g' > tbs.csv

cat tbs.csv|sed '/^$/d' > oratbs.csv

#生成 insert 语句

rm $tbsfile

while read STATUS TABLESPACE_NAME TS_TYPE EXT_MGT SEG_MGT TABLESPACE_SIZE

MAX_MB USED_MB FREE_MB PCT_USED PCT_FREE MAX_PCT_FREE

cat >> ${tbsfile} << EOF

insert into oratbs

(STATUS,TABLESPACE_NAME,TS_TYPE,EXT_MGT,SEG_MGT,TABLESPACE_SIZE,MAX_MB,USED_MB,F

REE_MB,PCT_USED,PCT_FREE,MAX_PCT_FREE,DBNAME,HOSTS)

values('$STATUS','$TABLESPACE_NAME','$TS_TYPE','$EXT_MGT','$SEG_MGT','$TABLESPAC

E_SIZE','$MAX_MB','$USED_MB','$FREE_MB','$PCT_USED','$PCT_FREE','$MAX_PCT_FREE',

'$DBNAME','$HOSTS');

EOF

done < oratbs.csv

exit 0

###输出磁盘信息 insert 语句

if [ "$1" = "disk" ]; then

#提取磁盘信息

df -h|sed '1d;/ /!N;s/\n//;s/ \+/ /;' >disk.csv

#替换多余空格、空行

cat disk.csv | sed 's/[ ][ ]*/,/g' > disk_m.csv

cat disk_m.csv|sed '/^$/d' > disk.csv

#生成 insert 语句

rm -rf $diskfile

while read FILESYSTEM BLOCKS USED AVAILABLE CAPACITY MOUNTED_ON

cat >> ${diskfile} << EOF

insert into disk (FILESYSTEM,BLOCKS,USED,AVAILABLE,CAPACITY,MOUNTED_ON,HOSTS)

values('$FILESYSTEM','$BLOCKS','$USED','$AVAILABLE','$CAPACITY','$MOUNTED_ON','$

HOSTS');

EOF

done < disk.csv

of 3

免费下载

oracle

相关文档

评论