Recover Deleted data

[cwsstins]

I've got a process going in my dev database to:
a) copy data from devA schema to devB schema
b) delete data from devA

For the last week, the a) portion of the process has been failing, but the b) portion has succeeded. So I've lost about 1 week's worth of test data.

I've got the program fixed to prevent b) from proceeding if a) fails, but I'm wondering if there's a way to recover the data that have been deleted for each day.

 

[Turkbear]

Hi,
Use your backups...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[stefanhei]

If devA is running in archivelog-mode and you used dml (delete) and not ddl (truncate) to delete you may use logminer to recover the data.

Stefan

 

[cwsstins]

I talked to my DBA and logminer is not a viable option at this point. I was able to get some of the data back with backups.

What I really need at this point is to modify the sql script so that step b) will not execute if step a) has failed. How can I indicate that step a) has failed or succeeded?

This is the guts of the sql:

exec spA_transfer;
commit;

exec spB_delete;
commit;

 

[KenCunningham]

Not too sure, but I would be looking at creating a one column/one value table which could be written to by the first process to indicate success or otherwise, then have the second process check the value in the table and proceed or not dependent on what is found. This value would then be reset at the end of the second process.

Note that this is from a unix scripting perspective, so I'm not sure whether it's practical in your case. No doubt someone will be along soo to tell you whether it is, or whether another method is available.

I don't mind people who aren't what they seem. I just wish they'd make their mind up.

 

[carp]

I would start by reexamining my exception handling strategy. If a failure in one part is causing a problem like the one you describe, it should either be stopping and sending out an SOS to somebody, or, at the very least, setting a flag that the second phase checks before merrily deleting your data.
As for recovering your data - if you've recovered all you can from backups and you can't use logminer (and I would question your DBA VERY closely on this!), then you have everything you are going to get.