其他4

原创 yczloveyy 2023-08-29

125

DATAGUARD 主从同步GAP超过xxx小时

一、数据库状态检查的步骤/命令(通过本步骤可以确定数据库当前的状态是否OK)

1.检查备库数据库的状态

SELECT open_mode FROM V$DATABASE;

OPEN_MODE
--------------------
READ ONLY WITH APPLY
复制

2.检查备库MRP进程是否正常：

select process,CLIENT_PROCESS,status,thread#,sequence# from v$managed_standby;

查看mrp进程是否处于APPLYING_LOG状态
复制

二、请描述告警出现的可能原因

1.可能是因为主库产生大量的归档日志，导致同步应用延迟

2.可能是日志同步慢造成，此时可能是因为网络问题，或者日志量大，获取其他原因

3.可能是因为备库日志应用慢造成，此时备库在应用日志时因为多余的等待而变慢

4.可能是因为部分还未来得及应用便被删除，造成了GAP。

三、预计多久不处理就可以出现UIOC(P1)

1.主要视备库是否有实时性要求比较高的查询业务，根据不同业务需求紧急度不同

四、告警检查的步骤(通过本步骤确定告警是否是误告)

1.检查当前延迟情况（是同步延迟还是应用延迟?）

（1）查询lag情况：
select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIME from V$DATAGUARD_STATS   WHERE NAME LIKE '%lag';
（2）排查主备库告警日志情况，是否明显的gap
复制

（3）判断日志应用的情况，看APPLIED
set line 180 pages 9999
col NAME for a65
select * from (SELECT SEQUENCE#, APPLIED, CREATOR, NAME
  FROM GV$ARCHIVED_LOG  
order by sequence# desc) where rownum<11;
复制

五、需要保存现场的步骤/命令

六、需要反馈上级以及运营的信息

七、建议处理的流程,步骤和对应命令

1.针对由于主库产生大量归档日志导致同步积压问题

（1）查询主库的归档产生情况：

最近10天中每天日志切换的量(即可分析10天的波度，又可分析24小时内，可很容易看出异常情况)
SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20, 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 , 
       SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23, 
       COUNT(*) TOTAL 
FROM v$log_history  a  
   where first_time>=to_char(sysdate-11)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) 
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;
复制

（2）排查告警日志,以上检查步骤等，确定当前日志同步及应用是否正常进行，如果是正常，则只能是尽量等待慢慢追赶。如果同步应用速度太慢，明显无法赶上日常新赶上情况，则需进一步排查。

（3）如果出现日志传输到备库且尚未被应用，但已经被删除掉。

1）检查主库是否存在缺失的归档日志，如果存在，则直接取归档过来备库进行应用。

2）如果主库已经不存在，可以让备份组帮忙恢复指定的归档到备库进行应用。

3）如果缺失的归档太多，且恢复比较困难，则可以考虑进行增备恢复。

增备方案示例：
复制

1.----备库取消归档应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2.----备库确定lowest scn
col current_scn for 99999999999999
SELECT CURRENT_SCN FROM V$DATABASE;
select min(fhscn) from x$kcvfh;
select min(f.fhscn) from x$kcvfh f, v$datafile d
where f.hxfil =d.file#
and d.enabled != 'READ ONLY' ;
取上述查询中的最小值

3.-----on primary db 端根据第2步中获取的最小scn 来进行增量备份
BACKUP INCREMENTAL FROM SCN 1676552 DATABASE FORMAT '/data/ora/ForStandby_%U' tag 'FORSTANDBY';
Eg:
RMAN> run {
allocate channel t1 device type disk;
allocate channel t2 device type disk;
allocate channel t3 device type disk;
allocate channel t4 device type disk;
BACKUP as compressed backupset INCREMENTAL FROM SCN 5992377
DATABASE FORMAT '/opt/app/oracle/fast_recovery_area/ORCL/backupset/2021_09_24/orc_%U.bak';
}
4.-----拷贝备份到 备库
$scp ForStandby_07pqprm4_1_1 192.168.0.10:/tmp
5.----on standby db 在备库端注册备份片，注意用户属主，权限
RMAN> CATALOG START WITH '/lixora/ForStandby';
6.----执行恢复
RMAN> RECOVER DATABASE NOREDO;
7.-----on primary db 生成新的standby 控制文件
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
8.----把主库端生成的standby 控制文件拷贝到备库，注意用户属主，权限
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/lixora/ForStandbyCTRL.bck';
scp /lixora/ForStandbyCTRL.bck 192.168.0.10:/tmp
9.------备份备库数据文件信息，用于在恢复新的standby控制文件后比对
spool datafile_names_step8.txt
set lines 200
col name format a60
select file#, name from v$datafile order by file# ;
spool off
10.------on standby Db 恢复新的standby 控制文件
RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
11.-----更新控制文件中的数据文件信息
使新的standby 控制文件生效
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
CATALOG START WITH '+DATA/zhglptdg/datafile/';
12.------on primary db。确保在备库发生日志gap 后，主库没有添加过新的数据文件。
SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 12611050666604；
如果有记录，则不能进行switch 操作，可以参考一下文档来恢复：
rman 增量恢复 dg gap后 主库添加新数据文件
Note 1531031.1 Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary
13.-----重命名数据文件
RMAN> SWITCH DATABASE TO COPY;
14.-----再次确认在恢复增量备份片后主库和备库scn 差距没有太大
SQL> col current_scn for 99999999999999
SELECT CURRENT_SCN FROM V$DATABASE;
select min(fhscn) from x$kcvfh;
select min(f.fhscn) from x$kcvfh f, v$datafile d
where f.hxfil =d.file#
and d.enabled != 'READ ONLY' ;
复制

