Link to home
Start Free TrialLog in
Avatar of Chandan Pratihast
Chandan PratihastFlag for Japan

asked on

Can Flashback table work after restore also?

Hi Experts, one more question on Flashback. I have one small table with 100 rows only. It has updates on every batch run. And suppose a batch aborts we want to flashback this. So, enabled the row movement on this table.  And it helps to restore the data of this table. And it is very effective. But the question is can this flash back (row movement at table level) also after the entire DB restore?
Or on the DB restore it will erase the logs and cannot flashback before the restore time?

Thank you.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I've never tried but as long as it is a normal restore to a point in time after the need to flashback, I would assume the flashback should still work.

If you want to flashback to say 8:00AM and you restore to 9:00AM, you should still be able to flashback.

If you open the database with resetlogs, you probably cannot flashback to a point before that time.

Again, never tried.  Might be an interesting experiment.  If you absolutely need to know:  I would set up a sample database and try it.
Avatar of Chandan Pratihast

ASKER

Thank you for reply, I will also try and let you all know.
Hi, I have tried and it seems after the restore the flashback table is not able to retrieve.
What I am trying "alter table <MYTABLE> ENABLE ROW MOVEMENT;" to try with one table.
Now is 11:00 AM 8-Aug. Restore done at 8:00 AM 8-Aug (today). Then flashback till 8-Aug 9:00AM is fine. But flashback 8-Aug 6:00AM gives below message:
ORA-01555: snapshot too old: rollback segment number 14 with name "_SYSSMU14_2473030277$" too small
flashback to 8-Aug 9:00 AM gives the data.

I think the retention period duration controlled by the below parameter (for row movement also?) am I right?
NAME                          TYPE    VALUE
----------------------------- ------- -----
db_flashback_retention_target integer 4320  

Thank you.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi

I set the undo_retention to 24*3600 but the flashback does not show data beyond the restore time. Thanks anyway.