Pages

Tuesday, September 26, 2006

Oracle DBA Interview process

As a standard practice, the DBA selection process is split into 3 phases :

  1. Screening of resumes & filtering out the suitable candidates that can be interviewed.
  2. Scheduling & completing the interviews (rating & grading also go hand-in-hand during interview).
  3. Final rating of candidates & decision making.
The above procedures are described in detail below :

Screening of resumes & filtering out the suitable candidates that can be interviewed :

This is one of the relatively simpler stages.
  • When we receive resumes for DBA interviews, the first step is to skim through it & get a general idea about the layout, tidiness, clutter (if any) of the resume.
  • However, care needs to be taken to not reject a resume due to an unnecessary detail mentioned. The candidate might still be worth a trial.


Scheduling & completing the interviews (rating & grading also go hand-in-hand during interview) :
  • If possible, have multiple DBA's design and take the interview.
  • Use the rating chart (illustrated in next section) during the interview also. This will provide rough figures.
  • After the interview, consulting other DBA's who were present during interview, finalize the ratings.
  • We describe below a set of interview questions that we use progressively, to rate candidates on DBA track.
  • The standard first question that we ask to get the candidate comfortable & be able to exhibit his best, is to invite him to introduce himself as a DBA throughout his carrier since he / she started working.

Preliminary screening questions : These questions should be used to filter out candidates at an initial stage by testing the conceptual knowledge & smaller details about database administration. If one passes 70% of a chosen subset of preliminary questions, move to intermediate test. Same law applies to next section too. If preliminary passing is below 70%, pick only 2 questions from each next phase & seek answers.

  • What is tnsping ? Can you use tnsping to confirm the database status ? Always ?
  • What are the database startup phases ? What's importance of each phase ?
  • How to monitor a log file that is continuously updated ? How do you monitor databases ?
  • How do you go about tuning a SQL statement ? What is Explain plan & what is it's use ?
  • Types of backups ? Difference between hot, cold, logical backups ?
  • Difference between data block, extent & segment ? What is meant by PCTFREE & PCTUSED ?
  • Difference between truncate & delete ? Difference between CHAR(10) / VARCHAR2(10) ?
  • Unix (if worked) : How to see hidden files ? How to search specific text in unknown files ?
  • What's the significance of listener configuration file on a client ?

Intermediate-knowledge test :

  • Difference between gv$, v$, v_$ & x$ objects ?
  • How does column cardinality affect your choice of indexes during DB design ?
  • Statspack (if worked) : What are the levels of snapshots ? How to install & deinstall Statspack ? How would you use it for clustered environment ?
  • DB cloning procedure ?
  • How'd you recreate a parent table without affecting the child objects ?
  • Import - 40 GB & Undo tablespace - 20 / 30 GB - how to import ? How to improve import performance ?
  • RMAN (if worked) : Recovery procedure ?
  • How to find Oracle background processes in Windows ?

Advanced database administration questions :
We call these as "super questions" - which are the most tricky, with maximum chances of error. With clarity of concepts and enough experience, one can answer these.

  • How to sync up the OEM with agent ?
  • Significant requirements / points of complete / incomplete recovery ?
  • Import - multiple times ? What should be the strategy ?
  • What should be the backup strategy of a large database - in TB's ?
  • We have a set of datafiles without controlfile & without DB-name on a disc. How to recover the database ?
  • RAC setup requirements & procedure ?
  • How to grant read-only access to v$session ?


Answer guidelines to above Oracle DBA interview questions :

