Oracle 12C R2新特性讲解之多租户，支持本地UNDO模式

在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模式上了。