Oracle: Preventing Corruption Before it's Too Late: Part 1

Tuesday Sep 30th 2003 by Marin Komadina
Share:

Marin Komadina discusses Oracle data-block corruption, describing several features of Oracle that may be used at an early stage to detect database corruption.

Every DBA has a personal horror story about Oracle database corruption. A few DBAs, those that never taste this experience are missing the most challenging part of the DBA profession.

Those mind-jarring calls in the middle of the night to come and repair a database problem only to discover upon arrival that the last good backups was made 3 days ago, hardware has error and above all the project manager is screaming from his warm bed: " The database must be recovered by tomorrow morning!"

Have you ever experienced this? Well, if not, you don't know what you are missing.

Luckily, database corruptions are very rare, but when they happen, they can have a catastrophic effect on the whole company's business. The first occurrence of a database problem may not be detected until much later, after the data corruption has actually occurred. One can use several techniques to determine whether the database is corrupt, but the main thing is also to understand the nature of the corruption. In this article, I will discuss Oracle data-block corruption and will describe several features of Oracle that may be used at an early stage to detect database corruption.

Part 1

  • Oracle Block Structure
  • System Components and Corruption Type
  • Oracle Hard Corruption

Part 2

  • Oracle Soft Corruption
  • Oracle Bug Corruption
  • Database Recommendations
  • Oracle Hardware Assisted Resilient Data (HARD)
  • Conclusion

Oracle Block Structure

Oracle block is a main database element, composed of several operating system blocks. The data block size is defined during database creation with initialization parameter db_block_size. Assuming a standard UNIX configuration, with regular file system structure we have the following physical block structure:

By default, Oracle extent has 5 Oracle blocks and each Oracle block has several operating system blocks. On the above picture is shown an Oracle block, which has 5 operating system blocks. Every OS block has a standard structure consisting of a block header and footer and a portion with the actual data.

Every Oracle data block has an internal, binary format with a defined structure consisting of a fixed block header, block directory, ITL (transaction list), block free space and data portion. Inside the fixed block header is stored data block address (DBA). DBA is a 48 bit integer block address, used for checking block integrity. The block address has two parts: the file number and the relative block number.

The relative block number describes block positioning relative to the tablespace to which the block belongs and the absolute file number describes block positioning globally in the database (FILE# in V$DATAFILE). Two different blocks in the database might have the same relative number, but they always have a different absolute block number. Using dbms_utility package we can get a block's actual, absolute address using the block's relative address:

SQL> variable dba varchar2; 
SQL> exec :dba := dbms_utility.make_data_block_address(101,5); -> relative address, file number
SQL> print dba
10059  -> absolute block address

Each formatted block has a block header. Some blocks also have a footer in case that block is a split block. The block header and footer, together make the "Block Wrapper".

Inside the Oracle block, Oracle save rows of data from user database objects. Every row, saved inside the block has a unique, ROWID address. The ROWID address is an 18-digit number with the following format

object_number.relative_file_number.block_number.row_number.

System Components and Corruption Type

A modern computer system has many different system layers. Several of them interact in takeover and transport of the Oracle data block from memory to the disk.

Every new component expands the possibility of a new error. An Oracle database block has to pass several communication points on the way to the physical storage:

  • Oracle SGA and sessions' PGA (System RAM)
  • UNIX buffer cache (System RAM)
  • disk controller cache (on-board external)
  • Storage Area Network cache (SAN box)
  • Physical disk spindle

Oracle data is being transferred from the database buffers (Oracle SGA or sessions' PGA), to the UNIX buffer cache, and then using ASYNC or SYNC IO UNIX system calls and OS device drivers, over the disk controller to the hard disk. For a storage implementation with SAN (Storage Area Network), data is buffered in the SAN cache and transferred in the background to the physical device.

During Oracle block transport, a block can be corrupted due to the following:

  • System Memory (memory or paging problems, caching problems)
  • Disk Controller (Bad I/O hardware or firmware, caching problems )
  • Disk Device ( problem with device driver or mechanical problem)
  • Others ( operating system bugs, oracle bugs, disk repair utilities )

An Oracle block can be soft or hard corrupted.

Hard corruption can occur due to a hardware or software error, in the memory or on the disk. The block is not readable at all, or block content has no known Oracle block format. An Oracle block is soft corrupted if the block has a format similar to Oracle block format, but content does not fit the physical or logical Oracle database structure. .

Oracle Hard Corruption

Since Oracle hard corruption points to problems with the magnetic media, this corruption is also called physical or media error.

The first evidence of possible problems can be found in the machine's system log:

WARNING: /sbus@2,0/SUNW,soc@0,0/
SUNW,pln@b0000000,912cec/ssd@2,0 (ssd117):
Error for Command: read(10)     Error Level: Retryable
Requested Block: 34274544       Error Block: 34274544
Vendor: SEAGATE                 Serial Number: 03421655
Sense Key: Hardware Error

This error message indicates that something is going on with a disk device, and possibly we can expect some problem on the database block level.

Let's look in global, to see how different hardware components interact with Oracle block:

System Memory is the main machine memory from which is allocated the database SGA (System Global Area) memory area. SGA is allocated on database start up and is used as cache for all database operations. The second part of the Oracle memory model is the sessions' PGA, where sessions store their operational environment. Moving of the Oracle data blocks is under the control of the database writer process, which will interact with the operating system during block take over. Operating systems will immediately return a response to the database writer, and in the background continue block handling, all the way to the physical device. We are talking about standard UNIX file system implementation.

Faulty working memory modules, if detected by the OS memory detection mechanism, can cause database block corruptions. Corruption happens in the database memory, the operating system buffer cache or in the file system IO buffers.

Memory corruptions could snip to the disk device before the operating system detect memory parity error. Upon detection, the operating system will write warning messages in the system's log and start with defensive action.

Some famous media corruption errors:

ORA-00600: internal error code, arguments: [3374], [], [], [], [], [], [], []

If you get an ORA-00600 [3374], it means that you have encountered a corruption in the

memory. Shutting down and restarting the database instance could clear the problem. If not, you will need to call Oracle Support for the procedure on how to write corrupted block out to the disk.

Upon instance startup, on touching corrupted object, Oracle will raise ORA-01578 error, indicating media corruption.

ORA-00600: internal error code, arguments: [3398], [15727], [15742], [], [], [], [], []

ORA-00600 [3398] database error indicating that the database writer process has detected a corrupted block in the cache and will crash the instance.

ORA-00600: internal error code, arguments: [4147], [], [], [], [], [], [], []

ORA-00600 [4147] indicates memory corruption with rollback segment blocks (invalid SCN) most probably due to a lost write to the rollback segment.

ORA-00600: internal error code, arguments: [4193], [15727], [15742], [], [], [], [], []

ORA-600[4193] indicates corruption in the rollback segment, when the transaction table and the rollback block are out of sync.

ORA-00600: internal error code, arguments: [12700], [], [], [], [], [], [], []
Block Checking: DBA = 16794980, Block Type = KTB-managed data block 
kdbchk: bad row tab 0, slot 42   -> bad row data 
kdrchk: row is marked as both Last and Next continue 

This error indicates corruption in the index, table or the mapping between them. In principle, index ROWID is pointing to a non-existent row in the data block. Corruption of this type can be in the data or in the index block. Upon discovery, the whole block will be marked corrupt and we will not have normal access to data from the block. Marking a block as corrupted may break referential integrity constraints and the object free list may become inaccessible, depending upon the location of the corrupted block.

Disk Controller is a hardware device (SCSI, SSA, Raid...), equipped with an on board cache used to control communication between the physical disk drive and the operating system IO calls. A faulty controller or faulty firmware on the controller board can cause corruptions on this level.

For example, here is error message from a database instance that crashed because of the database writer error condition:

"DBWR failed to complete async write within 183 seconds".

Oracle was attempting to write data to the disk. In normal circumstances, ASYNC IO call will return immediately control to Oracle. However, when there is problem with hardware, the I/O request will timeout after 180 seconds, logging a message in the database alert log and database writer trace file. The next attempt to write data will be in 360 seconds, and if it fails again, the database writer will terminate the Oracle instance.

Another situation with disk controller can occur when the controller is working correctly, but a controller bottleneck caused system write errors to be logged.

Disk Device represents a physical, mechanical device used for storing data. Disk devices have limited MTBF (Mean time before failure) and we can expect them to malfunction sooner or later. Even more critical than mechanical problems with disk devices, are disk area corruption problems. With disk area corruption problems, it is not possible to reread previously saved data. These physical errors are handled very well by the underlying operating system. The operating system will detect and solve most of these problems regularly in the background.

A typical Oracle error, following corruption due to a problem with the disk device:

ORA-00600: Internal message code, arguments: [01578] [...] [...] [] [] [].

select count(*) from artist_test;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 7, block # 128239) -> 7 is relative file number
ORA-01110: data file 22: '/oracle/artist/artist01.dbf' -> 22 is absolute file number

Oracle message ORA-01578, indicates a media block corruption. Each time a SQL statement tries to access (read or write) the corrupted block, Oracle will signal an error.

ORA-01578 usually comes with the ORA-0110 error indicating the file name and absolute file number. Several occurrences of ORA-1578 errors, always with the same arguments, definitely points to a media error. When the ORA-1578 error arises with different arguments, we are dealing with some other system error, possibly a problem with memory, I/O or some sort of swap problem.

The operating system will try to repair the corrupted block. When it has succeed, the block is zeroed, preventing Oracle from identifying the data block content.

Others - Many different reasons can cause Oracle block corruptions. For example, different operating system bugs or the usage of some disk repair utilities.

Conclusion:

Hard disk and memory media errors are the types of system errors that occur most often. A good backup strategy should be enough for a database administrator to keep the situation under control. These situations offer a good opportunity for a DBA to impress others, proving his skills and making a fast, efficient database recovery.

» See All Articles by Columnist Marin Komadina

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved