OraFAQ Forum: Server Administration » Oracle DBA interview

Home » RDBMS Server » Server Administration » Oracle DBA interview

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

Oracle DBA interview [message #165089]

Tue, 28 March 2006 05:22

dbasif
Messages: 35
Registered: March 2006
Location: Bangalore, India

Member

Hi All,

i had a dba technical round,i am sending few question which i have a doubt, can anybody explain me.

* user complains about db is slow, how will you proceed ?
(provided not to use any of the utility or statspack)
* how to find and recover the block corruption.
* what are semaphores and how it is important in oracle
* how to find out which user has generated a particular trace
file ?

Regards
Mohammad Asif
MindTree Consulting

Report message to a moderator

Re: Oracle DBA interview [message #165103 is a reply to message #165089]

Tue, 28 March 2006 06:12

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

What is your doubt?

Report message to a moderator

Re: Oracle DBA interview [message #165120 is a reply to message #165089]

Tue, 28 March 2006 06:47

kmsalih
Messages: 197
Registered: March 2006

Senior Member

Hi

There are so many methods for your first issue.

I will explain one.

Write a shell script to know the most cpu consuming users.( Use ps command)
If users are complaining that the db is slow run that.

From the result generated by the shell script you will get the spid of the process taking more cpu.

Query the database and find which is the user.

According to the importance of that session keep or kill that session.

Regards
SALIH KM

Report message to a moderator

Re: Oracle DBA interview [message #165231 is a reply to message #165089]

Wed, 29 March 2006 00:29

gajini
Messages: 262
Registered: January 2006

Senior Member

Can anyone answer the following the DBA interview questions

* how to find and recover the block corruption.
* how to find out which user has generated a particular trace
file ?

Thanks,
Malru

Report message to a moderator

Re: Oracle DBA interview [message #165239 is a reply to message #165120]

Wed, 29 March 2006 00:49

dbasif
Messages: 35
Registered: March 2006
Location: Bangalore, India

Member

Thanx Salih for ur reply.
but i also have given the same answer like, to find out from dictionary view which user is blocking the current session, and then kill that if that is not much important.

but the interviewer din't satisfied, he wanted some other solution, any clue

Regards
Asif

Report message to a moderator

Re: Oracle DBA interview [message #165242 is a reply to message #165231]

Wed, 29 March 2006 00:56

kmsalih
Messages: 197
Registered: March 2006

Senior Member

Hi ,

Finding the Block Corruption

Look the alert log file

Finding the owner of the trace file generated

Trace file will contain a number.
That is server process id (spid).

Query

Select p.spid, s.username from v_$sesssion where p.addr=s.paddr and background is null;

It will show the spid of all sessions except the sessions used by bgprocess.

Rgds
SALIH KM Cool

Report message to a moderator

Re: Oracle DBA interview [message #165248 is a reply to message #165239]

Wed, 29 March 2006 01:06

kmsalih
Messages: 197
Registered: March 2006

Senior Member

Hi Asif,

There are other ways like

1. Monitor V$session_longops
2. Monitor V$sql (executions,) and make the execution path of those sql.

Also it depends upon situation.

Rgds
SALIH KM Cool

[Updated on: Wed, 29 March 2006 01:07]

Report message to a moderator

Re: Oracle DBA interview [message #165251 is a reply to message #165242]

Wed, 29 March 2006 01:12

dbasif
Messages: 35
Registered: March 2006
Location: Bangalore, India

Member

Thanx Salih,

i have cheked, this works :

Select p.spid, s.username from v$session s,v$process p where p.addr=s.paddr

Regards
Asif

Report message to a moderator

Re: Oracle DBA interview [message #165610 is a reply to message #165251]

Fri, 31 March 2006 01:19

madhusunkara
Messages: 59
Registered: March 2006
Location: hyderabad,india

Member

for block corruption detection you can use any of the folowing utilities
1.dbverify
2.export
3.RMAN
4.alert log

dbverify can only do offline check

for recovery you have to use RMAN

Thanks
Madhu

Report message to a moderator

Re: Oracle DBA interview [message #165611 is a reply to message #165610]

Fri, 31 March 2006 01:21

madhusunkara
Messages: 59
Registered: March 2006
Location: hyderabad,india

Member

useful link talks about block corruption
http://www.quest-pipelines.com/newsletter-v4/0103_C.htm

Report message to a moderator

Re: Oracle DBA interview [message #165893 is a reply to message #165089]

Mon, 03 April 2006 04:02

nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...

Senior Member

* user complains about db is slow, how will you proceed ?
(provided not to use any of the utility or statspack)
check the top 10 user list by cpu on oem?
Take a snapshot,using oem, of a running database and see what is going on. Run the snapshot through oem's sql analyser.

* what are semaphores and how it is important in oracle
I *think* they are the way Oracle keeps track of resources(table rows, indexes, whatever).

Microsoft asks dba's how many gas stations are in your town. The answer is not important, they just want to see how you got your answer. Also, they ask "why is a manhole cover round", with the answer being 'it is the only geometric shape that will not fall in on itself.
But that's just Microsoft. For getting a jump on interview questons try Don Berleson's interview book at www.donberleson.com?
Neil.

[Updated on: Mon, 03 April 2006 04:10]

Report message to a moderator

Re: Oracle DBA interview [message #166187 is a reply to message #165611]

Wed, 05 April 2006 01:39

dbasif
Messages: 35
Registered: March 2006
Location: Bangalore, India

Member

Thanks Madhu, this document cleared all my doubts about block corruption.

Report message to a moderator

Previous Topic:	to know all the users
Next Topic:	oracle9i on suse linux

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 27 02:34:41 CDT 2024