Managing the undo tablespace - Oracle DBA

This post describes managing the undo tablespace - oracle dba which stores information used to roll back changes to the oracle database. It contains the following topics:

If you would like to Enrich your career with a Database Administrator(DBA) and get Oracle certified professional, then visit Mindmajix - A Global online training platform: “Oracle DBA Online Course ”  Course.  This course will help you to achieve excellence in this domain.

Managing the undo tablespace 

What is undo?

Every Oracle database must have a method of maintaining information that is used to roll back or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

[Related Article: Oracle DBA Tutorial]

Undo records are used to:

• Rollback transactions when a rollback statement is issued
• Recover the database
• Provide read consistency
• Analyze data as of an earlier point in time by using oracle flashback query
• Recover from logical corruptions using oracle flashback features

When a rollback statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before the image of the data for users who are accessing the data at the same time that another user is changing it.

[Related Article: Managing the Redo Log]

Introduction to automatic undo management:

This section introduces the concepts of automatic undo management and discusses the following topics:

Overview of automatic undo management:

Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing to undo information and space. In this management model, you create an undo tablespace, and the server automatically manages to undo segments and space among the various active sessions.

When the instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the system tablespace. This is not recommended in normal circumstances, and an alert message is written to the alert log file to warn that the system is running without an undo tablespace.

[Related Article: Oracle DBA Interview Questions]

Undo_tablespace = undotbs_01

In this case, if you have not already created the undo tablespace (in this example, undotbs_01), the startup command fails. The undo_tablespace parameter can be used to assign a specifically undo tablespace to an instance in an oracle real application clusters environment.

[Related Article: Oracle Apps DBA Interview Questions]

The following is a summary of the initialization parameters for automatic undo management:

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!