For permanent-table transactions in Oracle Database 19c, establish the CDB undo mode, the container that owns the undo storage, and the undo tablespace selected for the current instance. These observations support useful lifecycle and storage decisions.
Oracle DBA Lesson 64 Part 1 — Find Which Container Owns the Undo
Local and shared undo
| Mode | Ownership in the single-instance example |
|---|---|
| Local | Root and each PDB manage their own undo tablespaces |
| Shared | One active undo tablespace in root serves the CDB |
The mode applies to the CDB. In local mode, root still needs undo for root work. Application table data remains in its application container in either mode. Undo tablespaces have physical data files on disk. This is storage ownership; separate undo management still depends on finite underlying storage capacity.
The diagrams trace allocation scope rather than the physical travel of an undo block from memory to disk. Cached undo blocks can be in the database buffer cache in the SGA. Persistent undo storage belongs to undo tablespace data files. Temporary undo is outside this permanent-table example.
In RAC, account for each instance: local mode requires undo for each container on each instance where it is open; shared mode has one active undo tablespace per instance. Extend the single-instance picture before applying it to RAC.
See Administering a CDB for undo modes, multitenant architecture for storage and undo, and managing undo.
Establish mode in root
Confirm the current user and container, then read the CDB property:
SHOW USER
SHOW CON_NAME
SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';| PROPERTY_NAME | PROPERTY_VALUE |
|---|---|
| LOCAL_UNDO_ENABLED | TRUE |
TRUE selects local undo. FALSE selects shared undo. Use the value the database returns. An unexpected or missing result needs investigation before you conclude a mode. Creation tools and scripts can choose either mode, so read the database itself.
See DATABASE_PROPERTIES.
Map ownership
From root, list each undo tablespace with the container that owns it:
SELECT t.con_id, c.name, t.tablespace_name
FROM cdb_tablespaces t
JOIN v$containers c ON c.con_id = t.con_id
WHERE t.contents = 'UNDO'
ORDER BY t.con_id, t.tablespace_name;CONTENTS selects the storage type. CON_ID identifies its container; the join supplies the name.
| CON_ID | NAME | TABLESPACE_NAME |
|---|---|---|
| 1 | CDB$ROOT | UNDO_ROOT |
| 2 | PDB$SEED | UNDO_SEED |
| 3 | APP_PDB | UNDO_APP |
| 3 | APP_PDB | UNDO_OLD |
Names and container IDs vary. APP_PDB stands for the intended application container. Both of its rows are tablespaces owned by that container. The list does not choose between them. The active setting below identifies the selected tablespace.
Container-data visibility depends on grants, the common user's CONTAINER_DATA, and container open or restricted state. A missing row requires these checks. In shared mode, read the undo inventory from root; undo has different static-view visibility from within a PDB. After a conversion to shared mode, old local undo tablespaces may remain unused. Their presence alone does not establish the effective mode or which tablespace is in use.
See DBA_TABLESPACES, V$CONTAINERS, and viewing information about CDBs and PDBs for container-data visibility.
Identify selected undo
For the demonstrated local-mode case:
ALTER SESSION SET CONTAINER = APP_PDB;
SHOW CON_NAME
SHOW PARAMETER undo_tablespaceConfirm APP_PDB before interpreting the parameter.
| NAME | TYPE | VALUE |
|---|---|---|
| undo_tablespace | string | UNDO_APP |
This case selects UNDO_APP for that container and instance. The second inventory row, UNDO_OLD, does not become active simply because it is listed. Multiple undo tablespaces may exist while one is selected. A recent tablespace switch can leave old active transactions completing in the previous tablespace.
In shared mode, read the root's selected undo tablespace for the instance. Record mode, container scope, instance, and selection together. If the parameter is blank or the observations conflict, stop and investigate the undo configuration and the transaction or rollback-segment information. Do not guess from a tablespace name.
ALTER SESSION SET CONTAINER changes this session's current container; it does not convert the CDB's undo mode. A common user needs the applicable SET CONTAINER privilege and access to the destination. Restore the original container when finished, or disconnect after resolving any session transactions.
See UNDO_TABLESPACE and ALTER SESSION.
Use the evidence for a clone
For an Oracle 19c clone whose source remains READ WRITE, verify local undo and ARCHIVELOG, plus the relevant cloning requirements. A TRUE undo property satisfies only the undo condition. In root, the logging check can include:
SELECT log_mode FROM v$database;Preserve the archived logs required over the cloning interval. Also assess source and target compatibility, storage and files, privileges, and encryption or keystore requirements where they apply.
If the source CDB uses shared undo, the regular cloning method requires the source PDB to be opened READ ONLY. Plan that state and the application impact through the cloning procedure. A closed or offline unplug-and-plug or file-move procedure has its own conditions. Do not apply a hot-clone prerequisite to every lifecycle operation.
Changing undo mode is a CDB-level operation with a root procedure in upgrade mode, a shutdown, and a restart. It can create or retire undo storage and affects flashback boundaries. Plan capacity, downtime, and recovery before converting.
See cloning a PDB. Undo-mode conversion is part of Administering a CDB.
Recap
For permanent-table transactions in Oracle Database 19c, name the CDB undo mode, the container that owns the undo storage, and the undo tablespace selected for the current instance. LOCAL_UNDO_ENABLED = TRUE means local undo: root and each PDB manage their own undo tablespaces. FALSE means shared undo: one active undo tablespace in root serves the CDB. Root still owns undo in local mode, so a root undo tablespace does not mean every PDB shares it. Application table data stays in its application container in either mode. Persistent undo is in undo tablespace data files. Cached undo blocks can be in the database buffer cache in the SGA. Temporary undo is outside this case. In RAC, local mode needs undo for each container on each instance where it is open, and shared mode has one active undo tablespace per instance.
Read the property in root. Map rows with CONTENTS = 'UNDO' from CDB_TABLESPACES joined to V$CONTAINERS. A missing row can be grants, CONTAINER_DATA, or open or restricted state. In local mode, UNDO_TABLESPACE in the application container names the selection for that instance. UNDO_OLD can be listed beside UNDO_APP and still be unused. A recent switch can leave older transactions completing in the previous tablespace. In shared mode, read the inventory and the selection from root. Old local undo tablespaces can remain after a conversion to shared mode. ALTER SESSION SET CONTAINER changes only the session's container. A READ WRITE clone needs local undo and ARCHIVELOG, and the undo property covers only the undo condition. Shared undo uses a READ ONLY source for the regular cloning method. Changing the mode is a separate CDB operation in upgrade mode, with shutdown and restart, and it can change undo storage and flashback boundaries.
No comments:
Post a Comment