DBA Interview Questions

Oracle Database Administration (DBA):
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?
21. Large Pool is used for what?
22. What is PCT Increase setting?
23. What is PCTFREE and PCTUSED Setting?
24. What is Row Migration and Row Chaining?
25. What is 01555 - Snapshot Too Old error and how do you avoid it?
26. What is a Locally Managed Tablespace?
27. Can you audit SELECT statements?
28. What does DBMS_FGA package do?
29. What is Cost Based Optimization?
30. How often you should collect statistics for a table?
31. How do you collect statistics for a table, schema and Database?
32. Can you make collection of Statistics for tables automatic?
33. On which columns you should create Indexes?
34. What type of Indexes are available in Oracle?
35. What is B-Tree Index?
36. A table is having few rows, should you create indexes on this table?
37. A Column is having many repeated values which type of index you should create on this column, if you have to?
38. When should you rebuilt indexes?
39. Can you built indexes online?
40. Can you see Execution Plan of a statement.
41. A table is created with the following setting
storage (initial 200k
next 200k
minextents 2
maxextents 100
pctincrease 40)
What will be size of 4th extent?
42. What is DB Buffer Cache Advisor?
43. What is STATSPACK tool?
44. Can you change SHARED_POOL_SIZE online?
45. Can you Redefine a table Online?
46. Can you assign Priority to users?
47. You want users to change their passwords every 2 months. How do you enforce this?
48. How do you delete duplicate rows in a table?
49. What is Automatic Management of Segment Space setting?
50. What is the difference between DELETE and TRUNCATE statements?
51. What is COMPRESS and CONSISTENT setting in EXPORT utility?
52. What is the difference between Direct Path and Convention Path loading?
53. Can you disable and enable Primary key?
54. What is an Index Organized Table?
55. What is a Global Index and Local Index?
56. What is the difference between Range Partitioning and Hash Partitioning?
57. What is difference between Multithreaded/Shared Server and Dedicated Server?
58. Can you import objects from Oracle ver. 7.3 to 9i?
59. How do you move tables from one tablespace to another tablespace?
60. How do see how much space is used and free in a tablespace?
61. What are the steps for manual database upgrade?
62: How to clone a database using RMAN?
63: What are the manual steps to clone a database?
64: How many ASM instances are required to operate multiple databases?
65: What is the location of CRS Logs in a 10G RAC and what are they?
66: What are the mandatory processes in 9i and 10G?
67: What are the mandatory tablespaces required in 9i and 10G?
68: What is RAC?
69: What is Cache Fusion?
70: What components in RAC must reside in shared storage?
71: What is an interconnect network?
72: What is the use of cluster interconnect?
73: How do users connect to database in an Oracle RAC environment?
74: What is the use of a service in Oracle RAC environemnt?
75: Which enable the load balancing of applications in RAC?
76: What is a virtual IP address or VIP?
77: What is the use of VIP?
78: How do we verify that RAC instances are running?
79: What is FAN?
80: What is rolling upgrade?
81: Can rolling upgrade be used to upgrade from 10g to 11g database?
82: What are the initialization parameters that must have same value for every instance in an Oracle RAC database?
83: What are the components of Oracle clusterware?
84: What are the performance views in an Oracle RAC environment?
85: What are the types of connection load-balancing?
86: What is the usage of srvctl?
87: Can Oracle's Data Guard be used on Standard Edition, and if so how?
88: How can you test that the standby database is in sync?
89: What is the difference between Active Dataguard, and the Logical Standby implementation of 10g dataguard?
90: What is a Dataguard?
91: What are the uses of Oracle Data Guard?
92: What is Redo Transport Services?
93: What is apply services?
94: What is difference between physical and logical standby databases?
95: What is Data Guard Broker?
96: What are the Data guard Protection modes and summarize each?

Backup & Recovery Questions for an Oracle DBA:
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?
21. Where RMAN keeps information of backups if you are using RMAN without Catalog?
22. You have taken a manual backup of a datafile using o/s. How RMAN will know about it?
23. You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?
24. Which is more efficient Incremental Backups using RMAN or Incremental Export?
25. Can you start and shutdown DB using RMAN?
26. How do you recover from the loss of datafile if the DB is running in NOARCHIVELOG mode?
27. You loss one datafile and it does not contain important objects. The important objects are there in other datafiles which are intact. How do you proceed in this situation?
28. You lost some datafiles and you don't have any full backup and the database was running in NOARCHIVELOG mode. What you can do now?
29. How do you recover from the loss of datafile if the DB is running in ARCHIVELOG mode?
30. You loss one datafile and DB is running in ARCHIVELOG mode. You have full database backup of 1 week old and partial backup of this datafile which is just 1 day old. From which backup should you restore this file?
31. You loss controlfile how do you recover from this?
32. The current logfile gets damaged. What you can do now?
33. What is a Complete Recovery?
34. What is Cancel Based, Time based and Change Based Recovery?
35. Some user has accidentally dropped one table and you realize this after two days. Can you recover this table if the DB is running in ARCHIVELOG mode?
36. Do you have to restore Datafiles manually from backups if you are doing recovery using RMAN?
37. A database is running in ARCHIVELOG mode since last one month. A datafile is added to the database last week. Many objects are created in this datafile. After one week this datafile gets damaged before you can take any backup. Now can you recover this datafile when you don't have any backups?
38. How do you recover from the loss of a controlfile if you have backup of controlfile?
39. Only some blocks are damaged in a datafile. Can you just recover these blocks if you are using RMAN?
40. Some datafiles were there on a secondary disk and that disk has become damaged and it will take some days to get a new disk. How will you recover from this situation?
41. Have you faced any emergency situation. Tell us how you resolved it?
42. At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the parameters set to previous values.

Unix related Oracle DBA Interview Questions:
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. What is DISM in Solaris?
11. How do you see how many memory segments are acquired by Oracle Instances?
12. How do you see which segment belongs to which database instances?
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?