1.Introduction to Oracle Database
What is Oracle Database? Oracle Database is a Relational Database Management System (RDBMS) widely used in enterprise applications for high-performance transaction processing, data warehousing, and cloud solutions. Key Features- ACID Compliance (Atomicity, Consistency, Isolation, Durability)
- PL/SQL (Procedural SQL) for advanced scripting
- Automatic Storage Management (ASM)
- Partitioning for better performance
- High Availability with RAC & Data Guard
| Version | Release Year | Key Feature |
| Oracle 11g | 2007 | Advanced Compression, Active Data Guard |
| Oracle 12c | 2013 | Multi-Tenant Architecture |
| Oracle 18c | 2018 | Autonomous Database |
| Oracle 19c | 2019 | Long-Term Support, JSON Support |
| Oracle 21c | 2021 | Blockchain Tables, Native JSON |
2.Oracle Database Architecture
Core Components- Instance – Manages memory & processes
- Database – Stores datafiles, control files, redo logs
- Schemas – Logical structure of database objects (tables, views, procedures)
| Component | Description |
| SGA (System Global Area) | Stores shared memory structures (buffer cache, redo log buffer) |
| PGA (Program Global Area) | Memory dedicated to a process (sort areas, hash joins) |
| Redo Logs | Tracks changes for recovery |
| Process | Description |
| PMON (Process Monitor) | Cleans up failed processes |
| SMON (System Monitor) | Performs crash recovery |
| DBWR (Database Writer) | Writes data from buffer cache to disk |
| LGWR (Log Writer) | Writes redo logs |
3.Basic SQL & PL/SQL Commands
Database Operations
-- Create a new database (Run in SQL*Plus or Oracle DBCA)
CREATE DATABASE mydb;
ALTER DATABASE OPEN;
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
salary NUMBER(10,2)
);
ALTER TABLE employees ADD department_id NUMBER;
DROP TABLE employees;
INSERT INTO employees VALUES (1, 'John Doe', 60000);
UPDATE employees SET salary = 70000 WHERE emp_id = 1;
DELETE FROM employees WHERE emp_id = 1;
CREATE OR REPLACE PROCEDURE raise_salary (emp_number NUMBER, increase NUMBER) AS
BEGIN
UPDATE employees SET salary = salary + increase WHERE emp_id = emp_number;
END;
4.Indexing & Performance Tuning
Index Types| Index Type | Description |
| B-Tree Index | Default index, efficient for equality & range queries |
| Bitmap Index | Best for low-cardinality columns (few distinct values) |
| Function-Based Index | Used for computed columns |
| Partitioned Index | Improves performance for large tables |
CREATE INDEX idx_emp_name ON employees(name);
EXPLAIN PLAN FOR SELECT * FROM employees WHERE name = 'John';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
- Avoid SELECT * → Fetch only necessary columns.
- Use bind variables to optimize execution plans.
- Partition large tables for faster access.
- Enable Parallel Query Execution for complex reports.
5.Backup & Recovery Strategies
Types of Oracle Backups| Backup Type | Description |
| Full Backup | Entire database copy |
| Incremental Backup | Backs up only changed data |
| Redo Log Backup | Ensures transactions are not lost |
-- Full Database Backup
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> RECOVER DATABASE UNTIL TIME '2024-04-01 10:00:00';
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);
6.Security Best Practices
User & Role Management
-- Create User
CREATE USER dbadmin IDENTIFIED BY strongpassword;
-- Grant Permissions
GRANT CONNECT, RESOURCE TO dbadmin;
-- Revoke Permission
REVOKE RESOURCE FROM dbadmin;
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY 'SecretKey123';
- Always use bind variables
EXECUTE IMMEDIATE 'SELECT * FROM employees WHERE emp_id = :id' USING emp_id;
7.High Availability & Replication
Oracle High Availability Solutions| Feature | Description |
| RAC (Real Application Clusters) | Load balancing & failover across multiple nodes |
| Data Guard | Disaster recovery with primary-standby databases |
| GoldenGate | Real-time replication & data integration |
| ASM (Automatic Storage Management) | Disk management for high performance |
SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
8.Query Optimization & Troubleshooting
Identify Slow Queries
SELECT sql_id, sql_text, elapsed_time
FROM v$sql
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
SELECT sid, serial# FROM v$session WHERE username = 'DBADMIN';
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
ALTER SYSTEM SET OPTIMIZER_MODE = ALL_ROWS;
- Understand Oracle architecture (SGA, PGA, Background Processes).
- Optimize queries using indexes, partitions, and bind variables.
- Use RMAN for backups and implement Data Guard for high availability.
- Monitor database performance with V$ views and EXPLAIN PLAN.
- Enhance security using roles, encryption, and auditing.
- SQL Developer (GUI for querying & development).
- OEM (Oracle Enterprise Manager) (Monitoring & Performance Tuning).
- RMAN (Backup & Recovery).
- Data Pump (expdp & impdp for data migration).
