Oracle

Oracle is an enterprise-grade Relational Database Management System (RDBMS) developed by Oracle Corporation. It leads the global commercial database market with core advantages of high reliability, robust security, and excellent scalability. It supports large-scale data storage, complex transaction processing, and distributed architectures, and is widely used in core systems of critical industries such as government, finance, and telecommunications.

I. Background and Fundamentals

1. Development Background

The birth of Oracle stemmed from the demand for high-availability and high-concurrency enterprise-level data management. In the 1970s, with the acceleration of enterprise informatization, traditional file systems and small-scale databases could no longer meet the needs of massive data storage, multi-user concurrent access, and complex business logic processing.

  • In 1977, Larry Ellison and his team founded Software Development Laboratories (SDL) based on Edgar Codd's relational database theory, and began developing a relational database system.
  • In 1979, they released the first commercial relational database Oracle 2 (skipping Oracle 1 directly), which became the world's first commercial database to support the SQL language.
  • Subsequent versions have been continuously upgraded, gradually adding enterprise-level features such as distributed architecture, high-availability clusters, and data warehouses. Oracle 8i introduced Internet support, Oracle 9i enhanced cluster capabilities, Oracle 10g focused on grid computing, Oracle 12c launched a cloud-native architecture, and Oracle 23c further optimized performance in cloud environments.
  • Today, Oracle has evolved into a comprehensive technology ecosystem integrating databases, middleware, and cloud computing, with its database products consistently ranking first in the global commercial database market.

2. Core Fundamentals

  • Relational Database Model: Oracle fully complies with the relational model, where data is stored in tables as the basic storage unit. Relationships between tables are established through primary keys and foreign keys, supporting entity integrity, referential integrity, and domain integrity to ensure data consistency.
  • SQL and PL/SQL Support: It is fully compatible with standard SQL and extends with Procedural Language/SQL (PL/SQL) . PL/SQL combines the data manipulation capabilities of SQL with the process control capabilities of programming languages, supporting database objects such as stored procedures, functions, and triggers, which can encapsulate complex business logic.
  • ACID Transactions and Strong Consistency: It strictly adheres to ACID properties and supports distributed transactions (achieving cross-node transaction consistency through the two-phase commit protocol), meeting the stringent data reliability requirements of core businesses such as finance and telecommunications.
  • Commercial Licensing Model: It adopts a commercial licensing system, where users need to pay licensing fees based on the number of servers, CPU cores, etc., and receive official technical support and maintenance services from Oracle—distinguishing it from MySQL's open-source and free model.

II. Components and Technical Principles

Oracle adopts a multi-layered architectural design, with high modularity from underlying storage to upper-layer application interfaces. Its core technologies are built around three goals: high availability, high scalability, and high performance.

1. Core Architectural Components

The architecture of an Oracle database can be divided into three parts: instance layer, storage layer, and application interface layer. The separated design of instances and storage is the core foundation of its high-availability features.

Layer Core Components Function Description
Instance Layer (Memory + Processes) System Global Area (SGA), Program Global Area (PGA), Background Processes 1. SGA: A shared memory area that includes the database buffer cache (caching disk data), shared pool (caching SQL statements and execution plans), redo log buffer (caching transaction logs), etc.—it is the core of instance operation.
2. PGA: A private memory area allocated for individual user processes, storing session variables, sorted data, etc.
3. Background Processes: Include DBWR (Database Writer, writing dirty data from the database buffer cache to disk), LGWR (Log Writer, flushing the redo log buffer to disk in real time), CKPT (Checkpoint, triggering data writes and updating control files), SMON (System Monitor, responsible for instance recovery), PMON (Process Monitor, responsible for abnormal process recovery), etc.—ensuring stable instance operation.
Storage Layer (Disk Storage) Data Files, Redo Log Files, Control Files, Temporary Files 1. Data Files: Store actual business data; a tablespace corresponds to multiple data files, and data files cannot span tablespaces.
2. Redo Log Files: Record all data modification operations, used for transaction recovery and instance recovery. They adopt a default circular writing mechanism and support mirroring of multiple log file groups.
3. Control Files: Store database metadata (e.g., data file locations, log file locations, database name), which are critical for database startup and require multi-copy backup.
4. Temporary Files: Store temporary data for operations such as sorting and hash joins, and are automatically cleared when the instance is shut down.
Application Interface Layer Oracle Call Interface (OCI), JDBC, ODBC Provides access interfaces for various programming languages, supporting mainstream development languages such as Java, C/C++, and Python to realize interaction between applications and the database. It also supports client tools such as SQL*Plus and Oracle SQL Developer.

