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:
- Check the New Feature
- Disable the New Feature
- 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
Google+
Facebook
