ORACLE DATABASE

ORACLE DATABASE ADMIN NOTES

DATABASE:

In Oracle, a database is a collection of data that is treated as a unit, organized in such a way that it can be easily stored, retrieved, and managed. It consists of both the physical files (data files, control files, redo log files) that store data on disk and the logical structures (schemas, tablespaces, tables, indexes, etc.) that manage and organize the data.

An Oracle database is managed by an Oracle Database Management System (DBMS), which provides comprehensive features for transaction management, performance optimization, data integrity, security, and high availability.

In simple terms, an Oracle database includes:

• Data stored in tablespaces and schemas.

• Processes that manage and manipulate data.

• Memory structures (e.g., SGA, PGA) to efficiently process data.

• Physical files that store the actual data on disk.

It supports both SQL (Structured Query Language) and PL/SQL (Procedural Language/SQL) for querying and manipulating data.

PHYSICAL FILES:

In an Oracle database, physical files refer to the actual files stored on the disk that hold all the data and important metadata for the database’s functioning. These files are essential for the database’s operation and are divided into several types, each with a specific role in storing and managing data. Here are the key types of physical files in an Oracle database:

1. Data Files:

• Purpose: These files store the actual data in the database, including user and system data. They are linked to tablespaces, which logically organize data in the database.

• Content: Tables, indexes, views, and other database objects are physically stored in data files.

• File Extension: Typically .dbf.

• Characteristics: Each tablespace consists of one or more data files, and they can grow dynamically if auto extend is enabled.

Example: If a database has a "users" tablespace, the actual data related to users is stored in data files associated with that tablespace.

2. Control Files:

• Purpose: Control files contain critical metadata about the database. They store information about the structure of the database, such as the names and locations of data files, redo log files, the database name, and the timestamp of database creation.

• Content: They track changes to the database and play an essential role in database startup and recovery.

• File Extension: Typically .ctl.

• Characteristics: Every Oracle database has at least one control file, but it's recommended to have multiple copies (multiplexing) for redundancy and recovery purposes.

Example: Control files maintain records about the current state of the database and are critical for recovering the database after a crash.

3. Redo Log Files:

• Purpose: These files record all changes made to the database. They are used for recovery purposes in case of a system failure or crash.

• Content: Redo log files store the "before" and "after" images of data changes to ensure no data is lost during recovery.

• File Extension: Typically .log.

• Characteristics: Redo logs are generated cyclically, meaning older logs are overwritten once they are no longer needed, unless archived in archived redo logs mode for backup and recovery purposes.

Example: If a transaction updates a table, the change is first recorded in the redo log before being applied to the actual data file.

4. Temporary Files:

• Purpose: Temporary files are used to store transient data, such as intermediate results for large queries and sorting operations. They are also used in operations like sorting and joining large datasets or for temporary tables.

• Content: They do not hold permanent data and are reinitialized after the database shuts down.

• File Extension: Typically .tmp or .dbf (depending on the platform and configuration).

• Characteristics: These files are linked to temporary tablespaces.

Example: During a large query operation, such as sorting millions of rows, temporary data is stored in these files.

5. Archived Redo Log Files:

• Purpose: These are copies of the redo log files that are archived after the redo log buffer is filled and written to disk. They are crucial for point-in-time recovery and maintaining data consistency in backup strategies.

• Content: Archived redo logs contain a complete history of all changes made to the database.

• File Extension: Typically .arc or .log.

• Characteristics: In ARCHIVELOG mode, redo log files are saved as archived redo logs after they are filled, providing a means to restore data up to any point in time.

Example: Archived redo logs are critical in scenarios such as recovering data after a media failure or rolling forward a backup to a particular time.

6. Parameter Files (PFILE) and Server Parameter Files (SPFILE):

• Purpose: These files store initialization parameters for the Oracle instance. They define the configuration of the instance, such as memory allocations and process parameters.

