Reference:http://www.exforsys.com/forum/oracle-database/98713-dba-interview-questions.html - Questions from Shanthi:

(Many questions have been repeated in other posts)

What are things which play major role in designing the backup strategy?

What are RTO, MTBF, and MTTR?

What are the various tape backup solutions available in the market?

What is RAID? What is RAID0? What is RAID1? What is RAID 10?

What is the difference between physical and logical backups?

What is hot backup and what is cold backup?

When do you recommend hot backup? What are the pre-reqs?

How do you generate the begin backup script?

Give me the steps for recovering the full database from cold backup?

Give me the steps for TIME based recovery from the full database from hot backup. ?

Give me the steps for SCN based recovery from the full database from hot backup. ?

Give me the steps for CANCEL based recovery from the full database from hot backup. ?

Give me the steps for recovery with missing archived redo logs?

Give me the steps for recovery with missing online redo logs?

Give me the steps for recovery of missing data file?

Give me the steps for physical standby database creation?

What is db_recovery_file_dest ? When do you need to set this value ?

Give me the steps to perform the point in time recovery with a backup which is taken before the resetlogs of the db ?

Tell me about the steps required to enable the RMAN backup for a target database ?

What is the difference between obsolete RMAN backups and expired RMAN backups ?

What is recovery catalog ? Why do you need this ?

How do you install the RMAN recovery catalog ?

Can you skip specific tables when using RMAN DUPLICATE feature ?

What is FRA ? When do you use this ?

How do you clone the database using RMAN software ? Give me the brief steps ?

when do you use crosscheck command ?

How do you setup the RMAN tape backups ?

How do you identify the expired,active,obsolete backups ? Which RMAN command you use ?

What is channel ?

How do you enable the parallel backups with RMAN ?

What is auxiliary channel in RMAN? When do you need this ?

How do you identify what are the all the target databases that are being backed-up with RMAN database ?

Give me some of the RMAN catalog view names which contains the catalog information ?

What is oracle wallet ?

Tell me about the encryption options available with RMAN ?

List atleast 6 advantages of RMAN backups compare to traditional hot backups ?

How do you identikit the block corruption in RMAN database ? How do you fix this ?

How do you verify the integrity of the image copy in RMAN environment ?

How do you enable the encryption for RMAN backups ?

What is backup set ?

What is the difference between cumulative incremental and differential incremental backups ?

How do you enable the autobackup for the controlfile using RMAN ?

What is configure command ? When do you use this ?

Give me the steps for setting up the physical stand by database with RMAN ?

Give me the steps for changing the DBID in a cloned environment ?

What is the significance of incarnation and DBID in the RMAN backups ?

What are the steps which needs to be performed in $ORACLE_HOME for enabling the RMAN backups with netbackup or TSM tape library software ?

Questions for the candidate

Reference:http://www.kevinloney.com/free/newdbas/intrview.htm

Perseverance in technical expertise:
1. What errors did you hit during your most recent database recovery?
2. What was the most difficult technical obstacle you encountered during your last project?

Perseverance in relationships:
1. What negative "group relationship" issues exist in your current working environment? How are you addressing them?
2. What is your most difficult set of users, and how do you manage that relationship?
3. What do you want to learn in the next 12 months?
4. What communications method do you usually use when dealing with users? Can you provide examples?
5. What communications method is most effective when customers need your help?

Technical Experience:
(Use hypothetical questions rather than straight technical questions)
1. What database and overall architecture would you suggest for testing new middleware without impacting production?
2. How do you assess my database’s health?
3. How would you approach a performance problem with a three-tier application?
4. How do you test your backup/recovery procedures?
5. How would you support the upgrade process for multiple applications, with different application rollout cycles, in the same instance?

Questions to ask the interviewer

Reference:http://www.kevinloney.com/free/newdbas/intrview.htm

