1 / 77

Redo Logs and Recovery

Redo Logs and Recovery. John Hibbard Senior Principal Instructor Minneapolis MN john.hibbard@oracle.com Revised by Dr. Philip Cannata University of Texas, Austin, Texas cannata@cs.utexas.edu. Redo Buffer Operation. When user enters …. User #1. data file 2. undo file 3.

hillary-watts
Download Presentation

Redo Logs and Recovery

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Redo Logs and Recovery John Hibbard Senior Principal Instructor Minneapolis MN john.hibbard@oracle.com Revised by Dr. Philip Cannata University of Texas, Austin, Texas cannata@cs.utexas.edu

  2. Redo Buffer Operation When user enters… User #1 data file 2 undo file 3 data file 4 UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack database buffer cache

  3. Redo Buffer Operation When user enters… User #1 data file 2 undo file 3 data file 4 UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack The data block is retrieved from disk to the database buffer cache (unless it’salready there) 1234 5 database buffer cache

  4. Redo Buffer Operation When user enters… User #1 data file 2 undo file 3 data file 4 UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack The data block is retrieved from disk to the database buffer cache (unless it’salready there) 1234 5 U1 The undo block is then retrieved from disk to the database buffer cache (unless it’s already there) database buffer cache

  5. Redo Buffer Operation When user enters… User #1 data file 2 undo file 3 data file 4 UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack The data block is retrieved from disk to the database buffer cache (unless it’salready there) 1234 5 U1 The undo block is then retrieved from disk to the database buffer cache (unless it’s already there) 5 The undo data iscreated showing the ‘before image’ database buffer cache

  6. Redo Buffer Operation When user enters… Redo Buffer User #1 data file 2 undo file 3 data file 4 Tran File Block Row Column Value id UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack T1 3 12 - - 5 The data block is retrieved from disk to the database buffer cache (unless it’salready there) 1234 5 U1 The udo block is then retrieved from disk to the database buffer cache (unless it’s already there) 5 The undo data iscreated showing the ‘before image’ And an entry recording this change is made in the REDO buffer database buffer cache

  7. Redo Buffer Operation When user enters… Redo Buffer User #1 data file 2 undo file 3 data file 4 Tran File Block Row Column Value id UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack T1 3 12 - - 5 1234 5 Information is also recorded identifying the location of the data segment being changed. U1 5 database buffer cache

  8. Redo Buffer Operation When user enters… Redo Buffer User #1 data file 2 undo file 3 data file 4 Tran File Block Row Column Value id UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack T1 3 12 - - 5 1234 5 x 10 Then the data block ischanged... U1 5 database buffer cache

  9. Redo Buffer Operation When user enters… Redo Buffer User #1 data file 2 undo file 3 data file 4 Tran File Block Row Column Value id UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 1234 5 x 10 Then the data block ischanged... U1 And an entry recording this change is made in the REDO buffer 5 database buffer cache

  10. Redo Buffer Operation When user enters… Redo Buffer User #1 data file 2 undo file 3 data file 4 Tran File Block Row Column Value id UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 1234 5 x 10 Then the data block ischanged... U1 And an entry recording this change is made in the REDO buffer 5 database buffer cache

  11. Transaction identifier • Column address • Value of the column that changed Redo Buffer Operation Redo Buffer data file 2 undo file 3 data file 4 Tran File Block Row Column Value id 1234 5 XYZ Zack T1 3 12 - - 5 Keypoint T1 2 123 41 6 10 Only the very basic data is stored in the redo. The main redo items are:

  12. Redo Buffer Operation When user enters… Redo Buffer User #1 data file 2 undo file 3 data file 4 Tran File Block Row Column Value id UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 This continues for allchanges 1234 5 x 10 User # 2 U1 5 UPDATE prod SET large_col...WHERE id = XYZ database buffer cache

  13. Redo Buffer Operation When user enters… Redo Buffer User #1 data file 2 undo file 3 data file 4 Tran File Block Row Column Value id UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 This continues for allchanges 1234 5 x 10 User # 2 U1 5 UPDATE prod SET large_col...WHERE id = XYZ XYZ Zack database buffer cache

  14. Redo Buffer Operation When user enters… Redo Buffer User #1 data file 2 undo file 3 data file 4 Tran File Block Row Column Value id UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack This continues for allchanges 1234 5 x 10 User # 2 U1 5 UPDATE prod SET large_col...WHERE id = XYZ XYZ U2 Zack Zack database buffer cache

  15. Redo Buffer Operation When user enters… Redo Buffer User #1 data file 2 undo file 3 data file 4 Tran File Block Row Column Value id UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack This continues for allchanges 1234 5 T87 4 89 28 22 &&& x 10 User # 2 U1 5 UPDATE prod SET large_col...WHERE id = XYZ XYZ U2 xx &&&& Zack Zack database buffer cache

  16. Redo Buffer Operation When user enters… Redo Buffer User #1 data file 2 undo file 3 data file 4 Tran File Block Row Column Value id UPDATE emp SET sal = 10 WHERE id = 1234 1234 5 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack This continues for allchanges 1234 5 T87 4 89 28 22 &&&& x 20 x 10 T1 3 12 - - 10 User # 2 U1 T1 2 123 41 6 20 5 UPDATE prod SET large_col...WHERE id = XYZ 10 User #1 XYZ U2 UPDATE emp SET sal = 20 WHERE id =1234 xx &&&& Zack Zack database buffer cache

  17. Redo Buffer Operation Redo Buffer Then a user commits data file 2 undo file 3 data file 4 Tran File Block Row Column Value id User #1 1234 5 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 commit; T87 3 65 - - Zack 1234 5 T87 4 89 28 22 &&&& x 20 x 10 T1 3 12 - - 10 U1 T1 2 123 41 6 20 5 10 XYZ U2 xx &&&& Zack Zack database buffer cache

  18. Redo Buffer Operation Redo Buffer Then a user commits data file 2 undo file 3 data file 4 Tran File Block Row Column Value id User #1 1234 5 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 commit; T87 3 65 - - Zack And an entry recording the commit is made in the REDO buffer 1234 5 T87 4 89 28 22 &&&& x 20 x 10 T1 3 12 - - 10 U1 T1 2 123 41 6 20 5 T1 commit SCN time stamp 10 XYZ U2 xx &&&& Zack Zack database buffer cache

  19. Redo Buffer Operation Redo Buffer Then a user commits data file 2 undo file 3 data file 4 Tran File Block Row Column Value id User #1 1234 5 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 commit; T87 3 65 - - Zack And an entry recording the commit is made in the REDO buffer 1234 5 T87 4 89 28 22 &&&& x 20 x 10 T1 3 12 - - 10 U1 T1 2 123 41 6 20 This entry consists of: 5 • a “code” to indicate this is a commit (as opposed to a rollback or checkpoint) • a timestamp • the current SCN T1 commit SCN time stamp 10 XYZ U2 xx &&&& Zack Zack database buffer cache

  20. Redo Buffer Operation What is an SCN? Thought you’d never ask!!!

  21. System Change Number SCN is the System Change Number sequentially assigned by LGWR to indicate the “time” of a commit, rollback or checkpoint

  22. this SCN was assigned to the last transaction that issued a commit System Change Number SCN is the System Change Number sequentially assigned by LGWR to indicate the “time” of a commit, rollback or checkpoint For Example: 3256

  23. this SCN was assigned to the last transaction that issued a commit System Change Number SCN is the System Change Number sequentially assigned by LGWR to indicate the “time” of a commit, rollback or checkpoint For Example: 3256 3257

  24. this SCN was assigned to the last transaction that issued a commit System Change Number SCN is the System Change Number sequentially assigned by LGWR to indicate the “time” of a commit, rollback or checkpoint For Example: 3256 3257 3258

  25. this SCN was assigned to the last transaction that issued a commit System Change Number SCN is the System Change Number sequentially assigned by LGWR to indicate the “time” of a commit, rollback or checkpoint For Example: 3256 3257 3258 3259 3260 3261 3262 3263 3264 3265

  26. System Change Number SCN is the System Change Number sequentially assigned by LGWR to indicate the “time” of a commit, rollback or checkpoint For Example: 3256 3257 3258 3259 3260 3261 3262 3263 3264 3265 If one SCN is larger than another it means it was committed after the smaller was committed… and vice versa

  27. LGWR Log 1 Log 2 Redo Buffer Operation The log data will now get written to the logfile by the log writer (LGWR). Redo Buffer T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack T87 4 89 28 22 &&&& T1 3 12 - - 10 T1 2 123 41 6 20 T1 commit SCN# time stamp • LGWR writes when: • Commit • Buffer is 1/3 full • Checkpoint • 1 Meg of data in buffer • DBWR writes • 3 seconds

  28. LGWR Log 1 Log 2 Redo Buffer Operation Redo Buffer T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack T87 4 89 28 22 &&&& T1 3 12 - - 10 T1 2 123 41 6 20 T1 commit SCN# time stamp Notice that even though it was only transaction T1 that committed the data for T87 also was written even though it is not committed.

  29. Log 1 Log 2 Redo Buffer Operation Redo Buffer While this data is being written to the log file other data can continue to be placed in the redo buffer until the buffer is full. T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack T87 4 89 28 22 &&&& T1 3 12 - - 10 T1 2 123 41 6 20 T1 commit SCN# time stamp LGWR T87 3 65 - - &&&& T87 4 89 28 22 #### T2 3 20 - - - T2 7 47 34 9 22 T2 commit SCN# time stamp

  30. Log 1 Log 2 Redo Buffer Operation Redo Buffer While this data is being written to the log file other data can continue to be placed in the redo buffer until the buffer is full. Notice the two commits. T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack T87 4 89 28 22 &&&& T1 3 12 - - 10 T1 2 123 41 6 20 T1 commit SCN# time stamp LGWR T87 3 65 - - &&&& T87 4 89 28 22 #### T2 3 20 - - - T2 7 47 34 9 22 T2 commit SCN# time stamp T3 6 10 - - 15 T3 22 17 61 7 22 T3 commit SCN# time stamp

  31. Log 1 Log 2 Redo Buffer Operation Redo Buffer While this data is being written to the log file other data can continue to be placed in the redo buffer until the buffer is full T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack T87 4 89 28 22 &&&& T1 3 12 - - 10 T1 2 123 41 6 20 T1 commit SCN# time stamp LGWR T87 3 65 - - &&&& T87 4 89 28 22 #### T2 3 20 - - - T2 7 47 34 9 22 T2 commit SCN# time stamp T3 6 10 - - 15 T3 22 17 61 7 22 T3 commit SCN# time stamp Since LGWR is busy the two commits are “piggybacked” LGWR will write both at the same time and will use the same SCN for both

  32. LGWR Log 1 Log 2 Redo Buffer Operation Redo Buffer When LGWR finishes writing the group of log records the user is notified that their data has been committed. LGWR then begins writing the next group of log records. T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack T87 4 89 28 22 &&&& T1 3 12 - - 10 T1 2 123 41 6 20 T1 commit SCN# time stamp T87 3 65 - - &&&& T87 4 89 28 22 #### T2 3 20 - - - T2 7 47 34 9 22 T2 commit SCN# time stamp T3 6 10 - - 15 T3 22 17 61 7 22 T3 commit SCN# time stamp

  33. LGWR Log 1 Log 2 Redo Buffer Operation Redo Buffer T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack T87 4 89 28 22 &&&& T1 3 12 - - 10 T1 2 123 41 6 20 T1 commit SCN# time stamp T87 3 65 - - &&&& T87 4 89 28 22 #### T2 3 20 - - - T2 7 47 34 9 22 T2 commit SCN# time stamp T3 6 10 - - 15 T3 22 17 61 7 22 T3 commit SCN# time stamp

  34. LGWR Log 1 Log 2 Redo Buffer Operation Redo Buffer Additional data continues to be added to the redo buffer overwriting the previously written data T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack T87 4 89 28 22 &&&& T1 3 12 - - 10 T1 2 123 41 6 20 T1 commit SCN# time stamp T87 3 65 - - &&&& T87 4 89 28 22 #### T2 3 20 - - - T2 7 47 34 9 22 T2 commit SCN# time stamp T3 6 10 - - 15 T3 22 17 61 7 22 T3 commit SCN# time stamp T87 3 65 - - #### T87 4 89 28 22 @@@ T4 3 20 - - 15 T4 7 47 34 9 22 T4 3 20 - - 22 T4 7 47 34 9 33 T4 3 20 - - 33 T4 7 47 34 9 44

  35. LGWR Log 1 Log 2 Redo Buffer Operation Redo Buffer Additional data continues to be added to the redo buffer overwriting the previously written data T87 3 65 - - @@@ T87 4 89 28 22 ++++ T4 3 20 - - 44 T4 7 47 34 9 55 T4 3 20 - - 55 T4 7 47 34 9 66 T4 3 20 - - 66 T4 7 47 34 9 77 T87 3 65 - - &&&& T87 4 89 28 22 #### T2 3 20 - - - T2 7 47 34 9 22 T2 commit SCN# time stamp T3 6 10 - - 15 T3 22 17 61 7 22 T3 commit SCN# time stamp T87 3 65 - - #### T87 4 89 28 22 @@@ T4 3 20 - - 15 T4 7 47 34 9 22 T4 3 20 - - 22 T4 7 47 34 9 33 T4 3 20 - - 33 T4 7 47 34 9 44

  36. LGWR Log 1 Log 2 Redo Buffer Operation Redo Buffer OUCH! The REDO BUFFER just filled up! T87 3 65 - - @@@ T87 4 89 28 22 ++++ T4 3 20 - - 44 T4 7 47 34 9 55 T4 3 20 - - 55 T4 7 47 34 9 66 T4 3 20 - - 66 T4 7 47 34 9 77 T87 3 65 - - &&&& T87 4 89 28 22 #### T2 3 20 - - - T2 7 47 34 9 22 T2 commit SCN# time stamp T3 6 10 - - 15 T3 22 17 61 7 22 T3 commit SCN# time stamp T87 3 65 - - #### T87 4 89 28 22 @@@ T4 3 20 - - 15 T4 7 47 34 9 22 T4 3 20 - - 22 T4 7 47 34 9 33 T4 3 20 - - 33 T4 7 47 34 9 44

  37. Redo Buffer Operation All the users doing DML get this object on their screen for their efforts!

  38. Redo Buffer Operation

  39. LGWR Log 1 Log 2 Redo Buffer Operation Redo Buffer Solution: REDO BUFFER needs to be larger T87 3 65 - - @@@ T87 4 89 28 22 ++++ T4 3 20 - - 44 T4 7 47 34 9 55 T4 3 20 - - 55 T4 7 47 34 9 66 T4 3 20 - - 66 T4 7 47 34 9 77 T87 3 65 - - &&&& T87 4 89 28 22 #### T2 3 20 - - - T2 7 47 34 9 22 T2 commit SCN# time stamp T3 6 10 - - 15 T3 22 17 61 7 22 T3 commit SCN# time stamp T87 3 65 - - #### T87 4 89 28 22 @@@ T4 3 20 - - 15 T4 7 47 34 9 22 T4 3 20 - - 22 T4 7 47 34 9 33 T4 3 20 - - 33 T4 7 47 34 9 44

  40. NOW….To revert back to our discussionabout the physical redo logs

  41. Log 1 Log 2 Redo Log Operation LGWR continues writing to the log until the log fills up. LGWR

  42. Log 1 Log 2 Redo Log Operation LGWR continues writing to the log until the log fills up. LGWR

  43. Log 1 Log 2 Redo Log Operation LGWR continues writing to the log until the log fills up. LGWR

  44. Log 1 Log 2 Redo Log Operation LGWR continues writing to the log until the log fills up. LGWR then begins writing to the next redo log. This is called a log switch. LGWR

  45. Log 1 Log 2 a a Log 1 Log 2 b b Redo Log Operation For safety’s sake redo logs should always be mirrored. ALTER DATABASE ADD LOGFILE MEMBER log1b TO GROUP 1; ALTER DATABASE ADD LOGFILE MEMBER log2b TO GROUP 2; Note: this method is recommended instead of or in addition to using hardware mirroring

  46. Log 1 Log 2 a a Log 1 Log 2 b b Redo Log Operation For safety’s sake redo logs should always be mirrored. ALTER DATABASE ADD LOGFILE MEMBER log1b TO GROUP 1; ALTER DATABASE ADD LOGFILE MEMBER log2b TO GROUP 2; Note: this method is recommended instead of or in addition to using hardware mirroring LGWR will then write to the multiple logs simultaneously LGWR

  47. Database Recovery The Oracle recovery process has three parts performed in this order: Roll Forward: all changes made to data blocks that had not been written to the data files on disk are performed again by reapplying (redoing) the images originally recorded on the Redo Log. Open Database: users can begin normal operations Rollback: uncommitted transactions are rolled back using the undo log

  48. Database Recovery Redo Log data file 2 undo file 3 data file 4 Tran File Block Row Column Value id 1234 20 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack When recovery begins the control file is accessed to determine which enty in the redo log is to be used to start the roll foward portion of recovery. T87 4 89 28 22 &&&& T1 3 12 - - 10 T1 2 123 41 6 20 T1 commit SCN# time stamp T87 3 65 - - &&&& T87 4 89 28 22 #### T2 3 20 - - - T2 7 47 34 9 22 T2 commit SCN# time stamp T3 6 10 - - 15 T3 22 17 61 7 22 Control File database buffer cache

  49. Database Recovery Redo Log data file 2 undo file 3 data file 4 Tran File Block Row Column Value id 1234 20 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack That entry in the Redo Log is accessed. T87 4 89 28 22 &&&& T1 3 12 - - 10 The referenced block is retrieved from disk to the database buffer cache (unless it’s already there). It happens to be a roll back block. U1 T1 2 123 41 6 20 T1 commit SCN# time stamp T87 3 65 - - &&&& T87 4 89 28 22 #### T2 3 20 - - - T2 7 47 34 9 22 T2 commit SCN# time stamp T3 6 10 - - 15 T3 22 17 61 7 22 database buffer cache

  50. Database Recovery Redo Log data file 2 undo file 3 data file 4 Tran File Block Row Column Value id 1234 20 XYZ Zack T1 3 12 - - 5 T1 2 123 41 6 10 T87 3 65 - - Zack That entry in the Redo Log is accessed. T87 4 89 28 22 &&&& T1 3 12 - - 10 The referenced block is retrieved from disk to the database buffer cache (unless it’s already there). It happens to be a roll back block. U1 T1 2 123 41 6 20 5 T1 commit SCN# time stamp T87 3 65 - - &&&& T87 4 89 28 22 #### T2 3 20 - - - T2 7 47 34 9 22 T2 commit SCN# time stamp T3 6 10 - - 15 T3 22 17 61 7 22 The roll back data isrecreated showing the ‘before image’ database buffer cache

More Related