oracle意外宕机后的回滚恢复

                               创建:2022年7月4日 更新:2022年7月4日 76点热度 0人点赞 0条评论

问题

oracle在执行大事务过程中如果宕机等,那么在下次启动后有可能进行回滚操作,此时可以看到oracle没有运行的sql,但服务器有io较高,查询很缓慢,甚至启动时oracle无法正常open等。

排查

查看哪些在等待,发现是undo

select sid,event,p1,p2,p3,wait_time,seconds_in_wait,state from v$session_wait where wait_class <> 'Idle';

那么查询数据库回滚进程,发现有很多recovery

select * from v$fast_start_servers where state<>'IDLE';

查看回滚需要的进度和时间

select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
         decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"
   from v$fast_start_transactions;

如何应对

首先要保证oracle的可用性,但是极端情况下宕机不可避免,那么可以从以下方面提高oracle的回滚速度:

  • 提升io性能,尽量使用ssd,如果数据太大,那么至少把temp,redo,undo放入SSD
  • 提高并行回滚进程数量,默认为LOW
    ALTER SYSTEM SET FAST_START_PARALLEL_ROLLBACK = HIGH;

其他现象

有另一个库在启动过程中卡住,数据库没有open,可以看到日志提示如下:

Aborting crash recovery due to slave death, attempting serial crash recovery
Beginning crash recovery of 1 threads
Started redo scan
Mon Jul 04 09:53:36 2022
All dispatchers and shared servers shutdown
Completed redo scan
 read 100154 KB redo, 134038 data blocks need recovery
Started redo application at
 Thread 1: logseq 32302, block 231253
Recovery of Online Redo Log: Thread 1 Group 5 Seq 32302 Reading mem 0
  Mem# 0: /opt/app/oracle/oradata/rec/redo05a.log
  Mem# 1: /opt/app/oracle/oradata/rec/redo05b.log
Completed redo application of 18.86MB
Mon Jul 04 09:58:06 2022
Completed crash recovery at
 Thread 1: logseq 32302, block 431561, scn 1895150376
 23156 data blocks read, 23156 data blocks written, 100154 redo k-bytes read
Mon Jul 04 09:58:07 2022
Thread 1 advanced to log sequence 32303 (thread open)
Thread 1 opened at log sequence 32303
  Current log# 6 seq# 32303 mem# 0: /opt/app/oracle/oradata/rec/redo06a.log
  Current log# 6 seq# 32303 mem# 1: /opt/app/oracle/oradata/rec/redo06b.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Jul 04 09:58:08 2022
SMON: enabling cache recovery

这种情况还不清楚目前还只能等待恢复成功,也没有看到有进度相关的信息,或者如何提高并行

yuc

这个人很懒,什么都没留下

error: Content is protected !!