Re: Reading ORACLE Re-do logs
Date: 15 Sep 1993 07:55:40 GMT
Message-ID: <276hpsINNevr_at_gap.caltech.edu>
"Andrew Jones (lrpr_at_unb.ca)" <LRPR_at_UNB.CA> writes:
> Does anyone know if there's a way to generate a report or file
>from the re-do logs for an ORACLE V6 database?
No, not me.
> We'd like to be able to have a
>record of what was done when, and by what user. It appears that
>ORACLE auditing only says what operation was done when and by whom,
>but not the data content of the insert/delete/replace. The CASE
>tool will generate this type of log, but it's all in the application
>code, and that doesn't do much good if someone manages to connect to
>the database with a homegrown application or in an SQL interactive
>interface.
Hold on to that code, it might prove useful.
> That information must be in the re-do log, and I'd expected that
>ORACLE (like INGRES) would be able to report the content to me using
>a vendor-supplied utility; but no such luck. Now I'm looking for
>someone's homegrown solution, or a commercial product.
Andrew,
At the risk of sounding like a broken record, Oracle7 can do what you want (audit changes, not read redo-logs).
I have a demo of creating an audit trail in my upcoming publication:
"Dan's Oracle7 Guide" beta version due out on Oct 4.
The mechanism is a new Oracle7 database object called a trigger. Triggers are chunks of pl/sql which get fired by "events". Now it might become clear why you might want to hold on to the pl/sql which gets generated by CASE.
I'm not sure if this is old news to you but it least gave me a chance to tout my guide.
The README for triggers is displayed below:
README.triggers
One feature available to the ORACLE7 customer is the Procedural Option. To find out more about the Procedural Option, read the section on procedures. If the Procedural Option is installed, it allows the customer to create dbs objects called triggers which fire before a specified event is about to happen. Also a trigger may be set up to fire after a specified event has just finished. A trigger contains PL/SQL statements. The events which can be used to fire a trigger are listed below:
The point in time prior to the issuance of the following statements:
Insert, Update, Delete
The point in time after the issuance of the following statements:
Insert, Update, Delete
The point in time prior to the insertion of a row into a table The point in time prior to the update of a row in a table The point in time prior to the deletion of a row from a table
The point in time after the insertion of a row into a table The point in time after the update of a row in a table The point in time after the deletion of a row from a table
Counting the events above reveals that only twelve events associated with any one table can fire a trigger.
This directory contains a few demonstrations of triggers. In addition to the demonstrations, a file named trgtmplate.sql has been provided. It contains most of the syntax constructs that a programmer might need to write a trigger.
Steps are listed below for running the trigger demonstrations:
sqlplus usr/pwd _at_crtabs.sql # this creates the tables used by the demos
sqlplus usr/pwd _at_cr_trg1.sql # this creates trg1; a real goofy example
sqlplus usr/pwd _at_runupd.sql # this triggers trg1
sqlplus usr/pwd _at_instrg1.sql # this generates a pk from a sequence if the
# pk is missing
sqlplus usr/pwd _at_runins.sql # this demonstrates that instrg1 does work
more runins.lst.kp # this displays how runins.sql ran on a Sun
sqlplus usr/pwd _at_audtrg1.sql # this trigger places rows in an auditing table
# before updates and deletes of PHONE
sqlplus usr/pwd _at_drpinstrg1.sql# this drops instrg1 so it does not conflict
# with audtrg2
sqlplus usr/pwd _at_audtrg2.sql # this trigger places rows in an auditing table
# before inserts into PHONE
sqlplus usr/pwd _at_runaudtrg.sql # this demonstrates that audtrg1 and audtrg2
# do work
cr_person.sh # this demonstrates the steps to be followed
# to create a table named PERSON on a remote
# dbs
sqlplus usr/pwd _at_cr_dblink.sql # this creates a dbs link to the remote dbs
# containing the table named PERSON
sqlplus usr/pwd _at_drpaudtrg.sql # this drops audtrg1 and audtrg2 so they do not
# conflict with reftrg
sqlplus usr/pwd _at_reftrg.sql # this trigger ensures that rows in the PHONE
# table are linked to PERSON_at_ava2x7
sqlplus usr/pwd _at_runreftrg.sql # this demonstrates that reftrg does work
sqlplus usr/pwd _at_drpreftrg.sql # this drops reftrg so it does not
# conflict with bigtrg
sqlplus usr/pwd _at_bigtrg.sql # this trigger combines all of the trigger
# ideas presented so far into one trigger
sqlplus usr/pwd _at_runbigtrg.sql # this demonstrates that bigtrg does work
sqlplus usr/pwd _at_qrytrg.sql # this demonstrates a query against the
# data dictionary for triggers
==============================================================================
An excerpt from audtrg1.sql is displayed below:
set echo on
/* audtrg1.sql*/
/* simple trigger which audits changes made to a table */
CREATE OR REPLACE TRIGGER audtrg1
BEFORE DELETE OR UPDATE ON PHONE
FOR EACH ROW
DECLARE
phoneidaud PHONE_AUD.PHONEID%TYPE;
personidaud PHONE_AUD.PERSONID%TYPE;
phonenumaud PHONE_AUD.PHONENUM%TYPE;
typeaud PHONE_AUD.TYPE%TYPE;
timestampaud PHONE_AUD.TIMESTAMP%TYPE;
usernameaud PHONE_AUD.USERNAME%TYPE;
BEGIN
phoneidaud := :old.PHONEID;
personidaud := :old.PERSONID;
phonenumaud := :old.PHONENUM;
typeaud := :old.TYPE;
SELECT SYSDATE INTO timestampaud FROM DUAL; SELECT USER INTO usernameaud FROM DUAL;
INSERT INTO PHONE_AUD
(
PHONEID
,PERSONID ,PHONENUM ,TYPE ,TIMESTAMP ,USERNAME
)
VALUES
(
phoneidaud
,personidaud ,phonenumaud ,typeaud ,timestampaud ,usernameaud
);
EXCEPTION
WHEN OTHERS THEN
dbms_standard.raise_application_error (-20328, SQLERRM(SQLCODE)\
);
END;
/
/* end of audtrg1.sql */
exit
For info on how to get a copy of "Dans's Oracle7 Guide", send me email or snail mail.
-Dan
Daniel B. Bikle
Independent Oracle Consultant
dbikle_at_alumni.caltech.edu
415/854-9542
P.O. BOX 'D'
MENLO PARK CA 94026
Received on Wed Sep 15 1993 - 09:55:40 CEST