2.针对网络问题造成的同步慢的情况，通过手工从主库scp一个文件到备库观察传输速度，验证网络是否存在问题。

3.针对备库应用日志慢的情况

观察数据库mrp及prxx进程的等待事件情况，如果出现特殊等待事件，需要上报排查。

select event,p1,p2,p3 ，program from v$session;
复制

4.针对明显的日志GAP情况，请参考步骤1。

八、验证问题已经恢复的步骤和命令

1.检查当前延迟情况（是同步延迟还是应用延迟?）

（1）查询lag情况：
select to_char(SYSDATE,'yyyymmdd hh24:mi:ss') CTIME,NAME,VALUE,DATUM_TIME from V$DATAGUARD_STATS   WHERE NAME LIKE '%lag';
（2）排查主备库告警日志情况，是否明显的gap
（3）判断日志应用的情况，看APPLIED
set line 180 pages 9999
col NAME for a65
select * from (SELECT SEQUENCE#, APPLIED, CREATOR, NAME
  FROM GV$ARCHIVED_LOG  
order by sequence# desc) where rownum<11;
复制

长事务回滚影响性能

一、故障现象

对一张大表进行长事务DML操作时进程异常中断后需读取UNDO进行回滚，伴随有latch:undo 或db file sequential read相关等待系统资源消耗上升，该表相关业务无法正常执行。

二、可能故障原因

l 长事务操作写入UNDO,当需要回滚时数据量大且对操作资源消耗非常高。

三、分析思路

OLTP环境避免运行长事务，改为分批提交；

四、应急处理

查回滚事务：

select * from v$fast_start_transactions;

或者

select * from x$ktuxe where KTUXECFL='DEAD' AND KTUXESTA!='INACTIVE';

a) 如果应用许可，kill 会话后，建立同结构空表，然后换名后再回插数据;(如果访问频繁无法rename时停监听，kill 访问的会话再执行rename)

b) 如果不能使用空表，则需等待回滚完成，可设置参数开启并发回滚加快回滚速度;

i. 启用并行：

CPU资源较空闲时可

alter system set fast_start_parallel_rollback=high;

注：low是2倍cpu数，high是4倍CPU数，当并发子进程之间需要的资源冲突时(Wait for a undo 相关等待)，并发的回滚速度不如串行的回滚速度，可fast_start_parallel_rollback 改成 false

ii. 加快速度：

alter system set "_cleanup_rollback_entries"=400 scope=spfile;

需重启生效

iii. 暂时禁用smon的恢复:

oradebug setorapid 'SMON's Oracle PID';

oradebug event 10513 trace name context forever, level 2

事后恢复使用：

oradebug setorapid 'SMON's Oracle PID';

oradebug event 10513 trace name context off

ORA-04031_告警处理方案

ORACLE 告警日志或前台应用执行报出ORA-04031错误，04031 实际上是一个进程申请内存时，shared pool因为内存不足而找不到满足大小并且连续的内存空间而产生的错误信息，严重时可能导致整个实例hang。

Sun Jan 22 16:26:11 EAT 2019

Errors in file/oracle/app/oracle/admin/xxxxx2/bdump/xxxxx22_j006_8166.trc:

ORA-12012: 自动执行作业 2776 出错

ORA-04031: 无法分配 4120 字节的共享内存 ("shared pool","selecto.owner#,o.name,o.nam...","Typecheck","kgghteInit")

ORA-06512: 在 "XXXXXX.XX_TS_DIS_ID_EVENT", line 4

一、数据库状态检查的步骤/命令(通过本步骤可以确定数据库当前的状态是否OK)

1.检查告警日志ORA-04031是否持续在报错。

2.登录数据库手工执行任意测试SQL查看是否能够正常执行。

3.登录数据库检查等待事件情况。

3.检查主机状态：top、free -m等

二、请描述告警出现的可能原因

l 物理内存耗尽
l Shared pool大小不合理
l Shared pool子池个数不合理
l 内存碎片
l 过高的硬解析/没有绑定变量

三、预计多久不处理就可以出现UIOC(P1)

一般情况下出现了ORA-4031,说明数据库shared pool已经存在不足，如果从告警日志中看到数据库中频繁打印错误日志，数据库有异常等待之类，严重时可能导致整个实例hang。此时已经严重影响业务。

四、告警检查的步骤(通过本步骤确定告警是否是误告)

1.检查告警日志。

五、需要保存现场的步骤/命令

第1，2步涉及信息需在故障期间收集，其余三步可以后续做。

1.故障期间尽量备份以下试图

create table dbmgr.sga_20220000 as select tt.* from gv$sga tt;
create table dbmgr.sgastat_20220000 as select tt.* from gv$sgastat tt;
create table dbmgr.x_ksmss_20220000 as select tt.* from x$ksmss tt;
create table dbmgr.sga_dynamic_comp_20220000 as select tt.* from gv$sga_dynamic_components tt;
create table dbmgr.sga_re_ops_20220000 as select tt.* from gv$sga_resize_ops tt;
create table dbmgr.sqlarea_20220000 as select tt.* from gv$sqlarea tt;
create table dbmgr.sql_shared_cursor_20220000 as select tt.* from gv$sql_shared_cursor tt;
create table dbmgr.ash_20220000 as select tt.* from gv$active_session_history tt;
复制

2.建议收集heapdump和library cache dump：

收集heapdump和librarycache dump.（对诊断最直接，但会消耗一定的资源，且必须再问题发生期间收集）

*****************************************************************************

1) 收取Heapdump 的信息（必须在问题发生期间收集）:命令如下：

sqlplus /nolog

connect / as sysdba 

