Help - Search - Members - Calendar
Full Version: Recovery table after changes in table definitions
Oracle DBA Forums > Oracle > Oracle Forum
iam33
Hi and thank to all in advance!

Iīm new in this forum, so ...

I need to know how to recover a table which has change in the table definition. I explain my situation with an example:

1.- I drop a colum in the table

alter table test1 drop column c;

2.- I need to comeback in the time to recovery the before state, but not with a import.

In first time i thought to set a recovery point with:

CREATE RESTORE POINT uno;

And then (after the alter table) recovery the table to that point with:

FLASHBACK TABLE test1 TO RESTORE POINT uno;

but.....

ERROR at line 1:
ORA-01466: unable to read data - table definition has changed



Someone could to say me how to solve thar problem?

thousand Thanks!
andrew kerber
Do you not have an export of the table? Flashback table doesn't work if the definition has changed. You can flashback database or restore and recover.
iam33
QUOTE (andrew kerber @ Jul 23 2008, 01:57 PM) *
Do you not have an export of the table? Flashback table doesn't work if the definition has changed. You can flashback database or restore and recover.


thanks!

The problem is that the table is very "big" and the export is very slow to do,moreover I will to do it with more tables like that, so I ask other option...

when you say restore and recover you are referring to restore from simple backup?
burleson
>> the table is very "big" and the export is very slow to do,moreover I will to do it with more tables like that, so I ask other option...

So how about an RMAN backup? You can backup a gig per minute on a fast server.

Whati s it, exactly, that you are try to accomplish?
iam33
QUOTE (burleson @ Jul 23 2008, 04:52 PM) *
>> the table is very "big" and the export is very slow to do,moreover I will to do it with more tables like that, so I ask other option...

So how about an RMAN backup? You can backup a gig per minute on a fast server.

Whati s it, exactly, that you are try to accomplish?


thanks Donald!

The way you say is the same that I was going to do but I wanted to investigate if there was other way to do this, like using FLASHBACK or another.

Does RMAN allow to restore only one table (Remember: the table isnīt dropped)? How?



Thanks for all your help to all!
aussie_dba
"Does RMAN allow to restore only one table (Remember: the table isnīt dropped)? How?"

Just restore to a new instance and then copy the table over.
iam33
QUOTE (aussie_dba @ Jul 24 2008, 11:50 AM) *
"Does RMAN allow to restore only one table (Remember: the table isnīt dropped)? How?"

Just restore to a new instance and then copy the table over.


thanks aussie_dba!

I supoused that, but...

Iīm thinking there is a little black hole in the recover, because, I think that recovering a table which was altered in the definition itīs a comun situation and there would be another methods to recover it, wouldnīt it?

thanks!
SteveC
This is no different than recovering from user error. User performs DML and commits. Oops, didn't mean to do that. How do you recover the data (outside of flashback table since DDL changed)?
iam33
QUOTE (SteveC @ Jul 28 2008, 04:56 PM) *
This is no different than recovering from user error. User performs DML and commits. Oops, didn't mean to do that. How do you recover the data (outside of flashback table since DDL changed)?


Like I said before, there is a "black hole" about this theme, because I think there should be a recovery system that would allow recover a table easily to a previous state without need for the table is dropped.

...but it is only a thought myself...
SteveC
Pick a situation and stick with it. You've been given more than one answer in each case. What black hole are you talking about? There are specific option for specific scenarios. What is your question at this point?
iam33
QUOTE (SteveC @ Jul 31 2008, 10:49 AM) *
Pick a situation and stick with it. You've been given more than one answer in each case. What black hole are you talking about? There are specific option for specific scenarios. What is your question at this point?


How could I restore a table(the table has undergone changes definition) in a point of time?
SteveC
Once again:
import from an export taken prior
RMAN or other hot backup - tablespace point in time recovery
flashback database

This is basic stuff, covered well in the documentation.
iam33
erased by misunderstanding
SteveC
Sorry if you feel belittled when being told the nature of your problem is basic and covered in the documentation. Would you feel better if I had cut and pasted 20 pages of a pdf doc here?
iam33
erased by misunderstanding...
burleson
>> I thought this is a forum to resolve questions

You asked, and Steve answered, giveing you several options:

QUOTE
import from an export taken prior
RMAN or other hot backup - tablespace point in time recovery
flashback database


What's the problem, he answered correctly.

There is a difference between answering questions, and spoon-feeding people who, for whatever reason, are unwilling to read about the docs. Like Steve said, it would be silly to paste-in 30 pages from the documentation.

************************************************************************

>> people like you is what makes me think back to ask my problems in it

FYI, Steve Callan is one of the most respected Oracle authors in the world, and you are VERY LUCKY to get his assistance.

http://www.databasejournal.com/article.php/3099361

************************************************************************
>> How could I restore a table(the table has undergone changes definition) in a point of time?

Have you considered tracking changes with DDL triggers?

http://www.dba-oracle.com/t_ddl_triggers.htm
iam33
I would like to apologise publicly to Esteve for having misunderstood the words and and thank him for his invaluable assistance.

I also believe, as Donald says, that Steve is one of the best authors of Oracle's world.

Thanks and ... sorry Steve for my "misunderstood"! sad.gif
burleson
>> Steve is one of the best authors of Oracle's world.

Oh yes, Steve is one of my favorites!

I'm really looking forward to his new data warehousing book:

http://www.rampant-books.com/book_2009_data_warehousing.htm
iam33
I donīt know it, and the truth is that it seems a good book,doesnīt it?.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.