LOCAL_UNDO PDB下 undo 名字一样， 没有任何关系

select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME 
and a.con_id=b.con_id and a.CONTENTS='UNDO';

$ sqlplus / as sysdba;

SQL> startup
     ORACLE instance started.

SQL> show pdbs

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        2 PDB$SEED                       READ ONLY  NO
        3 PDB1                               MOUNTED

SQL> alter session set container=PDB1;

SQL> alter pluggable database PDB1 open restricted;

SQL> show pdbs

   CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
        3 PDB1                           READ WRITE YES

SQL> CREATE UNDO TABLESPACE test_undo datafile '...../PDB1/test_undo.dbf'  SIZE 100M AUTOEXTEND ON ;

SQL> ALTER SYSTEM set  UNDO_TABLESPACE='TEST_UNDO' scope=both;

SQL> DROP tablespace UNDOTBS1 including contents and datafiles;  << Drop previous undo tablespace

SQL> alter pluggable database PDB1 close;
SQL> alter pluggable database PDB1 open;

Also Check  : Undo Modes in 12.2 Multitenant Databases - Local and Shared Modes (Doc ID 2169828.1)

本文档介绍 12.2 开始新引入的多租户 DB 场景下的多种 Undo 模式。

详细信息

多租户下的 UNDO 模式

直到 12cR1，在容器内所有的 PDBs，一直使用共享的或者全局的 undo 表空间。从 12cR2 开始，引入了本地化的 undo 功能。当这个功能被启用后，每个容器内针对每个 Open 的实例有自己 Undo 表空间，当这个功能关闭时，整个 CDB 只有一个 Undo 表空间。

Undo 模式，无论是本地化还是共享，都是整个 CDB 的一种属性。所有 PDB 或者是本地化 undo，或者是整个 CDB 是共享 undo 模式。

Oracle 可以在两种模式之间来回切换，而且整个转换过程都是无缝的。

本地 Undo 模式

本地 undo 模式是指，每个容器都有自己的 undo tablespace，对于它打开的每个实例开放。这种模式下，Oracle 会为 CDB 中的每个容器自动创建一个 undo 表空间（数据文件初始化大小可能不同）。对于 RAC 架构的 CDB 来说，每个 PDB 的每个实例都有一个活动的 undo 表空间。

表空间支持了 12.2 的多租户场景下的如下新特性：

• Hot Clone
• Refresh PDB
• PDB Relocate
• Flashback PDB

Oracle 推荐使用本地 Undo 模式。

共享 Undo 模式

共享 Undo 模式意思是 CDB 的一个实例只有一个活动的 undo 表空间。对于 RAC 的 CDB 情况，每个实例都只有一个活动 undo 表空间。

在 12.2 我们将继续支持 shared undo，但是只是为了支持从旧版本升级。

如何检查当前的 undo 模式：

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPE DESCRIPTION

-------------------- ----- -----------------------------------

LOCAL_UNDO_ENABLED TRUE true if local undo is enabled

如何把 CDB 从本地模式转换成共享 UNDO 模式

当一个 CDB 是本地 undo 模式，您可以通过执行 ALTER DATABASE LOCAL UNDO OFF 命令并重启数据库来转换成共享 undo 模式。

当一个 CDB 在共享 undo 模式下，CDB 会忽略所有曾经在本地 undo 模式下创建的 undo 表空间，Oracle 推荐你删除这些本地 undo 表空间。

SQL> startup upgrade

ORACLE instance started. 

Total System Global Area  734003200 bytes

Fixed Size                  8261248 bytes

Variable Size             562037120 bytes

Database Buffers           50331648 bytes

Redo Buffers                8515584 bytes

In-Memory Area            104857600 bytes

Database mounted.

Database opened.

SQL> sho con_name 

CON_NAME

------------------------------

CDB$ROOT

SQL> ALTER DATABASE LOCAL UNDO off; 

Database altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production

[oracle@c1f575 ~]$ sqlplus / as sysdba 

SQL*Plus: Release 12.2.0.0.3 Production on Fri Aug 5 14:09:04 2016 

Copyright (c) 1982, 2016, Oracle.  All rights reserved. 

stConnected to an idle instance. 

SQL

SQL> startup

ORACLE instance started. 

Total System Global Area  734003200 bytes

Fixed Size                  8261248 bytes

Variable Size             562037120 bytes

Database Buffers           50331648 bytes

Redo Buffers                8515584 bytes

In-Memory Area            104857600 bytes

Database mounted.

Database opened.

SQL> select * from database_properties where property_name='LOCAL_UNDO_ENABLED'; 

PROPERTY_NAME

--------------------------------------------------------------------------------

PROPERTY_VALUE

--------------------------------------------------------------------------------

DESCRIPTION

--------------------------------------------------------------------------------

LOCAL_UNDO_ENABLED

FALSE

true if local undo is enabled 

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 

    CON_ID TABLESPACE_NAME

---------- ------------------------------

FILE_NAME

--------------------------------------------------------------------------------

         1 UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs01.dbf

