Oracle DBA 2-Week Detailed Study Plan with Answers
Day 1: Oracle Architecture Basics
Topics:
- Oracle instance: SGA, PGA, background processes (DBWR, LGWR, SMON, PMON)
- Database vs Instance
- Components of SGA: Database Buffer Cache, Shared Pool, Redo Log Buffer
Key Points & Sample Answer:
Q: What is an Oracle instance?
A: An Oracle instance is the combination of memory structures and background processes
that manage the database. It includes the SGA (shared memory) and processes like DBWR
(writes data to disk), LGWR (writes redo logs), SMON (system monitor), and PMON
(process monitor).
Practice:
Draw the architecture diagram and explain each component aloud.
Day 2: Oracle Database Files & Storage
Topics:
- Datafiles, control files, redo log files
- Tablespaces and segments
- Types of tablespaces (SYSTEM, SYSAUX, UNDO, TEMP)
Key Points & Sample Answer:
Q: What is the difference between a datafile and a control file?
A: Datafiles store the actual user and system data. Control files store metadata about the
database structure, such as database name, tablespaces, and checkpoint information.
Practice:
Locate these files on your test database server.
Day 3: Oracle Installation and Environment Setup
Topics:
- Oracle XE or full version installation steps
- Setting environment variables: ORACLE_HOME, ORACLE_SID, PATH
- Understanding Oracle Base and Oracle Home directories
Key Points & Sample Answer:
Q: What is ORACLE_HOME?
A: ORACLE_HOME is the directory where Oracle software is installed. It contains binaries,
libraries, and configuration files.
Practice:
Install Oracle XE on your machine or use a VM, set environment variables, start and stop the
database.
Day 4: User Management and Security
Topics:
- Creating users, granting and revoking privileges
- Roles vs privileges
- System privileges vs object privileges
Key Points & Sample Answer:
Q: How do you create a user and assign roles?
A:
CREATE USER test_user IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO test_user;
Practice:
Create users in your test database and grant different privileges.
Day 5: Backup & Recovery Concepts
Topics:
- Cold backup vs hot backup
- Archivelog mode and its importance
- RMAN basics and architecture
Key Points & Sample Answer:
Q: What is archivelog mode and why is it important?
A: Archivelog mode allows Oracle to archive redo logs before they are overwritten, enabling
recovery of the database to any point in time. It’s required for hot backups and point-in-
time recovery.
Practice:
Check if your test DB is in archivelog mode:
archive log list;
Day 6: Hands-on RMAN Backup & Recovery
Topics:
- RMAN commands to backup and restore
- Incremental backups
- Deleting obsolete backups
Key Points & Sample Answer:
Q: How do you take a full database backup using RMAN?
A:
rman target /
RMAN> backup database plus archivelog;
Practice:
Run RMAN backup on your test database and restore a file.
Day 7: SQL Basics & Explain Plan
Topics:
- Basic SQL queries, joins, subqueries
- Using EXPLAIN PLAN to analyze queries
- Indexes and their impact on query performance
Key Points & Sample Answer:
Q: How do you view the execution plan for a query?
A:
EXPLAIN PLAN FOR SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM table(DBMS_XPLAN.DISPLAY());
Practice:
Write queries and check their execution plans.
Day 8: Performance Tuning Concepts
Topics:
- AWR (Automatic Workload Repository) and ASH (Active Session History)
- Wait events and bottlenecks
- Optimizing SQL queries
Key Points & Sample Answer:
Q: What is an AWR report?
A: AWR collects performance statistics for the database to help identify performance issues.
Practice:
Generate an AWR report on your database and analyze it.
Day 9: Oracle Patching & Upgrading Basics
Topics:
- Using OPatch utility for patching
- Pre-upgrade and post-upgrade tasks
- Rolling upgrades vs downtime upgrades
Key Points & Sample Answer:
Q: How do you apply a patch using OPatch?
A:
$ORACLE_HOME/OPatch/opatch apply
Practice:
Read Oracle patch documentation and explore patch sets.
Day 10: Troubleshooting Common Issues
Topics:
- Reading alert logs and trace files
- Database startup/shutdown commands
- Common errors and fixes
Key Points & Sample Answer:
Q: How do you check the alert log?
A: Alert log is located in the ADR (Automatic Diagnostic Repository) directory, typically
under $ORACLE_BASE/diag/rdbms/. You can tail the alert log on Linux.
Practice:
Cause a simple error and check the alert log for messages.
Day 11: Real-life Scenario Questions
Topics:
- Database won’t open — troubleshooting steps
- Deadlock detection and resolution
- Handling corrupted datafiles
Key Points & Sample Answer:
Q: What will you do if the database does not open?
A: Check alert logs for errors, try starting the DB in mount mode, check control files, verify
file permissions, and resolve any corruption.
Practice:
Simulate errors on your test DB and try to fix them.
Day 12: Oracle RAC & Data Guard Basics
Topics:
- What is Oracle RAC (Real Application Clusters)?
- What is Data Guard and how it works?
- Role of primary and standby databases
Key Points & Sample Answer:
Q: What is Oracle Data Guard?
A: Data Guard maintains standby databases that replicate the primary database for disaster
recovery and high availability.
Practice:
Watch tutorials or read documentation about RAC and Data Guard.
Day 13: Linux Commands for Oracle DBA
Topics:
- Process monitoring (ps, top)
- File management (ls, tail, chmod)
- Oracle listener control (lsnrctl)
Key Points & Sample Answer:
Q: How do you check if Oracle processes are running?
A:
ps -ef | grep ora_
Practice:
Practice these commands on Linux shell.
Day 14: Mock Interview & Revision
Topics:
- Revise all important topics
- Answer mock questions
- Work on weak areas
Sample Mock Q&A:
1. What is the difference between a hot and cold backup?
2. How do you recover a lost control file?
3. Explain the function of DBWR process.
4. What is the purpose of the alert log?
Practice:
Do mock interviews with friends or record yourself answering questions.
