Interview Question 18 : What are oinstall and dba groups? Why we assign these groups to oracle user ?

 

What are oinstall and dba groups in Oracle?

Short answer

• oinstall → Controls software ownership and installation
• dba → Controls database administration privileges

Oracle separates these concerns intentionally for security, isolation, and operational correctness.

---

1. oinstall group (Oracle Inventory / software ownership)

What it is

oinstall is the primary group for the Oracle software owner (usually the oracle OS user).

Example:

oracle:x:54321:oinstall,dba

What oinstall controls internally

oinstall members can:

• Access Oracle Inventory (oraInventory)
• Install, patch, and upgrade Oracle software
• Own Oracle binaries under:
  • $ORACLE_HOME
  • $ORACLE_BASE

Internally, Oracle Universal Installer (OUI):

• Reads and writes inventory files
• Tracks installed homes, patches, versions
• Restricts access by group permissions

Typical directories:

/u01/app/oracle

/u01/app/oraInventory

Ownership:

owner: oracle

group: oinstall

permissions: 775

✅ Only oinstall members should modify Oracle software

---

Why Oracle needs oinstall

Think of oinstall as:

“Who is allowed to touch Oracle software itself?”

Reasons:

• Prevent unauthorized users from:
  • Replacing binaries
  • Injecting trojan executables
• Control who can:
  • Run OUI
  • Apply PSU/RU patches
  • Add or remove Oracle homes

✅ Security boundary between software management and DB administration

---

2. dba group (database administrative privilege)

What it is

dba is a secondary OS group that grants OS authentication for database admin access.

Members of dba can:

conn / as sysdba

without a password.

---

How Oracle uses dba internally

When you connect:

sqlplus / as sysdba

Oracle checks:

1. OS user
2. Group membership (dba)
3. Grants SYSDBA privilege internally

This is implemented via:

• OS authentication
• Bequeath (local) protocol
• No password file required (for local)

Internally:

• User mapped to internal user SYS
• Full control over database

✅ This is stronger than any database role

---

Why Oracle needs dba

Think of dba as:

“Who is trusted to control the database at OS level?”

Reasons:

• Emergency access when:
  • Database won’t open
  • Password file is missing or corrupt
• Allows:
  • Startup / shutdown
  • Recovery
  • Mount/open database
  • Bypass dictionary checks

⚠️ Members of dba can:

• Read any data
• Drop database
• Bypass auditing

So:

dba must be tightly controlled

---

3. Why assign BOTH groups to oracle user?

Typical setup:

user: oracle

primary group: oinstall

secondary groups: dba

Reason 1: Functional separation

Activity	Required Group
Install Oracle	oinstall
Apply patches	oinstall
Create database	dba
Startup/shutdown	dba
Run DB utilities	dba

Oracle user needs to do both:

• Manage software
• Administer database

---

Reason 2: Unix permissions model

• Only one primary group → used for default file creation
• oinstall as primary ensures:
  • All Oracle binaries are group-owned by oinstall
• dba as secondary ensures:
  • OS authentication works

---

4. Why Oracle separates oinstall and dba

This is deliberate defense-in-depth.

Without separation (bad design)

One group:

• Can install software
• Can administer database
• Can replace binaries

Single compromise = total takeover.

---

With separation (Oracle’s design)

You can create:

Role	Group
Software owner	oinstall only
DB operator	dba only
Full DBA	oinstall + dba

This allows:

• Least privilege
• Compliance (SOX, ISO, PCI)
• Auditability

---

5. Real-world example (production best practice)

Scenario

• Security team installs patches
• DBA team manages databases

Setup:

oinstall: oracle, patchuser

dba: dba1, dba2

Consequences:

• patchuser can patch Oracle
• Cannot drop database
• dba1 can admin DB
• Cannot modify binaries

✅ Strong operational separation

---

6. Other related Oracle groups (for context)

Group	Purpose
oper	Limited DBA (startup/shutdown)
asmadmin	Full ASM admin
asmdba	Database access to ASM
asmoper	Limited ASM operations
backupdba	RMAN-only admin
dgdba	Data Guard admin
kmdba	Encryption / TDE management