oradebug setmypid

oradebug unlimit

oradebug dump heapdump 536871426

oradebug tracefile_name

oradebug close_trace

复制

影响分析：从经验来看，一般Heapdump执行不会对系统造成太多的影响，影响基本可以忽略. 如果产生影响，也是系统产生4031后影响结果，非本身该操作影响.

2) 收取librarycache dump（必须在问题发生期间收集）. 命令如下：

ALTER SYSTEM SET EVENTS'IMMEDIATE TRACE NAME LIBRARY_CACHE LEVEL 16';
复制

影响分析：从经验来看，librarycache dump可能会对系统有些少许的影响(依据系统负载不同而影响不同)，如果故障期间在非业务高峰期，建议可以使用。

备注：执行Heapdump以及librarcy cache dump有必要可以联系 oracle 支持进行指导。

*****************************************************************************

3. 收集AWR，如果夯住则中断掉

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
复制

可以通过下面方法在另一个好的实例收集AWR

SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(     3616491197,  1, 61143, 61144) ) ;
复制

4.补充收集Alert log/trace file、AWR/ASH、OSW。进一步分析；必要时需要寻求oracle support的支持。

5.开SR并将以上收集信息上传SR,对于4031 的深入以及代码层面分析，Oracle 后台研发一般需要heapdump 或者 library cache dump 进行分析.

六、需要反馈上级以及运营的信息

七、建议处理的流程,步骤和对应命令

1.初步排查：

一般应急状况下，处理的第一时间不用去检查共享池碎片，意义不大。而且4031错误同样有可能导致整个数据库实例hang，所以应该去检查什么组件大小比较异常（有条件的话同类似的其他库环境去对比）

Select t.*
  From (Select Name,
               bytes / (1024 *1024) "MB",
               round(bytes /(Select Value
                               From v$parameter t
                              Where t.name = 'shared_pool_size') * 100,
                     2) || '%'"USED%"
          From v$sgastat
         Where pool = 'shared pool'
         Order By 2 Desc) t
 Where rownum < 20;
复制

根据当前组件大小状态，判断是哪部分出了问题，如果是可以回收空间的组件，条件允许的话通过flush shared_pool来处理。比如sql area、Ccursor等占据主要大小，可能与硬解析有关，可以通过下面的查询确认：

SET pages 10000
SET linesize 200
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
with c as
 (select force_matching_signature,count(*) cnt
    from v$sqlarea
   where force_matching_signature!= 0
   group by force_matching_signature
  having count(*) > 20),
sq as
 (select sql_text,
         force_matching_signature,
         row_number() over (partition by force_matching_signature order by sql_id desc) p
    from v$sqlarea s
   where force_matching_signature in
         (select force_matching_signature from c))
select sq.sql_text, sq.force_matching_signature, c.cnt "unsharedcount"
  from c, sq
 where sq.force_matching_signature= c.force_matching_signature
and sq.p = 1order by c.cnt desc;

复制

如果是无法回收的perm组件，则必须重启实例。临时的一个解决方法是，内存空间足够的情况下，内存中调大sharedpool，等到临时窗口后再重启实例恢复。

Select pool, Sum(bytes) / (1024 * 1024) "MB"
  From v$sgastat
 Where pool = 'shared pool'
 Group By pool;
复制

2. 以下应急方案在可行的情况下可以逐一尝试：

（1）调大问题池的 size，例如调大 shared pool 的大小，可暂时缓解ORA-04031，争取分析的时间，然后在空闲时段进行数据库实例重启。

alter system set shared_pool_size= xxG;
复制

需要判断是否可以调大shared_pool_size，一般可通过如下方式

select NAME,VALUE/1024/1024 from v$parameter where name in('shared_pool_size','db_cache_size','sga_target','sga_max_size')
union
select component,current_size/1024/1024 from v$sga_dynamic_components where component in ('shared pool','DEFAULT buffer cache','large pool');

NAME                           VALUE/1024/1024
------------------------------ ---------------
DEFAULT buffer cache                       400-->buffer cache当前实际分配内存大
db_cache_size                              304-->为SGA中占用内存db_cache_size参数设定值
large pool                                  16
sga_max_size                              3072
sga_target                                2048
shared pool                               1504-->shared pool 当前实际分配内存大小
shared_pool_size                          1008-->为SGA中占用内存shared_pool_size参数设定值。
7 rows selected.

复制

满足如下条件才可调整：

如果sga_max_size 实际比各个内存池大小加起来大很多，则可以调大shared_pool_size。

（2）不繁忙的系统，可以尝试flushshared pool （发生问题的是shared pool的前提下，但在很多情况下可能不会奏效，会很快又出现ORA-04031的问题）

  alter system flush shared_pool ;
复制

（3）重启故障数据库实例

在重启或者刷新shared_pool前，收集相关信息留做进一步的根因诊断。

八、验证问题已经恢复的步骤和命令

1.检查告警日志是否仍然报错。

Oracle HANG库应急预案

说明： 本文档不针对具体异常进行前置具体分析，目的是针对紧急故障进行快速应急恢复

数据库 HANG 的表现为：数据库出现大量等待(row cache object/library cache lock/library cache lock/library cache pin/library cache: mutex X 等)、前台无法登陆、登陆时 plsql dev 或页面出现卡顿、后台无法登陆，严重时出现 OS 命令卡顿等现象。
具体原因可能是：数据库出现大量等待事件、主机 CPU/内存使用率异常、连接数满等，这些往往已经影响到应用可用性，需要立刻处理。

1、收集 HANGANALYZE（5-10分钟）（可选）

说明： 该步骤目的为采集故障现场信息，结合紧急程度判断，非常紧急情况可跳过该步骤

