Re: alter system dump logfile
Title: alter system dump logfile Please check : oradebug DUMPLIST as given below... SQL oradebug dumplistEVENTSTRACE_BUFFER_ONTRACE_BUFFER_OFFHANGANALYZELATCHESPROCESSSTATESYSTEMSTATEINSTANTIATIONSTATEREFRESH_OS_STATSCROSSICCONTEXTAREAHEAPDUMPHEAPDUMP_ADDRPOKE_ADDRESSPOKE_LENGTHPOKE_VALUEPOKE_VALUE0GLOBAL_AREAMEMORY_LOGREALFREEDUMPERRORSTACKHANGANALYZE_PROCTEST_STACK_DUMPBG_MESSAGESENQUEUESSIMULATE_EOVKSFQP_LIMITKSKDUMPTRACEDBSCHEDULERGRANULELISTGRANULELISTCHKSCOREBOARDGES_STATEADJUST_SCNNEXT_SCN_WRAPCONTROLFFULL_DUMPSBUFFERSRECOVERYSET_TSN_P1BUFFERPIN_BLOCKSBC_SANITY_CHECKFLUSH_CACHELOGHISTARCHIVE_ERRORREDOHDRLOGERROROPEN_FILESDATA_ERR_ONDATA_ERR_OFFBLK0_FMTCHGTR_SET_BLOCKTR_SET_ALL_BLOCKSTR_SET_SIDETR_CRASH_AFTER_WRITETR_READ_ONE_SIDETR_CORRUPT_ONE_SIDETR_RESET_NORMALTEST_DB_ROBUSTNESSLOCKSGC_ELEMENTSFILE_HDRSKRB_CORRUPT_INTERVALKRB_CORRUPT_SIZEKRB_PIECE_FAILKRB_OPTIONSKRB_SIMULATE_NODE_AFFINITYKRB_TRACEKRB_BSET_DAYSDROP_SEGMENTSTREEDUMPLONGF_CREATEROW_CACHELIBRARY_CACHESHARED_SERVER_STATEKXFPCLEARSTATSKXFPDUMPTRACEKXFPBLATCHTESTKXFXSLAVESTATEKXFXCURSORSTATEWORKAREATAB_DUMPOBJECT_CACHESAVEPOINTSOLAP_DUMP - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Thursday, August 28, 2003 9:09 PM Subject: alter system dump logfile Hi all, as mentioned in Metalink article, one can use "ALTER SYSTEM DUMP LOGFILE ... LAYER 11 OPCODE 3" to dump deleted rows from archived logs. does anyone know 1. what others layers are and what they correspond to? 2. what other opcodes are and what they relate to? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: alter system dump logfile
Title: alter system dump logfile Thanks Manoj, Alex. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Manoj Kumar Jha [mailto:[EMAIL PROTECTED]Sent: Friday, August 29, 2003 1:39 AMTo: Multiple recipients of list ORACLE-LSubject: Re: alter system dump logfile Please check : oradebug DUMPLIST as given below... SQL oradebug dumplistEVENTSTRACE_BUFFER_ONTRACE_BUFFER_OFFHANGANALYZELATCHESPROCESSSTATESYSTEMSTATEINSTANTIATIONSTATEREFRESH_OS_STATSCROSSICCONTEXTAREAHEAPDUMPHEAPDUMP_ADDRPOKE_ADDRESSPOKE_LENGTHPOKE_VALUEPOKE_VALUE0GLOBAL_AREAMEMORY_LOGREALFREEDUMPERRORSTACKHANGANALYZE_PROCTEST_STACK_DUMPBG_MESSAGESENQUEUESSIMULATE_EOVKSFQP_LIMITKSKDUMPTRACEDBSCHEDULERGRANULELISTGRANULELISTCHKSCOREBOARDGES_STATEADJUST_SCNNEXT_SCN_WRAPCONTROLFFULL_DUMPSBUFFERSRECOVERYSET_TSN_P1BUFFERPIN_BLOCKSBC_SANITY_CHECKFLUSH_CACHELOGHISTARCHIVE_ERRORREDOHDRLOGERROROPEN_FILESDATA_ERR_ONDATA_ERR_OFFBLK0_FMTCHGTR_SET_BLOCKTR_SET_ALL_BLOCKSTR_SET_SIDETR_CRASH_AFTER_WRITETR_READ_ONE_SIDETR_CORRUPT_ONE_SIDETR_RESET_NORMALTEST_DB_ROBUSTNESSLOCKSGC_ELEMENTSFILE_HDRSKRB_CORRUPT_INTERVALKRB_CORRUPT_SIZEKRB_PIECE_FAILKRB_OPTIONSKRB_SIMULATE_NODE_AFFINITYKRB_TRACEKRB_BSET_DAYSDROP_SEGMENTSTREEDUMPLONGF_CREATEROW_CACHELIBRARY_CACHESHARED_SERVER_STATEKXFPCLEARSTATSKXFPDUMPTRACEKXFPBLATCHTESTKXFXSLAVESTATEKXFXCURSORSTATEWORKAREATAB_DUMPOBJECT_CACHESAVEPOINTSOLAP_DUMP - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Thursday, August 28, 2003 9:09 PM Subject: alter system dump logfile Hi all, as mentioned in Metalink article, one can use "ALTER SYSTEM DUMP LOGFILE ... LAYER 11 OPCODE 3" to dump deleted rows from archived logs. does anyone know 1. what others layers are and what they correspond to? 2. what other opcodes are and what they relate to? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
alter system dump logfile
Title: alter system dump logfile Hi all, as mentioned in Metalink article, one can use ALTER SYSTEM DUMP LOGFILE ... LAYER 11 OPCODE 3 to dump deleted rows from archived logs. does anyone know 1. what others layers are and what they correspond to? 2. what other opcodes are and what they relate to? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: alter system dump logfile
Title: RE: alter system dump logfile Here is for LAYER 11. Full list is too long. Layer 11: KCOCODRW - Row opcode 1 : KDOIUR - interpret undo redo opcode 2 : KDOIRP - insert row piece opcode 3 : KDODRP - drop row piece opcode 4 : KDOLKR - lock row piece opcode 5 : KDOURP - update row piece opcode 6 : KDOORP - overwrite row piece opcode 7 : KDOMFC - manipulate first column opcode 8 : KDOCFA - change forwarding address opcode 9 : KDOCKI - change cluster key index opcode 10 : KDOSKL - set key links opcode 11 : KDOQMI - quick multi-insert (ex. insert as select...) opcode 12 : KDOQMD - quick multi-delete opcode 13 : KDOTBF - toggle block header flags Alex. -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 28, 2003 8:39 AM To: Multiple recipients of list ORACLE-L Subject: alter system dump logfile Hi all, as mentioned in Metalink article, one can use ALTER SYSTEM DUMP LOGFILE ... LAYER 11 OPCODE 3 to dump deleted rows from archived logs. does anyone know 1. what others layers are and what they correspond to? 2. what other opcodes are and what they relate to? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: alter system dump logfile ... question
Title: RE: alter system dump logfile ... question 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- From: Jonathan Lewis [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 26, 2003 5:14 PM To: Multiple recipients of list ORACLE-L Subject: Re: alter system dump logfile ... question 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 [EMAIL PROTECTED] 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.3c57 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 0x 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error
alter system dump logfile ... question
Title: alter system dump logfile ... question 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.3c57 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 0x 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 !! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: alter system dump logfile ... question
Title: alter system dump logfile ... question I believe you'll need to use LogMiner. Dick Goulet -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, March 26, 2003 11:14 AMTo: Multiple recipients of list ORACLE-LSubject: alter system dump logfile ... question 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.3c57 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 0x 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 !!
RE: alter system dump logfile ... question
Title: alter system dump logfile ... question Dick, I tried various methods ... v$logmnr_contents has 0 rows ... that's why the hard way of doing things ... as usual metablink is little low of details about v$logmnr_contents containing no rows. Egads ... I am finally back on the list after 03/18 ... thanks to our resident investigator Bruce (the list master) ... 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-From: Goulet, Dick [mailto:[EMAIL PROTECTED]Sent: Wednesday, March 26, 2003 11:49 AMTo: Multiple recipients of list ORACLE-LSubject: RE: alter system dump logfile ... question I believe you'll need to use LogMiner. Dick Goulet -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, March 26, 2003 11:14 AMTo: Multiple recipients of list ORACLE-LSubject: alter system dump logfile ... question 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.3c57 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 0x 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 !! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: alter system dump logfile ... question
Raj I haven't tried reading logs without Logminer, but I have used Logminer without the dictionary. It is tough. Basically, everything is in hex. I would guess the who is the userid in hex. You could list the usernames and user_ids from dba_users and convert the user_id column to hex and see if a value matches. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 26, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Dick, I tried various methods ... v$logmnr_contents has 0 rows ... that's why the hard way of doing things ... as usual metablink is little low of details about v$logmnr_contents containing no rows. Egads ... I am finally back on the list after 03/18 ... thanks to our resident investigator Bruce (the list master) ... 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 11:49 AM To: Multiple recipients of list ORACLE-L I believe you'll need to use LogMiner. Dick Goulet -Original Message- Sent: Wednesday, March 26, 2003 11:14 AM To: Multiple recipients of list ORACLE-L 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.3c57 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 0x 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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: alter system dump logfile ... question
Title: RE: alter system dump logfile ... question Thanks Dennis, That's what I am trying to find ... which column to match ... and yes in past I have used logminer with a lot of success ... 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- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 26, 2003 2:09 PM To: '[EMAIL PROTECTED]' Cc: Jamadagni, Rajendra Subject: RE: alter system dump logfile ... question Raj I haven't tried reading logs without Logminer, but I have used Logminer without the dictionary. It is tough. Basically, everything is in hex. I would guess the who is the userid in hex. You could list the usernames and user_ids from dba_users and convert the user_id column to hex and see if a value matches. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 26, 2003 11:19 AM To: Multiple recipients of list ORACLE-L Subject: RE: alter system dump logfile ... question Dick, I tried various methods ... v$logmnr_contents has 0 rows ... that's why the hard way of doing things ... as usual metablink is little low of details about v$logmnr_contents containing no rows. Egads ... I am finally back on the list after 03/18 ... thanks to our resident investigator Bruce (the list master) ... 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- From: Goulet, Dick [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 26, 2003 11:49 AM To: Multiple recipients of list ORACLE-L Subject: RE: alter system dump logfile ... question I believe you'll need to use LogMiner. Dick Goulet -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Wednesday, March 26, 2003 11:14 AM To: Multiple recipients of list ORACLE-L Subject: alter system dump logfile ... question 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.3c57 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 0x 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 !! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: alter system dump logfile ... question
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 [EMAIL PROTECTED] 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.3c57 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 0x 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
