Wednesday, 3 March 2021

UNDO Modes in 19c Multitenant Databases – Local and Shared Modes

 

UNDO Modes in 19c Multitenant Databases – Local and Shared Modes

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. From 19 c Local undo mode is enable by default

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. shutdown and Startup the database with “upgrade” option.

SQL>

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade

ORACLE instance started.

 

Total System Global Area 1577056160 bytes

Fixed Size                  9267104 bytes

Variable Size             402653184 bytes

Database Buffers         1157627904 bytes

Redo Buffers                7507968 bytes

Database mounted.

Database opened.

SQL> 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

--------------------------------------------------------------------------------

DESCRIPTION

--------------------------------------------------------------------------------

LOCAL_UNDO_ENABLED

TRUE

true if local undo is enabled3. Change the Local Undo mode to shared undo mode by issuing an “ALTER DATABASE LOCAL UNDO OFF” statement.

 

SQL>  show con_name

 

CON_NAME

------------------------------

CDB$ROOT

SQL>

 

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. shutdown and Startup the database in “upgrade” mode.

 

SQL> show con_name

 

CON_NAME

------------------------------

CDB$ROOT

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade

ORACLE instance started.

 

Total System Global Area 1577056160 bytes

Fixed Size                  9267104 bytes

Variable Size             402653184 bytes

Database Buffers         1157627904 bytes

Redo Buffers                7507968 bytes

Database mounted.

Database opened.

SQL>

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 1577056160 bytes

Fixed Size                  9267104 bytes

Variable Size             402653184 bytes

Database Buffers         1157627904 bytes

Redo Buffers                7507968 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>

SQL

SQL>  CREATE UNDO TABLESPACE PDB1_undo datafile 'F:\oracle\oradata\CDB2\pdb1\pdb1undo.dbf' SIZE 10M AUTOEXTEND ON;

 

Tablespace created.

 

SQL>

SQL> alter system set  UNDO_TABLESPACE= ‘PDB1_undo ' scope=both;

 

System altered.

SQL> show parameter undo

 

SQL>

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

temp_undo_enabled                    boolean     FALSE

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      PDB1_undo

SQL>

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

SQL>

6. Verify that the Undo tablespace is created automatically.

SQLSQL> 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

F:\ORACLE\ORADATA\CDB2\UNDOTBS01.DBF

 

         4 UNDO_1

F:\ORACLE\ORADATA\CDB2\PDB1\CDB2\E63D29FFCECF4268ACD10F75F4A93FC3\DATAFILE\O1_MF

_UNDO_1_J410L9KT_.DBF

 

 

SQL>

 

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

--------------------------------------------------------------------------------

         4 UNDO_1

F:\ORACLE\ORADATA\CDB2\PDB1\CDB2\E63D29FFCECF4268ACD10F75F4A93FC3\DATAFILE\O1_MF

_UNDO_1_J410L9KT_.DBF

 

 

SQL>

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

Alert Log File entries (for PDB1)

No comments:

Post a Comment