System rollback segment Bad block recovery record in Oracle

Database starts error

The code is as follows	Copy Code
sql> startup Mount Pfile= ' C:\pfile.txt '

The ORACLE routine has started.

The code is as follows	Copy Code
Total System Global area 452984832 bytes Fixed Size 1291120 bytes Variable Size 201329808 bytes Database buffers 243269632 bytes Redo buffers 7094272 bytes

Database loading complete.

The code is as follows	Copy Code
sql> Recover database;

Complete media recovery.

The code is as follows	Copy Code
sql> ALTER DATABASE open; ALTER DATABASE open upgrade *

Line 1th Error:
Ora-01092:oracle instance terminated. Force Disconnect
View Alert Log

The code is as follows

Copy Code

Smon:enabling Cache Recovery Fri May 16 22:49:53 2014 Hex Dump of (file 1, block 404) in trace file c:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_2788.trc Corrupt block relative dba:0x00400194 (file 1, block 404) Fractured block found during buffer read Data in bad block: type:0 format:0 rdba:0x00000000 Last Change scn:0x0000.00000000 seq:0x0 flg:0x00 spare1:0x0 spare2:0x0 spare3:0x0 Consistency value in tail:0x00000000 Check value in block header:0x0 Block checksum disabled Reread of rdba:0x00400194 (file 1, block 404) found same corrupted data Fri May 16 22:49:55 2014 Errors in file c:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_2788.trc: Ora-00604:error occurred at recursive SQL level 1 Ora-01578:oracle data Block Corrupted (file # 1, Block # 404) Ora-01110:data file 1: ' C:\ORADATA\INTERLIB\SYSTEM01.DBF ' Error 604 happened during DB Open, shutting down database User:terminating instance due to error 604

To track the database startup process
This can be clearly seen because block 404 has bad blocks, database recursive SQL access to the bad block error, so that the database can not continue open, the database startup process to do 10046 tracking

The code is as follows

Copy Code

Parsing in CURSOR #2 len=148 dep=1 uid=0 oct=6 lid=0 tim=80533759 hv=3540833987 ad= ' 2a6ce1ec ' Update undo$ Set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#= : 11,ts#=:12,spare1=:13 where Us#=:1 End of STMT PARSE #2: c=62500,e=211766,p=14,cr=85,cu=0,mis=1,r=0,dep=1,og=4,tim=80533755 Binds #2: Kkscoacd Bind#0 Oacdty=01 mxl=32 (mxlc=00 mal=00 scl=00 pre=00 Oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0 Kxsbbbfp=2a6ceac2 bln=32 avl=09 flg=09 Value= "_syssmu1$" Bind#1 oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9b7c bln=24 avl=02 flg=05 value=2 Bind#2 oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9b58 bln=24 avl=02 flg=05 Value=9 Bind#3 oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9b34 bln=24 avl=02 flg=05 value=2 Bind#4 oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 KXSBBBFP=099B9B10 bln=24 avl=02 flg=05 Value=1 Bind#5 oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 KXSBBBFP=099B9AEC bln=24 avl=04 flg=05 value=13332 Bind#6 oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 Kxsbbbfp=099b9ac8 bln=24 avl=04 flg=05 value=24672 Bind#7 oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 KXSBBBFP=099B9AA4 bln=24 avl=05 flg=05 value=47727002 Bind#8 oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 KXSBBBFP=099B9A80 bln=24 avl=01 flg=05 Value=0 Bind#9 oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=099b9a5c bln=24 avl=01 flg=05 Value=0 Bind#10 oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 Kxsbbbfp=099b9a38 bln=24 avl=02 flg=05 Value=1 Bind#11 oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 KXSBBBFP=099B9A14 bln=24 avl=02 flg=05 Value=1 Bind#12 oacdty=02 mxl=22 mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 KXSBBBFP=099B9BA0 bln=22 avl=02 flg=05 Value=1 Wait #2: nam= ' db file sequential read ' ela= 10794 file#=1 block#=404 Blocks=1 obj#=-1 Hex Dump of (file 1, block 404) Dump of memory from 0x1fb9c000 to 0x1fb9e000 1fb9c000 00000000 00000000 00000000 00000000 [...]...... Repeat 286 Times 1fb9d1f0 00000000 00000000 00000000 00000001 [...]...... 1fb9d200 0000a200 0001d831 00000000 05010000 [..... 1 ........ 1fb9d210 00007f30 00000000 00000000 00000000 [0 ...]..... 1fb9d220 00000000 00000000 00000000 00000000 [...]...... Repeat 221 Times Corrupt block relative dba:0x00400194 (file 1, block 404) Fractured block found during buffer read Data in bad block: type:0 format:0 rdba:0x00000000 Last Change scn:0x0000.00000000 seq:0x0 flg:0x00 spare1:0x0 spare2:0x0 spare3:0x0 Consistency value in tail:0x00000000 Check value in block header:0x0 Block checksum disabled Reread of rdba:0x00400194 (file 1, block 404) found same corrupted data EXEC #2: c=0,e=45015,p=1,cr=1,cu=3,mis=1,r=0,dep=1,og=4,tim=80578919 ERROR #2: err=1578 tim=811723 Ora-00604:error occurred at recursive SQL level 1 Ora-01578:oracle data Block Corrupted (file # 1, Block # 404) Ora-01110:data file 1: ' C:\ORADATA\INTERLIB\SYSTEM01.DBF ' EXEC #1: c=328125,e=3468857,p=52,cr=619,cu=10,mis=0,r=0,dep=0,og=1,tim=82331842 ERROR #1: err=1092 tim=811898

dBV Detect bad Blocks

The code is as follows

Copy Code

C:\ORACLE\PRODUCT\10.2.0\DB_1\BIN>DBV FILE = C:\ORADATA\INTERLIB\SYSTEM01.DBF Dbverify:release 10.2.0.3.0-production on Monday May 19 15:22:41 2014 Copyright (c) 1982, +, Oracle. All rights reserved. Dbverify-Start verification: FILE = C:\ORADATA\INTERLIB\SYSTEM01.DBF Page 403 marked as corrupted Corrupt block relative dba:0x00400193 (file 1, block 403) Bad header found during DBV: Data in bad block: Type:70 format:1 rdba:0x00030030 Last Change scn:0x0000.3a6c2e79 seq:0x0 flg:0x00 SPARE1:0X4C spare2:0x45 spare3:0x2 Consistency value in tail:0x00070000 Check value in block header:0x1 Block checksum disabled Page 404 inflow-most likely media corruption Corrupt block relative dba:0x00400194 (file 1, block 404) Fractured block found during DBV: Data in bad block: type:0 format:0 rdba:0x00000000 Last Change scn:0x0000.00000000 seq:0x0 flg:0x00 spare1:0x0 spare2:0x0 spare3:0x0 Consistency value in tail:0x00000000 Check value in block header:0x0 Block checksum disabled Page 498 inflow-most likely media corruption Corrupt block relative dba:0x004001f2 (file 1, block 498) Fractured block found during DBV: Data in bad block: Type:6 Format:2 Rdba:0x004001f2 Last Change scn:0x0000.02d7eb9d seq:0x1 flg:0x06 spare1:0x0 spare2:0x0 spare3:0x0 Consistency value in tail:0x37290601 Check value in block header:0x6c5e Computed Block CHECKSUM:0XC2B5 Page 61078 inflow-most likely media corruption Corrupt block relative dba:0x0040ee96 (file 1, block 61078) Fractured block found during DBV: Data in bad block: Type:6 Format:2 rdba:0x0040ee96 Last Change scn:0x0000.02d5cf11 seq:0x1 flg:0x06 spare1:0x0 spare2:0x0 spare3:0x0 Consistency value in tail:0xa6e30601 Check value in block header:0x51d4 Computed Block checksum:0xf572 Page 61147 inflow-most likely media corruption Corrupt block relative dba:0x0040eedb (file 1, block 61147) Fractured block found during DBV: Data in bad block: Type:6 Format:2 Rdba:0x0040eedb Last Change scn:0x0000.02d7f7e6 seq:0x1 flg:0x06 spare1:0x0 spare2:0x0 spare3:0x0 Consistency value in tail:0x6ed80601 Check value in block Header:0x4ae8 Computed Block checksum:0x893f Page 61502 inflow-most likely media corruption Corrupt block relative dba:0x0040f03e (file 1, block 61502) Fractured block found during DBV: Data in bad block: Type:6 Format:2 rdba:0x0040f03e Last Change scn:0x0000.02d810dd seq:0x1 flg:0x06 spare1:0x0 spare2:0x0 spare3:0x0 Consistency value in tail:0xaf4c0601 Check value in block header:0xd99b Computed Block checksum:0xbf91 Page 61989 inflow-most likely media corruption Corrupt block relative dba:0x0040f225 (file 1, block 61989) Fractured block found during DBV: Data in bad block: Type:6 Format:2 rdba:0x0040f225 Last Change scn:0x0000.02d80f65 seq:0x1 flg:0x06 spare1:0x0 spare2:0x0 spare3:0x0 Consistency value in tail:0xdff70601 Check value in block HEADER:0X4E2A Computed Block checksum:0xd092

Dbverify-Verification Complete





Total pages Checked: 62720


Total pages processed (data): 37740


Total number of pages failed (data): 0


Total pages processed (index): 7021


Total number of pages failed (index): 0


Total number of pages processed (other): 1784


Total number of pages processed (paragraph): 0


Total number of failed pages (segments): 0


Total number of empty pages: 16169


Total number of pages marked as corrupted: 7


Total pages flowing: 5


Top Block scn:316431787 (0.316431787)





C:\oracle\product\10.2.0\db_1\bin>


According to our experience, the impact on the database startup is very large, mainly 403,404,498 bad blocks, in 10201 of the library to draw conclusions (403,404 is rollback,498 is seq$). So now we can understand that in the database startup process, you need to undo$ Table related column information, but because the rollback corresponding block has bad blocks, so that the database can not operate the update operation, which can not start normally.


Sql> SELECT OWNER, Segment_name, Segment_type, Tablespace_name, A.partition_name





2 from Dba_extents A


3 WHERE file_id = &file_id


4 and &block_id BETWEEN block_id and block_id + BLOCKS-1;


Enter value for file_id: 1


Original value 3:where file_id = &file_id


New value 3:where file_id = 1


Enter value for block_id: 404


The original value 4:and &block_id BETWEEN block_id and block_id + BLOCKS-1


New value 4:and 404 BETWEEN block_id and block_id + BLOCKS-1

The code is as follows	Copy Code
OWNER ------------------------------ Segment_name -------------------------------------------------------------------------------- Segment_type Tablespace_name Partition_name ------------------ ------------------------------ ------------------------------ SYS SYSTEM ROLLBACK SYSTEM

Common methods
Set event skip bad block, use suppressed parameters to mask rollback segment

The code is as follows

Copy Code

_corrupted_rollback_segments= _syssmu1$, _syssmu2$, _syssmu3$, _syssmu4$, _syssmu5$, _syssmu6$, _SYSSMU7$, _SYSSMU8$, _ syssmu9$, _syssmu10$, _syssmu11$, _syssmu12$, _syssmu13$, _syssmu14$, _syssmu15$, _syssmu16$, _SYSSMU17$, _SYSSMU18$, _ syssmu19$, _syssmu20$ Undo_management = MANUAL event = 10231 Trace name forever, level, 10232 trace name Context forever, level, 10233 t Race name forever, Level 10 sql> startup Mount Pfile= ' C:\pfile.txt ' The ORACLE routine has started. Total System Global area 452984832 bytes Fixed Size 1291120 bytes Variable Size 201329808 bytes Database buffers 243269632 bytes Redo buffers 7094272 bytes Database loading complete. sql> Recover database; Complete media recovery. sql> ALTER DATABASE open; ALTER DATABASE open upgrade *

Line 1th Error:
Ora-01092:oracle instance terminated. Force Disconnect
As can be seen here, the database in the update undo$ because the rollback exception can not be masked by the rollback segment and skip the method of the bad block to solve. Because it is the system undo block, the idea is to copy a block of the same location from another library of the same version and try it out.

Copy data block

The code is as follows

Copy Code

C:\oracle\product\10.2.0\db_1\bin>bbed Listfile=c:\bbed.txt Password: Bbed:release 2.0.0.0.0-limited Production on Mon May 19 10:47:14 2014 (c) Copyright to Oracle Corporation. All rights reserved. ************* !!! For Oracle Internal with only!!! *************** Bbed> Set BlockSize 8192 BLOCKSIZE 8192 Bbed> Set Mode Edit MODE Edit Bbed> Copy File 2 block 405 to file 1 Block 405 FILE:C:\ORADATA\INTERLIB\SYSTEM01.DBF (1) block:405 offsets:0 to 511 dba:0x00400195 ------------------------------------------------------------------------ 02a20000 94014000 d4c50800 00000104 60890000 00000500 36000000 66004545 0000e81f 401fc81e 201ea01d 481da01c f81b501b e81a801a d8197019 08196018 f8179017 e8168016 1816c815 68152815 d8145814 f8137813 18139812 3812f811 a8116811 18119810 3810b80f 580fd80e 780e380e e80da80d 580d180d c80c480c f00b480b e00a380a d0092809 c0087008 1008d007 80074007 f006b006 60062006 d0052805 a8046004 2004d003 d802b401 6001e000 00000000 0a001800 1300ff1d 78100000 78100000 02000000 00000000 0a165f45 00002e00 0201e71e 94014000 4c004300 03020100 2b05c000 5e15c000 0c001800 0c002000 29000100 82230000 82230000 02000000 00000000 0a163347 00003200 02014500 94014000 58004600 03022100 f30ec000 f50ec000 00000000 00000000 03000000 0d000e00 0e000e00 06c52008 4b5a2705 c51e3337 5f06c520 084b5a27 06c51f4c 28393906 c520084b 5a2706c5 273e0355 0d000000 ff165f43 0a001800 0c001400 0d004000 82230000 82230000 02000000 00000000 0a163346 00003200 02014500 94014000 58004500 03020100 f30ec000 f80ec000 00000000 00000000 06c52008 4b5a2705 C4201c60 5f000000 0c001800 0c002000 2a000100 82230000 82230000 02000000 00000000 0a163345 00003200 02014500 94014000 58004400 03022100 f30ec000 <32 bytes per line> bbed> sum apply Check value for File 1, block 405: Current = 0x8960, required = 0x8960 Restart database sql> startup Mount Pfile= ' C:\pfile.txt ' The ORACLE routine has started. Total System Global area 452984832 bytes Fixed Size 1291120 bytes Variable Size 201329808 bytes Database buffers 243269632 bytes Redo buffers 7094272 bytes Database loading complete. sql> Recover database; Complete media recovery. sql> ALTER DATABASE open; ALTER DATABASE open upgrade * Line 1th Error: Ora-01092:oracle instance terminated. Force Disconnect Analyze Alert Log Mon May 19 10:54:05 2014 Recovery of Online Redo log:thread 1 Group 3 Seq Reading Mem 0 mem# 0:c:\oradata\interlib\redo03.log Block recovery stopped at EOT RBA 13.5.16 Block recovery completed at RBA 13.5.16, SCN 0.316366922 Doing block recovery to file 1 Block 9 Block recovery from LOGSEQ, blocks 3 to SCN 316366921 Mon May 19 10:54:05 2014 Recovery of Online Redo log:thread 1 Group 3 Seq Reading Mem 0 mem# 0:c:\oradata\interlib\redo03.log Block recovery completed at RBA 13.5.16, SCN 0.316366922 Mon May 19 10:54:07 2014 Errors in file c:\oracle\product\10.2.0\admin\interlib\udump\interlib_ora_1208.trc: ORA-00604: Recursive SQL Level 1 error occurred ORA-00607: An internal error occurred while changing the data block ORA-00600: Internal error code, parameters: [4193], [102], [58], [], [], [], [], [] Error 604 happened during DB Open, shutting down database User:terminating instance due to error 604 This is because we have copied the undo section of a different library, and then the database starts with the undo block and the rollback segment header mismatch, so by modifying the undo header to fix it, use bbed to modify the header information, As described in previous articles, this is no longer repeated, specific reference: using bbed to resolve ora-00607/ora-00600[4194] failed to start the database sql> startup Mount Pfile= ' C:\pfile.txt ' The ORACLE routine has started. Total System Global area 452984832 bytes Fixed Size 1291120 bytes Variable Size 201329808 bytes Database buffers 243269632 bytes Redo buffers 7094272 bytes Database loading complete. sql> Recover database; Complete media recovery. Sql> ALTER DATABASE open upgrade; The database has changed. But because the seq$ has bad block, after the database is started, if using the non-system authentication login database will report the following error C:\oracle\product\10.2.0\db_1\bin>sqlplus interlib/oracle Sql*plus:release 10.2.0.3.0-production on Monday May 19 16:29:29 2014 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. ERROR: ORA-00600: Internal error code, parameters: [6807], [audses$], [144], [], [], [], [], [] Please enter user name: C:\oracle\product\10.2.0\db_1\bin>sqlplus/as SYSDBA Sql*plus:release 10.2.0.3.0-production on Monday May 19 16:30:08 2014 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-production With the partitioning, OLAP and Data Mining options Sql> Select Object_type from dba_objects where object_name= ' audses$ '; Object_type ------------------- SEQUENCE

Because the seq$ has a bad block that causes the problem because the database needs to be rebuilt, use EXP to export the data, and then rebuild the related work

Contact: Mobile Phone (13429648788) QQ (107644445)

Link: http://www.xifenfei.com/5333.html

Title: Log System rollback segment Bad block recovery

Author: Xi-FEI