Re: redo logs: instantaneous switches

"dsmcd" <lookingglass_at_stopspamdormouse.net> wrote in message news:UwYX9.999$H14.48327_at_news.uswest.net...
> I just went with the default sizes. It's a POS database, with
> six or eight registers. Average is a switch every 30 minutes,
> which at the time I deployed seemed reasonable according to
> advice and research available at the time. Guess not.
>
> This "log miner" is something new to me.

OK. It's nothing to do with the alert log, but a set of packages that allow you to see inside of the online redo logs.

Read up on it by all means, but the quick story is: it's a three-step process.

1. Create a "dictionary file" (needed to map redo change vectors into plain English statements: squiggle-squiggle-dash-dot-blah becomes 'update EMP set sal=900'). That's execute dbms_logmnr_d.build(<name_of_file>,<location_of_file>).

Note that the <location_of_file> parameter must match one of your settings for the init.ora parameter utl_file_dir.

2. Build a list of logs to analyze. You can analyze just one, or dozens. Online, archived, current. Makes no difference:

execute dbms_logmnr.add_logfile(<full_path_and_name_of_log>, dbms_logmnr.new)
AND
execute dbms_logmnr.add_logfile(<full_path_and_name_of_log>, dbms_logmnr.addfile)

Use the first one for the very first log you add to the list (the ".new" initialises a new list). All other logs to be analyzed in the same run are added with the second version of the command (".addfile" adds a file to an already-existing list)

3. Perform the analysis:

execute
dbms_logmnr.start_logmnr(dictfilename=>'path_and_name_of_dictionary_file')

That 'path_and_name' is the full-blown version of what you created in step 1, but in the right order and looking normal. That is, if in step 1 you created a file called 'HJRDICT.ORA', in directory 'C:\BLAH', then here you'd enter 'c:\blah\hjrdict.ora'.

Step 3 is the one that can take time, because it actually has to trawl through the logs and deal with the information it finds there.

At the end of step 3, you can query v$logmnr_contents, which shows you two really important columns: SQL_REDO (what SQL statement was actualy issued, or its logical equivalent) and SQL_UNDO (what SQL statement would you issue to logically reverse the effect of the issued statement.

It's also got columns for the User and the time a statement was issued, so that might help track down who is doing what.

>I'll look into it,
> unless you mean reading the alert log for switching info, in
> which case I included snips in my original message.
>
> Good to see you again, Howard! I still miss your website. It was
> invaluable to me. Sure glad I made hard copies.
>

Kind of you to say so. I'm currently being hassled about the unauthorised copies of the former site as well now, even though they're nothing to do with me. So this one looks set to run and run.

Regards
HJR Received on Thu Jan 23 2003 - 15:06:00 CST