Questions to ask the interviewer:
Process control issues:
1. What processes do you follow while implementing changes in production?
2. Beside the DBAs and system administrators, who has access to the "Oracle" operating system account?
3. How often is the oracle operating system account password changed?
4. Are the DBAs co-located with the teams they support?
5. How is capacity planning performed?
6. Is there adequate capacity already in place to support the expected growth over the next year?

Future opportunities/organization issues:
1. Is there a formal job definition for the DBA role?
2. Is there a defined technical career path?
3. How is IT aligned with the business areas?
4. How many employees report to more than one manager?
5. How do you determine if a DBA has been successful?
6. How are the application DBAs and production control DBAs organized?

Miscellaneous DB,Tuning,Unix

Reference:http://www.dbapool.com/faqs/

DB Administration

Which script is used to create base data dictionary tables?

Which are the views for finding the database version, options and port installed?

Can one change the INITIAL and NEXT parameter of an object once it is created?

How can one coalesce free space in a tablespace?

Which data dictionary view should one query to get the name of database's datafiles and redo log files?

What is the effect of DB

Name parameter and database name on each other in control file?

How can one determine the size of buffers in the buffer cache of SGA?

How you can check the version of oracle and its components in your database?

Can one rename a database user (schema)?

Can one resize the data files if experiencing problem with the tablespace size?

I would like to increase the size of the archive logs which should hopefully decrease the number of logs switched per minute?

Describe REDO and UNDO in detail?

Which oracle facility can be used for sending mails.

Which oracle facility can be used for scheduling jobs. I am getting error " Error accessing PRODUCT_USER_PROFILE Warning: Product user profile information not loaded!"?

How can I get the largest amount of physical reads by any query?

What is the difference between a database and an instance?

What is the difference between V$ and GV$ , also V$and V_$?

How do you find out the unix process id of a database session?

How to register Listener in the database?

Perf & Tuning

Which tools or utilities should I use to diagnose the poor performance of my database?

How can I calculate SGA size in Oracle 8i?

Which are the most crucial parameters in init.ora?

How can I get the largest amount of logical reads by any query?

What is the optimum value for hit ratio?

How can I get the largest amount of physical reads by any query?

When I should increase DB_BLOCK_BUFFERS?

What are the techniques to increase the reliability and performance?

What is the recommended size of block for an OLTP database.

How can I find Table, Index and Column which are indexed? How to determine redo log size?

Backup/Recovery

What are the different tools/utilities for taking backups?

What is the difference between Logical and Physical Backups?

What is the difference between On-line and off-line backups?

What kind of backup are supported by RMAN?

How I can get database in Archive log mode?

What are the popular backup solutions available in the market.

What I can do if running out of rollback segments while importing?

How to change temporarily change the archive location?

9i New Features

Can I create indexes on index organized tables online?

How to use iSQL*PLUS in Oracle 9i?

Oracle on Unix

How can one examine the size of Oracle Processes?

How can one Monitor paging/swapping on Unix system?

What are the Kernel Parameters associated with the SGA?

What are Semaphores?

How can one set number of process per user?

How can I find the OS Block Size .

How to check background process status in Unix environments?

How to run OEM on linux?

Technical-Oracle,Unix

Reference:http://www.databasejournal.com/features/oracle/article.php/3085171

-James Koopmann

