Oracle DBA interview questions with Answers -Basic Level
1. What is Oracle Database?
Answer:
Oracle Database is a relational database management system (RDBMS) developed by Oracle Corporation. It stores data in tables and provides features like security, backup & recovery, high availability, and performance tuning.
2. What is the difference between an Instance and a Database?
Answer:
|
Database |
Instance |
|
Collection of physical files |
Collection of memory structures and background processes |
|
Stored on disk |
Runs in memory |
|
Includes data files, control files, redo log files |
Includes SGA and background processes like DBWn, LGWR, SMON, PMON |
Simple definition:
- Database = Files
- Instance = Memory + Processes
3. What are the main Oracle background processes?
Answer:
- DBWn (Database Writer) – Writes dirty buffers to data files.
- LGWR (Log Writer) – Writes redo entries to redo log files.
- CKPT (Checkpoint) – Updates datafile headers and control files.
- SMON (System Monitor) – Performs instance recovery.
- PMON (Process Monitor) – Cleans up failed user processes.
- ARCn (Archiver) – Archives redo logs in ARCHIVELOG mode.
4. What is the SGA?
Answer:
SGA (System Global Area) is shared memory used by the Oracle instance.
Major components:
- Database Buffer Cache
- Shared Pool
- Redo Log Buffer
- Large Pool
- Java Pool
- Streams Pool
5. What is the PGA?
Answer:
PGA (Program Global Area) is private memory allocated to each server process.
It stores:
- Sort area
- Session information
- Stack space
- Hash area
Difference:
- SGA = Shared memory
- PGA = Private memory
6. What are Oracle Datafiles?
Answer:
Datafiles are physical files that store actual database data such as:
- Tables
- Indexes
- Undo data
- Other database objects
Each tablespace consists of one or more datafiles.
7. What is a Tablespace?
Answer:
A tablespace is a logical storage unit in Oracle that contains one or more datafiles.
Common tablespaces:
- SYSTEM
- SYSAUX
- USERS
- UNDOTBS1
- TEMP
8. What is the difference between ARCHIVELOG and NOARCHIVELOG mode?
Answer:
|
ARCHIVELOG |
NOARCHIVELOG |
|
Redo logs are archived |
Redo logs are overwritten |
|
Supports point-in-time recovery |
Cannot recover after media failure |
|
Suitable for production |
Suitable for test databases |
9. What is RMAN?
Answer:
RMAN (Recovery Manager) is Oracle's backup and recovery utility.
It is used to:
- Take full backups
- Incremental backups
- Restore databases
- Recover databases
- Validate backups
10. What is a Control File?
Answer:
The control file stores metadata about the database, including:
- Database name
- Datafile information
- Redo log information
- Checkpoint information
- Backup history
Without the control file, the database cannot be mounted.
Bonus SQL Question
How do you check the Oracle database version?
SELECT * FROM v$version;
or
SELECT banner FROM v$version;
Bonus DBA Commands
Check database status
SELECT status FROM v$instance;
Check database name
SELECT name FROM v$database;
Check archive log mode
ARCHIVE LOG LIST;
Check tablespaces
SELECT tablespace_name FROM dba_tablespaces;
Check datafiles
SELECT file_name FROM dba_data_files;