These follow the same pattern as dba:

• OS group → maps to SYS privileges

---

7. Security warning (very important)

Adding a user to dba is equivalent to giving them the SYS password—and more.

Implications:

• Bypasses auditing
• Bypasses database authentication
• Often violates compliance if misused

Best practice:

• Very few users in dba
• Strong monitoring on OS group changes

---

8. One‑line interview‑ready summary

oinstall controls ownership and installation of Oracle software, while dba controls OS‑authenticated database administrative access; assigning both to the oracle user allows it to manage software and administer the database while still enabling strong security separation.

Interview Question 17 : Type of SCN ?

 

1. Redo SCN vs Checkpoint SCN (hidden control mechanism)

Oracle tracks multiple SCNs internally, but two matter most here:

Redo SCN  : Highest SCN whose redo is safely on disk

Checkpoint SCN  : Lowest SCN for which all dirty buffers are written

Absolute rule

Checkpoint SCN ≤ Redo SCN

👉 Oracle never lets DBWR advance the checkpoint SCN beyond the redo SCN.

Why this matters

• DBWR can write dirty blocks
• But only blocks whose redo SCN ≤ redo flushed SCN
• If not → LGWR is forced first

This is the real internal enforcement of WAL.

---

2. Commit path: why LGWR is synchronous and DBWR is not

What happens on COMMIT (timeline)

Client session
   |
   |-- generate COMMIT redo record
   |
   |-- post LGWR
           |
           |-- write redo buffer to redo log
           |
           |-- fsync / IO completion
   |
   |<- commit success returned

Key point:

• Commit waits only for LGWR
• Commit does NOT wait for DBWR

Why DBWR is excluded

If commit waited for DBWR:

• Random I/O
• Unpredictable latency
• Massive scalability collapse

Oracle instead guarantees:

“If redo exists, we can always reproduce the data later”

---

3. Dirty buffer protection (the invisible gate)

Every dirty buffer in the buffer cache carries metadata:

• Buffer SCN
• Required redo SCN

Before DBWR writes a block:

1. DBWR checks required redo SCN
2. If redo is not on disk:
   • DBWR signals LGWR
   • DBWR waits
3. Only after redo flush completes:
   • DBWR writes the block

This mechanism is sometimes called a:

Redo write barrier

It is completely internal — DBAs only see effects, not the barrier itself.

---

4. Checkpoints: who really coordinates LGWR vs DBWR

Checkpoint is not just DBWR writing blocks.

During checkpoint:

• CKPT:
  • Signals LGWR to flush redo up to checkpoint SCN
  • Signals DBWR to write dirty buffers up to same SCN
• LGWR always goes first
• DBWR follows

Sequence:

CKPT
 ├─ LGWR → redo flush
 ├─ DBWR → dirty buffers
 └─ CKPT → datafile headers updated

Why datafile headers last:

• They record checkpoint SCN
• Must reflect redo that is already durable

---

5. RAC: same rule, more writers

In RAC, the rule becomes even stricter.

Extra components:

• Redo threads per instance
• LMS / GCS (global cache service)
• Shared disk

Rule still holds:

No instance can let DBWR write a block unless redo for that block is durable in that instance’s redo thread.

Additional complexity:

• Block shipping between instances
• Commit SCN must be globally consistent
• LGWR on own instance must flush before block can be shared as clean

This is why:

• Log latency kills RAC performance
• DBWR waits often hide behind global cache waits

---

6. What happens when LGWR is slow

Symptom 1: log file sync waits

• Users waiting on commit
• LGWR is bottleneck

Symptom 2: DBWR stalls

• Dirty buffers accumulate
• Buffer cache pressure increases
• “Free buffer waits” may appear

Why DBWR suffers

Because:

• DBWR cannot bypass LGWR
• Data blocks are hostage to redo durability

Root causes usually are:

• Slow redo disks
• Small redo logs
• Excessive commits
• Misconfigured commit batching

---

