Multitenant : Local Undo Mode in Oracle Database 12c Release 2 (12.2

 

)

In Oracle Database 12c Release 1 all containers in an instance shared the same undo tablespace. In Oracle 12c Release 2 each container in an instance can use its own undo tablespace. This new undo management mechanism is called local undo mode, whilst that of previous releases is now known as shared undo mode. Local undo mode is the default mode in newly created databases, so you will probably only need to consider switching undo modes for upgraded instances.

You should switch to local undo mode unless you have a compelling reason not to. Some of the new multitenant features in 12.2 rely on local undo. This article demonstrates how to switch to shared undo mode, only so you can see the process of switching back to local undo mode.

• Switching to Shared Undo Mode
• Switching to Local Undo Mode
• Other Considerations

Related articles.

• Multitenant : All Articles

Switching to Shared Undo Mode

We can display the current undo mode using the following query, which shows we are currently in local undo mode.

column property_name format a30
column property_value format a30

select property_name, property_value
from   database_properties
where  property_name = 'LOCAL_UNDO_ENABLED';

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

SQL>

 If this is a multitenant database that has been upgraded to 12.2 or above, this query may show "no rows selected" until local undo mode is enabled.

We also check for the presence of the undo tablespaces for the root container (con_id=1) and user-defined pluggable database (con_id=3).

select con_id, tablespace_name
from   cdb_tablespaces
where  contents = 'UNDO'
order by con_id;

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

SQL>

The following commands demonstrate how to switch to shared undo mode using the ALTER DATABASE LOCAL UNDO OFF command.

conn / as sysdba

shutdown immediate;
startup upgrade;

alter database local undo off;

shutdown immediate;
startup;

Once the instance is restarted we can check the undo mode again and see we are now in shared undo mode.

column property_name format a30
column property_value format a30

select property_name, property_value
from   database_properties
where  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             FALSE

SQL>

We still have the local undo tablespace for the user-defined pluggable database (con_id=3), even though the instance will no longer use it.

select con_id, tablespace_name
from   cdb_tablespaces
where  contents = 'UNDO'
order by con_id;

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

SQL>

For clarity, we should remove it.

alter session set container = pdb1;

select file_name
from   dba_data_files
where  tablespace_name = 'UNDOTBS1';

----------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/cdb1/pdb1/undotbs01.dbf

SQL>


drop tablespace undotbs1;

Tablespace dropped.

SQL>

The instance is now running in shared undo mode, with all old local undo tablespaces removed.

Switching to Local Undo Mode

We display the current undo mode using the following query, which shows we are currently in shared undo mode.

conn / as sysdba

column property_name format a30
column property_value format a30

select property_name, property_value
from   database_properties
where  property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ------------------------------
LOCAL_UNDO_ENABLED             FALSE

SQL>

We also check for the presence of the undo tablespaces and only see that of the root container (con_id=1).

select con_id, tablespace_name
from   cdb_tablespaces
where  contents = 'UNDO'
order by con_id;

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

SQL>

The following commands demonstrate how to switch to local undo mode using the ALTER DATABASE LOCAL UNDO ON command.

conn / as sysdba

shutdown immediate;
startup upgrade;

alter database local undo on;

shutdown immediate;
startup;

Once the instance is restarted we can check the undo mode again and see we are now in local undo mode.

column property_name format a30
column property_value format a30

select property_name, property_value
from   database_properties
where  property_name = 'LOCAL_UNDO_ENABLED';

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

SQL>

When we check for undo tablespaces we see Oracle has created a local undo tablespace for each user-defined pluggable databases.

select con_id, tablespace_name
from   cdb_tablespaces
where  tablespace_name LIKE 'UNDO%'
order by con_id;

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

SQL>

If we create a new pluggable database, we can see it is also created with a local undo tablespace.

create pluggable database pdb2 admin user pdb_adm identified by Password1;
alter pluggable database pdb2 save state;

select con_id, tablespace_name
from   cdb_tablespaces
where  contents = 'UNDO'
order by con_id;

    CON_ID TABLESPACE_NAME
---------- ------------------------------
         1 UNDOTBS1
         3 UNDO_1
         4 UNDOTBS1

SQL>