Oracle 数据库的日常巡检事项

A、本文主要介绍金蝶云星空在ORACLE数据库运行时，日常需要对数据库做的检查事项，目的是确保数据库能够正常稳定地运行。

B、数据库版本：11204。

C、涉及到的脚本，是在数据库服务器上，以 sys 身份、使用sqlplus工具登录数据库执行。

D、脚本包含了对数据库层面的检查，同时也包含对数据中心（按输入用户）的检查。

1、查阅数据库告警日志的信息，看看是否存在错误？

host adrci exec="show base; show problem;"

2、检查数据库的表空间使用情况，看看SYSTEM,SYSAUX表空间的消耗是否合理？业务数据

使用的表空间，是否将耗尽？

   col "USED_PERCENT" for a10;

select sysdate, t.tablespace_name, total, free, round(100*(1-(free/total)),3)||'%' "USED_PERCENT" from

(select tablespace_name, sum(bytes)/1024/1024 total from dba_data_files group by tablespace_name) t,

(select tablespace_name, sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) f

where t.tablespace_name=f.tablespace_name(+) order by round(100*(1-(free/total)),3);

3、检查数据库的定时作业，看看作业是否有执行出错的情况（如下红框，失败1次）。

4、检查数据文件的状态，看看数据文件已经有无离线？

    select status,online_status from dba_data_files group by status,online_status;

    5、最近10天内 object_id 序列号被使用的情况

col dt for 9999999999;

col max_object_id for 999999999999999;

col max2_object_id for 999999999999999;

select create_date, max_object_id, min_object_id,

     (max_object_id - min_object_id) dt,

     case when (max_object_id = min_object_id) then max2_object_id else 0 end max2_object_id

from (select to_char(created,'yyyy-mm-dd') as create_date,

        greatest(max(object_id), lag(max(object_id),1) over(order by to_char(created,'yyyy-mm-dd'))) max_object_id,

        greatest( lag(max(object_id),1) over(order by to_char(created,'yyyy-mm-dd')),

                 lag(max(object_id),2) over(order by to_char(created,'yyyy-mm-dd'))

                 ) min_object_id,                  

          least(max(object_id), lag(max(object_id),1) over(order by to_char(created,'yyyy-mm-dd'))) max2_object_id

       from dba_objects

 group by to_char(created,'yyyy-mm-dd'),to_char(created-1,'yyyy-mm-dd')

order by 1 desc) do where rownum <= 10;

6、检查数据库的索引状况，看看是否存在UNUSABLE或者INVISIBLE状态。

col index_owner for a10

col table_name for a20

select owner,status,count(*) from dba_indexes where owner in (&user)

group by owner,status order by owner, status;

select index_owner, status, idx_type, count(*) from

(select index_owner,status, 'PART_INDEX' idx_type from dba_ind_partitions

   union all select index_owner,status,'SUBPART_INDEX' idx_type from dba_ind_subpartitions )

where index_owner in (&user) group by index_owner,status,idx_type order by index_owner,status,idx_type;

7、位图索引（正常情况，系统不会使用位图索引）。

select owner,table_name,index_name from dba_indexes where owner in (&user)

and index_type='BITMAP' order by owner,table_name,index_name;

8、查看不可用，无效状态的约束。

select table_name,constraint_name,constraint_type,a.status,search_condition,last_ddl_time from dba_constraints a, dba_objects b where (a.status='DISABLED' or a.status='INVALID')

and a.owner in (&user) and b.owner in (&user)

and a.owner=b.owner and b.object_name=a.constraint_name order by a.owner, table_name;

9、物化视图的最后刷新时间及状态（有6个物化视图实时刷新）。

col mview_name for a30;

col staleness for a10;

select dm.owner, mview_name, refresh_mode, refresh_method, last_refresh_type, last_refresh_date, staleness, status

from dba_mviews dm, dba_objects do

where dm.owner in (&user) and do.owner in (&user)

 and dm.owner = do.owner and dm.mview_name=do.object_name

and do.object_type='MATERIALIZED VIEW' order by dm.owner,last_refresh_date;

10、TMP表当前的创建、尺寸大小情况

select owner, decode(created_date,null,null,table_name) table_name, created_date,

      tab_totals , size_m from

(select dts.owner, 'TMP' table_name , to_char(do.created,'yyyy/mm/dd') created_date,

       count(*) tab_totals , sum(dts.bytes/1024/1024) size_m

from (select dt.owner, dt.table_name, dt.owner||','||dt.table_name ot, ds.segment_name, ds.bytes from dba_tables dt

    inner join dba_segments ds on (ds.owner=dt.owner and ds.segment_name=dt.table_name)

    where dt.owner in (&user) and ds.owner in (&user)

    and ds.segment_type='TABLE'

    and regexp_like(dt.table_name,'^TMP[[:alnum:]]{27}$') ) dts

inner join (select owner, object_name, owner||','||object_name oo, created from dba_objects

    where owner in (&user) and object_type='TABLE') do

    on (do.oo=dts.ot)

group by rollup(dts.owner , to_char(do.created,'yyyy/mm/dd') ))

where ( (owner is not null and created_date is not null) or (owner is null and created_date is null) ) order by owner, created_date nulls last;

11、数据中心表的统计分析情况。

select owner,max(last_analyzed) max, min(last_analyzed) min from dba_tables

where owner in (&user) and temporary = 'N' and read_only='NO'

and table_name not like 'MLOG%' group by owner order by owner;

12、没有索引并且不被数据库对象引用的表。

select a.owner, a.table_name, a.partitioned parted, a.num_rows, a.last_analyzed,c.created from

(select * from dba_tables where temporary='N' and owner in (&user)

and not regexp_like(table_name,'^TMP[[:alnum:]]{27}$') and read_only='NO'

   and num_rows is not null and not exists (select 'x' from dba_mview_logs where table_name=log_table)) a,

   dba_indexes b, (select * from dba_objects where object_type='TABLE' and owner in (&user))