Log miner configuration
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Log miner configuration

  1. #1
    Join Date
    Jun 2003
    Posts
    108

    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.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    tahiti.oracle.com
    metalink.oracle.com

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    -- 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
    email: ocp_9i@yahoo.com

  4. #4
    Join Date
    Jun 2003
    Posts
    108
    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.

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Jun 2003
    Posts
    108
    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.

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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!!
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  8. #8
    Join Date
    Jun 2003
    Posts
    108
    Yes I agree Thomas.

    Since I am new into this, unable to do things easily.

    Sorry.

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width