Logo (2)
Big Data Hadoop Training

Expert Database Management in Oracle

  • April 5, 2025
  • Com 0

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

Versions

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

  1. Instance – Manages memory & processes
  2. Database – Stores datafiles, control files, redo logs
  3. Schemas – Logical structure of database objects (tables, views, procedures)

Memory Structure

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 Architecture

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

Copy Code 


-- Create a new database (Run in SQL*Plus or Oracle DBCA)
CREATE DATABASE mydb;
ALTER DATABASE OPEN;
Table Operations
Copy Code 


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, Update, Delete
Copy Code 


INSERT INTO employees VALUES (1, 'John Doe', 60000);
UPDATE employees SET salary = 70000 WHERE emp_id = 1;
DELETE FROM employees WHERE emp_id = 1;
PL/SQL Procedure Example
Copy Code 


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

Copy Code 


CREATE INDEX idx_emp_name ON employees(name);
Check Execution Plan
Copy Code 


EXPLAIN PLAN FOR SELECT * FROM employees WHERE name = 'John';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Performance Tuning Tips

  • 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

Using RMAN (Recovery Manager)

Copy Code 


-- Full Database Backup
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
Point-in-Time Recovery
Copy Code 


RMAN> RECOVER DATABASE UNTIL TIME '2024-04-01 10:00:00';
Flashback Query for Data Recovery
Copy Code 


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

6.Security Best Practices

User & Role Management

Copy Code 


-- Create User
CREATE USER dbadmin IDENTIFIED BY strongpassword;
-- Grant Permissions
GRANT CONNECT, RESOURCE TO dbadmin;
-- Revoke Permission
REVOKE RESOURCE FROM dbadmin;
Encryption & Data Security
Copy Code 


ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY 'SecretKey123';
Prevent SQL Injection

  • Always use bind variables
Copy Code 


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

Setting Up Data Guard
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (‘/path/to/standby.log’) SIZE 50M;
Monitor Replication Status

Copy Code 


SELECT THREAD#, SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;

8.Query Optimization & Troubleshooting

Identify Slow Queries

Copy Code 


SELECT sql_id, sql_text, elapsed_time
FROM v$sql
ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
Kill Long-Running Query
Copy Code 


SELECT sid, serial# FROM v$session WHERE username = 'DBADMIN';
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
Gather Table Statistics
Copy Code 


EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
Enable Automatic Query Optimization
Copy Code 


ALTER SYSTEM SET OPTIMIZER_MODE = ALL_ROWS;
Final Thoughts & Best Practices
Oracle Expert Database Management Key Takeaways

  • 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.

Recommended Tools for Oracle DB Management

  • SQL Developer (GUI for querying & development).
  • OEM (Oracle Enterprise Manager) (Monitoring & Performance Tuning).
  • RMAN (Backup & Recovery).
  • Data Pump (expdp & impdp for data migration).

Download Elysium Spark Note