2. Core Technical Principles

  • High-Availability Technologies

    • Real Application Clusters (RAC) : Allows multiple database instances to share the same set of storage devices, achieving load balancing and automatic failover. When an instance fails, other instances can take over its business without downtime, making it suitable for core business systems.
    • Data Guard: A disaster recovery technology based on redo log synchronization, divided into physical standby databases (data is completely consistent with the primary database and supports failover) and logical standby databases (data synchronization through SQL statements, which can be used for report querying)—realizing data consistency and disaster recovery between primary and standby databases.

  • Storage Optimization Technologies

    • Tablespace Management: Classifies and stores data in different tablespaces according to business needs (e.g., system tablespace, user tablespace, temporary tablespace), facilitating data management and backup/restore. It supports auto-extension and space quota functions.
    • Segment, Extent, Block Storage Structure: The smallest unit of data storage is the data block (8KB by default, configurable). Multiple data blocks form an extent, and multiple extents form a segment (e.g., table segment, index segment). This layered structure optimizes data read/write efficiency.
    • Partitioned Table Technology: Supports multiple partitioning methods such as range partitioning, hash partitioning, and list partitioning. Large tables are split into multiple small partitions, reducing the data scanning range during queries and improving query performance.

  • Concurrency Control Technologies

    • Multi-Version Concurrency Control (MVCC) : Generates multiple versions of data rows, allowing read operations not to block write operations and vice versa, improving system throughput in high-concurrency scenarios.
    • Locking Mechanism: Includes various lock types such as row-level locks, table-level locks, shared locks, and exclusive locks. Row-level locks are used for concurrent data modification, while table-level locks are used for batch operations. Lock granularity can be flexibly adjusted according to business requirements.

  • Index Technologies

    • Supports multiple index types such as B+ tree indexes, bitmap indexes, function-based indexes, and partitioned indexes. Bitmap indexes are suitable for low-cardinality columns (e.g., gender, status) and can significantly improve statistical query efficiency. Function-based indexes support indexing on the results of function calculations, solving performance issues of complex queries.

III. Application Scenarios

With its powerful enterprise-level features, Oracle has become the preferred database for critical business systems, with core application scenarios including:

  1. Financial Industry: Core transaction systems of banks, transaction clearing systems of securities companies, policy management systems of insurance companies, etc. These scenarios have extremely high requirements for data consistency and high availability. The Oracle RAC + Data Guard architecture can achieve 7×24 uninterrupted operation.
  2. Telecommunications Industry: User billing systems, core network element management systems, customer service systems, etc. The telecommunications industry has massive data volume and high concurrent access. Oracle's partitioned table and parallel query technologies can support fast processing of massive data.
  3. Government and Public Utilities: Government data platforms, social security systems, tax systems, etc. These systems involve sensitive data. Oracle's security features such as Transparent Data Encryption (TDE) and fine-grained access control can ensure data privacy and compliance.
  4. Large-Scale Enterprise Applications: Group ERP systems, Supply Chain Management (SCM) systems, Customer Relationship Management (CRM) systems, etc. Oracle can be deeply integrated with enterprise-level applications such as Oracle ERP and SAP, supporting complex business processes and data management needs.
  5. Data Warehousing and Business Intelligence: Oracle supports data warehouse modeling, parallel data loading, Online Analytical Processing (OLAP) and other functions, which can integrate multi-source enterprise data and provide data support for decision analysis.

IV. Summary

Oracle is a feature-complete and high-performance enterprise-level commercial database. Its separated architecture of instances and storage, rich high-availability technologies, and powerful concurrency control capabilities make it a benchmark product for critical business systems. Although it has higher deployment costs and greater maintenance complexity than open-source databases, it remains irreplaceable in scenarios with stringent requirements for reliability, security, and scalability. Mastering the core principles and operation and maintenance technologies of Oracle is an important skill for database administrators (DBAs) and enterprise-level developers.