sqlplus  / as sysdba (后台无法登陆时使用sqlplus -prelim / as sysdba)
SQL> oradebug setmypid  (sqlplus -prelim / as sysdba 时使用oradebug setospid < use an existing process id, for example PMON/SMON> )
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug hanganalyze 3  (RAC 环境使用oradebug -g all hanganalyze 3)
等待 1 分钟
SQL> oradebug hanganalyze 3  (RAC 环境使用oradebug -g all hanganalyze 3)
等待 1 分钟
SQL> oradebug dump systemstate 10 (RAC 环境使用oradebug -g all dump systemstate  10) 
等待 1 分钟
SQL> oradebug dump systemstate 10 (RAC 环境使用oradebug -g all dump systemstate  10) 
等待 1 分钟
SQL> oradebug tracefile_name --------保留 trace 文件
复制

2、全量KILL SESSION（4分钟）

在异常的数据库节点，进行KILL所有 SESSION操作，这里分两种情况：

2.1、情况一：如果能登录数据库（4分钟）

2.1.1、检查占用undo超过50MB的事务，目的在于避免大事务异常回滚需要较长时间，非常重要！！！（0.5分钟）

set linesize 180 pagesize 999
spool /tmp/undo_sess.txt
select inst_id,sid,serial#,xidusn,undo_size_m,sess_status,status,username,sql_id,prev_sql_id,machine,start_time,logon_time,related_rows,sql_text
  from (select t.xidusn,s.status sess_status,t.status,s.inst_id,s.sid,s.sql_id,s.prev_sql_id,s.serial#,s.username,s.machine,t.start_time,s.logon_time,t.used_urec related_rows, round(t.used_ublk * block_size / 1024 / 1024, 0) undo_size_m,q.sql_text
          from gv$session          s,
               gv$transaction      t,
               dba_tablespaces     dt,
               gv$system_parameter p,
               gv$sql q
         where q.sql_id(+) = s.sql_id and upper(p.name) = 'UNDO_TABLESPACE' and dt.tablespace_name = p.value and t.inst_id = s.inst_id and p.inst_id = s.inst_id and s.saddr = t.ses_addr
           and round(t.used_ublk * block_size / 1024 / 1024, 2) > 50
         order by t.used_ublk desc) t where rownum < 20;
spool off
复制

说明：

如有返回，undo_size_m为占用undo大小，需要记录对应的sid。然后转> 步骤2.1.2 KILL SESSION
如无返回，直接进入> 步骤2.1.2 KILL SESSION

2.1.2、记录当前所有用户SESSION对应的OS进程，生成OS级kill -9脚本（注意有占用undo的SESSION需要排除）（0.5分钟）

set head off
set feedback off
set pagesize 0
spool /tmp/kill_9_1.sh
select ' kill -9 ' || spid from (select spid from v$process where addr in(select paddr from v$session where username is not null
--and sid not in (<替换步骤2获取的所有sid>)
));
spool off
复制

2.1.3、生成数据库KILL SESSION脚本（0.5分钟）

set head off
set feedback off
set linesize 180 pagesize 0
spool /tmp/kill_sess_1.sql
select 'alter system kill session '''||sid||','||serial#||''' immediate;'  from v$session  where username is not null
--and sid not in (<替换步骤2获取的所有sid>)
;
spool off
复制

2.1.4、执行kill_sess_1.sql脚本（0.5分钟）

SQL>@/tmp/kill_sess_1.sql
复制

如果session kill之后不能2分钟内不能释放执行OS级kill -9脚本

sh /tmp/kill_9_1.sh
复制

说明：
如果上述kill session无法恢复应用，转步骤3。
数据库恢复正常，后续步骤不需要处理。

2.2、情况二：如果登不进数据库（2分钟）

如果sqlplus 登入hang住或者报ORA-00020连接数满，则在主机层面kill会话，为防止 SID 有相似的地方，这里使用精确匹配：（2分钟）

ps -ef|grep -v grep|grep LOCAL=NO|grep -w oracle$ORACLE_SID |awk '{print $2}'|xargs kill -9
复制

说明：
如果上述kill OS会话无法恢复应用，转步骤3。
如果数据库恢复正常，后续步骤不需要处理。

3、检查ORA-00600/ORA-07445报错（2 分钟）

执行下面命令查看后台alert日志，确认日志中是否有指定的ORA-00600/ORA-07445报错，无则转步骤4，有则转步骤5

tail -20000 alert_$ORACLE_SID.log |egrep -C5 'ORA-00600.*kghfrh:ds.*|ORA-00600.*kjbldynfr:!llink.*|ORA-00600.*kjcv_move_set_level:WRAP.*|ORA-00600.*kjxmgmb_nreq:!bat.*|ORA-00600.*kghfrmrg:nxt.*|ORA-00600.*ktugnb:clschk_kcbne.*|ORA-00600.*kkqcbydrv:saved value.*|ORA-00600.*kdBlkCheckError.*|ORA-00600.*KFFMLOCK.*|ORA-00600.*opiexe.*|ORA-00600.*kghssgfr.*|ORA-00600.*kglbrk-bad-lock.*|ORA-00600.*kgllkdl-bad-session.*|ORA-00600.*kglLockOwnersListDelete.*|ORA-00600.*kglpnal-bad-pinid.*|ORA-00600.*kghfrmrg:prv.*|ORA-00600.*17182.*|ORA-00600.*17147.*|ORA-00600.*17112.*|ORA-00600.*17163.*|ORA-00600.*17110.*|ORA-00600.*kss_get_type: bad control.*|ORA-07445.*kghfre.*|ORA-07445.*kcbzwb.*|ORA-07445.*kglsim_upd_newhp.*|ORA-07445.*kfupsiternext.*|ORA-07445.*kksParentHandleFreeCbk.*|ORA-07445.*kgldafr.*|ORA-07445.*kglntclr.*|ORA-07445.*kghalf.*'
复制

