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.