Oracle-L: RE: alter system dump logfile ... question

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Thu, 27 Mar 2003 05:18:35 -0800
Message-ID: <F001.0057422F.20030327051835@fatcity.com>

Okay ... Jonathan as soon as I read your reply, I realized where the heck can I find the transaction vector ... I can dump the file anytime ...

Let me do some more debugging ...

Raj

Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !!

-----Original Message-----
Sent: Wednesday, March 26, 2003 5:14 PM
To: Multiple recipients of list ORACLE-L

I've not bothered to try this myself, but I think you will need to dump the first redo record of the transaction to get the 'transaction audit' change vector that is the last vector of the first record.

In Oracle 8.1, this would give you the userid of the transaction owner - but this looks like a 9i redo dump and I think that the transaction audit vector is optimised in v9 so that it is only the first record of the first transaction for the session attach that holds user id - so you may have to backtrack a long way to find out who is responsible for a single delete.

(I may be wrong - this surmise is based on a couple of changes I have noticed in recent log dumps that I was doing for a completely different reason).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

For one-day tutorials:
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______April 8th
____UK_______April 22nd
____Denmark May 21-23rd
____USA_(FL)_May 2nd

Next dates for the 3-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____UK_(Manchester)_May
____Estonia___June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 26 March 2003 16:14

> I am trying to find who deleted certain rows fro a table. So I
dumped the
> right logfile with 'layer 11 opcode 3' option. I can see the
information
> about deleted rows, but where is it stored .. whodunit??
>
> Here is a partial listing ... the objectid is 29230 as seen from
objn value
> --------------------------------------------------------------------

> ----
> REDO RECORD - Thread:1 RBA: 0x0013de.0004a4d4.0088 LEN: 0x0118 VLD:
0x01
> SCN: 0x0010.61152c4f SUBSCN: 1 03/25/2003 11:59:02
> CHANGE #1 TYP:0 CLS:74 AFN:2 DBA:0x0080671f SCN:0x0010.61152c4f SEQ:
1
> OP:5.1
> ktudb redo: siz: 156 spc: 4728 flg: 0x0022 seq: 0x093e rec: 0x22
> xid: 0x001d.016.00003c57
> ktubu redo: slt: 22 rci: 33 opc: 11.1 objn: 29230 objd: 29230 tsn:
31
> Undo type: Regular undo Undo type: Last buffer split: No
> Tablespace Undo: No
> 0x00000000
> KDO undo record:
> KTB Redo
> op: 0x02 ver: 0x01
> op: C uba: 0x0080671f.093e.1c
> KDO Op code: IRP row dependencies Disabled
> xtype: XA bdba: 0x07d05920 hdba: 0x07d0590c
> itli: 1 ispac: 0 maxfr: 4858
> tabn: 0 slot: 3(0x3) size/delt: 44
> fb: --H-FL-- lb: 0x1 cc: 5
> null: -----
> col 0: [10] 61 62 63 5f 66 61 6d 5f 6c 61
> col 1: [10] 41 42 43 20 46 61 6d 69 6c 79
> col 2: [ 4] 48 50 49 56
> col 3: [ 1] 4c
> col 4: [11] 4c 6f 73 20 41 6e 67 65 6c 65 73
> CHANGE #2 TYP:0 CLS: 1 AFN:31 DBA:0x07d05920 SCN:0x0010.6115216c
SEQ: 1
> OP:11.3
> KTB Redo
> op: 0x02 ver: 0x01
> op: C uba: 0x0080671f.093e.22
> KDO Op code: DRP row dependencies Disabled
> xtype: XA bdba: 0x07d05920 hdba: 0x07d0590c
> itli: 1 ispac: 0 maxfr: 4858
> tabn: 0 slot: 3(0x3)
> --------------------------------------------------------------------

> ----
>
> I want to see if I can decode it from the dump, logminer is my last
resort.
> I could learn some more from decoding the dump too.
>
> Thanks in advance
> Raj
> -------------------------------------------------------------
> Rajendra dot Jamadagni at espn dot com
> Any views expressed here are strictly personal.
> QOTD: Any clod can have facts, having an opinion is an art !!
>
>

-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: jonathan_at_jlcomp.demon.co.uk Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

text/plain attachment: ESPN_Disclaimer.txt

Received on Thu Mar 27 2003 - 07:18:35 CST