首页 » ORACLE » Troubleshooting ORA-00600: internal error code, arguments: [ktubko_1] Instance terminated

Troubleshooting ORA-00600: internal error code, arguments: [ktubko_1] Instance terminated

前不久广州某客户Oracle 11.2.0.2 on Linux一套虚机环境,在线扩容了cpu和memory后数据库出现在ora-600 [ktubko_1] 反复重启, 一个事务回滚相关的内部错误,当然在线修改硬件资源这个操作是不建议的,这里记录一下ra-600 [ktubko_1]应急处理方法。

ktubko – kernel transaction undo get undo record to rollback transaction, non-cr only

DB Alert Log

Mon Oct 10 09:41:09 2022
Dumping diagnostic data in directory=[cdmp_20221010094109], requested by (instance=1, osid=77246 (SMON)), summary=[incident=55874085].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ORACLE Instance anbob01 (pid = 23) - Error 600 encountered while recovering transaction (2489, 1) on object 389809.
Errors in file /oracle/diag/rdbms/anbob/anbob01/trace/anbob01_smon_77246.trc:
ORA-00600: internal error code, arguments: [ktubko_1], [], [], [], [], [], [], [], [], [], [], []
Mon Oct 10 09:41:11 2022
Sweep [inc][55874085]: completed
Sweep [inc2][55874085]: completed
Mon Oct 10 09:41:26 2022
Thread 1 advanced to log sequence 71838 (LGWR switch)
  Current log# 2 seq# 71838 mem# 0: +DATA/dslrprd1/redo02.log
Mon Oct 10 09:41:26 2022
LNS: Standby redo logfile selected for thread 1 sequence 71838 for destination LOG_ARCHIVE_DEST_2
Mon Oct 10 09:41:37 2022
Archived Log entry 29265 added for thread 1 sequence 71837 ID 0x38838837 dest 1:
Mon Oct 10 09:43:50 2022
Thread 1 advanced to log sequence 71839 (LGWR switch)
  Current log# 3 seq# 71839 mem# 0: +DATA/dslrprd1/redo03.log
