在12.1中，所有的在一个实例中的PDB只能共享同一个UNDO表空间。在12.2中它们都有各自的undo表空间了。这种新的管理机制就叫做本地undo模式。与此同时，在之前的版本中现在就成为是共享undo模式。
一.shared undo 转换为local undo模式
1.查询当前模式
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.切换为local undo 模式
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 NO
3.验证：
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.dbf
二、local undo 转换为shared undo模式
1.查看当前模式
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.查看ROOT和自己定义的pdb对应的undo表空间
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
3.切换成shared undo模式
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.验证
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
注意：虽然已经不是本地undo模式了，但是之前存在的undo表空间不会自动删除。如果碍事，要手动删除。
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
删除多余的undo表空间
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
随着所有老的undo表空间被移除了，现在该实例就是运行在共享undo模式上了。