ITPub博客

首页 > 数据库 > Oracle > Oracle DB 相关常用sql汇总6[知乎系列续]

Oracle DB 相关常用sql汇总6[知乎系列续]

原创 Oracle 作者:kunlunzhiying 时间:2018-08-30 16:49:38 0 删除 编辑

####oracle AWR几个重要的指标####

DB time


DB CPU(s)


Redo size (bytes)和Block changes


select s.metric_name, s.metric_unit, trim(to_char(max(s.average), '999G999G999G999G999D9')) max_value

  from dba_hist_sysmetric_summary s

where s.metric_name in ('Redo Generated Per Sec', 'DB Block Changes Per Sec')

group by s.metric_name, s.metric_unit


内存读


物理写



select s.metric_name, s.metric_unit, trim(to_char(max(s.average), '999G999G999G999G999D9')) max_value

  from dba_hist_sysmetric_summary s

where s.metric_name in ('Logical Reads Per Sec',

                         'Physical Reads Per Sec',

                         'Physical Read Bytes Per Sec',

                         'Physical Writes Per Sec',

                         'Physical Write Bytes Per Sec')

group by s.metric_name, s.metric_unit

order by 1



DB Time = DB CPU + Non-Idle Wait +  Wait on CPU queue


DB Time也是前台用户耗费的数据库时间

DB CPU是前台用户使用CPU的时间



Global Cache blocks received和Global Cache blocks served   常说的GC Buffer相关等待事件



select s.metric_name, s.metric_unit, trim(to_char(max(s.average), '999G999G999G999G999D9')) max_value

  from dba_hist_sysmetric_summary s

where s.metric_name in ('User Calls Per Sec',

                         'Total Parse Count Per Sec',

                         'Hard Parse Count Per Sec',

                         'Logons Per Sec',

                         'Executions Per Sec',

                         'User Rollbacks Per Sec',

                         'User Transaction Per Sec')

group by s.metric_name, s.metric_unit

order by 1

AWR指标 top 10等待事件说明【Top 10 Foreground Events by Total Wait Time】

DB CPU和DB time的关系,我们可以按照所占比例对数据库健康状况进行分级,如果DB CPU占DB time超过90%,则数据库非常健康,超过80%为健康

如果CPU占DB time的30%-60%,那就表明数据库已经是不健康的状态,需要重点投入精力去改变健康状况

如果CPU占DB time的30%以下,那就表明数据库已经是非常不健康的状态,病入膏肓来形容一点都不夸张,不但要处理问题,而且要立即马上快速的恢复。

查询所有的等待事件,因为awr只包含了top10等待,所以用如下sql 查询等待事件【dba_hist_system_event,配合快照表dba_hist_snapshot】


select ss.dbid "DB Id",

       ss.snap_id - 1 "Begin Snap Id",

       ss.snap_id "End Snap Id",

       ss.instance_number "Inst num",

       to_char(ss.begin_interval_time, 'YYYY-MM-DD HH24:MI:SS') "Begin Snap Time",

       to_char(ss.end_interval_time, 'YYYY-MM-DD HH24:MI:SS') "End Snap Time",

       se.event_name "Event",

       (se.total_waits_fg - (select lse.total_waits_fg

                               from dba_hist_system_event lse

                              where lse.instance_number = se.instance_number

                                and lse.snap_id = se.snap_id - 1

                                and lse.dbid = se.dbid

                                and lse.event_name = se.event_name)) "Waits",

       round((se.time_waited_micro_fg - (select lse.time_waited_micro_fg

                                           from dba_hist_system_event lse

                                          where lse.instance_number = se.instance_number

                                            and lse.snap_id = se.snap_id - 1

                                            and lse.dbid = se.dbid

                                            and lse.event_name = se.event_name)) / 1000000,

             1) "Total Wait Time (sec)",

       se.wait_class "Wait Class"

  from dba_hist_system_event se, dba_hist_snapshot ss

 where ss.instance_number = se.instance_number

   and ss.snap_id = se.snap_id

   and ss.dbid = se.dbid

   and ss.instance_number = 1

   and ss.snap_id = 121743

   and se.wait_class <> 'Idle'

 order by ss.snap_id desc, ss.instance_number, 9 desc;


--每日归档量/每日归档次数

select logtime,count(*),round(sum(blocks*block_size)/1024/1024/1024) size_gb

from (select trunc(first_time,'dd') as logtime,a.blocks,a.block_size

from v$archived_log a

where a.dest_id=1

and a.first_time > trunc(sysdate-7))

group by logtime order by 1;

############Oracle 日志挖掘 logminner#########

要提前设置utl_file_dir 目录,与设置存放数据字典路径要一致


1、execute dbms_logmnr_d.build ('dict.ora','/tmp',dbms_logmnr_d.store_in_flat_file);

execute dbms_logmnr.add_logfile(logfilename=>'+FRA/BARP_G/ARCHIVELOG/2017_09_25/thread_5_seq_30041.23632.955645029',options=>dbms_logmnr.new);


EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/logs/log1.f',OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/logs/log2.f',OPTIONS => DBMS_LOGMNR.ADDFILE);


2、 EXECUTE dbms_logmnr.start_logmnr(DictFileName => '/tmp/dict.ora');  

3、 create table perfstat.logminer_5 nologging  as select * from v$logmnr_contents;

4、 execute dbms_logmnr.end_logmnr;

函数说明:

函数

说明

dbms_logmnr.new

在数据字典中添加第一个需要分析的文件

dbms_logmnr.addfile

在数据字典中添加其他需要分析的文件

#######Oracle 坏块处理恢复#######

Taking the advantage that the standby db and others standby dbs are

synchronized with the primary database :


1. Take an image copy of the datafile 98 from primary database or another

standby db where the file does not have corruption :


RMAN> backup as copy datafile 98 format '/temporary_location/data_ts_98.dbf';


Note: replace "'/temporary_location" with the complete location where you

can copy the file.

2. Copy the file '/temporary_location/data_ts_98.dbf' to the node where

is one instance of standby db.

3. In one of the instance of standby database, catalog the image copy of the

datafile :

RMAN> catalog datafilecopy ''/temporary_location_standby/data_ts_98.dbf';

4. Try to fix the corruption in the block reported corrupted :

RMAN> recover datafile 98 block 488820 ;



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28211342/viewspace-2213236/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
IT搬砖

注册时间:2014-07-28

  • 博文量
    665
  • 访问量
    334365