Oracle Crash Recovery By Mark Ramsay Oracle Crash
Oracle Crash Recovery By Mark Ramsay
Oracle Crash Recovery Before we start remember……… UNDO GENERATES REDO
Oracle Crash Recovery User A Buffer Cache Log Buffer 20 -20 -21 Redo Undo update curr_accnt set sort_code=’ 20 -20 -20’ where sort_code=’ 20 -20 -21’ Datafiles
Oracle Crash Recovery Buffer Cache User A Log Buffer 20 -20 -21 Redo SP Undo User A requests the server process to perform the update. The server process firsts checks to see if the block to be updated already exists in the buffer cache. Datafiles
Oracle Crash Recovery Buffer Cache User A Log Buffer 20 -20 -21 Redo SP Undo When the server process does not find the block in the buffer cache. It accesses the datafile to retrieve the block. Datafiles 20 -20 -20
Oracle Crash Recovery Buffer Cache User A Log Buffer 20 -20 -21 Redo 20 -20 -21 20 -20 -20 Data Block SP Undo The server process places the data block into the buffer cache. Next the server process gets an empty undo block…. Datafiles 20 -20 -21
Oracle Crash Recovery Buffer Cache User A Log Buffer 20 -20 -21 20 -20 -20 Data Block Redo 20 -20 -20 Undo Block SP Undo …and places the Undo block into the buffer cache. Datafiles
Oracle Crash Recovery Buffer Cache User A Log Buffer User A 20 -20 -21 Change Record 20 -20 -21 20 -20 -20 Data Block 20 -20 -20 Undo Block User A Undo Record Redo User A Change Record User A Undo Record SP Undo The server process first writes the change vectors for the data and undo blocks to the log buffer and then the redo log. Datafiles
Oracle Crash Recovery Buffer Cache User A Log Buffer User A 20 -20 -21 Change Record 20 -20 -21 20 -20 -20 Data Block 20 -20 -21 20 -20 -20 Undo Block User A Undo Record Redo User A Change Record User A Undo Record SP Undo Datafiles The before data i. e. column and row id, is then copied to the undo block….
Oracle Crash Recovery Buffer Cache User A 20 -20 -21 Change Record 20 -20 -20 Data Block 20 -20 -21 20 -20 -20 Undo Block …and the value in the data block is then updated to reflect the value in the update statement. In this case 20. User A then commits his update and a commit record is written to Redo. User A Undo Record User A Commit Redo User A Change Record User A Undo Record User A Commit SP Commit Log Buffer Undo Datafiles
Oracle Crash Recovery At this point it is worth remembering…. . The the log writer writes according to a very aggressive algorithm: Effectively in real time, and when you say commit, it IS real time ( your session hangs until the log buffer has been written).
Oracle Crash Recovery User A Buffer Cache Log Buffer 20 -20 -21 Redo Undo So we now have our two blocks in the buffer cache (Data and Undo) and our Redo records… Datafiles
Oracle Crash Recovery User A Buffer Cache Log Buffer 20 -20 -21 Redo User B Undo update acct_balance set balance=’ 2000’ where accnt_no’ 123456’ Datafiles
Oracle Crash Recovery Buffer Cache User A Log Buffer 20 -20 -21 Redo User B SP Undo User B requests the server process to perform the update. The server process firsts checks to see if the block to be updated already exists in the buffer cache. Datafiles
Oracle Crash Recovery Buffer Cache User A Log Buffer 20 -20 -21 Redo User B SP Undo When the server process does not find the block in the buffer cache. It accesses the datafile to retrieve the block. Datafiles 20 -20 -20
Oracle Crash Recovery Buffer Cache User A Log Buffer 20 -20 -21 Redo 1000 20 -20 -20 User B Data Block SP Undo The server process places the data block into the buffer cache. Next the server process gets an empty undo block…. Datafiles 20 -20 -21
Oracle Crash Recovery Buffer Cache User A Log Buffer User B 20 -20 -21 Change Record 1000 20 -20 -20 User B Data Block Undo Block User B Undo Record Redo User B Change Record User B Undo Record SP …and places the Undo block into the buffer cache. The server process then writes the change vectors that it is going to apply to the data and undo blocks, to the log buffer and the redo log. . Undo Datafiles
Oracle Crash Recovery Buffer Cache User A Log Buffer User B 20 -20 -21 Change Record 1000 20 -20 -20 User B Data Block Undo Block User B Undo Record Redo User B Change Record User B Undo Record SP Undo Datafiles The before data i. e. column and row id, is then copied to the undo block….
Oracle Crash Recovery Buffer Cache User A Log Buffer User B 20 -20 -21 Change Record 1000 20 -20 -20 User B Data Block Undo Block User B Undo Record Redo User B Change Record User B Undo Record SP Undo Datafiles The value in the data block is then updated to reflect the value in the update statement. In this case 2000.
Oracle Crash Recovery Buffer Cache User A Log Buffer User B 20 -20 -21 Change Record 1000 20 -20 -20 User B Data Block Undo Block User B Undo Record Redo User B Change Record User B Undo Record SP Undo User B, instead of issuing a commit, goes to lunch for several hours. Meanwhile, User A’s block is continuously updated. Datafiles
Oracle Crash Recovery Buffer Cache User A Log Buffer User B 20 -20 -21 Change Record 1000 20 -20 -20 User B Data Block Undo Block User B Undo Record Redo User B Change Record User B Undo Record SP DBWR starts to search the buffer cache for changed blocks which are no longer active. In this case, User B’s block. Not user A’s block. User A’s block may have changed, but it is still active. Undo Datafiles
Oracle Crash Recovery Buffer Cache User A Log Buffer User B 20 -20 -21 Change Record 1000 20 -20 -20 User B Undo Record User B Change Record Undo Block Data Block Redo User B Undo Record User B BWR SP Undo Having found User B’s block, DBWR writes the block to the datafiles and writes a block written record (BWR) to Redo. Datafiles 20000 So we now have uncommitted data in the datafiles!!
Oracle Crash Recovery At this point it is worth remembering…. . The database writer writes according to a very lazy algorithm, as little as possible as rarely as possible, which is why there is no correlation between committed/uncommitted changes, and datafile content.
Oracle Crash Recovery ORACLE CRASHES
Oracle Crash Recovery So what does Oracle need to do on start-up…. • It needs to perform crash recovery What is crash recovery…. • A rebuild of the SGA to the state it was in before the crash • A Rollback of uncommitted transactions
Oracle Crash Recovery Buffer Cache Log Buffer 20 -20 -21 Redo User B BWR Undo Oracle first reads through the REDO looking for BWR. Datafiles
Oracle Crash Recovery Buffer Cache Log Buffer 20 -20 -21 Redo User B BWR Undo Oracle knows it can ignore the data blocks for any BWRs as they have already been written to disk. In this case User Bs data block. Datafiles
Oracle Crash Recovery Buffer Cache Log Buffer 20 -20 -21 Redo Undo Oracle now re-reads the REDO so that it can start rebuilding the SGA. Datafiles
Oracle Crash Recovery Buffer Cache 20 -20 -20 Log Buffer 20 -20 -21 Redo User A Change Record Data Block Undo The first record Oracle comes across is User A’s change record. It rebuilds this block in the Buffer Cache. Datafiles
Oracle Crash Recovery Remember……… UNDO GENERATES REDO Well……………
Oracle Crash Recovery Buffer Cache 20 -20 -20 -21 20 -20 -20 Data Block Undo Block Log Buffer 20 -20 -21 Redo User A Undo Record Undo The next record Oracle comes across is User A’s undo record in the Redo log. It rebuilds this block in the Buffer Cache. Datafiles
Oracle Crash Recovery Buffer Cache 20 -20 -20 -21 20 -20 -20 Data Block Undo Block Log Buffer 20 -20 -21 Redo User A Commit Record Undo As the next record Oracle comes across is User A’s commit record. Oracle knows that this is a committed transaction. Datafiles
Oracle Crash Recovery Buffer Cache 20 -20 -20 -21 20 -20 -20 Data Block Undo Block Log Buffer 20 -20 -21 Redo Undo The next record would have been User B’s change record. However, we know Oracle will ignore this due to the BWR. Datafiles
Oracle Crash Recovery Buffer Cache 20 -20 -20 -21 20 -20 -20 Data Block Undo Block Log Buffer 20 -20 -21 1000 20 -20 -20 Redo User B’s Undo Record Undo Block Undo Therefore, Oracle moves onto the next record. User B’s undo record. Oracle rebuilds the block in the buffer cache. Datafiles
Oracle Crash Recovery Buffer Cache 20 -20 -20 -21 20 -20 -20 Data Block Undo Block Log Buffer 20 -20 -21 Redo 1000 20 -20 -20 Undo Block Undo Oracle has finished reading the Redo. (Remember there was no commit record for User B). The SGA is now back to how it was before the crash. Datafiles
Oracle Crash Recovery Buffer Cache Log Buffer 20 -20 -21 1000 20 -20 -20 Data Block Redo Undo Block Undo Oracle has one finally task to complete. The rollback of User B’s transaction. Datafiles
Oracle Crash Recovery Buffer Cache Log Buffer 20 -20 -21 1000 20 -20 -20 Data Block Redo Undo Block Undo It is at this stage Oracle opens the database, as rollback can occur with the database open. Datafiles
Oracle Crash Recovery Thanks to John Watson, an Oracle trainer, for explaining all this to me on an RMAN course.
Oracle Crash Recovery THE END
- Slides: 39