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:

Goal

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.

Solution

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