ITPub博客

首页 > 数据库 > Oracle > 12C关于CDB、PDB 回滚undo表空间的总结

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

原创 Oracle 作者:lusklusklusk 时间:2020-02-11 23:36:42 0 删除 编辑

官方文档

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2675189/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 10G OCP、11G OCM, 8年以上DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    392
  • 访问量
    551919