Home > Oracle > Oracle Block Recovery – for new data files having no backup

Oracle Block Recovery – for new data files having no backup


This example demonstrates, how to recover a newly added data file or tablespace from block corruption before having a backup.  To do a block recovery, we need to have a RMAN backup or backup copy of data file.    As shown in the previous example, we can always restore the data file and recover from the block corruption, but needs OS level operations (backing file is OS level and renaming  the  corrupted file etc).

In the below example, I used a different approach with out touching OS level files.  Here are the steps.

I took the table TEST and gathered information required to do a block recovery.  Using ‘dd’ command, corrupted the block.

SQL> select count(*) from test;

  COUNT(*)
———-
         9
SQL> select distinct dbms_rowid.rowid_block_number(rowid) from test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
————————————
                                  20

SQL> select distinct dbms_rowid.ROWID_RELATIVE_FNO(rowid) from test;

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
————————————
                                  16

SQL> select file_name from dba_data_files where file_id=16;

FILE_NAME
——————————————————————————–
/oradata/ctgr/tbs01.dbf
$ dd of=/oradata/ctgr/tbs01.dbf bs=8192 seek=20 conv=notrunc count=1 if=/dev/zero
1+0 records in
1+0 records out

SQL> alter system flush buffer_cache;

System altered.
SQL> select count(*) from test;
select count(*) from test
                     *
ERROR at line 1:
ORA-08103: object no longer exists

 Now I know, the block 20 is corrupted in file 16.  In case if we do not know the  block number or file ID, we can fire a RMAN backup or DBV utility to find the FIle ID and Block #.

Now just to prove that I do not have a backup, I tried recovering the blcok using RMAN, but failed.

$ rman target /

Recovery Manager: Release 10.2.0.4.0 – Production on Tue Oct 19 11:31:51 2010

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

connected to target database: CTGR (DBID=1764603925)

RMAN> blockrecover datafile 16 block 20;

Starting blockrecover at 19-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=38 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 10/19/2010 11:32:06
RMAN-06026: some targets not found – aborting restore
RMAN-06023: no backup or copy of datafile 16 found to restore

As the files was created recently and all the archive log files are on the disk, I can create a new COPY using RMAN restore command to a different location.  First take the corrupted  file OFFLINE.

RMAN> sql ‘alter database datafile 16 offline ‘;

sql statement: alter database datafile 16 offline

RMAN> run {
set newname for datafile 16 to ‘/oradata/ctgr/tbs01_temp1.dbf’;
restore datafile 16;
}2> 3> 4>

executing command: SET NEWNAME

Starting restore at 19-OCT-10
using channel ORA_DISK_1

creating datafile fno=16 name=/oradata/ctgr/tbs01_temp1.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 19-OCT-10

 Now the file /oradata/ctgr/tbs01.dbf restored to a different file as /oradata/ctgr/tbs01_temp1.dbf.  You can change the location of  to any temporary directory.  Since we did not issue the SWITCH comamnd, the newly restored file will act as a RMAN COPY of the original file.

RMAN> list copy of datafile 16;
List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
——- —- – ————— ———- ————— —-
2       16   A 19-OCT-10       4487919    18-OCT-10       /oradata/ctgr/tbs01_temp1.dbf

 Now, we are good as we have a COPY of data file, and RMAN can do  block recovery.

RMAN> blockrecover datafile 16 block 20;

Starting blockrecover at 19-OCT-10
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy /oradata/ctgr/tbs01_temp1.dbf

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

Finished blockrecover at 19-OCT-10

 As expected, RMAN copied the “good block”  from the newly created file and recovered the block.  As we made the data file OFFLINE we need RECOVER and ONLINE the data file to complete the activity. 

RMAN> recover datafile 16;

Starting recover at 19-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=37 devtype=DISK

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

Finished recover at 19-OCT-10
RMAN> sql ‘alter database datafile 16 online ‘;

sql statement: alter database datafile 16 online
SQL> select count(*) from test;

  COUNT(*)
———-
         9

There you go, now you have the TEST table back!  One more way to recover the block, and even may be more ways!!!

Advertisements
Categories: Oracle Tags: , , ,
  1. Sreeraj
    October 11, 2011 at 2:33 am

    Thanks – Got a new method to solve..great job.

    I have a doubt here – You have restored,but i dont see any recovery here.,,as you have mentioned these files are recently created and have archive logs.

    Pls clear my doubt

  2. Sreeraj
    October 11, 2011 at 2:35 am

    Ok.. got it…. i didnt see the below recovery & online section @ first place.. perfect.. added to my favourites.. thanks Sir

  1. No trackbacks yet.

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: