The ALTER SYSTEM DUMP command in Oracle is utilized to generate detailed dumps of various database components, such as datafiles and redo log files, for diagnostic and troubleshooting purposes.
Alireza Kamrani
28/March/2025
Dump commands and it's usage:
1. Dumping Datafile Blocks
To inspect the contents of specific blocks within a datafile, you can use the following commands:
• Dump a Single Block:
ALTER SYSTEM DUMP DATAFILE <file_number> BLOCK <block_number>;
Example:
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 10;
This command dumps block number 10 from datafile 3.
• Dump a Range of Blocks:
ALTER SYSTEM DUMP DATAFILE <file_number> BLOCK MIN <start_block> BLOCK MAX <end_block>;
Example:
ALTER SYSTEM DUMP DATAFILE 3 BLOCK MIN 10 BLOCK MAX 12;
This command dumps blocks 10 through 12 from datafile 3.
2. Dumping Redo Log Files
To analyze the contents of redo log files, you can use the following commands:
• Dump an Entire Redo Log File:
ALTER SYSTEM DUMP LOGFILE '<path_to_redo_log_file>';
Example:
ALTER SYSTEM DUMP LOGFILE '/u01/app/oradata/TEST/redo01.log';
This command dumps the contents of the specified redo log file.
• Dump Redo Records Within a Specific SCN Range:
ALTER SYSTEM DUMP REDO SCN MIN <start_scn> SCN MAX <end_scn>;
Example:
ALTER SYSTEM DUMP REDO SCN MIN 100000 SCN MAX 200000;
This command dumps redo records with SCNs between 100,000 and 200,000.
✴️Considerations:
• The output of these commands is written to trace files located in the directory specified by the USER_DUMP_DEST initialization parameter.
• These commands are primarily intended for diagnostic purposes and should be used with caution, especially in production environments, as they can generate large amounts of output.
• Ensure you have the necessary administrative privileges to execute these commands.
🔻Also one of applicable and useful commamd for DBA is taking dump of Redo logs to troubleshooting and diagnostic, in following section you can find more details about this solutions.
🔆 ALTER SYSTEM DUMP REDO
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 [19.13 and later]
NOTE: the following two statements must be run prior to running the procedure in order to have this work properly:
set serveroutput on
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
As an example, let's say we wish to dump all of the database redo for 20 seconds starting at timestamp '2024-09-24T12:51:40.386098-04:00'. Assuming that procedure DUMP_REDO has already been created in the schema, we would run:
SQL> set serveroutput on
SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> exec DUMP_REDO('2024-09-24T12:51:40.386098-04:00',20);
-- Run the following statements and upload the file produced
-- in the diagnostic_dest /trace directory:
----------------------------------------------
alter session set tracefile_identifier = 'REDO_DUMP';
alter system dump redo time min 1116247600 time max 1116247920;
PL/SQL procedure successfully completed.
The code for the DUMP_REDO procedure can be found below.
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:
create or replace procedure DUMP_REDO (incident_date IN varchar2, seconds_forward IN number) is
redo_day varchar2(20);
redo_time varchar2(20);
redo_date_char varchar2(35);
redo_date date;
rd_back date;
rd_back_char varchar2(35);
rd_year number;
rd_month number;
rd_day number;
rd_back_hour number;
rd_back_min number;
rd_back_sec number;
rd_forward date;
rd_forward_char varchar2(35);
rd_forward_hour number;
rd_forward_min number;
rd_forward_sec number;
time_back number;
time_forward number;
stmt1 varchar2(35) := 'alter system dump redo time min ';
stmt2 varchar2(10) := ' time max ';
stmt3 varchar2(1) := ';';
stmt varchar2(120);
begin
redo_day := substr(incident_date, 1, 10);
redo_time := substr(incident_date, 12, 8);
redo_date_char := concat(concat(redo_day, ' '), redo_time);
rd_year := to_number(substr(incident_date, 1, 4));
rd_month := to_number(substr(incident_date, 6, 2));
rd_day := to_number(substr(incident_date, 9, 2));
redo_date := to_date(redo_date_char, 'YYYY-MM-DD HH24:MI:SS');
rd_back := redo_date - 5/1440;
rd_back_char := to_char(rd_back);
rd_back_hour := to_number(substr(rd_back_char, 13, 2));
rd_back_min := to_number(substr(rd_back_char, 16, 2));
rd_back_sec := to_number(substr(rd_back_char, 19, 2));
rd_forward := redo_date + seconds_forward/86400;
rd_forward_char := to_char(rd_forward);
rd_forward_hour := to_number(substr(rd_forward_char, 13, 2));
rd_forward_min := to_number(substr(rd_forward_char, 16, 2));
rd_forward_sec := to_number(substr(rd_forward_char, 19, 2));
time_back := ((((((rd_year-1988))*12+(rd_month- 1))*31+(rd_day-1))*24+(rd_back_hour))*60+(rd_back_min))*60+(rd_back_sec);
time_forward := ((((((rd_year-1988))*12+(rd_month- 1))*31+(rd_day-1))*24+(rd_forward_hour))*60+(rd_forward_min))*60+(rd_forward_sec);
stmt := concat( concat( concat( concat (stmt1, time_back), stmt2), time_forward), stmt3);
dbms_output.put_line(chr(10));
dbms_output.put_line('-- Run the following statements and upload the file produced');
dbms_output.put_line('-- in the diagnostic_dest /trace directory:');
dbms_output.put_line('------------------------------------');
dbms_output.put_line('alter session set tracefile_identifier = ''REDO_DUMP'';');
dbms_output.put_line(stmt);
end;
/
***********************
To minimise the volume dumped by the “alter system dump logfile” command and make it easier to find the bit I wanted to see I used to “switch logfile” just before (and sometimes just after) the statement I was investigating.
With the advent of pluggable databases the “switch logfile” command now raises Oracle error:
“ORA-65040: operation not allowed from within a pluggable database” so I had to change the strategy.
This is just a brief note (echoing a footnote to an older note) of the approach I now use:
column current_scn new_value start_scn
select to_char(current_scn,'9999999999999999') current_scn from v$database;
accept X prompt "Do something interesting in another session then press return"
column current_scn new_value end_scn
select to_char(current_scn,'9999999999999999') current_scn from v$database;
alter session set tracefile_identifier='sometextyoulike';
alter system dump redo scn min &start_scn scn max &end_scn ;
alter session set tracefile_identifier='';
The list of options for the dump has been extended since I published the earlier note on dumping the log file and now (19.11.0.0) allows the following options (using C notation for the type of the variables you supply to each parameter):
rdba min %d rdba max %d tablespace_no %d
dba min %u %u dba max %u %u
securefile_dba %u %u
length %d
time min %d
time max %d
layer %d
opcode %d
scn min %llu
scn max %llu
xid %d %d %d
objno %u
con_id %d
skip corruption
If you try to restrict the dump on objno (object id) or xid (transaction id) then the trace file will skip any redo records generated by private threads / in-memory undo and report the text: “Skipping IMU Redo Record: cannot be filtered by XID/OBJNO”
The tablespace_no option can only be used when both rdba min and rdba max (rolback data block address range) have been specified.
The con_id option may only be legal when used to specify a PDB from the CDB
Remember: when you dump redo you don’t just get the redo for your session. There is some scope for being selective but the starting point would be all the redo for the PDB you’re working from.
🔆 Oracle LogMiner
A built-in tool that provides SQL reconstruction of redo logs, allowing users to analyze database changes.
Used for auditing, forensic analysis, and data recovery.
Can be queried with V$LOGMNR_CONTENTS, making it much more readable and useful.
Example:
BEGIN
DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/path/to/redo.log', OPTIONS => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.START_LOGMNR;
END;
/
SELECT SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE OPERATION = 'INSERT';
******************************
When should we use which one?
Use ALTER SYSTEM DUMP REDO if you are troubleshooting corruption or need low-level redo log analysis.
Use LogMiner if you want to analyze and recover transactions in an understandable SQL format.
When DBA can take a Dump of Redo?
This command is typically used for troubleshooting, forensic analysis, and debugging redo-related issues. Here are some scenarios where it can be helpful:
1. Analyzing Redo Log Contents
If you suspect data corruption or need to investigate what transactions were recorded in the redo logs, dumping the redo contents helps inspect the changes at a low level.
2. Debugging Flashback and Undo Issues
When troubleshooting flashback-related problems, you might need to verify if necessary undo or before-image changes exist in the redo logs.
3. Checking for Uncommitted Transactions
If an instance crashes and you need to investigate which transactions were active at the time, dumping redo logs can reveal in-progress transactions.
4. Troubleshooting Data Recovery Issues
If you suspect a problem with redo log corruption or need to manually inspect redo log records for a recovery scenario, this command provides raw redo log contents.
5. Performance Tuning and Auditing Changes
In some cases, you may want to analyze the redo logs to understand which operations are generating excessive redo and optimize accordingly.
Alireza Kamrani
No comments:
Post a Comment