13518219792
Oracle数据库无响应故障处理方式
目前成都创新互联公司已为近千家的企业提供了网站建设、域名、雅安服务器托管、网站托管、企业网站设计、河津网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。
Oracle数据库无响应故障，简单地讲就是数据库实例不能响应客户端发起的请求，客户端提交一个SQL后，就一直处于等待数据库实例返回结果的状态。更严重的现象是客户端根本不能连接到数据库，发起一个连接请求后，一直处于等待状态。Oracle数据库无响应故障怎么处理呢?下面跟我一起来学习Oracle数据库无响应故障的处理方法吧!
无响应的故障现象一般有以下几种：
1.Oracle的进程在等待某个资源或事件
这种现象一般可以从V$SESSION_WAT、V$LATCH、V$LATCHHOLDER等动态视图中检查进程正在等待的资源或事件，而被等待的资源或事件，一直都不能被获取，甚至是很长时间都不可获得。如果这个正在等待的进程持有了其他的资源，则会引起其他的进程等待，这样就很可能引起实例中大范围的会话发生等待。由于进程在等待资源或事件时，通常都处于SLEEP状态，消耗的CPU资源非常少(在等待latch时要稍微多消耗一些CPU资源)，所以从OS来看，CPU的消耗并不高，甚至是非常低。
这种因为等待而引起的个别进程Hang，相对比较容易处理。
2. OracleProcess Spins
所谓Spin，就是指Oracle进程中的代码在执行某个过程时，陷入了循环。在V$SESSION视图中，往往可以看到Hang住的会话，一直处于“ACTIVE”状态。对于这样的会话，用“alter system kill session ‘sid,serial#’”命令也不能完全断开会话，会话只能被标记为“killed”，会话会继续消耗大量的CPU。进程Spins由于是在做循环，CPU的消耗非常大，从OS上明显可以看到这样的进程，通常会消耗整个CPU的资源。
而对于这样的Hang住的会话，处理起来相对比较复杂，并且为了从根本上解决问题，需要超过DBA日常维护所需要的技能。
从故障范围来看，无响应故障可以分为以下几种情况：
1. 单个或部分会话(进程)Hang住
这种情况属于小范围的故障，业务影响相对较小，一般来说只会影响业务系统的个别模块。在一个多应用系统的数据库上面，如果Hang住的会话比较多，则影响的可能是其中的一个应用系统。这里有一个例外，如果Hang住的进程是系统后台进程，如pmon、smon等，则影响的范围就非常大了，最终甚至会影响整个数据库及所有应用系统。还有值得注意的是，即使是少部分会话Hang住，也要及时处理，否则极有可能会扩散到整个系统。
2. 单个数据库实例Hang住
这种情况造成的影响非常大。在这个实例上的所有应用系统均受到严重影响，并且在找到根源并最终解决问题之前，数据库实例往往须要重启。
3. OPS或RAC中的多个实例或所有实例都Hang住
在这种情况下，即使是OPS或RAC，都已经没办法提供高可用特性了。使用这个数据库的所有应用系统将不能继续提供服务，这种情况往往须要重启。
无响应故障成因分析
Oracle数据库无响应，一般主要由以下几种原因引起：
1. 数据库主机负载过高，严重超过主机承受能力
比如应用设计不当，数据库性能低下，活动会话数的大量增加，导致数据库主机的负载迅速增加，数据库不能正常操作，并最终Hang住;主机物理内存严重不足，引起大量的换页，特别是在SGA中的内存被大量换出到虚拟内存时，数据库实例往往就会Hang住。
2. 日常维护不当、不正确的操作引起数据库Hang住
比如归档日志的存储空间满，导致数据库不能归档，引起数据库Hang住;在一个大并发的繁忙的系
统上，对DML操作比较多的大表进行move、增加外键约束等操作也可能使系统在短时间内负载大幅升高，并引起数据库系统Hang住;不正确的资源计划(Resource Plan)配置，使进程得不到足够的CPU等。
3. Oracle数据库的Bug
几乎每个版本都存在着会导致数据库系统Hang住的Bug，这些Bug会在一些特定的条件下触发，特别是在RAC数据库中，引起数据库Hang住的Bug比较多。
4. 其他方面的一些原因
比如在RAC数据库中，如果一个节点退出或加入到RAC的过程中，当进行Resource Reconfiguration时，会使系统冻结一段时间，也有可能使系统Hang住。
以上所描述的几种常见的会导致Oracle数据库实例Hang住的原因中，大部分的情况是可以避免的，只要维护得当，一般不会出现这种故障。对于Oracle数据库Bug所导致的数据库无响应故障，由于是在特定的情况下才会触发，所以如果能够尽量对数据库打上最新版本的补丁，并且熟悉当前版本中会导致系统Hang住的Bug以及触发条件，就能够最大限度地避免这种故障的发生，提高系统的可用性。
那么，在数据库Hang住的情况下，如何去分析并发现导致问题的根源?一方面，由于系统Hang住会导致业务系统不可用，为了能够尽快地恢复业务，须快速地判断问题所在，然后Kill掉引起故障的会话和进程，或者数据库实例不得不重启以迅速恢复业务;但另一方面，如果只是重启数据库或Kill会话和进程来解决问题，在很多情况下是治标不治本的办法，在以后故障随时可能会出现。如何在二者之间进行抉择呢?对于数据库Hang故障的处理，首先是尽可能地收集到系统Hang住时的状态数据，然后尽快地恢复业务，恢复业务后分析收集到的数据，找到数据库系统Hang住的真正原因，然后再进行相应的处理。下一节将详细描述数据库系统Hang住后的处理流程。
无响应故障处理流程
对于Oracle无响应故障的处理，我们可以按下图所示的流程进行。
值得注意的是，上图并不是一个完整的Oracle数据库故障处理流程图，只是处理Oralce数据库无响应这一类特定的故障的流程，只列出了针对这一特定类型故障处理时的关键处理点。不过既然是故障，所以这类故障的处理流程与其他故障的处理流程，有着非常相似的地方。
下面是整个流程的详细说明：
1. 在出现数据库无响应故障后，首先确认系统的影响范围，如上节所描述的'，是部分业务系统或模块还是所有的业务系统都受影响，是不是整个实例或多个实例都无响应。同时应询问系统维护和开发人员，受影响的系统在出现故障前是否有过变动，包括主机硬件、操作系统、网络、数据库以及应用等。有时一个细小的变动就可能导致出现数据库Hang住这样严重的故障。曾经遇到一个库，应用只是修改了一个SELECT语句就导致了数据库Hang住。
2. 为了避免由于网络、数据库监听或客户端因素影响分析，建议都登录到主机上进行操作。
3. 如果主机不能登录(为了避免干扰流程主线，这里不讨论如网络问题这样也会导致不能连接的故障)，尝试关闭出现问题的业务系统，甚至是所有的业务系统。如果关闭了所有的业务系统之后，仍然不能连接，则只有考虑重新启动数据库主机。在数据库主机重新启动后，使用操作系统工具或OSW等长期监控操作系统的资源使用，同时监控Oracle数据库的性能和等待等。
4. 登录上主机后，先用top、topas等命令简单观察一下系统。看看系统的CPU使用、物理内存和虚拟内存的使用、IO使用等情况。
5. 使用SQLPLUS连接数据库，如果不能连接，则只能从操作系统上观察系统中是否有异常的现象，比如占用CPU过高的进程。使用gdb、dbx等debugger工具对数据库进行system state dump;使用strace、truss等工具检查异常进程的系统调用;使用pstack、procstack等工具察看异常进程的call stack等。
6. 使用SQLPLUS连接上数据库后，进行hanganalyze、system state dump等操作;或检查等待事件、异常会话等正在执行的SQL等待。
7. 找到故障产生的原因，如果暂时找不到原因，尽量收集数据。
8.确良如果应用急须恢复，可通过Kill会话、重启数据库实例等方式，先恢复应用。
9. 根据最终诊断结果，对数据库升级打补丁，或者修改应用等方式从根本上解决问题。
怎样避免数据库出现无响应故障
作为Oracle数据库DBA，除了处理故障之外，更重要的是如何预防故障的发生。根据前面对数据库无响应故障的成因分析，在日常的维护工作中，须做到以下几点：
1. 进行正确的维护操作
很多的数据库无响应故障都是由于不正确的维护操作引起的。应避免在业务高峰期做大的维护操作，比如像move、加主外键约束等会长时间锁表的操作。如果的确需要，尽量使用正确的操作方法。比如用ONLINE方式重建索引;建主键、唯一键约束时先建索引，然后在建约束时指定新建的索引，等等。也就是保证系统的并发性、可伸缩性，避免系统串行操作的出现。
2. 优化应用设计，优化数据库性能
为避免性能问题导致在业务高峰期数据库不能及时有效处理来自业务的请求，甚至于完全Hang住。对于数据库中存在串行访问的部分进行优化，比如latch、enqueue，还包括不合理的sequence设计等。特别是在RAC数据库中，严重串行访问等待往往更容易引起严重的性能问题。优化应用设计，使数据库具有更好的可伸缩性和并行处理能力，能够有效地避免性能问题引起的数据库Hang住。
3. 利用监控系统随时监控系统负载
遇到系统负载过高，内存不足，OS中虚拟内存换页很频繁等情况时，及时采取措施;监控Oracle数据库的核心进程，如pmon、smon等，看是否有异常，如过高的CPU消耗。出现异常应立即处理;监控归档空间和日志切换;监控数据库中的等待事件，比如是否有大量的enqueue、log file switch (archiving needed)、resmgr:become active等待事件等。
4. 为数据库打上补丁
很多的无响应故障是由于Oracle的Bug引起的，数据库DBA应关注当前版本中有哪些Bug会导致数据库Hang住，尽量为数据库打上解决这些Bug的补丁。
;
数据库Oracle工作原理数据库
数据库工作原理，包括数据库系统的处理过程和体系结构两个部分。
数据库系统的处理过程
要使用数据库，必须连接到数据库。当用户运行一个程序（如SQL*Plus）时，实际上是在客户机自动启动一个用户，并将连接请求通过网络发送到服务器。服务器上的数据库会为该用户进程派生一个对应的服务器进程，其数据库系统处理过程如下图：
1.处理过程可以简单地描述为：
2.用户在其计算机上运行基于Oracle的应用程序，即启动用户进程。
3.在客户机，服务器之间建立连接(CONNECT)。
4.在建立连接的基础上，为用户建立会话（SESSION），并为该会话创建一个PGA区（Program
Global
Area,程序全局区）以存储与该会话相关的信息。在同一个连接中，不同的用户有不同的会话。
5.启动服务器，由该服务器进程负责执行该会话的各项任务。
6.用户进程发送语句。
7.服务器进程解析，编译，执行语句，然后将结果写入数据库并返回给用户进程。
8.用户进程接收返回的SQL执行结果。
9.在应用程序中显示SQL执行结果。
总体结构
从作用和工作原理上看，可以将总体结构分成三部分，如下图：其中：
◆
内存结构：包括SGA和PGA。使用内存最多的是SGA，同时也是数据库性能的最大参数。
◆
进程结构：包括前台进程，后台进程。前台进程是指服务进程和用户进程。前台进程是根据实际需要而运行的，并在需要结束后立刻结束。后台进程是指在Oracle数据库启动后，自动启动的几个进程。
先创建一个函数
create
or
replace
function
my_concat(mid
in
integer)
return
varchar2
--记住：参数和返回值里的数据类型都不用定义长度
is
result
varchar2(4000);
--定义变量，记住Oracle中定义变量不需要
begin
for
temp_cursor
in
(select
b1
from
a
where
a1=mid)
loop
--此处在游标FOR循环中使用查询
result
:=result
||
temp_cursor.b1
||
',';
--Oracle中字符连接使用||，而sql
server中用+
end
loop;
result
:=
rtrim(result,',');
--去掉最后一个空格,还有Oracle中的赋值前面没有set
return
result;
end;
然后调用就行了select
a1,my_concat(a1)
from
a
group
by
a1
在平时的开发中，我们经常遇到数据表中出现重复的数据，那么该如何解决呢？这里介绍两种情况下的数据去重方法，一、完全重复数据去重；二、部分字段数据重复去重。
一、完全重复数据去重方法
对于表中完全重复数据去重，可以采用以下SQL语句。
Code
CREATETABLE"#temp"AS (SELECTDISTINCT * FROM 表名);--创建临时表，并把DISTINCT 去重后的数据插入到临时表中
truncateTABLE 表名;--清空原表数据
INSERTINTO 表名(SELECT * FROM"#temp");--将临时表数据插入到原表中
DROPTABLE"#temp";--删除临时表
具体思路是，首先创建一个临时表，然后将DISTINCT之后的表数据插入到这个临时表中；然后清空原表数据；再讲临时表中的数据插入到原表中；最后删除临时表。
二、部分数据去重方法
首先查找重复数据
select 字段1,字段2,count(*) from 表名 groupby 字段1,字段2 havingcount(*) 1
将上面的号改为=号就可以查询出没有重复的数据了。
想要删除这些重复的数据，可以使用下面语句进行删除：
deletefrom 表名 a where 字段1,字段2 in
(select 字段1,字段2,count(*) from 表名 groupby 字段1,字段2 havingcount(*) 1)
上面的语句非常简单，就是将查询到的数据删除掉。不过这种删除执行的效率非常低，对于大数据量来说，可能会将数据库卡死。
基于上述情况，可以先将查询到的重复的数据插入到一个临时表中，然后对进行删除，这样，执行删除的时候就不用再进行一次查询了。如下：
CREATETABLE 临时表 AS
(select 字段1,字段2,count(*) from 表名 groupby 字段1,字段2 havingcount(*) 1)
下面就可以进行这样的删除操作了：
deletefrom 表名 a where 字段1,字段2 in (select 字段1，字段2 from 临时表);
先建临时表再进行删除的操作要比直接用一条语句进行删除要高效得多。
上面的语句会把所有重复的全都删除，在oracle中，有个隐藏了自动rowid，里面给每条记录一个唯一的rowid，我们如果想保留最新的一条记录，我们就可以利用这个字段，保留重复数据中rowid最大的一条记录就可以了。
下面是查询重复数据的一个例子：
select a.rowid,a.* from 表名 a
where a.rowid !=
(
selectmax(b.rowid) from 表名 b
where a.字段1 = b.字段1 and
a.字段2 = b.字段2
)
上面括号中的语句是查询出重复数据中rowid最大的一条记录。而外面就是查询出除了rowid最大之外的其他重复的数据了。
由此，我们要删除重复数据，只保留最新的一条数据，就可以这样写了：
deletefrom 表名 a
where a.rowid !=
(
selectmax(b.rowid) from 表名 b
where a.字段1 = b.字段1 and
a.字段2 = b.字段2
)
同理，上述代码的执行效率毕竟低，所以我们可以考虑建立临时表，将需要判断重复的字段、rowid插入临时表中，然后删除的时候在进行比较。
createtable 临时表 as
select a.字段1,a.字段2,MAX(a.ROWID) dataid from 正式表 a GROUPBY a.字段1,a.字段2;
deletefrom 表名 a
where a.rowid !=
(
select b.dataid from 临时表 b
where a.字段1 = b.字段1 and
a.字段2 = b.字段2
);
commit;
一、 提高DML操作的办法：
简单说来：
1、暂停索引，更新后恢复.避免在更新的过程中涉及到索引的重建.
2、批量更新，每更新一些记录后及时进行提交动作.避免大量占用回滚段和或临时表空间.
3、创建一临时的大的表空间用来应对这些更新动作．
4、批量更新，每更新一些记录后及时进行提交动作.避免大量占用回滚段和或临时表空间.
5、创建一临时的大的表空间用来应对这些更新动作．
6、加大排序缓冲区
alter session set sort_area_size=100000000;
insert into tableb select * from tablea;
commit;
如果UPDATE的是索引字段，就会涉及到索引的重建，暂停索引不会提高多少的速度，反而有可能降低UPDATE速度，
因为在更新是索引可以提高数据的查询速度，重建索引引起的速度降低影响不大。
ORACLE优化修改参数最多也只能把性能提高15%，大部分都是SQL语句的优化！
update总体来说比insert要慢 ：
几点建议：
1、如果更新的数据量接近整个表，就不应该使用index而应该采用全表扫描
2、减少不必要的index，因为update表通常需要update index
3、如果你的服务器有多个cpu，采用parellel hint，可以大幅度的提高效率
另外，建表的参数非常重要，对于更新非常频繁的表，建议加大PCTFREE的值，以保证数据块中有足够的空间用于UPDATE， 从而降低CHAINED_ROWS。
二、 各种批量DML操作：
（1）、oracle批量拷贝：
set arraysize 20
set copycommit 5000
copy from username/password@oraclename append table_name1
using select * from table_name2;
（2）、常规插入方式：
insert into t1 select * from t;
为了提高速度可以使用下面方法，来减少插入过程中产生的日志:
alter table t1 nologging;
insert into t1 select * from t;
commit;
(3)、CTAS方式：
create table t1
as
select * from t;
为了提高速度可以使用下面方法，来减少插入过程中产生的日志，并且可以制定并行度:
create table t1 nologging parallel(degree 2) as select * from t;
（4）、Direct-Path插入：
insert /*+append*/ into t1 select * from t;
commit;
为了提高速度可以使用下面方法，来减少插入过程中产生的日志：
alter table t1 nologging;
insert /*+append*/ into t1 select * from t;
Direct-Path插入特点：
1、 append只在insert … select …中起作用，像insert /*+ append */ into t values(…)这类的语句是不起作用的。在update、delete操作中，append也不起作用。
2、 Direct-Path会使数据库不记录直接路径导入的数据的重做日志，会对恢复带来麻烦。
3、 Direct-Path直接在表段的高水位线以上的空白数据块中写数据，不会重用高水位线以下的空间，会对空间的使用造成一定的浪费，对查询的性能也会造成一定的影响。而常规插入会优先考虑使用高水位线之下有空闲空间存在的数据块。因此理论上Direct-Path插入会比常规插入速度更快，因为Direct-Path直接使用新数据块，而常规插入要遍历freelist获取可用空闲数据块，如果同 nologging 配合，这种速度优势会更加明显。
4、 以append方式插入记录后，要执行commit，才能对表进行查询。否则会出现错误：ORA-12838: 无法在并行模式下修改之后读/修改对象。
5、 用append导入数据后，如果没有提交或者回滚，在其他会话中任何对该表的DML都会被阻塞（不会报错），但对该表的查询可以正常执行。
6、 在归档模式下，要把表设置为nologging，然后以append方式批量添加记录，才会显著减少redo数量。在非归档模式下，不必设置表的 nologging属性，即可减少redo数量。如果表上有索引，则append方式批量添加记录，不会减少索引上产生的redo数量，索引上的redo 数量可能比表的redo数量还要大。
7、 数据直接插入数据文件，绕过buffer cache并且忽略了引用完整性约束。
8、 不管表是否在nologging 下，只要是 direct insert，就不会对数据内容生成undo。
9、 Oracle在Direct-Path INSERT 操作末尾，对具有索引的表执行索引维护，这样就避免了在drop掉索引后，再rebuild。
10、 Direct-Path INSERT比常规的插入需要更多的空间。因为它将数据插入在高水位之上。并行插入非分区表需要更多的空间，因为它需要为每一个并行线程创建临时段。
11、 在插入期间，数据库在表上获得排他锁，用户不能在表上执行并行插入、更新或者删除操作，并行的索引创建和build也不被允许。但却可以并行查询，但查询返回的是插入之前的结果集。
（5）、并行DML：
如果你的服务器有多个cpu，采用parellel hint，可以大幅度的提高效率
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(tableA, 2) */INTO tableA
SELECT * FROM tableB;
为了提高速度可以使用下面方法，来减少插入过程中产生的日志：
INSERT /*+ PARALLEL(tableA, 2) */INTO tableA NOLOGGING
SELECT * FROM tableB;
oracle默认并不会打开PDML，对DML语句必须手工启用。即需要执行
alter table enable parallel dml命令。
并行DML特点：
1、在并行DML模式中，默认的就是DIRECT-PATH插入，为了运行并行DML模式，必须满足以下条件：
a、必须是Oracle企业版；
b、必须在session中使并行DML生效，执行以下sql语句：
ALTER SESSION { ENABLE | FORCE } PARALLEL DML;
c、必须指定table的并行属性，在创建的时候或者其他时候，或者在insert操作时使用“PARALLEL”提示。
d、为了使Direct-Path Insert模式失效，在INSERT语句中指定“NOAPPEND”提示，覆盖并行DML模式。
2、并行Direct-Path INSERT到分区表：
类似于serial Direct-Path INSERT，每个并行操作分配给一个或者多个分区，每个并行操作插入数据到各自的分区段的高水位标志之上，commit之后，用户就能看到更新的数据。
3、并行Direct-Path INSERT到非分区表：
每个并行执行分配一个新的临时段，并插入数据到临时段。当commit运行后，并行执行协调者合并新的临时段到主表段，用户就能看到更新的数据。
4、Direct-Path INSERT可以使用Log或者不使用Log。
5、另外不得不说的是，并行不是一个可扩展的特性，只有在数据仓库或作为DBA等少数人的工具在批量数据操作时利于充分利用资源，而在OLTP环境下使用并行需要非常谨慎。事实上PDML还是有比较多的限制的，例如不支持触发器，引用约束，高级复制和分布式事务等特性，同时也会带来额外的空间占用，PDDL同 样是如此。
