DBA Hub

📋Steps in this guide1/4

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

In Oracle Database 12c Release 2 each pluggable database can use its own local undo tablespace.

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

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. 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). The following commands demonstrate how to switch to shared undo mode using the command. Once the instance is restarted we can check the undo mode again and see we are now in shared undo mode. 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. For clarity, we should remove it. The instance is now running in shared undo mode, with all old local undo tablespaces removed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
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>

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

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

SQL>

conn / as sysdba

shutdown immediate;
startup upgrade;

alter database local undo off;

shutdown immediate;
startup;

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>

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

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

SQL>

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

Switching to Local Undo Mode

We display the current undo mode using the following query, which shows we are currently in shared undo mode. We also check for the presence of the undo tablespaces and only see that of the root container (con_id=1). The following commands demonstrate how to switch to local undo mode using the command. Once the instance is restarted we can check the undo mode again and see we are now in local undo mode. When we check for undo tablespaces we see Oracle has created a local undo tablespace for each user-defined pluggable databases. If we create a new pluggable database, we can see it is also created with a local undo tablespace.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
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>

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

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

SQL>

conn / as sysdba

shutdown immediate;
startup upgrade;

alter database local undo on;

shutdown immediate;
startup;

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>

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>

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

Other Considerations

The documentation describes an optional step to create an undo tablespace in the seed database, if you don't want to rely on the auto-creation of the local undo tablespace. You can read about this here .
4

Oracle 19.9 Onward

Prior to Oracle 19.9, setting the parameter in the root container meant that value was set for all pluggable databases also. From Oracle 19.9 onward this is not the case. Now there are two ways to set the parameter in the root container. In addition, the parameter can be set separately in each PDB, provided local undo is being used. For more information see: - Managing the CDB Undo Mode - Multitenant : All Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
conn / as sysdba

-- Just the root container.
alter system set undo_retention=3000;

-- The root container and all PDBs.
alter system set undo_retention=3000 container=all;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!