Lesson 8	Oracle file structures
Objective	Review database physical files.

Database Physical Files after installing Oracle 12c

When you install Oracle 12c on a Windows machine, the following key files and directories are typically created:

Oracle Home Directory (ORACLE_HOME)
- This is the main directory where Oracle software is installed.
- Contains subdirectories like bin, network, rdbms, admin, and others.
- Example path: C:\oracle\product\12.1.0\dbhome_1
Oracle Base Directory (ORACLE_BASE)
- The base directory for Oracle installations, which holds the Oracle Home directory and other files.
- Example path: C:\oracle
Database Files
- Datafiles: Physical storage files for the database objects (tables, indexes, etc.).
  - Typically found in directories under ORACLE_BASE\oradata\.
- Control Files: Store metadata about the database structure, located in ORACLE_BASE\oradata\ or as specified in the initialization parameter file.
- Redo Log Files: Track all changes to the database, typically stored alongside datafiles or in a separate log directory.
- Archive Log Files (if archiving is enabled): Stored in the location specified by the LOG_ARCHIVE_DEST parameter.
Configuration Files
- Initialization Parameter File (init.ora or spfile.ora): Contains configuration parameters for the instance.
  - Location: ORACLE_HOME\database or ORACLE_BASE\admin\\pfile.
- Listener Configuration File (listener.ora): Configures the Oracle Net Listener.
  - Location: ORACLE_HOME\network\admin.
- TNS Names File (tnsnames.ora): Holds network service names.
  - Location: ORACLE_HOME\network\admin.
Admin Files
- Alert Logs: Logs significant database events and errors.
  - Location: ORACLE_BASE\diag\rdbms\\\trace.
- Trace Files: Detailed diagnostic files generated for errors and tracing.
  - Location: ORACLE_BASE\diag\rdbms\\\trace.
- Audit Files: Contains records of database operations for security auditing.
  - Location: ORACLE_BASE\admin\\adump.
Service-Related Files
- Oracle installs and registers several Windows services, such as:
  - OracleService<DB_NAME>: The main Oracle database service.
  - Oracle<ListenerName>: The Oracle Listener service.
  - OracleJobScheduler<DB_NAME>: The scheduler service for jobs in Oracle.
Registry Entries
- Oracle creates entries in the Windows Registry for configuration and environment variables.
- Registry paths include:
  - HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
  - HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleService<DB_NAME>
Log and Inventory Files
- Oracle Universal Installer (OUI) Inventory: Tracks installed Oracle products.
  - Example location: C:\Program Files\Oracle\Inventory.
- Installation Logs: Logs for the installation process, found in the temporary directory used during installation (e.g., C:\Program Files\Oracle\Inventory\logs).

These files and directories are essential for the proper functioning of the Oracle 12c database on a Windows machine. They enable configuration, operation, and management of the Oracle instance and facilitate networking, security, and diagnostic activities.
If you choose the default installation, you will end up with a directory structure called the Oracle Home that consists of subdirectories and files.
The following image shows a partial listing of my installation.

Partial listing of Installation

Oracle file structures — Oracle file Structures

My Oracle home is C:\ORACLE8 and the database files are located in the directory C:\ORACLE8\DATABASE. The following diagram shows the files found in a typical Oracle installation.

Contains parameters that specify the database block size — **Text in the Image:**

Initialization File

Control Files

Archive Log Destination

Tablespace

Data Files

Redo Log Group

Redo Log Member

**Description of Relevant Features:**

**Initialization File:** This file contains configuration settings for the database instance. It influences how the instance starts and operates.

**Control Files:** Control files are central to Oracle databases, maintaining metadata about the database structure, such as data files, log files, and checkpoint information. They are critical for database recovery and are referenced by various database processes.

**Archive Log Destination:** The destination where archive logs are stored. Archive logs are copies of redo logs created when the database is running in ARCHIVELOG mode. They are essential for point-in-time recovery, allowing for reconstruction of transactions.

**Tablespace:** Tablespaces are logical storage units that group related data files, which physically store the database’s data. Multiple tablespaces can exist within a single database, and each tablespace can have multiple data files.

**Data Files:** These files hold the actual data stored in the database. They are assigned to tablespaces and are essential for storing tables, indexes, and other database objects.

**Redo Log Group:** Redo log groups consist of one or more redo log members (copies of redo logs) that store all changes made to the database. They are critical for database recovery by replaying changes.

**Redo Log Member:** Each redo log group contains one or more redo log members, providing redundancy. If one member fails, another can be used to maintain the integrity of the redo logs.

The image uses arrows and grouping to show how each component interacts with others, emphasizing the organization of files into tablespaces and redo log groups for efficient data storage and recovery capabilities.

Initialization file: Contains parameters that specify the database block size, the amount of memory to use for the Shared Global Area (SGA), and that control other aspects of how the database instance operates.
Control files: Keeps track of all the files that make up a database.
Archive log destination: This is the location to which Oracle copies redo log files when they are filled.
Tablespace: Tablespaces are logical storage structures that contain table and index data. Oracle allows you to map a tablespace onto one or more physical files.
Datafiles: These are the files that hold the data for tables and indexes. They are the reason all the other files exist.
Redo log group: A group of one or more redo log files that Oracle treats as one. Oracle writes the same information to each file in a group.
Redo log member: Stores a log of all changes made to the database (the redo log), that is used in the event that the database needs to be recovered.
Redo log member: Stores a log of all changes made to the database (the redo log), that is used in the event that the database needs to be recovered.

There are two V$ views that you can use to query basic information about your database and to find files on disk. Examples of V$DATABASE and V$DATAFILE are listed below.

These are simplistic examples from a small database. The importance of these V$ views will become more relevant when we start talking about our backup and recovery options later in this series of courses. If you have the opportunity, I would recommend that you run these views against your own production databases. this will provide you with a better understanding of how your databases are structured. The next lesson wraps up this module.