Oracle DBA Certification Interview Questions & Answers

Oracle Database architecture is divided into two main components: the Instance and the Database. Many candidates confuse these two, but understanding this distinction is critical for certification interviews.

1. Oracle Instance
The instance consists of memory structures and background processes. It is the engine that runs the database.

Memory Components:

• SGA (System Global Area) - Shared memory for all sessions
• PGA (Program Global Area) - Private memory per session

Background Processes:

• DBWR - Writes dirty buffers to disk
• LGWR - Writes redo logs
• SMON - Performs recovery
• PMON - Cleans up failed processes
• CKPT - Updates control file and headers

2. Oracle Database
The database refers to physical files stored on disk:

• Datafiles
• Redo log files
• Control files
• Temp files

Real-world scenario:
If the instance crashes but datafiles remain intact, recovery is possible because redo logs replay committed transactions.

Interview Tip:
If interviewer asks “What happens during startup?” - explain NOMOUNT, MOUNT, OPEN stages.

This is a classic certification question.

Instance = Memory + Background processes.
Database = Physical files on disk.

The instance starts first. It then mounts and opens the database.

If instance shuts down, files still exist on disk. Without instance, database cannot be accessed.

Example:
Multiple instances can access one database in RAC environment.

The control file is one of the most critical files in Oracle.

It contains metadata about:

• Database name
• Datafile locations
• Redo log file locations
• Checkpoint information
• SCN numbers

If control file is lost, database cannot start.

How to check control file location:

SHOW PARAMETER control_files;

Best Practice:
Always multiplex control files across multiple disks.

Redo logs record all changes made to the database. They are essential for recovery.

Every DML operation generates redo entries stored in Redo Log Buffer, then written by LGWR to redo log files.

In case of crash, Oracle uses redo logs to replay committed transactions.

Important Concepts:

• Log switch
• Archive log mode
• Online redo logs

Production Example:
In financial systems, redo logs ensure zero data loss when properly archived.

Archive Log Mode allows Oracle to archive redo logs before overwriting them.

This enables:

• Point-in-time recovery
• Full database recovery
• Data Guard replication

Enable Archive Mode:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Without archive mode, recovery options are limited.

Oracle memory is divided into SGA and PGA.

SGA Components:

• Buffer Cache
• Shared Pool
• Redo Log Buffer
• Large Pool

PGA:
Private memory for session operations like sorting and hashing.

Check memory usage:

SELECT * FROM V$SGAINFO;

Oracle background processes handle database operations automatically.

• DBWR - Writes buffers to disk
• LGWR - Writes redo logs
• SMON - Crash recovery
• PMON - Cleans up dead processes
• ARCn - Archives redo logs

In RAC, additional processes like LMS appear.

A checkpoint is the process of synchronizing database buffers with datafiles.

CKPT updates control file and datafile headers with latest SCN.

Frequent checkpoints reduce crash recovery time.

Datafiles store actual table data physically.

Tablespaces are logical storage units mapped to datafiles.

One tablespace can have multiple datafiles.

Create tablespace:

CREATE TABLESPACE users
DATAFILE 'users01.dbf'
SIZE 500M;

SCN (System Change Number) is a logical timestamp used by Oracle for consistency and recovery.

Every committed transaction gets an SCN.

Used in:

• Flashback
• Recovery
• Data Guard

Check current SCN:

SELECT CURRENT_SCN FROM V$DATABASE;

User management is one of the primary responsibilities of a DBA.

Creating a user:

CREATE USER john IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA 100M ON users;

This command defines authentication, default tablespace, temporary tablespace, and storage quota.

Grant privileges:

GRANT CONNECT, RESOURCE TO john;

Lock/Unlock user:

ALTER USER john ACCOUNT LOCK;
ALTER USER john ACCOUNT UNLOCK;

Real-world DBA scenario:
In enterprise environments, we rarely grant system privileges directly. We use roles for better control and auditing.

Interview Tip:
Mention principle of least privilege.

This question checks your understanding of Oracle security model.

System Privileges:
Allow users to perform database-wide actions.

• CREATE TABLE
• CREATE USER
• DROP TABLE

Object Privileges:
Allow actions on specific objects.

• SELECT on table
• INSERT on table
• UPDATE on view

Example:

GRANT SELECT ON hr.employees TO john;

Certification Insight:
OCP often tests difference between WITH GRANT OPTION and WITH ADMIN OPTION.

Roles are collections of privileges grouped together for easier management.

Instead of granting privileges individually, DBAs create roles and assign them to users.

Create Role:

CREATE ROLE app_role;
GRANT SELECT, INSERT ON hr.employees TO app_role;
GRANT app_role TO john;

Why use roles?

• Simplifies administration
• Improves security management
• Easier revocation

Profiles control password policies and resource limits for users.

Example:

CREATE PROFILE secure_profile LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_LOCK_TIME 1;

Assign Profile:

ALTER USER john PROFILE secure_profile;

Real-world usage:
Used to enforce enterprise password rotation and security compliance.

Oracle supports advanced password management using profiles and password verify functions.

Key parameters:

• PASSWORD_LIFE_TIME
• PASSWORD_REUSE_TIME
• FAILED_LOGIN_ATTEMPTS

Enable password complexity:
Oracle provides default VERIFY_FUNCTION.

Enterprise Example:
Financial institutions mandate password expiry every 30 days.

Auditing tracks database activities for security and compliance.

Oracle supports:

• Statement auditing
• Privilege auditing
• Object auditing

Enable auditing:

AUDIT SELECT TABLE BY john;

Audit records stored in DBA_AUDIT_TRAIL.

Certification Tip:
Know difference between traditional auditing and Unified Auditing.

Unified Auditing consolidates all auditing into a single framework.

It replaces traditional AUDIT commands in newer versions.

Check unified auditing:

SELECT * FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

Enterprise Benefit:
Improved performance and centralized security logs.

Revoking privileges removes previously granted access.

Example:

REVOKE SELECT ON hr.employees FROM john;

If privilege granted WITH GRANT OPTION, revocation cascades.

This is a very common certification question.

WITH GRANT OPTION:
Used for object privileges. Allows user to grant object privilege to others.

WITH ADMIN OPTION:
Used for system privileges and roles. Allows granting system privileges to others.

Monitoring sessions helps DBAs identify performance or blocking issues.

Check active sessions:

SELECT SID, SERIAL#, USERNAME, STATUS
FROM V$SESSION;

Kill session:

ALTER SYSTEM KILL SESSION 'sid,serial#';

Production Example:
Long-running sessions can block business transactions.

RMAN (Recovery Manager) is Oracle's built-in utility for backup and recovery operations.

Why RMAN?

• Performs physical backups
• Tracks backup metadata
• Supports incremental backups
• Integrates with Media Managers

Basic RMAN Backup:

RMAN> BACKUP DATABASE;

Enterprise Insight:
In production, backups are usually automated via shell scripts and scheduled through cron or OEM.

Oracle supports multiple backup types:

1. Full Backup - Complete database backup.

2. Incremental Backup - Only changed blocks since last backup.

3. Differential Incremental - Changes since last level 1 backup.

4. Cumulative Incremental - Changes since last level 0 backup.

Example:

BACKUP INCREMENTAL LEVEL 0 DATABASE;
BACKUP INCREMENTAL LEVEL 1 DATABASE;

Certification Tip:
Understand difference between Level 0 and Level 1 backups.

Archivelog mode allows redo logs to be archived for recovery purposes.

Check Mode:

ARCHIVE LOG LIST;

Enable Archivelog:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

Why Important?
Required for point-in-time recovery.

Flashback allows viewing or restoring database objects to a previous state.

Types:

• Flashback Query
• Flashback Table
• Flashback Drop
• Flashback Database

Example:

SELECT * FROM employees
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);

Production Benefit:
Reduces need for full restore in accidental delete cases.

Data Pump (expdp/impdp) is used for logical backup and migration.

Export Example:

expdp system/password DIRECTORY=dp_dir DUMPFILE=backup.dmp LOGFILE=exp.log FULL=Y;

Import Example:

impdp system/password DIRECTORY=dp_dir DUMPFILE=backup.dmp LOGFILE=imp.log;

Use Case:
Schema migration, database upgrades.

Physical Backup:

• Uses RMAN
• Copies datafiles, control files, redo logs
• Used for full recovery

Logical Backup:

• Uses Data Pump
• Exports objects (tables, schema)
• Used for migration

Certification Insight:
RMAN is preferred for production backup strategy.

Point-In-Time Recovery (PITR) restores database to a specific time before failure.

RMAN Example:

RUN {
SET UNTIL TIME "TO_DATE('2026-02-10 10:00:00', 'YYYY-MM-DD HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
}

Enterprise Scenario:
Used after accidental mass data deletion.

Control file stores metadata about database structure.

Contains:

• Datafile locations
• Redo log information
• Checkpoint details

Backup Control File:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Loss of control file prevents database startup.

Redo logs record all database changes.

Used for crash recovery.

Key Concepts:

• Online Redo Logs
• Archived Redo Logs
• Redo Log Groups

Check Logs:

SELECT * FROM V$LOG;

