Disable Local undo tablespaces for PDBs 12c Release 2

This article explains how to disable the new feauture of Oracle database 12cR2 “Local undo tablespaces for PDBs” and to drop the UNDO tablespaces for PDBs.

 

Tables of contents:

  1. Check the New Feature
  2. Disable the New Feature
  3. Drop Local undo tablespaces for all PDBs.

 

1. Check the New Feature:

When we create the Oracle database “wadhah” as container,we create a PDB “wadhahpdb”  with the features “Local undo tablespaces for PDBs”.

 

b. Check the feature:

To check the oracle database features, we can check the dictionnary view database_properties;

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

 

c. Check the UNDO tablespaces per PDBs:

I have Two Pluggable databases:

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 UNDOTBS1                       ONLINE
WADHAHPDB2                    4 UNDOTBS1                       ONLINE

2. Disable the New Feature:

To disable this feature, we will follow these steps:

a. Connect to the CDB$ROOT container:

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

 

b. Execute the commande “ALTER DATABASE LOCAL UNDO off” with startup upgrade:

 

SQL>  ALTER DATABASE LOCAL UNDO off;
 ALTER DATABASE LOCAL UNDO off
*
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 off;
Database altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
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.

 

c. Check the feature value:

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

 

3. Drop Local undo tablespaces for all PDBs:

a. Open all PDBs:

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

 

b. Check the Undo tablespaces status:

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 UNDOTBS1                       ONLINE
WADHAHPDB2                    4 UNDOTBS1                       ONLINE

c. Drop The tablespace UNDO for the pluggable database wadhahpdb:

SQL> alter session set container=wadhahpdb;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
WADHAHPDB
SQL> alter tablespace undotbs1 offline;
Tablespace altered.
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

 

d. Drop The tablespace UNDO for the pluggable database wadhahpdb2:

SQL> alter session set container=wadhahpdb2;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
WADHAHPDB2
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

 

Conclusion:

This article explains how step by step to disable the new feautures of Oracle database 12cR2 “Local undo tablespaces for PDBs” and drop their UNDO Tablespaces

 

 

Bookmark the permalink.
Loading Facebook Comments ...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.