LEC5_DBA2_Managing Undo Data PDF

Managing Undo Data LECTURE 5 Objectives -Explain DML and undo data generation. - Monitor and administer undo data. - Describe the difference between undo data and redo data. - Configure undo retention. - Guarantee undo retention. - Enable temporary undo. - Use the Undo Advisor. Undo Data:...

Managing Undo Data LECTURE 5 Objectives -Explain DML and undo data generation. - Monitor and administer undo data. - Describe the difference between undo data and redo data. - Configure undo retention. - Guarantee undo retention. - Enable temporary undo. - Use the Undo Advisor. Undo Data: Overview Undo data is: ◦A record of the action of a transaction. ◦Captured for every transaction that changes data. ◦ Retained at least until the transaction is ended. ◦Used to support: ◦ Rollback operations. ◦ Read-consistent queries. ◦ Oracle Flashback Query, Oracle Flashback Transaction, and Oracle Flashback Table. ◦ Recovery from failed transactions. Undo Data: Overview Undo information is retained for all transactions, at least until the transaction is ended by one of the following: ◦ User undoes a transaction (transaction rolls back). ◦ User ends a transaction (transaction commits). ◦ User executes a DDL statement, such as a CREATE, DROP, RENAME, or ALTER statement. If the current transaction contains any DML statements, the database server first commits the transaction and then executes and commits the DDL as a new transaction. ◦ User session terminates abnormally (transaction rolls back). ◦ User session terminates normally with an exit (transaction commits). Transactions and Undo Data Storing Undo Information - Undo information is stored in undo segments, which are 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. Comparing Undo Data and Redo Data Managing Undo Automatic undo management: ◦ Fully automated management of undo data and space in a dedicated undo tablespace For all sessions. ◦ Self-tuning in AUTOEXTEND tablespaces to satisfy long-running queries. ◦ Self-tuning in fixed-size tablespaces for best retention. DBA tasks in support of Flashback operations: - Configuring undo retention. - Changing undo tablespace to a fixed size. - Avoiding space and “snapshot too old” errors. Configuring Undo Retention - UNDO_RETENTION specifies (in seconds) how long already committed undo information is to be retained. - Set this parameter when: - The undo tablespace has the AUTOEXTEND option enabled. - You want to set undo retention for LOBs. - You want to guarantee retention. Categories of Undo Guaranteeing Undo Retention Changing an Undo Tablespace to a Fixed Size Rationale: – Supporting Flashback operations – Limiting tablespace growth. Steps: 1. Run regular workload. 2. Self-tuning mechanism establishes minimum required size. 3. (Optional) Use the Enterprise Manager Cloud Control Undo Advisor, which calculates required size for future growth. 4. (Optional) Change undo tablespace to a fixed size. Temporary Undo: Overview Temporary Undo: Benefits - Temporary undo reduces the amount of undo stored in the undo tablespaces. -Temporary undo reduces the size of the redo log. -Temporary undo enables DML operations on temporary tables in a physical standby database with the Oracle Active Data Guard option. Enabling Temporary Undo Monitoring Temporary Undo Viewing Undo Information Viewing Undo Activity Using the Undo Advisor THANKS