Disaster Recovery ensures business continuity during catastrophic failure.

Common strategies:

• Data Guard
• Standby Database
• Regular RMAN backups
• Offsite storage

Enterprise Example:
Primary DB in Mumbai, Standby DB in Bangalore data center.

AWR (Automatic Workload Repository) is a performance diagnostic tool that captures database workload statistics.

What it collects:

• CPU usage
• Wait events
• SQL execution stats
• Memory usage

Generate AWR Report:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Real-world usage:
DBAs analyze AWR reports to identify top SQL consuming resources.

ADDM (Automatic Database Diagnostic Monitor) analyzes AWR data and provides tuning recommendations.

It helps identify:

• CPU bottlenecks
• I/O contention
• Memory shortages
• High-load SQL queries

Enterprise Note:
ADDM is automatically executed after each AWR snapshot.

To identify high resource-consuming SQL:

Query V$SQL:

SELECT sql_text, executions, buffer_gets
FROM v$sql
ORDER BY buffer_gets DESC;

In production:
Check AWR report “Top SQL by Elapsed Time”.

Wait events indicate why a session is waiting.

Examples:

• db file sequential read
• log file sync
• buffer busy waits

Check Wait Events:

SELECT event, total_waits
FROM v$system_event;

DBA Insight:
High wait times signal performance bottlenecks.

Indexes improve query performance.

Main Types:

• B-Tree Index
• Bitmap Index
• Function-Based Index
• Composite Index

Create Index:

CREATE INDEX emp_idx ON employees(emp_id);

Certification Tip:
Bitmap indexes are ideal for low-cardinality columns.

Explain Plan shows how Oracle executes a query.

Example:

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE emp_id = 100;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Use Case:
Helps verify whether index is being used.

SGA (System Global Area) is shared memory for database instance.

Components:

• Shared Pool
• Buffer Cache
• Redo Log Buffer

Check SGA:

SHOW PARAMETER sga;

PGA (Program Global Area) is private memory allocated per process.

Used for:

• Sorting
• Hash joins
• Session variables

Check PGA:

SHOW PARAMETER pga;

Memory tuning involves optimizing SGA and PGA allocation.

Automatic Memory Management:

SHOW PARAMETER memory_target;

Manual Tuning:

• Adjust shared_pool_size
• Adjust db_cache_size
• Adjust pga_aggregate_target

Production Insight:
Monitor memory advisory views for recommendations.

Cursor sharing determines how SQL statements reuse execution plans.

Parameter:

SHOW PARAMETER cursor_sharing;

Values:

• EXACT
• FORCE

Best Practice:
Use bind variables to avoid hard parsing overhead.

Oracle Data Guard is a disaster recovery solution that maintains standby databases synchronized with the primary database.

Key Benefits:

• High Availability
• Disaster Recovery
• Data Protection

Types of Standby:

• Physical Standby
• Logical Standby
• Snapshot Standby

Real-world use:
Used in production systems to ensure minimal downtime during failures.

Physical Standby:
Exact copy of primary database using redo apply.

Logical Standby:
Uses SQL apply and allows read/write access for certain operations.

Key Difference:

• Physical = block-level replication
• Logical = SQL-level replication

Switchover:
Planned role reversal between primary and standby.

Failover:
Unplanned switch due to primary database failure.

DBA Note:
Switchover = zero data loss.
Failover may cause minimal data loss depending on configuration.

Oracle RAC (Real Application Clusters) allows multiple instances to run on different servers accessing the same database.

Benefits:

• High availability
• Load balancing
• Scalability

Real-world Example:
Used in enterprise banking and telecom systems for continuous uptime.

Cache Fusion allows data blocks to be shared across RAC instances without writing to disk.

How it works:

• Blocks transferred over interconnect
• Reduces disk I/O
• Improves performance

ASM (Automatic Storage Management) manages database storage efficiently.

Advantages:

• Striping
• Mirroring
• Simplified storage management

ASM Disk Groups:
DATA, FRA, etc.

Oracle uses OPatch utility for patching.

Steps:

opatch lsinventory
opatch apply

Best Practice:
Always backup before patching and test in staging environment.

Database upgrade can be performed using:

• DBUA (GUI tool)
• Manual upgrade using scripts

Pre-upgrade checks:

• Check compatibility
• Backup database
• Run pre-upgrade utility

Monitoring tools include:

• AWR Reports
• ADDM
• Enterprise Manager
• V$ Views

Common Monitoring Query:

SELECT * FROM v$session;

Step-by-step approach:

1. Check CPU and memory usage
2. Review AWR report
3. Identify Top SQL
4. Check wait events
5. Verify indexing

