Tag Archives: interview

11g, ASM, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, RAC, SQL, uncategorized, unix

Senior-Level DBA Interview Question:

Leave a comment

With well over a decade of Oracle experience from version 6 to 11g, I have had my share of interviewing prospective DBA employees in a range from Junior to Senior DBAs. Needless to say in this field, technical competence is the key to any new hire and early on I made the blunder of not asking the write questions during the interview. I am sure every senior person or manager has made this mistake and had to deal with either training the person from scratch or letting them go. Either of these options is not desirable in our fast past business world. So, I decided to write a couple of articles which cover what I believe a Junior, Mid, and Senior Oracle DBA should know to fill their prospective positions.

In this article we will cover what a Senior Level DBA should know to be successful. If you have any comments or questions, please email me at larry.catt@relidb.com.

Questions: NOTE: Answers here can be very broad, however they not always wrong. That is why it is important to have a proven DBA on staff or one you can call to review the answers. I have seen very inventive ways to get around the most impossible tasks and while they do not actually meet the standard way of doing the work, I would not count these people out. A person whom thinks outside of the box is very valuable in IT and believe it or not, some of the best DBA’s I have ever met thought completely outside the box, however always accomplished the task.

1. You have a brand new Solaris Machine, freshly built by the SA, with all Oracle RDBMS recommended patching and Kernel Parameters set. Please give the order of tasks to install Oracle up to its latest CPU?

2. You have just moved Oracle Binary files from an ORACLE_HOME on one server to another server. What is the oracle utility command you would use to make this new ORACLE_HOME usable?

3. Tell me why just copying the oracle datafiles from HP-UX to LINUX RHEL and starting the database would not work?

4. In what months does Oracle release CPU patches and what is the utility to apply them.

5. You need to shutdown the oracle database but it will not shutdown with SHUTDOWN IMMEDIATE. How will you shutdown the database?

6. How do you logon to Oracle server without knowing any oracle passwords?

7. How would you determine that SCATTERED READ where occuring?

8. How would you see the SQL of the currently active sessions from a command prompt?

9. When having multiple oracle homes on a single server or client what is the parameter that points all Oracle installs at one TNSNAMES.ORA file.

10. You have ASM database that used by three production systems. The server which the ASM database is shutdown. What happens to the production RDBMS?

Answers:

1. You have a brand new Solaris Machine, freshly built by the SA, with all Oracle RDBMS recommended patching and Kernel Parameters set. Please give the order of tasks to install Oracle up to its latest CPU?

– Verify that all patches are installed and kernel parameters set.(Some not all SA say the system is ready when it is not. You can save yourself a lot of problems by double checking)
– Configuration of .profile or .bash_profile for the Oracle owner.
– Base Installation of Oracle (ie what to get to 10.2.0.4 must install Oracle 10.1 first).
– Upgrade to Oracle Version Desired.
– Upgrade the database(if upgrade is necessary)
– Run UTLRP.SQL.
– Apply lastest CPU patch.
– Run UTLRP.SQL.
– Configuration of Listener.
– Configuration of client TNSNAMES.ORA file.

2. You have just moved Oracle Binary files from an ORACLE_HOME on one server to another server. What is the oracle utility command you would use to make this new ORACLE_HOME usable?

Solution:
Relink all

3. Tell me why just copying the oracle datafiles from HP-UX to LINUX RHEL and starting the database would not work?

Solution:
Differing Indian codes.

4. In what months does Oracle release CPU patches and what is the utility to apply them.

Solution:
JAN, APR, JUL, OCT

5. You need to shutdown the oracle database but it will not shutdown with SHUTDOWN IMMEDIATE. How will you shutdown the database?

Solution:
Kill the smon process.

6. How do you logon to Oracle server without knowing any oracle passwords?

Solution:
Sudo to the Oracle software owners account and logon with the command sqlplus ‘/ as sysdba’

7. How would you determine that SCATTERED READ where?
Solution:

Look in the views v$session_wait or v$session_wait_history.

Could give:

Statspack or EM report.

8. How would you see the SQL of the currently active sessions from a command prompt?

Solution:
Compare tables V$SQL view by sql_address with V$SESSION view of currently active sessions.

9. When having multiple oracle homes on a single server or client what is the parameter that points all Oracle installs at one TNSNAMES.ORA file.

Solution:
TNS_ADMIN

10. You have ASM database that used by three production systems. The server which the ASM database is shutdown. What happens to the production RDBMS?

