Oracle Database Startup and Shutdown

ADVERTISEMENT

Starting and shutting down an Oracle database properly is a fundamental skill for DBAs. This post covers essential interview questions on the startup and shutdown processes, helping you understand different modes, internal steps, and best practices to manage Oracle databases safely and efficiently.

1. What are the different methods to start up an Oracle database?

  • The most common way is using SQL*Plus with the STARTUP command. It can start the database in three stages:
    • NOMOUNT: Starts the instance and allocates memory but doesn’t mount the database.
    • MOUNT: Reads control files to identify the database but data files remain closed.
    • OPEN: Opens data files and redo logs so users can connect.
  • In Oracle RAC, use SRVCTL commands to start the database across cluster nodes.
  • RMAN can also start the database automatically during recovery.

2. What are the different methods to shut down an Oracle database?

  • Oracle supports four shutdown methods:
    • NORMAL: Waits for all users to disconnect before shutting down.
    • IMMEDIATE: Disconnects users immediately and rolls back active transactions.
    • TRANSACTIONAL: Waits for active transactions to complete, then shuts down.
    • ABORT: Forces an immediate shutdown without cleanup; recovery needed next startup.

3. What happens internally during database startup?

  • Oracle allocates memory areas (SGA, PGA), starts background processes, reads initialization parameters, opens control files (MOUNT), and finally opens data files and redo logs to allow user access.

4. What happens internally during database shutdown?

  • Oracle closes user sessions, flushes all dirty data from memory to disk, stops background processes, releases locks, and frees allocated memory.

5. What is the difference between NOMOUNT, MOUNT, and OPEN states in database startup?

  • NOMOUNT: Starts instance memory and background processes, but doesn’t read control files. Used mainly for creating or restoring control files.
  • MOUNT: Reads control files to locate database files but data files remain closed; used for maintenance tasks like renaming data files or enabling ARCHIVELOG mode.
  • OPEN: Opens all files; database is fully available for normal operations.

6. What are the key initialization parameters used during startup?

  • Important parameters include:
    • DB_NAME: Database identifier.
    • CONTROL_FILES: Paths to control files.
    • MEMORY_TARGET: Total memory allocation for SGA and PGA.
    • UNDO_TABLESPACE: Defines undo tablespace.
    • LOG_ARCHIVE_DEST: Location for archived logs.

7. How does the Oracle instance recover during startup?

  • If the database wasn’t shut down cleanly, Oracle applies redo logs to data files to fix inconsistencies and complete unfinished transactions before opening the database.

8. What is a clean shutdown versus an abnormal shutdown?

  • Clean shutdown ensures all users disconnect, transactions finish or rollback, data is written to disk, and memory is freed properly.
  • Abnormal shutdown happens suddenly (like power failure or SHUTDOWN ABORT), skipping cleanup and requiring recovery at next startup.

9. What happens when you issue a SHUTDOWN ABORT?

  • Oracle immediately stops all processes without waiting for transactions or writing data to disk. This can lead to data inconsistencies that are fixed during recovery when the database starts again.

10. How do you check the database status before starting or shutting it down?

  • Run SELECT status FROM v$instance; in SQL*Plus to check if the instance is started, mounted, or open.
  • Use lsnrctl status to check listener status.
  • On OS level, you can check Oracle processes or services running.

11. What is the role of background processes during startup and shutdown?

  • Background processes manage memory allocation, write data to disk (DBWR), handle recovery (SMON), monitor other processes (PMON), write redo logs (LGWR), and checkpoint progress (CKPT). They keep the database stable during startup and shutdown.

12. How do Oracle startup modes affect availability and recovery?

  • The mode controls how much of the database is available:
    • In NOMOUNT, only instance is ready for maintenance.
    • In MOUNT, database files are identified but not accessible to users.
    • In OPEN, database is fully accessible.
  • Recovery happens before the database opens if the previous shutdown was abnormal.

13. What is the impact of using the “startup force” command?

  • STARTUP FORCE performs an immediate shutdown (like SHUTDOWN ABORT), then starts the database fresh. It’s used when the database is unresponsive or stuck.

14. Can you explain the role of the alert log during startup and shutdown?

  • The alert log is a text file that records all key startup, shutdown events, errors, and messages. It’s the first place DBAs look when troubleshooting startup or shutdown problems.

15. How do you start and stop a database using SRVCTL (Oracle RAC environment)?

  • In RAC, use srvctl start database -d <db_name> to start and srvctl stop database -d <db_name> to stop the database safely across all nodes, ensuring cluster-wide consistency.

ADVERTISEMENT