Golden Rule:
Never tune blindly - always analyze metrics first.

Point-in-Time Recovery (PITR) allows restoring the database to a specific time before failure.

When used:

• Accidental data deletion
• Logical corruption

RMAN Example:

RUN {
  SET UNTIL TIME "TO_DATE('2026-02-10 10:30:00','YYYY-MM-DD HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
}

Important: Database must be in ARCHIVELOG mode.

Flashback Database allows you to rewind the database to a previous state without restoring backups.

Advantages:

• Faster than restore
• No need to restore full backup

Enable Flashback:

ALTER DATABASE FLASHBACK ON;

Recovery Example:

FLASHBACK DATABASE TO TIMESTAMP ...

ORA-01555 occurs when undo data required for consistent read is overwritten.

Common Causes:

• Long running queries
• Small UNDO tablespace

Solutions:

• Increase UNDO tablespace
• Increase UNDO_RETENTION
• Tune long-running queries

Node eviction in RAC usually happens due to:

• Interconnect failure
• CPU starvation
• Memory pressure

Steps:

1. Check CRS logs
2. Verify network interconnect
3. Check OS metrics

Network security can be implemented using:

• SQL*Net encryption
• Firewall restrictions
• IP whitelisting

Enable encryption:

TDE encrypts data at rest inside database files.

Types:

• Column-level encryption
• Tablespace encryption

Benefit: Protects data even if storage is stolen.

Blocking sessions cause performance issues.

Query:

SELECT blocking_session, sid FROM v$session WHERE blocking_session IS NOT NULL;

Resolution:

• Identify blocking SQL
• Kill session if required

AWR report provides performance statistics.

Focus Areas:

• Top Wait Events
• Top SQL
• Load Profile
• Instance Efficiency

Tip: Always compare with baseline.

Database corruption may be physical or logical.

Steps:

1. Identify corrupted block
2. Use RMAN block recovery
3. Restore datafile if needed

RMAN Example:

RECOVER DATAFILE 5 BLOCK 10;

Capacity planning ensures future scalability.

Analyze:

• Database growth rate
• CPU utilization trends
• I/O throughput
• User load patterns

Best Practice: Monitor monthly growth and forecast yearly requirements.

Oracle Data Guard provides disaster recovery and high availability by maintaining synchronized standby databases.

Components:

• Primary Database
• Standby Database (Physical / Logical)
• Redo Transport Services
• Apply Services

Process Flow:

1. Primary generates redo
2. Redo shipped via LNS
3. Standby receives via RFS
4. Redo applied using MRP

Protection Modes:

• Maximum Performance
• Maximum Availability
• Maximum Protection

In interviews, always explain synchronous vs asynchronous redo transport.

Active Data Guard allows read-only queries on standby while redo is being applied.

Benefit:

• Offloads reporting workload
• Improves primary performance

Requires separate license.

Redo lag can occur due to network latency or I/O bottlenecks.

Steps:

1. Check v$dataguard_stats
2. Verify network throughput
3. Monitor archive generation rate

High redo generation often requires bandwidth upgrade.

ASM (Automatic Storage Management) manages Oracle database files efficiently.

Main Components:

• ASM Instance
• Disk Groups
• Failure Groups

Advantages:

• Striping
• Mirroring
• Automatic load balancing

Rebalancing happens automatically when disks are added or removed.

Manual Trigger:

ALTER DISKGROUP DATA REBALANCE POWER 8;

Higher POWER value increases rebalance speed but consumes CPU.

Oracle Multitenant architecture consists of:

• Container Database (CDB)
• Pluggable Databases (PDBs)

Benefits:

• Easy consolidation
• Simplified patching
• Resource isolation

Interview Tip: Explain how to unplug and plug PDB.

PDB cloning allows quick database provisioning.

Command:

CREATE PLUGGABLE DATABASE pdb_clone FROM pdb1;

Used in testing and DevOps pipelines.

SQL Plan Baseline ensures consistent execution plans.

Use Case:

• Prevent performance regression
• Stabilize critical queries

Stored in SQL Management Base.

High CPU usage investigation steps:

1. Check OS top/htop
2. Identify top sessions from v$session
3. Check top SQL from AWR
4. Look for inefficient execution plans

Most common cause: full table scans on large tables.

A complete DR strategy includes:

1. Backup Strategy

• Full weekly backup
• Daily incremental
• Archived redo logs

2. Data Guard for HA

• Physical standby in different data center

3. Flashback for Logical Failures

4. Regular DR Drills

5. RPO & RTO Definition

Enterprise interviews expect discussion on business impact analysis and SLA alignment.