• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Undo Modes in 12.2 Multitenant Databases – Local and Shared Modes

by admin

Till 12cR1 the Undo tablespace used to be shared or global across all the PDBs in a container. From 12cR2 onwards, the local Undo is introduced. When local undo is enabled, each container has its own undo tablespace for every instance in which it is open. When local undo is disabled, there is one undo tablespace for the entire CDB.

The Undo mode, whether it’s local or shared, is the property of the entire CDB. Either all the PDBs are local undo mode or there’s shared undo for the entire CDB.

There are capabilities to switch between local and shared undo and back again. And the transition is seamless.

Local Undo Mode

Local undo mode means that each container has its own undo tablespace for every instance in which it is open. In this mode, Oracle Database automatically creates an undo tablespace for every container in the CDB (The initial size of undo datafile may vary). For an Oracle RAC CDB, there is one active undo tablespace for each instance for each PDB in local undo mode.

Local undo enables many of the major new capabilities of Multitenant in 12.2, including:

  • Hot Clone
  • Refresh PDB
  • PDB Relocate
  • Flashback PDB
Note: Oracle recommends local mode.

Shared Undo Mode

Shared undo mode means that there is one active undo tablespace for a single-instance CDB. For an Oracle RAC CDB, there is one active undo tablespace for each instance. Oracle continues to support shared undo in 12.2, but that is for upgrade transitional purposes only.

How to check the current Undo mode

To check the current undo mode use the below query:

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME          PROPERTY_VALUE    DESCRIPTION
--------------------   --------------    -----------------------------------
LOCAL_UNDO_ENABLED     TRUE              true if local undo is enabled

How to Convert CDB From LOCAL to SHARED UNDO MODE

When a CDB is in local undo mode, you can change it to use shared undo mode by issuing an ALTER DATABASE LOCAL UNDO OFF statement and restarting the database.

1. Startup the database with “upgrade” option.

SQL> startup upgrade

ORACLE instance started. 

Total System Global Area  734003200 bytes
Fixed Size                  8261248 bytes
Variable Size             562037120 bytes
Database Buffers           50331648 bytes
Redo Buffers                8515584 bytes
In-Memory Area            104857600 bytes

Database mounted.
Database opened.

2. Verify the current undo mode as “local”.

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';
 
PROPERTY_NAME           PROPERTY_VALUE            
--------------------    --------------- 
LOCAL_UNDO_ENABLED      TRUE 

3. Change the Local Undo mode to shared undo mode by issuing an “ALTER DATABASE LOCAL UNDO OFF” statement.

SQL> sho con_name 
 
CON_NAME
------------------------------
CDB$ROOT
SQL> ALTER DATABASE LOCAL UNDO off; 

Database altered.

4. Verify the current Undo mode again.

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';
 
PROPERTY_NAME           PROPERTY_VALUE            
--------------------    --------------- 
LOCAL_UNDO_ENABLED      FALSE

5. Shutdown the database and startup again in normal mode.

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

ORACLE instance started. 

Total System Global Area  734003200 bytes
Fixed Size                  8261248 bytes
Variable Size             562037120 bytes
Database Buffers           50331648 bytes
Redo Buffers                8515584 bytes
In-Memory Area            104857600 bytes

Database mounted.
Database opened.

6. When database is in shared undo mode, the CDB ignores any local undo tablespaces that were created when it was in local undo mode. Oracle recommends that you delete these local undo tablespaces.

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 
 
 CON_ID TABLESPACE_NAME       FILE_NAME
---------- ----------------      ----------------------
   1           UNDOTBS1             /u01/app/oracle/oradata/orcl/undotbs01.dbf 
   2           UNDOTBS1             /u01/app/oracle/oradata/orcl/pdb1/undotbs01.dbf
   3           UNDOTBS1             /u01/app/oracle/oradata/orcl/pdb2/undotbs01.dbf

Drop the Undo tablespace from the PDBs:

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE; 

Pluggable database altered.
SQL>  ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 

SQL> drop tablespace UNDOTBS1 including contents and datafiles; 

Tablespace dropped.

How to Convert CDB from SHARED to LOCAL UNDO MODE

When a CDB is in shared undo mode, you can change it to use local undo mode by issuing an ALTER DATABASE LOCAL UNDO ON statement and restarting the database.

When a CDB is changed from shared undo mode to local undo mode, Oracle Database creates the required undo tablespaces automatically.

