Who issued ALTER DATABASE RECOVER

Printable View

02-12-2002, 01:59 PM
marist89

Oracle 8.1.7.2
Solaris

I got a lovely ORA-1031 error message in my alert.log this morning stating:

Code:

Tue Feb 12 07:28:21 2002 ALTER DATABASE RECOVER Tue Feb 12 07:28:21 2002 ORA-1031 signalled during: ALTER DATABASE RECOVER ...

None of my DBA's have owned up to issuing this command nor have any of the users. Obviously, the user didn't know what they were doing, and Oracle handled it correctly, but I still want to know WHO did it.

I looked at the alert.log, listener.log, backup logs and other OS logs without any concrete proof. I can't use Logminer as this is a DDL command and I'm only on 8.1.7.2. I'm not running auditing on this database.

Any suggestions?
02-12-2002, 02:06 PM
Mr.Hanky

Start cleaning your gun!

How many people were still there at 7:30 pm and how many have the rights , knowledge (or lack therof) to issue this command?

If it is NT the event viewer may provide some info, any other "who was logged in" type logs.

Hire honest employees, I would have told you ASAP.

MH
02-12-2002, 02:10 PM
marist89

That's kind of the thing. My DBA's have the power to issue this command, but if they did, it would give a different error message.

I think it is a user, but I can't prove it.

I'm on Solaris and accounting is not turned on either.
02-12-2002, 02:10 PM
mbaliga

Hi,

What about audit log?

Baliga
02-12-2002, 02:11 PM
marist89

Quote:

Originally posted by Mr.Hanky

Hire honest employees, I would have told you ASAP.

If it IS one of my DBA's, I might have an opening soon ... :)
02-12-2002, 02:12 PM
marist89

Quote:

Originally posted by mbaliga
Hi,

What about audit log?

Baliga

Auditing is not turned on.
02-12-2002, 02:12 PM
Mr.Hanky

Jeff; Is there a possibility that Oracle issued this command at startup, something to do with cache recovery?

MH
02-12-2002, 02:13 PM
marist89

Quote:

Originally posted by Mr.Hanky
Jeff; Is there a possibility that Oracle issued this command at startup, something to do with cache recovery?

Database has been up since Oct 13, 2001.
02-12-2002, 02:24 PM
marist89

I know the v$sqlarea.address of the command, but since the user has logged out, I can't correlate it back to v$session.
02-12-2002, 02:56 PM
np70

Jeff,

listener.log will tell you which machine and time request came from (if it was different machine).

np70
02-12-2002, 03:03 PM
NDT

Quote:

Originally posted by marist89

Quote:

Originally posted by Mr.Hanky

Hire honest employees, I would have told you ASAP.

If it IS one of my DBA's, I might have an opening soon ... :)

Maybe it's a honest mistake, maybe someone accidently run a b&r shellscript which would trigger that command.
02-12-2002, 03:11 PM
marist89

Quote:

Originally posted by np70
Jeff,

listener.log will tell you which machine and time request came from (if it was different machine).

All the entries in listener.log have been accounted for. They all had a purpose and can be explained.
02-12-2002, 03:12 PM
marist89

Quote:

Originally posted by NDT

Maybe it's a honest mistake, maybe someone accidently run a b&r shellscript which would trigger that command.

I agree it's a mistake. I want to find out WHO so I can make them an honest person.
02-12-2002, 05:05 PM
maachan

If this was issued from OS, then do

last | more

at OS prompt

You can see what was logged on from what IP address at what time.

That might help to narrow it down some.
02-12-2002, 05:05 PM
Mr.Hanky

Ted Night, Caddyshack.

"WELL, WE'RE WAITING"

What's the deal, did you find the cause of this error.

MH
02-12-2002, 05:11 PM
marist89

Quote:

Originally posted by maachan
If this was issued from OS, then do

last | more

at OS prompt

You can see what was logged on from what IP address at what time.

That might help to narrow it down some.

last only shows who was logged on and not what commands they ran. There was nobody logged in to the box at the time. I would need lastcomm to show me which commands were executed, but that relies on OS accounting to be turned on.
02-12-2002, 05:12 PM
marist89

Quote:

Originally posted by Mr.Hanky

What's the deal, did you find the cause of this error.

Still investigating. I have my suspects, but no evidence yet.
02-12-2002, 05:22 PM
sln81

can you use the PARSING_USER_ID column in the V$SQLAREA and get some result?
02-12-2002, 05:51 PM
jmodic

Quote:

Originally posted by sln81
can you use the PARSING_USER_ID column in the V$SQLAREA and get some result?

The parsing of that statement obviously failed (insufficient privileges ORA error), so that particular statement was realy never put in the library cache. So looking for it in V$SQLAREA is useless, IMHO.
02-12-2002, 06:10 PM
marist89

It was actually in v$sqlarea with the correct timestamp, but since the parsing failed, I don't think the PARSING_USER_ID or PARSING_SCHEMA_ID are reliable (they both indicate SYS did the parsing but SYS would have privs...)
02-12-2002, 06:30 PM
sln81

Now this might be possible.

Those who have the sysdba priv could have issued the command. So parsing was successful and the execution might have failed because the DB is up and running.

If all of your dba's have sysdba privs, then .... ????
02-12-2002, 06:56 PM
jmodic

I was obviously speaking too soon in my previous post, saying that that particular statement was actualy never put in the library cache due to failde pares phase. I was wrong about that statement not being put in the library cache, but I was not wrong about failed parsing. That statement realy wasn't parsed succesfully, ORA-1031 (insufficient privileges) clearly indicates this.

I just did some tests on my home database. I logged as SCOTT (with only standard priviledges) and tried ALTER DATABASE RECOVER. It failed fith ORA-1031. So parsing of that statement failed, hovever it indeed was put in V$SQLAREA, with parsing_user_id=0.

Then I connected as SYS and tried the same RECOVER statement on an open database. This time it failed with "ORA-01124: cannot recover data file 1 - file is in use or recovery", which is also recorded in alert.log. This same happens when connected as any user with DBA role or ALTER DATABASE priviledge.

This indicates that the user who isued this statement on Jeff's database was not connected as SYS/INTERNAL or as SYSDBA, he even was not connected as a user with enabled DBA role or ALTER DATABASE privs. So I think this narrows Jeff's potential troublemakers to a non-dba users.

Now Jeff, surprise me and say that all your users have DBA role granted :D
02-12-2002, 07:11 PM
pravin

Jeff

Whats the latest update on this case. ?

How is the search and hunt going on .

Pravin.
02-12-2002, 10:17 PM
marist89

Quote:

Originally posted by jmodic
Now Jeff, surprise me and say that all your users have DBA role granted :D

No, no. Only a handfull of people have the DBA role. The scenario you tested validates my own tests today. I still have my suspicians, but can't prove anything. I'm contemplating auditing user connects and disconnects, but am concerned about the potential overhead during connect time. Somedays I hate being a DBA.