Below are the senior Oracle DBA interview questions for people who are projecting 3+ years of DBA experience:
1. Explain about password file, what is stored inside password file and its use?
2. What is force=y option while generating password file?
3. What are the contents of control file and which parameter defines the controlfile retention?
4. Difference between checkpoint and SCN number?
5. Differentiate between data file header and data block header? What it contains?
6. Give the command to take controlfile trace backup
7. Can I have redo log groups with different size?
8. Differentiate between redo log groups and redo log members
9. How do you rename redo log file – online or offline? Give the command
10. Which parameter defines the archive log naming format?
11. Can I change the archive log destination while the database is running?
12. I lost redo log file and have no multiplexed copy or archive log. How can I recover the database?
13. What is different about v$datafile and dba_data_files?
14. What are the contents of oracle inventory file and in which format does it exists?
15. You are designing a database for a client. Explain below:
a. How would you recommend the storage of datafiles, control files, redo log files etc?
b. How will you calculate the initial size of the database?
c. What backup strategy you would recommend?
d. On which locations you will set alerts?
16. How many OS blocks does DB block contains?
17. Why data blocks are always 80% used and not 100%?
18. How many types of segments are there in Oracle? How many types of objects are there in Oracle and how are they stored in the segments?
19. How will you calculate the best data block size for a new database and propose it to the client?
20. What is undo retention policy? How do you estimate the undo retention policy?
21. Explain what happens during instance recovery?
22. Difference between Roll-back and roll-forward
23. A user is complaining DB is running slow. What could be the issue?
24. What is a temporary table and how it is different from normal table?
25. What is materialized view and how it is different from view and normal table?
26. How do you query rowID column in a table? What exactly is rowID?
27. How do you give partial access to a user on a table?
28. What are the table partitioning strategies used in your environment?
29. What is global partitioned index and local partitioned index? How does it impact the performance?
30. Do you recommend partitioned tables in Data Ware House or OLTP databases?
31. I want to install Oracle software on 300 servers at a time. How will you do it?
32. There are 50 random email alerts for multiple databases. How will you prioritize the alerts and solve them?
33. Temp tablespace utilization is 100% full. What you will do?
34. My client recommends to put undo and temp on auto extend ON. What you have to say about it?
35. Explain about tablespace utilization process in your environment?
36. Client is asking for sysdba access. What is the command to give access?
37. Ping is working but tnsping is not working. What could be the issue?
38. I am not able to add data file to tablespace. What could be the issue?
39. My database size is 25 TB. How many channels will you allocate in RMAN command?
40. Differentiate between automatic channels and manual channels.
41. What is backup optimization on in RMAN? Do you recommend it to be enabled?
42. I want to configure tape backups. How will you configure tape with RMAN
43. How to take cold backup using rman?
44. What are the ways to improve rman backup / recovery performance in terms of time?
45. I have taken L0 backup on 1st and L1 everyday till 15th. The recovery window is of only 7 days. So how will you recover database as L0 is OBSOLETE backup
46. Difference between expired backups and obsolete backups
47. What is the RMAN command to take controlfile and spfile backup?
48. What is the command to delete all the backup older than 30 days?
49. What is database incarnation? What happens when database goes into new incarnation?
50. I have a reporting database of 2 TB. What backup strategy will you recommend?
51. How do I sync recovery catalog with my target database?
52. Tell me the process of recovery catalog creation with commands.
53. Recovery catalog server is down. How will handle the failed backups for an environment with 1000 databases?
54. When will you recommend cumulative over differential backups?
55. What happens when you type recover database?
56. Why cold backup is known as consistent backup? How can I take consistent backup using RMAN?
57. What is fractured block?
58. What happens when you put DB in Begin backup mode?
59. I lost my entire database. Tell me the steps you will follow to recover the database.
60. What is database health check? How do you perform health check?
61. My controlfile retention is 30 days. I want to recover my database from a backup 45 days old. How will you perform the activity when the backup details are not there in the controlfile?
62. How to do perform PITR recovery?
63. How do you calculate the archivelog backup frequency and schedule in crontab?
64. Why you will not recommend catalog scripting over shell scripting for backups?
65. Explain RMAN restore / recover cloning process with steps?
66. How do you rename a database post cloning?
67. Explain RMAN Duplicate and difference between RMAN Duplicate cloning for new database and cloning for physical standby?
68. I started the cloning and it failed in between. What should I do now?
69. Difference between MRP and LSP
70. Archives are not arriving at the standby. What will you check?
71. There is a GAP of 1000 archives in my standby. How will you resolve it?
72. I want my standby to run 4 hours behind the primary server. How can I achieve it?
73. How to improve the performance of MRP process?
74. The client does not want to spend on active data guard license. What will you recommend?
75. What happens when you convert physical standby to snapshot standby?
76. In which conditions you will recommend Oracle Data Guard to client?
77. Tell me the steps to configure physical standby?
78. How will you verify that the standby is in sync with primary?
79. What is guaranteed restore point? How it is different from restore point?
80. Explain the difference between flashback database and database PITR
81. Explain about how upgrades are done in your environment?
82. What are the pre-upgrade steps you follow?
83. Explain the process of upgrading from 11g to 12c version?
84. What are the post upgrade steps in oracle?
5+ Experience Questions
Typical Interview Questions for people projecting 5+ years of experience:
- Application team query is running slow. What you will do?
- Select statement is taking longer than usual. What could be the issue?
- What is the frequency of index rebuilding in your environment?
- What is invalid object in Oracle
- Differentiate between delete and truncate command
- Explain stats gathering and how it impacts database performance?
- How many DB writer process can you configure in database? Tell me the command for increasing the DB writer process.
- Difference between 8i, 9i, 10g, 11g and 12c databases as per your experience.
- What is the most challenging task for you as a DBA?
- Explain about TKPROF utility and when do you use it
- Explain about cost in SQL and how optimizer defines cost?
- How do you setup data guard in 8i?
- What are the instance level, RMAN level and other changes in 9i and 10g?
- Junior DBA brought down the listener, how do you react?
- Your production database is running in NOARCHIVELOG mode, what could be the issue?
- How do you migrate database from windows to Linux?
- What was your last database design and how did you work on the project?
General Interview Questions
These are the questions with which interview will start
1. Tell about yourself, your background, some experience, and what you have worked on.
2. What do you like doing the most?
3. What do you like the least
4. Was there a particular company that you liked and why?
5. Can you tell me about a specific time that you used your technical skills to solve a problem?
6. What did you learn from that experience?
7. Tell me about a time when you had delivered bad news to someone.
8. What was your approach? And what was the outcome of the situation?
9. Tell me an example when you were given a task to fix things
10. Did you learn anything from that experience?