Oracle Database: Components, Structure, and Management

Oracle database components function and interact through a combination of physical and logical structures, memory components, and background processes, all working to ensure data integrity and availability.

Here's a breakdown of how these core components function and interact:

I. Oracle Database Fundamentals

An Oracle database consists of both logical and physical structures.

A. Physical Structures These are viewable from the operating system and are fundamental to data storage and recovery.

• Control files: These are crucial binary files that record the physical structure of the database, including its name, timestamps, datafile locations and statuses, redo log file locations, archiving mode, tablespace information, checkpoint data, current log sequence number, and backup files. Oracle recommends at least two control files, but three on different disks is preferred for redundancy and availability to prevent a single point of failure. If a control file is corrupted or inaccessible due to media failure, the instance will shut down.

• Online redo log files: These files contain information necessary to re-create any database transactions and are multiplexed for reliability. The Log Writer (LGWR) writes to all members of a redo log group simultaneously. A "log switch" occurs when LGWR moves to a new group. In noarchivelog mode, older redo log files are overwritten, while in archivelog mode, log archiving happens before reuse. The Log Writer always writes to online redo log files before the Database Buffer Writer (DBWn) writes dirty blocks to datafiles, a critical "write-ahead protocol" that ensures data integrity.

• Archived redo log files: These are copies of online redo log files used for recovery purposes. If the database is in archivelog mode, the Archiver (ARCn) copies redo records from online redo logs to archived redo logs before they are reused.

• Other physical files include password files, parameter files, data files, alert logs, trace files, and backup files. Alert logs capture major changes, events, errors, warnings, and messages, and list initialization parameters during startup, aiding in troubleshooting.

B. Logical Structures These cannot be viewed from the operating system but define how data is organized within the database.

• Tablespace: Comprises at least one datafile, physically located on the server's file system. All database objects reside within a tablespace. A datafile belongs to exactly one tablespace.

    ◦ Temporary tablespaces: Used to manage space for database sort operations and global temporary tables. Assigning them to users prevents errors and improves parallel execution efficiency.

    ◦ Undo tablespace: Essential for maintaining undo information, which are records of transaction actions before commitment. Undo records are used for transaction rollbacks, database recovery, providing read consistency, Flashback Query, and Flashback features to recover from logical corruptions. Automatic undo management via undo tablespaces, introduced in Oracle 9i, is strongly recommended over rollback segments.

• Data block: The smallest unit of Oracle Storage where data is stored. Oracle I/O operates on data blocks.

• Extents: A specific number of continuous data blocks obtained in a single allocation, used to store information as part of a segment.

• Segment: Consists of one or more extents allocated within a tablespace. There are four types: Data, Index, Undo, and Temporary segments.

II. Oracle Instance Components

An Oracle Instance comprises the memory structure (SGA/PGA) and background processes.

A. Memory Structure (SGA/PGA) These are memory areas used by the database to improve performance and manage operations.

• Database buffer cache: Keeps recently used data in memory to reduce disk I/O.

• Shared pool: Made up of the library cache (stores recently executed SQL/PLSQL statements for reuse, boosting performance) and data dictionary cache (stores recently used data definitions like information about datafiles, tables, indexes, privileges, users, improving query and update performance).

• Redo log buffer: Keeps transaction log information (redo records) in memory before being written to online redo log files.

• Optional components include the Large pool (caches data for large operations like RMAN backup/recovery), Java pool, and Streams pool.

B. Background Processes These are processes that support the instance and perform various database operations.

• System Monitor (SMON): Performs system-level cleanup, instance recovery at startup if the database was not shut down cleanly, and cleans unused temporary segments.

• Process Monitor (PMON): Cleans up after failed user connections, resets active transaction tables, releases locks, and removes process IDs.

• Log Writer (LGWR): Writes redo records from the log buffer to online redo log files on disk.

• Database Buffer Writer (DBWn): Writes dirty blocks from the Database Buffer Cache to datafiles.

• Checkpoint (CKPT): Updates control files for synchronization with datafiles and ensures system-wide data consistency using System Change Numbers (SCN). Checkpoints are performed during proper database shutdown.

• Archiver (ARCn): Copies online redo log files to archived redo log files when archiving is enabled.

III. Interplay for Data Integrity and Availability

The functioning and interaction of these components are crucial for maintaining data integrity (accuracy and consistency) and availability (ensuring data is accessible when needed).

A. Strategic Backup and Recovery (B/R) Backup and Recovery are crucial for guaranteeing recovery and business continuity and minimizing Mean Time to Recover (MTTR).

• RMAN (Recovery Manager) is primarily used due to its benefits, including the ability to perform incremental backups, leverage the Data Recovery Advisor for diagnosing and repairing data failures and corruption, easy automation, verification of backup usability, and recovery of corrupted blocks without taking datafiles offline.

