1

My team is planning a very large set of updates to our apps soon, including some hefty DB updates (Oracle 11gR2). As I was writing scripts that would revert all the DB updates (as a roll back contingency) and researching potential Oracle features, I came across this Oracle documentation. I see that flashbacks use "flashback logs" to restore the DB to a specific state. I also see that the restore points use the system change number to bookmark the DB. \

This SO questions says flashback will "return a table to the state it was in 10 minutes ago" but does that mean the data will be reverted too? (we have lots of reference tables as well)

Would either of these Oracle features be useful to undo our DB updates while maintaining the integrity of our production data? It's unclear to me what the two features do in practice and how they are different.

Community
  • 1
  • 1
dev_feed
  • 689
  • 2
  • 7
  • 25

2 Answers2

1

The main difference is that flashback rolls back changes including the changes made by others in the whole table or database to any point of time in the past within the range of flashback setting. To roll back to restored points will only rollback what you change in your transaction, and changes by others won't be affected.

Jian Huang
  • 1,155
  • 7
  • 17
  • So if DB access is only permitted to one connection during the transition, there's effectively no difference? – dev_feed Mar 12 '14 at 19:16
  • 1
    @dev_feed I tend to think so in a single-user dev environment. However, I strongly recommend you to involve your Oracle DBAs if you want to use the same approach in prod. – Jian Huang Mar 12 '14 at 19:40
  • It's starting to look like RMAN or export/import might be a better option. Would they be more useful in this situation? – dev_feed Mar 13 '14 at 10:39
  • @dev_feed There are too many backup and recovery scenarios based on different situations. It is very difficult to give a general answer without knowing the scope and details of your database project. Again talk to your DBAs. – Jian Huang Mar 13 '14 at 18:04
0

When you create a Guaranteed restore point it will keep enough flashback logs to flashback the database to the guaranteed restore point.

Guaranteed restore points must be dropped manually using DROP RESTORE POINT statement. Guaranteed restore points do not expire. If you do not do that flash recovery area will grow indefinitely until filesystem or Diskgroup becomes full...

Flashback database to restore point

fdominguez.bbdd
  • 31
  • 2