Oracle DBA Architecture Mock Interview Questions set 1

ORACLE INSTANCE – BASICS

  1. What is an Oracle Instance?
  2. What are the main components of an Oracle Instance?
  3. How is an Oracle Instance different from an Oracle Database?
  4. Can one Oracle Instance access multiple databases?
  5. Can multiple instances access a single database? Explain with an example.
  6. What happens to the instance when the database is closed?
  7. Is an instance required to be running to access database files?
  8. What is the relationship between background processes and memory structures?
  9. How do you identify whether an instance is running?
  10. Which views provide instance information?

2️⃣ INSTANCE STARTUP PHASES

Conceptual Questions

  1. What are the different phases of Oracle instance startup?
  2. What happens during STARTUP NOMOUNT?
  3. What files are required in NOMOUNT stage?
  4. What happens during STARTUP MOUNT?
  5. Which control file operations occur during mount phase?
  6. What happens during STARTUP OPEN?
  7. What is the difference between MOUNT and OPEN database?
  8. Can users connect during NOMOUNT stage?
  9. Which background processes start at each phase?
  10. What internal checks occur during database open?

Practical Questions

  1. How do you start an instance without opening the database?
  2. How do you start the database in restricted mode?
  3. How do you open a database in READ ONLY mode?
  4. What SQL commands are used for each startup phase?
  5. What happens if control files are missing during startup?

3️⃣ INSTANCE SHUTDOWN MODES

Conceptual

  1. What are the different shutdown modes in Oracle?
  2. Difference between SHUTDOWN NORMAL and IMMEDIATE?
  3. Difference between IMMEDIATE and TRANSACTIONAL?
  4. Why is SHUTDOWN ABORT considered unsafe?
  5. What happens internally during SHUTDOWN IMMEDIATE?
  6. Which shutdown mode performs instance recovery on next startup?
  7. Which shutdown mode waits for user sessions?

Scenario-Based

  1. Database is hanging and not responding—what shutdown method will you use?
  2. Users are running long transactions; how will you safely shutdown?
  3. DBA used SHUTDOWN ABORT accidentally—what should be done next?
  4. What happens if SHUTDOWN IMMEDIATE hangs?
  5. How do you identify active sessions blocking shutdown?

4️⃣ PARAMETER FILES (SPFILE & PFILE)

Basics

  1. What is a parameter file?
  2. Difference between PFILE and SPFILE?
  3. Why is SPFILE preferred over PFILE?
  4. Default location of SPFILE in Oracle 19c?
  5. Can Oracle start without a parameter file?
  6. What happens if SPFILE is corrupted?

Advanced

  1. How do you create a PFILE from SPFILE?
  2. How do you create an SPFILE from PFILE?
  3. Can you edit SPFILE manually?
  4. What is the significance of SCOPE parameter?
  5. What is the difference between SCOPE=MEMORY, SPFILE, BOTH?
  6. Can parameter changes be made dynamically?
  7. How do you identify whether instance is using SPFILE or PFILE?

5️⃣ IMPORTANT INITIALIZATION PARAMETERS

Memory & Performance

  1. What is SGA_TARGET?
  2. Difference between SGA_TARGET and SGA_MAX_SIZE?
  3. What is PGA_AGGREGATE_TARGET?
  4. Difference between ASMM and AMM?
  5. What parameter enables AMM?
  6. What happens if MEMORY_TARGET is set?

Database & Instance

  1. What is DB_NAME?
  2. What is DB_UNIQUE_NAME?
  3. What is CONTROL_FILES parameter?
  4. What is PROCESSES parameter?
  5. What is OPEN_CURSORS?
  6. What happens if PROCESSES limit is reached?
  7. What is UNDO_MANAGEMENT?
  8. What is UNDO_TABLESPACE?
  9. What is COMPATIBLE parameter?

6️⃣ ALERT LOG AND TRACE FILES

Basics

  1. What is an alert log file?
  2. What information does the alert log contain?
  3. Default location of alert log in Oracle 19c?
  4. What is ADR (Automatic Diagnostic Repository)?
  5. Difference between trace files and alert log?

Advanced / Troubleshooting

  1. When is a trace file generated?
  2. How do you enable SQL trace for a session?
  3. How do you identify background process trace files?
  4. What is the naming convention of trace files?
  5. How do you read alert log in real time?
  6. What files should be checked when database fails to start?
  7. What does ORA-600 error indicate?
  8. What does ORA-7445 error indicate?

7️⃣ INSTANCE SGA COMPONENTS

Basics

  1. What is SGA?
  2. What are the main components of SGA?
  3. What is Database Buffer Cache?
  4. What is Shared Pool?
  5. What is Redo Log Buffer?
  6. What is Large Pool?
  7. What is Java Pool?
  8. What is Streams Pool?

Advanced

  1. What happens when Shared Pool is exhausted?
  2. Difference between Library Cache and Data Dictionary Cache?
  3. What causes “library cache lock” waits?
  4. How do you monitor SGA usage?
  5. What dynamic views show SGA information?
  6. What happens if redo log buffer is too small?

8️⃣ ORACLE BASE & ORACLE HOME

Basics

  1. What is ORACLE_BASE?
  2. What is ORACLE_HOME?
  3. Difference between ORACLE_BASE and ORACLE_HOME?
  4. Why is ORACLE_BASE important?
  5. Can multiple ORACLE_HOMEs exist under one ORACLE_BASE?
  6. Default Oracle Base path in Linux?

Practical

  1. How do you identify ORACLE_HOME in a running instance?
  2. What happens if ORACLE_HOME is deleted accidentally?
  3. Can two databases share same ORACLE_HOME?

9️⃣ FILE NAMING CONVENTIONS & DEFAULT LOCATIONS

  1. Default location of datafiles in Oracle 19c?
  2. Default location of control files?
  3. Default location of redo log files?
  4. Naming convention of datafiles?
  5. Naming convention of control files?
  6. Naming convention of redo logs?
  7. Default location of password file?
  8. Default location of SPFILE?
  9. What is the purpose of OMF (Oracle Managed Files)?
  10. Advantages of using OMF?

🔟 TROUBLESHOOTING & SCENARIO-BASED QUESTIONS

  1. Database fails to start with ORA-01078 — how do you troubleshoot?
  2. Instance started but database does not open—possible reasons?
  3. Control file is missing—what steps will you follow?
  4. Parameter file is corrupted—how do you recover?
  5. Alert log shows “checkpoint not complete”—what does it mean?
  6. Database crashes frequently—what files will you analyze?
  7. Instance is consuming high memory—how will you troubleshoot?
  8. Users report slow logins—what SGA component might be the issue?
  9. Database hangs during shutdown—what diagnostic steps will you take?
  10. After reboot, database does not auto-start—why?
  11. ORA-04031 error occurs—what is the root cause and solution?
  12. Instance recovery takes too long—how will you reduce recovery time?
  13. Database opens but users cannot connect—what could be wrong?
  14. PROCESSES limit reached—how do you fix without restart?

1️⃣1️⃣ ADVANCED REAL-TIME SCENARIOS

  1. You changed a parameter dynamically, but it reverted after restart—why?
  2. Database started in NOMOUNT but control file is missing—next steps?
  3. Instance crashes due to power failure—what happens during next startup?
  4. You want to change DB_NAME—what steps are required?
  5. Alert log is growing very fast—how do you manage it?
  6. Instance starts but background processes are failing—how do you debug?
  7. DBA changed memory parameters incorrectly—database won’t start—what now?
Scroll to Top