Managing Oracle's Undo Tablespaces
January 23, 2008
In the days of yore, we had rollback segments. In order to use a rollback segment in a transaction, either by letting Oracle choose one or by explicitly setting/naming one, the rollback segment first had to be specified in the initialization parameter file. With the advent of Automatic Undo Management (AUM), Oracle abstracts the management of undo space for you. Its fairly common to see references to three undo-related parameters: undo_management, undo_retention, and undo_tablespace.
How the database operates, that is, manual versus automatic undo modes, is driven by the setting of the undo_management parameter. How long Oracle will attempt to retain undo information is set by undo_retention, with a default value of 900. An out of the box/DBCA-created name for the tablespace is UNDOTBS1. The parameters and their values are shown below via SQL*Plus.
So, the question is this: if using automatic undo, do you need to specify an undo tablespace? In other words, because you had to specify the name of rollback segments somewhere (i.e., in the control file) for manual undo, are you required to specify the name of the undo tablespace somewhere as well (i.e., in the init.ora file)? The answer is no, you do not have to name the undo tablespace in the init.ora file. If you dont specify the name of the undo tablespace, how does Oracle know if you have one to begin with?
Recall that the creation of an undo tablespace starts with CREATE UNDO TABLESPACE <name>. As you must have one and only one undo tablespace online at any given time when using AUM (assuming the database is open), Oracle will still know which undo tablespace is active for the database, and you dont need to name/specify it in the init.ora file.
Given that you do not have to specify the tablespace, what are the cases where you could or should do so? One case is when you have more than one undo tablespace. Perhaps your database starts with UNDOTBS1, and while open, you change over to UNDOTBS2. Upon shutdown, which tablespace will be the undo tablespace of record upon the next startup? A second case is for informational purposes. It doesnt hurt to specify the tablespace, and if other DBAs are involved, it removes the guessing game aspect as to which undo tablespace is online/active. A third case is perhaps the most important reason why you should always specify an undo tablespace.
Lets suppose you create a second undo tablespace, naming it UNDOTBS2. When it is created, and recalling that only one undo tablespace at a time is active, what is the status of the newly created tablespace online or offline?
If there is another undo tablespace, and it is not being used as the current undo tablespace of record, then it doesn't matter if that not used undo tablespace is online or offline. What is also shown is that it is necessary in and of itself that the in-use designated undo tablespace be online. Put another way, being online is not sufficient, but rather, it is necessary. This hints at the third case mentioned above. However, if not in use, then the status does not matter.
Now that there are two undo tablespaces to choose from, even with the undo_tablespace parameter blanked out, lets confirm that Oracle will remember which undo tablespace was last used prior to shutdown and used on the next startup. The system is altered, but the scope (for the server parameter file) is not specified.
The output clearly shows that Oracle remembers what the current undo tablespace is, even if you dont specify it. What happens if you have undo_management set to auto, but no undo tablespace specified? Are you forced or required to use a separately created undo tablespace, or will the SYSTEM tablespace act as the default?
The first problematic area is that you have to go out of your way to drop undo tablespaces. To drop a tablespace in general, the database must be open. If open, there must be an active undo tablespace. This almost seems like a catch-22 situation, but there is an out. The solution is to unset the undo_tablespace parameter. Once unset (set the value to ), you can then drop it. Undo has to go somewhere, and that place is now the SYSTEM tablespace. It should be blatantly obvious as to why you would not want to use this tablespace as the undo repository. Since undo is now in this tablespace, and coming back to the online versus offline conditions, what is always true about the status of the system tablespace? Thats right - the system tablespace can never be offline.
Now that all explicitly created undo tablespaces are dropped and undo is being stored in system, what happens when we create a new undo tablespace but dont specify it for the parameter? Will Oracle automatically look for an undo tablespace? Lets create a new undo tablespace (reusing the space/file from before)
Apparently, Oracle cant wait to offload undo onto an undo-specific tablespace. It doesnt even wait for you to set the parameter, thats how eager it is to get undo out of SYSTEM.
What are the object lessons demonstrated about undo tablespaces? From Oracles perspective, they are:
Since you will always have an online SYSTEM tablespace at your disposal, one way to get around running out of disk space (because an undo tablespace was allowed to autoextend and all space is used up on a drive or mount point, and space is limited elsewhere), you can unset the undo_tablespace parameter, defaulting to SYSTEM for a moment, then drop the oversized undo tablespace, and re-create it with a smaller/more constrained size. Of course, if you have enough space elsewhere, create the new undo tablespace there before dropping the current one. Add this one to your bag of tricks; it may come in handy some day to dig your database out of a hole. Its not without a price though, because when dropped, the undo for however long retention was set to is gone as well.