VMware ESXi and vSphere Cluster Management

Oracle Database Fundamentals

Learn Oracle Database fundamentals, including relational concepts, architecture, SQL, PL/SQL, security, administration, connectivity, backup, recovery, and availability.

Oracle Database is an enterprise relational database management system (RDBMS). It stores structured data in tables, enforces rules about that data, processes SQL statements, and provides transaction, security, backup, and availability features.

This lesson introduces the vocabulary and practical tasks needed by beginners, developers, and junior database administrators. Basic command-line use and familiarity with tables and records are helpful, but introductory SQL is included.

Oracle overview

What Oracle Database is

Oracle Database is a database product from Oracle Corporation. Oracle Corporation also provides other products and services, such as cloud infrastructure, application software, development tools, and middleware. These products are not the same thing as Oracle Database, although they may integrate with it.

Oracle Database is commonly used for banking and financial systems, telecommunications, healthcare, government applications, supply chains, ERP systems, customer management, analytics, and other workloads that require reliable concurrent access to important data.

Editions and deployment options

Oracle Database is available in editions and service offerings with different feature, scalability, and licensing characteristics. The exact edition names, feature limits, and licensing terms can change, so administrators should verify current Oracle documentation and agreements before selecting an edition.

• Enterprise-oriented deployments: Designed for demanding workloads, advanced security, partitioning, high availability, and large-scale operations where licensed features permit.
• Standard deployments: Intended for smaller or less specialized environments with a different feature and scalability profile.
• Developer and learning installations: Useful for practicing SQL and PL/SQL, subject to their usage terms and resource limits.
• Cloud database services: Oracle Database can be provisioned and managed through cloud services, reducing some infrastructure administration.
• On-premises and virtualized installations: Organizations may operate Oracle on physical servers, virtual machines, or private cloud platforms.

Relational database foundations

Tables, rows, columns, and schemas

A database is an organized collection of data and the software structures that manage it. A table stores related records. Each record is a row, and each property of a record is a column. A schema is the collection of database objects owned by a particular user, including tables, views, indexes, sequences, and program units.

For example, an EMPLOYEES table might have EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT_ID, and HIRE_DATE columns. Each employee is represented by one row.

Keys and relationships

• A primary key uniquely identifies each row and cannot contain NULL.
• A foreign key stores values that refer to a key in another table.
• One-to-many relationships connect one parent row to multiple child rows, such as one department having many employees.
• A many-to-many relationship is normally represented by a third, junction table.

A constraint is a rule enforced by Oracle. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.

Data types

Common Oracle data types include NUMBER for numeric values, VARCHAR2 for variable-length text, DATE for date and time values, TIMESTAMP for more precise date and time values, and CLOB or BLOB for large character or binary data. Select types according to the meaning and expected size of the data rather than storing everything as text.

Transactions and ACID

A transaction is a logical unit of work. Oracle supports the ACID properties:

• Atomicity: A transaction is completed as a unit or undone.
• Consistency: Constraints and database rules remain valid after a successful transaction.
• Isolation: Concurrent transactions are controlled so that intermediate changes are not incorrectly exposed.
• Durability: Committed changes survive normal failures through data and redo mechanisms.

Oracle architecture

Database versus instance

An Oracle database is the physical collection of files that stores data and recovery information. An instance is the set of memory structures and background processes that access that database. A database can be started and stopped by starting or stopping an instance. In clustered configurations, multiple instances can access one database.

SGA, PGA, and background processes

The System Global Area (SGA) is shared memory for an Oracle instance. It includes structures such as the database buffer cache, shared pool, and redo log buffer. The buffer cache holds recently used data blocks, while the shared pool stores parsed SQL information and other shared metadata.

The Program Global Area (PGA) is private memory associated with a server process or session. It can contain session state, sort areas, and other work memory. The amount of PGA needed depends on the work being performed.

Background processes perform recurring or specialized work. Database Writer (DBWn) writes changed blocks to data files. Log Writer (LGWR) writes redo entries to online redo logs. Checkpoint (CKPT) coordinates checkpoint information. System Monitor (SMON) performs instance recovery tasks, and Process Monitor (PMON) handles cleanup related to failed processes. Exact process names and responsibilities vary by release and configuration.

Physical files and logical storage

Data files contain table, index, and other object data. Control files contain important database metadata, including the database identity and file information. Online redo log files record changes so Oracle can recover committed work and repair inconsistent files after a failure. Archived redo logs are saved copies of filled online redo logs, used for point-in-time and media recovery.

A tablespace is a logical storage container made from one or more data files. A table or index occupies a segment. A segment is divided into extents, and extents are made of database blocks. This hierarchy separates logical object management from physical files.

Oracle accounts and security

An Oracle user is an authenticated account. By default, a user also owns a schema with the same name, although a schema and a user are conceptually different: the user is an account, while the schema is its collection of objects.