Mon Oct 10 09:43:50 2022
LNS: Standby redo logfile selected for thread 1 sequence 71839 for destination LOG_ARCHIVE_DEST_2
Mon Oct 10 09:44:00 2022
Archived Log entry 29267 added for thread 1 sequence 71838 ID 0x38838837 dest 1:
Mon Oct 10 09:44:11 2022
Errors in file /oracle/diag/rdbms/anbob/anbob01/trace/anbob01_smon_77246.trc  (incident=55874086):
ORA-00600: internal error code, arguments: [ktubko_1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/anbob/anbob01/incident/incdir_55874086/anbob01_smon_77246_i55874086.trc
Mon Oct 10 09:44:13 2022
PMON (ospid: 77198): terminating the instance due to error 474
Mon Oct 10 09:44:13 2022
opiodr aborting process unknown ospid (110899) as a result of ORA-1092
Mon Oct 10 09:44:13 2022
System state dump requested by (instance=1, osid=77198 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /oracle/diag/rdbms/anbob/anbob01/trace/anbob01_diag_77208.trc
Mon Oct 10 09:44:13 2022
opiodr aborting process unknown ospid (115704) as a result of ORA-1092
Mon Oct 10 09:44:13 2022
ORA-1092 : opitsk aborting process
Instance terminated by PMON, pid = 77198

Smon Trace file

*** ACTION NAME:() 2022-10-10 09:32:01.607
 
SMON[TX-RECO]:about to recover undo segment 1036 status:6 inst:1
SMON[TX-RECO]: mark undo segment 1036 as available status:2 ret:0
SMON[TX-RECO]:about to recover undo segment 1276 status:6 inst:1
Dead transaction 0x04fc.01b.000068f5 recovered by SMON
SMON[TX-RECO]: mark undo segment 1276 as available status:2 ret:0
SMON[TX-RECO]:about to recover undo segment 1303 status:6 inst:1
Dead transaction 0x0517.00e.0000354d recovered by SMON
SMON[TX-RECO]: mark undo segment 1303 as available status:2 ret:0
SMON[TX-RECO]:about to recover undo segment 2489 status:6 inst:2
Incorrect next uba in kturCurrBackoutOneChg while backing out xid: 0x09b9.001.00008634 uba: 0x0881036d.0a9c.35
Undo record:
ktubu redo: slt: 1 rci: 52 opc: 10.22 objn: 389809 objd: 389809 tsn: 13
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00000000
index undo for leaf key operations
KTB Redo 
op: 0x03  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: Z
Dump kdilk : itl=42, kdxlkflg=0x1 sdc=0 indexid=0x100df6ea block=0x10cdfc32
(kdxlpu): purge leaf row
key :(19):  0d 38 36 30 30 30 32 32 38 39 34 33 32 38 04 44 53 4c 52
Undo block: tsn 0x5 rdba: 0x881036d
Dump of buffer cache at level 4 for tsn=5, rdba=142672749
BH (0x65fa90938) file#: 34 rdba: 0x0881036d (34/66413) class: 4994 ba: 0x65e03a000
  set: 61 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
  dbwrid: 0 obj: -1 objn: 0 tsn: 5 afn: 34 hint: f
  hash: [0x95ac76658,0x95ac76658] lru: [0x65fa90b98,0x91cfab5e0]
  ckptq: [NULL] fileq: [NULL] objq: [0x8ac156690,0x8ac156690] objaq: [0x65fa91618,0x8ac156680]
  st: SCURRENT md: NULL tch: 1 atm: 542555381,542562694
  flags: affinity_lock
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
  cr pin refcnt: 0 sh pin refcnt: 0
Data block dump: tsn: 0xd rdba: 0x10cdfc32
Dump of buffer cache at level 3 for tsn=13, rdba=281934898
BH (0x65fb68060) file#: 67 rdba: 0x10cdfc32 (67/916530) class: 1 ba: 0x65f290000
  set: 65 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
  dbwrid: 1 obj: 389809 objn: 389809 tsn: 13 afn: 67 hint: f
  hash: [0x90b7cf590,0x90b7cf590] lru: [0x65fb682c0,0x91cfad1e0]
  ckptq: [NULL] fileq: [NULL] objq: [0x892283d50,0x892283d50] objaq: [0x892283d40,0x892283d40]
  st: XCURRENT md: NULL tch: 1 atm: 542561348,542562739
  flags: affinity_lock
  LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
  cr pin refcnt: 0 sh pin refcnt: 0
  buffer tsn: 13 rdba: 0x10cdfc32 (67/916530)
  scn: 0x011d.b929e3fd seq: 0x04 flg: 0x04 tail: 0xe3fd0604
  frmt: 0x02 chkval: 0xec67 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000065F290000 to 0x000000065F292000
...
Block header dump:  0x10cdfc32
 Object id on Block? Y
 seg/obj: 0x5f2b1  csc: 0x11d.b929e3fd  itc: 131  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10cdfa00 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x041c.002.0002fdf3  0x00c0ed7b.5f77.01  CB--    0  scn 0x011d.b8c1f170
0x02   0x07ef.008.000510b1  0x08814853.5132.1a  C---    0  scn 0x011d.b8c66bc2
0x03   0x07f3.018.00083fd1  0x08806b85.658e.0f  C---    0  scn 0x011d.b8c66be7
0x04   0x0403.003.0014f1d4  0x00c09259.a7ae.13  C---    0  scn 0x011d.b8c0a5be
0x05   0x07f3.01d.00084158  0x08806b85.658e.16  C---    0  scn 0x011d.b8c66c11
0x06   0x0403.00c.0014f07c  0x00c0d7fa.a7b0.01  C---    0  scn 0x011d.b8c1340b
0x07   0x07f3.019.00084187  0x08806b85.658e.1f  C---    0  scn 0x011d.b8c66c3f
0x08   0x07ff.01a.000fee92  0x08c070e5.96c9.1f  C---    0  scn 0x011d.b8c71373
...
0x28   0x0934.013.0000931e  0x08c088d5.0bb5.23  C-U-    0  scn 0x011d.b8be4f62
0x29   0x09ba.003.000009b4  0x08c00f5c.00df.24  C-U-    0  scn 0x011d.b8be4f92
0x2a   0x09b9.001.00008634  0x0881036d.0a9c.35  C-U-    0  scn 0x0114.bacea0d2
0x2b   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
...
0x7e   0x0886.005.00009a2b  0x08c1d30c.0c13.28  C-U-    0  scn 0x011d.b8be5224
0x7f   0x0ae4.004.00000ab4  0x11c03372.00c9.40  C-U-    0  scn 0x011d.b8be5371
0x80   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x81   0x0afa.010.0000010a  0x11c03d6f.004e.44  C-U-    0  scn 0x011d.b8be5320
0x82   0x0ac7.000.00000904  0x11c03f90.009e.1e  C-U-    0  scn 0x011d.b8be5165
0x83   0x0b13.010.000000e7  0x11c4e6ca.0052.0a  C-U-    0  scn 0x011d.b8be5873
Leaf block dump
===============
header address 27366329468=0x65f290c7c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 2
kdxconro 93
kdxcofbo 222=0xde
kdxcofeo 2425=0x979
kdxcoavs 2203
kdxlespl 0
kdxlende 0
kdxlenxt 269401599=0x100ebdff
kdxleprv 269401093=0x100ebc05
kdxledsz 6
kdxlebksz 4936
row#0[2614] flag: ------, lock: 0, len=27, data:(6):  0f 6b 2b ca 00 00
...
ORACLE Instance dslrprd11 (pid = 23) - Error 600 encountered while recovering transaction (2489, 1) on object 389809.

*** 2022-10-10 09:32:02.138
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00600: internal error code, arguments: [ktubko_1], [], [], [], [], [], [], [], [], [], [], []
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
skdstdst()+36        call     kgdsdst()            000000000 ? 000000000 ?
                                                   7FFFD22095D8 ? 000000001 ?
                                                   000000001 ? 000000002 ?
ksedst1()+98         call     skdstdst()           000000000 ? 000000000 ?
                                                   7FFFD22095D8 ? 000000001 ?
                                                   000000000 ? 000000002 ?
ksedst()+34          call     ksedst1()            000000000 ? 000000001 ?
                                                   7FFFD22095D8 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbkedDefDump()+2741  call     ksedst()             000000000 ? 000000001 ?
                                                   7FFFD22095D8 ? 000000001 ?
                                                   000000000 ? 000000002 ?
ksedmp()+36          call     dbkedDefDump()       000000003 ? 000000000 ?
                                                   7FFFD22095D8 ? 000000001 ?
                                                   000000000 ? 000000002 ?
kturRecoverTxn()+17  call     ksedmp()             000000003 ? 000000000 ?
293                                                7FFFD22095D8 ? 000000001 ?
                                                   000000000 ? 000000002 ?
kturRecoverUndoSegm  call     kturRecoverTxn()     7FFFD220EEB8 ? 000000001 ?
ent()+1202                                         000000001 ? 000000000 ?
                                                   000000010 ? 2200000004 ?
kturRecoverActiveTx  call     kturRecoverUndoSegm  0000009B9 ? 7FFFD220F058 ?
ns()+3684                     ent()                000000001 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
ktprbeg()+323        call     kturRecoverActiveTx  000000010 ? 000000000 ?
                              ns()                 0000009B9 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
ktmmon()+13554       call     ktprbeg()            000000000 ? 000000000 ?
                                                   0000009B9 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
ktmSmonMain()+207    call     ktmmon()             0600304B8 ? 000000000 ?
                                                   0000009B9 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
ksbrdp()+971         call     ktmSmonMain()        0600304B8 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
opirip()+618         call     ksbrdp()             0600304B8 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
opidrv()+598         call     opirip()             000000032 ? 000000004 ?
                                                   7FFFD2211B08 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
sou2o()+98           call     opidrv()             000000032 ? 000000004 ?
                                                   7FFFD2211B08 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
opimai_real()+261    call     sou2o()              7FFFD2211AE0 ? 000000032 ?
                                                   000000004 ? 7FFFD2211B08 ?
                                                   00000FFFF ? 100000010 ?
ssthrdmain()+252     call     opimai_real()        000000000 ? 7FFFD2211CD0 ?
                                                   000000004 ? 7FFFD2211B08 ?
                                                   00000FFFF ? 100000010 ?
main()+196           call     ssthrdmain()         000000003 ? 7FFFD2211CD0 ?
                                                   000000001 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
__libc_start_main()  call     main()               000000003 ? 7FFFD2211E70 ?
+253                                               000000001 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
_start()+36          call     __libc_start_main()  000A077E4 ? 000000001 ?
                                                   7FFFD2211E68 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
 

--------------------- Binary Stack Dump ---------------------

Dump continued from file: /oracle/diag/rdbms/dslrprd1new/anbob01/trace/anbob01_smon_77246.trc
ORA-00600: internal error code, arguments: [ktubko_1], [], [], [], [], [], [], [], [], [], [], []

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
skdstdst()+36        call     kgdsdst()            000000000 ? 000000000 ?
                                                   7FFFD2201058 ? 000000001 ?
                                                   000000001 ? 000000002 ?
ksedst1()+98         call     skdstdst()           000000000 ? 000000000 ?
                                                   7FFFD2201058 ? 000000001 ?
                                                   000000000 ? 000000002 ?
ksedst()+34          call     ksedst1()            000000000 ? 000000001 ?
                                                   7FFFD2201058 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbkedDefDump()+2741  call     ksedst()             000000000 ? 000000001 ?
                                                   7FFFD2201058 ? 000000001 ?
                                                   000000000 ? 000000002 ?
ksedmp()+36          call     dbkedDefDump()       000000003 ? 000000002 ?
                                                   7FFFD2201058 ? 000000001 ?
                                                   000000000 ? 000000002 ?
ksfdmp()+64          call     ksedmp()             000000003 ? 000000002 ?
                                                   7FFFD2201058 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbgexPhaseII()+1764  call     ksfdmp()             000000003 ? 000000002 ?
                                                   7FFFD2201058 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbgexProcessError()  call     dbgexPhaseII()       7FBF06D98710 ? 7FBF06B51798 ?
+2651                                              7FFFD220D3D0 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbgeExecuteForError  call     dbgexProcessError()  7FBF06D98710 ? 7FBF06B51798 ?
()+83                                              000000001 ? 000000000 ?
                                                   100000000 ? 000000002 ?
dbgePostErrorKGE()+  call     dbgeExecuteForError  7FBF06D98710 ? 7FBF06B51798 ?
2131                          ()                   000000001 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbkePostKGE_kgsf()+  call     dbgePostErrorKGE()   000000000 ? 7FBF06920498 ?
63                                                 000000258 ? 7FBF06B51798 ?
                                                   100000000 ? 000000002 ?
kgeadse()+390        call     dbkePostKGE_kgsf()   00B7C7EA0 ? 7FBF06920498 ?
                                                   000000258 ? 7FBF06B51798 ?
                                                   100000000 ? 000000002 ?
kgerinv_internal()+  call     kgeadse()            00B7C7EA0 ? 7FBF06920498 ?
45                                                 000000258 ? 100000000 ?
                                                   000000000 ? 000000002 ?
kgerinv()+33         call     kgerinv_internal()   00B7C7EA0 ? 7FBF06920498 ?
                                                   7FFFD21FD890 ? 000000258 ?
                                                   000000000 ? 000000002 ?
kgeasnmierr()+143    call     kgerinv()            00B7C7EA0 ? 7FBF06920498 ?
                                                   7FFFD21FD890 ? 000000002 ?
                                                   000000000 ? 000000002 ?
kturCurrBackoutOneC  call     kgeasnmierr()        00B7C7EA0 ? 7FBF06920498 ?
hg()+5986                                          7FFFD21FD890 ? 000000002 ?
                                                   000000000 ? 000000000 ?
kturRecoverTxn()+15  call     kturCurrBackoutOneC  7FFFD220EBC8 ? 7FFFD220E5D4 ?
227                           hg()                 000000004 ? 7FFFD220EEB8 ?
                                                   7FFFD220EAD0 ? 7FFFD220E5DC ?
kturRecoverUndoSegm  call     kturRecoverTxn()     7FFFD220EEB8 ? 000000001 ?
ent()+1202                                         000000001 ? 000000000 ?
                                                   000000010 ? 2200000004 ?
kturRecoverActiveTx  call     kturRecoverUndoSegm  0000009B9 ? 7FFFD220F058 ?
ns()+3684                     ent()                000000001 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
ktprbeg()+323        call     kturRecoverActiveTx  000000010 ? 000000000 ?
                              ns()                 0000009B9 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
ktmmon()+13554       call     ktprbeg()            000000000 ? 000000000 ?
                                                   0000009B9 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
ktmSmonMain()+207    call     ktmmon()             0600304B8 ? 000000000 ?
                                                   0000009B9 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
ksbrdp()+971         call     ktmSmonMain()        0600304B8 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
opirip()+618         call     ksbrdp()             0600304B8 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
opidrv()+598         call     opirip()             000000032 ? 000000004 ?
                                                   7FFFD2211B08 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?
sou2o()+98           call     opidrv()             000000032 ? 000000004 ?
                                                   7FFFD2211B08 ? 000000000 ?
                                                   00000FFFF ? 100000010 ?


Note:
kgeasnmierr – kernel generic error assert named internal error
kturCurrBackoutOneChg – kernel transaction undo rollback current read; backout change (rollback)
kturRecoverTxn() kernel transaction undo rollback recover transaction
kturRecoverUndoSegment – kernel transaction undo rollback recover undo segment
kturRecoverActiveTxns – kernel transaction undo rollback recover active transactions
ktprbeg() kernel transaction parallel recovery begin
ktmmon()+13554 kernel transaction transaction monitor (smon) tx monitor background timeout action (smon)
ktmSmonMain()+207 kernel transaction transaction monitor (smon) main smon routine

从trace文件中可以看到是obj# 389809的索引段在level block 的index undo for leaf key operations时purge leaf row出现了不正确的uba,rollback segment编号为2489, 事务表状态为6 PARTLY AVAILABLE( like DBA_ROLLBACK_SEGS.status or undo$.status)。

/* segment status (see KTS.H): */
/* 1 = INVALID, 2 = AVAILABLE, 3 = IN USE, 4 = OFFLINE, 5 = NEED RECOVERY,
* 6 = PARTLY AVAILABLE (contains in-doubt txs)

PARTLY AVAILABLE means  it contains active transactions that SMON has not yet finished recovering  or it contains in-doubt transactions(分布式事务)

根据ora600 和call stack 不难定位 匹配bug 14604955
Rediscovery Notes
ORA-00600 [ktubko_1] error.

Top Call Stack:
.. <- kgeasnmierr <- kturCurrBackoutOneChg <- kturRecoverTxnMain <- kturRecoverTxn <- kturRecoverUndoSegment
<- kturRecoverActiveTxns <- ktprbeg <- ktmmon <- ktmSmonMain <- ksbrdp <- opirip <- ..

解决方法
对于undo segment问题通常紧急救援是屏蔽rollseg, drop, 重建对象。当然如果有备份建议备份还原。
可以先屏蔽报错中提示的rollback segment 使用_OFFLINE_ROLLBACK_SEGMENTS或 _CORRUPTED_ROLLBACK_SEGMENTS, 需要重启实例。

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

ORACLE will not support a database that has used the

_CORRUPTED_ROLLBACK_SEGMENTS parameter.

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

UNDO SEGMENT

查找UNDO SEGMENT NAME

select segment_name from dba_rollback_segs where segment_id=2489;
--  _SYSSMU2489_xxxxxx$

--增加pfile ,启动数据库
*._OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU2489_xxxxxx$

-- 可以dump undo header查看rollseg上的活动事务,通常如果不是非常高的并发,1个undo segment中算法均分是1个ACTIVE TX。  可以dump rollseg header查看。

ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU2489_xxxx$';

当然因为有ACTIVE tx, 可以直接使用_CORRUPTED_ROLLBACK_SEGMENTS.

_OFFLINE_ROLLBACK_SEGMENTS或 _CORRUPTED_ROLLBACK_SEGMENTS参数

If the datafile related to rollback segment get corrupted, the first method go for the recovery from backup.

If your backup is not working at all, so last unsupported option to choose the parameter _OFFLINE_ROLLBACK_SEGMENTS in init.ora. _OFFLINE_ROLLBACK_SEGMENTS is a unsupported init.ora parameter which can allow you to cause logical database corruption. It is rarely required in Oracle7.3 onwards due to deferred transaction recovery.

When you required this parameter to set _OFFLINE_ROLLBACK_SEGMENTS ?

When you start a database, SMON will recover all the uncommitted transaction. To do the recovery it will check the rollback segment header first to read the transaction table. But if any reason SMON is unable to read the header block of any online rollback segments then you required this parameter.

Unreadable rollback segment header may cause for block corruption, datafile corruption or datafile unavailable.If tablespace is online but the rollback segment header block get corrupted, you need this parameter to start the database.Because you have to stop SMON from performing transaction recovery for specific rollback segments. While starting the database SMON will not check the transaction table only for those rollback segments under the parameter _OFFLINE_ROLLBACK_SEGMENTS . You should choose this parameter only when all the transaction related to this rollback segment is INACTIVE.

How to know the transaction is in inactive mode ?

Do the following steps

===============

1. Start the database with _OFFLINE_ROLLBACK_SEGMENTS first.
2. Find the header file number and header block number from the following query.

select segment_name,header_file,header_block from dba_segments where segment_name like '';

3. dump the block with following command.

alter system dump datafile n   block n ;

4. observe for the uncommitted transaction from block dump like below. Observe the status field.
If it is ‘9’ means transaction committed. but if the status value is ’10’ then it is an uncommitted transaction.So this rollback segment is not a good candidate for _OFFLINE_ROLLBACK_SEGMENTS.

5. After checking the step 4, if you find all the status field is ‘9’, then drop the rollback segment immediately.
6. Stop the database, take the parameter out from init.ora and start the database. So your dictionary is perfect.
you can continue your production job with this database.

7. After checking the step 4, if you find any status field is ’10’ , then you should immediately shutdown the database.
Start the database with “_corrupted_rollback_segments”. And put the name of those rollback segment names where did you find the value of status=’10’.

8. There is a logical corruption. So you should drop the rollback segment And do a complete export and import of this database to make it supported.

 

What is _CORRUPTED_ROLLBACK_SEGMENTS ?

What is _CORRUPTED_ROLLBACK_SEGMENTS ?

_CORRUPTED_ROLLBACK_SEGMENTS is more dangerous parameter than _OFFLINE_ROLLBACK_SEGMENTS.

It basically prevents access to the listed rollback segments headers and assumes all transactions in them are committed. This can very easily cause logical database corruption. Opening any database with any rollback segment listed in this parameter instantly makes the database unsupported. This should
only EVER be used after a full backup has been taken as as part of a last attempt to salvage data from a database.

Let me make a chart for more details DATAFILE TRANSACTION

_offline_rollback_segments ONLINE/OFFLINE/RECOVERY INACTIVE

_corrupted_rollback_segments ONLINE/OFFLINE/RECOVERY ACTIVE

 ONLINE ACTIVE * you can use this option, but better option to choose _corrupted_rollback_segments

IMPORTANT: If this corruption happened with system rollback segment, there is no way to use any of this parameter. You must have to recover database from old backup.

Differences between these parameters

There are differences between these two parameters. If the rollback segment is listed in the _offline parameter list, the transaction table is still read accessible. This is important for delayed block cleanout. If a select statement reads a data block with an open ITL which points to the transaction table of the _offline rollback segment, the table is still checked. If the transaction is committed, delayed block clean out occurs. If the transaction is uncommitted, it will generate ORA-1578. It will not allow to select the block.

If the rollback segment is listed in the _corrupted parameter list, the transaction table is not read accessibleAll transactions are assumed COMMITTED and delayed block cleanout will occur but there is no consistent read view of uncommitted transactions (logical corruption).

 

reference https://www.parnassusdata.com/en/node/206

打赏

,

目前这篇文章还没有评论(Rss)

我要评论