What all are the recoverable points of failure in an Oracle database?

Question

I am testing a product against an Oracle database for failure scenarios. The product talks to Oracle through a session and maintains a few tables. I want to make sure that whenever Oracle recovers from whatever errors it can recover, the product can recover too.

For this, I will be manually simulating failures and checking what happens. What are all the failure scenarios I should try?

Answer 1

Not really sure what you are asking for here.. If your database is backed up correctly, then there is no unrecoverable points of failure. However most "failures" will result in your session being terminated or at the least being thrown an error. In most cases you wont want to deal with specific errors, but rather simply rollback the transaction ( if possible ) and log the issue for inspection.

If you are testing a 3rd party application, and assuming your are on a standalone database, I'd just test it for session termination to see if it automatically reconnects itself, correctly cleans up the work it was doing, etc. You could also lockup its session I guess and see how it handles timeouts.

Sorry for the vague response, but the question is quite vague.

Answer 2

Your question is rather vague but I would test:

• Network disconnect between the application and Oracle. (Generally when Oracle and the application are on different servers.
• Database shutdown and restart. (Does the application automatically reconnect?. Can it recover in-process transactions?)
• Database deadlock. (Kill the application's transaction? Does it recover?)
• If the application does two-phase commits, does it recover if the database becomes unavailable during the commit phase?

I assume you will be running the database with transaction logging enabled. I would consider rolling the database back to a previous point in time unrecoverable.

Answer 3

Are you trying to test the database? Or the application?

Assuming that the Oracle database is configured correctly (i.e. it is in ARCHIVELOG mode, backups are done regularly, DataGuard is in place depending on your recovery requirements, etc.), by far the most common source of problems in a failure is that the application itself has not defined its transaction boundaries correctly. The classic scenario here is a banking application that wants to make a $50 payment from account A to account B debits A by $50 in one transaction and credits B that $50 in a separate transaction. Unless you happen to be able to test what happens when system fails after the first transaction commits and before the second transaction commits, you won't see that the transaction boundaries are incorrect and the application might inadvertently lose $50.