ORACLE数据库日常维护作业计划.doc

Oracle Advanced Product ServicesOracle数据库日常维护手册1.1.工作内容31.1.1每日工作31.1.2每周的工作 （通过工具自动跑出结果，手工进行问题处理）51.1.3每月的工作（人工分析处理）221.1.4 每年的工作（结合工具进行全面检查）25文档文档控制审阅记录审阅人职位 分发记录审阅人职位 相关文档1.1. 工作内容1.1.1 每日工作1.1.1.1 检查数据库状态 （目前人工，以后自动，）确认所有的INSTANCE状态以及listener状态正常，登陆到所有数据库或例程，检测ORACLE后台进程: $ ps ef|grep ora$ lsnrctl statusSQL select status from v$instance;1.1.1.2 检查CPU使用率 （自动，无报警即可）top / glance/vmstatsar -u 60 100例如sar u显示内容包括： %usr：CPU处在用户模式下的时间百分比。 %sys：CPU处在系统模式下的时间百分比。 %wio：CPU等待输入输出完成时间的百分比。 %idle：CPU空闲时间百分比对于%usr，建议客户定期收集，建立系统性能的基线，以便判断系统CPU使用的趋势。如果%usr突然异常增长，建议客户使用topas,glance找到使用CPU最高资源的进程，进行进一步分析。如果是oracle用户进程，可以使用如下语句获得该进程正在执行的SQL：-通过PID找SQLSELECT /*+ ORDERED */ 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;而且我们通过v$session的sid，可以进一步从v$session_wait得到该进程的等待事件：-通过PID找该进程的等待事件：SELECT /*+ ORDERED */ event,p1,p2,p3 FROM v$session_wait a WHERE a.sid IN ( SELECT b.sid FROM v$session b WHERE b.paddr = (SELECT addr FROM v$process c WHERE c.spid = &pid);而得到该语句的执行计划，我们可以如下查询：SELECT /*+ ORDERED */ OPERATION , OPTIONS, OBJECT_NAME, OPTIMIZER, ID, PARENT_ID, COST,CARDINALITY,BYTES FROM v$sql_plan a where a.hash_value=&hav and a.address=&address;对于%wio，如果该数值过高，表示硬盘存在I/O等待。对于%idle，表明了系统CPU的空闲率，如果该数值持续低于10，那么系统需要扩充CPU资源。1.1.1.3 检查内存使用率 （自动，无报警即可）vmstat 5 5sar w 5 5lsps s #列出pagespaceswapinfo1.1.1.4 检查表空间的使用情况select f.tablespace_name,a.total,f.free,round(f.free/a.total)*100) % Free from(select tablespace_name, sum(bytes/(1024*1024) total from dba_data_files group by tablespace_name) a,(select tablespace_name, round(sum(bytes/(1024*1024) free from dba_free_space group by tablespace_name) fWHERE a.tablespace_name = f.tablespace_name(+)order by % Free1.1.1.5 event使用日常工作中经常会使用一些event事件来处理ORACLE问题,例如对整个数据库设置event事件，或对某个session设置event事件，通过event事件可以处理或跳过一些ORACLE问题针对system级别设置event事件SQLALTER SYSTEM SET EVENT=10325 trace name context forever, level 10, 10015 trace name context forever, level 1 COMMENT=Debug tracing of control and rollback SCOPE=SPFILE;针对session级别设置event事件，例如设置10046 event事件10046 EVENT levels: 1 - Enable standard SQL_TRACE functionality (Default) 4 - As Level 1 PLUS trace bind values 8 - As Level 1 PLUS trace waits This is especially useful for spotting latch wait etc. but can also be used to spot full table scans and index scans. 12 - As Level 1 PLUS both trace bind values and waits使用SQL语句设定10046事件SQLalter session set events 10046 trace name context forever, level 12;SQLalter session set events 10046 trace name context off;使用包设置给对应session设置和关闭event事件SQLexecute sys.dbms_system.set_ev(133,3117,10046,12, );SQLexecute sys.dbms_system.set_ev(133,3117,10046,0,);1.1.1.6 oradebug使用$ ps -ef|grep lmonoracle 8874 1 0 16:27 ? 00:00:18 ora_lmon_orcl2oracle 3243 2439 0 19:02 pts/3 00:00:00 grep lmon$ sqlplus / as sysdbaSQLoradebug setospid 8874SQLoradebug ipcSQLoradebug tracefile_name;SQL exit具体生成的debug信息bdump目录下-rw-rw- 1 oracle dba 7744 5月 3 19:04 orcl2_lmon_8874.trc这个debug主要可以查看心跳线走的是那个IP1.1.2 每周的工作 （通过工具自动跑出结果，手工进行问题处理）1.1.2.1 检查文件系统如果文件系统的剩余空间过小或增长较快，需对其进行确认并删除不用的文件以释放空间。 $df k$df hHP-UX: $bdf1.1.2.2 检查裸设备Aix系统$lsvg #查看vg名称$lsvg l vgname #查看vg对应裸设备是否是open状态Vxvm卷管理vxdisk list -看磁盘列表vxdisk list disk_name -看指定磁盘信息1.1.2.3 定期检查TRACE文件及listener日志如果这些日志量非常大，占用很大的资源空间，可考虑定期删除以便释放资源。一般情况下这些文件存在的相关目录：警告日志： $ORACLE_BASE/admin/bdump 或者通过 SQL show parameter background_dump_dest参数查看其存放位置；Trc文件： $ORACLE_BASE/admin/udump或者通过 SQL show parameter user_dump_dest参数查看其存放位置；Listener日志： $ORACLE_HOME/network/log*针对网络连接方式，一般用户都了解通过设置客户端tnsnames.ora连接服务器端的listener,在日常工作中经常遇到一些网络或配置问题导致在数据库及listener运行正常的情况下，客户端仍然无法正常连接服务器的情况。通过一个例子来描述该情况。客户端一般通过访问本地的tnsnames.ora配置别名访问数据库服务器，具体该文件有默认路径在$ORACLE_HOME/network/admin目录下，当然也可以通过设置环境变量TNS_ADMIN来改变他的默认路径。*如果设置服务器端的sqlnet.ora文件会导致所有的客户端连接都产生trace文件，导致所有的客户端响应速度降低。一般出现问题首先设置客户端sqlnet.ora文件来跟踪客户端连接。这里给出一个例子服务器 192.168.111.2客户端 192.168.111.1首先给出一个服务器段的监听配置$ cat listener.ora SID_LIST_LISTENER_SERVER1 = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /oracle/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) )LISTENER_SERVER1 = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1-vip)(PORT = 1522)(IP = FIRST) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.2)(PORT = 1522)(IP = FIRST) ) )客户端tnsnames.ora配置RAC1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.2)(PORT = 1522) ) (CONNECT_DATA = (SERVICE_NAME = test) ) )在连接过程中，sqlplus system/sysrac1ERROR:ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务有ORA-12514错误警告，为了进一步分析，可以通过设置客户端sqlnet.ora文件中添加TRACE_LEVEL_CLIENT = 16来跟踪客户端连接服务器的整个过程来判断问题所在。在客户端设置后，会在当前路径下生成对应的trace文件。1.1.2.4 检查告警日志文件（alert_SID.log）Oracle在运行过程中，会在警告日志文件(alert_SID.log)中记录数据库的一些运行情况：数据库的启动、关闭，启动时的非缺省参数；数据库的重做日志切换情况，记录每次切换的时间，及如果因为检查点(checkpoint)操作没有执行完成造成不能切换，会记录不能切换的原因；对数据库进行的某些操作，如创建或删除表空间、增加数据文件；数据库发生的错误，如表空间不够、出现坏块、数据库内部错误(ORA600)定期检查日志文件，根据日志中发现的问题及时进行处理问题处理启动参数不对检查初始化参数文件因为检查点操作或归档操作没有完成造成重做日志不能切换如果经常发生这样的情况，可以考虑增加重做日志文件组；想办法提高检查点或归档操作的效率；有人未经授权删除了表空间检查数据库的安全问题，是否密码太简单；如有必要，撤消某些用户的系统权限出现坏块检查是否是硬件问题(如磁盘本生有坏块)，如果不是，检查是那个数据库对象出现了坏块，对这个对象进行重建表空间不够增加数据文件到相应的表空间出现ORA-600根据日志文件的内容查看相应的TRC文件，如果是Oracle的bug，要及时打上相应的补丁alert_SID.log通常是在$ORACLE_BASE/admin/bdump目录下。使用 Unix tail或者more命令查看该日志信息（为了方便查询，每天查询后将查询的日志mv到与日志同一目录下的bak目录下）；告警日志常见报错分析：ORA-00600,ORA-07445主要参考文档153788.1，在必要时，请提交SR，获得更多的服务。ORA-04031主要参考文档430473.1，146599.1，在必要时，请提交SR，获得更多的服务。主要分析有：使用pool advice来分析shared_pool是否需要调整；共享池碎片问题，主要分析是否存在硬解析的语句及其他原因；详细内容可参考附件：也可以考虑添加event:SQL alter session set events 4031 trace name errorstack level 3; SQL alter session set events 4031 trace name HEAPDUMP level 536870914;ORA-01555：分析alert及对应的trace文件，检查发生快照过旧对应的SQL语句；分析上述语句其运行时间是否异常，对其进行相应的优化和调整；UNDO表空间大小调整和undo_retention时间长短调整；4，检查v$undostat视图内容；ORA-01653及ORA-01654：请主要添加表空间的数据文件，保障表空间的可用空间。ORA-01684及ORA-01685：请注意监控表和索引对象的max_extents参数设定。ORA-00060Oracle会自动将死锁的一个会话回滚，请开发人员检查其业务逻辑。1.1.2.5 查看数据库连接信息定时对数据库的连接情况进行检查，看与数据库建立的会话数目是不是正常，如果建立了过多的连接，会消耗数据库的资源。同时，对一些“挂死”的连接，可能需要手工进行清理。以下的SQL语句列出当前数据库建立的会话情况：select count(*) from v$session; 查看当前会话连接数select sid,serial#,username,program,machine,status from v$session; -查看会话的详细信息其中，SID 会话(session)的ID号；SERIAL# 会话的序列号，和SID一起用来唯一标识一个会话；USERNAME 建立该会话的用户名；PROGRAM 这个会话是用什么工具连接到数据库的；STATUS 当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表示当前会话没有执行任何操作； 如果DBA要手工断开某个会话，则执行：（一般不建议使用这种方式去杀掉数据库的连接，这样有时候session不会断开。容易引起死连接。建议通过sid查到操作系统的spid,使用ps ef|grep spidno的方式确认spid不是ORACLE的后台进程。使用操作系统的kill -9命令杀掉连接 ）alter system kill session SID,SERIAL#;注意：上例中SID为1到10(USERNAME列为空)的会话，是Oracle的后台进程，不要对这些会话进行任何操作。1.1.2.6 检查数据文件的状态select file_name,status from dba_data_files;如果数据文件的STATUS列不是AVAILABLE，那么就要采取相应的措施，如对该数据文件进行恢复操作，或重建该数据文件所在的表空间。1.1.2.7 检查SGA,PGA使用情况Select * from v$sgastat;Select * from v$pgastat;*关注SGA，PGA区域使用问题*在日常工作中，经常遇到应用程序没有使用绑定变量大量消耗shared pool的案例，通过下面脚本查看消耗shared pool的语句。select substr(sql_text,1,400) sql_text,sum(SHARABLE_MEM) from v$sqlarea having count(*) 100 group by substr(sql_text,1,400)order by sum(SHARABLE_MEM)判断每个进程占用PGA的使用情况col c1 heading Program|Name format a30col c2 heading PGA|Used|Memory format 999,999,999col c3 heading PGA|Allocated|Memory format 999,999,999,999col c4 heading PGA|Maximum|Memory format 999,999,999,999selectprogram c1,pga_used_mem c2,pga_alloc_mem c3,pga_max_mem c4fromv$processorder byc4 desc;1.1.2.8 检查数据库的等待事件set pages 80set lines 120col event for a40select sid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where event not like SQL% and event not like rdbms%如果数据库长时间持续出现大量像latch free，enqueue，buffer busy waits，db file sequential read，db file scattered read等等待事件时，需要对其进行分析，可能存在问题的语句。1.1.2.9 检查临时表空间占用V$sort_usage;V$sort_segment;查看具体占用临时表空间的语句如下：select vs.username, vs.module, vs.action, vs.saddr, v1.session_addr, v1.session_num, v1.tablespace, contents, v1.segtype, v1.segfile#, v1.extents, V2.TOTAL_EXTENTS, v2.used_extents, v2.free_extents, v2.MAX_SIZE, v2.max_used_size, v2.current_users, dt.file_name, VQ.SQL_TEXT, VQ.RUNTIME_MEM, VQ.SORTS from v$sort_usage v1, v$sort_segment v2, dba_temp_files DT, v$session vs , V$SQL VQ where v1.tablespace = v2.tablespace_name and v2.tablespace_name = dt.tablespace_name and vs.saddr = v1.session_addr and VQ.ADDRESS =VS.SQL_ADDRESSAND NVL(VQ.MODULE, ) = NVL(VS.MODULE, )AND VS.STATUS = ACTIVE1.1.2.10 检查undo表空间占用检查是否存在UNDO表空间空间不足情况：Select * from v$undostat where nospaceerrcnt0;*检查当前回滚表空间的使用,通过该脚本可以准确获取回滚表空间的使用情况*select decode(sign(round(100*(UNDOB-UNDO*DBS)/UNDOB),0)-10), 1,normal:,decode(sign(round(100*(UNDOB-UNDO*DBS)/UNDOB),0)-20), 1,warning:,error:)| the |instance_name| undo tablespace |tablespace_name| total space |UNDOB/1024/1024|MB used space |round(UNDO*DBS/1024/1024),0)|MB idle |round(100*(UNDOB-UNDO*DBS)/UNDOB),0)|% at |to_char(sysdate,yyyymmddhh24miss) as aFROM (select instance_name from V$instance),(select nvl(sum(undoblks),0) UNDO from v$undostatwhere begin_time (select sysdate - UR/(3600*24) from (select value as UR from v$parameter where name=undo_retention),(select value as DBS from v$parameter where name=db_block_size),(select sum(bytes) as UNDOB,tablespace_name from dba_data_files where tablespace_name=(select upper(value) as UNDO from v$parameter where name=undo_tablespace) group by tablespace_name);*add by lipeng 2009/2/9通过下面脚本可以检查回滚表空间过期与非过期的比例*select sum(bytes)/1024/1024 UNEXPIRED BYTES from dba_undo_extents where tablespace_name=UNDOTBS1and status=UNEXPIRED;select sum(bytes)/1024/1024 EXPIRED BYTES from dba_undo_extents where tablespace_name=UNDOTBS1and status=EXPIRED;*add by lipeng 2009/2/9通过设置_undo_autotune可以启动和关闭回滚段保留功能,在10g中该参数默认为true,该参数可以动态调整*Alter system set _undo_autotune = false;*add by lipeng 2009/2/9检查消耗回滚段的大事务,注意回滚表空间名称*SELECT V.SID, V.SERIAL#, V.USERNAME, U.SEGMENT_NAME, U.BLOCKSFROM V$SESSION V, V$TRANSACTION T, DBA_ROLLBACK_SEGS R, DBA_UNDO_EXTENTS UWHERE V.SADDR=T.SES_ADDR AND R.SEGMENT_NAME=U.SEGMENT_NAME ANDU.STATUS = ACTIVE ANDT.XIDUSN=R.SEGMENT_ID AND R.TABLESPACE_NAME=UNDOTBS1;1.1.2.11 锁表问题的处理查询目前锁对象信息,更细节的锁处理方式请查看工作中长用脚本col sid for 999999col username for a10col schemaname for a10col osuser for a16col machine for a16col terminal for a20col owner for a10col object_name for a30col object_type for a10select sid,serial#,username,SCHEMANAME,osuser,MACHINE,terminal,PROGRAM,owner,object_name,object_type,o.object_id from dba_objects o,v$locked_object l,v$session s where o.object_id=l.object_id and s.sid=l.session_id;解锁处理：alter system kill session &sid,&serial#;*该部分内容主要关注在RAC两个节点间锁表现象，由于RAC两个节点在处理相同业务时候，有可能导致两个节点锁表，下面通过一些具体举例描述该问题。*SELECT INST_ID,DECODE(request,0,Holder: ,Waiter: ) | sid sess, id1, id2, lmode, request, type FROM GV$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM GV$LOCK WHERE request 0) ORDER BY id1, request测试rac环境下的锁create table system.akdas (A1 number, Col1 Varchar2(10), Col2 Varchar2(10); insert into system.akdas values(5,Hello,Hi); insert into system.akdas values(6,Sudip,Datta); insert into system.akdas values(7,Preetam,Roy); insert into system.akdas values(8,Michael,Polaski);From Node 1: = update system.akdas set a1=11 where a1=6;From Node 2: = update system.akdas set a1=12 where a1=7; update system.akdas set a1=11 where a1=6; /* this will wait for Node1: to complete the transaction */查询RAC环境锁的状态select a.INST_ID, a.SID, a.TYPE, a.CTIME,b.INST_ID, b.SID, b.TYPE, b.CTIME from gv$lock a,gv$lock b where (a.ID1,a.ID2,a.TYPE) in (select a.ID1,a.ID2,a.TYPE from gv$lock where request0)and a.ID1 = b.ID1 and a.ID2 = b.ID2and a.type like TX and b.type like TXand a.LMODE = 6and b.LMODE = 0INST_ID SID TY CTIME INST_ID SID TY CTIME- - - - - - - - 1 2148 TX 3036 2 1051 TX 3015prompt CTIME is in Seconds prompt REQUEST is waiterprompt REQUEST = 0 LMODE = 6 is holderset lines 120 col BLOCK for 9 col LMODE for 9 col INST_ID for 9 col REQUEST for 9 col SID for 999999 select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK from gv$lock where (ID1,ID2,TYPE) in (select ID1,ID2,TYPE from gv$lock where request0);INST_ID SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK- - - - - - - - - 1 2148 TX 655451 82666 6 0 46 2 2 1051 TX 655451 82666 0 6 25 0prompt = prompt This Scripts is get who is waiterprompt Query 1. Waiting for TX Enqueue where mode is Exclusive prompt = prompt set linesize 100 set pagesize 66 col c1 for a15 col c1 heading Program Name select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST from gv$lock l,gv$session s where l.type like TX and l.REQUEST =6 and l.inst_id=s.inst_id and l.sid=s.sid order by id1 / INST_ID SID Program Name TY ID1 ID2 LMODE REQUEST- - - - - - - - 2 1051 sqlplusSERV-TEST TX 655451 82666 0 6 2 (TNS V1-V3)prompt prompt This Scripts is get who is holderprompt Query 2. Holding for TX Enqueue where mode greater than 6 prompt = prompt set linesize 100 set pagesize 66 col c1 for a15 col c1 heading Program Name select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST from gv$lock l,gv$session s where l.type like TX and l.LMODE =6 and (l.ID1,l.ID2) in (select id1,id2 from gv$lock where type like TX and REQUEST =6) and l.inst_id=s.inst_id and l.sid=s.sid order by id1 / INST_ID SID Program Name TY ID1 ID2 LMODE REQUEST- - - - - - - - 1 2148 sqlplusSERV-TEST TX 655451 82666 6 0 1 (TNS V1-V3)prompt prompt prompt Query 3. Object# ,File#, Block# and Slot# TX Enqueue in detail prompt = prompt set linesize 110 col c0 for 999 col c0 heading INS col c1 for a15 col c1 heading Program Name select inst_id c0,sid,program c1,ROW_WAIT_OBJ# object_no, ROW_WAIT_FILE# Rfile_no,ROW_WAIT_BLOCK# Block_no ,ROW_WAI