Hi Dears,
In this article, we will see How to Switch/Recreate Undo Tablespace at PDB Level(12cR2)
In Oracle Database 12c Release 1 all containers in an instance shared the same undo tablespace.
In Oracle 12c Release 2 each container in an instance can use its own undo tablespace.
This new undo management mechanism is called local undo mode, Local undo mode is the default mode in newly created databases
You will probably only need to consider switching undo modes for upgraded instances.
Local undo mode:
In this mode, each PDB in container will have their own active undo tablespace
Share Undo mode:
In this mode, Every PDB will use only one undo tablespace for the entire instance.
Check local undo in database
|
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 |
SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------------------ LOCAL_UNDO_ENABLED TRUE show parameter undo; NAME TYPE VALUE ----------------- ------- -------------------------------- temp_undo_enabled boolean FALSE undo_management string AUTO undo_retention integer 5 undo_tablespace string UNDOTBS1 SQL> startup SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- --------------------------------- 2 PDB$SEED READ ONLY NO 3 PDB2 MOUNTED SQL> alter session set container=PDB1; SQL> alter pluggable database PDB1 open restricted; SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ----------------------------- 3 PDB1 READ WRITE YES SQL > CREATE BIGFILE tablespace UNDOTBS02 datafile ‘/u01/app/oracle/PDB1/UNDOTBS01.dbf’ size 100m auto extend on; SQL > ALTER SYSTEM set UNDO_TABLESPACE= ‘UNDOTBS02’ scope=both; SQL > DROP tablespace UNDOTBS1 including contents and datafiles; SQL> alter pluggable database PDB1 close; SQL> alter pluggable database PDB1 open; |
CONVERT CDB FROM LOCAL TO SHARED UNDO MODE(12CR1)
When a CDB is in local undo mode, you can change it to use shared undo mode by issuing an ALTER DATABASE LOCAL UNDO OFF statement and restarting the database.
|
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 |
SQL> startup upgrade SQL> sho con_name CON_NAME SQL> ALTER DATABASE LOCAL UNDO off; SQL > shutdown immediate SQL> startup SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE ------------------------------ ------------------------------ LOCAL_UNDO_ENABLED FALSE SQL> 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 SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE; Pluggable database altered. SQL> 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 -------------------------------------------------------------------------------------- 3 UNDOTBS1 /u01/app/oracle/oradata/orcl/pdb1/undotbs01.dbf 1 UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf |
After converting to shared mode we have to drop undo tablespaces manually, by connecting to the respective database.
|
1 2 3 4 |
SQL> ALTER SESSION SET CONTAINER=PDB1; Session altered. SQL> drop tablespace UNDOTBS1 including contents and datafiles; Tablespace dropped. |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Loading...