1. Startup the database in “upgrade” mode.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area  734003200 bytes
Fixed Size                  8261248 bytes
Variable Size             562037120 bytes
Database Buffers           50331648 bytes
Redo Buffers                8515584 bytes
In-Memory Area            104857600 bytes

Database mounted.
Database opened.

2. Next, convert the CDB from shared to local undo mode using the below statement.

SQL>  alter database local undo on;

Database altered.

3. Startup the database with “force” option.

SQL> startup force;

ORACLE instance started.

Total System Global Area  734003200 bytes
Fixed Size                  8261248 bytes
Variable Size             562037120 bytes
Database Buffers           50331648 bytes
Redo Buffers                8515584 bytes
In-Memory Area            104857600 bytes

Database mounted.
Database opened.

4. Create Undo tablespace for PDB$SEED. This is an optional step and can be used to customize the Undo tablespace, created for all the PDBs.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;

Pluggable database altered.
SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

Session altered.
SQL> CREATE UNDO TABLESPACE test_undo datafile '/u01/app/oracle/oradata/orcl/pdb1/testundo.dbf' SIZE 10M AUTOEXTEND ON;

Tablespace created.
SQL> alter system set  UNDO_TABLESPACE='TEST_UNDO' scope=both; 

System altered.
SQL> show parameter undo 

NAME                                   TYPE          VALUE
------------------------------------   -----------   ------------------------------
temp_undo_enabled                      boolean       FALSE
undo_management                        string        AUTO
undo_retention                         integer       900
undo_tablespace                        string        TEST_UNDO
SQL>  ALTER SESSION SET CONTAINER=CDB$ROOT; 

Session altered.
SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY FORCE; 

Pluggable database altered.

5. Open the PDB is read write mode.

SQL> ALTER PLUGGABLE DATABASE PDB1  OPEN READ WRITE; 

Pluggable database altered.
SQL>  show parameter undo 

NAME                                    TYPE           VALUE
------------------------------------    -----------    ------------------------------
temp_undo_enabled                       boolean        FALSE
undo_management                         string         AUTO
undo_retention                          integer        900
undo_tablespace                         string         UNDOTBS1 

6. Verify that the Undo tablespace is created automatically.

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 
 
 CON_ID       TABLESPACE_NAME       FILE_NAME
----------    ----------------      ----------------------
   3           UNDO_1               /u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf

From the PDB

SQL> ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 
SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 
 
 CON_ID       TABLESPACE_NAME       FILE_NAME
----------    ----------------      ----------------------
   3           UNDO_1               /u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf

As you can see from the output above, Undo tablespace is created automatically.

Alert Log File entries (for PDB1)

2018-03-05T14:44:52.406849+05:30
ALTER PLUGGABLE DATABASE PDB1  OPEN READ WRITE
2018-03-05T14:44:54.137062+05:30
PDB1(3):Endian type of dictionary set to little
PDB1(3):Autotune of undo retention is turned on.
PDB1(3):Undo initialization finished serial:0 start:2394185293 end:2394185366 diff:73 ms (0.1 seconds)
PDB1(3):Database Characterset for PDB1 is AL32UTF8
PDB1(3):Opatch validation is skipped for PDB PDB1 (con_id=0)
2018-03-05T14:44:55.457499+05:30
PDB1(3):Opening pdb with no Resource Manager plan active
PDB1(3):CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE '/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 8192 MAXSIZE 34359721984 ONLINE
2018-03-05T14:44:58.688075+05:30
PDB1(3):[18452] Successfully onlined Undo Tablespace 5.
PDB1(3):Completed: CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE '/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 8192 MAXSIZE 34359721984 ONLINE
Pluggable database PDB1 opened read write
Completed: ALTER PLUGGABLE DATABASE PDB1  OPEN READ WRITE

Filed Under: oracle, oracle 12c

Some more articles you might also be interested in …

  1. How to move spfile from ASM to filesystem
  2. Oracle Database – How to recover from a lost datafile with no backup
  3. How to monitor Undo Tablespace Usage and the Free Space in Oracle Database
  4. Oracle Interview Questions – Flash Recovery Area
  5. How to upgrade RMAN catalog SCHEMA from 11g to 12.1.0.2 without upgrading the catalog database
  6. Oracle RMAN – Restore and Recovery of a Noarchivelog Database
  7. How to add or drop redo log groups in Oracle RAC
  8. Oracleasm Service Fails to Start After Upgrade to oracleasm-support-2.1.11-1 RPM Package
  9. How to backup and delete archivelogs older than # number of days
  10. PL/SQL: Factorial Program

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright