1、数据库项目组日常运维及应急故障处理手册常见问题及处理方案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 a.STATUS = ACTIVE and a.sql_address =b
2、.address order by address, piece;数据库无法连接数据库无法连接，一般可能是如下原因造成：1数据库宕了2监听异常3数据库挂起4归档目录满5数据库或应用主机的网卡出现问题不能正常工作6应用主机到数据库主机的网络出现问题。1、数据库宕了立即启动数据库。2、监听异常此时一般表达为：监听进程占用CPU资源大；监听日志异常。此时，立即重启监听，监听重启一般能在1分钟之内完成。3、数据库挂起立即重启数据库。4、归档目录满1在没有部署OGG数据同步的情况下，立即清理归档日志文件。2如果部署了OGG数据同步，查看OGG正在读取的归档日志文件，立即清理OGG不再需要的日志文件。5、
3、数据库或应用主机的网卡出现问题不能正常工作。立即联系主机工程师处理。6、应用主机到数据库主机的网络出现问题。立即联系网络维护人员查看。CRS/GI无法启动对于10g及11gR1版本的CRS问题1、进入/tmp目录下，看是否产生了文件如果有的话，看文件内容，一般会提示OCR无法访问，或者心跳IP无法正常绑定等信息。2、如果/tmp目录下没有文件此时查看ocssd.log文件，看是否能从中得到有价值的信息。可能的问题：网络心跳不通。3、/tmp目录无c且日志中没有报错信息，只有停CRS时的日志信息。此时可能是RAC两个节点对并发裸设备的访问有问题，此时考虑：1停掉两个节点的CRS。2两个节点先同时
4、去激活并发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立即重启的话，不能获取并
5、保存分析数据库挂起原因的信息，在后续分析问题时，没有足够信息用于分析问题产生的根本原因。 一般正常重启的话，都需要手动获取用于分析数据库重启原因的信息，以便编写分析报告，但是在最长情况下，获取日志信息可能就要40分钟时间。此时一般做systemstate dump，且如果是rac情况的话，需要2个节点都做，且需要做2次或以上。常规处理步骤，分如下几种情况处理：1所有业务模块都慢。2局部业务模块慢。3数据库hang住。所有业务模块都慢此时首先查看系统资源，看是否属于CPU资源使用率100%的问题，如果是，参考本章“CPU使用率高的问题解决方法。如果系统资源正常，那很可能是数据库hang住了，此时
6、参考数据库Hang局部。局部业务模块慢分析运行慢的模块的sql语句：1看是否是新上的sql。2看执行方案是否高效。3优化运行慢的模块的sql语句。数据库hang住应急处理方式：重启数据库。常规处理方式：1分析alert日志，看是否能从alert日志中，可以很快找到引起问题的原因。2做3级别的hanganalyze，先做一次，然后隔一分钟以后再做一次。并分析hanganalyze 生成的trace文件，看是否可以找到引起数据库hang住的会话的信息。3做systemstate dump此时生成systemstate dump的时间会比拟长，尤其是在会话数量较多的情况下。且生成dump文件的大小较
7、大，在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 $ORACL
8、E_SID| grep LOCAL=NO | grep -v grep |awk print $2|xargs -i kill -9 Windows：SQL select orakill |from 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_mode
9、SQL 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- - - -
10、-12.29.103137 TAXIS.9572b613.12.29.103137 30-aug-2021 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数的两
11、倍数目，然后调整对于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,它指示文件将按需要创立,格式将为expCASES_nn.dmp,其中nn 从 01 开始,然后按需要向上增加相关监控- 监控
12、长事务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
13、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 order by start_tim
14、e;坏块恢复在遇到坏块的时，一般应按以下的流程来处理：1 如果坏块的对象是索引，重建索引2 使用备份来进行恢复3 使用10231事件，或者过程，让oracle跳过坏块，然后用exp导出表和使用CREATE TABLE AS创立新表。4 尝试使用SQL脚本将完好的数据复制到一个新表中，或者用EXP配合QUERY参数导出完好的数据。5 手工修改坏块。有两种情况是不能使用事件10231和来跳过坏块的：1 硬件问题造成OS层不能读取数据。2 表中的非数据块，或者说是元数据块。比方段头，Extent Map块。这种坏块是不能跳过的。3 在表中存在有其他异常的块，从单个块来看都没有损坏，checksum值
15、也是正确的，但是有的块在段内却是有问题的。比方在段的高水位下存在未格式化的块，查询这样的表时，会报ORA-8103错误；如果块的object id与段在数据字典里的data object id不相符，那么会报ORA-1401错误。Oracle数据文件的坏块，可分为物理坏块和逻辑坏块。物理坏块也称为介质坏块，指的是块格式本身是坏的，块内的数据没有任何意义。而逻辑坏块，指的是块内的数据在逻辑上存在问题。比方说索引块的索引值没有按从小到大排列。物理坏块一般是由于内存问题、OS问题、IO子系统问题和硬件问题，逻辑坏块是由于ORACLE BUG等原因引起。对数据库中的坏块进行验证。RMAN backup
16、 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 CHECKSU
17、M还可以通过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
18、 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
19、_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 A
20、WRSET 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_na
21、me = 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.c
22、reate_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.EX
23、EC 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 DBM
24、S_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_
25、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_
26、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 );
27、显示执行方案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;
