Shulou(Shulou.com)06/01 Report--

Find a problem

The database of a netizen is out of order and the power is lost, so the database can not be open. Looking at the errors in its database alert log alone, it is very simple, as follows:

Fri Oct 26 10:33:53 2018Recovery of Online Redo Log: Thread 1 Group 3 Seq 39 Reading mem 0Mem# 0: / fs/fs/oradata/orcl/redo03.logBlock recovery stopped at EOT rba 39.77.16Block recovery completed at rba 39.77.16, scn 0.1002048587ORACLE Instance orcl (pid = 8)-Error 600 encountered while recovering transaction (9,30) on object 9149.Fri Oct 26 10:33:53 2018Errors in file / fs/fs/oradata/admin/orcl/bdump/orcl_smon_192644.trc:ORA-00600: internal error code Arguments: [6856], [0], [43], [] Fri Oct 26 10:33:56 2018Errors in file / fs/fs/oradata/admin/orcl/bdump/orcl_smon_192644.trc:ORA-00600: internal error code, arguments: [4194], [33], [36], [] Doing block recovery for file 2 block 713Block recovery from logseq 39, block 82 to scn 1002048595

For this kind of error, it is obvious that the rollback segment can be blocked, and the database can be opened smoothly after it is blocked, but the crash will be dropped soon, so rebuilding undo will bypass this problem.

After opening the database, and then looking at the database, you will find that there are many errors in alert log, as shown below:

Fri Oct 26 11:01:46 2018Errors in file / fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc:ORA-00600: internal error code, arguments: [17147], [0x110549070], [], [] Fri Oct 26 11:01:46 2018Errors in file / fs/fs/oradata/admin/orcl/bdump/orcl_m001_373218.trc:ORA-00600: internal error code, arguments: [kdddgb5], [196650], [0] [], [] ORA-600 encountered when generating server alert SMG-4120Fri Oct 26 11:01:47 2018Errors in file / fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc:ORA-00600: internal error code, arguments: [KGHALO4], [0x11047F6F0], [] [] ORA-600 encountered when generating server alert SMG-4121Fri Oct 26 11:01:48 2018Errors in file / fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc:ORA-00600: internal error code, arguments: [KGHALO4], [0x11047F6F0], [] [] ORA-600 encountered when generating server alert SMG-4121Fri Oct 26 11:01:50 2018Errors in file / fs/fs/oradata/admin/orcl/bdump/orcl_m001_373218.trc:ORA-00600: internal error code, arguments: [kdddgb5], [196650], [0], [] Fri Oct 26 11:02:22 2018Errors in file / fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc:ORA-00600: internal error code Arguments: [17114], [0x110549070], [], [] Fri Oct 26 11:02:23 2018Errors in file / fs/fs/oradata/admin/orcl/bdump/orcl_mmon_385148.trc:ORA-00600: internal error code, arguments: [kebm_mmon_main_1], [39], [], [] ORA-00039: error during periodic actionORA-00600: internal error code Arguments: [17114], [0x110549070], [], [] Fri Oct 26 11:03:30 2018Restarting dead background process MMON

In addition, because there are bad blocks reported in other alert log, the system is checked by dbv, and a small number of bad blocks are found, as follows:

DBVERIFY: Release 10.2.0.4.0-Production on Fri Oct 26 10:37:20 2018 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY-Verification starting: FILE = system01.dbf DBV-00200: Block, DBA 4255202, already marked corruptBlock Checking: DBA = 4258751, Block Type = KTB-managed data blockdata header at 0x11022a05ckdbchk: fsbo (596) wrong, (hsz 4178) Page 64447 failed with check code 6129Block Checking: DBA = 4259386, Block Type = KTB-managed data block**** kdxcofbo = 208! = 24UV-end index block validationPage 65082 failed with check code 6401Block Checking: DBA = 4269609, Block Type = Unlimited data segment headerIncorrect extent count in the extent map: 16777317Block Checking: DBA = 4269612 Block Type = KTB-managed data block**** kdxcofbo = 224! = 216 Murray-end index block validationPage 75308 failed with check code 6401Block Checking: DBA = 4269615, Block Type = KTB-managed data block**** actual rows locked by itl 2 = 1! = # in trans. Header = 0Murray-end index block validationPage 75311 failed with check code 6401Page 85271 is influx-most likely media corruptCorrupt block relative dba: 0x00414d17 (file 1, block 85271) Fractured block found during dbv:Data in bad block:type: 6 format: 2 rdba: 0x00414d17last change scn: 0x0000.3afaf495 seq: 0x1 flg: 0x04spare1: 0x0 spare2: 0x0 spare3: 0x0consistency value in tail: 0xfe830601check value in block header: 0x96c6computed block checksum: 0x3c6b Page 85383 is influx-most likely media corruptCorrupt block relative dba: 0x00414d87 (file 1 Block 85383) Fractured block found during dbv:Data in bad block:type: 6 format: 2 rdba: 0x00414d87last change scn: 0x0000.3b6b9d19 seq: 0x1 flg: 0x06spare1: 0x0 spare2: 0x0 spare3: 0x0consistency value in tail: 0x970f0601check value in block header: 0xe825computed block checksum: 0x3c6b DBVERIFY-Verification complete Total Pages Examined: 640000Total Pages Processed (Data): 116312Total Pages Failing (Data): 1Total Pages Processed (Index): 65914Total Pages Failing (Index): 3Total Pages Processed (Other): 64634Total Pages Processed (Seg): 0Total Pages Failing (Seg): 0Total Pages Empty: 393138Total Pages Marked Corrupt: 3Total Pages Influx: 2Highest block SCN: 1002028510 (0.1002028510)

In fact, this part of the error is not difficult to deal with, part is the index of the business table, but the others are almost all AWR-related base tables, and there are two bad blocks related to system-related base tables and indexes, namely I_H_OBJ#_COL# and COM$, HISTGRM$.

For the business index, it is very simple to directly drop reconstruction. For the index of this sys, you can do drop reconstruction by setting 38003 event.

For the base table COM$,HISTGRM$, because it is a non-bootstrap$ core object, it can also be disposed of.

Treatment method

However, considering that it is after all the case of power loss and abnormal undo, it is safer to rebuild the library. Finally, what's a little bit weird about this library is the full library 1.2TB, which has a table LOB automatic 980GB, and it's relatively slow to rebuild the database. For large tables with automatic LOB, it is usually recommended to be based on sharding, otherwise an ORA-01555 error will be reported. The following is a commonly used rowid-based sharding script for your reference:

Set verify off undefine rowid_ranges undefine segment_name undefine owner set head off set pages 0 set trimspool on select 'where rowid between''| | sys.dbms_rowid.rowid_create (1, d.oid, c.fid1, c.bid1, 0) | |''and''| | sys.dbms_rowid.rowid_create (1, d.oid, c.fid2, c.bid2, 9999) | |''| |'| 'from (select distinct b.rn, first_value (a.fid) over (partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid1, last_value (a.fid) over (partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) fid2, first_value (sign (range2-range1), 1 A.bid + ((b.rn-a.range1) * a.chunks1), a.bid) over (partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid1, last_value (decode (range2-range1), 1, a.bid + ((b.rn-a.range1 + 1) * a.chunks1)-1 (a.bid + a.blocks-1)) over (partition by b.rn order by a.fid, a.bid rows between unbounded preceding and unbounded following) bid2 from (select fid, bid, blocks, chunks1, trunc ((sum2-blocks + 1-0.1) / chunks1) range1, trunc ((sum2-0.1) / chunks1) range2 from (select / * + rule * / relative_fno fid Block_id bid, blocks, sum (blocks) over () sum1, trunc ((sum (blocks) over ()) / & & rowid_ranges) chunks1, sum (blocks) over (order by relative_fno, block_id) sum2 from dba_extents where segment_name = upper ('& segment_name') and owner = upper ('& owner') where sum1 > & & rowid_ranges) a (select rownum-1 rn from dual connect by level

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.