如何查看oracle数据库中哪些session异常阻塞了系统

Oracle数据库运维过程中有时会遇到一种异常情况，由于错误的操作或代码BUG造成session异常地持有锁不释放，并大量阻塞系统对话。这时候需要找出造成异常阻塞的session并清除。 oracle session通常具有三个特征： （1）一个session可能阻塞多个session； （2）一个session最多被一个session阻塞； （3）session阻塞关系不会形成环路。（环路即死锁，oracle能自动解除） 因此session的阻塞关系为一棵树，进而DB系统所有session的BLOCK阻塞关系是一个由若干session阻塞关系树构成的森林，而异常session一定会在故障爆发时成为根（root）。因此，找寻异常锁表session的过程就是找出异常的root。 一般认为异常root有两个特征：（1）block树的规模过大，阻塞树规模即被root层层阻塞的session总数；（2）阻塞的平均等待时间过长。 查找异常session的方法一： OEM— performance— Blocking Sessions 查找异常session的方法二： select r.root_sid, s.serial#, r.blocked_num, r.avg_wait_seconds, s.username,s.status,s.event,s.MACHINE, s.PROGRAM,s.sql_id,s.prev_sql_id from (select root_sid, avg(seconds_in_wait) as avg_wait_seconds, count(*) - 1 as blocked_num from (select CONNECT_BY_ROOT sid as root_sid, seconds_in_wait from v$session start with blocking_session is null connect by prior sid = blocking_session) group by root_sid having count(*) 1) r, v$session s where r.root_sid = s.sid order by r.blocked_num desc, r.avg_wait_seconds desc; 该SQL语句即是根据v$session的字段blocking_session统计阻塞树根阻塞session的计数以及平均阻塞时间、并进行排序，排名最前的往往是异常session。 另外需要注意的是，持有锁时间最长、或等待时间最长的session都不一定是造成阻塞的根源session！

成都网站建设公司更懂你！成都创新互联只做搜索引擎喜欢的网站！成都网站制作前台采用搜索引擎认可的DIV+CSS架构,全站HTML静态,H5高端网站建设+CSS3网站,提供：网站建设，微信开发，成都微信小程序，商城网站建设，成都app开发，主机域名，服务器租售，网站代托管运营，微信公众号代托管运营。

Oracle常见错误代码的分析与解决一