7. Why Oracle chose redo-first instead of data-first

Let’s compare alternatives.

Option A: Write data first ❌

• Breaks crash recovery
• Corrupt datafiles possible
• Unrecoverable inconsistencies

Option B: Write redo first ✅

• Sequential I/O
• Small writes
• Perfect recovery model
• Scales across CPU cores

This design decision is why Oracle can commit millions of TPS without writing datafiles synchronously.

---

8. Recovery proves LGWR’s primacy

After crash:

1. Oracle mounts database
2. Reads datafiles (possibly stale)
3. Reads redo logs
4. Applies redo → roll forward
5. Applies undo → roll back

Notice:

Datafiles are not trusted
Redo logs are trusted

That trust only exists because LGWR always wrote first.

---

9. Short mental model (architect‑level)

• Redo = truth
• Datafiles = cache
• LGWR = durability gatekeeper
• DBWR = background janitor

---

10. One‑line summary (perfect for interviews)

LGWR must write before DBWR because Oracle’s crash recovery depends on redo being the authoritative source of committed changes; DBWR is explicitly prevented from writing data blocks unless corresponding redo is already durable, enforcing Write‑Ahead Logging.

Interview Question 16 : Why LGWR writes before DBWR writes?

 

Why does LGWR write before DBWR in Oracle?

Short answer (core principle)

LGWR must write redo first to guarantee that every committed change can be recovered, even if dirty data blocks have not yet been written by DBWR.

This rule is called Write‑Ahead Logging (WAL) and it is non‑negotiable for any database that promises transactional durability.

LGWR (Log Writer): Writes redo entries from redo log buffer to online redo logs

DBWR (DB Writer): Writes dirty data blocks from buffer cache to datafiles

---

The problem Oracle must solve

Oracle must ensure ACID durability:

Once a user sees COMMIT successful, the change must survive:

• Instance crash
• Power failure
• OS crash
• Datafile not written yet

But Oracle uses:

• Deferred writes to datafiles (for performance)
• Memory caching of data blocks

So inevitably:

• Changed data blocks (dirty buffers) may sit in memory
• DBWR may write them minutes later
• Yet COMMIT must return immediately

This is where LGWR before DBWR becomes mandatory.

---

The WAL rule (the law of databases)

Redo describing a change must reach disk before the data blocks containing that change are allowed to be written.

Oracle enforces:

Redo on disk  ⇒  Data block may be written
Redo not on disk ⇒ Data block must NOT be written

This means:

• LGWR always precedes DBWR in the timeline of durability

---

Internal sequence (step by step)

1. User modifies data

UPDATE emp SET sal = 5000 WHERE empno = 7788;

Internally:

• Data block modified in buffer cache
• Undo generated in undo segment
• Redo generated for:
  • Data change
  • Undo change

💡 Redo goes to redo log buffer (SGA)

---

2. User issues COMMIT

On COMMIT:

• LGWR is signaled
• LGWR writes:
  • All redo entries up to the commit SCN
  • Including the COMMIT record itself

Only after redo is safely written to disk:

Commit complete

✅ Durability now guaranteed
❌ Data block may still be only in memory

---

3. DBWR writes later (asynchronously)

DBWR writes dirty blocks due to:

• Checkpoints
• Buffer pressure
• Tablespace going offline
• Clean shutdown

Before DBWR can write a block:

• Oracle checks: Has corresponding redo been flushed?
• If not → LGWR is forced first

This is called a redo write barrier.

---

Why Oracle does NOT let DBWR write first

Scenario if DBWR wrote first (dangerous)

1. DBWR writes dirty block to datafile
2. LGWR has not flushed redo yet
3. Instance crashes

After crash:

• Datafile shows new data
• Redo logs do not contain the change

➡️ Database corruption ➡️ Inconsistent datafiles ➡️ Recovery impossible

💥 This violates atomicity and durability

---

How Oracle enforces LGWR-first internally

1. Redo SCN tracking

Each dirty buffer tracks:

• Lowest redo SCN required
• DBWR checks redo availability before writing

