Oracle數(shù)據(jù)庫日常管理方案精.docx

1. Oracle數(shù)據(jù)庫日常管理方案Oracle數(shù)據(jù)庫的運維處理，更主要的是做好數(shù)據(jù)庫的管理工作，做好數(shù)據(jù)庫的檢查與備份工作，然后需要熟悉相關(guān)的邏輯結(jié)構(gòu)故障處理方法、物理結(jié)構(gòu)故障處理方法，應(yīng)對數(shù)據(jù)庫損壞的情況，在數(shù)據(jù)庫發(fā)生損壞而無法修復(fù)的時候，能利用數(shù)據(jù)庫管理工作中所做的數(shù)據(jù)備份及時恢復(fù)系統(tǒng)、數(shù)據(jù)，恢復(fù)正常生產(chǎn)工作。1.1. 數(shù)據(jù)庫管理Oracle數(shù)據(jù)庫的管理主要是要做好日常的檢查管理工作，并檢查好數(shù)據(jù)庫的備份情況，在發(fā)生緊急情況時能及時不僅切換到備用系統(tǒng)上進行工作，并要能在數(shù)據(jù)庫數(shù)據(jù)出現(xiàn)異常的時候，能及時恢復(fù)數(shù)據(jù)。因此，做好日常的檢查與管理工作尤其重要，主要工作包括：l 數(shù)據(jù)庫的性能監(jiān)控檢查l 數(shù)據(jù)庫狀態(tài)巡檢檢查l 數(shù)據(jù)庫備份情況與可恢復(fù)性檢查1.1.1. 數(shù)據(jù)庫性能監(jiān)控管理通過對數(shù)據(jù)庫性能監(jiān)控的管理工作，可以有效的防范風(fēng)險事件的發(fā)生，并能從長時間的運行數(shù)據(jù)中，總結(jié)出數(shù)據(jù)庫性能的狀態(tài)，為系統(tǒng)性能優(yōu)化、隱患事件排查提供更好的依據(jù)。. 查找前十條性能差的sqlSELECT * FROM (select PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE, DISK_READS,sql_text FROM v$sqlarea order BY disk_reads DESC )where ROWNUM 8 AND a.event = enqueue ORDER BY username;. 如何確定哪個表空間讀寫頻繁select name,phyrds,phywrts,readtim,writetim from v$filestat a,v$dbfile b where a.file#=b.file# order by readtim desc;SELECT s.sid, kglpnmod “Mode”, kglpnreq “Req”, SPID “OS Process” FROM v$session_wait w, x$kglpn p, v$session s ,v$process o WHERE p.kglpnuse=s.saddr AND kglpnhdl=w.p1raw and w.event like %library cache pin% and s.paddr=o.addr;. 查詢?nèi)頀呙璧谋鞸QL col name for a30SQL select name,value from v$sysstat where name in (table scans (short tables),table scans (long tables);NAME VALUE- -table scans (short tables) 18602table scans (long tables) 111SQL select count(target),target from v$session_longops where opname = Table Scan group by target;COUNT(TARGET) TARGET- - 84 ECM_APPL.RPTLOG 159 ECM_DCTM_OTHR.DMR_CONTENT_S 9 ECM_DCTM_OTHR.DM_SYSOBJECT_R 2 ECM_DCTM_OTHR.DM_SYSOBJECT_S. 查出全表掃描的表Select sql_text from v$sqltext t, v$sql_plan p Where t.hash_value=p.hash_value And p.operation=TABLE ACCESS And p.option=FULL Order by p.hash-value, t.piece;. 查找Fast Full index 掃描的Sql語句可以這樣: Select sql_text from v$sqltext t, v$sql_plan p Where t.hash_value=p.hash_value And p.operation=INDEX And p.option=FULL SCAN Order by p.hash-value, t.piece;. 查詢硬語法分析的次數(shù)SQL select name,value from v$sysstat where name like parse count%;NAME VALUE- -parse count (total) 16103parse count (hard) 343parse count (failures) 5該項顯示buffer cache大小是否合適。公式：1-(physical reads-physical reads direct-physical reads direct (lob) / session logical reads)執(zhí)行：select 1-(a.value-b.value-c.value)/d.value) from v$sysstat a,v$sysstat b,v$sysstat c,v$sysstat d where =physical reads and =physical reads direct and =physical reads direct (lob) and =session logical reads;. 顯示buffer命中率公式：1-(physical reads/ (db block gets+consistent gets)執(zhí)行：select 1-(sum(decode(name,physical reads,value,0)/(sum(decode(name,db block gets,value,0)+sum(decode(name,consistent gets,value,0) buffer hit ratio from v$sysstat;Soft parse ratio：這項將顯示系統(tǒng)是否有太多硬解析。該值將會與原始統(tǒng)計數(shù)據(jù)對比以確保精確。例如，軟解析率僅為0.2則表示硬解析率太高。不過，如果總解析量(parse count total)偏低，這項值可以被忽略。公式：1 - ( parse count (hard) / parse count (total) ) 執(zhí)行：select 1-(a.value/b.value) from v$sysstat a,v$sysstat b Where =parse count (hard) and =parse count (total);In-memory sort ratio：該項顯示內(nèi)存中完成的排序所占比例。最理想狀態(tài)下，在OLTP系統(tǒng)中，大部分排序不僅小并且能夠完全在內(nèi)存里完成排序。公式：sorts (memory) / ( sorts (memory) + sorts (disk) )執(zhí)行：select a.value/(b.value+c.value) from v$sysstat a,v$sysstat b,v$sysstat c where =sorts (memory) and =sorts (memory) and =sorts (disk);Parse to execute ratio：在生產(chǎn)環(huán)境，最理想狀態(tài)是一條sql語句一次解析多數(shù)運行。公式：1 - (parse count/execute count)執(zhí)行：select 1-(a.value/b.value) from v$sysstat a,v$sysstat b where =parse count (total) and =execute count;Parse CPU to total CPU ratio：該項顯示總的CPU花費在執(zhí)行及解析上的比率。如果這項比率較低，說明系統(tǒng)執(zhí)行了太多的解析。公式：1 - (parse time cpu / CPU used by this session)執(zhí)行：select 1-(a.value/b.value) from v$sysstat a,v$sysstat b where =parse time cpu and =CPU used by this session;Parse time CPU to parse time elapsed：通常，該項顯示鎖競爭比率。這項比率計算是否時間花費在解析分配給CPU進行周期運算(即生產(chǎn)工作)。解析時間花費不在CPU周期運算通常表示由于鎖競爭導(dǎo)致了時間花費。公式：parse time cpu / parse time elapsed執(zhí)行：select a.value/b.value from v$sysstat a,v$sysstat b where =parse time cpu and =parse time elapsed;從V$SYSSTAT獲取負(fù)載間檔(Load Profile)數(shù)據(jù)負(fù)載間檔是監(jiān)控系統(tǒng)吞吐量和負(fù)載變化的重要部分，該部分提供如下每秒和每個事務(wù)的統(tǒng)計信息：logons cumulative, parse count (total), parse count (hard), executes, physical reads, physical writes, block changes, and redo size.被格式化的數(shù)據(jù)可檢查rates是否過高，或用于對比其它基線數(shù)據(jù)設(shè)置為識別system profile在期間如何變化。例如，計算每個事務(wù)中block changes可用如下公式：db block changes / ( user commits + user rollbacks )執(zhí)行：select a.value/(b.value+c.value) from v$sysstat a,v$sysstat b,v$sysstat c where =db block changes and =user commits and =user rollbacks;其它計算統(tǒng)計以衡量負(fù)載方式，如下：Blocks changed for each read：這項顯示出block changes在block reads中的比例。它將指出是否系統(tǒng)主要用于只讀訪問或是主要進行諸多數(shù)據(jù)操作(如：inserts/updates/deletes)公式：db block changes / session logical reads執(zhí)行：select a.value/b.value from v$sysstat a,v$sysstat b where =db block changes and =session logical reads ;Rows for each sort：數(shù)據(jù)排序情況公式：sorts (rows) / ( sorts (memory) + sorts (disk) )執(zhí)行：select a.value/(b.value+c.value) from v$sysstat a,v$sysstat b,v$sysstat c where =sorts (rows) and =sorts (memory) and =sorts (disk);. 查看某表的約束條件select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper(&table_name);select c.constraint_name,c.constraint_type,cc.column_name from user_constraints c,user_cons_columns cc where c.owner = upper(&table_owner) and c.table_name = upper(&table_name) and c.owner = cc.owner and c.constraint_name = cc.constraint_name order by cc.position;0. 查看表的信息Select * from user_tables;1. 查看表空間的名稱及大小select t.tablespace_name, round(sum(bytes/(1024*1024),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;select t.tablespace_name, round(sum(bytes/(1024*1024),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;TABLESPACE_NAME TS_SIZE- -CWMLITE 20DRSYS 20ODM 20PERFSTAT 99SYSTEM 4002. 查看回滾段名稱及大小select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ;3. 移動一個表的多個分區(qū)BEGIN FOR x IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = BIG_TABLE2) LOOP EXECUTE IMMEDIATE alter table big_table2 move partition | x.partition_name; END LOOP;END;/4. 查看LOCKSELECT /*+ ORDERED USE_HASH(H,R) */ H.SID HOLD_SID, R.SID WAIT_SID, decode(H.type, ”MR”, ”Media Recovery”, ”RT”, ”Redo Thread”, ”UN”, ”User Name”, ”TX”, ”Transaction”, ”TM”, ”DML”, ”UL”, ”PL/SQL User Lock”, ”DX”, ”Distributed Xaction”, ”CF”, ”Control File”, ”IS”, ”Instance State”, ”FS”, ”File Set”, ”IR”, ”Instance Recovery”, ”ST”, ”Disk Space Transaction”, ”TS”, ”Temp Segment”, ”IV”, ”Library Cache Invalidation”, ”LS”, ”Log Start or Switch”, ”RW”, ”Row Wait”, ”SQ”, ”Sequence Number”, ”TE”, ”Extend Table”, ”TT”, ”Temp Table”, H.type) type, decode(H.lmode, 0, ”None”, 1, ”Null”, 2, ”Row-S (SS)”, 3, ”Row-X (SX)”, 4, ”Share”, 5, ”S/Row-X (SSX)”, 6, ”Exclusive”, to_char(H.lmode) hold, decode(r.request, 0, ”None”, 1, ”Null”, 2, ”Row-S (SS)”, 3, ”Row-X (SX)”, 4, ”Share”, 5, ”S/Row-X (SSX)”,6, ”Exclusive”, to_char(R.request) request, R.ID1,R.ID2,R.CTIMEFROM VLOCK H,VLOCK R WHERE H.BLOCK = 1 AND R.BLOCK=0 and H.TYPE ”MR” AND R.TYPE ”MR” AND H.ID1 = R.ID1 AND H.ID2 = R.ID5. 找ORACLE字符集select * from ps$ where name=NLS_CHARACTERSET; 6. 查看ORACLE運行的OS平臺SQL run begin dbms_output.put_line(dbms_utility.port_string); end; /7. 查看空間詳細(xì)使用情況CREATE OR REPLACE PROCEDURE show_space ( p_segname IN VARCHAR2, p_owner IN VARCHAR2 DEFAULT USER, p_type IN VARCHAR2 DEFAULT TABLE, p_partition IN VARCHAR2 DEFAULT NULL )AS l_total_blocks NUMBER; l_total_bytes NUMBER; l_unused_blocks NUMBER; l_unused_bytes NUMBER; l_lastusedextfileid NUMBER; l_lastusedextblockid NUMBER; l_last_used_block NUMBER; PROCEDURE p (p_label IN VARCHAR2, p_num IN NUMBER) IS BEGIN DBMS_OUTPUT.put_line (RPAD (p_label, 40, .) | p_num); END;BEGIN DBMS_SPACE.unused_space (segment_owner = p_owner, segment_name = p_segname, segment_type = p_type, partition_name = p_partition, total_blocks = l_total_blocks, total_bytes = l_total_bytes, unused_blocks = l_unused_blocks, unused_bytes = l_unused_bytes, last_used_extent_file_id = l_lastusedextfileid, last_used_extent_block_id = l_lastusedextblocki last_used_block = l_last_used_block ); p (Total Blocks, l_total_blocks); p (Total Bytes, l_total_bytes); p (Unused Blocks, l_unused_blocks); p (Unused Bytes, l_unused_bytes); p (Last Used Ext FileId, l_lastusedextfileid); p (Last Used Ext BlockId, l_lastusedextblockid); p (Last Used Block, l_last_used_block);END;/8. 顯示緩沖區(qū)的相關(guān)SQLSELECT tch, file#, dbablk, CASE WHEN obj = 4294967295 THEN rbs/compat segmentELSE (SELECT MAX ( (| object_type| ) | owner| .| object_name) | DECODE (COUNT (*), 1, , maybe!) FROM dba_objects WHERE data_object_id = x.obj) END whatFROM (SELECT tch,file#,dbablk,obj FROM x$bh WHERE state 0 ORDER BY tch DESC) xWHERE ROWNUM = 5;9. 獲取生成的根據(jù)文件名select c.value |/ | d.instance_name | _ora_ |a.spid | .trc trace from v$process a,v$session b,v$parameter c,v$instance d where a.addr=b.paddr and b.audsid=userenv(sessionid) and =user_dump_dest ;在v$session_longops視圖中，sofar字段表示已經(jīng)掃描的塊數(shù)，totalwork表示總得需要掃描的塊數(shù)，所以我們即可以對正在運行的長查詢進行監(jiān)控，比如在索引創(chuàng)建時，查看索引創(chuàng)建的進度，也可以查看系統(tǒng)中以往的長查詢。col opname format a32col target_desc format a32col perwork format a12set lines 131select sid,OPNAME,TARGET_DESC,sofar,TOTALWORK,trunc(sofar/totalwork*100,2)|% as perwork from v$session_longops where sofar!=totalwork;set lines 121set pages 999col opname format a29col target format a29col target_desc format a12col perwork format a12col remain format 99col start_time format a21col sofar format 99999999col totalwork format 99999999col sql_text format a101col bufgets format 99999999select opname,target,to_char(start_time,yy-mm-dd:hh24:mi:ss) start_time,elapsed_seconds elapsed,executions execs,buffer_gets/decode(executions,0,1,executions) bufgets,module,sql_text from v$session_longops sl,v$sqlarea sa where sl.sql_hash_value = sa.hash_value and upper(substr(module,1,4) RMAN and substr(opname,1,4) RMAN and module SQL*Plus and sl.start_timetrunc(sysdate) order by start_time;0. IO事件監(jiān)控1. 監(jiān)控事例的等待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;2. 回滾段的爭用情況select name, waits, gets, waits/gets Ratio from v$rollstat a, v$rollname b where a.usn = b.usn;3. 監(jiān)控表空間的 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;4. 監(jiān)控文件系統(tǒng)的 I/O 比例select substr(a.file#,1,2) #, substr(,1,30) Name, a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#;1. 在某個用戶下找所有的索引select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name from user_ind_columns, user_indexes where user_ind_columns.index_name=user_indexes.index_name and user_ind_columns.table_name = user_indexes.table_name order by user_indexes.table_type, user_indexes.table_name, user_indexes.index_name, column_position;2. 監(jiān)控 SGA 的命中率select a.value + b.value logical_reads, c.value phys_reads, round(100* (a.value+b.value)-c.value) / (a.value+b.value) BUFFER HIT RATIO from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;3. 監(jiān)控 SGA 中字典緩沖區(qū)的命中率 select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 miss ratio, (1-(sum(getmisses)/ (sum(gets)+sum(getmisses)*100 Hit ratio from v$rowcache where gets+getmisses 0 group by parameter, gets, getmisses; 監(jiān)控 SGA 中共享緩存區(qū)的命中率，應(yīng)該小于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;顯示所有數(shù)據(jù)庫對象的類別和大小 select count(name) num_instances ,type ,sum(source_size) source_size , sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size, sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size group by type order by 2;監(jiān)控 SGA 中重做日志緩存區(qū)的命中率，應(yīng)該小于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);監(jiān)控內(nèi)存和硬盤的排序比率，最好使它小于 .10，增加 sort_area_size:SELECT name, value FROM v$sysstat WHERE name IN (sorts (memory), sorts (disk);監(jiān)控當(dāng)前數(shù)據(jù)庫誰在運行什么SQL語句SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;監(jiān)控字典緩沖區(qū)select (sum(pins - reloads) / sum(pins) lib cache from v$librarycache; select (sum(gets - getmisses - usage - fixed) / sum(gets) row cache from v$rowcache; select sum(pins) executions, sum(reloads) cache misses while executing from v$librarycache;后者除以前者,此比率小于1%,接近0%為好。select sum(gets) dictionary gets,sum(getmisses) dictionary cache get misses from v$rowcache;4. 監(jiān)控 MTS select busy/(busy+idle) shared servers busy from v$dispatcher; 此值大于0.5時，參數(shù)需加大。 select sum(wait)/sum(totalq) dispatcher waits from v$queue where type=dispatcher; select count(*) from v$dispatcher; select servers_highwater from v$mts; servers_highwater接近mts_max_servers時，參數(shù)需加大5. 碎片程度select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)10;alter tablespace name coalesce; alter table name deallocate unused;create or replace view ts_blocks_v as select tablespace_name,block_id,bytes,blocks,free space segment_name from dba_free_space union all select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;select * from ts_blocks_v;select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space group by tablespace_name;6. 查看碎片程度高的表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);7. 表、索引的存儲情況檢查select segment_name,sum(bytes),count(*) ext_quan from dba_extents where tablespace_name =&tablespace_name and segment_type=TABLE group by tablespace_name, segment_name;select segment_name,count(*) from dba_extents where segment_type=INDEX and owner=&owner group by segment_name;8. 找使用CPU多的用戶sessioncpu used by this session：查找各回話連接的CPU使用率select a.sid,spid,status,substr(gram,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;9. 尋找CPU使用過量的sessionSELECT sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN (SELECT decode(sql_hash_value, 0, prev_hash_value, sql_hash_value), decode(sql_hash_value, 0, prev_sql_addr, sql_address) FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = &pid) ORDER BY piece ASC;1.1.2. 數(shù)據(jù)庫巡檢管理. 日常檢測.1. 檢測系統(tǒng)卷狀態(tài)df -k可以看到系統(tǒng)各個卷的使用情況; 如果文件系統(tǒng)的剩余空間小于 20% ，需刪除不用的文件以釋放空間。 或找系統(tǒng)管理員調(diào)整空間大小。.2. 檢查表空間的使用情況 SELECT tablespace_name, max_m, count_blocks free_blk_cnt, sum_free_m, to_char(100*sum_free_m/sum_m, 99.99) | % AS pct_free FROM