4、重启监听方案（4 分钟）

4.1、临时屏蔽 crontab

>cron_null
crontab cron_null
复制

4.2、停监听

需要先同运营确认是否可以停监听，注意$ORACLE_SID需要替换，而且有的库存在多个监听，都要停掉

GI 单实例：crsctl stop res $ORACLE_SID.lsnr -f
RAC 环境：srvctl stop listener -l $ORACLE_SID
VCS 单实例：lsnrctl stop $ORACLE_SID
复制

4.3、kill OS会话

为防止 SID 有相似的地方，这里使用精确匹配

ps -ef|grep -v grep|grep LOCAL=NO|grep -w oracle$ORACLE_SID |awk '{print $2}'|xargs kill -9
复制

4.4、后台登陆数据库会话及检查等待事件是否已释放

这一步特别重要，确认已正常则继续4.5-4.7，如果还不正常则转> 5重启数据库方案

--检查连接数，使用率已下降
select 'session:' || round(a.cnt / value * 100) || '%'
  from v$parameter s, (select count(*) as cnt from v$session) a
 where s.NAME = 'sessions'
union
select 'process:' || round(a.cnt / value * 100) || '%'
  from v$parameter s, (select count(*) as cnt from v$process) a
 where s.NAME = 'processes';
--检查等待事件，已恢复正常
set lines 180 pages 999
column event format a60
select event, count(*) as amount
  from v$session
 where event is not null
   and wait_class <> 'Idle'
 group by event
 order by count(*) desc;
复制

4.5、启动监听

GI 单实例：crsctl start res $ORACLE_SID.lsnr
RAC 环境：srvctl start listener -l $ORACLE_SID
VCS 单实例：lsnrctl start $ORACLE_SID
复制

4.6、启动 crontab

crontab .cron_file
复制

4.7、前台验证连通性，并通知运营检查应用

5、重启数据库方案（10分钟）

5.1、前置检查步骤（1分钟）

a.检查undo占用

--检查是否有活跃会话占用大于50M的undo
set linesize 180 pagesize 999
select inst_id,sid,serial#,xidusn,undo_size_m,sess_status,status,username,sql_id,prev_sql_id,machine,start_time,logon_time,related_rows,sql_text
  from (select t.xidusn,s.status sess_status,t.status,s.inst_id,s.sid,s.sql_id,s.prev_sql_id,s.serial#,s.username,s.machine,t.start_time,s.logon_time,t.used_urec related_rows, round(t.used_ublk * block_size / 1024 / 1024, 0) undo_size_m,q.sql_text
          from gv$session          s,
               gv$transaction      t,
               dba_tablespaces     dt,
               gv$system_parameter p,
               gv$sql q
         where q.sql_id(+) = s.sql_id and upper(p.name) = 'UNDO_TABLESPACE' and dt.tablespace_name = p.value and t.inst_id = s.inst_id and p.inst_id = s.inst_id and s.saddr = t.ses_addr
           and round(t.used_ublk * block_size / 1024 / 1024, 2) > 50
         order by t.used_ublk desc) t where rownum < 20;
复制

--检查正在回滚的事务占用undo大小
select a.inst_id,(select s.instance_name from gv$instance s where s.INST_ID= a.INST_ID ) instance_name,usn,slt,state,pid,
a.undoblockstotal ,a.undoblocksdone "Done",a.undoblockstotal - a.undoblocksdone "ToDo",to_char(sysdate, 'yyyy-mm-ddhh24:mi:ss')
"Now",round((a.undoblockstotal -a.undoblocksdone)/(a.undoblocksdone / a.cputime)/60,2) "Need_time_min",round(a.undoblockstotal * 8192 / 1024 / 1024 / 1024,2) ||' G' "Undo_used_gb",decode(cputime,0,'unknown',to_char(sysdate +(((undoblockstotal -undoblocksdone) /(undoblocksdone / cputime)) / 86400),'yyyy-mm-ddhh24:mi:ss')) "Estimatedtime to complete" 
  from gv$fast_start_transactions a 
  where state ='RECOVERING';
复制

b.检查MTTR时间

set linesize 180
select recovery_estimated_ios,
       actual_redo_blks,
       target_redo_blks,
       log_file_size_redo_blks,
       target_mttr,
       estimated_mttr
  from v$instance_recovery;
复制

说明： 如果没有大undo占用并且$instance_recovery.estimated_mttr小于600秒，可以立即关闭数据库，否则建议延迟关闭。

5.2、关闭数据库（不分架构）（6分钟）

5.2.1、正常关闭数据库（5分钟）

show parameter job_queue_processes 
show parameter aq_tm_processes       

alter system set job_queue_processes=0 scope=both;
alter system set aq_tm_processes=0 scope=both;

alter system switch logfile; --停主库前多次switch logfile
alter system switch logfile;
alter system switch logfile;
alter system archive log current;
alter system checkpoint;     --做一个检查点
shutdown immediate
复制

说明：如果在5分钟后，关闭还未结束，alert.log中没有相关的关闭信息，执行步骤5.2.2
5.2.2、强制关闭数据库（1分钟）（风险较大，请升级部门长确认方案）

shutdown abort
复制

5.3、启动数据库

5.3.1、如果前面是正常停库

startup
复制

5.3.2、如果前面是非正常停库

create pfile=’/tmp/inittemp.ora’ from spfile;
复制

