Operating System - HP-UX
1748169 Members
4242 Online
108758 Solutions
New Discussion юеВ

Can you help me,about oracle database

 
l.g
Occasional Contributor

Can you help me,about oracle database

Somebody change my database ,
The database is Oracle 8.0.6 and in Hp-Ux11.0,
I want to know 'who and what time what he did',
Where can find the logfile and find about 'Update,Insert and Delete' information?


Thank you
8 REPLIES 8
Deepak Extross
Honored Contributor

Re: Can you help me,about oracle database

The first thing you should do is check who has update/delete permissions on your database. If it's a small set of users, you can probably zero down on your man by checking their shell history for recent sql calls.
If your database is open to the whole wide world, well, its going to be difficult.
If you have a rough idea of the time the data was changed, you can try to find out who was logged in at the time using 'last'.
l.g
Occasional Contributor

Re: Can you help me,about oracle database

Dear my friend ,I don't know that time ,and this database for world wide.
Did you know where is the logfile?Oracle has no alertlog?
I can't believe it!
Deepak Extross
Honored Contributor

Re: Can you help me,about oracle database

Are you expecting *every* SQL statement ever fired on your database to be logged? I'm not sure I'd want my database to do that, or the log would soon be bigger than my database iteslf.

By the way, you may find more people wanting to respond if you did something about your points-allocation history:
---------------------------
gaolu - forum profile
basic information
country:
HONG_KONG
personal quote:
certification:
member since:
February 26, 2002
last contribution date:
April 02, 2002
This member has assigned points to 0 of 12 responses to his/her questions.

------------------------
Tom Geudens
Honored Contributor

Re: Can you help me,about oracle database

Hi,
1. You'll find all the "logfiles" oracle has (if you follow the OFA structure) underneath /oracle1/app/oracle/admin/
To know which file is where, you'll have to take a look in the init.ora file for that SID. That file should be in /oracle1/app/oracle/admin//pfile

2. Probably these files are useless. They will show major changes in the environment, but not inserts, deletes, updates or even table drops.

3. In order to find those things you'll need your archivelog files. To find the location of those, you'll have to look into the init.ora file again (search for "log_archive_dest"). However, there is one small problem. There is no default/free tool to interpret those archivelogs :-(. On top of that you'll still have NO track of table drops.

4. In your case I would try a recovery of a backup of the database (in another location) of a time you are sure the database was still ok. I would then start replaying the archivelogs. This would help you pinpoint the exact time the change occured. However, this is a very intensive job ... :-(. It would help if you know what the "change" was exactly.

Hope this helps,
Tom Geudens
A life ? Cool ! Where can I download one of those from ?
Andreas D. Skjervold
Honored Contributor

Re: Can you help me,about oracle database

Hi

Oracle infact has a alertlog, but this functions only for the system, so it logs logswitches, stop and start and error messages.
Alertlog is found in $ORACLE_BASE/admin//bdump

To get more info logged you'll have to turn on auditing, but this will inturn generate wast amounts of data.

So I would say that you have no way of checking who did what in your database.

Andreas
Only by ignoring what everyone think is important, can you be aware of what everyone ignores!
l.g
Occasional Contributor

Re: Can you help me,about oracle database

Thanks

thanks your help!

If I have some I don't know
please tell me !

thanks again
Tom Geudens
Honored Contributor

Re: Can you help me,about oracle database

Hi,
Just one small point (as Deepak already mentioned). It would help if you assigned points to the answers (giving points according to how much a particular answer helped). You (and only you) can do this for this thread (you have to be logged in to do this). This has several positive effects :
- The people that answer get a "physical" reward for their effort.
- Other people know whether or not a question has been resolved (if you award 8 or more points to an answer, the thread gets a "rabbit" in front of it). This will also help when someone is searching a solution for a similar problem.
- You are more likely to get help if you assign points. I know it should not make a difference, but it does :-)

If you have questions on how this works, let me know !
Regards,
Tom Geudens

P.S. No points (N/A) for this answer
A life ? Cool ! Where can I download one of those from ?
Shixin Jiang
New Member

Re: Can you help me,about oracle database

Hi,

1. You can find out the session connection log to your database in $ORACLE_HOME/network/log/listener.log file. There is a timestamp, username, and host address (IP), etc.

2. You can also use logminer utility provided by Oracle to find out the DML statements againt your database objects.

Hope you can get a little help.
sjiang