Oracle数据库日常管理方案精.docx

1. Oracle数据库日常管理方案Oracle数据库的运维处理，更主要的是做好数据库的管理工作，做好数据库的检查与备份工作，然后需要熟悉相关的逻辑结构故障处理方法、物理结构故障处理方法，应对数据库损坏的情况，在数据库发生损坏而无法修复的时候，能利用数据库管理工作中所做的数据备份及时恢复系统、数据，恢复正常生产工作。1.1. 数据库管理Oracle数据库的管理主要是要做好日常的检查管理工作，并检查好数据库的备份情况，在发生紧急情况时能及时不仅切换到备用系统上进行工作，并要能在数据库数据出现异常的时候，能及时恢复数据。因此，做好日常的检查与管理工作尤其重要，主要工作包括：l 数据库的性能监控检查l 数据库状态巡检检查l 数据库备份情况与可恢复性检查1.1.1. 数据库性能监控管理通过对数据库性能监控的管理工作，可以有效的防范风险事件的发生，并能从长时间的运行数据中，总结出数据库性能的状态，为系统性能优化、隐患事件排查提供更好的依据。. 查找前十条性能差的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;. 查询全表扫描的表SQL 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;. 查询硬语法分析的次数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)执行：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)执行：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：这项将显示系统是否有太多硬解析。该值将会与原始统计数据对比以确保精确。例如，软解析率仅为0.2则表示硬解析率太高。不过，如果总解析量(parse count total)偏低，这项值可以被忽略。公式：1 - ( parse count (hard) / parse count (total) ) 执行：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：该项显示内存中完成的排序所占比例。最理想状态下，在OLTP系统中，大部分排序不仅小并且能够完全在内存里完成排序。公式：sorts (memory) / ( sorts (memory) + sorts (disk) )执行：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：在生产环境，最理想状态是一条sql语句一次解析多数运行。公式：1 - (parse count/execute count)执行：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花费在执行及解析上的比率。如果这项比率较低，说明系统执行了太多的解析。公式：1 - (parse time cpu / CPU used by this session)执行：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进行周期运算(即生产工作)。解析时间花费不在CPU周期运算通常表示由于锁竞争导致了时间花费。公式：parse time cpu / parse time elapsed执行：select a.value/b.value from v$sysstat a,v$sysstat b where =parse time cpu and =parse time elapsed;从V$SYSSTAT获取负载间档(Load Profile)数据负载间档是监控系统吞吐量和负载变化的重要部分，该部分提供如下每秒和每个事务的统计信息：logons cumulative, parse count (total), parse count (hard), executes, physical reads, physical writes, block changes, and redo size.被格式化的数据可检查rates是否过高，或用于对比其它基线数据设置为识别system profile在期间如何变化。例如，计算每个事务中block changes可用如下公式：db block changes / ( user commits + user rollbacks )执行：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;其它计算统计以衡量负载方式，如下：Blocks changed for each read：这项显示出block changes在block reads中的比例。它将指出是否系统主要用于只读访问或是主要进行诸多数据操作(如：inserts/updates/deletes)公式：db block changes / session logical reads执行：select a.value/b.value from v$sysstat a,v$sysstat b where =db block changes and =session logical reads ;Rows for each sort：数据排序情况公式：sorts (rows) / ( sorts (memory) + sorts (disk) )执行：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. 移动一个表的多个分区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. 查看空间详细使用情况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. 显示缓冲区的相关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. 获取生成的根据文件名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字段表示已经扫描的块数，totalwork表示总得需要扫描的块数，所以我们即可以对正在运行的长查询进行监控，比如在索引创建时，查看索引创建的进度，也可以查看系统中以往的长查询。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事件监控1. 监控事例的等待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. 监控表空间的 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. 监控文件系统的 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. 监控 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. 监控 SGA 中字典缓冲区的命中率 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; 监控 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;显示所有数据库对象的类别和大小 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;监控 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);监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size:SELECT name, value FROM v$sysstat WHERE name IN (sorts (memory), sorts (disk);监控当前数据库谁在运行什么SQL语句SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;监控字典缓冲区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. 监控 MTS select busy/(busy+idle) shared servers busy from v$dispatcher; 此值大于0.5时，参数需加大。 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时，参数需加大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. 数据库巡检管理. 日常检测.1. 检测系统卷状态df -k可以看到系统各个卷的使用情况; 如果文件系统的剩余空间小于 20% ，需删除不用的文件以释放空间。 或找系统管理员调整空间大小。.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