大型数据库系统分析与设计：Less07-Undo

1、Managing Undo DataObjectivesAfter completing this lesson, you should be able to do the following:Explain DML and redo data generationMonitor and administer undo dataDescribe the difference between undo data and redo dataConfigure undo retentionGuarantee undo retentionUse the Undo AdvisorData Manipul

2、ationData manipulation language (DML) consists of the following SQL statements:INSERTUPDATEDELETEMERGEDML always executes as part of a transaction, which can be:Rolled back, using the ROLLBACK commandCommitted, using the COMMIT commandData Manipulation and Redo DataBlock 1Block 3Block 2SQL COMMIT;Re

3、do log fileVerify that redo data is on the disk and write COMMIT recordWrite verified, COMMITrecord written, control returned SQL SQL DELETE FROM employees WHERE salary 10000;SQL UPDATE employeesSET salary = 5000WHERE employee_id = 202;Undo DataUndo data is:A copy of original, premodified dataCaptur

4、ed for every transaction that changes dataRetained at least until the transaction is endedUsed to support:Rollback operationsRead-consistent and flashback queriesRecovery from failed transactionsUserUndo DataFull Notes PageTransactions and Undo DataEach transaction is assignedto only one undo segmen

5、t.An undo segment can servicemore than one transaction at a time.Update transactionOld valueNew valueUndo segmentTableStoring Undo InformationUndo information is stored in undo segments, which are, in turn, stored in an undo tablespace. Undo tablespaces:Are used only for undo segmentsHave special re

6、covery considerationsMay be associated with only a single instanceRequire that only one of them be the current writable undo tablespace for a given instance at any given timeUndo Data Versus Redo DataUndoRedoRecord ofHow to undo a changeHow to reproduce a changeUsed forRollback, read-consistency, an

7、d flashbackRecovery, to roll forward database changesStored inUndo segmentsRedo log filesProtects againstInconsistent reads in multiuser systemsData lossMonitoring UndoUndo usually requires little management. The areas to monitor include:Free space in an undo tablespace“Snapshot too old” errorsDBAMo

8、nitoring UndoFull Notes PageAdministering UndoAdministration 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 u

9、ndo tablespace properly.Consider guaranteeing undo retention.Use automatic undo management:DBAUNDO_MANAGEMENT=AUTOUNDO_TABLESPACE=UNDOTBS1Configuring Undo RetentionDBAUNDO_RETENTION specifies (in seconds) the amount of already committed undo information that is to be retained. The only time you must

10、 set this parameter is when:The undo tablespace has the AUTOEXTEND option enabledYou want to set undo retention for LOBsYou want to guarantee retentionConfiguring Undo RetentionFull Notes PageGuaranteeing Undo RetentionGuarantee: 15 minutesA transaction that generatesmore undo than what thereis spac

11、e for will fail.Undo dataSELECT statementsrunning 15 minutes or lessare always satisfied.Sizing the Undo TablespaceCurrent table-space sizeUndo consumption rateUsing the Undo AdvisorSummaryIn this lesson, you should have learned how to:Explain DML and redo data generationMonitor and administer undo segmentsConfigure undo retentionGuarantee undo retentionUse the Undo AdvisorPractice Overview: Managing Undo SegmentsThis