在使用ORACLE的过程过 我们会经常遇到一些ORACLE产生的错误 对于初学者而言 这些错误可能有点模糊 而且可能一时不知怎么去处理产生的这些错误 本人就使用中出现比较频繁的错误代码一一做出分析 希望能够帮助你找到一个合理解决这些错误的方法 同时也希望你能够提出你的不同看法 毕竟作为一种交流的手段 个人意见难免过于偏颇 而且也必定存在着不足 出错之处在所难免 写这篇文章的目的就是想通过相互之间的交流共同促进 共同进步 ORA :unable to extend rollback segment NAME by NUM intablespace NAME 产生原因 上述ORACLE错误为回滚段表空间不足引起的 这也是ORACLE数据管理员最常见的ORACLE错误信息 当用户在做一个非常庞大的数据操作导致现有回滚段的不足 使可分配用的回滚段表空间已满 无法再进行分配 就会出现上述的错误 解决方式 使用 ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size_of_file 命令向指定的数据增加表空间 根据具体的情况可以增加一个或多个表空间 当然这与还与你主机上的裸盘设备有关 如果你主机的裸盘设备已经没有多余的使用空间 建议你不要轻意的增加回滚段表空间的大小 可使用下列的语句先查询一下剩余的tablespace空间有多少 Select user_name sql_text from V$open_cursor where user_name= ; 如果多余的空间比较多 就可以适当追加一个大的回滚段给表空间使用 从而避免上述的错误 你也可以用以下语句来检测一下rollback segment的竞争状况 Select class count from V$waitstat where calss in( system undo header system undo block undo header undo block );和 Select sum(value) from V$sysstat where name in ( db_block_gets consistents gets );　如果任何一个class in count/sum(value)大于 % 就应该考虑增加rollback segment 相应的英文如下 Cause:Failed to allocate extent from the rollback segment in tablespace Action:Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the specified tablespace ORA :unable to extend temp segment by num in tablespace name　产生原因 ORACLE临时段表空间不足 因为ORACLE总是尽量分配连续空间 一但没有足够的可分配空间或者分配不连续就会出现上述的现象 解决方法 我们知道由于ORACLE将表空间作为逻辑结构 单元 而表空间的物理结构是数据文件 数据文件在磁盘上物理地创建 表空间的所有对象也存在于磁盘上 为了给表空间增加空间 就必须增加数据文件 先查看一下指定表空间的可用空间 使用视图SYS DBA_FREE_SPACE 视图中每条记录代表可用空间的碎片大小 SQLSelect file_id block_id blocks bytes from sys dba_free_space where tablespace_name= ; 返回的信息可初步确定可用空间的最大块 看一下它是否小于错误信息中提到的尺寸 再查看一下缺省的表空间参数 SQLSELECT INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS PCT_INCREASE FROM SYS DBA_TABLESPACES WHERE TABLESPACE_NAME=name; 通过下面的SQL命令修改临时段表空间的缺省存储值 SQLALTER TABLESPACE name DEFAULT STORAGE (INITIAL XXX NEXT YYY); 适当增大缺省值的大小有可能解决出现的错误问题 也可以通过修改用户的临时表空间大小来解决这个问题 SQLALTER USER username TEMPORARY TABLESPACE new_tablespace_name; 使用ALTER TABLESPACE命令 一但完成 所增加的空间就可使用 无需退出数据库或使表空间脱机 但要注意 一旦添加了数据文件 就不能再删除它 若要删除 就要删除表空间 一个报错例子如下 ORA :unable to extend temp segment by in tablespace TEMPSPACE 相应的英文如下 Cause: Failed to allocate extent for temp segment in tablespace Action:Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the specified tablespace or create the object in another tablespace ORA :Oracle data block corrupted(file # num block # num)　产生原因 当ORACLE访问一个数据块时 由于 硬件的I/O错误 操作系统的I/O错误或缓冲问题 内存或paging问题 ORACLE试图访问一个未被格式化的系统块失败 数据文件部分溢出等上述几种情况的一种引起了逻辑坏块或者物理坏块 这时就会报ORA 的错误 解决方式 由于ORACLE只有在访问到有问题的数据文件时才会报错 所以报错的时间有可能会比实际出错的时间要晚 如果ORA 出错信息提示数据坏块指向的是用户自己的数据文件 则用以下方法来解决 如果通过下面的SQL语句查出的坏块出现有索引上 则只需重建索引即可 SQLSelect owner segment_name segment_type from dba_extents where file_id= and beeen block_id and block_id+blocks ; (和分别是ORA 报出的坏块出现的文件号和块号) 如果坏块出现在表上 先用以下语句分析是否为永久性坏块（建议多执行一两次 有助于鉴别数据坏块是永久性的（硬盘上的物理坏块）还是随机性的（内存或硬件错误引起）） SQLAnalyze table validate structure cascade; 执行该命令后 可能会出现以下的结果 ORA 与原先错误信息有相同的参数 为永久性的物理或逻辑坏块 与原先错误信息有不同的参数 可能与内存 page space和I/O设备有关 如果用户有此表的最新备份 那么最好是用此备份来恢复此表 或者使用event 来取出坏块以外的数据 先关闭数据库 编辑init ora文件 加入 event= trace name context forever level startup restrict 创建一个临时表 SQLcreate table errortemp as select * from error;(error是坏表的表名) 把event从init ora文件中删掉并重起数据库 rename坏表 把临时表rename成坏表的表名 创建表上的INDEX等 如果ORA 出错信息提示数据坏块指向的是数据字典或者是回滚段的话 你应该立即与ORACLE公司联系 共同商量一个好的解决办法 这里所讲的解决方法只是比较常见的一种 一些更为具体的解决办法可以查看一下ORACLE的故障解决手册 那里面有浞及使用ROWID方法来取出坏块以外的数据的方法 这里就不介绍了 相应的英文如下 Cause:The given data block was corrupted probably due to program errors Action:Try to restore the segment containing the given data block This may involve dropping the segment and recreating it If there is a trace file report the messages recorded in it to customer support ORA :max # of extents num reached for rollback segment num　产生原因 这种错误通常为一个回滚段和一个表空间已经达到MAXEXTENTS参数设置的极限 要注意的是这个MAXEXTENTS不是该回滚段或表空间的硬件极限 硬件极限取决于数据库创建时在init ora文件中指定的DB_BLOCK_SIZE参数的值 解决方法 使用SQL命令ALTER TABLESPACE…STORAGE（MAXEXTENTS XXXX）来增加 MAXEXTENTS 其中 XXXX 值必须大于错误信息中所指的数值 但不能大于LARGEST MAXEXTENT的值 如果已经达到了LARGEST MAXEXTENT VALUE 解决的办法就是重新创建较大的范围尺寸 使用带有选项PRESS=Y的Export工具导出表 如果表空间有可用空间 先给表做一个备份 用alter tablespace tablespace_name更改其名字 然后再装载表回数据库 查看其错误出现的地方 如果出现在回滚段或索引上 那么必须将其删除并重建 如果出现在临时表空间 修改临时表空间的存储字段 便可解决这个问题 一个报错例子如下 ORA :max # extents reached for rollback segment RBS_ 相应的英文如下 Cause: An attempt was made to extend a rollback segment that already has reached its maximum size or space could not be allocated in the data dictionary to contain the definition of the object Action:If possible increase the value of either the MAXEXTENTS or PCTINCREASE initialization parameters or find the data dictionary table lacking space and alter the storage parameters as described in the Oracle Server Administrator s Guide ORA :internal error code arguments:[num] [?] [?] [?] [?]　产生 lishixinzhi/Article/program/Oracle/201311/18838

Oracle重做日志丢失的故障处理

Oracle重做日志

Oracle的重做日志文件(Online redo logfile)循环记录了数据库所有的事务 它的大小 个数和存储位置对数据库性能和恢复有重要影响 它一般由大小相同的几组文件构成 我们可以查看数据库视图v$logfile知道redo logfile的个数和存储位置 对每一个Oracle数据库都要求至少具有两个联机重做日志

每一次新的事务提交时 Oracle将该事务写入日志文件 但并非此时也将修改的数据块写回原数据文件 由于内存读写和磁盘I/O存在几个数量级的效率差别 Oracle通过减少数据文件的物理I/O读写来大大提高数据库的性能 同时 又通过优先写日志文件来保证数据的正确性和一致性 基于这种机制 重做日志文件在数据库的实例恢复和介质恢复时至关重要 是oracle数据库最重要的物理文件之一

如果数据库在启动时检测到重做日志丢失 数据库将无法启动 如果数据库在运行时切换日志文件组 检测到下一组或者全部的重做日志丢失 数据库将会崩溃 由于磁盘介质损坏或者人为的误删除文件 造成严重后果的事件近期时有发生 本文列举了重做日志丢失的数据库恢复 但如果按照冗余原则合理分布日志文件组的成员 如果工程师了解日志文件的基本原理和使用原则 就完全可以避免出现下列问题

恢复方法

故障现象

SQL startup mount

Oracle Instance Started

Database mounted

ORA : open failed for members of log group of thread

ORA : online log thread : /ORACLE/ORADATA/H /REDO LOG

ORA : unable to open file

OSD : unable to open file

O/S Error: (OS ) The system cannot find the file specified

恢复注意事项

以下所列举的恢复方法 都属于不完全恢复或者强制恢复 会丢失当前重做日志中的事务数据 一旦操作不当 将带来数据丢失等严重后果 请遵循以下几个恢复原则

请勿在生产系统上试用

如果生产系统出现重做日志文件丢失的故障 请勿自行操作破坏现场 应该立刻联系Oracle工程师

恢复成功之后 需要马上做一次数据库的全备份

建议重做日志文件一定要实现镜象在不同的磁盘上 避免这种情况的发生

恢复方法

首先检查重做日志文件状态 看看报错的日志文件的状态是否为Current

SQL select * from v$log;

SQL select * from v$logfile;

如果重做日志文件状态为Inactive 我们可以直接清除该日志文件的内容

SQL alter database clear logfile /ORACLE/ORADATA/H /REDO LOG ;

如果重做日志文件状态为Current 恢复工作较为复杂 有以下四种情况

）通过下面步骤 数据库顺利打开

SQL recover database until cancel;

Type Cancel when prompted

SQLalter database open resetlogs;

）第一种情况的 recover database until cancel 操作遇到ORA ORA ORA 错误 需要整个数据库的物理备份 并根据归档日志恢复到错误时间点 前提是数据库是归档模式

restore old backup

SQL startup mount

SQL recover database until cancel using backup controlfile;

SQL alter database open resetlogs;

）如果数据库是非归档模式 只能恢复整个物理备份 然后直接打开数据库 这种情况将丢失物理备份至故障发生前的全部数据

）如果数据库是非归档模式 且没有物理备份 只能通过特殊的隐含参数 允许数据库不一致的状况下打开数据库 这种恢复方法是没有办法之后的恢复方法 将导致数据库不一致 一般情况下不要采用 如确有需要 请在Oracle的技术人员指导下使用该方法

?        关闭数据库

SQLshutdown immediate

?        在initsid ora中加入如下参数

_allow_resetlogs_corruption=TRUE

?        重新启动数据库 利用until cancel恢复

SQLrecover database until cancel;

Cancel

?        打开数据库

SQLalter database open resetlogs;

?        数据库被打开后 马上执行一个全库导出

关闭数据库 在initsid ora中去掉_all_resetlogs_corrupt参数

lishixinzhi/Article/program/Oracle/201311/16743

新闻名称：如何查看oracle故障 检查oracle数据库是否正常
本文网址：http://myzitong.com/article/hgcoeg.html