-
Log miner configuration
Hi DBAs,
My DB is running in archivelog mode and i would like to configure the log miner to view the archive log files.
Can anyone let me know the configuration steps and also set up of utl_file_dir in parameter file.
OS - WIN NT
DB -Oracle 8.1.5 production.
Thanx in advance.
-
tahiti.oracle.com
metalink.oracle.com
-
-- DBMS_LOGMNR
--
-- For 8i
-- Set utl_file_dir in init.ora
--
-- Build dictionary
--
exec sys.dbms_logmnr_d.build('dict.ora','/oracle/P05/saparch');
--
-- Add logfiles (archive or redo)
--
exec sys.dbms_logmnr.add_logfile('/tmp/P05arch1_41180.dbf',sys.dbms_logmnr.NEW);
exec dbms_logmnr.add_logfile('/u02/oradata/B2BFOR/redo02.log',dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile('/u02/oradata/B2BFOR/redo03.log',dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile('/u02/oradata/B2BFOR/redo04.log',dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile('/u02/oradata/B2BFOR/redo05.log',dbms_logmnr.ADDFILE);
exec dbms_logmnr.add_logfile('/u02/oradata/B2BFOR/redo06.log',dbms_logmnr.ADDFILE);
--
-- start , specify the date.
-- You can also specify the scn
--
execute sys.dbms_logmnr.start_logmnr(dictfilename=>'/oracle/P05/saparch/dict.ora',starttime=>to_date('28/05/2003:15:59:00','DD/MM/YYYY:HH24:mi:ss'),endtime=>to_date('28/05/2003:16:00:00','DD/MM/YYYY:HH24:MI:SS'));
--or
execute dbms_logmnr.start_logmnr(dictfilename=>'/u01/oracle/admin/B2BFOR/utl_file/dict',starttime=>to_date('29/04/2003:3PM','DD/MM/YYYY:HHAM'),endtime=>to_date('30/04/2003:10:17:00','DD/MM/YYYY:HH:MI:SS'));
-- select
-- Remember it will only we visable in you current session, not for other people.
set linesize 132
column username format a10
column sql_redo format a40
column sql_undo format a40
select scn ,to_char(timestamp,'dd-Mon-YY:HH24:MI:SS') Time ,username, sql_redo
from v$logmnr_contents
where username = 'SAPR3';
--and upper(sql_redo) like '%BKPF%';
-- Undo is to undo the statements
-- Redo is the statements executed
--
-- Check settings
--
select * from v$logmnr_dictionary;
select * from v$logmnr_logs;
select * from v$logmnr_parameters;
--
-- Stop logmnr --
--
execute dbms_logmnr.end_logmnr;
--Example:
SQL> select scn ,to_char(timestamp,'dd-Mon-YY:HH24:MI:SS') Time ,username,sql_redo, sql_undo from v$logmnr_contents where username <> 'SYS';
SCN TIME USERNAME SQL_REDO SQL_UNDO
---------- ------------------------ ---------- ---------------------------------------- ----------------------------------------
757558 30-Apr-03:10:13:19 WLCS set transaction read write;
757558 30-Apr-03:10:13:19 WLCS insert into "WLCS"."HUGO"("HH") values ( delete from "WLCS"."HUGO" where "HH" = '
'hugo '); hugo ' and ROWID = 'AAABH3AAEAAAADz
AAA';
757559 30-Apr-03:10:13:31 WLCS insert into "WLCS"."HUGO"("HH") values ( delete from "WLCS"."HUGO" where "HH" = '
'venter '); venter ' and ROWID = 'AAABH3AAEAAAADz
AAB';
757561 30-Apr-03:10:13:34 WLCS commit;
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Hi Mr.julian,
Thanx for ur valuable reply.
I executed the following and got error.Before execute this I brought my db as mount stage and
I set the parameter file - utl_file_dir ('C:\oracle\dictionary.ora').
execute dbms_logmnr_.start_logmnr('c:\oracle\dictionary.ora')
Error:
numeric or values error : character to number conversion error.
What could be the error. Please give some input to solve this.
Thanx in advance.
-
Originally posted by sri_dba
Hi Mr.julian,
Thanx for ur valuable reply.
I executed the following and got error.Before execute this I brought my db as mount stage and
I set the parameter file - utl_file_dir ('C:\oracle\dictionary.ora').
execute dbms_logmnr_.start_logmnr('c:\oracle\dictionary.ora')
Error:
numeric or values error : character to number conversion error.
What could be the error. Please give some input to solve this.
Thanx in advance.
Try this:
execute dbms_logmnr.start_logmnr(DICTFILENAME =>'c:\oracle\dictionary.ora');
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
I executed as you said,,, but i got the following error msg.
Invalid file name
"sys.x$dbms_logmnr, line 70
at line 2.
I created a text file naming as dictionary.ora in the following path.
C:\oracle\dictionay.ora
Then i mentioned this into parameter file
utl_file_dir = c:\oracle\dictionary.ora.
-
Originally posted by sri_dba
I executed as you said,,, but i got the following error msg.
Invalid file name
"sys.x$dbms_logmnr, line 70
at line 2.
I created a text file naming as dictionary.ora in the following path.
C:\oracle\dictionay.ora
Then i mentioned this into parameter file
utl_file_dir = c:\oracle\dictionary.ora.
Spoon feeding symptoms! RTFM is VERY Important!!
-
Yes I agree Thomas.
Since I am new into this, unable to do things easily.
Sorry.
-
Not utl_file_dir = c:\oracle\dictionary.ora but utl_file_dir = c:\oracle
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Originally posted by julian
Not utl_file_dir = c:\oracle\dictionary.ora but utl_file_dir = c:\oracle
Julian, added to that he created a test file 'dictionary.ora'.
Sri, use
exec sys.dbms_logmnr_d.build('dictionary.ora','c:\oracle'); to build the dictionary file.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|