12C关于CDB、PDB 回滚undo表空间的总结

官方文档

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/administering-a-cdb-with-sql-plus.html#GUID-8F8B2FF8-7FA7-40CD-8AA5-ACABCD3964D8

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/TEMP_UNDO_ENABLED.html#GUID-E2A01A84-2D63-401F-B64E-C96B18C5DCA6

1、12.1版本时同一个实例中的所有PDB只能共享同一个undo表空间。

2、12.2开始每个PDB默认使用自己的undo表空间了，参见database_properties.property_name='LOCAL_UNDO_ENABLED',这种新的管理机制就叫做local undo模式。

3、12.2是shared undo模式时，PDB可以创建undo表空间，但是这个表空间查不到，表空间的数据文件也查不到

4、12.2参数TEMP_UNDO_ENABLED默认为false，该参数设置为true时表示All undo for operations on temporary objects is deemed temporary。11G版本临时表生成的Undo记录是存储在Undo表空间,12C一旦设置TEMP_UNDO_ENABLED参数为true则表示临时表生成的Undo记录存储做临时表空间中

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

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION

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

LOCAL_UNDO_ENABLED             TRUE                           true if local undo is enabled

SQL> select con_id,tablespace_name from cdb_tablespaces where contents='UNDO';

    CON_ID TABLESPACE_NAME

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

         1 UNDOTBS1

         1 UNDO102

         4 UNDOTBS1

         5 UNDOTBS1

         5 UNDO902

         6 UNDOTBS1

SQL> select b.pdb_name,b.con_id,a.tablespace_name,a.file_name from cdb_data_files a,cdb_pdbs b where a.con_id=b.con_id and a.tablespace_name like '%UNDO%';

PDB_NAME       CON_ID TABLESPACE FILE_NAME

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

POCP2               4 UNDOTBS1   /u02/data/OCP/ABC/datafile/o1_mf_undotbs1_h0pqtc7m_.dbf

POCP999             5 UNDOTBS1   /u02/data/test/undotbs42b.db

POCP999             5 UNDO902    /u02/data/test/undo902.dbf

TEST123             6 UNDOTBS1   /u02/data/OCP/XYZ/datafile/o1_mf_undotbs1_h51j8fnt_.dbf

切换成shared undo模式，发现PDB里面还是可以创建undo表空间，但是无法在cdb_tablespaces查到新建的undo表空间，pocp999这个PDB新建了UNDO903的表空间，但是这个表空间查不到，新建的undo文件也查不到

SQL> shutdown immediate;

SQL> startup upgrade;

SQL> ALTER DATABASE LOCAL UNDO OFF;

SQL> shutdown immediate;

SQL> startup;

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 con_id,tablespace_name from cdb_tablespaces where contents='UNDO';

    CON_ID TABLESPACE

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

         1 UNDOTBS1

         1 UNDO102

         4 UNDOTBS1

         5 UNDOTBS1

         5 UNDO902

         6 UNDOTBS1

SQL> select b.pdb_name,b.con_id,a.tablespace_name,a.file_name from cdb_data_files a,cdb_pdbs b where a.con_id=b.con_id and a.tablespace_name like '%UNDO%';

PDB_NAME       CON_ID TABLESPACE FILE_NAME

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

POCP2               4 UNDOTBS1   /u02/data/OCP/ABC/datafile/o1_mf_undotbs1_h0pqtc7m_.dbf

POCP999             5 UNDOTBS1   /u02/data/test/undotbs42b.db

POCP999             5 UNDO902    /u02/data/test/undo902.dbf

TEST123             6 UNDOTBS1   /u02/data/OCP/XYZ/datafile/o1_mf_undotbs1_h51j8fnt_.dbf

SQL> alter session set container=pocp999;

SQL> create undo tablespace UNDO903 datafile '/u02/data/test/undo903.dbf' size 10M;

Tablespace created.

SQL> select con_id,tablespace_name from cdb_tablespaces where contents='UNDO';

    CON_ID TABLESPACE

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

         5 UNDOTBS1

         5 UNDO902

SQL> select b.pdb_name,b.con_id,a.tablespace_name,a.file_name from cdb_data_files a,cdb_pdbs b where a.con_id=b.con_id and a.tablespace_name like '%UNDO%';

PDB_NAME       CON_ID TABLESPACE FILE_NAME

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

POCP999             5 UNDOTBS1   /u02/data/test/undotbs42b.db

POCP999             5 UNDO902    /u02/data/test/undo902.dbf

SQL> alter session set container=CDB$ROOT;

SQL> select con_id,tablespace_name from cdb_tablespaces where contents='UNDO';

    CON_ID TABLESPACE_NAME

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

         1 UNDOTBS1

         1 UNDO102

         4 UNDOTBS1

         5 UNDOTBS1

         5 UNDO902

         6 UNDOTBS1

SQL> shutdown immediate;

SQL> startup

SQL> select con_id,tablespace_name from cdb_tablespaces where contents='UNDO';

    CON_ID TABLESPACE_NAME

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

         1 UNDOTBS1

         1 UNDO102

         4 UNDOTBS1

         5 UNDOTBS1

         5 UNDO902

         6 UNDOTBS1

再切换回local undo模式，之前shared mode模式时在PDB里面创建的undo表空间还是看不到

SQL> shutdown immediate;

SQL> startup upgrade;

SQL> ALTER DATABASE LOCAL UNDO ON;

SQL> shutdown immediate;

SQL> startup;

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

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION

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

LOCAL_UNDO_ENABLED             TRUE                           true if local undo is enabled

SQL> select con_id,tablespace_name from cdb_tablespaces where contents='UNDO';

    CON_ID TABLESPACE_NAME

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

         1 UNDOTBS1

         1 UNDO102

         4 UNDOTBS1

         5 UNDOTBS1

         5 UNDO902

         6 UNDOTBS1

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.

TEMP_UNDO_ENABLED determines whether transactions within a particular session can have a temporary undo log.

When TEMP_UNDO_ENABLED is set to true and the COMPATIBLE initialization parameter is set to 12.0.0, this feature is enabled. ...All undo for operations on temporary objects is deemed temporary.