2. Checkpoint mechanism

During checkpoint:

• CKPT signals:
  • LGWR to flush redo up to checkpoint SCN
  • DBWR to write all buffers <= that SCN

Sequence:

LGWR → Redo

DBWR → Data blocks

CKPT → Datafile headers

---

Crash recovery depends on this ordering

After instance crash

Oracle performs:

1. Roll Forward
   • Reads redo logs
   • Reapplies changes to data blocks
2. Roll Back
   • Uses undo for uncommitted transactions

This only works if:

• Redo always exists on disk
• Even if data blocks were never written

Thus:

Redo is the source of truth after a crash, not datafiles

---

Performance benefit (secondary but important)

LGWR:

• Writes sequentially
• Very fast
• Small I/O

DBWR:

• Writes random blocks
• Expensive I/O

Letting LGWR commit fast while DBWR is deferred:

• Improves throughput
• Reduces IO contention
• Scales better on busy systems

---

Analogy (sticky note vs filing cabinet)

• Redo log = sticky note journal
• Datafiles = filing cabinet

Rule:

Write the sticky note first, then reorganize the cabinet later

If the office burns:

• Sticky notes let you reconstruct
• Cabinets alone mean lost intent

---

Key guarantees achieved

Guarantee	Achieved by LGWR-before-DBWR
Durability	✅
Atomicity	✅
Crash recovery	✅
Fast commits	✅
Deferred writes	✅

---

One-line takeaway (exam / interview gold)

LGWR writes before DBWR because redo must be safely on disk before any data block changes can be permanently stored, ensuring crash recovery and transactional durability under Oracle’s Write-Ahead Logging protocol.

Interview Question 15 : How INSERT and DELETE Statements Are Executed in Oracle ?

 

1️⃣ How an INSERT Statement Is Executed in Oracle

Example

INSERT INTO emp (empno, ename, sal)

VALUES (101, 'ANURAG', 50000);

---

Step‑by‑Step Execution Flow

1️⃣ Parsing Phase

• Syntax and object validation
• Privilege check (INSERT on table)
• Hard or soft parse
• Execution plan preparation

✅ Uses Shared Pool (SGA)

---

2️⃣ Space Identification

Oracle determines:

• Which data block has free space
• Uses freelists / bitmap / ASSM
• No row locking needed (row doesn’t exist yet)

---

3️⃣ Undo Generation (Yes, Even for INSERT)

Oracle creates undo for the insert:

• Contains information to delete the inserted row if rollback happens

📌 Undo is mandatory for:

• Rollback
• Read consistency
• Flashback

---

4️⃣ Redo Generation

Redo is generated for:

• Undo records
• Inserted row data

Redo is placed in:

• Redo Log Buffer
• Later flushed to redo logs by LGWR

---

5️⃣ Data Written to Buffer Cache

• New row inserted in memory
• Data block marked DIRTY
• Not yet written to disk

---

6️⃣ Commit or Rollback

✅ COMMIT

• LGWR flushes redo
• Transaction becomes permanent
• Space becomes visible to others

❌ ROLLBACK

• Undo used to remove inserted row
• Block returns to original state

---

Key INSERT Characteristics

Aspect	Behavior
Locks	Only table TM lock (RX)
Row locks	❌ None
Undo generated	✅ Yes
Redo generated	✅ Yes
Disk write	❌ Deferred

---

2️⃣ How a DELETE Statement Is Executed in Oracle

Example

DELETE FROM emp WHERE empno = 100;

---

Step‑by‑Step Execution Flow

1️⃣ Parsing & Optimization

Same as UPDATE:

• Parse SQL
• Optimizer selects index or scan

---

2️⃣ Row Identification

• Oracle finds target ROWIDs
• Rows are identified, not yet removed

---

3️⃣ Acquire Locks

• Row‑level exclusive (RX) locks on rows
• TM lock on table

✅ Prevents concurrent UPDATE/DELETE on same rows
✅ SELECTs are NOT blocked

---

4️⃣ Undo Generation (BEFORE DELETE)

Undo stores:

• Complete old row image
• Required for:
  • Rollback
  • Consistent reads
  • Flashback

📌 DELETE generates a lot of undo

---

5️⃣ Redo Generation

Redo recorded for:

• Undo
• Delete operation

---

6️⃣ Row Is Marked Deleted (Not Removed)

Important concept:

DELETE does not physically remove the row immediately

Instead:

• Row is logically removed
• Space reusable later
• Cleaned by subsequent inserts or segment shrink

✅ This enables rollback

---

7️⃣ Commit or Rollback

✅ COMMIT

• Redo flushed
• Locks released
• Row permanently deleted

❌ ROLLBACK

• Row restored completely from undo

---

Key DELETE Characteristics

Aspect	Behavior
Row locks	✅ Yes
Undo volume	🔥 High
Redo	✅ Yes
Space freed	❌ Logical first
SELECT blocked	❌ No

---

3️⃣ UPDATE vs INSERT vs DELETE (Internal Comparison)

Feature	INSERT	UPDATE	DELETE
Row exists before	❌	✅	✅
Row‑level lock	❌	✅	✅
Undo usage	Medium	Medium	High
Redo generation	✅	✅	✅
Affects indexes	✅	✅	✅
Can rollback	✅	✅	✅

---

4️⃣ Why DELETE Is Slower Than TRUNCATE (Interview Favorite)

DELETE	TRUNCATE
DML	DDL
Row‑by‑row	Metadata only
Undo generated	✅
Redo	✅
Rollback	✅
Triggers fire	✅

🔑 TRUNCATE is fast because it doesn’t generate undo for each row

---

5️⃣ RAC‑Specific Behavior (Bonus DBA Knowledge)

In Oracle RAC:

• Row locks are tracked globally via GES
• Cache Fusion ensures consistency across instances
• UPDATE/DELETE on same row from two instances:
  • One waits for the other

✅ No data corruption
✅ Distributed locking handled automatically

---

6️⃣ Common Interview Traps (Correct Answers)

Q: Does DELETE free disk space immediately?

❌ No
✅ Row is logically removed; space reused later

---

Q: Does INSERT generate undo?

✅ Yes — for rollback

---

Q: Which DML generates most undo?

✅ DELETE

---

Q: Can SELECT run while DELETE is happening?

✅ Yes (read consistency via undo)

---

Q: When is data written to disk?

✅ Later by DBWR, not during DML

---

7️⃣ One‑Line Interview Answers ⭐

• INSERT:

  Adds new rows by generating undo and redo and modifying memory blocks, making changes permanent only after commit.

• DELETE:

  Logically removes rows by locking them, generating undo and redo, and permanently deleting them upon commit.

• UPDATE (recap):

  Modifies existing rows by generating undo and redo while maintaining read consistency.

---

✅ Final DBA Mental Model

DML Statement
   ↓
Parse & Optimize
   ↓
Row identification
   ↓
Locks
   ↓
Undo
   ↓
Redo
   ↓
Buffer Cache change
   ↓
COMMIT / ROLLBACK

Interview Question 14 : Can you explain how update statement is executed in oracle database ?

 

How an UPDATE Statement Is Executed in Oracle Database

High‑Level Overview (One‑Line)

When an UPDATE statement is issued, Oracle parses the SQL, locates the target rows, locks them, creates undo, generates redo, modifies data in memory, and finally commits or rolls back the changes.

---

Example Statement

UPDATE emp

SET salary = salary + 1000

WHERE empno = 100;

---

Step‑by‑Step Execution Flow

---

1️⃣ SQL Parsing Phase

a) Syntax & Semantic Check

• Oracle verifies:
  • SQL syntax
  • Table and column existence
  • User privileges (UPDATE privilege on table)

b) Hard Parse or Soft Parse

• Soft parse: Execution plan already in shared pool ✅
• Hard parse: Plan must be generated ❌ (CPU expensive)

📌 Uses SGA → Shared Pool

---

2️⃣ Optimizer Chooses Execution Plan

Oracle Optimizer decides:

• Full table scan vs index access
• Join order (if joins exist)
• Row filtering strategy

for :

WHERE empno = 100

✅ Optimizer will usually choose an index unique scan on primary key

---

3️⃣ Row Source Generation

• Oracle identifies ROWIDs of rows to be updated
• Row filtering happens here

✅ No actual data change yet

---

4️⃣ Acquire Required Locks

Row‑Level Locks (Important)

• Oracle automatically places:
  • Row Exclusive (RX) lock on affected rows
  • Table‑level TM lock (Row Share mode)

✅ Locks prevent other sessions from modifying the same rows
✅ Readers are NOT blocked (Oracle multi‑version concurrency control)

📌 Locking happens BEFORE data change

---

5️⃣ Undo Data Creation

Before modifying data, Oracle:

• Copies old version of the row into an Undo segment
• Registers undo information for rollback and read consistency

📌 Undo is generated first, not redo.

✅ Enables:

• Rollback
• Consistent reads for other queries

---

6️⃣ Redo Generation

Oracle generates redo entries describing:

• Undo creation
• New data changes

Redo is written:

• First to Redo Log Buffer (SGA)
• Later flushed to disk by LGWR

✅ Ensures durability and crash recovery

---

7️⃣ Data Block Changes in Buffer Cache

• Data blocks are:
  • Read into DB Buffer Cache (if not already present)
  • Modified in memory only
• Blocks are marked DIRTY

📌 Data is NOT immediately written to disk

---

8️⃣ Transaction Remains Open

At this point:

• UPDATE is executed
• Locks are held
• Undo and redo exist
• Data blocks are dirty in memory

❗ Changes are NOT permanent yet

---

9️⃣ Commit or Rollback

---

✅ If COMMIT Is Issued

1. LGWR writes redo to disk
2. Commit SCN generated
3. Locks released
4. Undo marked reusable
5. Commit acknowledged to user

✅ Data becomes permanent

---

❌ If ROLLBACK Is Issued

1. Oracle uses undo data
2. Restores old row versions
3. Locks released
4. Redo written for rollback

✅ Database returns to original state

---

10️⃣ Background Processes Involved

Process	Role
Server Process	Executes UPDATE
DBWR	Writes dirty blocks later
LGWR	Writes redo on commit
CKPT	Checkpoint coordination
SMON	Crash recovery (if needed)

---

Key Oracle Concepts During UPDATE

---

Read Consistency (MVCC)

• Other sessions querying the same row:
  • See old data until commit
• Achieved using Undo

✅ Readers are not blocked by writers

---

Concurrency Behavior

Operation	Allowed
Concurrent SELECT	✅
Concurrent UPDATE same row	❌ (waits)
UPDATE different rows	✅

---

Isolation Levels

• Default: READ COMMITTED
• Oracle allows:
  • READ COMMITTED
  • SERIALIZABLE

Isolation level affects:

• Consistent read versions
• Undo usage

---

What Happens on Disk?

Area	Written When
Datafiles	Later by DBWR
Redo Logs	On commit
Undo Tablespace	Immediately logged
TEMP	Not used (normally)

---

Common Interview Questions (With Answers)

Q: Does UPDATE write data directly to disk?

❌ No
✅ Modifies memory blocks first

---

Q: Is redo generated for UPDATE?

✅ Yes (for both undo and redo)

---

Q: Why is undo generated before redo?

✅ To ensure rollback and read consistency

---

Q: Are SELECTs blocked by UPDATE?

❌ No
✅ Oracle uses MVCC

---

Q: When is commit actually safe?

✅ When LGWR writes redo to disk

---

Simple Real‑Life Analogy

🏦 Bank Transaction

• Undo = Previous balance image
• Redo = Transaction log
• Buffer cache = Working counter
• Datafile = Bank vault
• Commit = Receipt printed

---

One‑Line Interview Answer ⭐

Oracle executes an UPDATE by parsing the SQL, locating target rows, locking them, generating undo and redo, modifying data in the buffer cache, and making the changes permanent only after a commit.