分类目录归档：数据库

_gc_undo_affinity=FALSE触发ORA-01558

发表于 2025 年 2 月 1 日由惜分飞

最近有客户遭遇非系统回滚段报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参数导致.
gc_undo_affinity_ora-1558

还有一个类似bug,需要注意:Bug 19700135 ORA-600 [4187] when the undo segment wrap# is close to the max value of 0xffffffff,主要影响版本为：

关于该bug的描述

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.

发表在 Oracle | 标签为 ORA-01558, ORA-600 4187, _gc_undo_affinity | 评论关闭

public授权语句

发表于 2025 年 1 月 31 日由惜分飞

在上一篇文章中写到数据泵迁移导致sys授权丢失(impdp导入数据丢失sys授权问题分析),这次进一步完善在有些系统中,会出现对部分权限授权给public的操作,使用逻辑方式(exp/imp,expdp/impdp)进行迁移,可能会导致这个部分权限丢失,从而使得系统部分功能异常,可以通过类似sql查询出来授权语句,在新库上执行

select 'grant ' || privilege || ' on ' || '"' || OWNER || '"."' ||
       table_name || '"' || ' to ' || grantee || ';' "GRANTS"
  from dba_tab_privs
 where privilege not in ('READ', 'WRITE')
   and table_name not like '%/%'
   and owner not in ('SYSTEM',
                     'WMSYS',
                     'XDB',
                     'CTXSYS',
                     'MDSYS',
                     'EXFSYS',
                     'APEX_030200',
                     'ORDSYS',
                     'ORDPLUGINS',
                     'DBSNMP',
                     'OLAPSYS',
                     'ORDDATA')
   and grantee in ('PUBLIC')
 order by 1;

发表在 Oracle | 标签为 public授权 | 评论关闭