Preliminary screening questions :

  • What is tnsping ? Can you use tnsping to confirm the database status ? Always ?
  • A. Oracle utility to verify the status of a DB listener. No, we can never use - only listener can be verified.
  • What are the database startup phases ? What's importance of each phase ?
  • A. Instance start (SGA allocation based on init parameters), Mount (read control file & locate database files), open (open all the files in read-write mode - or read-only if a tablespace is read-only)
  • How to monitor a log file that is continuously updated ? How do you monitor databases ?
  • A. Write a script, schedule in cron for frequent intervals. Script can have a temporary copy of log & compare (diff) it with current log at every run, if there's difference, REPORT to DBA & replace old temporary copy with current copy.
  • A. OEM / Unix scripts / manually
  • How do you go about tuning a SQL statement ? What is Explain plan & what is it's use ?
  • A. Look at syntax, explain plan, index availability & usage, full table scans, etc. . Explain plan is an Oracle feature to see & analyze the detailed execution path of any SQL statement.
  • Types of backups ? Difference between hot, cold, logical backups ? How do you shutdown DB for logical backup ?
  • A. Hot / online - DB isn't shutdown (tablespace begin / end backup is used); Logical - export (no shutdown) , Cold - shutdown & backup files.
  • Difference between data block, extent & segment ? What is meant by PCTFREE & PCTUSED ?
  • A. Data block - smallest unit of allocation in database; extent - group of data blocks that can be allocated to an object; segment - group of extents belonging to a specific database object.
  • Difference between truncate & delete ? Difference between CHAR(10) / VARCHAR2(10) ?
  • A. 'trunc' resets high-water mark, deallocates space, can't be rolled back; 'delete' doesn't reset HWM as it doesn't release space, & can be rolled back.
  • A. CHAR(n) type allocates n fixed bytes, where VARCHAR2(n) allocates space upto n bytes only when an actual value is used.
  • Unix (if worked) : How to see hidden files ? How to search specific text in unknown files ?
  • A. '-a' ; grep "txt" ./*
  • What's the significance of listener configuration file on a client ?
  • A. No significance - it's not used.
Intermediate-knowledge test :

  • Difference between gv$, v$, v_$ & x$ objects ?
  • A. V_$ views are created over X$ base tables . v_$ views have v$ synonyms & gv$ views are global views that have multiple instance information (of the same DB - as in Oracle parallel server, RAC)
  • How does column cardinality affect your choice of indexes during DB design ?
  • A. If table is large & any column's cardinality to total rows ratio is small (less than 1:200), that column should have bitmap index creteated on in, if it is used in queries. Simply, if the unique values in a column is quite less, as compared to total rows in a table,bitmap index is required for better performance.
  • Statspack (if worked) : What are the levels of snapshots ? How to install & deinstall Statspack ? How would you use it for clustered (say unix level) environment ?
  • A. L1-7 ; Installation / removal - spcreate / spdrop ; Cluster - no change, as it is installed on the database level, not instance level.
  • DB cloning procedure ?
  • A. map & place online / cold backup data files --> generate & edit basic text controlfile --> nomount --> run 'create controlfile' from basic controlfile --> OPEN with restlogs
  • How'd you recreate a parent table without affecting the child objects ?
  • A. Disable constraints, recreate, enable.
  • Import - 40 GB & Undo tablespace - 20 / 30 GB - how to import ? How to improve import performance ?
  • A. Use commit ; Performance : groups of schemas in parallel
  • RMAN (if worked) : Recovery procedure ?
  • A. Tape identification --> restore --> recover
  • How to find Oracle background processes in Windows ?
  • A. 'procexp' - a windows tool helps find the Windows threads, which are Oracle background processes
Advanced database administration questions :

  • How to sync up the OEM with agent ?
  • A. Shut agent, remove server, clean agent directories (of .q & snmp.ro / rw files), restart, discover server
  • Significant requirements / points of complete / incomplete recovery ?
  • A. Complete - point-in-time (time based / ARCH no. based), incomplete - past-time recovery with resetlogs (cancel based)
  • Import - multiple times ? What should be the strategy ?
  • What should be the backup strategy of a large database - in TB's ?
  • A. RMAN - incremental L1 daily, Full L0 weekly
  • We have a set of datafiles without controlfile & without DB-name on a disc. How to recover the database ?
  • A. Generate basic text control file from any DB --> map & arrange available datafiles --> edit text controlfile for new paths & DB name --> NOMOUNT DB --> run the file as .sql --> OPEN DB with resetlogs
  • RAC setup requirements & procedure ?
  • A. Requirements - A. mimimum 2 servers, NAS storage, 2 NIC cards on all servers
  • A. Procedure - Coming soon.
  • How to grant read-only access to v$session ?
  • A. Grant select to v_$session


Final rating of candidates & decision making :

  • For effective rating, have a chart ready to list your comments & grades. We use a standard chart like this (open the chart in another window, for clarity, by clicking on it).
  • Feel free to add / subtract columns to the above Oracle DBA rating chart based on your individual interview requirements.
  • It's good to add interview dates, contact numbers, in "remarks" field along with other comments.
  • Have another round of interview if you feel necessary & make your decision based on the ratings.
When you prepare well & go well equipped with your weapons, you will have successful interviews & candidates would also be happy about the professionalism of the interviewer. Happy interviewing !
Feel free ask me any questions, through comments on this page.