by From 12.2 onwards, each container in an instance can use its own undo table space and this is called local undo mode. In previous release, all containers in an instance shared the same undo table space in multitenant set up.
Today I upgraded, container database from 12.1 -> 12.2 via DBUA and looked at the UNDO setting. Found that we have to manually switch the undo modes for upgraded instances. So here are the steps.
|
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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED'; no rows selected SQL> SELECT con_id, tablespace_name FROM cdb_tablespaces WHERE tablespace_name LIKE 'UNDO%'; CON_ID TABLESPACE_NAME ---------- ------------------------------ 1 UNDOTBS1 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP UPGRADE; ORACLE instance started. Total System Global Area 3221225472 bytes Fixed Size 8797928 bytes Variable Size 1207959832 bytes Database Buffers 1996488704 bytes Redo Buffers 7979008 bytes Database mounted. Database opened. SQL> ALTER DATABASE LOCAL UNDO ON; Database altered. SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP; ORACLE instance started. Total System Global Area 3221225472 bytes Fixed Size 8797928 bytes Variable Size 1207959832 bytes Database Buffers 1996488704 bytes Redo Buffers 7979008 bytes Database mounted. Database opened. SQL> SELECT property_name, property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE ------------------------------------------------------------------------------ LOCAL_UNDO_ENABLED TRUE SQL> SELECT con_id, tablespace_name FROM cdb_tablespaces WHERE tablespace_name LIKE 'UNDO%'; CON_ID TABLESPACE_NAME ---------- ------------------------------ 1 UNDOTBS1 3 UNDO_1 SQL> exit |
Note: Local undo mode is the default mode in newly created databases/PDB’s.
Mandy
Leave a Reply