Thanks so muchHaven't posted to the list, forgot the defaults
220.127.116.11Linux RHEL 5 64bit16g memory_targetPGA is 0
Going to try some of the below tonight when I log back on
Sent via the Samsung Galaxy Note® 4, an AT&T 4G LTE smartphone
-------- Original message --------
From: "Ruel, Chris" <Chris.Ruel@xxxxxxx>
Date: 4/18/17 4:23 PM (GMT-05:00)
To: lyallbarbour@xxxxxxxxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
Subject: RE: Log Miner help
I don’t know what version you are on, but, here some general things that I
have found helpful:
Have your query get more than you think you will need (rows, columns etc. In
fact, I often do a select * from v$logmnr_contents
Instead of a straight query, do a CREATE TABLE AS SELECT with your query. That
way, you can query it multiple times selecting rows and columns as you see fit.
won’t have to wait for the query to read all the log files if you have to run
it more than once. Make sure you have enough space where you will put this
table and keep an eye on it for a long job like yours.
Make sure you do not have a backup job that will run while your query is
running and remove your archive logs if you left them in the archive dest after
You can look at v$session_longops to see the progress of your query as it works
through the logs.
It’s probably too late now, but, for tables you think you will be logmining,
make sure you research the correct level of supplemental logging you will
also be done at the database level but can add overhead as well. I can’t
really advise you on this as I do not know your situation. Unfortunately, it
will have to wait for next time anyway…can reverse supplemental log.
Here is my general approach:
--some example files…note the restore directory, not the archive_log_dest:
--if you have a tons/days of logs to search, this will take a while…not sure
you can speed it up much
sys.dbms_logmnr.dict_from_online_catalog + sys.dbms_logmnr.print_pretty_sql);
create table logmnr_contents tablespace users
as select *
--where seg_name = '<your_table_name>'
--and seg_owner = '<your_schema_owner>'
Chris Ruel * Oracle Database Administrator * Lincoln Financial Group
cruel@xxxxxxx * Desk:317.759.2172 *
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Lyall Barbour
Sent: Tuesday, April 18, 2017 3:31 PM
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Subject: Log Miner help
Are there any log miner specific gurus out there? i've had a rough month and
am currently trying to mine about 5 days of archive logs and the query is
1) no supplemental logging on in the past
2) dbms_logmnr_d.build (options => dbms_logmnr_d.store_in_redo_logs)
3) add_logfile (.newfile, then a bunch of addfile)
4) start_logmnr (starttime'04/14', endtime 4/18,
DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE )
5) then run the query that's taking about 7 hours so far and not come back to
the spool file.
Any ideas to speed it up? Hell, i'm not even sure if my query will get what i
want... and that's taken 7 hours so far, yipee!
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
which it is addressed. If you are not the intended recipient of this E-mail,
hereby notified that any dissemination, distribution, copying, or action taken
relation to the contents of and attachments to this E-mail is strictly
and may be unlawful. If you have received this E-mail in error, please notify
sender immediately and permanently delete the original and any copy of this
and any printout. Thank You.**