cp /var/log/messages /var/log/messagesdate +%Y%m%d%H%M&&>/var/log/messages
echo “cat find /oracle -name 'alert*.log'>>find /oracle -name 'alert*.log'``date +%Y%m%d%H%M&&>find /oracle -name 'alert*.log'”
echo “cat find /grid -name 'crsd.log'>>find /grid -name 'crsd.log'``date +%Y%m%d%H%M&&>find /grid -name 'crsd.log'”
echo “cat find /grid -name 'ocssd.log'>>find /grid -name 'ocssd.log'``date +%Y%m%d%H%M&&>find /grid -name 'ocssd.log'”
find . -name ‘*txt’ -atime +5|xargs rm -rf
set line200
col path for a40
select name,path from vasm_disk where name like '%DGDATA%';
select count(name) from vasm_disk where name like ‘%DGDATA%’;
select name,path from vasm_disk where name like '%DGSYS%'; select count(name) from vasm_disk where name like ‘%DGSYS%’;
select name,path from vasm_disk where name like '%OCR_VOTE%'; select count(name) from vasm_disk where name like ‘%OCR_VOTE%’;
日志切换和生成速度、大小：
SQL>select group#,bytes from vlog_history where thread#=2 group by to_char(first_time,‘yyyy-mm-dd hh24’) order by to_char(first_time,‘yyyy-mm-dd hh24’);
SQL> SELECT RUN_ID,NAME,CHECK_NAME,RUN_MODE,SRC_INCIDENT FROM V$HM_RUN; #了解曾经进行的各种Health Monitor检查历史情况：
RUN_ID NAME CHECK_NAME RUN_MODE SRC_INCIDENT复制
61 HM_RUN_61 DB Structure Integrity Check REACTIVE 0 41 HM_RUN_41 DB Structure Integrity Check REACTIVE 0复制
SQL> SELECT TYPE,DESCRIPTION FROM V$HM_FINDING WHERE RUN_ID=41; #了解某次检查中发现的问题
no rows selected
SQL> SELECT TYPE,DESCRIPTION FROM V$HM_FINDING WHERE RUN_ID = 61;
no rows selected
5.表空间碎片评估方法
以下语句将按表空间计算FSFI(free space fragmentation index）值,如果FSFI值<30%,则该表空间的碎片较多：
select a.tablespace_name,sqrt(max(a.blocks)/sum(a.blocks)*(100/sqrt(count(a.blocks)))) FSFI
from dba_free_space a,dba_tablespaces b
where a.tablespace_name=b.tablespace_name and b.contents not in (‘TEMPORARY’,‘UNDO’)
group by a.tablespace_name order by FSFI;
6.表碎片的评估方法
显示碎片率最高的前100个表：
col frag format 999999.99
col owner format a30
col table_name format a30
select * from (
select a.owner,a.table_name,a.num_rows,a.avg_row_lena.num_rows,sum(b.bytes),(a.avg_row_lena.num_rows)/sum(b.bytes) frag
from dba_tables a,dba_segments b
where a.table_name=b.segment_name and a.owner=b.owner and a.owner not in (‘SYS’,‘SYSTEM’,‘OUTLN’,‘DMSYS’,‘TSMSYS’,‘DBSNMP’,‘WMSYS’,’
EXFSYS’,‘CTXSYS’,‘XDB’,‘OLAPSYS’,‘ORDSYS’,‘MDSYS’,‘SYSMAN’)
group by a.owner,a.table_name,a.avg_row_len,a.num_rows
having a.avg_row_len*a.num_rows/sum(b.bytes)<0.7
order by sum(b.bytes) desc)
where rownum<=100;
7.索引碎片的评估方法
下列语句显示索引高度Blevel>=3,并且索引大小超过100MB的索引：
col tablespace_name format a20
col owner format a10
col index_name format a30
select id.tablespace_name,id.owner,id.index_name,id.blevel,sum(sg.bytes)/1024/1024,sg.blocks,sg.extents
from dba_indexes id,dba_segments sg
where id.owner=sg.owner and id.index_name=sg.segment_name and id.tablespace_name=sg.tablespace_name
and id.owner not in (‘SYS’,‘SYSTEM’,‘USER’,‘DBSNMP’,‘ORDSYS’,‘OUTLN’) and sg.extents>100 and id.blevel>=3
group by id.tablespace_name,id.owner,id.index_name,id.blevel,sg.blocks,sg.extents
having sum(sg.bytes)/1024/1024>100;
下列语句中PCT_DELETED的含义为索引被删除项与索引项总数的所占比例，如果PCT_DELETED>=20%,则说明该索引碎片严
重，由于analyze语句会对被分析索引产生锁，即在生产系统运行时会产生一定影响因此优先考虑按blevel分析方法：
analyze index <index_name> validate structure;
select del_lf_rows*100/decode(lf_rows,0,1,lf_rows) pct_deleted from index_stats;
8.查看automatic segment advisor分析结果
查看automatic segment advisor的finding结果
select af.task_name,ao.attr2 segname,ao.attr3 partition,ao.type,af.message
from dba_advisor_findings af,dba_advisor_objects ao
where ao.task_id=af.task_id
and ao.object_id=af.object_id;
只查询可以进行shrink操作的对象
select f.impact,o.type,o.attr1,o.attr2,f.message,f.more_info
from dba_advisor_findings f,dba_advisor_objects o
where f.object_id=o.object_id and f.task_name=o.task_name and f.message like ‘%shrink%’
order by f.impact desc;
查看automatic segment advisor的recommendations结果
告诉客户那些表、索引存在碎片、预估回收多少空间和推荐的空间回收语句，结合上面手工产生的结果进行综合评估制定策略
select tablespace_name,segment_name,segment_type,partition_name,recommendations,c1
from table(dbms_space.asa_recommendations(‘FALSE’,‘FALSE’,‘FALSE’));
