SQL & PL/SQL

Hi,
you ought to create synonym for this package or call the package as SYS.dbms_logmnr

...and you don't have to use execute immediate.

Edited by: Ivan Blanarik on May 6, 2011 9:07 AM

With your help, I could run the procedure like this.
CREATE OR REPLACE PROCEDURE LOGMINER IS

BEGIN

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/home/oracle/oradata/SFS/arch/1_32317_734026431.dbf', OPTIONS => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
DBMS_LOGMNR.END_LOGMNR;
END LOGMINER;
/


and I like to run this one every 30 minutes with another archive file that means +1 No of archive file.
I might think this can be done with some dynamic sql, but I couldn't figure out how to exactly.
Can you check my code?


CREATE OR REPLACE PROCEDURE LOGMINER2 IS
tmp_num NUMBER;
v_TBL_NM1 VARCHAR2(64) := '/home/oracle/oradata/SFS/arch/1_';
v_TBL_NM2 VARCHAR2(64) := '_734026431.dbf'
v_TBL_NM VARCHAR2(64);
v_log_mnr VARCHAR2(256);

BEGIN

tmp_num := 32311;
V_TBL_NM := CONCAT(v_TBL_NM1, TO_CHAR(tmp_num), v_TBL_NM2);
v_log_mnr := 'DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => ''||V_TBL_NM||'', OPTIONS => DBMS_LOGMNR.NEW);
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
DBMS_LOGMNR.END_LOGMNR;

EXECUTE IMMEDIATE v_log_mnr;
tmp_num := tmp_num +1;

END LOGMINER2;

Edited by: user9359279 on May 6, 2011 6:23 PM

Perhabs this could help you ;-)

CREATE OR REPLACE PROCEDURE LOGMINER2
IS
  v_TBL_NM VARCHAR2(64);
BEGIN
  SELECT name 
  INTO v_TBL_NM
  FROM V$ARCHIVED_LOG 
  WHERE recid = (SELECT max(recid) FROM V$ARCHIVED_LOG);
  
  DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => V_TBL_NM, OPTIONS => DBMS_LOGMNR.NEW) ;
  DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG) ;
  DBMS_LOGMNR.END_LOGMNR;
END LOGMINER2;
/

begin
dbms_scheduler.create_job(
job_name => 'YOUR_SCHEMA.JOB_LOGMINER2'
  ,job_type => 'STORED_PROCEDURE'
  ,job_action => 'YOUR_SCHEMA.LOGMINER2'
  ,start_date => sysdate
  ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=30'
  ,enabled => TRUE
  ,comments => 'Automatic execution of LOGMINER2');
end;
/

Thank you. I like to run logminer and write to unix file.
In the logminer, I like to write only SQL_REDO parts.


Can you check my procedure, I could succeed to write something to file , but I couldn't write SQL_REDO column to file by whole.
It has error ORA-06650: line 7, colmun 3: PLS-00306: wrong number or types of arguments in call to 'LOGMINER4'

CREATE OR REPLACE PROCEDURE SYS.LOGMINER4
IS
v_LOG_NM VARCHAR2(64);
f utl_file.file_type;
BEGIN
SELECT name
INTO v_LOG_NM
FROM V$ARCHIVED_LOG
WHERE recid = (SELECT max(recid) FROM V$ARCHIVED_LOG);

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => v_LOG_NM, OPTIONS => DBMS_LOGMNR.NEW) ;
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG) ;



f := utl_file.fopen ('/home/oracle/oradata/SFS/logmine','sample.txt', 'W');
for rec in (SELECT sql_redo
FROM V$LOGMNR_CONTENTS
where SEG_OWNER='ORAASFS' and OPERATION IN ('UPDATE', 'INSERT', 'UPDATE') and TABLE_NAME LIKE 'TM_%') loop
utl_file.put_line (f, rec.sql_redo);
end loop;
utl_file.fclose(f);

utl_file.fclose(f);
DBMS_LOGMNR.END_LOGMNR;

END LOGMINER4;
/

Edited by: user9359279 on May 16, 2011 3:17 PM

Hi,
I don't know how logminer works, but you should write records to file in loop. And add some exception handling ;-)

CREATE OR REPLACE PROCEDURE SYS.LOGMINER3
IS
v_LOG_NM VARCHAR2(64);
f utl_file.file_type;

BEGIN
SELECT name 
INTO v_LOG_NM
FROM V$ARCHIVED_LOG 
WHERE recid = (SELECT max(recid) FROM V$ARCHIVED_LOG);

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => v_LOG_NM, OPTIONS => DBMS_LOGMNR.NEW) ;
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG) ;

f := utl_file.fopen ('/home/oracle/oradata/SFS/logmine','sample.txt', 'W');

FOR rec_sql_redo in (
  SELECT SQL_REDO 
  FROM V$LOGMNR_CONTENTS
  where SEG_OWNER='ORAASFS' 
  and OPERATION IN ('UPDATE', 'INSERT', 'UPDATE') 
  and TABLE_NAME LIKE 'TM_%'
  order by timestamp
)
LOOP
  utl_file.put_line(f, rec_sql_redo);
END LOOP;


utl_file.fclose(f);
DBMS_LOGMNR.END_LOGMNR;

END LOGMINER3;
/

I made the procedure like this. It work fine. Thank you.

CREATE OR REPLACE PROCEDURE SYSTEM.P_LOGMINER_RESYNC
IS
v_LOG_NM VARCHAR2(256);
f utl_file.file_type;
BEGIN
SELECT name
INTO v_LOG_NM
FROM V$ARCHIVED_LOG
WHERE recid = (SELECT max(recid) FROM V$ARCHIVED_LOG);

DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => v_LOG_NM, OPTIONS => DBMS_LOGMNR.NEW) ;
DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG) ;


f := utl_file.fopen ('OUTPUT','sample.txt', 'W');
for rec in (SELECT SQL_REDO
FROM V$LOGMNR_CONTENTS
where SEG_OWNER='ORAASFS' and OPERATION IN ('UPDATE', 'INSERT', 'DELETE') and TABLE_NAME LIKE 'TM_SFS_%' order by timestamp) loop
utl_file.put_line (f, rec.SQL_REDO);
end loop;
utl_file.fclose(f);

DBMS_LOGMNR.END_LOGMNR;

END P_LOGMINER_RESYNC;
/

Hi Buddy,
Could please send me what are the Pre as well as Post Requirement for the Logminer..

Thanks
Arun