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.
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:
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.)
Recent comments
1 year 3 weeks ago
1 year 15 weeks ago
1 year 19 weeks ago
1 year 20 weeks ago
1 year 25 weeks ago
1 year 46 weeks ago
2 years 14 weeks ago
2 years 44 weeks ago
3 years 28 weeks ago
3 years 29 weeks ago