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 ;