Tuesday, August 26, 2014

Configuring the RMAN Catalog Server

Concept behind RMAN Catalog is a centralized repository to maintain and keep all the Database Backup information taken by RMAN. By default whenever any RMAN Backup runs for any particular database then its relevant information will get stored into Control file but control file keeps this information for last 7 days only (default which can be changed). So in order to keep the backup information for long time for multiple databases, oracle has introduced a centralized repository called as Catalog recovery. Catalog recovery is simply a schema into a separate database which stores all the backup information for every database those are registered into that.

Lets see how to create the recovery catalog and register a database to keep the backup information into it.

Server Details:
----------------
Catalog Database : DCCAT
Target Database  : DCPROC

On catalog Server :

1. Create a tablespace which is going to hold all the catalog information into its datafile.

SQL> create tablespace DCCAT datafile ‘/oracle/dbawork/DCCAT/DCCAT01.dbf’
2 Size 30M autoextend on next 30M
3 extent management local
4 segment space management auto uniform size 64K;

Tablespace created.

2. Create a catalog user and assign the default tablespace which is created above.

SQL> create user DCCAT    
2 identified by DCCAT
3 default tablespace DCCAT
4 quota unlimited on DCCAT;

User created.

3. Provide necessary grants to catalog User which inherits all the views to manage the Recovery catalog.

SQL> grant connect, resource, recovery_catalog_owner to DCCAT;

Grant succeeded.

SQL> select privilege from dba_sys_privs where grantee = 'RECOVERY_CATALOG_OWNER';

PRIVILEGE
----------------------------------------
CREATE SYNONYM
CREATE CLUSTER
ALTER SESSION
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE SESSION
CREATE TYPE
CREATE VIEW
CREATE TRIGGER

11 rows selected.

The role RECOVERY_CATALOG_OWNER has all of the privileges need to query and maintain the recovery catalog.

4. Create Catalog:

On target Server Server:
------------------------

[oracle@olx785 ~]$ export ORACLE_SID=DCPROC
[oracle@olx785 ~]$ rman catalog DCCAT/DCCAT@DCCAT

Or

RMAN> connect catalog DCCAT/DCCAT@DCCAT;

Recovery Manager: Release 11.2.0.3.0 – Production on Thu Nov 14 20:18:51 2013

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

connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

5. Registered database with RMAN

RMAN> connect target /

Or

[oracle@olx785 ~]$ rman target sys/oracle@DCPROC catalog DCCAT/DCCAT@DCCAT

connected to target database: DCPROC (DBID=1012901700)

/*if it shows RMAN-06004 Error: target database not connected then proceed to fire next step i.e. “RMAN> register database;”*/

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

6. Check Registered Database:

SYS> select DBID, NAME from DCCAT.RC_DATABASE order by 2;

7. Backup Status:

SYS> select DB_ID,TO_CHAR (start_time, ‘dd-mon-yyyy hh24:mi:ss’) start_time ,TO_CHAR (COMPLETION_TIME, ‘dd-mon-yyyy hh24:mi:ss’) COMPLETION_TIME,STATUS from DCCAT.RC_BACKUP_SET;

DB Creation Using Silent Mode of DBCA

Oracle Provides lots of alternatives to create database among which there is a very popular and handy tool called database Configuration Assistant (DBCA).DBCA can run in either GUI or silent mode and most of the times people prefer to use GUI mode but many Times there are requirement to create database but without using GUI.

So here We are going to Create Database using Database Configuration Assistant but in silent mode i.e. without using GUI of Database Assistant tool. The keyword “dbca -silent” invokes the binary of dbca from bin directory in silent mode.

Prerequisites:

1. Check oratab file whether entry/name of the database you are creating is already existing or not because if at all “xyz” database is not running but if “xyz” was running long back and its entry is still there in oratab though its been deleted from server then your database creation operation will get failed.

2. Create all the required directories(viz. data directory)

Implementation :

1. Create a file and write whole dbca command which will run later. here I have used vi editor to create it manually.

