Jump to redo dumping for PDBs and small tests
Here’s a little procedure I’ve been using since Oracle 8i to dump the contents of the current log file – I’ve mentioned it several times in the past but never published it, so I’ll be checking for references to it and linking to it.
The code hasn’t changed in a long time, although I did add a query to get the full tracefile name from v$process when that became available. There’s also an (optional) called to dbms_support.my_sid to pick up the SID of the current session that slid into the code when that package became available. For PDBs, though, you’ll have to use the “dump redo” command (see footnote).
rem rem Script: c_dump_log.sql rem Author: Jonathan Lewis rem Dated: December 2002 rem Purpose: Create procedured to dump the current online redo log file. rem rem Last tested rem 18.3.0.0 rem 12.2.0.1 rem 11.1.0.7 rem 11.2.0.6 rem 10.2.0.5 rem 10.1.0.4 rem 9.2.0.8 rem 8.1.7.4 rem rem Notes: rem Must be run as a DBA rem Very simple minded - no error trapping rem create or replace procedure dump_log as m_log_name varchar2(255); m_process varchar2(255); m_trace_name varchar2(255); begin select lf.member into m_log_name from V$log lo, v$logfile lf where lo.status = 'CURRENT' and lf.group# = lo.group# and rownum = 1 ; execute immediate 'alter system dump logfile ''' || m_log_name || ''''; select spid into m_process from v$session se, v$process pr where se.sid = --dbms_support.mysid (select sid from v$mystat where rownum = 1) and pr.addr = se.paddr ; select tracefile into m_trace_name from v$session se, v$process pr where se.sid = --dbms_support.mysid (select sid from v$mystat where rownum = 1) and pr.addr = se.paddr ; dbms_output.put_line('Trace file is: ' || m_trace_name); dbms_output.put_line('Log file name is: ' || m_log_name); dbms_output.put_line('Trace file name includes: ' || m_process); end; / show errors drop public synonym dump_log; create public synonym dump_log for dump_log; grant execute on dump_log to public;
I don’t use the package often but if I want to find out what redo is generated during a test I usually follow the sequence:
- alter system switch logfile;
- do the experiment
- execute dump_log
If you’re running in a PDB there’s an extra step needed as you can’t “switch logfile” inside a PDB so I’ll either do a log file switch before I start the test or (if there are steps in the test script that could generate a lot of log file I don’t want to see) I include a “pause” in the test script and use another session to do the logfile switch – in both cases the second session has to be connected to the CDB.
You will have noticed the creation of the public synonym and granting of the execute privilege to public. In my own sandbox database that’s a convenience – you may want to be a little more protective in your development and test systems.
The “dump logfile” command has a number of options for selective dumping – I have a note in my file commenting on these options, but I haven’t checked if there are any new ones (or changes to existing ones) for a long time:
alter system dump logfile '{filename}' scn min {first SCN to dump} scn max {last SCN to dump} time min {seconds since midnight at the end of 1st Sept 1987} time max {see redo_time_calc.sql} layer {integer} opcode {integer} e.g.: layer 23 Block Written Records layer 5 Undo handling in general layer 5 opcode 4 Undo Seg header on commit; or rollback; layer 9999 opcode 9999 Trick to validate the whole log file structure xid {usn} {slot} {sequence} -- 10g only, may break on IMU redo (see below) objno {object_id} -- 10g only, may break on IMU redo (see below) dba min {datafile no} . {blockno} -- with spaces either side of the dot. dba max {datafile no} . {blockno} -- with spaces either side of the dot. rba min {log file seq no} . {blockno} -- with spaces either side of the dot. rba max {log file seq no} . {blockno} -- with spaces either side of the dot.. (The dots in the last four options becomes invalid syntax in 10g).
The introduction to this note references back to a presentation I did in the year 2000, but the closing comment suggests that I probably haven’t checked the list since some time in the 10g timeline.
The reference to redo_time_calc.sql points to the following script, that expresses the time as the number of seconds since Jan 1988, with the unfortunate simplification that Oracle thinks there are 31 days in every month of the year:
rem rem Script: redo_time_calc3.sql rem Author: Jonathan Lewis rem Dated: Dec 2012 rem Purpose: rem select 86400 * ( 31 * months_between( trunc(sysdate,'MM'), to_date('01-Jan-1988','dd-mon-yyyy') ) + sysdate - trunc(sysdate,'MM') ) redo_now from dual ; select 86400 * ( (sysdate - 10/1440) - trunc((sysdate-10/1440),'MM') + 31 * months_between( trunc((sysdate - 10/1440),'MM'), to_date('01-Jan-1988','dd-mon-yyyy') ) ) ten_minutes_ago, 86400 * ( sysdate - trunc(sysdate,'MM') + 31 * months_between( trunc(sysdate,'MM'), to_date('01-Jan-1988','dd-mon-yyyy') ) ) redo_now, to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') now from dual ;
This isn’t a piece of code I use much – the original version (which I published in Oracle Core, p.241) was something I wrote in 2003 and had to adjust by hand each time I used it without realising that I’d got it wrong. Luckily someone pointed out my error and gave me the corrected code a little while after I’d published the book. (It was one of those “why didn’t I think of that” moments – it seemed so obvious after he’d told me the right answer.)
Footnote: PDBs
The “dump logfile” command is not permitted inside a PDB, so I’ve had to adopt a different strategy for getting the redo when working in a PDB – switching to the “dump redo” command.
column current_scn new_value start_scn select to_char(current_scn,'9999999999999999') current_scn from v$database; -- do something interesting here column current_scn new_value end_scn select to_char(current_scn,'9999999999999999') current_scn from v$database; alter session set tracefile_identifier='something_memorable'; alter system dump redo scn min &start_scn scn max &end_scn ; alter session set tracefile_identifier='';
All the other parameters for “dump logfile” work with “dump redo”, though the only one I tend to use is the “layer” parameter.
[…] dump_log procedure is simply a pl/sql wrapper for a call to ‘alter system dump logfile {current […]
Pingback by Quiz night | Oracle Scratchpad — November 27, 2018 @ 9:17 pm GMT Nov 27,2018 |
[…] I “switch logfile”, do a little work, and use a little procedure I’ve written to dump the current log file. The log file switch is just a convenient way to keep the dump as small as possible – but […]
Pingback by Redo | Oracle Scratchpad — November 27, 2018 @ 9:22 pm GMT Nov 27,2018 |
[…] using a package I wrote a long time ago to report a delta in my session’s stats. The call to dump_log uses another little package to identify the current log file and issue an “alter system dump […]
Pingback by Index splits | Oracle Scratchpad — November 27, 2018 @ 9:29 pm GMT Nov 27,2018 |
[…] step to “alter system dump logfile” is not allowed inside a PDB, so as a general pattern I now capture the current SCN before and after an operation that I’m investigating and then […]
Pingback by Update restarts | Oracle Scratchpad — August 25, 2022 @ 1:44 pm BST Aug 25,2022 |
[…] database”, so I had to change the strategy. This is just a brief note (echoing a footnote to an older note) of the mechanism I now […]
Pingback by Dumping redo | Oracle Scratchpad — September 12, 2022 @ 10:05 am BST Sep 12,2022 |