In 12.1, all PDB s in one instance can only share the same UNDO table space. In 12.2, they all have their own undo table spaces. This new management mechanism is called the local undo model. At the same time, in previous versions, it has now become a shared undo mode.
I. shared undo to local undo mode
1. Query the current mode
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%' order by con_id;
CON_ID TABLESPACE_NAME
---------- ------------------------------
1 UNDOTBS1
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/shiyu/system01.dbf
/u01/app/oracle/oradata/shiyu/pdbseed/system01.dbf
/u01/app/oracle/oradata/shiyu/sysaux01.dbf
/u01/app/oracle/oradata/shiyu/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/shiyu/undotbs01.dbf
/u01/app/oracle/oradata/shiyu/users01.dbf
/u01/app/oracle/oradata/shiyu/orclpdb/system01.dbf
/u01/app/oracle/oradata/shiyu/orclpdb/sysaux01.dbf
/u01/app/oracle/oradata/shiyu/orclpdb/users01.dbf
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/shiyu/undotbs01.dbf
/u01/app/oracle/oradata/shiyu/orclpdb/system01.dbf
/u01/app/oracle/oradata/shiyu/orclpdb/sysaux01.dbf
/u01/app/oracle/oradata/shiyu/orclpdb/users01.dbf
2. Switch to local undo mode
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 843055104 bytes
Fixed Size 8798360 bytes
Variable Size 591400808 bytes
Database Buffers 239075328 bytes
Redo Buffers 3780608 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 843055104 bytes
Fixed Size 8798360 bytes
Variable Size 591400808 bytes
Database Buffers 239075328 bytes
Redo Buffers 3780608 bytes
Database mounted.
Database opened.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB MOUNTED
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> alter database open;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCLPDB READ WRITE NO3. validation:
SQL> col property_name for a25;
SQL>col property_value for a25;
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%' order by con_id;
CON_ID TABLESPACE_NAME
---------- ------------------------------
1 UNDOTBS1
3 UNDO_1
SQL> alter session set container=ORCLPDB;
Session altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/shiyu/orclpdb/system01.dbf
/u01/app/oracle/oradata/shiyu/orclpdb/sysaux01.dbf
/u01/app/oracle/oradata/shiyu/orclpdb/users01.dbf
/u01/app/oracle/oradata/shiyu/orclpdb/system01_i1_undo.dbf2. Conversion of local undo to shared undo
1. View the current mode
SQL> col property_name for a25; SQL>col property_value for a25; SQL> select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE ------------------------- ------------------------- LOCAL_UNDO_ENABLED TRUE
2. View the undo table space corresponding to ROOT and pdb you defined
SQL> select con_id, tablespace_name from cdb_tablespaces where tablespace_name like 'UNDO%' order by con_id;
CON_ID TABLESPACE_NAME
---------- ------------------------------
1 UNDOTBS1
3 UNDO_13. Switch to shared undo mode
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup upgrade ORACLE instance started. Total System Global Area 843055104 bytes Fixed Size 8798360 bytes Variable Size 591400808 bytes Database Buffers 239075328 bytes Redo Buffers 3780608 bytes Database mounted. Database opened. SQL> alter database local undo off; Database altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 843055104 bytes Fixed Size 8798360 bytes Variable Size 591400808 bytes Database Buffers 239075328 bytes Redo Buffers 3780608 bytes Database mounted. Database opened.
4. validation
QL> col property_name for a25; SQL> col property_value for a25; SQL> select property_name,property_value from database_properties where property_name='LOCAL_UNDO_ENABLED'; PROPERTY_NAME PROPERTY_VALUE ------------------------- ------------------------- LOCAL_UNDO_ENABLED FALSE
Note: Although it is no longer a local undo mode, the existing undo table spaces will not be automatically deleted. If there is an obstacle, delete it manually.
SQL> select con_id, tablespace_name from cdb_tablespaces where tablespace_name like 'UNDO%' order by con_id;
CON_ID TABLESPACE_NAME
---------- ------------------------------
1 UNDOTBS1
3 UNDO_1Delete redundant undo table spaces
With all the old undo table spaces removed, the instance now runs in shared undo mode.SQL> alter session set container=ORCLPDB; Session altered. SQL> select file_name from dba_data_files where tablespace_name = 'UNDOTBS1'; no rows selected SQL> select file_name from dba_data_files where tablespace_name = 'UNDO_1'; FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/shiyu/orclpdb/system01_i1_undo.dbf SQL> drop tablespace undo_1; Tablespace dropped. SQL> select file_name from dba_data_files where tablespace_name = 'UNDO_1'; no rows selected