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. You 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 restoring.
- 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 need…this can 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:
begin
--some example files…note the restore directory, not the archive_log_dest:
sys.dbms_logmnr.add_logfile('/opt/oracle/oradata/restore_archive_logs/1_219538_605365557.dbf');
sys.dbms_logmnr.add_logfile('/opt/oracle/oradata/restore_archive_logs/1_219539_605365557.dbf');
sys.dbms_logmnr.add_logfile('/opt/oracle/oradata/restore_archive_logs/1_219540_605365557.dbf');
sys.dbms_logmnr.add_logfile('/opt/oracle/oradata/restore_archive_logs/1_219541_605365557.dbf');
sys.dbms_logmnr.add_logfile('/opt/oracle/oradata/restore_archive_logs/1_219542_605365557.dbf');
sys.dbms_logmnr.add_logfile('/opt/oracle/oradata/restore_archive_logs/1_219543_605365557.dbf');
sys.dbms_logmnr.add_logfile('/opt/oracle/oradata/restore_archive_logs/1_219544_605365557.dbf');
sys.dbms_logmnr.add_logfile('/opt/oracle/oradata/restore_archive_logs/1_219545_605365557.dbf');
sys.dbms_logmnr.add_logfile('/opt/oracle/oradata/restore_archive_logs/1_219546_605365557.dbf');
sys.dbms_logmnr.add_logfile('/opt/oracle/oradata/restore_archive_logs/1_219547_605365557.dbf');
--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.start_logmnr(options =>
sys.dbms_logmnr.dict_from_online_catalog + sys.dbms_logmnr.print_pretty_sql);
end;
/
create table logmnr_contents tablespace users
as select *
from v$logmnr_contents
--optional
--where seg_name = '<your_table_name>'
--and seg_owner = '<your_schema_owner>'
;
begin
sys.dbms_logmnr.end_logmnr;
end;
/
Chris..
_____________________________________________________________________
Chris Ruel * Oracle Database Administrator * Lincoln Financial Group
cruel@xxxxxxx<mailto:cruel@xxxxxxx> * Desk:317.759.2172 * Cell 317.523.8482
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
Hello gurus,
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
taking forever.
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!
Lyall Barbour
-- //www.freelists.org/webpage/oracle-l
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
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
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**
