DBA维护工作目录

---------------------------DBA维护工作目录--------------------------------------------

1	检查备份日志	每天	上午	1－5分钟	*****
　　　检查前一天备份的日志，看备份是否成功完成，或者备份过程中出现什么错误提示。

2.确定所有oracle后台进程运行正常            每天	上午	1－5分钟  *****
     Ps –ef|grep ora 查看数据读写进程，日志读写进程，监控进程等进程是否正常。
3.查看alert日志文件，分析新增内容           每天	上午	5－10分钟  *****


     tail -f $ORACLE_HOME/admin/bdump/alert.log 或者vi $ORACLE_HOME/admin/bdump/alert.log 等命令检查系统日志看是否有什么异常提示。


4	检查是否有20%以上可用空闲表空间	每天	上午	1－5分钟	在系统使用过程中，对表空间可用率不足20%应尽快进行处理  *****

SELECT upper(f.tablespace_name) "表空间名",

       d.Tot_grootte_Mb "表空间大小(M)",

       d.Tot_grootte_Mb - f.total_bytes "已使用空间(M)",

       to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') "使用比",

       f.total_bytes "空闲空间(M)",

       f.max_bytes "最大块(M)"

 FROM      

    (SELECT tablespace_name,

            round(SUM(bytes)/(1024*1024),2) total_bytes,

            round(MAX(bytes)/(1024*1024),2) max_bytes

      FROM sys.dba_free_space

     GROUP BY tablespace_name) f,

    (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb

      FROM   sys.dba_data_files dd

      GROUP BY dd.tablespace_name) d

WHERE d.tablespace_name = f.tablespace_name    

ORDER BY 4 DESC;


4	检查是否有表或索引已接近其可以使用的最多扩展的数量。	每天	上午	1－5分钟	对于表或索引的可用扩展数量与最大可用扩展数量差小于5时，应该尽快进行处理
SELECT segment_name, segment_type, owner,

             a.tablespace_name "tablespacename",

             initial_extent/1024 "inital_extent(K)",

             next_extent/1024 "next_extent(K)",

             pct_increase,

            b.bytes/1024 "tablespace max free space(K)",

           b.sum_bytes/1024 "tablespace total free space(K)"

  FROM dba_segments a  ,

   (SELECT tablespace_name,MAX(bytes) bytes,SUM(bytes) sum_bytes FROM dba_free_space GROUP BY tablespace_name) b

 WHERE a.tablespace_name=b.tablespace_name 

AND next_extent>b.bytes

 ORDER BY 4,3,1;

5	检查系统无效对象并编译	每周		周一上午	1－10分钟
     （1） select 'alter '||object_type||' '||owner||'.'||object_name||' COMPILE;' from dba_objects a where  a.status = 'INVALID'
     （2）
create or replace procedure exec_proc
is
 obj_number number := 0;

 cursor C1 is select o.obj#, 
           'ALTER ' || decode (o.type#,
                               4, 'VIEW ',
                               7, 'PROCEDURE ',
                               8, 'FUNCTION ',
                               9, 'PACKAGE ',
                               11, 'PACKAGE ',
                               12, 'TRIGGER ',
                               13, 'TYPE ',
                               14, 'TYPE ',
                               ' ') ||
           '"' || u.name || '"."' || o.name || '" COMPILE ' ||
                       decode (o.type#,
                               9, 'SPECIFICATION',
                               11, 'BODY',
                               13, 'SPECIFICATION',
                               14, 'BODY',
                               ' ')
           from sys.obj$ o, sys.user$ u
           where o.obj# > obj_number and 
           u.user# = o.owner# and o.remoteowner is NULL and
           o.status in (4,5,6) and o.type# in (4, 7, 8, 9, 11, 12, 13, 14)
           order by o.obj#;

  DDL_CURSOR integer;
  ddl_statement varchar2(200);
  iterations number;
  loop_count number;
  my_err     number;
  validate   number;
BEGIN
 loop_count := 0;
 -- To make sure we eventually stop, pick a max number of iterations
 select count(*) into iterations from sys.obj$ where remoteowner is NULL and
           status in (4,5,6) and type# in (4, 7, 8, 9, 11, 12, 13, 14); 

 DDL_CURSOR := dbms_sql.open_cursor;
 OPEN C1;

 LOOP

   BEGIN
     FETCH C1 INTO obj_number, ddl_statement;
     EXIT WHEN C1%NOTFOUND OR loop_count > iterations;
   EXCEPTION
    WHEN OTHERS THEN
      my_err := SQLCODE;
      IF my_err = -1555 THEN -- snapshot too old, re-execute fetch query
       CLOSE C1;
       -- Here is why C1 orders by obj#.  When we restart the query, we 
       -- will only find object with obj# greater than the last one tried.
       -- This keeps us from re-trying objects that failed.
       OPEN  C1;
       GOTO continue;
      ELSE
       RAISE;
      END IF;
   END;

   -- Check to see if already validated as a result of earlier compiles
   select count(*) into validate from obj$ where obj# = obj_number and
    status in (4,5,6);

   IF validate = 1 THEN
     BEGIN
       -- Issue the Alter Statement  (Parse implicitly executes DDLs)
       dbms_sql.parse(DDL_CURSOR, ddl_statement, dbms_sql.native);
     EXCEPTION
       WHEN OTHERS THEN
        null; -- ignore, and proceed.
     END;
   END IF;

 <>
   loop_count := loop_count + 1;
 END LOOP;
 dbms_sql.close_cursor(DDL_CURSOR);
 CLOSE C1;
