Saturday, 7 March 2026

RMAN Backup Details

Wednesday, 4 March 2026

Oracle DBA Interview Q and A

Professional Oracle DBA Interview & Real-Time Q&A

1️⃣ Will archive logs generate during Online Index Rebuild?

Q: Will archive logs be generated while rebuilding an index online?
A: Yes.

ALTER INDEX … REBUILD ONLINE generates redo.
If the database is in ARCHIVELOG mode, redo will produce archive logs.
Even during online rebuild, DML activity is captured in redo.

How to reduce redo generation?


ALTER INDEX emp_idx REBUILD ONLINE NOLOGGING;

⚠ Important:

NOLOGGING reduces redo generation.
However, the index becomes unrecoverable from redo during media recovery.
Always take a backup after using NOLOGGING.

2️⃣ What is `_btree_bitmap_plans` parameter?

Q: What is _btree_bitmap_plans?

A:
It is a hidden parameter that controls whether the optimizer can convert B-tree indexes into bitmap plans during query optimization.

Used when:

Boolean predicates
Multiple AND/OR conditions
Data warehouse environments

Example:


WHERE gender='M'
AND status='A'

Optimizer may:

Convert multiple B-tree indexes into bitmap
Perform bitmap AND/OR operations

⚠ Production Note:

It is an undocumented (hidden) parameter.
Should only be modified under Oracle Support guidance.

3️⃣ Incremental RMAN Backup Running Longer Than Usual

Q: My incremental backup is taking longer than usual. Why?

Possible Reasons:

1️⃣ Increased block changes

More DML since last level 0/1 backup
Check v$backup_datafile

2️⃣ Block Change Tracking disabled


SELECT status FROM v$block_change_tracking;

3️⃣ Network issue between DB server and Media server

NIC configuration change
Network latency
Check with OS/Network team

4️⃣ I/O performance degradation

Check AWR for I/O wait events

5️⃣ FRA full or slow disk

4️⃣ Why Voting Disks are Odd in Number?

Q: Why must voting disks be in odd numbers?

A:
Oracle Clusterware requires more than 50% availability to maintain quorum.

Example:

2 voting disks → Need 2 (100%) to survive
3 voting disks → Need 2 (majority)
5 voting disks → Need 3

Odd numbers ensure:

Clear majority
Avoid split-brain scenarios

5️⃣ How long does SQL_ID stay in Shared Pool?

Q: Is there a fixed time?

A: No fixed time.

SQL remains in shared pool:

Until aged out due to memory pressure
Or manually flushed

Factors:

Shared pool size
Cursor usage frequency
Memory pressure

Check:


SELECT sql_id, executions
FROM v$sql
WHERE sql_id='&sqlid';

6️⃣ Why does PLAN_HASH_VALUE change?

Reasons:

1️⃣ Statistics change
2️⃣ Data volume change
3️⃣ New index creation
4️⃣ Parameter change
5️⃣ Adaptive features

Optimizer generates multiple plans and chooses lowest cost.

7️⃣ Why does CHILD_NUMBER change?

A new child cursor is created due to:

Different bind values (Bind Peeking)
Histogram present
Different optimizer parameters
Different NLS settings
Different bind length
Bind-aware cursor
Cardinality feedback

Important Concept:

✅ Same SQL_ID
✅ Same PLAN_HASH_VALUE
❌ Different CHILD_NUMBER possible

Example:

No index on column
Change optimizer_index_cost_adj
Plan remains Full Table Scan
But new child cursor created

8️⃣ Histogram and Skewed Data Example

Table: EMP
Rows: 1M
Gender:

M → 98%
F → 2%

With histogram:


SELECT * FROM emp WHERE gender=:b1;

If:

:b1='M' → Full Table Scan
:b1='F' → Index Scan

Different plans due to skew.

9️⃣ SQL Profile vs SQL Plan Baseline

SQL Profile

Improves optimizer estimates
Reactive tuning
Doesn’t force plan
Uses auxiliary statistics

Used when:

Bad cardinality estimates
Need quick fix

SQL Plan Baseline (SPM)

Controls which plans are allowed
Prevents plan regression
Forces approved plan
Proactive stability control

Check:


SELECT sql_handle, plan_name, enabled, accepted, fixed
FROM dba_sql_plan_baselines;

When to use?

Situation	Use
Immediate tuning required	SQL Profile
Prevent plan regression	SQL Baseline
Multiple PHVs exist	SQL Baseline
Upgrade stability	SQL Baseline

🔟 Post 11g → 12c Migration Plan Stability

Problem:
No automatic guarantee all SQL will perform same after upgrade.

1️⃣1️⃣ Dirty Buffer & DBWR Behavior

Q: Can DBWR write uncommitted data to datafiles?

A: Yes.

Key Points:

Dirty buffers may contain committed & uncommitted data.
DBWR writes them if:
- Free buffer shortage
- Checkpoint
- Tablespace offline

BUT:

✔ Users cannot read uncommitted data (Read Consistency)
✔ Undo ensures rollback
✔ Redo ensures recovery

Crash Recovery Process

1️⃣ Roll forward using redo
2️⃣ Roll back uncommitted using undo

Final state = Only committed data.

1️⃣2️⃣ Low vs High Cardinality

Low Cardinality:
Few distinct values
Example: Gender (M/F)

High Cardinality:
Many distinct values
Example: Employee_ID

1️⃣3️⃣ Capture vs Use SQL Plan Baseline

optimizer_capture_sql_plan_baselines = TRUE
→ Automatically capture new plans
optimizer_use_sql_plan_baselines = TRUE
→ Use stored baselines during optimization

1️⃣4️⃣ Session Lock Scenario

Session A:


UPDATE emp SET sal=1000 WHERE empno=1;
-- no commit

Session B:


UPDATE emp SET sal=2000 WHERE empno=1;

Result:

Session B waits
It is a ROW LOCK
Not a latch

Difference:

Latch	Lock
Protects SGA structures	Protects data rows
Short duration	Transaction-based
Memory concurrency	Data consistency

1️⃣5️⃣ Connection Pool Timeout Issue

Error:


Timeout waiting for idle object
Unable to get connection

Causes:

Connection pool exhausted
Long-running queries
MaxActive too low
Sessions not released
App using 1 connection per query

Checks:


SELECT COUNT(*) FROM v$session WHERE status='ACTIVE';

Check OS:

ulimit -u
max user processes

After increasing max active connections, issue resolved.

Wednesday, 25 February 2026

OCI - Networking

All these are part of Oracle Corporation Cloud Infrastructure (OCI) networking.

1️⃣ What is VCN? (Virtual Cloud Network)

In your screenshot:
Training_VCN

🔹 VCN (Virtual Cloud Network)

A VCN is your private network inside OCI Cloud.

It is similar to:

A Virtual Data Center network
Like your company LAN, but in cloud

Example:


Training_VCN
CIDR: 10.0.0.0/16 (example)

Inside a VCN you create:

Subnets
Route Tables
Gateways
Security Lists
DRG etc.

2️⃣ What is CIDR Block?

Example from your screenshot:

Public Subnet → 10.0.0.0/24
Private Subnet → 10.0.1.0/24

🔹 CIDR (Classless Inter-Domain Routing)

It defines IP address range.

Example:


10.0.0.0/24

Means:

Total 256 IP addresses
10.0.0.0 – 10.0.0.255

3️⃣ What is Subnet?

From your screenshots:

Training_Public_Subnet → 10.0.0.0/24
Training_Private_Subnet → 10.0.1.0/24

🔹 Subnet

A subnet is a smaller network inside VCN.

Think like:


VCN = Big Apartment Building
Subnet = One Floor
VM = One Flat

🔹 Types of Subnets

✅ Public Subnet

Has route to Internet Gateway
VMs can have Public IP
Used for:
- Bastion server
- Web server

✅ Private Subnet

No direct internet access
Used for:
- Database
- Application server
- Internal systems

Your screenshot shows:


Subnet Access:
- Public Subnet
- Private Subnet

4️⃣ What is Internet Gateway (IGW)?

From your screenshot:


Training-IGW

🔹 Internet Gateway

It allows traffic between:


VCN  <---->  Internet

If a subnet has route:


0.0.0.0/0 → Internet Gateway

Then that subnet becomes PUBLIC.

5️⃣ What is Route Table?

From your screenshot:


Default Route Table for Training_VCN
Private_route_table

🔹 Route Table

It tells traffic:
👉 “Where should I go?”

Example Route Rule:


Destination: 0.0.0.0/0
Target: Internet Gateway

Meaning:
“All internet traffic go via IGW”

Your Private Route Table shows:


No items to display

Meaning:

No internet access
Only internal VCN traffic allowed

6️⃣ What is Route Rule?

Inside Route Table → Route Rules

Each rule contains:

Destination CIDR
Target Type
Target

Example:


Destination: 0.0.0.0/0
Target Type: Internet Gateway
Target: Training-IGW

7️⃣ What is DRG? (Dynamic Routing Gateway)

In your screenshot:


No items to display
Create DRG Attachment

🔹 DRG

Used to connect:

OCI VCN ↔ On-Premise Data Center
OCI ↔ OCI (different regions)
OCI ↔ FastConnect / IPSec VPN

If you want Hybrid Cloud → DRG required.

8️⃣ What is DHCP Option?

From screenshot:


Default DHCP Options for Training_VCN

🔹 DHCP

Automatically gives:

IP address
DNS server
Gateway

When VM starts → DHCP assigns networking details.

9️⃣ What is Security List? (Not shown but important)

Security List works like:
👉 Firewall

Controls:

Ingress (Inbound)
Egress (Outbound)

Example:
Allow:

Port 22 (SSH)
Port 1521 (Oracle DB)
Port 80/443 (Web)

🔟 What is DNS Domain Name?

From screenshot:


trainingsubnet.trainingvcn.oraclevcn.com

Used for:

Internal hostname resolution

Private subnet shows:


DNS isn’t enabled

1️⃣1️⃣ Regional Subnet

Your subnet type:


Regional

Means:

Available across all Availability Domains in region

Example region in screenshot:
Frankfurt (eu-frankfurt-1)

1️⃣2️⃣ Full Architecture from Your Screenshot

Your design currently:


Training_VCN
│
├── Public Subnet (10.0.0.0/24)
│     └── Internet Gateway attached
│
├── Private Subnet (10.0.1.0/24)
│     └── Private Route Table (No Internet)
│
└── Internet Gateway (Training-IGW)

This is a standard 2-tier architecture.

🔥 Simple Real-Time Example (For Oracle DBA)

If you deploy:

Component	Subnet
Bastion Host	Public Subnet
App Server	Private Subnet
Database	Private Subnet

Flow:


Your Laptop → Public IP → Bastion
Bastion → Private IP → DB

🚀 What is VPC / VCN Difference?

In AWS:

It is called VPC

In OCI:

It is called VCN

Both mean same:
Virtual private cloud network.

🎯 Summary Table

Component	Purpose
VCN	Entire virtual network
Subnet	Smaller network inside VCN
CIDR	IP range
Route Table	Traffic direction rules
Route Rule	Single routing entry
Internet Gateway	Connect to Internet
DRG	Connect to On-prem
DHCP	Auto IP configuration
Security List	Firewall

Sunday, 8 February 2026

Oracle Database @Google Cloud - Multi Cloud

👉 What this diagram is about (view)

This picture shows how Oracle Cloud and Google Cloud are directly connected by a fast private road.

Oracle Cloud = one city
Google Cloud = another city
Interconnect = a private highway between the two cities
No public internet involved

🔹 Simple story

Your application is in Google Cloud
Your database is in Oracle Cloud

Instead of sending data over the public internet (slow + risky),
Oracle and Google built a dedicated private connection just for customers.

🔵 Diagram 2: Oracle Database @ Google Cloud (September 2024)

👉 What this diagram is about (Layman view)

This picture shows Oracle Database running inside Google Cloud itself.

Not connected from outside — it is already there.

🔹 Simple story

Your application is in Google Cloud
Your Oracle database is ALSO in Google Cloud

Oracle installs and manages its database inside Google’s data center, but:

Oracle still controls the database
Google still controls the cloud

🔹 What happens here

No cross-cloud traffic
No interconnect needed
App and DB talk like neighbors
Extremely low latency
Oracle handles DB operations
Google handles infrastructure

Oracle Database@Google Cloud runs Oracle-managed OCI database infrastructure colocated within Google Cloud regions.

Applications use native GCP services, while databases run on OCI Exadata with OCI networking constructs such as VCN and subnets.

Connectivity between GCP VPCs and OCI networks is privately managed by Oracle, eliminating the need for interconnects or public networking.

The OCI control plane remains in Oracle Cloud, while the data plane resides inside Google Cloud, providing low latency, high availability, and full Oracle-managed database operations.

Inside Google data center, Oracle does this:

Oracle installs multiple independent racks
Each rack group has:
- Independent power feeds
- Independent network paths
- Independent storage
Oracle labels these internally as:
- AD-1
- AD-2
- FD-1 / FD-2 / FD-3

⚠️ These AD/FD are OCI logical constructs,
not Google’s zones.

Who manages what (VERY IMPORTANT)

Layer	Who manages it
Building, power, cooling	Google
Physical servers, storage	Oracle
Network between Oracle racks	Oracle
Oracle Exadata / ADB	Oracle
AD / FD logic	Oracle
Patching, backups, RAC	Oracle
App (VMs, GKE, Cloud Run)	You / Google

So Oracle is running OCI inside GCP, not OCI on top of GCP.

Multi-AD / HA in Oracle DB @ GCP

Example: Autonomous Database

Oracle deploys:
- Primary DB in one Oracle AD
- Standby DB in another Oracle AD
Both ADs are inside same GCP region
Failover handled by Oracle

👉 From DB point of view:
Same HA behavior as OCI region

=========================================================================

This diagram is not about architecture — it is about how easy Oracle Database@Google Cloud is to buy, operate, and use.
Think of it as customer journey + operations flow.

I’ll explain it step by step, in plain technical language, then summarize it in one clean mental model.

1️⃣ What this diagram represents (big picture)

Goal of the diagram:
👉 “Oracle Database behaves like a native Google Cloud service, even though Oracle manages it underneath.”

So this diagram answers:

How do you buy it?
How do you deploy & manage it?
How do you use it with other GCP services?

2️⃣ Step 1: Purchase in Google Cloud Marketplace

What happens technically

Oracle publishes Oracle Database@Google Cloud as a Marketplace offering
You subscribe using:
- Your Google Cloud account
- Your Google billing
No separate Oracle contract process

Key technical implication

Billing appears in GCP Billing
IAM access tied to GCP project
Subscription links your GCP project ↔ Oracle tenancy

📌 Under the hood:
Google forwards subscription metadata to Oracle → Oracle activates OCI resources.

3️⃣ Step 2: Deploy, manage, and monitor from Google Cloud Console

This is the most important part of the diagram.

What you see

Oracle Database appears as a service inside GCP Console
You can:
- Create Exadata / Autonomous DB
- Scale CPU / storage
- View metrics
- Monitor health