Local Undo Mode vs Shared Undo in Oracle

Back to: Oracle DBA Tutorials

Local Undo Mode vs Shared Undo in Oracle

In this article, we will learn about Local Undo Mode vs Shared Undo in Oracle. Please read our previous article discussing Creating a new Pluggable Database in Oracle with examples. This is very interesting in Oracle 12c and 18c. To make this topic clearer, let’s take an example.

The container database contains one root database, which contains 0 or 1 pluggable database. In the above image, we have one root database and one pluggable database, PDB1. In the root database, we have system, users, syntax, temp, and unto tablespace.

We can set up our database to contain only one undo tablespace in the root. This way, all the pluggable databases will share this undo tablespace. We can even set up each pluggable database to have its own undo tablespace, which is better for database performance. This method is also easier for cloning.

How do you set up shared undo tablespace?

In the table database_properties, there is a parameter called local_undo_enabled. This parameter should be true. In Oracle 18c, this parameter is undo, which indicates that each pluggable database will have its own undo data. If the value of the parameter local_undo_enabled is false, then the pluggable databases present inside the root database will have shared undo tablespace.

When is Local UNDO Mode Required?

It is very important to have local undo if we need to make a database clone or near zero downtime PDB re-allocations. You have to start the database using a startup upgrade to change the value for the local undo enabled.

For example, we have a pluggable database, PDB1, which contains uncommitted transactions. If we want to clone, we copy the pluggable database from PDB1 to PDB5. When the value of LOCAL_UNDO_ENABLED = true, the uncommitted transactions will not be cloned because each pluggable database has its own undo tablespaces.

When the parameter LOCAL_UNDO_ENABLED=false, then if we try to clone for one pluggable database that contains uncommitted transactions, it will give you an error with ORA-65035, which is enabled to create a pluggable database.

The error shows “Cause: An attempt was made to clone a pluggable database that did not have local undo enabled.” As a DBA, we have to make the local_undo_enabled = true.

When local_undo_enabled = false, why is a clone not possible?

Because there is only one shared undo tablespace, we have multiple pluggable databases, each with information and uncommitted data. If we make a clone of one database, the other databases will be affected. We will be doing this exercise to understand more about the undo segment and undo data. Now, let us learn and share the local undo with an exercise.

Step 1: Connect to the database.

Open the virtual machine, open a terminal, and connect to the container database as sqlplus / as sysdba.

We have connected to the container database CDB$ROOT.

Step 2: Check the Database Properties

Let’s go ahead and check the database properties using the below query.

Statement: select property_value from database_properties where property_name=’LOCAL_UNDO_ENABLED’;

We can see the property_value is True. So, this is the default value.

Step 3: Change the value of LOCAL_UNDO_ENABLED.

Let’s try changing the value of LOCAL_UNDO_ENABLED to false. To do that, we have to shut down the database first.

Statement: shutdown immediate;

We have shut down the database. Now, let’s start the database using the startup upgrade method.

Statement: startup upgrade;

Now that the database has started, let’s issue the command to change the parameter LOCAL_UNDO_ENABLED to FALSE. This means that we will have only one undo tablespace.

Statement: alter database local undo off;

After changing the value, we have to shutdown the database using “shutdown immediate” and start it normally using “startup.” Once the database is started, we have to check the parameter LOCAL_UNDO_ENABLED value.

Statement: select property_value from database_properties where property_name=’LOCAL_UNDO_ENABLED’;

So, the pluggable databases inside the container database will share the undo tablespace. Let’s check the pluggable databases.

Command: show pdbs;

Let’s go ahead and connect to any of the pluggable databases.

Statement: alter session set container=jayapdb;

We have connected to the container database jayapdb.

Step 4: Create a table and do uncommitted transactions.

To test the undo tablespace, we will create a new table and make some uncommitted transactions.

Statement: create table test ( n number );

The table is now created. Now, let’s try to add a new record to the table.

Statement: insert into test values (1);

So, a new record has been inserted. We will not commit this transaction because uncommitted transactions use the undo segment, and the shared undo segment is available. So, if we try to clone the pluggable database and try to clone the DB, we will get an error. Let’s now try to clone a db and perform the action. Now, let’s open another terminal.

Step 5: Create another pluggable database using cloning.

Let’s open another terminal.

We have connected to the container database. Now, we have to create a new directory for the pluggable database. We will be creating a new pluggable database folder pdbnew. We will be creating a new pluggable database directory in the path /u01/app/oracle/oradata/JAYADB/pdbnew

The pdbnew folder contains all the files and folders related to the pluggable database pdbnew. Now, let’s try to create a pluggable database using the clone of jayapdb.

create pluggable database pdbnew from jayapdb
create_file_dest = '/u01/app/oracle/oradata/ORCL/pdbkh1';

Now, let’s try to execute this statement.

We get an error message showing error number ORA-65035, “unable to create a pluggable database from JAYAPDB.” We can check the details of this error using the command below. This command gives an explanation of each error that occurs in the database.

Command: host oerr ora 65035

We got an error message stating, “An attempt was made to clone a pluggable database that did not have local undo enabled.” This clearly shows that the error is caused by the shared undo tablespace. So, the local undo should always be true. If the value is false, we cannot clone the database.

Step 6: Change the local undo to true.

Now, let’s try to shut down the database and start the database in the startup mode.

The database is opened in the upgrade mode. Let’s issue the command “alter database local undo on;”.

Statement: alter pluggable database undo on;

Once the command is issued, we will restart the database.

After the value is modified, the database is started again. Let’s check the value from the database properties.

Statement: select property_value from database_properties where property_name=’LOCAL_UNDO_ENABLED’;

The value is true. If there is enough space on our disk, we can clone as many databases as we want without any error when the value is true. 

In the next article, I will discuss Automatic Undo Management in Oracle. In this article, I explain Local Undo Mode vs Shared Undo in Oracle with examples. I hope you enjoy this Local Undo Mode vs Shared Undo in Oracle article.