Administrative accounts, such as SYS and SYSTEM, have powerful capabilities. SYSDBA is an administrative privilege used for tasks such as starting an instance and performing certain recovery operations. Do not use highly privileged accounts for application code or routine reporting.

• System privileges authorize actions across object types, such as CREATE SESSION or CREATE TABLE.
• Object privileges authorize actions on a specific object, such as SELECT on a table.
• Roles group privileges so access can be managed consistently.
• Authentication verifies an identity using a password, external identity provider, certificate, or another configured method.

Use least privilege: grant only the access required for a task, limit administrative access, avoid sharing accounts, protect credentials, and review grants periodically.

SQL in Oracle

Creating related tables

CREATE TABLE departments (
  department_id NUMBER PRIMARY KEY,
  department_name VARCHAR2(100) NOT NULL
);

CREATE TABLE employees (
  employee_id NUMBER PRIMARY KEY,
  employee_name VARCHAR2(100) NOT NULL,
  department_id NUMBER REFERENCES departments(department_id),
  hire_date DATE DEFAULT SYSDATE
);

Insert parent rows before child rows when a foreign key requires the parent to exist.

INSERT INTO departments (department_id, department_name)
VALUES (10, 'Operations');

INSERT INTO employees (employee_id, employee_name, department_id)
VALUES (1001, 'Asha Patel', 10);

SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10
ORDER BY employee_name;

Filtering, grouping, and joins

Use WHERE to filter rows, ORDER BY to sort results, GROUP BY to form groups, and HAVING to filter groups. An inner join returns rows with matching values in both tables.

SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d
  ON d.department_id = e.department_id
WHERE d.department_name = 'Operations'
ORDER BY e.employee_name;

Oracle SQL functions include character functions such as UPPER and SUBSTR, numeric functions such as ROUND, date functions such as ADD_MONTHS, conversion functions such as TO_DATE and TO_CHAR, and aggregate functions such as COUNT, SUM, AVG, MIN, and MAX. Handle NULL deliberately with functions such as NVL or COALESCE.

Transactions

UPDATE employees
SET employee_name = 'Asha P. Patel'
WHERE employee_id = 1001;

SELECT employee_name FROM employees WHERE employee_id = 1001;

-- Make the change permanent:
COMMIT;

-- Or undo changes made since the last commit:
ROLLBACK;

SAVEPOINT marks a position within a transaction. ROLLBACK TO SAVEPOINT can undo later work without undoing the entire transaction. DDL often causes implicit commits, so do not mix structural changes and transactional experiments casually.

Sequences, views, indexes, and synonyms

• A sequence generates numeric values, often for keys.
• A view is a stored query that presents data without duplicating the underlying rows.
• An index can speed up selective searches and joins, but adds storage and write overhead.
• A synonym provides an alternate name for an object and can simplify references, but does not grant access by itself.

PL/SQL basics

PL/SQL is Oracle's procedural extension to SQL. SQL describes the data operation; PL/SQL adds variables, conditions, loops, reusable procedures and functions, and exception handling. Use SQL alone for set-based queries and changes when it expresses the task clearly. Use PL/SQL when procedural control, reusable database-side logic, or coordinated multi-step processing is needed.

Blocks, variables, and control structures

DECLARE
  l_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO l_count
  FROM employees
  WHERE department_id = 10;

  IF l_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Employees found: ' || l_count);
  ELSE
    DBMS_OUTPUT.PUT_LINE('No employees found');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;

A PL/SQL block can contain an optional DECLARE section, a required executable BEGIN...END section, and an optional EXCEPTION section. Control structures include IF, CASE, LOOP, WHILE, and FOR.

Procedures, functions, and triggers

CREATE OR REPLACE PROCEDURE add_department (
  p_id   IN departments.department_id%TYPE,
  p_name IN departments.department_name%TYPE
) AS
BEGIN
  INSERT INTO departments (department_id, department_name)
  VALUES (p_id, p_name);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    RAISE_APPLICATION_ERROR(-20001, 'Department ID already exists');
END;

A procedure performs an operation and may have parameters. A function returns a value and can be used in appropriate SQL or PL/SQL contexts. A trigger runs automatically in response to events such as INSERT, UPDATE, or DELETE. Triggers can enforce specialized rules or auditing, but excessive trigger logic can make data changes difficult to understand and troubleshoot.

Oracle tools and connectivity

SQL*Plus is a command-line client for executing SQL, PL/SQL, and administrative commands. Oracle SQL Developer is a graphical tool for browsing objects, editing SQL, running scripts, and inspecting results. Both use Oracle connectivity services.

Oracle Net Services carries client connections to an Oracle service. A listener is a server-side network process that accepts connection requests and directs them to a registered database service. A service name identifies a logical database service. A SID traditionally identifies an instance; it is not interchangeable with a service name in every connection configuration.

