My Oracle Support Banner

PLSQL Procedure That Outputs an ALTER SYSTEM DUMP REDO Statement Based on an Alert Log Timestamp and the Number of Seconds of Redo to Dump (Doc ID 2902425.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version and later
Information in this document applies to any platform.


Often customers or Oracle Support are asked to provide a dump of database redo for diagnostic purposes. From 11g and on, ALTER SYSTEM DUMP REDO can be used to dump a merged collection of redo records from multiple logfiles into a single trace file ordered by SCN. Provided the redologs / archivelogs are present in locations recorded in the database control file, this command provides an easy way to dump redo for all redologs/archives accessible to the database.

When dumping records based on timestamps, the procedure is not straightforward since human-readable timestamps must be converted to an internal database format. An internal script has typically been used for this purpose.

The procedure presented in this document will take an alert log timestamp of the form 'YYYY-MM-DDTHH24:MI:SS.<fractional_seconds>-<time_zone>' and the number of seconds forward from this timestamp that redo needs to be dumped for, and output an equivalent ALTER SYSTEM DUMP REDO statement.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.