OraFAQ Forum: Backup & Recovery » Data missed from table

Home » RDBMS Server » Backup & Recovery » Data missed from table (Oracle,10.2.0.4.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data missed from table [message #561020]

Wed, 18 July 2012 05:37

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

Yesterday my colleague noticed that, the complete data (1 year) got missed out from a table and the data is available only from last friday.

And then we restroed the data from the backup.

Now we have been asked to find out who has deleted or truncated the data? But in the database, audit is enabled only to log sys operations.
So is there any way to find who ran the delete or truncate command on this DCA_CLA_BATCH table on friday?

SQL> show parameter audit

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
audit_file_dest                      string                           /dborafiles/edcap/edcapd/adump
audit_sys_operations                 boolean                          TRUE
audit_syslog_level                   string
audit_trail                          string                           OS

Report message to a moderator

Re: Data missed from table [message #561025 is a reply to message #561020]

Wed, 18 July 2012 06:05

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Database Utilities
Chapter 17 Using LogMiner to Analyze Redo Log Files

Regards
Michel

Report message to a moderator

Re: Data missed from table [message #561034 is a reply to message #561025]

Wed, 18 July 2012 06:58

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

Hi Michel,

If I use logminer, Can I get the machine and osuser detail for that delete or truncate query?

Report message to a moderator

Re: Data missed from table [message #561038 is a reply to message #561034]

Wed, 18 July 2012 07:04

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can have all the following information:

SQL> desc V$LOGMNR_CONTENTS
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 SCN                                       NUMBER
 CSCN                                      NUMBER
 TIMESTAMP                                 DATE
 COMMIT_TIMESTAMP                          DATE
 THREAD#                                   NUMBER
 LOG_ID                                    NUMBER
 XIDUSN                                    NUMBER
 XIDSLT                                    NUMBER
 XIDSQN                                    NUMBER
 PXIDUSN                                   NUMBER
 PXIDSLT                                   NUMBER
 PXIDSQN                                   NUMBER
 RBASQN                                    NUMBER
 RBABLK                                    NUMBER
 RBABYTE                                   NUMBER
 UBAFIL                                    NUMBER
 UBABLK                                    NUMBER
 UBAREC                                    NUMBER
 UBASQN                                    NUMBER
 ABS_FILE#                                 NUMBER
 REL_FILE#                                 NUMBER
 DATA_BLK#                                 NUMBER
 DATA_OBJ#                                 NUMBER
 DATA_OBJD#                                NUMBER
 SEG_OWNER                                 VARCHAR2(32)
 SEG_NAME                                  VARCHAR2(256)
 TABLE_NAME                                VARCHAR2(32)
 SEG_TYPE                                  NUMBER
 SEG_TYPE_NAME                             VARCHAR2(32)
 TABLE_SPACE                               VARCHAR2(32)
 ROW_ID                                    VARCHAR2(18)
 SESSION#                                  NUMBER
 SERIAL#                                   NUMBER
 USERNAME                                  VARCHAR2(30)
 SESSION_INFO                              VARCHAR2(4000)
 TX_NAME                                   VARCHAR2(256)
 ROLLBACK                                  NUMBER
 OPERATION                                 VARCHAR2(32)
 OPERATION_CODE                            NUMBER
 SQL_REDO                                  VARCHAR2(4000)
 SQL_UNDO                                  VARCHAR2(4000)
 RS_ID                                     VARCHAR2(32)
 SEQUENCE#                                 NUMBER
 SSN                                       NUMBER
 CSF                                       NUMBER
 INFO                                      VARCHAR2(32)
 STATUS                                    NUMBER
 REDO_VALUE                                NUMBER
 UNDO_VALUE                                NUMBER
 SQL_COLUMN_TYPE                           VARCHAR2(30)
 SQL_COLUMN_NAME                           VARCHAR2(30)
 REDO_LENGTH                               NUMBER
 REDO_OFFSET                               NUMBER
 UNDO_LENGTH                               NUMBER
 UNDO_OFFSET                               NUMBER
 DATA_OBJV#                                NUMBER
 SAFE_RESUME_SCN                           NUMBER
 XID                                       RAW(8)
 PXID                                      RAW(8)
 AUDIT_SESSIONID                           NUMBER

Regards
Michel

Report message to a moderator

Re: Data missed from table [message #561226 is a reply to message #561038]

Thu, 19 July 2012 10:11

sathik
Messages: 196
Registered: August 2009
Location: INDIA

Senior Member

Thanks Michel. I tried and got my required info in the session_info column.

Report message to a moderator

Previous Topic:	cloning script request
Next Topic:	Need to restore database, have all files

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 02 17:33:01 CST 2025