然后修改新生成的 /tmp/inittemp.ora 参数文件中的设置

设置或修改 fast_start_parallel_rollback=high
复制

启动数据库

startup pfile=’/tmp/inittemp.ora’
复制

使用原参数文件重启数据库

shutdown immediate
startup
复制

5.4、后台登陆数据库会话及检查等待事件，前台验证连通性，并通知运营检查应用

--检查连接数，使用率已下降
select 'session:' || round(a.cnt / value * 100) || '%'
  from v$parameter s, (select count(*) as cnt from v$session) a
 where s.NAME = 'sessions'
union
select 'process:' || round(a.cnt / value * 100) || '%'
  from v$parameter s, (select count(*) as cnt from v$process) a
 where s.NAME = 'processes';
--检查等待事件，已恢复正常
set lines 180 pages 999
column event format a60
select event, count(*) as amount
  from v$session
 where event is not null
   and wait_class <> 'Idle'
 group by event
 order by count(*) desc;
复制

如果前面有停监听记得启动监听

GI 单实例：crsctl start res $ORACLE_SID.lsnr RAC 环境：srvctl start listener -l $ORACLE_SID VCS 单实例：lsnrctl start $ORACLE_SID

复制

Oracle HANG库应急预案

说明： 本文档不针对具体异常进行前置具体分析，目的是针对紧急故障进行快速应急恢复

1、收集 HANGANALYZE（5-10分钟）（可选）

说明： 该步骤目的为采集故障现场信息，结合紧急程度判断，非常紧急情况可跳过该步骤

sqlplus  / as sysdba (后台无法登陆时使用sqlplus -prelim / as sysdba)
SQL> oradebug setmypid  (sqlplus -prelim / as sysdba 时使用oradebug setospid < use an existing process id, for example PMON/SMON> )
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug hanganalyze 3  (RAC 环境使用oradebug -g all hanganalyze 3)
等待 1 分钟
SQL> oradebug hanganalyze 3  (RAC 环境使用oradebug -g all hanganalyze 3)
等待 1 分钟
SQL> oradebug dump systemstate 10 (RAC 环境使用oradebug -g all dump systemstate  10) 
等待 1 分钟
SQL> oradebug dump systemstate 10 (RAC 环境使用oradebug -g all dump systemstate  10) 
等待 1 分钟
SQL> oradebug tracefile_name --------保留 trace 文件
复制

2、全量KILL SESSION（4分钟）

在异常的数据库节点，进行KILL所有 SESSION操作，这里分两种情况：

2.1、情况一：如果能登录数据库（4分钟）

2.1.1、检查占用undo超过50MB的事务，目的在于避免大事务异常回滚需要较长时间，非常重要！！！（0.5分钟）

set linesize 180 pagesize 999
spool /tmp/undo_sess.txt
select inst_id,sid,serial#,xidusn,undo_size_m,sess_status,status,username,sql_id,prev_sql_id,machine,start_time,logon_time,related_rows,sql_text
  from (select t.xidusn,s.status sess_status,t.status,s.inst_id,s.sid,s.sql_id,s.prev_sql_id,s.serial#,s.username,s.machine,t.start_time,s.logon_time,t.used_urec related_rows, round(t.used_ublk * block_size / 1024 / 1024, 0) undo_size_m,q.sql_text
          from gv$session          s,
               gv$transaction      t,
               dba_tablespaces     dt,
               gv$system_parameter p,
               gv$sql q
         where q.sql_id(+) = s.sql_id and upper(p.name) = 'UNDO_TABLESPACE' and dt.tablespace_name = p.value and t.inst_id = s.inst_id and p.inst_id = s.inst_id and s.saddr = t.ses_addr
           and round(t.used_ublk * block_size / 1024 / 1024, 2) > 50
         order by t.used_ublk desc) t where rownum < 20;
spool off
复制

说明：

如有返回，undo_size_m为占用undo大小，需要记录对应的sid。然后转> 步骤2.1.2 KILL SESSION
如无返回，直接进入> 步骤2.1.2 KILL SESSION

2.1.2、记录当前所有用户SESSION对应的OS进程，生成OS级kill -9脚本（注意有占用undo的SESSION需要排除）（0.5分钟）

set head off
set feedback off
set pagesize 0
spool /tmp/kill_9_1.sh
select ' kill -9 ' || spid from (select spid from v$process where addr in(select paddr from v$session where username is not null
--and sid not in (<替换步骤2获取的所有sid>)
));
spool off
复制

2.1.3、生成数据库KILL SESSION脚本（0.5分钟）

set head off
set feedback off
set linesize 180 pagesize 0
spool /tmp/kill_sess_1.sql
select 'alter system kill session '''||sid||','||serial#||''' immediate;'  from v$session  where username is not null
--and sid not in (<替换步骤2获取的所有sid>)
;
spool off
复制

2.1.4、执行kill_sess_1.sql脚本（0.5分钟）

SQL>@/tmp/kill_sess_1.sql
复制

如果session kill之后不能2分钟内不能释放执行OS级kill -9脚本

sh /tmp/kill_9_1.sh
复制

说明：
如果上述kill session无法恢复应用，转步骤3。
数据库恢复正常，后续步骤不需要处理。

2.2、情况二：如果登不进数据库（2分钟）

如果sqlplus 登入hang住或者报ORA-00020连接数满，则在主机层面kill会话，为防止 SID 有相似的地方，这里使用精确匹配：（2分钟）

ps -ef|grep -v grep|grep LOCAL=NO|grep -w oracle$ORACLE_SID |awk '{print $2}'|xargs kill -9
复制

