Oracle DB 相關常用sql彙總6[知乎系列續]

####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 ;