分类目录归档：数据库

最近有客户遭遇非系统回滚段报ORA-01558的故障,类似:ORA-01558: out of transaction ID’s in rollback segment _SYSSMU4_1254879796$,在之前的恢复case中遇到两次system rollback报ORA-01558而不能正常启动的案例.(ORA-01092 ORA-00604 ORA-01558故障处理和ORA-01558: out of transaction ID’s in rollback segment SYSTEM),这次是业务回滚段,出来起来相对比较简单,直接重建该回滚段所在undo表空间即可.遭遇该问题的主要原因是由于19c rac中由于禁用drm,设置了_gc_undo_affinity=FALSE参数导致.

ORA-600 [4187] can occur for undo segments where wrap# is close to the max value of 0xffffffff (KSQNMAXVAL).
This normally affects databases with high transaction rate that have existed for a relatively long time.
 
To identify undo segments causing the above error and others that may potentially cause it 
in the future, run the next query:
 
 select b.segment_name, b.tablespace_name 
         ,a.ktuxeusn "Undo Segment Number"
         ,a.ktuxeslt "Slot"
         ,a.ktuxesqn "Wrap#"
   from  x$ktuxe a, dba_rollback_segs b
   where a.ktuxesqn > -429496730 and a.ktuxesqn < 0
       and a.ktuxeusn = b.segment_id;
 
Then drop the undo segments or the undo tablespace from the output above.
 
With this fix in place an error ORA-1558 is eventually produced for the affected undo segment
which still requires dropping the undo segment:
  ORA-1558 "out of transaction ID's in rollback segment %s"
   Cause: All the available transaction id's have been used
   Action: Shutdown the instance and restart using other rollback segment(s),
                then drop the rollback segment that has no more transaction id's.