• Content: These files contain settings like the size of memory structures (SGA, PGA), the number of background processes, and other key parameters for the instance.

• File Extensions: PFILE: .ora, SPFILE: .ora.

• Characteristics: PFILE is a static text file, while SPFILE is a binary file that can be modified dynamically by the database.

Example: If you want to adjust the amount of memory Oracle allocates (e.g., to the SGA), you would change the parameters in the PFILE or SPFILE.

7. Password Files (Optional):

• Purpose: These files store passwords for users with administrative privileges (such as SYSDBA).

• File Extension: Typically .pwd.

• Characteristics: This file is required for remote database startup, shutdown, or certain maintenance operations.

Summary of Oracle Physical Files:

File Type	Purpose	File Extension	Example Use
Data Files	Store actual data for tables, indexes, etc.	.dbf	Storing user data in tables.
Control Files	Store metadata about the database structure and status.	.ctl	Tracking locations of data files and redo logs.
Redo Log Files	Record all database changes for recovery.	.log	Ensuring no data is lost during recovery.
Temporary Files	Hold transient data for sorting and large operations.	.tmp, .dbf	Supporting sorting operations during query execution.
Archived Redo Log Files	Maintain history of all database changes for recovery purposes.	.arc, .log	Point-in-time recovery in case of media failure.
Parameter Files	Store configuration parameters for database instance management.	.ora	Setting memory allocations and instance configuration.
Password Files	Store passwords for privileged administrative users.	.pwd	Enabling remote database administration (e.g., for SYSDBA users).

These physical files are crucial for managing and running an Oracle database. The Oracle DBMS uses these files to store, retrieve, and manipulate data, ensuring efficient operation, backup, and recovery.

LOGICAL FILES:

In an Oracle database, logical structures are the higher-level, user-facing components that organize how data is stored, accessed, and managed within the database. While physical files deal with the actual storage of data on disk, logical structures manage how that data is logically arranged and manipulated. These structures help users interact with data in a structured and efficient way.

Key Logical Structures in Oracle:

1. Tablespaces:

   • Definition: A tablespace is a logical storage unit that defines where Oracle stores the data within the database. It is composed of one or more data files (physical files).

   • Purpose: Tablespaces help manage database storage by grouping related logical storage structures (e.g., tables and indexes) together.

   • Types: SYSTEM, SYSAUX, USER, TEMP, UNDO.

   • Example: The "users" tablespace stores user-related data such as tables and indexes. The "temp" tablespace stores temporary data used for sorting.

2. Segments:

   • Definition: A segment is a set of extents allocated for storing a specific type of object, like a table, index, or undo data.

   • Purpose: Segments are the logical storage units within a tablespace.

   • Types: Table segments, Index segments, Undo segments, and Temporary segments.

   • Example: A table segment is created when a user creates a table, and it stores all the data inserted into that table.

3. Extents:

   • Definition: An extent is a contiguous block of space within a tablespace, allocated for storing data.

   • Purpose: Extents are made up of multiple Oracle blocks and are the units by which segments grow. When a segment needs more space, Oracle allocates an additional extent.

   • Example: As more rows are inserted into a table, Oracle allocates additional extents to store the new data.

4. Oracle Blocks:

   • Definition: Also called "database blocks" or "logical blocks," Oracle blocks are the smallest unit of storage in the Oracle database.

   • Purpose: They are the fundamental building blocks of tablespaces and store the actual data (e.g., rows from tables).

   • Size: Typically between 2KB to 32KB, depending on database configuration.

   • Example: When you insert data into a table, Oracle stores the data in Oracle blocks within a segment.

5. Schema Objects:

   • Definition: Schema objects are logical structures that belong to a database schema and include objects such as tables, views, indexes, sequences, and procedures.

   • Purpose: They define how data is logically organized, accessed, and manipulated by users and applications.

   • Types of Schema Objects:

     • Tables: Store actual data in rows and columns.

     • Indexes: Improve the performance of data retrieval operations.

     • Views: Virtual tables created by querying one or more tables.

     • Sequences: Generate unique numeric values, often used for primary keys.

     • Procedures and Functions: PL/SQL code blocks stored in the database.

     • Synonyms: Aliases for database objects.

   • Example: A table is a schema object that stores customer information, while an index might be used to quickly search for a customer by ID.

6. Partitions:

   • Definition: Partitions divide large tables or indexes into smaller, more manageable pieces, each stored separately within a tablespace.

   • Purpose: They improve query performance and manageability by distributing data across partitions based on ranges, lists, or hash keys.

   • Example: A sales table might be partitioned by month, allowing queries to quickly access sales data for a specific month without scanning the entire table.

7. Data Dictionary:

   • Definition: The data dictionary is a collection of metadata that Oracle uses to describe the structure of the database.

   • Purpose: It stores information about schema objects, user privileges, database constraints, and more.

   • Components:

     • USER_ views: Provide information about the user's objects.

     • ALL_ views: Provide information about all objects accessible by the user.

     • DBA_ views: Provide information about all objects in the database.

   • Example: When you query USER_TABLES, you get a list of tables owned by the current user.

8. Clusters:

   • Definition: Clusters are a group of one or more tables that share the same data blocks, stored together based on a common key.

   • Purpose: Clustering reduces the I/O required to retrieve related data from multiple tables.

   • Example: A cluster could store employee and department tables where each department is stored with its corresponding employees.

9. Indexes:

   • Definition: Indexes are schema objects that allow for faster data retrieval by creating a data structure that references the actual rows in the table.

   • Purpose: They improve query performance, especially for queries with WHERE clauses and joins.

   • Types:

     • B-tree indexes (most common type).

     • Bitmap indexes (useful for columns with low cardinality).

     • Function-based indexes (for indexes based on expressions).

   • Example: An index on a customer ID column allows Oracle to quickly locate rows for a specific customer without scanning the entire table.

10. Synonyms:

    • Definition: A synonym is an alias for a schema object, such as a table or view.

    • Purpose: Synonyms simplify access to objects and allow users to refer to them without needing to specify the schema.

    • Example: If a user needs frequent access to a table in another schema, a synonym can be created so that the user can access the table using a shorter name.

Summary of Logical Structures:

Logical Structure	Description	Purpose	Example
Tablespaces	Logical storage unit that groups related objects.	Organizes data storage across physical files.	SYSTEM, SYSAUX, USERS tablespaces.
Segments	Set of extents used by specific objects like tables and indexes.	Allocates space for database objects.	Table segments, index segments.
Extents	Contiguous blocks of storage within a tablespace.	Manage segment growth by allocating space in extents.	Additional extents allocated when a table grows.
Oracle Blocks	Smallest logical unit of storage, made up of several OS blocks.	Store actual data in rows or indexes.	Oracle block size typically ranges from 2KB to 32KB.
Schema Objects	Logical structures such as tables, indexes, views, and sequences.	Define and manipulate the data stored in the database.	Customer table, Employee index, Order view, Primary key sequence.
Partitions	Subdivisions of large tables or indexes.	Improve performance and manageability for large datasets.	Partitioning a sales table by month.
Data Dictionary	Metadata repository containing information about database objects.	Stores and manages metadata for the database.	Querying USER_TABLES for a list of tables.
Clusters	Group of related tables stored together in the same data blocks.	Optimize I/O performance for related data retrieval.	Clustering employee and department tables on the department ID.
Indexes	Logical structures to speed up data retrieval.	Improve performance of queries and joins.	Index on a customer's ID to quickly retrieve records.
Synonyms	Aliases for schema objects.	Simplify access to objects by hiding schema names.	Synonym for a table in another schema, allowing shorter names in queries.

These logical structures work together to provide a flexible and efficient way to organize, store, and retrieve data within an Oracle database. They ensure data consistency, improve performance, and optimize storage management.