We have an application which requires switching between two identical schemas with many tables on a daily basis. Flashback query is used to guarantee data consistency, to make sure we get 100% identical copy of the first schema as of some time. With sufficient undo space and 10gR2, is it safe to assume that no rows will ever be lost?
I agree it sounds wacky. That was my first reaction when I was approached by our dba’s after they discovered it. I don’t know much about the application and vendor claims it’s perfectly safe and valid if the source schema is quiesced. I guess they need to do some additional processing on a consistent copy of the schema.
there is two options here :
a) use serializable or read only isolation – the database would be consistent.
b) use flashback query themselves – mandate that you set undo_retention to “X” and then they can just flashback and get a read consistent version of the database back “X” units in time.
anything else does not really make sense. To synchronize the two scheme’s (using flashback) would mean a big bump and grind – AND – would prove that the data they need is flashback queryable – meaning, they didn’t need to copy it, it already exist.