END exec_proc;


6	检查是否有表或索引设置增长的百分比或下一次的扩展的尺寸过大	每周		周一上午
	1－5分钟	查找数据库表或索引增量TOP 10，根据表或索引实际的增长速率调整其增量大小
     


7	检查是否某表空间内对象的下一次扩展的总和超过目前表空间的最大可用尺寸	每周		周一上午 1－5分钟 查找是否表空间内对象的下一次扩展的总和超过目前表空间的最大可用尺寸
SELECT segment_name, segment_type, owner,

             a.tablespace_name "tablespacename",

             initial_extent/1024 "inital_extent(K)",

             next_extent/1024 "next_extent(K)",

             pct_increase,

            b.bytes/1024 "tablespace max free space(K)",

           b.sum_bytes/1024 "tablespace total free space(K)"

  FROM dba_segments a  ,

   (SELECT tablespace_name,MAX(bytes) bytes,SUM(bytes) sum_bytes FROM dba_free_space GROUP BY tablespace_name) b

 WHERE a.tablespace_name=b.tablespace_name 

AND next_extent>b.bytes/1024

 ORDER BY 4,3,1;


8	检查是否有数据库用户使用非TEMPORARY类型表空间作为临时表空间。	每周		周一上午	1－5分钟	运行SQL查询是否有用户使用非TEMPORARY类型表空间作为临时表空间

9	检查是否有表或索引碎片过多需要优化	每周		周一上午	10－30分钟 *****


　　　　　查看碎片程度高的表 
　　SELECT segment_name table_name , COUNT(*) extents 
　　FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name 
　　HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name); 
	
10	检查空闲表空间是否碎块过多需要优化	每周		周一上午	10－30分钟 *****

  （1）.    select tablespace_name,sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks)))) FSFI
from dba_free_space
group by tablespace_name order by 1;
      
      可以看出，FSFI的最大可能值为100（一个理想的单文件表空间）。随着范围的增加，FSFI值缓慢下降，而随着最大范围尺寸的减少，FSFI值会迅速下降。

    统计出了数据库的FSFI值，就可以把它作为一个可比参数。在一个有着足够有效自由空间，且FSFI值超过30的表空间中，很少会遇见有效自由空间的问题。当一个空间将要接近可比参数时，就需要做碎片整理了

 ---------------------------------------------------------------------------------(2).数据库运行了一段时间后，由于不断的在表空间上创建和删除对象，会在表空间上产生大量的碎片，DBA应该及时了解表空间的碎片和可用空间情况，以决定是否要对碎片进行整理或为表空间增加数据文件。
　　
　　select tablespace_name,
count(*) chunks ,
max(bytes/1024/1024) max_chunk
　　  from dba_free_space
     group by tablespace_name;
     
　　上面的SQL列出了数据库中每个表空间的空闲块情况,如下所示：
　　TABLESPACE_NAME          CHUNKS    MAX_CHUNK
　　-------------------- ---------- ----------
　　INDX                               1  57.9921875
　　RBS                                3   490.992188
　　RMAN_TS                           1   16.515625
　　SYSTEM                            1   207.296875
　　TEMP                             20   70.8046875
　　TOOLS                             1   11.8359375
　　USERS                            67   71.3671875
　　
　　其中，CHUNKS列表示表空间中有多少可用的空闲块(每个空闲块是由一些连续的Oracle数据块组成)，如果这样的空闲块过多，比如平均到每个数据文件上超过了100个，那么该表空间的碎片状况就比较严重了，可以尝试用以下的SQL命令进行表空间相邻碎片的接合：
　　
　　alter tablespace 表空间名 coalesce;
　　
　　然后再执行查看表空间碎片的SQL语句，看表空间的碎片有没有减少。如果没有效果，并且表空间的碎片已经严重影响到了数据库的运行，则考虑对该表空间进行重建。
　　MAX_CHUNK列的结果是表空间上最大的可用块大小，如果该表空间上的对象所需分配的空间(NEXT值)大于可用块的大小的话，就会提示ORA-1652、ORA-1653、ORA-1654的错误信息，DBA应该及时对表空间的空间进行扩充，以避免这些错误发生。
对表空间的扩充对表空间的数据文件大小进行扩展，或向表空间增加数据文件


	
11	汇总本周数据库程序库缓冲命中率	每周		周一上午	1－5分钟  *****


监控 SGA 中重做日志缓存区的命中率，应该小于1% 
　　SELECT name, gets, misses, immediate_gets, immediate_misses, 
　　Decode(gets,0,0,misses/gets*100) ratio1, 
　　Decode(immediate_gets+immediate_misses,0,0, 
　　immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 
　　FROM v$latch WHERE name IN ('redo allocation', 'redo copy'); 

监控 SGA 中共享缓存区的命中率，应该小于1% 
　　select sum(pins) "Total Pins", sum(reloads) "Total Reloads", 
　　sum(reloads)/sum(pins) *100 libcache 
　　from v$librarycache; 
　　select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"  
from v$librarycache; 

注：.调整Library Cache
　　库高速缓存（Library Cache）中包含私用和共享SQL区和PL/SQL区。调整SGA的重要问题是确保库高速缓存足够大，以使ORACLE能在共享池中保持分析和执行语句，提高语句分析和执行效率，降低资源消耗。通过比较Library Cache的命中率来决定它的大小。查询V$LIBRARYCACHE 数据字典视图（其中，pins表示高速缓存命中率，reloads表示高速缓存失败）
SQL〉SELECT SUM(pins),SUM(reloads)
　　FROM v$librarycache;
如果sum(reload)/sum(pins)≈0，说明Library Cache的命中率比较合适，若大于1，则需要增加共享池（SHARED－POOL－SIZE）的大小（在初始化参数文件中）。
　　.调整数据字典高速缓存（Dictionary Cache）
　　数据字典高速缓存包括了有关数据库的结构、用户、实体信息等。数据字典的命中率对系统有很大的影响。命中率的计算中，getmisses 表示失败次数，gets表示成功次数。
　　查询V$ROWCACHE表：
　　SQL>SELECT (1-(SUM(getmisses)/(SUM(gets)+SUM(getmisses))))*100
　　FROM v$rowcache;
　　如果该值>90%，说明命中率合适。否则，应增大共享池的大小。
	
12	汇总本周内存中排序比率	每周		周一上午	1－5分钟  *****


　　监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size 
　　SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)'); 
	
13	汇总本周数据库数据缓冲命中率	每周	周一上午	1－5分钟

　.调整数据库缓冲区高速缓存,Oracle 在运行期间向数据库高速缓存读写数据，高速缓存命中表示信息已在内存中，高速缓存失败意味着ORACLE必需进行磁盘I/O。保持高速缓存失败率最小的关键是确保高速缓存的大小。初始化参数DB－BLOCK－BUFFERS控制数据库缓冲区高速缓存的大小。可通过查询V$SYSSTAT命中率，以确定是否应当增加DB－BLOCK－BUFFERS的值。
　　SQL>SELECT name,value
　　FROM V$SYSSTAT
　　WHERE name IN (’dbblock gets’,’consistent gets’,’physical reads’);
　　通过查询结果
　　命中率=1-physical reads/(dbblock gets+consistent gets) 
　　如果命中率<0.6～0.7，则应增大DB－BLOCK－BUFFERS。

	
14	汇总本周数据库级的TOP 5等待事件	每周	周一上午	1－5分钟
************************************************************
    select event,sum(decode(wait_Time,0,0,1)) "Prev",
   sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
    from v$session_Wait
    group by event order by 4;
************************************************************
	
15	汇总本周最耗CPU的 TOP 10 SQL	每周	周一上午	5－10分钟  *****

*************************************
找使用CPU多的用户session
    12是cpu used by this session
    select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
    from v$session a,v$process b,v$sesstat c
    where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;
*************************************　


	
16	汇总本周磁盘I/O TOP 10的SQL	每周	周一上午	5－10分钟
*****************************************
监控文件系统的 I/O 比例
    select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
    a.status, a.bytes, b.phyrds, b.phywrts
    from v$datafile a, v$filestat b
    where a.file# = b.file#;

****************************************
	
17	汇总本周表空间I/O TOP 10	每周	周一上午	5－10分钟
********************************
监控表空间的 I/O 比例
    select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
    f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
    from v$filestat f, dba_data_files df
    where f.file# = df.file_id
    order by df.tablespace_name;


********************************
	
18	修改数据库sys和system用户口令	每月	每月第一个工作日	1－5分钟
	
19	修改数据库用户口令	
20	检查是否有索引使用的扩展数量过多需要优化	每月	每月第一个工作日	5－10分钟	使用较大的Extent进行重建索引

21	检查系统Patch情况	每月	每月第一个工作日	10分钟	检查Patch记录

22	汇总本月增长最快的10个表空间	每月	每月第一个工作日	5－10分钟	根据表空间的已使用和空闲的空间计算增长速度

23	汇总上报上月每天工作纪录报告	每月	每月第一个工作日	2－4小时	
24      检查是否有失效的索引
select index_name,
     owner,
     table_name,
     tablespace_name
from dba_indexes
where owner not in ('SYS','SYSTEM')
and status != 'VALID';

如果有记录返回，考虑重建这些索引

25     检查有无运行失败的JOB
select  job,
       this_date,
       this_sec,
       next_date,
       next_sec,
       failures,
       what
from dba_jobs
where failures !=0 or failures is not null;

26 检查SGA使用情况
select *  from v$sga;
检查SGA各部份的分配情况，与实际内存比较是否合理

27 检查SGA各部分占用内存情况
select * from v$sgastat;
检查有无占用大量Shared pool的对象，及是否有内存浪费情况