System rollback segment Bad block recovery record in Oracle

Source: Internet
Author: User
Tags dba error code relative reserved rollback oracle database sqlplus

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.