[oracle@neeraj]$ vi dbca_KYTE.sh
[oracle@neeraj]$ chmod 775 dbca_KYTE.sh
[oracle@neeraj]$ cat dbca_KYTE.sh
dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName KYTE -sid KYTE -sysPassword earth01 -systemPassword earth01 -emConfiguration NONE -datafileDestination /oracle/dbawork/KYTE/oradata -recoveryAreaDestination /oracle/dbawork/KYTE/oradata -storageType FS -datafileJarLocation $ORACLE_HOME/assistants/dbca/templates -characterSet AL32UTF8 -nationalCharacterSet UTF8 -totalMemory 2096 -registerWithDirService false -obfuscatedPasswords false -sampleSchema false -oratabLocation /etc/oratab

here we logged into server “neeraj″ and using vi editor created file called ‘dbca_KYTE.sh’ where mentioned whole command to perform database creation operation and provided necessary permission. All Directories mentioned in the command has already been created.

2. Now we can run the file ‘dbca_KYTE.sh’ to create database automatically in the backend but it will show the result in the foreground and simultaneously write the log into a logfile. Below is the demonstration:

[oracle@neeraj]$ ./dbca_KYTE.sh
Copying database files
1% complete
3% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/KYTE/KYTE.log” for further details.

3. If you open the above log file(/u01/app/oracle/cfgtoollogs/dbca/KYTE/KYTE.log) you will see the result the as follows :

[oracle@neeraj]$ cat /u01/app/oracle/cfgtoollogs/dbca/KYTE/KYTE.log
Copying database files
DBCA_PROGRESS : 1%
DBCA_PROGRESS : 3%
DBCA_PROGRESS : 37%
Creating and starting Oracle instance
DBCA_PROGRESS : 40%
DBCA_PROGRESS : 45%
DBCA_PROGRESS : 50%
DBCA_PROGRESS : 55%
DBCA_PROGRESS : 56%
DBCA_PROGRESS : 60%
DBCA_PROGRESS : 62%
Completing Database Creation
DBCA_PROGRESS : 66%
DBCA_PROGRESS : 70%
DBCA_PROGRESS : 73%
DBCA_PROGRESS : 85%
DBCA_PROGRESS : 96%
DBCA_PROGRESS : 100%
Database creation complete. For details check the logfiles at:
/u01/app/oracle/cfgtoollogs/dbca/KYTE.
Database Information:
Global Database Name:KYTE
System Identifier(SID):KYTE
[oracle@neeraj]$

4. Now lets see database status as follows :

[oracle@neeraj]$ . oraenv
ORACLE_SID = [DRPROC] ? DCPROC
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@neeraj]$ sqlplus “/as sysdba”

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 27 20:20:16 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the OLAP, Data Mining and Real Application Testing options

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
------------- ------
DCPROC          OPEN

SQL>

Here we have successfully created database called “DCPROC” :)

Saturday, August 23, 2014

Recover Corrupted datafile on standby

In this post, I will demonstrate how we can recover a lost/corrupted/inaccessible datafile on standby from primary.

Overview:

1.Simulate loss of a datafile on standby database by renaming it.
2.Restart standby database – Stops at mount stage as datafile is inaccessible.
3.Check that redo apply to standby is stopped.
4.Connect to primary database as target and standby as auxiliary.
5.Take backup of the affected datafile from primary so that backup file is created on standby.
6.Recover standby database and open it.
7.check that redo apply has been restarted on standby and configuration is successful again.

Implementation:

Step 1

Find out names of datafiles on standby

SYS> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dg02/system01.dbf
/u01/app/oracle/oradata/dg02/sysaux01.dbf
/u01/app/oracle/oradata/dg02/undotbs01.dbf
/u01/app/oracle/oradata/dg02/users01.dbf
/u01/app/oracle/oradata/dg02/example01.dbf

To simulate loss of the datafile of example tablespace, rename it

SYS>ho mv /u01/app/oracle/oradata/dg02/example01.dbf /u01/app/oracle/oradata/dg02/example.dbf

Step 2

Restart standby database

Stops at mount stage due to missing datafile

SYS> startup force;
ORACLE instance started.

Total System Global Area  146472960 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/dg02/system01.dbf'

Step 3:

Switch logs on primary and verify that redo apply has stopped on standby

SYS> alter system switch logfile;

SYS> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

Step 4:

Using RMAN, connect to primary as target and standby as auxiliary

