oracle 12.2 local undo

12.1引入temp undo概念
12.2引入local undo，也就是每个pdb拥有自己的undo表空间（以前版本共用一个undo表空间），rac中每个pdb每个实例都有自己的undo，使用local undo的益处：
1、隔离后，减少undo表空间的争用，同时方便拔插
2、flashback a PDB
3、point-in-time recovery PDB
4、relocating a PDB or cloning a PDB that is in open read/write mode
所以推荐使用local undo。

shared undo:
[img]http://www.orasql.com/blog//uploadfiles/d030128e-605d-4174-b6b6-b8647ab142c8_98810.png[/img]
local undo:
[img]http://www.orasql.com/blog//uploadfiles/79477bc2-b503-44d3-88b3-0f2ff2810b7c_11477.png[/img]
在DBCA时会有local undo选项，且默认勾选
[img]http://www.orasql.com/blog//uploadfiles/3de65706-ed57-4473-bd47-cb2f4df597a8_75643.png[/img]
关闭local undo
[code]SQL> col PROPERTY_NAME for a20
SQL> col PROPERTY_VALUE for a30
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 name from v$datafile;

NAME
-----------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/orasql/PDB1/system01.dbf
/u02/app/oracle/oradata/orasql/PDB1/sysaux01.dbf
/u02/app/oracle/oradata/orasql/PDB1/undotbs01.dbf
/u02/app/oracle/oradata/orasql/PDB1/users01.dbf
/u04/app/oracle/oradata/orasql/PDB1/oradata1.dbf
/u02/app/oracle/oradata/orasql/PDB1/oraind1.dbf

6 rows selected.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2667577344 bytes
Fixed Size 8796336 bytes
Variable Size 922748752 bytes
Database Buffers 1728053248 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> SHOW 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> startup;
ORACLE instance started.

Total System Global Area 2667577344 bytes
Fixed Size 8796336 bytes
Variable Size 922748752 bytes
Database Buffers 1728053248 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> alter session set container=pdb1;

Session altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/orasql/undotbs01.dbf
/u02/app/oracle/oradata/orasql/PDB1/system01.dbf
/u02/app/oracle/oradata/orasql/PDB1/sysaux01.dbf
/u02/app/oracle/oradata/orasql/PDB1/undotbs01.dbf
/u02/app/oracle/oradata/orasql/PDB1/users01.dbf
/u04/app/oracle/oradata/orasql/PDB1/oradata1.dbf
/u02/app/oracle/oradata/orasql/PDB1/oraind1.dbf

7 rows selected.

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
-------------------- ------------------------------
LOCAL_UNDO_ENABLED FALSE[/code]
关闭后在PDB中查询undo为CDB中共享的undo表空间。
打开local undo：
[code]SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 2667577344 bytes
Fixed Size 8796336 bytes
Variable Size 922748752 bytes
Database Buffers 1728053248 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>
SQL> startup
ORACLE instance started.

Total System Global Area 2667577344 bytes
Fixed Size 8796336 bytes
Variable Size 922748752 bytes
Database Buffers 1728053248 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY YES
3 PDB1 READ WRITE NO
SQL> alter session set container =pdb1;

Session altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/orasql/PDB1/system01.dbf
/u02/app/oracle/oradata/orasql/PDB1/sysaux01.dbf
/u02/app/oracle/oradata/orasql/PDB1/undotbs01.dbf
/u02/app/oracle/oradata/orasql/PDB1/users01.dbf
/u04/app/oracle/oradata/orasql/PDB1/oradata1.dbf
/u02/app/oracle/oradata/orasql/PDB1/oraind1.dbf

6 rows selected.

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE
-------------------- ------------------------------
LOCAL_UNDO_ENABLED TRUE

SQL>[/code]
打开loacl undo后，自动为当前的PDB创建undo表空间，同时新创建pdb，自动创建自己的undo表空间
[code]SQL> conn / as sysdba
Connected.
SQL> create pluggable database pdb2 admin user pdbadmin identified by oracle;

Pluggable database created.

SQL> alter session set container =pdb2
2 ;

Session altered.

SQL> select name from v$datafile;

NAME
---------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/ORASQL/418A7FFE42A76015E053E2AB100A5EE4/datafile/o1_mf_system_d2wqh4mg_.dbf
/u02/app/oracle/oradata/ORASQL/418A7FFE42A76015E053E2AB100A5EE4/datafile/o1_mf_sysaux_d2wqh4nb_.dbf
/u02/app/oracle/oradata/ORASQL/418A7FFE42A76015E053E2AB100A5EE4/datafile/o1_mf_undotbs1_d2wqh4nf_.dbf[/code]
可以通过配置pdb$seed的undo，从而使根据seed模板创建的pdb后undo表空间的名字、大小等：
[code]SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;

Warning: PDB altered with errors.

SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

Session altered.

SQL> CREATE UNDO TABLESPACE seedundots1
2 DATAFILE 'seedundotbs_1a.dbf'
3 SIZE 10M AUTOEXTEND ON
4 RETENTION GUARANTEE;

Tablespace created.

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> alter system set undo_tablespace='seedundots1';

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 seedundots1
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

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

Warning: PDB altered with errors.

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> create pluggable database pdb3 admin user pdbadmin identified by oracle;

Pluggable database created.
SQL> alter pluggable database pdb3 open;

Warning: PDB altered with errors.

SQL> alter session set container=pdb3;

Session altered.
SQL> col name for a140
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/ORASQL/418ADCBFBC787F28E053E2AB100A20FB/datafile/o1_mf_system_d2wrzp8w_.dbf
/u02/app/oracle/oradata/ORASQL/418ADCBFBC787F28E053E2AB100A20FB/datafile/o1_mf_sysaux_d2wrzp8z_.dbf
/u02/app/oracle/oradata/ORASQL/418ADCBFBC787F28E053E2AB100A20FB/datafile/o1_mf_undotbs1_d2wrzp90_.dbf
/u02/app/oracle/oradata/ORASQL/418ADCBFBC787F28E053E2AB100A20FB/datafile/o1_mf_seedundo_d2wrzp94_.dbf

TABLESPACENAME Totle_size(G) Free_space(G) Used_space(G) Used_percent%
------------------------------ ------------- ------------- ------------- -------------
SYSTEM .28 0 .28 99.66
SYSAUX .55 0 .55 99.52
SEEDUNDOTS1 .01 .01 0 29.38
UNDOTBS1 .17 .17 0 2.29

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string seedundots1
SQL> drop tablespace UNDOTBS1;

Tablespace dropped.[/code]
或者是直接在pdb$seed中删除undotbs1，之后创建的pdb都只有SEEDUNDOTS1表空间：
[code]SQL> ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;

Pluggable database altered.
SQL> alter session set container=PDB$SEED;

SQL> drop tablespace UNDOTBS1 ;

Tablespace dropped.

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

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

Pluggable database altered.

SQL> create pluggable database pdb4 admin user pdbadmin identified by oracle;

Pluggable database created.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO
6 PDB4 MOUNTED
SQL> alter pluggable database pdb4 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 PDB3 READ WRITE NO
6 PDB4 READ WRITE NO
SQL> alter session set container=pdb4;

Session altered.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/ORASQL/418E5E1835CC5261E053E2AB100A984D/datafile/o1_mf_system_d2x7p4y5_.dbf
/u02/app/oracle/oradata/ORASQL/418E5E1835CC5261E053E2AB100A984D/datafile/o1_mf_sysaux_d2x7p4yb_.dbf
/u02/app/oracle/oradata/ORASQL/418E5E1835CC5261E053E2AB100A984D/datafile/o1_mf_seedundo_d2x7p4yg_.dbf

TABLESPACENAME Totle_size(G) Free_space(G) Used_space(G) Used_percent%
------------------------------ ------------- ------------- ------------- -------------
SYSAUX .55 0 .55 99.67
SYSTEM .28 0 .28 99.66
SEEDUNDOTS1 .01 .01 0 33.13

SQL>[/code]

官方文档解释
Local Undo Mode

Local undo mode means that each container has its own undo tablespace for every instance in which it is open. In this mode, Oracle Database automatically creates an undo tablespace for every container in the CDB. For an Oracle RAC CDB, there is one active undo tablespace for each instance for each PDB in local undo mode.
Local undo mode provides increased isolation for each container and improves the efficiency of some operations, such as unplugging the container or performing point-in-time recovery on the container. In addition, local undo mode is required for some operations to be supported, such as relocating a PDB or cloning a PDB that is in open read/write mode.
When a CDB is in local undo mode, the following applies:
Any user who has the appropriate privileges for the current container can create an undo tablespace for the container.
Undo tablespaces are visible in static data dictionary views and dynamic performance (V$) views in every container in the CDB.

Shared Undo Mode

Shared undo mode means that there is one active undo tablespace for a single-instance CDB. For an Oracle RAC CDB, there is one active undo tablespace for each instance.
When a CDB is in shared undo mode, the following applies:
Only a common user who has the appropriate privileges and whose current container is the CDB root can create an undo tablespace.
When the current container is not the CDB root, an attempt to create an undo tablespace fails and returns an error.
Undo tablespaces are visible in static data dictionary views and dynamic performance (V$) views when the current container is the CDB root. Undo tablespaces are visible only in dynamic performance views when the current container is a PDB, an application root, or an application PDB.

Note:
When you change the undo mode of a CDB, the new undo mode applies to an individual container the first time the container is opened after the change.
When you change the undo mode of a CDB, containers in the CDB cannot flash back to a time or SCN that is prior to the change.

详细参考官方文档：
http://docs.oracle.com/database/122/ADMIN/administering-a-cdb-with-sql-plus.htm#ADMIN-GUID-24EA5811-94F0-4EEC-864F-23AEF48F2D51