sqlplus username@//db-host.example:1521/service_name

In SQL Developer, supply the username, password, host, port, and service name in a connection definition. An application connection string contains equivalent information, but its exact format depends on the driver and programming language.

Database administration basics

Starting, stopping, and checking an instance

sqlplus / as sysdba
STARTUP;

Authorized administrators use STARTUP to start an instance. SHUTDOWN IMMEDIATE stops new work, waits for active transactions to finish when possible, and closes the database cleanly. Avoid SHUTDOWN ABORT except when normal shutdown is not possible or an approved emergency procedure requires it.

Administrative views such as V$INSTANCE, V$DATABASE, V$SESSION, and V$PARAMETER help inspect status, sessions, and configuration. Access to these views depends on account privileges. Monitor active sessions, waits, long-running statements, blocking sessions, tablespace capacity, and redo generation.

Users and storage

CREATE USER reporting_user IDENTIFIED BY "StrongPassword";
GRANT CREATE SESSION TO reporting_user;

GRANT SELECT ON departments TO reporting_user;
GRANT SELECT ON employees TO reporting_user;

Use a password policy and secure secret storage in real environments. Granting CREATE SESSION permits login, but it does not automatically permit reading application tables. For larger designs, create a read-only role and grant that role to the reporting account.

Tablespace administration includes checking free space, adding or resizing data files, and setting authorized autoextension limits. Autoextension can prevent short-term failures but must be bounded and monitored because it can consume underlying storage.

Basic performance considerations

• Use appropriate indexes for important filters and joins, while remembering that indexes increase insert, update, and delete work.
• Return only needed columns and rows instead of selecting unnecessary data.
• Use bind variables in applications to improve cursor reuse and reduce injection risk.
• Keep statistics current according to the organization's maintenance policy.
• Investigate execution plans, waits, blocking, CPU, I/O, memory, and data growth together rather than tuning from one symptom.

Backup, recovery, and availability

Backups protect against media failure, human error, corruption, and destructive incidents. Redo records changes so Oracle can recover work after a crash. Backups without a tested recovery procedure do not provide dependable recoverability.

Recovery Manager (RMAN) is Oracle's backup and recovery utility. It can create full and incremental backups, back up archived redo logs, record backup metadata, validate files, and restore or recover database components.

rman target /
BACKUP DATABASE PLUS ARCHIVELOG;

A basic workflow is to configure an appropriate backup destination and retention policy, run a full or incremental backup, include required archived redo logs, validate or list the backup, and perform restore tests. During recovery, restore required files from backup and apply redo and archived redo until the desired consistent point is reached.

Archive log mode causes completed online redo logs to be archived instead of overwritten immediately. It is generally required for most production backup and point-in-time recovery strategies. Confirm the recovery objectives, retention period, backup location, encryption, and restore testing policy before relying on a design.

Data Guard maintains one or more standby databases and can support disaster recovery and role transitions. Real Application Clusters (RAC) allows multiple instances to access one database, supporting scale and availability when correctly designed. These technologies solve different problems and require specialized operational planning.

Practical workflow: creating and querying data

1. Create the parent DEPARTMENTS table and child EMPLOYEES table.
2. Insert departments before employees that reference them.
3. Query individual tables to verify the rows.
4. Use an inner join to display employee and department names together.
5. Commit only after reviewing changes in a safe practice environment.

INSERT INTO departments (department_id, department_name)
VALUES (20, 'Engineering');

INSERT INTO employees (employee_id, employee_name, department_id)
VALUES (1002, 'Luis Garcia', 20);

SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON d.department_id = e.department_id
WHERE d.department_name = 'Engineering';

Troubleshooting common Oracle problems

Exam-relevant distinctions

• Database versus instance: Files are the database; memory and processes are the instance.
• Schema versus user: A schema owns objects; a user authenticates to the database. They commonly share a name but are different concepts.
• SGA versus PGA: SGA is shared instance memory; PGA is private process or session memory.
• Online redo versus archived redo: Online redo records current changes; archived redo preserves completed redo for longer recovery sequences.
• Service name versus SID: A service name identifies a logical service; a SID identifies an instance in configurations that use it.
• DDL, DML, DCL, and TCL: Structure, data, access control, and transaction control respectively.
• Privilege versus role: A privilege authorizes an action; a role groups privileges.
• RMAN versus SQL Developer: RMAN is specialized for backup and recovery; SQL Developer is primarily a graphical development and database access tool.

Next steps

After practicing these fundamentals, continue with Oracle database administration and SQL topics, then study advanced SQL, database design and normalization, PL/SQL programming, performance tuning, security administration, RMAN recovery, Data Guard, RAC, and cloud deployment.