Difference between revisions of "Local Undo"

From Robs_Wiki
Jump to: navigation, search
(Change from shared to local undo)
(Local Undo Concept)
 
Line 1: Line 1:
 
__TOC__
 
__TOC__
 
== Local Undo Concept ==
 
== Local Undo Concept ==
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. Local undo allows for many new features, like flashback pluggable databases and online cloning of PDB's.
+
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. Local undo allows for many new features, like flashback pluggable databases and online cloning of PDB's while the source stays open in R/W mode.
 +
 
 
== Change from shared to local undo ==
 
== Change from shared to local undo ==
 
<pre>
 
<pre>

Latest revision as of 16:37, 4 October 2020

Local Undo Concept

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. Local undo allows for many new features, like flashback pluggable databases and online cloning of PDB's while the source stays open in R/W mode.

Change from shared to local undo

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

Now change to LOCAL_UNDO

CONN / AS SYSDBA

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;

ALTER DATABASE LOCAL UNDO ON;

SHUTDOWN IMMEDIATE;
STARTUP;

Check the UNDO again:

SQL> @cdb_check_undo

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

change from local to shared undo

This is just a reverse operation, although there is no reason you would want shared undo.<br / All steps are the same, just use "ALTER DATABSE LOCAL UNDO OFF" as opposed to ON. </pre>