• Autobackup Controlfile and Backup Optimization are enabled in RMAN configuration.

• The Fast Recovery Area (FRA) is the default B/R file location, acting as a central storage area that automatically manages disk space and offers increased reliability as disk is safer than tape.

• Oracle block change tracking improves incremental backup performance by recording data block changes, allowing RMAN to identify changed blocks without scanning all data blocks.

• Production databases are always run in archivelog mode to enable full recovery options.

• A redundancy set including the last backup of data and control files, multiplexed copies of current redo log files, copies of the current control file, and all archived redo logs since the last backup is maintained for safety. Tape copies are also maintained offsite with encryption for security.

• Hot backups (database online, inconsistent state) and cold backups (database shut down, consistent state) are strategic choices, with RMAN able to perform whole-database hot backups.

• Recovery typically involves restoring files and then applying redo log files and incremental backups. For example, recovering a lost/damaged control file with autobackup enabled involves restoring the control file from autobackup, mounting the database, restoring the database, recovering it, and then opening with RESETLOGS.

B. Oracle Data Guard Data Guard ensures high availability, data protection, and disaster recovery. It creates, maintains, manages, and monitors standby databases as copies of the production (primary) database. If the primary database becomes unavailable, Data Guard can switch a standby to the primary role, minimizing downtime.

• Configuration: One primary database and one or more standby databases, with redo logs shipped and applied to standbys.

• Types of Standby Databases:

    ◦ Physical Standby: A physically identical copy, synchronized via Redo Apply (recovering redo data).

    ◦ Logical Standby: Contains the same logical information but can have different physical organization. Synchronized via SQL Apply (transforming redo data into SQL statements). This allows concurrent user access for queries and reporting, and enables almost no downtime upgrades.

    ◦ Snapshot Standby: A fully updatable standby that archives redo logs but doesn't apply them until converted back to a physical standby.

• Operations: Switchover (planned role reversal with no data loss) and Failover (occurs when primary fails, transitioning a standby to primary, can be configured for zero data loss).

• Protection Modes: Max Availability (zero data loss, sync transport, availability priority), Max Protection (sync transport, zero data loss), Max Performance (performance priority, potential data loss).

C. Oracle ASM (Automatic Storage Management) ASM is a file system manager that optimizes storage for database files, supporting features like striping (for performance) and mirroring (for high availability).

• An ASM Instance is a lightweight memory structure that maintains metadata about files and disk groups.

• A Disk Group is a group of disks (LUNs or JBODs) across which striping is done for performance.

• ASM Redundancy involves mirroring disk files within the same disk group for availability. Types include External (storage array handles mirroring), Normal (ASM maintains two copies, tolerates 1 fault), and High (ASM maintains three copies, default, tolerates 2 faults).

• Fail Groups allow mirroring across separate servers for improved fault tolerance.

D. Database Security Database security focuses on preventing unauthorized use of the database and its components. Security breaches are often caused by internal users. Joe's strategy is based on Cisco Systems' Triple A principles (Authentication, Authorization, and Auditing) and Data Encryption via TDE.

• Authentication: Granting database access through credential validation, managing password profiles (failed login attempts, password lifetime), and enforcing password complexity.

• Authorization: Limiting access for authenticated users. Oracle follows the Principle of Least Privilege, meaning users cannot create sessions or manipulate objects without explicit privileges. Avoiding the ADMIN OPTION helps maintain oversight.

• Auditing: Monitoring user activity. Fine Grained Auditing (FGA) is used to audit specific selects, inserts, or updates on sensitive objects to manage space efficiently.

• Data Encryption: Using Transparent Data Encryption (TDE) for sensitive data. TDE automatically manages encryption/decryption keys, allows indexing of encrypted columns, and uses the strong AES algorithm by default. An Oracle Wallet stores TDE's master key.

E. Database Startup and Shutdown The database undergoes specific stages during startup and shutdown to ensure data consistency and integrity.

• Startup:

    1. NOMOUNT: Reads spfile/pfile, allocates SGA memory, starts background processes, creating an instance. Alert logs are created. Database creation or control file re-creation can occur here.

    2. MOUNT: Oracle opens and reads the control files, locating datafiles and redo log files. Operations like renaming datafiles or enabling/disabling archivelog mode can be done.

    3. OPEN: Datafiles and redo log files are opened. If inconsistent, SMON performs instance recovery, and the database becomes available.

• Shutdown: A checkpoint is performed (dirty blocks written, control/datafile headers updated for synchronization), datafiles and redo log files are closed. Then the database is dismounted and control files closed. Finally, the instance is deleted from memory.

By integrating these components and processes, Oracle databases aim to provide a robust and reliable platform for managing critical enterprise data, ensuring that data is consistently available, protected from loss, and secured against unauthorized access.