说明：
如果上述kill OS会话无法恢复应用，转步骤3。
如果数据库恢复正常，后续步骤不需要处理。

3、检查ORA-00600/ORA-07445报错（2 分钟）

执行下面命令查看后台alert日志，确认日志中是否有指定的ORA-00600/ORA-07445报错，无则转步骤4，有则转步骤5

tail -20000 alert_$ORACLE_SID.log |egrep -C5 'ORA-00600.*kghfrh:ds.*|ORA-00600.*kjbldynfr:!llink.*|ORA-00600.*kjcv_move_set_level:WRAP.*|ORA-00600.*kjxmgmb_nreq:!bat.*|ORA-00600.*kghfrmrg:nxt.*|ORA-00600.*ktugnb:clschk_kcbne.*|ORA-00600.*kkqcbydrv:saved value.*|ORA-00600.*kdBlkCheckError.*|ORA-00600.*KFFMLOCK.*|ORA-00600.*opiexe.*|ORA-00600.*kghssgfr.*|ORA-00600.*kglbrk-bad-lock.*|ORA-00600.*kgllkdl-bad-session.*|ORA-00600.*kglLockOwnersListDelete.*|ORA-00600.*kglpnal-bad-pinid.*|ORA-00600.*kghfrmrg:prv.*|ORA-00600.*17182.*|ORA-00600.*17147.*|ORA-00600.*17112.*|ORA-00600.*17163.*|ORA-00600.*17110.*|ORA-00600.*kss_get_type: bad control.*|ORA-07445.*kghfre.*|ORA-07445.*kcbzwb.*|ORA-07445.*kglsim_upd_newhp.*|ORA-07445.*kfupsiternext.*|ORA-07445.*kksParentHandleFreeCbk.*|ORA-07445.*kgldafr.*|ORA-07445.*kglntclr.*|ORA-07445.*kghalf.*'
复制

4、重启监听方案（4 分钟）

4.1、临时屏蔽 crontab

>cron_null
crontab cron_null
复制

4.2、停监听

需要先同运营确认是否可以停监听，注意$ORACLE_SID需要替换，而且有的库存在多个监听，都要停掉

GI 单实例：crsctl stop res $ORACLE_SID.lsnr -f
RAC 环境：srvctl stop listener -l $ORACLE_SID
VCS 单实例：lsnrctl stop $ORACLE_SID
复制

4.3、kill OS会话

为防止 SID 有相似的地方，这里使用精确匹配

ps -ef|grep -v grep|grep LOCAL=NO|grep -w oracle$ORACLE_SID |awk '{print $2}'|xargs kill -9
复制

4.4、后台登陆数据库会话及检查等待事件是否已释放

这一步特别重要，确认已正常则继续4.5-4.7，如果还不正常则转> 5重启数据库方案

--检查连接数，使用率已下降
select 'session:' || round(a.cnt / value * 100) || '%'
  from v$parameter s, (select count(*) as cnt from v$session) a
 where s.NAME = 'sessions'
union
select 'process:' || round(a.cnt / value * 100) || '%'
  from v$parameter s, (select count(*) as cnt from v$process) a
 where s.NAME = 'processes';
--检查等待事件，已恢复正常
set lines 180 pages 999
column event format a60
select event, count(*) as amount
  from v$session
 where event is not null
   and wait_class <> 'Idle'
 group by event
 order by count(*) desc;
复制

4.5、启动监听

GI 单实例：crsctl start res $ORACLE_SID.lsnr
RAC 环境：srvctl start listener -l $ORACLE_SID
VCS 单实例：lsnrctl start $ORACLE_SID
复制

4.6、启动 crontab

crontab .cron_file
复制

4.7、前台验证连通性，并通知运营检查应用

5、重启数据库方案（10分钟）

5.1、前置检查步骤（1分钟）

a.检查undo占用

--检查是否有活跃会话占用大于50M的undo
set linesize 180 pagesize 999
select inst_id,sid,serial#,xidusn,undo_size_m,sess_status,status,username,sql_id,prev_sql_id,machine,start_time,logon_time,related_rows,sql_text
  from (select t.xidusn,s.status sess_status,t.status,s.inst_id,s.sid,s.sql_id,s.prev_sql_id,s.serial#,s.username,s.machine,t.start_time,s.logon_time,t.used_urec related_rows, round(t.used_ublk * block_size / 1024 / 1024, 0) undo_size_m,q.sql_text
          from gv$session          s,
               gv$transaction      t,
               dba_tablespaces     dt,
               gv$system_parameter p,
               gv$sql q
         where q.sql_id(+) = s.sql_id and upper(p.name) = 'UNDO_TABLESPACE' and dt.tablespace_name = p.value and t.inst_id = s.inst_id and p.inst_id = s.inst_id and s.saddr = t.ses_addr
           and round(t.used_ublk * block_size / 1024 / 1024, 2) > 50
         order by t.used_ublk desc) t where rownum < 20;
复制

--检查正在回滚的事务占用undo大小
select a.inst_id,(select s.instance_name from gv$instance s where s.INST_ID= a.INST_ID ) instance_name,usn,slt,state,pid,
a.undoblockstotal ,a.undoblocksdone "Done",a.undoblockstotal - a.undoblocksdone "ToDo",to_char(sysdate, 'yyyy-mm-ddhh24:mi:ss')
"Now",round((a.undoblockstotal -a.undoblocksdone)/(a.undoblocksdone / a.cputime)/60,2) "Need_time_min",round(a.undoblockstotal * 8192 / 1024 / 1024 / 1024,2) ||' G' "Undo_used_gb",decode(cputime,0,'unknown',to_char(sysdate +(((undoblockstotal -undoblocksdone) /(undoblocksdone / cputime)) / 86400),'yyyy-mm-ddhh24:mi:ss')) "Estimatedtime to complete" 
  from gv$fast_start_transactions a 
  where state ='RECOVERING';
