OraFAQ Forum: Security » Need to find out how database got truncated.

Home » RDBMS Server » Security » Need to find out how database got truncated.

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

Need to find out how database got truncated. [message #411943]

Tue, 07 July 2009 05:56

poojaa
Messages: 6
Registered: July 2009
Location: India

Junior Member

Hi All,
I met with an absolute disaster last week when 3 entire databases got truncated one after the other.
We have now restored the Dbs using the back ups but we need to find the root cause.
After a little analysis we found out the time slot (Start:7/3/2009 1:07:56 AM
END: 7/3/2009 1:08:27 AM)
when the db was wiped off but no other detail could be learned as the audits were disabled.
Do we have any mechanism to find out what happened?
We have the cold back ups and the redo logs for analysis.
Please advise.

Thanks,
Pooja Acharya

Report message to a moderator

Re: Need to find out how database got truncated. [message #411953 is a reply to message #411943]

Tue, 07 July 2009 06:05

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

Senior Member
Account Moderator

Oracle version (4 decimals)?
What do you mean exactly by "wiped off"?

First have a look at alert.log, audit log (if you are in 10g and up), trace files.

Regards
Michel

Report message to a moderator

Re: Need to find out how database got truncated. [message #411966 is a reply to message #411953]

Tue, 07 July 2009 06:21

poojaa
Messages: 6
Registered: July 2009
Location: India

Junior Member

1. Oracle RDBMS server 10g (10.2.0.3)
2. Wiped-off meaning all the data was gone leaving behind empty tables
3. Audit logs were at default which i believe is disabled.
We could only find the logons and logoffs in dba_audit_session-no other info Sad

.
Will now check with alert logs.

Report message to a moderator

Re: Need to find out how database got truncated. [message #411970 is a reply to message #411966]

Tue, 07 July 2009 06:25

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

>>Wiped-off meaning all the data was gone leaving behind empty tables

Any error message in alert log?

Babu

Report message to a moderator

Re: Need to find out how database got truncated. [message #411983 is a reply to message #411970]

Tue, 07 July 2009 06:44

poojaa
Messages: 6
Registered: July 2009
Location: India

Junior Member

There is no error in the alert log..
Are these the only logs whichj we can look for?
Also i would like to know if there is any way we can learn the ip addresses logged on - in the specified time range..

Report message to a moderator

Re: Need to find out how database got truncated. [message #411989 is a reply to message #411966]

Tue, 07 July 2009 06:53

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

Senior Member
Account Moderator

Quote:

We could only find the logons and logoffs in dba_audit_session-no other info

When you connect as sysdba, a file is created at OS level. This is the one I asked.

You can use Log Miner on the archived files to know what happened.

Regards
Michel

Report message to a moderator

Re: Need to find out how database got truncated. [message #412002 is a reply to message #411989]

Tue, 07 July 2009 07:20

poojaa
Messages: 6
Registered: July 2009
Location: India

Junior Member

Michel,
Will the logMiner work even when supplemental logging is disabled as in my case?

Report message to a moderator

Re: Need to find out how database got truncated. [message #412012 is a reply to message #412002]

Tue, 07 July 2009 07:35

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

Senior Member
Account Moderator

Yes.
Log Miner existed BEFORE supplemental logging.

Regards
Michel

Report message to a moderator

Re: Need to find out how database got truncated. [message #412020 is a reply to message #412012]

Tue, 07 July 2009 07:45

poojaa
Messages: 6
Registered: July 2009
Location: India

Junior Member

Could u please give me some guidelines as to how to proceed with the analysis?
I am not a Dba so i have never had a chance to work with LogMiner.
Would Really appreciate it

Report message to a moderator

Re: Need to find out how database got truncated. [message #412025 is a reply to message #412020]

Tue, 07 July 2009 07:54

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

Senior Member
Account Moderator

Log Miner is described in the documentation.
http://tahiti.oracle.com
http://otn.oracle.com/pls/db102/db102.federated_search

Regards
Michel

Report message to a moderator

Re: Need to find out how database got truncated. [message #412027 is a reply to message #411943]

Tue, 07 July 2009 07:59

poojaa
Messages: 6
Registered: July 2009
Location: India

Junior Member

Thanks a lot..!!
Will let u know if i find something Smile

Regards,
Pooja

Report message to a moderator

Re: Need to find out how database got truncated. [message #412624 is a reply to message #412027]

Fri, 10 July 2009 03:10

kamkan
Messages: 27
Registered: April 2007
Location: Chennai, INDIA

Junior Member

Analyze listener log during that period for any clue ....

Report message to a moderator

Re: Need to find out how database got truncated. [message #412632 is a reply to message #412624]

Fri, 10 July 2009 03:46

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

Senior Member
Account Moderator

Listener.log will not give you what happened.
Maybe just who connected, only if (s)he has used SQL*Net.

Regards
Michel

Report message to a moderator

Previous Topic:	How to authenticate an oracle user by OID (Oracle Internet Directory)/ globally
Next Topic:	user authentication while using database link

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 10 20:11:45 CDT 2025