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