Can i recover transaction after instance crash in oracle 11g

Question

i updated data in my table before commit transaction i shut the database with shutdown abort when again start the database the data gone

how to recover uncommitted transaction in oracle 11g?

Answer 1

Below 5 point only comes in use while doing instance recovery in Oracle RAC.

1. All nodes available.

2. One or more RAC instances fail.

3. Node failure is detected by any one of the remaining instances.

4. Global Resource Directory(GRD) is reconfigured and distributed among surviving nodes.

5. The instance which first detected the failed instance, reads the failed instances redo logs to determine the logs which are needed to be recovered. The above task is done by the SMON process of the instance that detected failure.

6. Until this time database activity is frozen, The SMON issues recovery requests for all the blocks that are needed for recovery. Once all the blocks are available, the other blocks which are not needed for recovery are available for normal processing.

Answer 2

There are two possible ways of doing that (besides some workarounds):

Cache Recovery To solve this dilemma, two separate steps are generally used by Oracle Database for a successful recovery of a system failure: rolling forward with the redo log (cache recovery) and rolling back with the rollback or undo segments (transaction recovery).

The online redo log is a set of operating system files that record all changes made to any database block, including data, index, and rollback segments, whether the changes are committed or uncommitted. All changes to Oracle Database blocks are recorded in the online redo log.

The first step of recovery from an instance or media failure is called cache recovery or rolling forward, and involves reapplying all of the changes recorded in the redo log to the datafiles. Because rollback data is also recorded in the redo log, rolling forward also regenerates the corresponding rollback segments.

Rolling forward proceeds through as many redo log files as necessary to bring the database forward in time. Rolling forward usually includes online redo log files (instance recovery or media recovery) and could include archived redo log files (media recovery only).

After rolling forward, the data blocks contain all committed changes. They could also contain uncommitted changes that were either saved to the datafiles before the failure, or were recorded in the redo log and introduced during cache recovery.

Transaction Recovery After the roll forward, any changes that were not committed must be undone. Oracle Database applies undo blocks to roll back uncommitted changes in data blocks that were either written before the failure or introduced by redo application during cache recovery. This process is called rolling back or transaction recovery. Figure 12-2 illustrates rolling forward and rolling back, the two steps necessary to recover from any type of system failure.

Figure 12-2 Basic Recovery Steps: Rolling Forward and Rolling Back

Oracle Database can roll back multiple transactions simultaneously as needed. All transactions that were active at the time of failure are marked as terminated. Instead of waiting for SMON to roll back terminated transactions, new transactions can recover blocking transactions themselves to get the row locks they need.

Source link here.

A small addition, to shed some light on the case:

Oracle performs crash recovery and instance recovery automatically after an instance failure. In the case of media failure, a database administrator (DBA) must initiate a recovery operation. Recovering a backup involves two distinct operations: rolling the backup forward to a more recent time by applying redo data, and rolling back all changes made in uncommitted transactions to their original state. In general, recovery refers to the various operations involved in restoring, rolling forward, and rolling back a backup. Backup and recovery refers to the various strategies and operations involved in protecting the database against data loss and reconstructing the database should a loss occur.

In brief, you can not recover the updated data, as it should be rolled back, in order to preserve the Database consistency. Have in mind that transactions are atomic, so they should be either COMMITTED or ROLLED BACK. Since the session that initiated it is now killed(stopped), no one can COMMIT it - thus the SMON does a ROLLBACK.

Answer 3

Uncommitted transactions will be rolled-back one the instance starts after the crash.