What Is Local Undo Mode

Since I mentioned local undo mode in my previous posts, lots of you asked me to elaborate on it, and remove the confusion around the local undo in the PDBs. Today, we’ll be looking in more details at the local undo mode, how you can check your database’s undo mode, and how to change it.

With the introduction of the 12c database (release 1), initially the undo tablespace was shared by all the PDBs within the same CDB, meaning there was only one UNDO tablespace for the instances. This is called shared undo mode.

Starting with 12cR2, the local undo mode was introduced, meaning that each PDB has their own UNDO tablespaces. This is required by a few Oracle features, ie. hot cloning. If the database (the CDB) is created with DBCA, then local undo mode is the default mode. If the database (the CDB) is created with the CREATE DATABASE statement, then shared undo mode is the default. In the latter case, you need to use the undo_mode_clause and specify LOCAL UNDO ON explicitly, if you want to create a database with local undo mode.

Since lots of CDBs are created with DBCA, as it is much more easier and convenient, than running the create database statement, most Oracle databases version 12cR2 and up, have local undo enabled. You can probably find the shared undo mode in the databases that were upgraded from 12.1 to later releases.

Where does Oracle need the local undo mode? Here are some of features that I came across, that require local undo mode: PDB hot cloning, creating refreshable PDBs, near zero downtime PDB relocation, proxy PDBs.

How to determine what undo mode the database is in? The answer is in the database_properties view

col property_name for A20
col property_value for A20

select property_name, property_value 
from database_properties
where property_name like '%UNDO%';

PROPERTY_NAME        PROPERTY_VALUE
-------------------- --------------------
LOCAL_UNDO_ENABLED   TRUE

Another hint would be to check if the PDBs have their own UNDO tablespace:

select con_id, tablespace_name
from cdb_tablespaces
where contents like '%UNDO%'
order by 1;

    CON_ID TABLESPACE_NAME
---------- --------------------
         1 UNDOTBS1
         3 UNDOTBS1

How to switch to local undo mode?

To switch to local undo mode it is pretty easy, but it requires an outage. Of course you can only switch to local undo mode if you are in shared undo mode.

sqlplus / as sysdba
shutdown immediate;
startup upgrade

alter database local undo on;

shutdown immediate;
startup;


col property_name for A20
col property_value for A20

select property_name, property_value 
from database_properties
where property_name like '%UNDO%;

The interesting thing is that Oracle created the local undo tablespaces for each existing PDB in the CDB. Even more if I create a new PDB, then Oracle will automatically create the local undo tablespace for that PDB.

It is possible to switch to shared undo mode, but most likely you will not be doing that. The method to switch to shared undo mode is similar to what we did before.

sqlplus / as sysdba
shutdown immediate;
startup upgrade;
alter database local undo off;

shutdown immediate;
startup;

Note that the local undo tablespace inside the PDBs will not be dropped by Oracle and it will not be used either. For clarity purposes it is recommended to drop them manually after you switch the database to shared undo mode.

I created a mind map for you, to easily review the local undo mode. Feel free to print it, use it and share it.

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com. Follow the link to get the best price, only $18.99 CAD available until the end of the month!