Tag: corruption

Corruption In Oracle Database

Posted on by Osama Mustafa in Database
There are two types of corruption in Oracle database physical and logical what is the difference between them,Database written to blocks if this write failed then Database Corruption happened Sometimes because I/O Problems, Power Problem … etc which cause no time for header to been updated, usually Oracle Corruption will not effecting on your work until you try to read this block. There are two types of Corruption:

1- Physical Corruption  (Media Corruption)

2- Logical Corruption (Soft Corruption) 

The Details can be Find in Alertlog
ORA-01578:ORACLE data block corrupted (file # string, block # string)


Physical Corruption :

this kind of corruption can be happened when I/O Problems, Memory Failure, Server Controller

Regarding to Oracle documentation the corruption could be happened by:

  • Bad header:the beginning of the block (cache header) is corrupt with invalid values.
  • Block is Fractured/Incomplete:Information from the block header does not match the block tail
  • Block checksum is invalid
  • Block is misplaced
you can DB_BLOCK_CHECKSUM init parameter are used to identify if the block was changed by something external to Oracle and after the block was last written by Oracle. You can check the Parameter documentation Here.
Logical Corruption:

this kind of corruption you will not find it alertlog only by DBVerify utility, also be noted if db_block_checking Parameter is enabled, it may produce the internal error ORA-600 [kddummy_blkchk]

So , the difference between both kind is clear , Logical Corruption is Header/Footer and database will try to read that block , In the physical Corruption Something prevent us to read that block.

How can i know i have Corruption:

1- RMAN
2- DBVerify

DBVerify :

DBVERIFY is an external command-line utility that performs a physical data structure integrity check on an offline database. It can be used against backup files and online files (or pieces of files). You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored or as a diagnostic aid when you have encountered data corruption problems.

Documentation is here.

RMAN

Rman could Detect both corruption logical/physical , check the below script that could check if the database contains corruption without even take backup:

RMAN> configure device type disk parallelism 4;
RMAN> backup validate check logical database;
OR
RMAN> run {
allocate channel diskl1 type disk;
allocate channel disk2 type disk;
allocate channel disk3 type disk;
allocate channel disk4 type disk;
backup validate check logical database;
}

The above script to check Database Validation.


Which Is Better :

  1. RMAN can be run with PARALLELISM option
  2. DB Verfiy Check Empty Block.
  3. Both Tools Can check for Rang Blocks.

RMAN:
VALIDATE DATAFILE 1 BLOCK …. to ……

DBV:
start=… end=….

Some Other Blogs Could Be useful :
1- Database Corruption
2-  Other Tools To Check Corruption

Thank you
Osama mustafa

 

Opening the database with corrupted redo log

Posted on by Osama Mustafa in Database
ORA-00333: redo log read error block 9233 count 2312

Opening the database with corrupted redo log can cause a loss of committed transactions, therefore, you need to do it at your own risk

Steps :

SQL> Startup Mount;
SQL> alter system set “_allow_resetlogs_corruption”=true scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 281018368 bytes
Fixed Size 779000 bytes
Variable Size 229383432 bytes
Database Buffers 50331648 bytes
Redo Buffers 524288 bytes
Database mounted.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel;

ORA-00280: change 101350984923848 for thread 1 is in sequence #2333

Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/home/oracle/oradata/rsdb/system01.dbf’

ORA-01112: media recovery not started

SQL> alter database open resetlogs;

SQL>shutdown immediate;
SQL>Startup;

You can Do this Steps When Data Its Not Important ,  Such As Test Evn , ….

Enjoy

Osama Mustafa

Repair Database Corrpution Using 11g New Tools

Posted on by Osama Mustafa in Database
As Database Administrator you Face Some Issue About The Database Corruption  But In 11g You have Some New Tool Called “data recovery advisor” .

With DRA you check the block corruptions and loss of data files and control files. The GUI feature also available from Enterprise manager.

Lets Start Using This Tool :

RMAN> BACKUP VALIDATE

Starting backup at 01-FEB-12

21 FAILED 0 169474 2048000 1046708826
File Name: /dbs1/orcl/system01.dbf
Block Type Blocks Failing Blocks Processed

 To Use This Tool Follow The Below Step :

RMAN> LIST FAILURE

List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
1041 HIGH OPEN 01-FEB-12 Datafile 21: ‘/dbs1/orcl/system.dbf ‘ contains one or more corrupt blocks

RMAN> ADVISE FAILURE;

List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
———- ——– ——— ————- ——-
1041 HIGH OPEN01-FEB-12 Datafile 21:”/dbs1/orcl/system.dbf ‘ contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=319 device type=DISK
analyzing automatic repair options complete

RMAN> Repair failure preview;
RMAN> REPAIR FAILURE;
RMAN> REPAIR FAILURE NO PROMPT;

Thank you
Osama Mustafa