表空间预测

最新推荐文章于 2021-04-03 16:48:49 发布

weixin_34234823

最新推荐文章于 2021-04-03 16:48:49 发布

阅读量99

点赞数

原文链接：http://www.cnblogs.com/feiyun8616/p/7245450.html

版权

1.通过脚本实现，每天收集信息，做一个线性分析

#! /bin/ksh

#set -x

SID=$1

ORACLE_SID=stat10g
ORACLE_HOME=/oracle10g/product/10.2
PATH=$PATH:/usr/bin:/usr/sbin:$ORACLE_HOME/bin:.:/usr/local/bin
export ORACLE_SID ORACLE_HOME PATH

DATE=`date +%Y%m%d`
BASEDIR=/home/oracle/monitor/segment_size
LOGDIR=/database/log/segment_size

#Window DBs
DATABASE="boeprod testdb"

#LOGG=/${BASEDIR}/win.log
#>LOGG

for i in ${DATABASE}
do
LOG=/${LOGDIR}/log/${i}_`date '+%Y%m%d'`.log

sqlplus oper/oper123<< EOF > ${LOG}
set echo off
--heading on feedback on head on serveroutput on
prompt "tbs utilization:"
set line 142;
set pagesize 1000;
col tablespace_name for a30;
set num 10;
col instance_name for a15
col used_ for 999,990.90;
col free_ for a15;
col free_percentage for a15;

select a.tablespace_name,all_sum "all_sum(M)",
to_char(nvl(free_sum,0),'9,999,990.00')||'M' free_,
to_char(100*nvl(free_sum,0)/all_sum,'900.00')||'%' free_percentage,instance_name
from
(select tablespace_name,sum(bytes)/1024/1024 all_sum
from dba_data_files@${i}
group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 free_sum
from dba_free_space@${i}
group by tablespace_name) c, v\$instance@${i}
where
a.tablespace_name = c.tablespace_name(+)
order by 4
/

prompt "tbs percentage utilization:"
--tbs percentage utilization
select tablespace_name, to_char(sysdate,'YYYY-MM') "Date", org_mb total, free_mb free, pct_free pct_free,
--CASE WHEN ( (((0.8*org_mb)-free_mb)/(1-0.8)) < 1) THEN 0
-- ELSE (((0.8*org_mb)-free_mb)/(1-0.8))
-- END as "Free80%-Add",
CASE WHEN ( (((0.2*org_mb)-free_mb)/(1-0.2)) < 1) THEN 0
ELSE (((0.2*org_mb)-free_mb)/(1-0.2))
END as "Free20%-Add",
CASE WHEN ( (((0.17*org_mb)-free_mb)/(1-0.17)) < 1) THEN 0
ELSE (((0.17*org_mb)-free_mb)/(1-0.17))
END as "Free17%-Add",
CASE WHEN ( (((0.16*org_mb)-free_mb)/(1-0.16)) < 1) THEN 0
ELSE (((0.16*org_mb)-free_mb)/(1-0.16))
END as "Free16%-Add",
CASE WHEN ( (((0.15*org_mb)-free_mb)/(1-0.15)) < 1) THEN 0
ELSE (((0.15*org_mb)-free_mb)/(1-0.15))
END as "Free15%-Add"
from
( select a.tablespace_name
,((sum(a.bytes)/1024/1024)/max(b.Org_Mb))*100 Pct_free
,max(b.Org_Mb) Org_Mb
,max(b.Org_Mb) - sum(a.bytes)/1024/1024 Used_Mb
,sum(a.bytes)/1024/1024 Free_Mb
,max(a.bytes)/1024/1024 Max_Mb
from dba_free_space@${i} a,
(select tablespace_name ,sum(bytes)/1024/1024 Org_Mb from dba_data_files@${i} group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name
order by 1 desc
)
/

col file_name for a55
col tablespace_name for a30
select tablespace_name,file_name,autoextensible,sum(maxbytes)/1024/1024 totalbytes,sum(bytes)/1024/1024 bytes from dba_data_Files@${i} group by tablespace_name,file_name,autoextensible order by tablespace_name,file_name
/

select to_char(s1.run_date,'YYYYMMDD HH24:MI:SS') origin_date,s1.owner, s1.segment_type seg_type,s1.segment_name, s1.extents "last_exts", s2.extents "cur_exts", s1.bytes "last_bytes", s2.bytes "cur_bytes", round(((s2.bytes - s1.bytes)/s1.bytes)*100,2) "%Growth+"
from ${i}_segmon_statistics s1, dba_segments@${i} s2
where s1.segment_name = s2.segment_name
and s1.owner = s2.owner
and s1.segment_type = s1.segment_type
and nvl(s1.partition_name,'*') = nvl(s2.partition_name,'*')
and ( ((s2.extents - s1.extents)/s1.extents) * 100 >= &incr_percent or ((s2.bytes - s1.bytes)/s1.bytes)*100 >= &incr_percent)
and s2.bytes > &lower_bound
and s1.run_date = (select min(run_date) from ${i}_segmon_statistics)
order by 9,2,3,4
/

column capture_date format A11
column segment_name format A35
column segment_type format A15
column owner format A20
column MB format 99,999.99

select to_char(sysdate,'YYYY-MON-DD') capture_date,owner,segment_name,segment_type,tablespace_name,sum(bytes)/1024/1024 MB
from dba_segments@${i}
where owner not in ('OUTLN','SCOTT','SYSTEM')
group by owner,segment_name,segment_type,tablespace_name having (sum(bytes)/1024/1024 >5)
order by MB desc
/
exit;
EOF

cat ${LOG}
#/bin/mailx -s "Temporary monitor ${i} tablespace" a@bb.com < ${LOG};
#cat ${LOG}>>${LOGG}
done

###add for 12c cdb

for i in ${DATABASE_12C}
do

LOG=/${LOGDIR}/log/${i}_`date '+%Y%m%d'`.log

sqlplus c##oper/oper123<< EOF >> ${LOG}
set echo off
--heading on feedback on head on serveroutput on
prompt "tbs utilization:"
set line 142;
set pagesize 1000;
col tablespace_name for a30;
set num 10;
col instance_name for a15
col used_ for 999,990.90;
col free_ for a15;
col free_percentage for a15;

prompt"top20seg:"
--top20seg.sql
--def run_dt = sysdate - 6 months
def incr_percent = 0
def lower_bound = 65536
col owner for a12
col seg_type for a12
col segment_name for a35
col "%Growth+" for '999'
select to_char(s1.run_date,'YYYYMMDD HH24:MI:SS') origin_date,s1.owner, s1.segment_type seg_type,s1.segment_name, s1.extents "last_exts", s2.extents "cur_exts", s1.bytes "last_bytes", s2.bytes "cur_bytes", round(((s2.bytes - s1.bytes)/s1.bytes)*100,2) "%Growth+"
from ${i}_segmon_statistics s1, dba_segments@${i} s2
where s1.segment_name = s2.segment_name
and s1.owner = s2.owner
and s1.segment_type = s1.segment_type
and nvl(s1.partition_name,'*') = nvl(s2.partition_name,'*')
and ( ((s2.extents - s1.extents)/s1.extents) * 100 >= &incr_percent or ((s2.bytes - s1.bytes)/s1.bytes)*100 >= &incr_percent)
and s2.bytes > &lower_bound
and s1.run_date = (select min(run_date) from ${i}_segmon_statistics)
order by 9,2,3,4
/

column capture_date format A11
column segment_name format A35
column segment_type format A15
column owner format A20
column MB format 99,999.99

cat ${LOG}
#/bin/mailx -s "Temporary monitor ${i} tablespace" a@bb.com < ${LOG};
#cat ${LOG}>>${LOGG}
done
#######

2。

LOBSEGMENT过大的处理

http://blog.csdn.net/lijun_blue/article/details/7238589

3.sample:

1.cd /database/log/segment_size/log_<db_name>
grep 204288 *

dbprod_segments_20170829.log:TB_L 204288 20,182.63M 09.88% dbprod
dbprod_segments_20170830.log:TB_L 204288 19,699.63M 09.64% dbprod
dbprod_segments_20170831.log:TB_L 204288 30,214.19M 14.79% dbprod

sed -n '61,72p' dbprod_segments_20170830.log
sed -n '61,72p' dbprod_segments_20170831.log

--db_CONT_AUDIT_LOG decrease 5G
--db_DAILY_INVENTORY decrease 5G

SQL>
CAPTURE_DAT OWNER SEGMENT_NAME TYPE TABLESPACE_NAME MB
----------- -------------------- ----------------------------------- --------------- ------------------------------ ----------
2017-AUG-30 dbDATA db_CONT_AUDIT_LOG TABLE PARTITION TB_L 34,035.63
2017-AUG-30 dbDATA db_SUMM_CONT_YARD_INVENTORY TABLE TB_L 22,519.00
2017-AUG-30 dbDATA db_DAILY_INVENTORY TABLE PARTITION TB_L 15,023.94
2017-AUG-30 dbDATA db_CONT_YC_MOVEMENT TABLE PARTITION TB_L 10,422.69
2017-AUG-30 dbDATA db_SUMM_IN_YARD_DWELL_TIME TABLE TB_L 8,388.00
2017-AUG-30 dbDATA db_CONT_AUDIT_LOG_PK INDEX IDX_L 8,168.00
2017-AUG-30 dbDATA db_SUMM_GATE_MOVEMENT TABLE TB_L 7,839.00
2017-AUG-30 dbDATA db_CONT_HOLD_RELEASE TABLE TB_L 6,119.00
2017-AUG-30 dbDATA db_CONFIRMED_GATE_MOVEMENT TABLE PARTITION TB_L 4,938.81

3.
sed -n '61,72p' dbprod_segments_20170926.log
sed -n '61,72p' dbprod_segments_20170927.log

4.趋势，每天长400M,剩余空间20+16=36, 支持40天。20170927
　
dbprod_segments_20170919.log:TB_L 204288 23,678.56M 11.59% dbprod
dbprod_segments_20170920.log:TB_L 204288 23,199.56M 11.36% dbprod
dbprod_segments_20170921.log:TB_L 204288 22,733.56M 11.13% dbprod
dbprod_segments_20170922.log:TB_L 204288 22,337.56M 10.93% dbprod
dbprod_segments_20170923.log:TB_L 204288 21,943.56M 10.74% dbprod
dbprod_segments_20170924.log:TB_L 204288 21,576.56M 10.56% dbprod
dbprod_segments_20170925.log:TB_L 204288 21,285.56M 10.42% dbprod
dbprod_segments_20170926.log:TB_L 204288 20,891.56M 10.23% dbprod
dbprod_segments_20170927.log:TB_L 204288 20,452.00M 10.01% dbprod

TB_L /dbproddb/data/TB_l_50.dbf YES 4096 8192

TB_L /dbproddb/data/TB_l_51.dbf YES 4096 8192

TB_L /dbproddb/data/TB_l_52.dbf YES 4096 8192

TB_L /dbproddb/data/TB_l_53.dbf YES 4096 8192

转载于:https://www.cnblogs.com/feiyun8616/p/7245450.html

weixin_34234823

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
表空间预测

1.通过脚本实现，每天收集信息，做一个线性分析#! /bin/ksh#set -xSID=$1ORACLE_SID=stat10gORACLE_HOME=/oracle10g/product/10.2PATH=$PATH:/usr/bin:/usr/sbin:$ORACLE_HOME/bin:.:/usr/local/binexport ORACLE_SID ORACLE_H...
复制链接

扫一扫