who shut the DB down?

We have 5 dba's and all of them have the power to shut down the DB. I think accidentally, one of them shut down the DB during peak times but no one one wants to admit to it.

Auditing has not being enabled yet. Is there any way that I can find out exactly who shut the DB down?

you check the hour of the shutting down in alert.log, and you torture them each until finding what have done each of them at this hour.
Or, put them on the lie detector.
Check for all alibi.
Hire a detective.
Install a candy camera.

What version was it ? If its 8i, minimum actions of startup and shutdown will be logged in default audit location.

Again, It depends you use Oracle/System super user or userid with DBA privs...

Check your audit trails and/or alert logs. Events like startup, shutdown and connect with admin privileges are automatically audited by Oracle, having you enabled it or not.


Adriano.

Thanks "guys", I checked the alert log and I found this message:
Instance terminated by USER, pid = 28101

But when I did:

select username, user_id, external_name from dba_users;

I did not get anything closer to 28101.
Can any body give me some direction on how to proceed?

Thanks,
clinton

Hi,
the PID is created by the OS and once the process dies, any other user can get the same process id later on. Even auditing won't help you to know who gave the shutdown unless each DBA logs in as a different User. Trace file will help in determining the time /cause of the shutdown.


Cheers

You could check the status of object each DBA was working with. Shutdown doesn't happen by accident. Somebody thought they could cover their tracks . . .

Look for missing/changed data or objects.

Good luck

I think that by running the logminer, you would be able to trace the sessions to the last and from that pont you can kind of get who was that black sheep. :D

Sam

Are you working in UNIX environment? if yes then give me some details like which machine and which version of operating system..........

Quote:

---

Originally posted by clinton
We have 5 dba's and all of them have the power to shut down the DB. I think accidentally, one of them shut down the DB during peak times but no one one wants to admit to it.

Auditing has not being enabled yet. Is there any way that I can find out exactly who shut the DB down?

---

Here is some piece of information to track the culprit.

Check for a particular file that was generated at $ORACLE_HOME/rdbms/audit when the database was shutdown (i.e. check the date/time stamp).

In the file, you may see something like this...

...
Tue Mar 6 11:50:30 2001
ACTION : 'connect internal' OSPRIV : DBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/33

Tue Mar 6 11:51:50 2001
ACTION : 'shutdown'
...

If every user has a unique unix userid, then from there you can identify by the 'CLIENT USER' as shown above. But if you have a common userid to logon to your server, just like 'oracle' in our case, then refer to what's in the 'CLIENT TERMINAL', w/c is 'pts/33' in our case.

Using the 'client terminal' info, use the 'last' unix command to display from what host the user TELNETed from, as well as, the server logon time, as follows.


>last | grep pts/33
rsupport pts/33 dhcp-233113.ca.o Wed Mar 7 10:18 still logged
in
rsupport pts/33 dhcp-233131.ca.o Tue Mar 6 12:29 - 14:26
(01:57)
rsupport pts/33 dhcp-233131.ca.o Tue Mar 6 11:45 - 12:13
(00:28)

Then 'ping' the hostname from your Microsoft Windows command line to determine the IP address of the origin host machine. Once you have the IP address, issue the command, 'nbtstat -A ' to determine the username and hostname the IP address is assigned to.


Hope this would help you to nail the person :D

Sam

Hi Sam and the rest ,
I think with sam's method, I'm on the right way to capture the culprit.

One last question.
When I went through the ORACLE_HOME, I found out that, the
default audit trail were a lot(i mean a lot) so I could not really narrow down the time stamp. Do you know of any easier way to narrow the time stamp?

This is what I did:
cd $ORACLE_HOME/rdbms/audit
ls

ora_29944.aud ora_43866.aud ora_56872.aud ora_9304562.aud
ora_29946.aud ora_43922.aud ora_56882.aud ora_9305594.aud
ora_30202.aud ora_43934.aud ora_56890.aud ora_9308434.aud
ora_30442.aud ora_44150.aud ora_56898.aud ora_9312820.aud
ora_30466.aud ora_44368.aud ora_56914.aud ora_9316174.aud
ora_30482.aud ora_44896.aud ora_56922.aud ora_9319786.aud
ora_30978.aud ora_44902.aud ora_56928.aud ora_93226.aud
ora_30982.aud ora_45158.aud ora_57046.aud ora_9323398.aud
ora_31068.aud ora_45354.aud ora_57050.aud ora_9327010.aud
ora_31074.aud ora_45428.aud ora_57056.aud ora_94744.aud
ora_31224.aud ora_45476.aud ora_57134.aud ora_94750.aud
ora_31276.aud ora_45684.aud ora_57280.aud ora_94958.aud
ora_31744.aud ora_45728.aud ora_57282.aud ora_95916.aud
ora_31994.aud ora_45730.aud ora_57288.aud ora_96880.aud
ora_32082.aud ora_45860.aud ora_57290.aud ora_97762.aud
ora_32090.aud ora_45924.aud ora_57292.aud ora_97944.aud
ora_32098.aud ora_45930.aud ora_57296.aud ora_9796.aud
ora_32106.aud ora_45932.aud ora_57314.aud ora_98046.aud
ora_32114.aud ora_45938.aud ora_57344.aud ora_98424.aud
ora_32134.aud ora_46088.aud ora_57350.aud ora_98430.aud
ora_32196.aud ora_46094.aud ora_57356.aud ora_99546.aud



NOTE: THIS IS JUST AN EDITED VERSION.
IS THERE AN EASIER WAY TO FIND THE TIME STAMP? OR I HAVE TO GO THROUGH EACH OF THEM INDIVIDUALLY?




thanks,
Clinton

One way to do this is write a shell script that would list the directory and awk the files between certain time frame and then you could reduce the files that you need to search. Other way would be to write a script that would open all the files and search for the "SHUTDOWN" word and then list only those files that has this. One other eaier way is to use the "find " command and search the directory for shutdown and then start analysing only those files.

Hope this would help you.
Sam

Searching the files for the word 'SHUTDOWN' can be done with a single command:

grep -i shutdown ora*.aud

(-i means ignore case)

or, just to get the filenames

grep -l -i shutdown ora*.aud

Also, the ls -l command will give the list of files, including the modification date, if you pipe the output of the ls to the grep command (look at the format of the date returned), you can quickly get a list of files for a certain date.

e.g.

ls -l ora*.aud | grep "Mar 08"

to list all files for the specified date.

HTH

David.

[Edited by Dave_A on 03-08-2001 at 08:25 AM]