1 / 16

Managing Undo Data

Managing Undo Data. Objectives. After completing this lesson, you should be able to do the following: Explain DML and redo data generation Monitor and administer undo data Describe the difference between undo data and redo data Configure undo retention Guarantee undo retention

berit
Download Presentation

Managing Undo Data

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. Managing Undo Data

  2. Objectives • After completing this lesson, you should be able to do the following: • Explain DML and redo data generation • Monitor and administer undo data • Describe the difference between undo data and redo data • Configure undo retention • Guarantee undo retention • Use the Undo Advisor

  3. Data Manipulation • Data manipulation language (DML) consists of the following SQL statements: • INSERT • UPDATE • DELETE • MERGE • DML always executes as part of a transaction, which can be: • Rolled back, using the ROLLBACK command • Committed, using the COMMIT command

  4. Data Manipulation and Redo Data SQL> UPDATE employees SET salary = 5000 WHERE employee_id = 202; Block 1 Block 2 Block 3 SQL> DELETE FROMemployeesWHERE salary > 10000; Verify that redo data is on the disk and write COMMIT record SQL> COMMIT; Write verified, COMMITrecord written, control returned SQL> Redo log file

  5. Undo Data • Undo data is: • A copy of original, premodified data • Captured for every transaction that changes data • Retained at least until the transaction is ended • Used to support: • Rollback operations • Read-consistent and flashback queries • Recovery from failed transactions User

  6. Transactions and Undo Data Old value • Each transaction is assignedto only one undo segment. • An undo segment can servicemore than one transaction at a time. Table New value Undo segment Update transaction

  7. Storing Undo Information • Undo information is stored in undo segments, which are, in turn, stored in an undo tablespace. Undo tablespaces: • Are used only for undo segments • Have special recovery considerations • May be associated with only a single instance • Require that only one of them be the current writable undo tablespace for a given instance at any given time

  8. Undo Data Versus Redo Data

  9. Monitoring Undo • Undo usually requires little management. The areas to monitor include: • Free space in an undo tablespace • “Snapshot too old” errors DBA

  10. Administering Undo • Administration of undo should include preventing: • Space errors in an undo tablespace: • Size the undo tablespace properly. • Ensure that large transactions commit periodically. • “Snapshot too old” errors: • Configure an appropriate undo retention interval. • Size the undo tablespace properly. • Consider guaranteeing undo retention. • Use automatic undo management: UNDO_MANAGEMENT=AUTO UNDO_TABLESPACE=UNDOTBS1 DBA

  11. DBA Configuring Undo Retention • UNDO_RETENTION specifies (in seconds) the amount of already committed undo information that is to be retained. The only time you must set this parameter is when: • The undo tablespace has the AUTOEXTEND option enabled • You want to set undo retention for LOBs • You want to guarantee retention

  12. Guaranteeing Undo Retention Guarantee: 15 minutes Undo data SELECT statementsrunning 15 minutes or lessare always satisfied. A transaction that generatesmore undo than what thereis space for will fail.

  13. Sizing the Undo Tablespace Current table-space size Undo consumption rate

  14. Using the Undo Advisor

  15. Summary • In this lesson, you should have learned how to: • Explain DML and redo data generation • Monitor and administer undo segments • Configure undo retention • Guarantee undo retention • Use the Undo Advisor

  16. Practice Overview: Managing Undo Segments • This practice covers the following topics: • Calculating undo tablespace sizing to support a 48-hour retention interval • Modifying an undo tablespace to support a 48-hour retention interval

More Related