[oracle@node1 ~]$ . oraenv
ORACLE_SID = [DCPROC] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle

[oracle@node1 ~]$ rman target / auxiliary sys/oracle@DRPROC

connected to target database: DG01 (DBID=434142737)
connected to auxiliary database: DG01 (DBID=434142737, not open)

Step 5:

RMAN>
– Try to take backupset type of backup of example tablespace on primary so that backup file is created on standby

— Fails as only image copies can be transported over network using RMAN

RMAN>backup tablespace example auxiliary format '/u01/app/oracle/oradata/dg02/example01.dbf';

Starting backup at 26-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 12/26/2013 14:37:04
RMAN-06955: Network copies are only supported for image copies.

– Take image copy backup of example tablespace on primary so that backup file is created on standby

RMAN> backup as copy tablespace example auxiliary format
2> '/u01/app/oracle/oradata/dg02/example01.dbf';

Starting backup at 26-DEC-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/dg01/example01.dbf
output file name=/u01/app/oracle/oradata/dg02/example01.dbf tag=TAG20131226T143727
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 26-DEC-13

– check that image copy has been created on standby

SYS>ho ls /u01/app/oracle/oradata/dg02/example01.dbf
/u01/app/oracle/oradata/dg02/example01.dbf

Step 6:

Recover standby database and open it

SYS> recover managed standby database disconnect;

SYS> alter database open;

Check that redo apply is resumed again and configuration is successful

SYS> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

Tuesday, August 19, 2014

Data Guard Physical Standby Missing File tips

Introduction – Physical and Logical Standby

A Physical standby database is an exact copy of the primary database. It is always kept in a managed recovery mode and is unusable as long as primary is up and functional. 

The prominent difference with a logical standby database is that the latter is not an exact replica of the primary database. A logical standby can be a subset or a superset of the primary and is a fully operational database used for reporting etc. Unlike a physical standby, tables in logical standby can be queries also. 

In this particular scenario we are using a physical standby database where some datafiles are missing causing the managed recovery process to stop and hence forcing it to get out of sync with primary database.

Environment

Let us review the db environment which we are going to use for demonstrating this scenario.
1. Primary has 200 datafiles and standby has only 166 datafiles
2. Primary is a 3 node cluster and Standby is a 2 node cluster.
3. The DB name is DCPROC
4. MRP on standby is not running

Problem and Symptoms

Here is a detailed description of the actual problem and symptoms/indications which helped us choose the appropriate corrective measures.

1. On discovering that physical standby is out of sync, when we tried to start the MRP on standby, it reported the following error in alert log:

Errors in file /u01/app/oracle/admin/mydb/bdump/mydb1_mrp0_21189.trc
ORA-01111: name for data file 167 is unknown - rename to correct file
"ORA-01110: data file 167: '/u01/app/oracle/product/9.2.0/dbs/UNNAMED00167'
ORA-01157: cannot identify/lock data file 167 - see DBWR trace file
ORA-01111: name for data file 167 is unknown - rename to correct file
ORA-01110: data file 167: '/u01/app/oracle/product/9.2.0/dbs/UNNAMED00167'

2. On further investigation, standby’s alert log also shows following errors:

Tue Sep 9 04:05:03 2008
Media Recovery Log /u03/oradata/mydb/arc_backup/mydb_2_2173.arc
Media Recovery Log /u03/oradata/mydb/arc_backup/mydb_1_1896.arc
WARNING: File being created with same name as in Primary
Existing file may be overwritten
File #167 added to control file as 'UNNAMED00167'. Originally created as:
'/u07/oradata/mydb/myfile_1.dbf'
Recovery was unable to create the file as: '/u07/oradata/mydb/myfile_1.dbf'
MRP0: Background Media Recovery terminated with error 1274
Tue Sep 9 04:05:06 2008
Errors in file /u01/app/oracle/admin/mydb/bdump/mydb1_mrp0_7175.trc:
ORA-01274: cannot add datafile '/u07/oradata/mydb/myfile_1.dbf' - file could not be created
ORA-01119: error in creating database file '/u07/oradata/mydb/myfile_1.dbf'
ORA-27054: Message 27054 not found; product=RDBMS; facility=ORA
Linux-x86_64 Error: 13: Permission denied

