DBA Hub

📋Steps in this guide1/4

Local/Shared Undo mode in oracle 12.2 multitenant database - DBACLASS DBACLASS

There are two undo modes in oracle 12.2 Multitenant database 1. Local undo mode 2. Shared undo mode Local undo mode: In this mode, each container ( i.e PDB ) in multitenant will have their own active undo tablespace. Share Undo mode: In this mode, There will be only one undo tablespace for the instance. […]

oracle clusteringintermediate
by OracleDba
15 views
1

Overview

There are two undo modes in oracle 12.2 Multitenant database 1. Local undo mode 2. Shared undo mode Local undo mode: In this mode, each container ( i.e PDB ) in multitenant will have their own active undo tablespace. Share Undo mode:
2

Section 2

In this mode, There will be only one undo tablespace for the instance. NOTE – In previous releases, undo was in shared mode. NOTE – From oracle 12.2, Oracle recommends to use local undo mode only. How to check the current undo mode:

Code/Command (click line numbers to comment):

1
2
3
4
5
select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

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

Section 3

Convert local undo mode to shared undo mode: After converting to shared undo mode, the individual undo tablespaces won’t be dropped automatically. We have to drop them manually, by connecting to the respective database. Convert shared undo mode to local undo mode:

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
select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

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

SQL> sho con_name 

CON_NAME
------------------------------
CDB$ROOT

shutdown immediate;
startup upgrade;
SQL> ALTER DATABASE LOCAL UNDO off; 

Database altered.


select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME 			PROPERTY_VALUE            
-------------------- 	--------------- 		
LOCAL_UNDO_ENABLED 			FALSE 

shutdown immediate;
startup

alter pluggable database PDB1 OPEN READ WRITE;

ALTER PLUGGABLE DATABASE PDB2 OPEN READ WRITE;

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



SQL>  ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 

SQL> drop tablespace UNDOTBS1 including contents and datafiles; 

Tablespace dropped.


SQL>  ALTER SESSION SET CONTAINER=PDB2; 

Session altered


SQL> drop tablespace UNDOTBS1 including contents and datafiles; 

Tablespace dropped.
4

Section 4

Once local undo is enabled, individual undo tablespaces will be created automatically for each PDB.

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
select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME 			PROPERTY_VALUE            
-------------------- 	--------------- 		
LOCAL_UNDO_ENABLED 			FALSE


shutdown immediate;
startup upgrade;


alter database local undo on;


select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

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

shutdown immediate;
startup

alter pluggable database PDB1 OPEN READ WRITE;

ALTER PLUGGABLE DATABASE PDB2 OPEN READ WRITE;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!