 Why to the instructions and examples for logminer scrape so bad? [message #122243] |
Sat, 04 June 2005 04:25  |
omichaud
Messages: 3
Registered: June 2005
Location: Arkansas
|
Junior Member |
|
|
Hi there!
I'm running Oracle 9.2.0.4 with all the bells and whistles on RHAS3. We have 4 databases altogether. I have OMS, Catalogs, and intelligent agent all finally happy in their new home.
It wasn't always that way though.. and I'm having an issue or two with recovery. Well that and I need to figure out how to enter more than one option for the startup call ... like skip corrupt AND only commited. .. I should probably be commited.
We experienced some datafile and archivelog corruption.. not sure where ot how it got there but it did. I can recover to a specific date, and was able to dig up some archivelogs for a few days more.. but I can't use them very readily, as they're not catalogued and oracle says they're too corrupt.
I've developed this PL/SQL script (I'll include the snippet down at the end here) that uses logminer, and goes through looking for clean transactions outside the corrupt regions and I can view the queries I want to get back .. but they end up stored as variables.
I've gone to metalink.. and I think that Oracle Corporation must save the dregs of their personnel base in customer relations to go to this department. The docs and the support ops seem to stop after you reach the point that I have reached.
I don't want to just get stats, I want to use the data! I would dearly love to be able to restore the redo logs but so far using the graphic version of logminer (viewer) is fairly unsuccessful with large files. I even tried to see if I could even do 10 records at a time and logminer for Oracle Enterprise Manager fails.
I would like to be able to either bring the records I find after my filter INTO the active database.. so that we can roll forward a little bit further.. basically circumvent the corruption to get all that we can back for the application user schema.
One of two basic quesitons in the end.
1 - Where do I go from here to recreate and restore the good log data retrieved by LOGMNR?
or
2 - Is there a way to execute the sql statements I am retreiving from V$LOGMNR_CONTENTS.SQL_REDO in PL/SQL?
Hep! Poor ol' redneck needs some sleep!!
This is what I have:
spool off
BEGIN
DBMS_LOGMNR.ADD_LOGFILE('/u01/tmp/arch/1_846.dbf', DBMS_LOGMNR.NEW );
END;
/
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.SKIP_CORRUPTION, DICTFILENAME =>'/u01/tmp/arch/miner_dictionary.dic');
spool test.sql
SET SERVEROUTPUT ON SIZE 100000
DECLARE
u_arc VARCHAR(4000);
d_con BINARY_INTEGER := 0;
c_limit BINARY_INTEGER := 25;
disp_ech VARCHAR(40);
CURSOR c_arc IS
SELECT SEG_OWNER, SUBSTR(SQL_REDO,1,40) AS DISP_ECH,
RTRIM(SQL_REDO) AS SQLTXT
FROM V$LOGMNR_CONTENTS WHERE SEG_OWNER LIKE 'LMEADM';
BEGIN
FOR r_arc in c_arc
LOOP
u_arc := r_arc.SQLTXT;
disp_ech := r_arc.DISP_ECH;
IF disp_ech NOT LIKE 'Unsupp%' and disp_ech not like ' ' THEN
d_con := d_con + 1;
DBMS_OUTPUT.PUT_LINE(d_con|| ' '||disp_ech);
DBMS_OUTPUT.PUT_LINE('Dont you wish you could run the query that u_arc represents here?');
DBMS_SQL.EXEC('u_arc');
EXIT WHEN c_arc%NOTFOUND;
IF d_con = c_limit THEN
DBMS_OUTPUT.PUT_LINE('commit records here');
d_con := 0;
EXIT;
END IF;
END IF;
END LOOP;
END;
/
spool off
execute dbms_logmnr.end_logmnr;
[Updated on: Sat, 04 June 2005 19:18] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
