In Oracle 12c Release 1 Undo tablespace was always Shared across PDBs. Rollback segment of each pdb was written in Undo tablespace which was created under the Container Root database.
Oracle 12.2 comes with a new Feature that allows Local Undo creation and Shared undo creation.
Local Undo tablespace to PDB helps us in the following way :
- When we want to do Hot Cloning of Pluggable Database.
- Performing near zero downtime PDB relocation.
- While Refreshing PDBs
- Using Proxy PDBs.
We can CDB for Local undo mode at the time of CDB database creation or by altering LOCAL_UNDO_ENABLED property.
LOCAL_UNDO_ENABLED property is disabled by default and only one Undo tablespace is created under Root.
Use the following steps to enable LOCAL_UNDO mode :
Step 1: Check property LOCAL_UNDOENABLED.
SQL> col property_value a10 SQL> col property_value format a10 SQL> col description format a30 SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_V DESCRIPTION -------------------- ---------- ------------------------------ LOCAL_UNDO_ENABLED FALSE true if local undo is enabled
Step 2: To change local_undo mode we need to shutdown database gracefully
Shut down the database.
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down.
Step 3: Start database in upgrade mode
SQL> startup upgrade; ORACLE instance started. Total System Global Area 1241513984 bytes Fixed Size 8792248 bytes Variable Size 788531016 bytes Database Buffers 436207616 bytes Redo Buffers 7983104 bytes Database mounted. Database opened.
Step 4: Use alter database local undo on to enable local undo mode
SQL> alter database local undo on; Database altered.
Step 5: Shut down the database
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down.
Step 6: Start the database in normal mode
SQL> startup ORACLE instance started. Total System Global Area 1241513984 bytes Fixed Size 8792248 bytes Variable Size 788531016 bytes Database Buffers 436207616 bytes Redo Buffers 7983104 bytes Database mounted. Database opened.
Step 7: Check local_undo mode
SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_V DESCRIPTION -------------------- ---------- ------------------------------ LOCAL_UNDO_ENABLED TRUE true if local undo is enabled
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us an Invitation or Follow us:
Telegram Channel: https://t.me/helporacle
Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/
Joel Perez’s LinkedIn: Joel Perez’s Profile
LinkedIn Group: Oracle Cloud DBAAS
Facebook Page: OracleHelp
