Oracle Block Corruption - Detection and Resolution

-

Oracle Block Corruption - Detection and Resolution


Any kind of corruption in database can lead to stand still operation of the organization and can cause sever impact to organisation. In this blog , We are going to discuss about Block Corruption of the oracle database.

Block Corruption : 

Oracle Block Corruption occurs due inconsistent block Header ,Footer ,Checksum or Structure. Depending on the kind of insistency ,We can divide corruption into Physical and Logical corruption.

Physical Corruption is due to Bad header, header or footer mismatch ,Invalid Block checksum & misplaced block. Usually this kind of the block corruption are due to external reason and difficult to resolve by oracle utilities alone. 

Logical Corruption is due to corrupt or invalid Block structure when block checksum is valid. This kind of the Corruption can be resolved by the Oracle Utilities.

Detection of Block Corruption


Whenever a corrupt block is read Oracle reports an error in the alert log. Below are the message written into the alert log for block corruption :

ORA-00600: internal error code, arguments: [4553], [2], [0], [], [], [], [], []

Tue Jan  5 16:20:17 2010

Errors in file /u02/oracle/admin/prod/bdump/prod1/prod1_p013_319648.trc:

ORA-01578: ORACLE data block corrupted (file # 1607, block # 368273)

ORA-01110: data file 1607: '/dev/rprod_4096_363'

ORA-10564: tablespace I_PRICE_REBUILD

ORA-01110: data file 1607: '/dev/rprod_4096_363'

ORA-10560: block type '0'

ORA-00600: internal error code, arguments: [4553], [2], [0], [], [], [], [], []

Tue Jan  5 16:20:17 2010

Errors in file /u02/oracle/admin/prod/bdump/prod1/prod1_p005_278668.trc:

ORA-00600: internal error code, arguments: [4553], [2], [0], [], [], [], [], []

Tue Jan  5 16:20:17 2010

Errors in file /u02/oracle/admin/prod/bdump/prod1/prod1_p013_319648.trc:

ORA-00600: internal error code, arguments: [2037], [2449918198], [37382], [0], [0], [0], [3371616278], [3540]

Tue Jan  5 16:20:17 2010

Hex dump of (file 1630, block 406686) in trace file /u02/oracle/admin/prod/bdump/prod1/prod1_dbw1_123112.trc

Corrupt block relative dba: 0x97c6349e (file 1630, block 406686)

Bad header found during preparing block for write

Data in bad block:

 type: 2 format: 1 rdba: 0x000097c6

 last change scn: 0x1121.349ec816 seq: 0x0 flg: 0x02

 spare1: 0x6 spare2: 0x2 spare3: 0x0

 consistency value in tail: 0x00001121

 check value in block header: 0x300

 block checksum disabled

Tue Jan  5 16:20:17 2010

Errors in file /u02/oracle/admin/prod/bdump/prod1/prod1_dbw1_123112.trc:

ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], []

Tue Jan  5 16:20:17 2010

Errors in file /u02/oracle/admin/prod/bdump/prod1/prod1_p012_213838.trc:

ORA-00600: internal error code, arguments: [2037], [721587535], [11010], [1], [6], [0], [2370816022], [4899]

Tue Jan  5 16:20:17 2010

Hex dump of (file 1574, block 197819) in trace file /u02/oracle/admin/prod/bdump/prod1/prod1_dbw0_82638.trc

Corrupt block relative dba: 0x89c304bb (file 1574, block 197819)

Bad header found during preparing block for write

Data in bad block:

 type: 0 format: 0 rdba: 0x000089c3

 last change scn: 0x1175.04bbc816 seq: 0x0 flg: 0x02

 spare1: 0x6 spare2: 0x2 spare3: 0x0

 consistency value in tail: 0x01801175

 check value in block header: 0x100

 block checksum disabled

Tue Jan  5 16:20:17 2010

Errors in file /u02/oracle/admin/prod/bdump/prod1/prod1_dbw0_82638.trc:

ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], []

Tue Jan  5 16:20:17 2010

Errors in file /u02/oracle/admin/prod/bdump/prod1/prod1_dbw1_123112.trc:

ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], []

Tue Jan  5 16:20:17 2010

DBW1: terminating instance due to error 471

Instance terminated by DBW1, pid = 123112

Tue Jan  5 16:28:52 2010

 
  

Block Corruption Resolution :


STEP 1 :- Need to check the alert log and collect the list of datafile that’s is showing data block corruption:

 

Corrupt block relative dba: 0x89c304bb (file 1574, block 197819)
Corrupt block relative dba: 0xb684a70a (file 1753, block 304906)
Corrupt block relative dba: 0x9206c2b7 (file 1607, block 443063)
Corrupt block relative dba: 0x3983169a (file 1253, block 202394)
Corrupt block relative dba: 0xb08e885a (file 1729, block 952410)
Corrupt block relative dba: 0xa243d619 (file 1672, block 251417)
Corrupt block relative dba: 0xb0cd1252 (file 1730, block 856658)
Corrupt block relative dba: 0xb881ad36 (file 1761, block 109878)


STEP 2 :- Connect to RMAN and run: “backup check logical validate database;”
This command will take time to complete. Once this will complete, it will list all the data block along with the corresponding datafile in view V$DATABASE_BLOCK_CORRUPTION. 

rman target /

 

Recovery Manager: Release 11.1.0.7.0 - Production on Sun May 12 11:35:52 2013

 

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

 

connected to target database: prod (DBID=3681250126)

 

RMAN> backup check logical validate database;

 

Starting backup at 12-MAY-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=289 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=286 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=292 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=288 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=291 device type=DISK

allocated channel: ORA_DISK_6

channel ORA_DISK_6: SID=295 device type=DISK

allocated channel: ORA_DISK_7

channel ORA_DISK_7: SID=287 device type=DISK

allocated channel: ORA_DISK_8

channel ORA_DISK_8: SID=293 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00015 name=/u04/oradata/prod/prod_dmdb_indx_01.dbf

input datafile file number=00024 name=/u03/oradata/prod/prod_staging_data_01.dbf

input datafile file number=00010 name=/u02/oradata/prod/prod_dmdb_data_02.dbf

input datafile file number=00002 name=/u02/oradata/prod/prod_sysaux_01.dbf
………
………
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

15   OK     0              235225       981376          11454196539294

  File Name: /u04/oradata/prod/prod_dmdb_indx_01.dbf

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              0

  Index      0              738109

  Other      0              8042

 

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

24   OK     0              1            929664          11454221478170

  File Name: /u03/oradata/prod/prod_staging_data_01.dbf

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              909040

  Index      0              14799

  Other      0              5824

 

Finished backup at 12-MAY-13



STEP 3 :- Run “blockrecover corruption list”. 
It will pull all the corrupted blocks from the v$database_corruption_list and will recover. 

RMAN> blockrecover corruption list;

 

Starting recover at 12-MAY-13

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

using channel ORA_DISK_7

using channel ORA_DISK_8

 

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00013

channel ORA_DISK_1: reading from backup piece /backupset/2013_05_11/o1_mf_nnnd0_WEEKLY_FULL_ONLI      NE_8rxtmoj2_.bkp

channel ORA_DISK_1: piece handle /backupset/2013_05_11/o1_mf_nnnd0_WEEKLY_FULL_ONLINE_8rxtmoj2_.      bkp tag=WEEKLY_FULL_ONLINE

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:09:06


STEP 4 :- Once the recovery will finish, run once again the “backup check logical validate database;” and after completion view again the V$DATABASE_BLOCK_CORRUPTION. No Datafile will be listed there, if it comes,  

                    then again need to run the step 3.


----------------------------------------------------------------------------------------------------

Commands that can be used:

RMAN> backup check logical validate datafile 1672;

RMAN> backup validate check logical database;

RMAN> blockrecover corruption list;

RMAN> blockrecover datafile 1281 block 8759;

RMAN> blockrecover datafile 1281 block 8759 from tag 'TAG20100101T200011';

RMAN> list backup of datafile 1281;

RMAN> BLOCKRECOVER DATAFILE 172 block 59903 DATAFILE 1607 block 368273 DATAFILE 1630  block 406686 DATAFILE 1574 block 197819 DATAFILE 1753 block 304906 DATAFILE 1607 block 443063 DATAFILE 1729 block 952410;

RMAN> BLOCKRECOVER DATAFILE 172 block 59903 DATAFILE 1607 block 368273 DATAFILE 1630  block 406686 DATAFILE 1574 block 197819 DATAFILE 1753 block 304906 DATAFILE 1607 block 443063 DATAFILE 1729 block 952410 from tag 'TAG20100101T200011';

You can read below books to increase your knowledge on oracle database:

Expert Oracle Database Architecture: Techniques and Solutions for High Performance and Productivity

Oracle Database 19c DBA By Examples: Installation and Administration


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


Location: Singapore

Comments

Post a Comment

Please do not enter any spam link in comment Section suggestions are Always Appreciated. Thanks.. !

Popular posts from this blog

Restart Innodb MySQL Cluster after Complete outage(All node Down)

-
Image
Restart Innodb MySQL Cluster after Complete outage(All node Down) In DBA World, Their could be activity(like DC Migration) where all node of an MySQL Innodb cluster need to brought down. In MySQL Innodb cluster, We need to follow below steps in order to bring up cluster. 1) Start all node in the MySQL Innodb cluster. systemctl start myql 2) Try to connect cluster using mysqlsh and check status. 3) Strat cluster using complete outage command. Restart Cluster using below command: var cluster = dba.rebootClusterFromCompleteOutage(); root@mysqlhost01:~# mysqlsh --uri clusteradmin@mysqlhost01:3306 Please provide the password for 'clusteradmin@mysqlhost01:3306': ************************ Save password for 'clusteradmin@mysqlhost01:3306'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.17 Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of...
Read more

Install & Configure MySQL Router - MySQL InnoDB Cluster

-
Image
Install & Configure MySQL Router -  MySQL InnoDB Cluster MySQL Router is one of the important component of MySQL InnoDB Cluster as it's used by applications to connect to the MySQL DBs running on cluster.  In this blog, We will show How we can install and configure MySQL Router either on application server or on dedicated server for MySQL Router. Below are the step to install and configure MySQL Router: 1) Install MySQL Router on the Server(In our case , We are restoring MySQL Router on the ubuntu server). Run command as root user : apt-get install MySQL-router 2) Bootstrap or Configure MySQL Router mysqlrouter --bootstrap clusteradmin@mysqlhost01:3306 --user=mysqlrouter In Case of any issue in configuring MySQL Router ,We can use -force option of the bootstrap. mysqlrouter --bootstrap clusteradmin@n4ldbdev5030:3306 --directory=/home/myrouter --user=mysqlrouter --force 3) Test Connectivity via MySQL Router. mysqlsh --uri clusteradmin@localhost:6446 you can learn more ...
Read more