Home » RDBMS Server » Security » Need to find out how database got truncated.
Need to find out how database got truncated. [message #411943] Tue, 07 July 2009 05:56 Go to next message
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
Re: Need to find out how database got truncated. [message #411953 is a reply to message #411943] Tue, 07 July 2009 06:05 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Need to find out how database got truncated. [message #411966 is a reply to message #411953] Tue, 07 July 2009 06:21 Go to previous messageGo to next message
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.
Re: Need to find out how database got truncated. [message #411970 is a reply to message #411966] Tue, 07 July 2009 06:25 Go to previous messageGo to next message
babuknb
Messages: 1703
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
Re: Need to find out how database got truncated. [message #411983 is a reply to message #411970] Tue, 07 July 2009 06:44 Go to previous messageGo to next message
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..
Re: Need to find out how database got truncated. [message #411989 is a reply to message #411966] Tue, 07 July 2009 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Need to find out how database got truncated. [message #412002 is a reply to message #411989] Tue, 07 July 2009 07:20 Go to previous messageGo to next message
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?
Re: Need to find out how database got truncated. [message #412012 is a reply to message #412002] Tue, 07 July 2009 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
Log Miner existed BEFORE supplemental logging.

Regards
Michel
Re: Need to find out how database got truncated. [message #412020 is a reply to message #412012] Tue, 07 July 2009 07:45 Go to previous messageGo to next message
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
Re: Need to find out how database got truncated. [message #412025 is a reply to message #412020] Tue, 07 July 2009 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Need to find out how database got truncated. [message #412027 is a reply to message #411943] Tue, 07 July 2009 07:59 Go to previous messageGo to next message
poojaa
Messages: 6
Registered: July 2009
Location: India
Junior Member
Thanks a lot..!!
Will let u know if i find something Smile

Regards,
Pooja
Re: Need to find out how database got truncated. [message #412624 is a reply to message #412027] Fri, 10 July 2009 03:10 Go to previous messageGo to next message
kamkan
Messages: 27
Registered: April 2007
Location: Chennai, INDIA
Junior Member
Analyze listener log during that period for any clue ....
Re: Need to find out how database got truncated. [message #412632 is a reply to message #412624] Fri, 10 July 2009 03:46 Go to previous message
Michel Cadot
Messages: 57613
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: How to authenticate an oracle user by OID (Oracle Internet Directory)/ globally
Next Topic: user authentication while using database link
Goto Forum:
  


Current Time: Sun Apr 20 03:40:40 CDT 2014

Total time taken to generate the page: 0.21282 seconds