Solution:
Other database would shutdown abort.

Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Mid-Level DBA Interview Questions

Leave a comment

With well over a decade of Oracle experience from version 6 to 11g, I have had my share of interviewing prospective DBA employees in a range from Junior to Senior DBAs. Needless to say in this field, technical competence is the key to any new hire and early on I made the blunder of not asking the write questions during the interview. I am sure every senior person or manager has made this mistake and had to deal with either training the person from scratch or letting them go. Either of these options is not desirable in our fast past business world. So, I decided to write a couple articles which cover what I believe a Junior, Mid, and Senior Oracle DBA should know to fill their prospective positions.

In this article we will cover what a Mid Level DBA should know to be successful. If you have any comments or questions, please email me at larry.catt@relidb.com.

Questions:

1. Define the directory where Oracle developed scripts reside for the maintenance of an Oracle database and how would you call this directory from within the Oracle database?

2. Define the difference between, USER_, ALL_, and DBA_ data dictionary views and define the one column missing from USER_ which exists in both ALL_ and DBA_ views?

3. How would you find the number of invalid objects in an Oracle database and how would you resolve them?

4. Where are the network related files located on a standard Oracle installation and name two of the standard files you would find there?

5. Define the Oracle utility provided for backup and recovery of an Oracle database and the two options for storing information about backed up databases?

6. Define the Oracle utility used to manage an Oracle listener?

7. Define two UNIX layer system variables required for an Oracle database to operate properly?

8. Define the Oracle utility normally used to install CPU patches and where is this utility stored?

9. Name the Oracle package which allows you to schedule routine jobs within the Oracle database, just like CRON in UNIX or Scheduler in Windows?

10. Name the utility used to format perfstat report trace files?

Answers:

1. Define the directory where Oracle developed scripts reside for the maintenance of an Oracle database and how would you call this directory from within the Oracle database?

– The directory where Oracle stores the maintenance scripts is $ORACLE_HOME/rdbms/admin.
– The question mark represents the ORACLE_HOME within the database, thus you would call this directory with ‘?/rdbms/admin’. Another acceptable answer is ‘@?/rdbms/admin’.

2. Define the difference between, USER_, ALL_, and DBA_ data dictionary views and define the one column missing from USER_ which exists in both ALL_ and DBA_ views?

– USER_ – These views only contain definition of objects which you own.
– ALL_ – These views contain definitions of all objects which you have access to.
– DBA_ – These views contain definitions of all objects in the database, even if you do not have access to them.

– The USER_ views do not contain a column named OWNER, by definition of the view you are the owner.

3. How would you find the number of invalid objects in an Oracle database and how would you resolve them?

– By using the data dictionary view DBA_OBJECTS with the statement “select count(*) from dba_objects where status=’INVALID’;”
– You would use the Oracle script, “UTLRP.SQL” located in directory $ORACLE_HOME/rdbms/admin.

4. Where are the network related files located on a standard Oracle installation and name two of the standard files you would find there?

– In a standard Oracle installation the network files are located under “$ORACLE_HOME/network/admin”.
– Files located in this directory are: “LISTENER.ORA”, “TNSNAMES.ORA”, or “SQLNET.ORA”.

5. Define the Oracle utility provided for backup and recovery of an Oracle database and the two options for storing information about backed up databases?

– RMAN (Recovery Manager)
– The two methods for storing information about backed up database are: the controlfile or Recovery Catalog.

6. Define the Oracle utility used to manage an Oracle listener?

– LSNRCTL (LISTENER CONTROL)

7. Define two UNIX layer system variables required for an Oracle database to operate properly?

– $ORACLE_HOME – defines the directory where the Oracle binaries are installed.
– $ORACLE_SID – defines the instance name used to access a database.

8. Define the Oracle utility normally used to install CPU patches and where is this utility stored?

– “opatch” is the utility which is used to install CPU patches.
– The “opatch” utility is located in the directory “$ORACLE_HOME/OPatch”. NOTE: “opatch” is not a valid answer in a standard Oracle installation the directory has a capital O and P.

9. Name the Oracle package which allows you to schedule routine jobs within the Oracle database, just like CRON in UNIX or Scheduler in Windows?

– DBMS_JOBS – This answer is valid for all versions of Oracle.
– DBMS_SCHEDULER – This answer is only valid for Oracle 10g or higher.

10. Name the utility used to format perfstat report trace files?

– TKPROF

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com