Interviewer Scenario

From an interviewer:
I tend more towards trying to determine if the candidate has honest to goodness real world experience or is it all book knowledge and passed the OCP.

An OCP is good (don't get me wrong) but I'd almost rather considersomeone that has actually "been there done that".

Did an interview for a contractor once. Some pretty tough questions. Inoticed she kept glancing towards a spiral notebook she had carried in.I asked her if the answers I was looking for were in the book.When she told me they were, I told her to find them and answer myquestion.The fact that she had made a note of the problem and the 'fix' told meshe had indeed run in that problem and thought enough about it to makesnotes of the fix.Told her to use her notes the remainder of the interview if she wanted.

I am a firm believer that you don't try to remember everything....you'll never do it ...especially as you get a little further along in age. You just MIGHT remember it incorrectly and cause yourself more pain than gain.Books exist for more than lining your shelves.

As far as the individual not knowing what RAC was/is.....maybe theyhaven't had the 'privilege' of working on 9i yet and their world iscentered still in 8i or even V7. Did they know what OPS was/is?
How often do you REALLY get in to histograms on a daily basis?
I'd much rather a DBA know how to backup and be able to restore and recover a database than give me a stock/pat answer on what a histogramis and how it is used.

Reference: http://uni-base.com/ArticleContainer.aspx?articleId=10&ArticleTypeId=2

1. Why is a UNION ALL faster than a UNION?
The union operation, you will recall, brings two sets of data together. It will *NOT* however produce duplicate or redundant rows. To perform this feat of magic, a SORT operation is done on both tables. This is obviously computationally intensive, and uses significant memory as well. A UNION ALL conversely just dumps collection of both sets together in random order, not worrying about duplicates.

2. What are some advantages to using Oracle's CREATE DATABASE statement to create a new database manually?
You can script the process to include it in a set of install scripts you deliver with a product.
You can put your create database script in CVS for version control, so as you make changes or adjustments to it, you can track them like you do changes to software code.
You can log the output and review it for errors.
You learn more about the process of database creation, such as what options are available and why.

3. What are three rules of thumb to create good passwords? How would a DBA enforce those rules in Oracle? What business challenges might you encounter?
Typical password cracking software uses a dictionary in the local language, as well as a list of proper names, and combinations thereof to attempt to guess unknown passwords. Since computers can churn through 10's of thousands of attempts quickly, this can be a very affective way to break into a database. A good password therefore should not be a dictionary word, it should not be a proper name, birthday, or other obvious guessable information. It should also be of sufficient length, such as eight to ten characters, including upper and lowercase, special characters, and even alternate characters if possible.
Oracle has a facility called password security profiles. When installed they can enforce complexity, and length rules as well as other password related security measures.
In the security arena, passwords can be made better, and it is a fairly solvable problem. However, what about in the real-world? Often the biggest challenge is in implementing a set of rules like this in the enterprise. There will likely be a lot of resistance to this, as it creates additional hassles for users of the system who may not be used to thinking about security seriously. Educating business folks about the real risks, by coming up with real stories of vulnerabilities and break-ins you've encountered on the job, or those discussed on the internet goes a long way towards emphasizing what is at stake.

4. Describe the Oracle Wait Interface, how it works, and what it provides. What are some limitations? What do the db_file_sequential_read and db_file_scattered_read events indicate?
The Oracle Wait Interface refers to Oracle's data dictionary for managing wait events. Selecting from tables such as v$system_event and v$session_event give you event totals through the life of the database (or session). The former are totals for the whole system, and latter on a per session basis. The event db_file_sequential_read refers to single block reads, and table accesses by rowid. db_file_scattered_read conversely refers to full table scans. It is so named because the blocks are read, and scattered into the buffer cache.

5. How do you return the top-N results of a query in Oracle? Why doesn't the obvious method work?
Most people think of using the ROWNUM pseudocolumn with ORDER BY. Unfortunately the ROWNUM is determined *before* the ORDER BY so you don't get the results you want. The answer is to use a subquery to do the ORDER BY first. For example to return the top-5 employees by salary:
SELECT * FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM < 5;

6. Can Oracle's Data Guard be used on Standard Edition, and if so how? How can you test that the standby database is in sync?
Oracle's Data Guard technology is a layer of software and automation built on top of the standby database facility. In Oracle Standard Edition it is possible to be a standby database, and update it *manually*. Roughly, put your production database in archivelog mode. Create a hotbackup of the database and move it to the standby machine. Then create a standby controlfile on the production machine, and ship that file, along with all the archived redolog files to the standby server. Once you have all these files assembled, place them in their proper locations, recover the standby database, and you're ready to roll. From this point on, you must manually ship, and manually apply those archived redologs to stay in sync with production.
To test your standby database, make a change to a table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you're done, shutdown your standby and startup again in standby mode.

7. What is a database link? What is the difference between a public and a private database link? What is a fixed user database link?
A database link allows you to make a connection with a remote database, Oracle or not, and query tables from it, even incorporating those accesses with joins to local tables.
A private database link only works for, and is accessible to the user/schema that owns it. A global one can be accessed by any user in the database.
A fixed user link specifies that you will connect to the remote db as one and only one user that is defined in the link. Alternatively, a current user database link will connect as the current user you are logged in as.

Screening and Interview Questions for DBA

Reference: http://grumpy-dba.com/ - by Jay Caviness

I mentioned ... that when I interview DBA’s I want to see if they know why things work, not just how they work. I want to see an underlying systemic understanding of not only the database but how other parts of a system affect and are affected by the database. However, to get to that point there is typically a pre-screening phone interview to weed out applicants, and I do need to know core knowledge. Depending on the level of DBA I am looking for come a few questions, I will ask some of the lower level questions to senior level people because I have seen many-a-DBA lock up on remembering the parts of the SGA.

Junior DBA:
What is the SGA? What are its components?
Name several methods to back up an Oracle database. What are their advantages/disadvantages?
What are the four states a database instance can be in?
What are undo segments for?
What is the temporary tablespace?

Mid-Level DBA
What is the cost based optimizer? What are statistics, histograms, etc?
What happens during a crash recovery?
How do you recover from a lost: datafile, tablespace, undo tablespace, control file?
Is a full table scan ever better than an index scan? If so, how and why?
What is an ORA-1555, how can it be avoided?

Senior Level
What is your philosophy on datafile placement on SAN, NAS or local storage?
When is it appropriate to set tracing on SQL*Net?
When is RAC/Dataguard/streams appropriate to use for high availability?
List methods to prevent, detect and repair corruption in a database
Finally, my database is slow, ask me five questions to determine a cause.

This is a starting point. You may have noticed that the questions get more open ended as the level increases. Generally the more complex a problem the more solutions can present themselves

Reference: http://neworacledba.blogspot.com/2008/04/performance-tuning.html

" Here are common Oracle DBA Questions I have encountered: "

1. What is the format of an Oracle Release Number?

Example: 10.1.0.3.010 - Major database release number1 - Database maintenance release number0 - Application server release number3 - Component specific release number0 - Platform specific release number

2. What is a mutating table?
A mutating table is a table that is currently being modified by an update, delete, or insert statement. If an application has a row trigger that reads or modifies the mutating table, the common ORA-04091 error is generated.
ORA-04091: "table %s.%s is mutating, trigger/function may not see it" Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it. Action: Rewrite the trigger (or function) so it does not read that table.

3. What is the difference between deadlock and blocking lock?

A deadlock is a condition where two or more users are waiting for data locked by each other.A blocking lock is held by the blocking session.

4. What are the data structures used for database recovery?-

Redo Logs- Rollback and Undo Segments- Control Files

5. When monitoring Oracle databases, what are the common V$ used?

V$PROCESSV$SESSION V$SESS_IO V$SESSION_LONGOPS V$SESSION_WAIT V$SYSSTAT V$RESOURCE_LIMIT V$SQLAREA V$LATCH

6. What are some Initialization Parameters that affect performance?-

DB_BLOCK_SIZE: database block size- DB_CACHE_SIZE: size of the buffer cache in the SGA- SHARED_POOL_SIZE: size of the shared pool in the SGA- PROCESSES: maximum number of processes that can be started by that instance- SESSIONS: set by default from the value of processes- JAVA_POOL_SIZE: If using Java stored procedures, this parameter should be set depending on the memory requirements for the Java environment- LOG_ARCHIVE_XXX: Enables redo log archiving. - ROLLBACK_SEGMENTS: Allocates one or more rollback segments by name to this instance

7. What is the difference between Suspending a database and Quiescing a database?

Starting with 9i, the concepts of Suspending and Quiescing a database were introduced. Suspending a database : database suspend/resume feature- ALTER SYSTEM SUSPEND statement suspends a database by halting all input and output (I/O) to datafiles and control files- ALTER SYSTEM RESUME statement resumes normal database operationQuiescing a database: By putting a database on quiesced state, only DBA transactions, queries, or PL/SQL statements are allowed- ALTER SYSTEM QUIESCE statement puts the database into a quiesced state

Reference: http://jarneil.wordpress.com/2008/02/10/interviewing-oracle-dbas/

"First off, I think it is fair to say the interview was split roughly in two, with the first half covering more attitudinal questions and the second half covering more technical Oracle questions. We certainly place as much weight on finding the correct sort of candidate, one with a good attitude to learning and customer service, as much as what particular level of Oracle skills they have. Also it’s worth pointing out this is for a job in a small company, where there is no strong demarcation of responsibilities, so the DBAs are expected to do a goodly amount of system administration work, and have ownership of configuring the SANs. The candidate we chose I would say displayed excellent answers in both the attitudinal and technical questions. Here are the Oracle questions we asked, I think these are pretty straightforward and gentle questions, there are not even that many of them!"

What approach would you take if asked to investigate a query that was running slowly?
A couple of candidates answered this pretty well. I was looking for things like checking the explain plan, and tracing the session. I was not looking for someone to mention the buffer cache hit ratio - though someone did!

What SQL commands will cause a sort to take place?
Unfortunately a few candidates did not get past order by (which all got), some said distinct and group by. The candidate we hired mentioned these and UNION. No one mentioned create index.

How does Oracle implement read-consistency?
Quite a few candidates stumbled at this question. It is such a fundamental part of the Oracle RDBMS that you would hope a candidate would at least have go at answering. A couple of candidates said they were stumped. I was looking for them to mention that a query only sees data committed before the start of the query and uses undo to reconstruct any data if it finds it with a commited time that was after the start of the query. No one mentioned SCN.

Do you know what ORA-01555 is?
This is somewhat related to the previous question. Practically every candidate did not know this. I do not expect a candidate to know every single Oracle error message, I certainly don’t. However 1555 is such a classic error that I would expect good dbas to know what it is. The one candidate who did know that it’s snapshot too old, got the job.

What resources do you use to keep up-to-date with Oracle?
Well I probably was a little disappointed with the responses to this one, only one candidate mentioned a good spread of bloggers, or indeed blogs at all. Everyone mentioned OTN, but no one mentioned actually downloading a new release and trying it out for themselves. Oh and this question lead on to which authors the candidates liked, Tom Kyte was the definite winner with Steven Feuerstein in second place. I was astounded not one candidate mentioned Jonathan Lewis.

Explain the architecture of RAC?
So, I realise this could potentially lead to a wide ranging answer, but most of the candidates had RAC on their CV so you’d expect them to able to explain it at least to a level that they new it was different from single instance Oracle. Minimum requirement was multiple instances accessing the same database datafiles, communicating through a private interconnect, which most met.
Finally, here is one of the non-technical questions we asked:

What kind of working environment do you need to be most effective?
I’ll leave it to the reader to work out what we expected from this one!