<<删除 pdb 对应的 undo 表空间>>

SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN READ WRITE; 

Pluggable database altered. 

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 

    CON_ID TABLESPACE_NAME

---------- ------------------------------

FILE_NAME

--------------------------------------------------------------------------------

         3 UNDOTBS1

/u01/app/oracle/oradata/orcl/pdb1/undotbs01.dbf

         1 UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs01.dbf 

SQL>  ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 

SQL> drop tablespace UNDOTBS1 including contents and datafiles; 

Tablespace dropped.

如何把 CDB 从共享 UNDO 模式转换成本地 UNDO 模式

当 CDB 在共享 undo 模式下时，你可以通过执行 ALTER DATABASE LOCAL UNDO ON 命令并重启 DB 来转换 undo 到本地 undo 模式。

当 CDB 从共享 undo 模式转换为本地 undo 模式时，Oracle 会自动创建需要的 undo 表空间。

SQL> startup upgrade

ORACLE instance started.

Total System Global Area  734003200 bytes

Fixed Size                  8261248 bytes

Variable Size             562037120 bytes

Database Buffers           50331648 bytes

Redo Buffers                8515584 bytes

In-Memory Area            104857600 bytes

Database mounted.

Database opened.

SQL>  alter database local undo on;

Database altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area  734003200 bytes

Fixed Size                  8261248 bytes

Variable Size             562037120 bytes

Database Buffers           50331648 bytes

Redo Buffers                8515584 bytes

In-Memory Area            104857600 bytes

Database mounted.

Database opened.

SQL> show con_name

 CON_NAME

------------------------------

CDB$ROOT

<<为 PDB$SEED 创建 Undo 表空间，这是一个可选的操作，可以用来客制化其它 PDBs 的 Undo 表空间>>

SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;

Pluggable database altered.

SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

Session altered.

SQL> CREATE UNDO TABLESPACE test_undo datafile '/u01/app/oracle/oradata/orcl/pdb1/testundo.dbf' SIZE 10M AUTOEXTEND ON ;

Tablespace created. 

SQL> alter system set  UNDO_TABLESPACE='TEST_UNDO' scope=both; 

System altered. 

SQL> show parameter undo 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

temp_undo_enabled                    boolean     FALSE

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      TEST_UNDO

SQL>  ALTER SESSION SET CONTAINER=CDB$ROOT; 

Session altered. 

SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY FORCE; 

Pluggable database altered. 

<<将 PDB 启动到 write 模式>>

SQL> ALTER PLUGGABLE DATABASE PDB1  OPEN READ WRITE; 

Pluggable database altered. 

SQL>  show parameter undo 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

temp_undo_enabled                    boolean     FALSE

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1 

SQL>  select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 

    CON_ID TABLESPACE_NAME

---------- ------------------------------

FILE_NAME

--------------------------------------------------------------------------------

         3 UNDO_1

/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf  << Undo 表空间被自动创建  用的一样的名字

         1 UNDOTBS1

/u01/app/oracle/oradata/orcl/undotbs01.dbf

<<  切换到 PDB>> 

SQL> ALTER SESSION SET CONTAINER=PDB1; 

Session altered. 

SQL> select a.CON_ID,a.TABLESPACE_NAME,b.FILE_NAME from cdb_tablespaces a,CDB_DATA_FILES b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.con_id=b.con_id and a.CONTENTS='UNDO'; 

    CON_ID TABLESPACE_NAME

---------- ------------------------------

FILE_NAME

--------------------------------------------------------------------------------

         3 UNDO_1 << Undo 表空间被自动创建

/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf

PDB1 的 Alert Log File

2016-08-05T14:44:52.406849+05:30

ALTER PLUGGABLE DATABASE PDB1  OPEN READ WRITE

2016-08-05T14:44:54.137062+05:30

PDB1(3):Endian type of dictionary set to little

PDB1(3):Autotune of undo retention is turned on.

PDB1(3):Undo initialization finished serial:0 start:2394185293 end:2394185366 diff:73 ms (0.1 seconds)

PDB1(3):Database Characterset for PDB1 is AL32UTF8

PDB1(3):Opatch validation is skipped for PDB PDB1 (con_id=0)

2016-08-05T14:44:55.457499+05:30

PDB1(3):Opening pdb with no Resource Manager plan active

PDB1(3):CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE '/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 8192 MAXSIZE 34359721984 ONLINE

2016-08-05T14:44:58.688075+05:30

PDB1(3):[18452] Successfully onlined Undo Tablespace 5.

PDB1(3):Completed: CREATE SMALLFILE UNDO TABLESPACE undo_1 DATAFILE '/u01/app/oracle/oradata/orcl/pdb1/pdb1_i1_undo.dbf' SIZE 10485760 AUTOEXTEND ON NEXT 8192 MAXSIZE 34359721984 ONLINE

Pluggable database PDB1 opened read write

Completed: ALTER PLUGGABLE DATABASE PDB1  OPEN READ WRITE