数据库专项项目组日常运维及应急故障处理标准手册

常见问题及处理方案CPU使用率高的问题通过操作系统命令top topas glance等查看top进程号，确认是系统进程还是oracle应用进程，查询当前top进程执行的操作和sql语句进行分析。根据进程号获取正在执行的sqlSELECT a.osuser, a.username,b.address,b.hash_value, b.sql_text from v$session a, v$sqltext b, v$process pwhere p.spid = &spid and p.addr = a.paddr and a.STATUS = ACTIVE and a.sql_address =b.address order by address, piece;数据库无法连接数据库无法连接，一般可能是如下原因造成：（1）数据库宕了（2）监听异常（3）数据库挂起（4）归档目录满（5）数据库或应用主机的网卡出现问题不能正常工作（6）应用主机到数据库主机的网络出现问题。1、数据库宕了立即启动数据库。2、监听异常此时一般体现为：监听进程占用CPU资源大；监听日志异常。此时，立即重启监听，监听重启一般能在1分钟之内完成。3、数据库挂起立即重启数据库。4、归档目录满（1）在没有部署OGG数据同步的情况下，立即清理归档日志文件。（2）如果部署了OGG数据同步，查看OGG正在读取的归档日志文件，立即清理OGG不再需要的日志文件。5、数据库或应用主机的网卡出现问题不能正常工作。立即联系主机工程师处理。6、应用主机到数据库主机的网络出现问题。立即联系网络维护人员查看。CRS/GI无法启动对于10g及11gR1版本的CRS问题1、进入/tmp目录下，看是否产生了crsctl.xxxxx文件如果有的话，看文件内容，一般会提示OCR无法访问，或者心跳IP无法正常绑定等信息。2、如果/tmp目录下没有crsctl.xxxxx文件此时查看ocssd.log文件，看是否能从中得到有价值的信息。可能的问题：网络心跳不通。3、/tmp目录无crsctl.xxxxx且日志中没有报错信息，只有停CRS时的日志信息。此时可能是RAC两个节点对并发裸设备的访问有问题，此时考虑：（1）停掉两个节点的CRS。（2）两个节点先同时去激活并发VG，然后再激活VG。（3）重新启动CRS。对于11gR2的GI问题分析$GRID_HOME/log/nodename目录下的日志文件，看是否能从中找出无法启动的原因。常见问题：1、心跳IP不同。2、ASM实例无法启动。对CRS的故障诊断和分析,参加本文档中RAC部分的MOS文档.数据库响应慢应急处理步骤：（1）找到占用CPU资源大的sql或者模块，然后停掉此应用模块。（2）如果属于由于种种原因引起的数据库hang住情况，立即重启数据库，此时重启需要约15分钟时间。重要说明：如果重启数据库的话，会有如下负面影响：（1）要kill掉所有连接到数据库中的会话，所有会话都会回滚。（2）立即重启的话，不能获取并保留分析数据库挂起原因的信息，在后续分析问题时，没有足够信息用于分析问题产生的根本原因。 一般正常重启的话，都需要手动获取用于分析数据库重启原因的信息，以便编写分析报告，但是在最长情况下，获取日志信息可能就要40分钟时间。此时一般做systemstate dump，且如果是rac情况的话，需要2个节点都做，且需要做2次或以上。常规处理步骤，分如下几种情况处理：（1）所有业务模块都慢。（2）部分业务模块慢。（3）数据库hang住。所有业务模块都慢此时首先查看系统资源，看是否属于CPU资源使用率100%的问题，如果是，参考本章“CPU使用率高的问题”解决办法。如果系统资源正常，那很可能是数据库hang住了，此时参考数据库Hang部分。部分业务模块慢分析运行慢的模块的sql语句：（1）看是否是新上的sql。（2）看执行计划是否高效。（3）优化运行慢的模块的sql语句。数据库hang住应急处理方式：重启数据库。常规处理方式：（1）分析alert日志，看是否能从alert日志中，可以很快找到引起问题的原因。（2）做3级别的hanganalyze，先做一次，然后隔一分钟以后再做一次。并分析hanganalyze 生成的trace文件，看是否可以找到引起数据库hang住的会话的信息。（3）做systemstate dump此时生成systemstate dump的时间会比较长，尤其是在会话数量较多的情况下。且生成dump文件的大小较大，在G级别以上。在生成一次以后，过一分钟再收集一次，另外如果是RAC，那么两个节点都需要收集。对hang做dump请参考“对数据库HANG做DUMP一章”。数据误删除此问题，没有应急办法，只能按如下步骤处理：1、对于10g及以上版本，看是否可以通过闪回进行恢复。2、查看测试环境数据库，看其中是否有需要的数据。3、使用备份进行恢复，此方法一般花费时间较长。快速shutdown数据库1. 停止监听2. 做一个检查点操作SQL alter system checkpoint;3. 杀掉所有LOCAL=NO的操作系统进程AIX、HP-UX、Linux、Solaris：$ ps -ef|grep $ORACLE_SID| grep LOCAL=NO | grep -v grep |awk print $2|xargs -i kill -9 Windows：SQL select orakill |(select value from v$parameter where name = instance_name) | |p.spidfrom v$process p, v$bgprocess bpwhere p.ADDR = bp.PADDR(+)and bp.PADDR is nulland p.SPID is not null;在命令行执行：C: orakill db1 7642C: orakill db1 76444. 停止数据库SQL shutdown immediate清理分布式事务- 9i需要设置_sum_debug_modeSQL alter session set _smu_debug_mode = 4; alter session set nls_date_format=YYYY-MM-DD HH24:MI:SS;column local_trna_id format a20column global_tran_id format a25SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, FAIL_TIME,STATE, MIXED FROM DBA_2PC_PENDING;LOCAL_TRAN_ID GLOBAL_TRAN_ID FAIL_TIME STATE MIX- - - - -12.29.103137 TAXIS.9572b613.12.29.103137 30-aug-2011 10:09:11 collecting noSQL commit force 12.29.103137; Commit complete.SQL EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(12.29.103137);PL/SQL procedure successfully completed.SQL commit;- 清理每个分布式事务都需要commit;数据泵1. 相关参数PARALLEL参数考虑可以设置成物理CPU（不是逻辑CPU）数的两倍数目，然后调整对于Data Pump Export，PARALLEL参数必须要小于等于dump files数对于Data Pump Import，PARALLEL不要比dump文件数大很多，可以大一些。这个参数也指定了导入时创建索引的并行度。PARALLEL只允许在企业版使用。nohup expdp system/manager schemas=kdjm DIRECTORY=DUMP_FILES PARALLEL=3 dumpfile=expCASES_%U.dmp logfile=nnsiexp2008_12_28.log &通配符 %U,它指示文件将按需要创建,格式将为expCASES_nn.dmp,其中nn 从 01 开始,然后按需要向上增加相关监控- 监控长事务set linesize 120column opname heading Operation format a25column target heading Target format a15column pct heading Percent format 999column es heading Elapsed|Seconds format 999999column tr heading Time|Remaining|Seconds format 99999column program format a30column machine format a16select L.sid ssid, substr(opname,1,25) opname, target, trunc(sofar/totalwork)*100) pct, to_char(60*sofar*8192/(24*60*(last_update_time-start_time)/1024/1024/60, 9999.0) Rate, round(elapsed_seconds/60, 2) es, round(time_remaining/60, 2) tr, program, machine from v$session_longops L, v$session s where time_remaining 0 and l.sid = s.sidorder by start_time;坏块恢复在遇到坏块的时，一般应按以下的流程来处理：1 如果坏块的对象是索引，重建索引2 使用备份来进行恢复3 使用10231事件，或者DBMS_REPAIR.SKIP_CORRUPT_BLOCKS过程，让oracle跳过坏块，然后用exp导出表和使用CREATE TABLE AS创建新表。4 尝试使用SQL脚本将完好的数据复制到一个新表中，或者用EXP配合QUERY参数导出完好的数据。5 手工修改坏块。有两种情况是不能使用事件10231和DBMS_REPAIR.SKIP_CORRUPT_BLOCKS来跳过坏块的：1 硬件问题造成OS层不能读取数据。2 表中的非数据块，或者说是元数据块。比如段头，Extent Map块。这种坏块是不能跳过的。3 在表中存在有其他异常的块，从单个块来看都没有损坏，checksum值也是正确的，但是有的块在段内却是有问题的。比如在段的高水位下存在未格式化的块，查询这样的表时，会报ORA-8103错误；如果块的object id与段在数据字典里的data object id不相符，则会报ORA-1401错误。Oracle数据文件的坏块，可分为物理坏块和逻辑坏块。物理坏块（也称为介质坏块），指的是块格式本身是坏的，块内的数据没有任何意义。而逻辑坏块，指的是块内的数据在逻辑上存在问题。比如说索引块的索引值没有按从小到大排列。物理坏块一般是由于内存问题、OS问题、IO子系统问题和硬件问题，逻辑坏块是由于ORACLE BUG等原因引起。对数据库中的坏块进行验证。RMAN backup validate database;恢复一个数据文件上的多个坏块RMAN blockrecover datafile 14 block 56,107,276,517;检验后我们查 V$DATABASE_BLOCK_CORRUPTIONSQL select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO- - - - - 14 276 1 0 CHECKSUM 14 517 1 0 CHECKSUM 14 107 1 0 CHECKSUM 14 56 1 0 CHECKSUM还可以通过blockrecover corruption list进行块的恢复，这是在大量块损坏时或全部块损坏时使用，前提是先执行backup validate database,在V$DATABASE_BLOCK_CORRUPTION里有对应的坏块的列表。RMAN blockrecover corruption list;SQL TUNINGSQL PROFILE创建PROFILEdeclare v_hhint sys.sqlprof_attr; cl_sql_text clob;begin select SQL_TEXT into cl_sql_text from v$sql where sql_id = 1304vvhkfctzq and rownum true, replace = true);end;/获取PFOFILE的名字SELECTname,created,category,sql_Textfromdba_sql_profilesORDERBYcreatedDESC;删除PFOFILEBEGIN DBMS_SQLTUNE.DROP_SQL_PROFILE(name=PROFILE_NAME);END;/ 获取OUTLINE HINT的信息SELECT sql_attr.attr_val outline_hints FROM dba_sql_profiles sql_profiles, sys.SQLPROF$ATTR sql_attr WHERE sql_profiles.signature = sql_attr.signature AND sql_profiles.name = SQLPROFILE_1304vvhkfctzq ORDER BY sql_attr.attr# ASC;STAIn order to execute Sql Tuning Adviser API, one must be granted with “ADVISER” role.grant adviser to ; for a specific statement from AWRSET SERVEROUTPUT ON- Tuning task created for specific a statement from the AWR.DECLAREl_sql_tune_task_id VARCHAR2(100);BEGINl_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (begin_snap = 764,end_snap = 938,sql_id = 19v5guvsgcd1v,scope = DBMS_SQLTUNE.scope_comprehensive,time_limit = 60,task_name = 19v5guvsgcd1v_AWR_tuning_task,description = Tuning task for statement 19v5guvsgcd1v in AWR.);DBMS_OUTPUT.put_line(l_sql_tune_task_id: | l_sql_tune_task_id);END; or for a specific statement from Shared Library CacheDECLAREl_sql_tune_task_id VARCHAR2(100);BEGINl_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (sql_id = fhahkc71k304u,scope = DBMS_SQLTUNE.scope_comprehensive,time_limit = 60,task_name = fhahkc71k304u_tuning_task,description = Tuning task for statement fhahkc71k304u.);DBMS_OUTPUT.put_line(l_sql_tune_task_id: | l_sql_tune_task_id);END;/- Interrupt and resume a tuning task.EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name = emp_dept_tuning_task);EXEC DBMS_SQLTUNE.resume_tuning_task (task_name = emp_dept_tuning_task);- Cancel a tuning task.EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name = emp_dept_tuning_task);- Reset a tuning task allowing it to be re-executed.EXEC DBMS_SQLTUNE.reset_tuning_task (task_name = emp_dept_tuning_task);execute the tuning taskEXEC DBMS_SQLTUNE.execute_tuning_task(task_name = fhahkc71k304u_AWR_tuning_task);report tuning task findingsSET LONG 100000;SET PAGESIZE 1000SET LINESIZE 300SELECT DBMS_SQLTUNE.report_tuning_task(fhahkc71k304u_AWR_tuning_task) AS recommendations FROM dual;SET PAGESIZE 24accept recommendationsexecute dbms_sqltune.accept_sql_profile(task_name =fhahkc71k304u_AWR_tuning_task, replace = TRUE);useful viewsDBA_ADVISOR_TASKSDBA_ADVISOR_FINDINGSDBA_ADVISOR_RECOMMENDATIONSDBA_ADVISOR_RATIONALEDBA_SQLTUNE_STATISTICSDBA_SQLTUNE_BINDSDBA_SQLTUNE_PLANSDBA_SQLSETDBA_SQLSET_BINDSDBA_SQLSET_STATEMENTSDBA_SQLSET_REFERENCESDBA_SQL_PROFILESV$SQLV$SQLAREAV$ACTIVE_SESSION_HISTORY收集统计信息exec dbms_stats.gather_table_stats(ownname = SYS, tabname = T2, estimate_percent = 100, cascade = true, method_opt = for all columns size 1 );显示执行计划select * from table(dbms_xplan.display);select * from table(dbms_xplan.display_cursor(null,null,ALLSTATS LAST);select * from table(dbms_xplan.display_awr(a7tgurqg403wp, null, null, ALL);显示执行计划的outlineselect * from table(dbms_xplan.display_cursor(sql_id, null, outline);显示query block(qb_name)select*fromtable(dbms_xplan.display(null,null,typical alias -rows -bytes -cost);柱状图删除柱状图declare srec dbms_stats.statrec; m_distcnt number; m_density number; m_nullcnt number; m_avgclen number; n_array dbms_stats.numarray; begin dbms_stats.get_column_stats( ownname = user, tabname = t1, colname = n1, distcnt = m_distcnt, density = m_density, nullcnt = m_nullcnt, srec = srec, avgclen = m_avgclen ); srec.bkvals := null; srec.novals := dbms_stats.numarray( utl_raw.cast_to_number(srec.minval), utl_raw.cast_to_number(srec.maxval) ); srec.epc := 2; dbms_stats.prepare_column_values(srec, srec.novals); m_density := 1/m_distcnt; dbms_stats.set_column_stats( ownname = user, tabname = t1, colname = n1, distcnt = m_distcnt, density = m_density, nullcnt = m_nullcnt, srec = srec, avgclen = m_avgclen );exception when others then raise; - should handle div/0end;/表碎片整理Oracle 10g之前一般使用alter table table_name move tablespace，然后rebuild索引，因为move会导致rowid改变，从而原来的索引失效。查看哪些表在空间上存在问题，可以查看dba_tables视图的avg_space列，这列显示的是数据库块的平均空闲空间大小，我们在建表的时候可以通过制定PCTFREE参数来设置每个数据块中保留空间的百分比，这部分空间用于因更新块内的行而导致的增长，默认值是10%，也就是819 bytes左右（块大小为8KB）。如果avg_space大于2KB，可能重整空间会有意义，这个值太大说明空间利用率较低，块空闲较多。我们调整空间的主要目的是降低高水位线（HIGH WATER MARK），使扫描的表块数变小，从而提高效率。Oracle 10g之后alter table table_name enable row movement;alter table table_name shrink space cascade;alter table table_name move disable row movement;清除监听日志lsnrctlsetlog_statusofflsnrctlsetlog_statuson重建oraInventory$ cat /etc/oraInst.locinventory_loc=/oracle/oraInventoryinst_group=oinstall$ cd $ORACLE_HOME/oui/bin$ ./runInstaller -silent -attachHome ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME=OraDb11g_home1或者（/oracle/oraInventory目录可以mv掉，新建个目录）export ORACLE_HOME=/oracle/product/crs/10.2.0cd $ORACLE_HOME/oui/bin./attachHome.sh物化视图删除基于DBLINK的物化视图的时候要确认DBLINK的存在，否则会造成物化视图注册信息无法清除。删除物化视图并不意味着要删除物化视图日志。因为物化视图日志可以同时支持多个物化视图的刷新，如果物化视图日志被删除，那么所有基于这个物化视图日志的物化视图无法再进行快速刷新。在ON COMMIT刷新模式下, 如果基表的DML很频繁, 会造成刷新很频繁, 这可能会造成DROP物化视图的语句一直挂起。 迁移数据文件1.使数据文件离线alter database datafile old_path offline2.移动数据文件RMAN copy datafile old_path to new_path;3 重命名数据文件SQLalter tablespace tbs_name rename datafile old_path to new_path;4 恢复数据文件RMAN recover datafile new_path;5 使数据文件在线alter database datafile new_path online;SQLNETIP访问限制修改（需重启监听）$ORACLE_HOME/network/admin/sqlnet.ora :tcp.validnode_checking=yestcp.invited_nodes=(localhost, 本机ip, 应用服务器ip，管理机ip等)重启监听：lsnrctl stop；lsnrctl start。参考文章When do SQLNET.ORA changes take effect ? (Doc ID 562589.1)How to Centralize TNSNAMES.ORA, LISTENER.ORA And SQLNET.ORA files (Doc ID 362761.1)Configure Multiple DB Instances To Share SQLNET.ORA Without Giving WALLET_LOCATION For EUS (Doc ID 405682.1)Parameter OUTBOUND_CONNECT_TIMEOUT in SQLNET.ORA (Doc ID 519391.1)增加Current SCN主要针对ORA-00600 2662通过adjust_scn event来调整MOS 30681.1文章如下Doc ID: Note:30681.1 Subject: EVENT: ADJUST_SCN - Quick Reference Type: REFERENCE Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 20-OCT-1997 Last Revision Date: 04-AUG-2000 Language: USAENG ADJUST_SCN Event* WARNING * This event should only ever be used under the guidance of an experienced Oracle analyst. If an SCN is ahead of the current database SCN, this indicates some form of database corruption. The database should be rebuilt after bumping the SCN. * The ADJUST_SCN event is useful in some recovery situations where the current SCN needs to be incremented by a large value to ensure it is ahead of the highest SCN in the database. This is typically required if either: a. An ORA-600 2662 error is signalled against database blocks or b. ORA-1555 errors keep occuring after forcing the database open or ORA-604 / ORA-1555 errors occur during database open. (Note: If startup reports ORA-704 & ORA-1555 errors together then the ADJUST_SCN event cannot be used to bump the SCN as the error is occuring during bootstrap. Repeated startup/shutdown attempts may help if the SCN mismatch is small) or c. If a database has been forced open used _ALLOW_RESETLOGS_CORRUPTION (See ) The ADJUST_SCN event acts as described below. *NOTE: You can check that the ADJUST_SCN event has fired as it should write a message to the alert log in the form Debugging event used to advance scn to %s. If this message is NOT present in the alert log the event has probably not fired. If the database will NOT open: Take a backup. You can use event 10015 to trigger an ADJUST_SCN on database open:startup mount;alter session set events 10015 trace name adjust_scn level 1; (NB: You can only use IMMEDIATE here on an OPEN database. If the database is only mounted use the 10015 trigger to adjust SCN, otherwise you get ORA 600 2251, 65535, 95 )alter database open;If you get an ORA 600:2256 shutdown, use a higher level and reopen. Do *NOT* set this event in init.ora or the instance will crash as soon as SMON or PMON try to do any clean up. Always use it with the alter session command. If the database *IS* OPEN: You can increase the SCN thus:alter session set events IMMEDIATE trace name ADJUST_SCN level 1; LEVEL: Level 1 is usually sufficient - it raises the SCN to 1 billion (1024*1024*1024) Level 2 raises it to 2 billion etc. If you try to raise the SCN to a level LESS THAN or EQUAL to its current setting you will get - See below. Ie: The event steps the SCN to known levels. You cannot usethe same level twice. Calculating a Level from 600 errors: To get a LEVEL for ADJUST_SCN:a) Determine the TARGET scn: ora-600 2662 See Use TARGET = blocks SCN ora-600 2256 See Use TARGET = Current SCN b) Multiply the TARGET wrap number by 4. This will give you the level to use in the adjust_scn to get the correct wrap number.c) Next, add the following value to the level to get the desired base value as well : Add to Level Base 0 0 1 24 2 48 3 72在Oracle 10g之前,一般采用使用这个event的方式来增大SCN.ORA-00600: internal error code, arguments: 2662, 0, 4, 0, 3, 8388617, , 这个报错参数的含义在metalink中如此描述的：Arg a Current SCN WRAPArg b Current SCN BASEArg c dependent SCN WRAP为了存储更大的SCN值，当SCN BASE到足够大并开始重置的时候，SCN WRAP将加1。Arg d dependent SCN BASEArg e Where present this is the DBA where the dependent SCN came from.也就是Arg d 的值是从哪个block中找到的，通常是一个data block address。通过这几个参数根据一定的规则可以计算出我们需要的level。计算规则如下：1. Arg c *4得出一个数值，假设为V_Wrap2. 如果Arg d=0，则V_Wrap值为需要的levelArg d 24，V_Wrap+1为需要的levelArg d 48，V_Wrap+2为需要的levelArg d 72，V_Wrap+3为需要的level计算方法的例子ORA-00600: internal error code, arguments: 2662, 0, 2179133, 8656, , , , Arg C *4 = 8656 * 4 = 34624Arg d = 24所以level = 34624 + 1 = 34625因此在这个例子中我们应该执行alter session set events IMMEDIATE trace name ADJUST_SCN level 34625;但是随着oracle scn的patch更新后,11g后续的版本有些已经不能使用这种方式推进scn了.11.2.0.4,12.0.1.0默认屏蔽;11.2.0.2.5屏蔽_minimum_giga_scn调整这个参数可以将SCN提高1G的倍数,即1024*1024*1024.ALTER system SET _minimum_giga_scn = 1 scope = spfile;或者在pfile里加入但是随着oracle scn的patch更新后,11g后续的版本有些已经不能使用这种方式推进scn了.11.2.0.4,12.0.1.0默认屏蔽;修改控制文件推荐SCNdump函数联合ue修改oradebug修改SCNBBED修改SCN参考文章Master Note: Overview for SCN issues (Doc ID 1503937.1)分区表11g interval分区按年自增CREATE TABLE year_interval_partition_table(id number,time_col date) partition by range(time_col)INTERVAL (NUMTOYMINTERVAL(1,year)(PARTITION part1 VALUES LESS THAN (TO_DATE (2013-06-0