Technical - Oracle
1. Explain the difference between a hot backup and a cold backup and the benefits associated with each
2. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
3. How do you switch from an init.ora file to a spfile?
4. Explain the difference between a data block, an extent and a segment.
5. Give two examples of how you might determine the structure of the table DEPT.
6. Where would you look for errors from the database engine?
7. Compare and contrast TRUNCATE and DELETE for a table.
8. Give the reasoning behind using an index.
9. Give the two types of tables involved in producing a star schema and the type of data they hold.
10. What type of index should you use on a fact table?
11. Give two examples of referential integrity constraints.
12. A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
14. What command would you use to create a backup control file?
15. Give the stages of instance startup to a usable state where normal users may access it.
16. What column differentiates the V$ views to the GV$ views and how?
17. How would you go about generating an EXPLAIN plan?
18. How would you go about increasing the buffer cache hit ratio?
19. Explain an ORA-01555
20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
21. How would you determine the time zone under which a database was operating?
22. Explain the use of setting GLOBAL_NAMES equal to TRUE.
23. What command would you use to encrypt a PL/SQL application?
24. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
25. Explain the use of table functions.
26. Name three advisory statistics you can collect.
27. Where in the Oracle directory tree structure are audit traces placed?
28. Explain materialized views and how they are used.
29. When a user process fails, what background process cleans up after it?
30. What background process refreshes materialized views?
31.How would you determine what sessions are connected and what resources they are waiting for?
32. Describe what redo logs are.
33. How would you force a log switch?
34. Give two methods you could use to determine what DDL changes have been made.
35. What does coalescing a tablespace do?
36. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
37. Name a tablespace automatically created when you create a database.
38. When creating a user, what permissions must you grant to allow them to connect to the database?
39. How do you add a data file to a tablespace?
40. How do you resize a data file?
41. What view would you use to look at the size of a data file?
42. What view would you use to determine free space in a tablespace?
43. How would you determine who has added a row to a table?
44. How can you rebuild an index?
45. Explain what partitioning is and what its benefit is.
46. You have just compiled a PL/SQL package but got errors, how would you view the errors?
47. How can you gather statistics on a table?
48. How can you enable a trace for a session?
49. What is the difference between the SQL*Loader and IMPORT utilities?
50. Name two files used for network connection to a database.

Technical - UNIX
1. How do you list the files in an UNIX directory while also showing hidden files?
2. How do you execute a UNIX command in the background?
3. What UNIX command will control the default file permissions when files are created?
4. Explain the read, write, and execute permissions on a UNIX directory.
5. What is the difference between a soft link and a hard link?
6. Give the command to display space usage on the UNIX file system.
7. Explain iostat, vmstat and netstat.
8. How would you change all occurrences of a value using VI?
9. Give two UNIX kernel parameters that effect an Oracle install
10. Briefly, how do you install Oracle software on UNIX.

Mid-level DBA

1) When you enter 'sqlplus scott/tiger' which background process checks for authentication ? From which table/view it checks for authentication ? If password is wrong from where oracle throws the msg ?
2) If you create a permanent tablespace and allot it to 5 users as temporary tablespace, how many extents will be allocated by oracle for each user ?
3) When redo log contains both roll forward and rollback entries, why you require a separate rollback segment ?
4) When you insert or import 8 GB of data and your rollback segment is of 100 MB size (without autoextend), then what will happen to the transaction? During inserts or import what entries will goto rollback segment ?
5) When deferred rollback segment is used ?
6) Is it possible to change ownership of Process IDs from oracle to the user who have invoked the process (for example all scott user processes are owned by Oracle user when you check in unix ps -ef grep oracle command). Why the PIDs are owned by Oracle instead of the user who has invoked it ?
7) What are the other reasons for ORA-1555 error except rbs size is small ?
8) After starting database, where will the dictionary files will reside (memory or system tablespace datafile). If your answer is memory, then whether all the v$ and x$ and other dictionary files are loaded in memory ? ( I answered that it depeneds upon the size of SGA).
9) Where freelists are stored ?
10) Is 'direct_io' parameter is equal to raw

4 Questions to DBAs

Reference:http://www.dba-oracle.com/art_dba_interview.htm

What is the most challenging aspect of your job?

This question will give you clues about the mindset of the DBA. When asked what is challenging about the DBA job, the DBA’s answer will reveal a great deal about his or her personality and ability to work well with other people. The DBA who talks about the technical challenges of the job and difficulties administering and maintaining the database may not be ideally suited for a position where contact for the development staff is critical. On the other hand, the DBA who speaks openly of the challenges associated with getting the developers fast answers and accurate information might be a better consideration.

How do you perceive the relationship between the DBA and the development staff?

