12C關於CDB、PDB 回滾undo表空間的總結

官方文件

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_h41j8fnt_.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_h41j8fnt_.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.