3. On checking the view v$archived_log, there were lot of log sequence# which were APPLIED=NO

4. Also note that there is no gap in the sequence#

What caused the missing datafile(s) condition on Standby?

Parameter db_file_name_convert was not set at standby database. So as long as the files were created on /u02 and /u03 on primary, there was no problem on the standby because standby had /u02 and /u03. But when file#167 was added at /u07 on primary (on Sep 9 04:05:03 2008), it could not map to a /u07 mount point on standby because /u07 does not exists on standby and db_file_name_convert was also not set. As indicated by the alert log, the file#167 was registered in the standby’s control file as “UNANMED00167” at the default location of $ORACLE_HOME/dbs but the file was not created physically on standby database.

Action Plan: How to resolve this

1. At the standby:

Please set the db_file_name_convert parameter at the Standby for the /u07 folder at the Primary to the corresponding folder at the Standby.

Since this parameter is a Static parameter, you need to bounce the Standby DB.

As step#1, you can do following instead of the above step:

At the standby:

Create /u07 soft link for /u02, to eliminate the bounce of standby db due to the addition of db_file_name_convert init.ora parameter

2. At the standby :

SQL> alter system set standby_file_management=manual;

3. At the Primary for the datafile 167 :

SQL> alter tablespace <tablespace name> begin backup;

Copy the Datafile from the Primary to Standby to the correct location.

SQL> Alter tablespace <tablespace name> end backup;

4. At the Standby:

SQL> alter database rename file '.......UNNAMED00167' to '< actual location of the datafile >';     

You can skip steps#3 and #4 and instead do following step after #2:

At the Standby:

SQL> ALTER DATABASE CREATE DATAFILE '< ....UNNAMED00167>' as '<datafile name with the correct path>';

To create the remaining datafiles at the Standby automatically:

SQL> alter system set standby_file_management=auto;

Start the MRP at the Standby

SQL> alter database recover managed standby database;

At standby database ensure the MRP is running as expected

SQL> select process, status, sequence# from v$managed_standby;

Word of Caution: Prevent this from happening again

Before adding datafiles on the primary, make sure:

1. The corresponding mount point exists on the standby
2. Or there should be an appropriate mapping between the primary’s and standby’s mount points using the parameter db_file_name_convert     
3. Or create a soft link on standby server with the same name as that of primary’s mount point if it does not exist on the standby.

Reference:

Sunday, August 3, 2014

Block Corruption in Oracle Database

During backup and recovery practice sessions, we often struggle to perform block recovery scenario. This is because we find it difficult to corrupt an Oracle block.

I performed this test on Oracle 10g Release 2 (10.2.0.1) on Windows XP and for the purpose of this test we need to keep our database in archivelog mode. In this article, I will discuss how to corrupt an Oracle data block, but before beginning this discussion, I would like to answer:

Why to corrupt an Oracle Block?

We will be corrupting an Oracle block in order to practice recovery procedures involved when one encounters a Block Corruption in a production environment. If a block gets corrupted in any of our production databases we will be in a position to rectify and correct the error instead of wandering for help.

This is purely for educational purpose and please do not practice this on any of your production/development/testing databases, rather create a new database for this purpose and practice it there.

For the purpose of this test, I have created a separate tablespace and a new schema.

SQL> create tablespace corrupt_ts datafile '/u02/mydb/data/corrupt01.dbf' size 10m;  

Tablespace created.  
 
SQL> create user test identified by test default tablespace corrupt_ts;  

User created.  
 
SQL> grant connect, resource to test; 

Grant succeeded.  
 
Create and populate test table with dummy data as shown:

SQL> conn test/test 

Connected. 
 
SQL> create table t1 as select rownum rno, object_name from all_objects 
  2  where object_name like 'AQ%'; 
 
Table created. 
 
SQL> select count(*) from t1; 
 
  COUNT(*) 
---------- 
        42 

Insert a record into this table which we will be corrupting:

SQL> insert into t1 values (99, 'LET ME CORRUPT'); 
 
1 row created. 
 
SQL> commit; 
 
Commit complete. 

Let us take RMAN full database backup before we corrupt the block.

RMAN> backup format '/u02/rman/fulldb_%U' database plus archivelog; 
 
