Oracle DBA Tips and Techniques

By Arjun Raja

AUTOMATIC BLOCK REPAIR IN 11G RELEASE 2

Posted by Arjun Raja on December 29, 2009

SET UP ACTIVE DATAGUARD AND TEST AUTOMATIC BLOCK CORRUPTION REPAIR.

Automatic block repair allows corrupt blocks on the primary database or physical standby database to be automatically repaired, as soon as they are detected, by transferring good blocks from the other destination.

In addition, RECOVER BLOCK is enhanced to restore blocks from a physical standby database. The physical standby database must be in real-time query mode – Also called active dataguard.

1 – SETUP ACTIVE DATAGUARD –

Firstly ON STANDBY DATABASE –

alter database recover managed standby database cancel;

alter database open read only;

alter database recover managed standby database using current logfile disconnect;

Confirm that standby database is open –

SQL> select file_name from dba_data_files;

FILE_NAME

FILE_NAME
——————————————————————————–
/u02/oradata/bozo/users01.dbf
/u02/oradata/bozo/undotbs01.dbf
/u02/oradata/bozo/sysaux01.dbf
/u02/oradata/bozo/system01.dbf

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ ONLY WITH APPLY

SQL>

Try to create a table in the standby database –

SQL> create table test (col_1 number(10));
create table test (col_1 number(10))
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

Check for table test on STANDBY –

SQL> desc test ;
ERROR:
ORA-04043: object test does not exist

Check for table test on PRIMARY

SQL> desc test;
ERROR:
ORA-04043: object test does not exist

Create table test on PRIMARY –

SQL> create table test (col_1 number(10));

Table created.

SQL>

Now desc test on both primary and standby –

Primary and standby –

Desc test;

SQL> Desc test;
Name Null? Type
—————————————– ——– —————————-
COL_1 NUMBER(10)

SQL>

So while the standby database is open in READ ONLY mode, media recovery is still in progress.

2 – BLOCK CORRUPTION – AUTOMATIC MEDIA RECOVERY USING BLOCK FROM STANDBY SITE.

Simulate a block corruption –

Firstly check the file users01.dbf for any corruption –

cd /u02/oradata/arjun

[oracle@redhat346 arjun]$ dbv file=users01.dbf

DBVERIFY: Release 11.2.0.1.0 – Production on Wed Dec 23 11:03:47 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

DBVERIFY – Verification starting : FILE = /u02/oradata/arjun/users01.dbf

DBVERIFY – Verification complete

Total Pages Examined : 1280
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 127
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1153
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1303084 (0.1303084)
[oracle@redhat346 arjun]$

Run following command to corrupt datafile – /u02/oradata/arjun/users01.dbf

vi corrupt.sh

dd if=/dev/zero of=/u02/oradata/arjun/users01.dbf bs=8192 seek=139 count=2 conv=notrunc < backup validate tablespace users format ‘/u02/oradat/arjun/%U%s%d%t’;

Starting backup at 24-DEC-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 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=00005 name=/u02/oradata/arjun/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
5 FAILED 0 1134 1280 1374339
File Name: /u02/oradata/arjun/users01.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 11
Index 0 0
Other 2 135

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_17914.trc for details
Finished backup at 24-DEC-09

Backup validate proves block corruption.

Also can be confirmed via this view –

SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
5 139 2 0 ALL ZERO

SQL>

Now since this database is a primary database in a dataguard configuration with active dataguard in place, AUTO BMR( Block Media Recovery) will kick in on the primary database box and fetch a non-corrupt block from the standby database and restore and recover this block.

Check of alert log on PRIMARY database –

Thu Dec 24 10:30:52 2009
Hex dump of (file 5, block 139) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_17914.trc
Corrupt block relative dba: 0x0140008b (file 5, block 139)
Completely zero block found during validation
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=139, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Hex dump of (file 5, block 140) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_17914.trc
Corrupt block relative dba: 0x0140008c (file 5, block 140)
Completely zero block found during validation
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data
Reread of blocknum=140, file=/u02/oradata/arjun/arjun01.dbf. found same corrupt data

Hex dump of (file 5, block 139) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_19540.trc
Corrupt block relative dba: 0x0140008b (file 5, block 139)
Completely zero block found during multiblock buffer read
Reading datafile ‘/u02/oradata/arjun/arjun01.dbf’ for corruption at rdba: 0x0140008b (file 5, block 139)
Reread (file 5, block 139) found same corrupt data
Starting background process ABMR
Thu Dec 24 11:17:55 2009
ABMR started with pid=36, OS id=19550
Auto BMR service is active.
Requesting Auto BMR for (file# 5, block# 139)
Hex dump of (file 5, block 140) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_ora_19540.trc
Corrupt block relative dba: 0x0140008c (file 5, block 140)
Completely zero block found during multiblock buffer read
Reading datafile ‘/u02/oradata/arjun/arjun01.dbf’ for corruption at rdba: 0x0140008c (file 5, block 140)
Reread (file 5, block 140) found same corrupt data
Requesting Auto BMR for (file# 5, block# 140)
Waiting Auto BMR response for (file# 5, block# 139)
Auto BMR successful
Waiting Auto BMR response for (file# 5, block# 140)
Auto BMR response already received
Auto BMR successful

Now check for corrupt blocks –

cd /u02/oradata/arjun

dbv file=users01.dbf

INCASE THE DATAFILE HEADER ITSELF IS CORRUPT, THEN AUTOMATIC BLOCK MEDIA RECOVERY IS NOT POSSIBLE.

Starting background process ABMR
Wed Dec 23 11:17:08 2009
ABMR started with pid=31, OS id=5840
Auto BMR service is active.
Requesting Auto BMR for (file# 5, block# 3)
Waiting Auto BMR response for (file# 5, block# 3)
Wed Dec 23 11:17:09 2009
Read of datafile ‘/u02/oradata/arjun/arjun01.dbf’ (fno 5) header failed with ORA-01210
Hex dump of (file 5, block 1) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_bmr0_5842.trc
Corrupt block relative dba: 0x01400001 (file 5, block 1)
Completely zero block found during datafile header read
Rereading datafile 5 header failed with ORA-01210
Hex dump of (file 5, block 1) in trace file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_bmr0_5842.trc
Corrupt block relative dba: 0x01400001 (file 5, block 1)
Completely zero block found during datafile header read
Errors in file /u01/app/oracle/diag/rdbms/arjun/arjun/trace/arjun_bmr0_5842.trc:
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: ‘/u02/oradata/arjun/users01.dbf’
ORA-01210: data file header is media corrupt
Auto BMR failed for (file# 5, block# 3); error=Invalid file header
Wed Dec 23 11:17:10 2009
Corrupt Block Found
TSN = 6, TSNAME = ARJUN
RFN = 5, BLK = 3, RDBA = 20971523
OBJN = 4, OBJD = -1, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =
Wed Dec 23 11:17:10 2009
Checker run found 1 new persistent data failures

In this case – restoration of the datafile from a backup is required –

OFCOURSE DATABASE BLOCK CORRUPTIONS CAN BE RECOVERED USING RMAN – again RMAN gets the good blocks from the standby site incase active dataguard is running.

SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
5 139 2 0 ALL ZERO

SQL>

[oracle@redhat346 trace]$ rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Thu Dec 24 11:11:08 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ARJUN (DBID=3469830310)

RMAN> RECOVER CORRUPTION LIST;

RMAN> recover corruption list;

Starting recover at 24-DEC-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
finished standby search, restored 2 blocks

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 24-DEC-09

RMAN>

SQL > SQL> select * from v$database_block_corruption;

no rows selected

SQL> SQL>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: