Lec 5 Notes PDF

Slide 3: The Oracle Database server saves the old value (undo data) when a process changes data in a database. It stores the data as it exists before modifications. Capturing undo data enables you to roll back your uncommitted data. Undo supports read-consistent and flashback queries. Undo can also...

Slide 3: The Oracle Database server saves the old value (undo data) when a process changes data in a database. It stores the data as it exists before modifications. Capturing undo data enables you to roll back your uncommitted data. Undo supports read-consistent and flashback queries. Undo can also be used to “rewind” (flash back) transactions and tables. Read-consistent queries provide results that are consistent with the data as of the time a query started. For a read-consistent query to succeed, the original information must still exist as undo information. If the original data is no longer available, you receive a “Snapshot too old” error (ORA-01555). As long as the undo information is retained, the Oracle Database server can reconstruct data to satisfy read-consistent queries. Flashback queries purposely ask for a version of the data as it existed at some time in the past. As long as undo information for that past time still exists, flashback queries can complete successfully. Oracle Flashback Transaction uses undo to create compensating transactions, to back out a transaction and its dependent transactions. With Oracle Flashback Table, you can recover a table to a specific point in time. Undo data is also used to recover from failed transactions. A failed transaction occurs when a user session ends abnormally (possibly because of network errors or a failure on the client computer) before the user decides to commit or roll back the transaction. Failed transactions may also occur when the instance crashes or you issue the SHUTDOWN ABORT command. Slide 5: When a transaction starts, it is assigned to an undo segment. Throughout the life of the transaction, when data is changed, the original (before the change) values are copied into the undo segment. You can see which transactions are assigned to which undo segments by checking the V$TRANSACTION dynamic performance view. Undo segments are specialized segments that are automatically created by the database server as needed to support transactions. Like all segments, undo segments are made up of extents, which, in turn, consist of data blocks. Undo segmpents automatically grow and shrink as needed, acting as a circular storage buffer for their assigned transactions. Transactions fill extents in their undo segments until a transaction is completed or all space is consumed. If an extent fills up and more space is needed, the transaction acquires that space from the next extent in the segment. After all extents have been consumed, the transaction either wraps around back into the first extent or requests a new extent to be allocated to the undo segment Slide 6: Undo segments can exist only in a specialized form of tablespace called an undo tablespace. (You cannot create other segment types, such as tables, in the undo tablespace.) The Database Configuration Assistant (DBCA) automatically creates a smallfile undo tablespace. You can also create a bigfile undo tablespace. However, in a high-volume online transaction processing (OLTP) environment with many short concurrent transactions, contention could occur on the file header. An undo tablespace, stored in multiple data files, can resolve this potential issue. Slide 7: Undo data and redo data seem similar at first, but they serve different purposes. Undo data is needed if there is the need to undo a change, and this occurs for read consistency and rollback. Redo data is needed if there is the need to perform the changes again, in cases where they are lost for some reason. Undo block changes are also written to the redo log. Slide 8: Oracle Database 12c: Administration Workshop 9 - 8 The Oracle Database server provides automatic undo management, which is a fully automated mechanism for managing undo information and space in a dedicated undo tablespace for all sessions. The system automatically tunes itself to provide the best possible retention of undo information. More precisely, the undo retention period for auto-extending tablespaces is tuned to be slightly longer than the longest-running active query. For fixed-size undo tablespaces, the database dynamically tunes for best possible retention. Although by default the Oracle Database server manages undo data and space automatically, you may need to perform some tasks if your database is using Flashback operations. The administration of undo should prevent space errors, the use of too much space, and “Snapshot too old” errors Slide 9: The UNDO_RETENTION initialization parameter specifies (in seconds) the low threshold value of undo retention. Set the minimum undo retention period for the auto-extending undo tablespace to be as long as the longest expected Flashback operation. For auto-extending undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to meet the undo requirements of the queries. But this autotuned retention period may be insufficient for your Flashback operations. For fixed-size undo tablespaces, the system automatically tunes for the best possible undo retention period on the basis of undo tablespace size and usage history; it ignores UNDO_RETENTION unless retention guarantee is enabled. So for automatic undo management, the UNDO_RETENTION setting is used for the three cases listed in the slide. In cases other than these three, this parameter is ignored Slide 10: Undo information is divided into three categories: Uncommitted undo information (Active): Supports a currently running transaction, and is required if a user wants to roll back or if the transaction has failed. Uncommitted undo information is never overwritten. Committed undo information (Unexpired): Is no longer needed to support a running transaction but is still needed to meet the undo retention interval. It is also known as “unexpired” undo information. Committed undo information is retained when possible without causing an active transaction to fail because of lack of space. Expired undo information (Expired): Is no longer needed to support a running transaction. Expired undo information is overwritten when space is required by an active transaction. Slide 11: The default undo behavior is to overwrite the undo information of committed transactions that has not yet expired rather than to allow an active transaction to fail because of lack of undo space. This behavior can be changed by guaranteeing retention. With guaranteed retention, undo retention settings are enforced even if they cause transactions to fail. RETENTION GUARANTEE is a tablespace attribute rather than an initialization parameter. This attribute can be changed only with SQL command-line statements. The syntax to change an undo tablespace to guarantee retention is: SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE; To return a guaranteed undo tablespace to its normal setting, use the following command: SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE; The retention guarantee applies only to undo tablespaces. Attempts to set it on a non-undo tablespace result in the following error: SQL> ALTER TABLESPACE example RETENTION GUARANTEE; ERROR at line 1: ORA-30044: 'Retention' can only specified for undo tablespace Slide 12: You might have two reasons for changing the undo tablespace to a fixed size: to support Flashback operations (where you expect future use of the undo) or to prevent the tablespace from growing too large. If you decide to change the undo tablespace to a fixed size, you must choose a large enough size to avoid the following two errors: DML failures (because there is not enough space to create the undo for new transactions) “Snapshot too old” errors (because there was insufficient undo data for read consistency) Oracle recommends that you run a regular, full workload allowing the undo tablespace to grow to its minimum required size. The automatically gathered statistics include the duration of the longest-running query and the undo generation rate. Slide 13: Temporary tables are widely used as scratch areas for staging intermediate results. This is because changing those tables is much faster than with non-temporary tables. The performance gain is mainly because no redo entries are directly generated for changes on temporary tables. However, the undo for operations on temporary tables (and indices) is still logged to the redo log. Undo for temporary tables is useful for consistent reads and transaction rollbacks during the life of that temporary object. Beyond this scope the undo is superfluous. Hence it need not be persisted in the redo stream. For instance, transaction recovery just discards undo for temporary objects Slide 14: Enabling temporary undo provides the following benefits: Temporary undo reduces the amount of undo stored in the undo tablespaces. Less undo in the undo tablespaces can result in more realistic undo retention period requirements for undo records. Temporary undo reduces the size of the redo log. Performance is improved because less data is written to the redo log, and components that parse redo log records, such as LogMiner, perform better because there is less redo data to parse. Temporary undo enables data manipulation language (DML) operations on temporary tables in a physical standby database with the Oracle Active Data Guard option. However, data definition language (DDL) operations that create temporary tables must be issued on the primary database Slide 15: You can enable temporary undo for a specific session or for the entire database. When you enable temporary undo for a session using an ALTER SESSION statement, the session creates temporary undo without affecting other sessions. When you enable temporary undo for the system using an ALTER SYSTEM statement, all existing sessions and new sessions create temporary undo. When a session uses temporary objects for the first time, the current value of the TEMP_UNDO_ENABLED initialization parameter is set for the rest of the session. Therefore, if temporary undo is enabled for a session and the session uses temporary objects, then temporary undo cannot be disabled for the session. Similarly, if temporary undo is disabled for a session and the session uses temporary objects, then temporary undo cannot be enabled for the session. The feature of temporary undo is available for databases with the COMPATIBLE initialization parameter set to at least 12.1.0.0.0. Slide 16: V$TEMPUNDOSTAT shows various statistics related to the temporary undo log for this database instance. It displays a histogram of statistical data to show how the system is working. Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order of the BEGIN_TIME column value. This view contains a total of 576 rows, spanning a 4-day cycle. This view is similar to the V$UNDOSTAT view. The example shows you some of the important columns of the V$TEMPUNDOSTAT view: BEGIN_TIME: Identifies the beginning of the time interval TXNCOUNT: Total Number of transactions that have bound to temp undo segment within the corresponding time interval MAXCONCURRENCY: Highest number of transactions executed concurrently, which modified temporary objects within the corresponding time interval UNDOBLKCNT: Total number of temporary undo blocks consumed during the corresponding time interval USCOUNT: Temp undo segments created during the corresponding time interval NOSPACEERRCNT: Total number of times the error no space left for temporary undo was raised during the corresponding time interval Note: For more information on V$TEMPUNDOSTAT refer to the Oracle Database Reference Guide Slide 18: At the bottom of the Undo Management Details page, you can view additional statistical information:. Undo Generation Rate: Displays the undo generation (in KB per second). Undo Space Usage: Shows the use of space in the tablespace. Steal Activity Breakdown: Shows the number of attempts to steal expired undo blocks from other undo segments and attempts to obtain undo space by stealing unexpired extents from other transactions Slide 19: You can access the Undo Advisor in Enterprise Manager Cloud Control by Performance > Advisors Home > Automatic Undo Management. The middle part of the General tab contains the Undo Advisor analysis. It provides an estimate of the undo tablespace size required to satisfy a given undo retention. Click Show Graph to see a graphical representation of the required tablespace size. You can click a point on the graph to see the tablespace size required to support the selected period. To change the undo tablespace to a fixed size, click Edit Undo Tablespace and then click Edit in the Datafile section.