Starting backup at 01-FEB-08 
current log archived 
piece handle=/u02/rman/fulldb_0LJ7LIML_1_1 tag=TAG20080201T234641 comment=NONE 
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:28 
Finished backup at 02-FEB-08 
 
Starting backup at 02-FEB-08 
current log archived 
using channel ORA_DISK_1 
channel ORA_DISK_1: starting archive log backupset 
channel ORA_DISK_1: specifying archive log(s) in backup set 
input archive log thread=1 sequence=105 recid=105 stamp=645581560 
channel ORA_DISK_1: starting piece 1 at 02-FEB-08 
channel ORA_DISK_1: finished piece 1 at 02-FEB-08 
piece handle==/u02/rman/fulldb_0MJ7LINS_1_1 tag=TAG20080202T001242 comment=NONE 
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04 
Finished backup at 02-FEB-08 
 
Take the tablespace offline so that we can make changes to the datafile. There are many freeware and shareware Hex Editors available in the market. I am using UltraEdit editor to make changes in our datafile.

SQL> alter tablespace corrupt_ts offline; 
 
Tablespace altered. 

In Windows

Open datafile “'c:\mydb\data\corrupt01.dbf” using UltraEdit (press “Ctrl+h” to toggle between Hex Mode). Search for our record entry “LET ME CORRUPT” in the file and changed “CORRUPT” to “NORRUPT” and save the file and close UltraEdit. I just changed “C” to “N”.

In Linux

For example I want to corrupt 13th block of mytbs01.dbf:

$ dd if=/dev/zero of=/u02/mydb/data/corrupt01.dbf bs=8k conv=notrunc seek=13 count=1;

1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.0186921 seconds, 438 kB/s

Bring back the tablespace to online mode.

SQL> alter tablespace corrupt_ts online; 
 
Tablespace altered. 

You may notice that Oracle doesn’t complain when it brings the datafile online because the file header wasn’t modified. Oracle will complain only when it tries to access the corrupt blocks. Let’s see what happens when we try to query table “T1”.

SQL> conn test/test 
Connected. 

SQL> select * from t1; 
 
       RNO OBJECT_NAME 
---------- ------------------------------ 
         1 AQ$_AGENT 
         2 AQ$_DEQUEUE_HISTORY 
          : 
          : 
 
        30 AQ$_JMS_NAMEARRAY 
ERROR: 
ORA-01578: ORACLE data block corrupted (file # 6, block # 13) 
ORA-01110: data file 6: '/u02/mydb/data/corrupt01.dbf ' 
 
30 rows selected. 

Whenever we get these kind of error messages, we need to check all the blocks

RMAN> validate check logical database;

Starting validate at 16-NOV-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=107 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u02/mydb/data/system01.dbf
input datafile file number=00002 name=/u02/mydb/data/sysaux01.dbf
input datafile file number=00003 name=/u02/mydb/data/undotbs01.dbf
input datafile file number=00004 name=/u02/mydb/data/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              17594        38400           277491
 File Name: /u02/mydb/data/system01.dbf
 Block Type Blocks Failing Blocks Processed
 ---------- -------------- ----------------
 Data       0              13854
 Index      0              4487
 Other      0              2465

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              20381        25600           277631
 File Name: /u02/mydb/data/sysaux01.dbf
 Block Type Blocks Failing Blocks Processed
 ---------- -------------- ----------------
 Data       0              869
 Index      0              957
 Other      0              3393

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              541          22784           277631
 File Name: /u02/mydb/data/undotbs01.dbf
 Block Type Blocks Failing Blocks Processed
 ---------- -------------- ----------------
 Data       0              0
 Index      0              0
 Other      0              22243

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    FAILED 0              1133         1280            271968
 File Name: /u02/mydb/data/corrupt01.dbf
 Block Type Blocks Failing Blocks Processed
 ---------- -------------- ----------------
 Data       0              10
 Index      0              0
 Other      1              137

validate found one or more corrupt blocks
See trace file /home/oracle/prima/diag/rdbms/prima/prima/trace/prima_ora_18316.trc for details
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2
Control File OK     0              612
Finished validate at 16-NOV-10

Query returns 30 records and then complains of block corruption in file 6. Block numbered 13 is being reported as corrupt. Let us see what all blocks are corrupt in “corruption01.dbf” datafile by running dbv utility.

Let’s verify corrupted block:

$ dbv file=/u02/mydb/data/corrupt01.dbf blocksize=8192
    
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Feb 4 00:00:11 2008 
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved. 
 
DBVERIFY - Verification starting : FILE = /u02/mydb/data/corrupt01.dbf 
Page 13 is marked corrupt 
Corrupt block relative dba: 0x0180000d (file 6, block 13) 
Bad check value found during dbv: 
Data in bad block: 
 type: 6 format: 2 rdba: 0x0180000d 
 last change scn: 0x0000.0039aa9f seq: 0x3 flg: 0x06 
 spare1: 0x0 spare2: 0x0 spare3: 0x0 
 consistency value in tail: 0xaa9f0603 
 check value in block header: 0x85b0 
 computed block checksum: 0x1b00 
 
DBVERIFY - Verification complete 
 
Total Pages Examined         : 1280 
Total Pages Processed (Data) : 4 
Total Pages Failing   (Data) : 0 
Total Pages Processed (Index): 0 
Total Pages Failing   (Index): 0 
Total Pages Processed (Other): 11 
Total Pages Processed (Seg)  : 0 
Total Pages Failing   (Seg)  : 0 
Total Pages Empty            : 1264 
Total Pages Marked Corrupt   : 1 
Total Pages Influx           : 0 
Highest block SCN            : 3779231 (0.3779231) 
C:\ora10g\BIN> 

This utility scans all the blocks in a given datafile and outputs the corrupt ones. In my case, I have one block marked as corrupt. Make a note of all the corrupt blocks as we need to recover them to previous state.

Start RMAN session and recover all the corrupt blocks. The beauty of RMAN is that it leaves the entire datafile online except the corrupted blocks and we need to recover only those corrupt blocks instead of entire datafile.

Method 1:
  
RMAN> blockrecover datafile 6 block 13; 
 
Starting blockrecover at 04-FEB-08 
using target database control file instead of recovery catalog 
allocated channel: ORA_DISK_1 
channel ORA_DISK_1: sid=44 devtype=DISK 
 
channel ORA_DISK_1: restoring block(s) 
channel ORA_DISK_1: specifying block(s) to restore from backup set 
restoring blocks of datafile 00006 
channel ORA_DISK_1: reading from backup piece C:\MYDB\RMAN\FULLDB_0KJ7LH72_1_1 
channel ORA_DISK_1: restored block(s) from backup piece 1 
piece handle=C:\MYDB\RMAN\FULLDB_0KJ7LH72_1_1 tag=TAG20080201T234641 
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:36 
 
starting media recovery 
 
archive log thread 1 sequence 105 is already on disk as file C:\MYDB\FRA\MYDB\ARCHIVELOG\2008_02_02\O1_MF_1_10 
5_3T72T48S_.ARC 
archive log thread 1 sequence 106 is already on disk as file C:\MYDB\FRA\MYDB\ARCHIVELOG\2008_02_03\O1_MF_1_10 
6_3TD97K0Z_.ARC 
media recovery complete, elapsed time: 00:00:35 
Finished blockrecover at 04-FEB-08 
  
RMAN reports success of block recovery command. Let us query the table again by logging in to SQL*Plus:
  
SQL> select * from t1; 
 
       RNO OBJECT_NAME 
---------- ------------------------------ 
         1 AQ$_AGENT 
         2 AQ$_DEQUEUE_HISTORY 
          : 
          : 
 
        41 AQ$_JMS_ARRAY_ERROR_INFO 
        42 AQ$_JMS_ARRAY_ERRORS 
        99 LET ME CORRUPT 
 
43 rows selected. 

the query runs successfully and our original record is restored.

Method 2:

The next 11g New Feature here is: It will take the block out of the Flashback Logs, if present there.

RMAN> blockrecover corruption list;

Starting recover at 16-NOV-10
using channel ORA_DISK_1
searching flashback logs for block images
finished flashback log search, restored 1 blocks

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

Finished recover at 16-NOV-10

During the whole process, i.e validate check logical database & blockrecover corruption list, the corrupt tablespace remains online and usable, except the table