Shulou(Shulou.com)06/01 Report--

Official document

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

In version 1.12.1, all PDB in the same instance can only share the same undo tablespace.

2.12.2 each PDB uses its own undo table space by default. See database_properties.property_name='LOCAL_UNDO_ENABLED', a new management mechanism called local undo schema.

When PDB is in shared undo mode, PDB can create a undo tablespace, but the tablespace cannot be found, nor can the data file in the tablespace.

4. The parameter TEMP_UNDO_ENABLED defaults to false, and when this parameter is set to true, it indicates All undo for operations on temporary objects is deemed temporary. The Undo records generated by the 11G temporary table are stored in the Undo table space. 12C once the TEMP_UNDO_ENABLED parameter is set to true, it means that the Undo records generated by the temporary table are stored in the temporary table space.

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.pdbroomname UNDO%' b.conformidlega.tablespaceplaynamerea.fileaccountname 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

Switching to shared undo mode, it is found that undo tablespaces can still be created in PDB, but the newly created undo tablespaces cannot be found in cdb_tablespaces. Pocp999 this PDB has created a new UNDO903 tablespaces, but this tablespace cannot be found, nor can the newly created undo files.

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.pdbroomname UNDO%' b.conformidlega.tablespaceplaynamerea.fileaccountname 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 size

Tablespace created.

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

CON_ID TABLESPACE

--

5 UNDOTBS1

5 UNDO902

SQL > select b.pdbroomname UNDO%' b.conformidlega.tablespaceplaynamerea.fileaccountname 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

Switch back to local undo mode. The undo tablespace created in PDB in shared mode mode is still not visible.

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.