Enable Local undo tablespaces for PDBs 12c Release 2

This article explains how to enable the new feauture of Oracle database 12cR2 “Local undo tablespaces for PDBs”, and to check the auto creation of the UNDO tablespaces for all the PDBs.

 

Tables of contents:

1. Check the New Feature value
2. Enable the New Feature
3. Check the auto creation of Local undo tablespaces for all PDBs.

 

1. Check the New Feature value:

To check the oracle database features, we can check the dictionnary view database_properties from the container CDB$ROOT:

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';
PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ------------------------------
LOCAL_UNDO_ENABLED             FALSE                true if local undo is enabled

 

2. Enable the New Feature:

a. Check the Undo tablespaces for all PDBs:

SQL> SELECT b.name ,a.con_id, a.tablespace_name, a.status
FROM   cdb_tablespaces a,v$pdbs b
WHERE  a.tablespace_name LIKE 'UNDO%' and  a.con_id=b.con_id
ORDER BY a.con_id;

no rows selected

 

b. Enable the feature value:

SQL>  ALTER DATABASE LOCAL UNDO on;
 ALTER DATABASE LOCAL UNDO on
*
ERROR at line 1:
ORA-65192: database must be in UPGRADE mode for this operation


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup UPGRADE
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size                  8801008 bytes
Variable Size             742393104 bytes
Database Buffers          314572800 bytes
Redo Buffers                7974912 bytes
Database mounted.
Database opened.
SQL> ALTER DATABASE LOCAL UNDO on;
Database altered.

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

 

SQL> SELECT b.name ,a.con_id, a.tablespace_name, a.status
FROM   cdb_tablespaces a,v$pdbs b
WHERE  a.tablespace_name LIKE 'UNDO%' and  a.con_id=b.con_id
ORDER BY a.con_id;

no rows selected

 

c. Open all PDBs:

After the open all PDBs, the creation of all UNDO tablespaces for all PDBs done automatically:

SQL> alter pluggable database all open;
Pluggable database altered.

 

3. Check the auto creation of Local undo tablespaces for all PDBs.

a. Check the alerts file:

alter pluggable database all open
2018-05-27T22:31:10.644348+01:00
WADHAHPDB(3):Endian type of dictionary set to little
2018-05-27T22:31:10.656496+01:00
WADHAHPDB2(4):Endian type of dictionary set to little
2018-05-27T22:31:11.278884+01:00
Zero unrecovered txns found while converting pdb(3) to local undo mode,recovery not necessary
PDB WADHAHPDB(3) converted to local undo mode, scn: 0x0000000062c94098
WADHAHPDB(3):Autotune of undo retention is turned on.
2018-05-27T22:31:11.555733+01:00
Zero unrecovered txns found while converting pdb(4) to local undo mode,recovery not necessary
PDB WADHAHPDB2(4) converted to local undo mode, scn: 0x0000000062c942a8
WADHAHPDB2(4):Autotune of undo retention is turned on.
2018-05-27T22:31:11.655093+01:00
WADHAHPDB(3):Undo initialization finished serial:0 start:19092532 end:19092854 diff:322 ms (0.3 seconds)
WADHAHPDB(3):Database Characterset for WADHAHPDB is AL32UTF8
2018-05-27T22:31:11.868530+01:00
WADHAHPDB2(4):Undo initialization finished serial:0 start:19092793 end:19093066 diff:273 ms (0.3 seconds)
WADHAHPDB2(4):Database Characterset for WADHAHPDB2 is AL32UTF8
WADHAHPDB2(4):Opatch validation is skipped for PDB WADHAHPDB2 (con_id=0)
2018-05-27T22:31:12.773185+01:00
WADHAHPDB(3):Opatch validation is skipped for PDB WADHAHPDB (con_id=0)
2018-05-27T22:31:17.492708+01:00
WADHAHPDB(3):Opening pdb with no Resource Manager plan active
2018-05-27T22:31:19.055851+01:00
WADHAHPDB2(4):Opening pdb with no Resource Manager plan active
2018-05-27T22:31:20.943956+01:00
WADHAHPDB(3):CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE SIZE 104857600 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
2018-05-27T22:31:20.976402+01:00
WADHAHPDB2(4):CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE SIZE 104857600 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
2018-05-27T22:31:22.719478+01:00
WADHAHPDB(3):[17293] Successfully onlined Undo Tablespace 6.
WADHAHPDB(3):Completed: CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE SIZE 104857600 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
Pluggable database WADHAHPDB opened read write
2018-05-27T22:31:22.887596+01:00
WADHAHPDB2(4):[17295] Successfully onlined Undo Tablespace 6.
WADHAHPDB2(4):Completed: CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE SIZE 104857600 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
Pluggable database WADHAHPDB2 opened read write
2018-05-27T22:31:22.947290+01:00
Completed: alter pluggable database all open

 

b. Check again the Undo tablespaces for all PDBs after Local Undo enabled:

SQL>  SELECT b.name ,a.con_id, a.tablespace_name, a.status
FROM   cdb_tablespaces a,v$pdbs b
WHERE  a.tablespace_name LIKE 'UNDO%' and  a.con_id=b.con_id
ORDER BY a.con_id;

NAME                     CON_ID TABLESPACE_NAME                STATUS
-------------------- ---------- ------------------------------ ---------
WADHAHPDB                     3 UNDO_1                         ONLINE
WADHAHPDB2                    4 UNDO_1                         ONLINE

Conclusion:

This article explains how to enable the new feauture “Local undo tablespaces for PDBs” of Oracle database 12cR2 , and to check the creation of the UNDO tablespaces for all the PDBs.