Guaranteed Restore Point: Missing Automatic Expiration for Upgrade Rollback

When the undo tablespace reaches its MAXSIZE limit while a guaranteed restore point (GRP) is active, Oracle cannot reclaim or purge the undo segments required to revert to that point. Unlike standard flashback points, a GRP forces the database to retain all undo data generated since the point was created, regardless of the undo_retention setting.

If the upgrade process involves significant DML or schema changes, the undo tablespace will grow until it hits the hard limit. At this point, any transaction requiring additional undo space will fail (typically with an ORA-01555 or ORA-01650), potentially causing the upgrade to crash. There is no native automated mechanism to drop the GRP once the upgrade is verified.

Recommended Management Strategies

To prevent the upgrade from failing without losing rollback capabilities, follow these strategies:

• Pre-size the Undo Tablespace: Estimate the expected undo volume based on upgrade DML and increase the MAXSIZE or add datafiles to the undo tablespace before starting the upgrade.
• Monitor Undo Growth: Use V$UNDOSPACE and V$UNDOSTAT during the upgrade to track the rate of consumption against the remaining de-allallocated space.
• Manual Expiration: Once the upgrade is fully verified, you must manually execute DROP RESTORE POINT ; immediately to allow the database to reclaim the space segments.

Verification Commands

Use the following commands to monitor the status of your environment during the process:

-- Check for active guaranteed restore points
SELECT name,time, scn, GUARANTEE_FLASHBACK_DATABASE FROM v$restorepoint;

-- Monitor undo tablespace usage and limits
SELECT tablespace_name, bytes/1024/1024 as used_mb, maxbytes/1024/1024 as max_mb, autoextensible 
FROM dba_data_files
WHERE tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO');

Note: This analysis assumes the database is in ARCHIVELOG mode, which is required for GRPs. If using a multitenant architecture, the behavior applies to the PDB level.