The answer to this question is critical when evaluating a DBA candidate. Many DBAs, by virtue of their high pay and product-specific knowledge, tend to think of developers as underlings; in some cases, DBAs view developers with outright contempt, believing their queries to be naive. On the other hand, DBAs with the proper attitude will respond to this question by talking about the developers as clients to whom they provide data services essential to the application. In some shops, the DBAs may be responsible for code, reviewing SQL queries, or DML statements written by developers; so, a good relationship is vital.

Do you treat your test and production systems differently? If so, why?

The answer to this question will often provide insight into the priorities of the candidate. In many shops, the DBA doesn’t perceive the test databases as being as important as the production database. The appropriate DBA candidate will note that the data integrity of the test databases is crucial to the development staff because it is their “production” environment in many cases.

What experience have you had analyzing new systems?

The answer to this question will reveal a great deal about the breadth of the DBA’s background. Although technical proficiency is an absolute requirement for the DBA, you must remember that the DBA needs to have some tangential knowledge about the functional areas within the business that they are supporting: for example, networking; Java syntax; or business-side concerns, like finance or accounting. DBAs with a strong business background will often be very useful to the initial design and implementation of new business functionality within your organization.

Backup and Recovery

Reference:http://www.dbload.com/oracledba/oracledba_back1.htm
1. Which types of backups you can take in Oracle?
2. A database is running in NOARCHIVELOG mode then which type of backups you can take?
3. Can you take partial backups if the Database is running in NOARCHIVELOG mode?
4. Can you take Online Backups if the the database is running in NOARCHIVELOG mode?
5. How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?
6. You cannot shutdown the database for even some minutes, then in which mode you should run the database?
7. Where should you place Archive logfiles, in the same disk where DB is or another disk?
8. Can you take online backup of a Control file if yes, how?
9. What is a Logical Backup?
10. Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?
11. Why do you take tablespaces in Backup mode?
12. What is the advantage of RMAN utility?
13. How RMAN improves backup time?
14. Can you take Offline backups using RMAN?
15. How do you see information about backups in RMAN?
16. What is a Recovery Catalog?
17. Should you place Recovery Catalog in the Same DB?
18. Can you use RMAN without Recovery catalog?
19. Can you take Image Backups using RMAN?
20. Can you use Backupsets created by RMAN with any other utility?

Unix Related

Reference:http://www.dbload.com/oracledba/oracledba_unix.htm

1. How do you see how many instances are running?

2. How do you automate starting and shutting down of databases in Unix?

3. You have written a script to take backups. How do you make it run automatically every week?

4. What is OERR utility?

5. How do you see Virtual Memory Statistics in Linux?

6. How do you see how much hard disk space is free in Linux?

7. What is SAR?

8. What is SHMMAX?

9. Swap partition must be how much the size of RAM?

10. How do you see how many memory segments are acquired by Oracle Instances?

11. How do you see which segment belongs to which database instances?

12. What is DISM in Solaris?

13. What is VMSTAT?

14. How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?

15. How do you remove Memory segments?

16. What is the difference between Soft Link and Hard Link?

17. What is stored in oratab file?

18. How do you see how many processes are running in Unix?

19. How do you kill a process in Unix?

20. Can you change priority of a Process in Unix?

Technical Interview

1. What is an Oracle Instance?
2. What information is stored in Control File?
3. When you start an Oracle DB which file is accessed first?
4. What is the Job of SMON, PMON processes?
5. What is Instance Recovery?
6. What is written in Redo Log Files?
7. How do you control number of Datafiles one can have in an Oracle database?
8. How many Maximum Datafiles can there be in an Oracle Database?
9. What is a Tablespace?
10. What is the purpose of Redo Log files?
11. Which default Database roles are created when you create a Database?
12. What is a Checkpoint?
13. Which Process reads data from Datafiles?
14. Which Process writes data in Datafiles?
15. Can you make a Datafile auto extendible. If yes, how?
16. What is a Shared Pool?
17. What is kept in the Database Buffer Cache?
18. How many maximum Redo Logfiles one can have in a Database?
19. What is difference between PFile and SPFile?
20. What is PGA_AGGREGRATE_TARGET parameter?