Home » RDBMS Server » Backup & Recovery » Block recovery using logical backup
Block recovery using logical backup [message #325853] Mon, 09 June 2008 04:18 Go to next message
srini_thiru
Messages: 133
Registered: May 2008
Senior Member

Hi to all,

We are using oracle 9i database. last night One data file got corrupted. When I look into the v$database_block_corruption, I found that 5 blocks are corrupted. And there is no backup of data file. We have only logical backup that too taken after the block corruption. Any other options available to bring the data file to normal mode..

This is the sample output of v$database_block_corruption

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE

19 494215 1 0 FRACTURED

Like this there are four more blocks corrupted

our database is running in archive log mode. how can i recover the datafile. How to view which archive file belongs to which datafile.


thanks in advance,
Seenu

Report message to a moderator

Re: Block recovery using logical backup [message #325855 is a reply to message #325853] Mon, 09 June 2008 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68649
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
A "logical" backup is NOT a backup.
We're just going to learn it, now take real backups.

Regards
Michel

Report message to a moderator

Re: Block recovery using logical backup [message #325859 is a reply to message #325853] Mon, 09 June 2008 04:46 Go to previous messageGo to next message
srini_thiru
Messages: 133
Registered: May 2008
Senior Member


Hi Michel,

What are the options available to recover the datafile. the database is open. but 5 blocks are corrupted. As it is a production database i can't drop the datafile..

Waiting for your reply..


thanks,

Seenu

Report message to a moderator

Re: Block recovery using logical backup [message #325870 is a reply to message #325859] Mon, 09 June 2008 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68649
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The options are only available if you have a RMAN backup.
You have not, you can't do it.

Regards
Michel

Report message to a moderator

Re: Block recovery using logical backup [message #325875 is a reply to message #325853] Mon, 09 June 2008 05:33 Go to previous messageGo to next message
srini_thiru
Messages: 133
Registered: May 2008
Senior Member


Whether can i recreate the datafile and apply all archive log i have. How to recover datafile by droping the exisiting one and recreating the new one

thanks,

Seenu

Report message to a moderator

Re: Block recovery using logical backup [message #325876 is a reply to message #325853] Mon, 09 June 2008 05:43 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
So simply drop those datafiles.

Report message to a moderator

Re: Block recovery using logical backup [message #325883 is a reply to message #325875] Mon, 09 June 2008 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68649
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can recover a file ONLY if you have a PHYSICAL backup not a logical one.
Do you understand now why "logical backup" is NOT a backup?

With a logical backup you can recover NOTHING.
The only thing you can do is to restore the data as they were at the time you logically backup them.

Regards
Michel

Report message to a moderator

To drop corrupted datafile [message #326100 is a reply to message #325853] Tue, 10 June 2008 03:43 Go to previous messageGo to next message
srini_thiru
Messages: 133
Registered: May 2008
Senior Member


Hi,

One of my database datafile got corrupted. I don't have a backup of the datafile.

When i try to open the database i can't open because of that corrupted datafile. i like to drop the datafile but when i try to drop it the response i got is database not open. Presently the database is in MOUNT STATE.

How can i make my database open...

Report message to a moderator

Re: To drop corrupted datafile [message #326117 is a reply to message #326100] Tue, 10 June 2008 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68649
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Drop the tablespace.

Regards
Michel

Report message to a moderator

Re: To drop corrupted datafile [message #326187 is a reply to message #326100] Tue, 10 June 2008 09:18 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
To drop the tablespace you need to open the database.
Follow simple steps;
1)startup mount
2)alter database datafile <locationofdatafile> offline drop;
3)alter database open;
4)drop tablespace <tablespace_name> including contents and datafiles;
5)Recreate the tablespace
6)backup database

Thats it I suppose.

Report message to a moderator

importing tables [message #326196 is a reply to message #325853] Tue, 10 June 2008 10:20 Go to previous messageGo to next message
srini_thiru
Messages: 133
Registered: May 2008
Senior Member


HI,

I am using oracle 10g release 2 version. In one of my datafile 3 blocks are corrupted. I have a logical backup of the database. there are 15 tables in that corrupted datafile.

if i drop these tables and recreate using logical backup whether the blocks will be recovered or not.

or else can i rename the tables and import the particular table from logical backup..


Thanks in Advance,

Seenu

Report message to a moderator

Re: importing tables [message #326197 is a reply to message #326196] Tue, 10 June 2008 10:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> In one of my datafile 3 blocks are corrupted.
At most 3 tables have a problem.
Which objects contain the bad blocks?

What exactly is a logical backup?

[Updated on: Tue, 10 June 2008 10:28] by Moderator

Report message to a moderator

Re: importing tables [message #326198 is a reply to message #326196] Tue, 10 June 2008 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68649
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The answer is the same to your previous topic on the subject yesterday:

NO

Regards
Michel

Report message to a moderator

Re: Block recovery using logical backup [message #326210 is a reply to message #325853] Tue, 10 June 2008 12:39 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
did you try what i posted?

Report message to a moderator

Re: Block recovery using logical backup [message #326216 is a reply to message #326210] Tue, 10 June 2008 12:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68649
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
did you try what i posted?

This will not help to the actual question: recover the data.

Regards
Michel

Report message to a moderator

Re: Block recovery using logical backup [message #326218 is a reply to message #325853] Tue, 10 June 2008 13:02 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
but it will surely help otherwise. Would it?

Report message to a moderator

Re: Block recovery using logical backup [message #326226 is a reply to message #326218] Tue, 10 June 2008 13:43 Go to previous message
Michel Cadot
Messages: 68649
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it would.

Regards
Michel

Report message to a moderator

Previous Topic: How can i make complete database backup in oracle 8i
Next Topic: RMAN complete restore -- noarchive log fails..
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri May 10 16:56:58 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.