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” :)

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#;

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:

http://www.dba-oracle.com/t_data_guard_physical_standby_missing_file_tips.html

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