复制

b.检查MTTR时间

set linesize 180
select recovery_estimated_ios,
       actual_redo_blks,
       target_redo_blks,
       log_file_size_redo_blks,
       target_mttr,
       estimated_mttr
  from v$instance_recovery;
复制

说明： 如果没有大undo占用并且$instance_recovery.estimated_mttr小于600秒，可以立即关闭数据库，否则建议延迟关闭。

5.2、关闭数据库（不分架构）（6分钟）

5.2.1、正常关闭数据库（5分钟）

show parameter job_queue_processes 
show parameter aq_tm_processes       

alter system set job_queue_processes=0 scope=both;
alter system set aq_tm_processes=0 scope=both;

alter system switch logfile; --停主库前多次switch logfile
alter system switch logfile;
alter system switch logfile;
alter system archive log current;
alter system checkpoint;     --做一个检查点
shutdown immediate
复制

shutdown abort
复制

5.3、启动数据库

5.3.1、如果前面是正常停库

startup
复制

5.3.2、如果前面是非正常停库

create pfile=’/tmp/inittemp.ora’ from spfile;
复制

然后修改新生成的 /tmp/inittemp.ora 参数文件中的设置

设置或修改 fast_start_parallel_rollback=high
复制

启动数据库

startup pfile=’/tmp/inittemp.ora’
复制

使用原参数文件重启数据库

shutdown immediate
startup
复制

5.4、后台登陆数据库会话及检查等待事件，前台验证连通性，并通知运营检查应用

--检查连接数，使用率已下降
select 'session:' || round(a.cnt / value * 100) || '%'
  from v$parameter s, (select count(*) as cnt from v$session) a
 where s.NAME = 'sessions'
union
select 'process:' || round(a.cnt / value * 100) || '%'
  from v$parameter s, (select count(*) as cnt from v$process) a
 where s.NAME = 'processes';
--检查等待事件，已恢复正常
set lines 180 pages 999
column event format a60
select event, count(*) as amount
  from v$session
 where event is not null
   and wait_class <> 'Idle'
 group by event
 order by count(*) desc;
复制

如果前面有停监听记得启动监听


GI 单实例：crsctl start res $ORACLE_SID.lsnr
RAC 环境：srvctl start listener -l $ORACLE_SID
VCS 单实例：lsnrctl start $ORACLE_SID
复制

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

其他4

DATAGUARD 主从同步GAP超过xxx小时

一、数据库状态检查的步骤/命令(通过本步骤可以确定数据库当前的状态是否OK)

二、请描述告警出现的可能原因

三、预计多久不处理就可以出现UIOC(P1)

四、告警检查的步骤(通过本步骤确定告警是否是误告)

五、需要保存现场的步骤/命令

六、需要反馈上级以及运营的信息

七、建议处理的流程,步骤和对应命令

八、验证问题已经恢复的步骤和命令

长事务回滚影响性能

一、故障现象

二、可能故障原因

三、分析思路

四、应急处理

ORA-04031_告警处理方案

一、数据库状态检查的步骤/命令(通过本步骤可以确定数据库当前的状态是否OK)

二、请描述告警出现的可能原因

三、预计多久不处理就可以出现UIOC(P1)

四、告警检查的步骤(通过本步骤确定告警是否是误告)

五、需要保存现场的步骤/命令

六、需要反馈上级以及运营的信息

七、建议处理的流程,步骤和对应命令

八、验证问题已经恢复的步骤和命令

Oracle HANG库应急预案

1、收集 HANGANALYZE（5-10分钟）（可选）

2、全量KILL SESSION（4分钟）

2.1、情况一：如果能登录数据库（4分钟）

2.2、情况二：如果登不进数据库（2分钟）

3、检查ORA-00600/ORA-07445报错 （2 分钟）

4、重启监听方案 （4 分钟）

4.1、临时屏蔽 crontab

4.2、停监听

4.3、kill OS会话

4.4、后台登陆数据库会话及检查等待事件是否已释放

4.5、启动监听

4.6、启动 crontab

4.7、前台验证连通性，并通知运营检查应用

5、重启数据库方案（10分钟）

5.1、前置检查步骤（1分钟）

5.2、关闭数据库（不分架构）（6分钟）

5.3、启动数据库

5.4、后台登陆数据库会话及检查等待事件，前台验证连通性，并通知运营检查应用

Oracle HANG库应急预案

1、收集 HANGANALYZE（5-10分钟）（可选）

2、全量KILL SESSION（4分钟）

2.1、情况一：如果能登录数据库（4分钟）

2.2、情况二：如果登不进数据库（2分钟）

3、检查ORA-00600/ORA-07445报错 （2 分钟）

4、重启监听方案 （4 分钟）

4.1、临时屏蔽 crontab

4.2、停监听

4.3、kill OS会话

4.4、后台登陆数据库会话及检查等待事件是否已释放

4.5、启动监听

4.6、启动 crontab

4.7、前台验证连通性，并通知运营检查应用

5、重启数据库方案（10分钟）

5.1、前置检查步骤（1分钟）

5.2、关闭数据库（不分架构）（6分钟）

5.3、启动数据库

5.4、后台登陆数据库会话及检查等待事件，前台验证连通性，并通知运营检查应用

评论

相关阅读

3、检查ORA-00600/ORA-07445报错（2 分钟）

4、重启监听方案（4 分钟）

3、检查ORA-00600/ORA-07445报错（2 分钟）

4、重启监听方案（4 分钟）