Database Physical Files in Oracle AI Database

Installing Oracle AI Database on a Windows machine creates a predictable set of files and directories, all organized under Optimal Flexible Architecture (OFA) conventions rather than a single flat folder. Knowing where each piece lives matters for more than housekeeping: every backup and recovery decision you make later in this course, from an RMAN backup job to a Data Guard standby to a flashback operation, depends on knowing exactly which physical files exist and where they sit.

A typical installation produces the following categories of files and directories:

1. Oracle Home Directory (ORACLE_HOME)
   • The directory where the Oracle software itself is installed, containing subdirectories such as bin, network, rdbms, and admin.
   • OFA places this under Oracle Base, typically something like ORACLE_BASE\product\<release>\dbhome_1.
2. Oracle Base Directory (ORACLE_BASE)
   • The parent directory for Oracle installations, holding the Oracle Home directory plus the database's administration and data directories.
   • Example path: C:\app\<username>.
3. Database Files
   • Data files: physical storage for the database's objects (tables, indexes, and so on), typically under ORACLE_BASE\oradata\<DB_NAME>.
   • Control files: store metadata about the database structure, also under ORACLE_BASE\oradata\<DB_NAME> or wherever the CONTROL_FILES parameter points.
   • Redo log files: track every change made to the database, usually stored alongside the data files or in their own log directory.
   • Archived redo log files (only if the database runs in ARCHIVELOG mode): stored at the location the LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_n parameters specify, or in the Fast Recovery Area if you use one instead.
4. Configuration Files
   • Initialization parameter file (PFILE/init.ora or SPFILE): holds the instance's configuration parameters, typically under ORACLE_HOME\database or ORACLE_BASE\admin\<DB_NAME>\pfile. PFILE is still fully supported and not deprecated, it's simply less convenient for ongoing administration than SPFILE, which lets persistent ALTER SYSTEM changes stick without hand-editing a text file. See Additional Database Parameters for more.
   • Listener configuration file (listener.ora) and TNS names file (tnsnames.ora): both live in ORACLE_HOME\network\admin. Whatever naming scheme you pick for the service names inside tnsnames.ora, keep it predictable and consistent across every client that needs to reach the database, and confirm each entry resolves correctly with TNSPING before you rely on it. See Creating and Maintaining the tnsnames.ora File for the full naming and distribution strategy.
5. Diagnostic Files
   • Alert log and trace files: written under the Automatic Diagnostic Repository, whose location is controlled by the DIAGNOSTIC_DEST parameter, typically ORACLE_BASE\diag\rdbms\<DB_NAME>\<SID>\trace.
   • Audit files: records of database operations kept for security auditing, typically under ORACLE_BASE\admin\<DB_NAME>\adump.
6. Service-Related Files
   • The installer registers Windows services such as OracleService<SID> (the database service itself) and a listener service, so the instance can start automatically with Windows rather than requiring a manual login session.
7. Registry Entries
   • Configuration and environment variables live under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, with a corresponding service key under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services.

Together these files and directories are what let Windows configure, start, and manage an Oracle instance, and what give administrators a consistent place to look for networking, security, and diagnostic information.

The following diagram shows the files found in a typical Oracle installation.

1. Initialization file: Contains parameters that specify the database block size, the amount of memory to use for the System Global Area (SGA), and other settings that control how the database instance operates.
2. Control files: Keep track of every file that makes up the database, plus checkpoint information used for recovery.
3. Archive log destination: The location where Oracle copies redo log files once they fill, when the database runs in ARCHIVELOG mode. Essential for point-in-time recovery.
4. Tablespace: A logical storage structure that contains table and index data. Oracle maps a tablespace onto one or more physical data files.
5. Data files: The files that physically hold the data for tables and indexes. They're the reason all the other files exist.
6. Redo log group: A group of one or more redo log files that Oracle treats as a single unit, writing the same information to every member.
7. Redo log member: An individual file within a redo log group. Multiple members give the group redundancy: if one member is damaged, the others still protect the redo stream.

Not every file discussed above is strictly required for the database to exist and open. Oracle AI Database distinguishes what the instance needs to start, what the database needs to mount, and what it needs to open:

• To mount: at least one control file. The control file is the binary inventory of data files, online redo logs, and checkpoint information; the instance cannot mount without it. If you don't explicitly set the CONTROL_FILES parameter, Oracle creates a control file in the same directory as the parameter file using a default, OS-dependent name; Oracle strongly recommends at least two control files stored on separate physical disks, not just one file that happens to be multiplexed on paper. In a multitenant database, the CDB owns the control file; individual PDBs don't have their own.
• To open: at least one data file, and at least two online redo log files (two groups, at minimum one member each). The two-group minimum guarantees that one file is always available for writing while the other is being archived or cleared; multiplexed members within each group are strongly recommended for protection against media failure, but are not themselves a technical requirement to open the database.
• To start the instance (NOMOUNT): a parameter file, PFILE or SPFILE. It isn't a "database file" in the strict sense used above, but the instance can't start without one, since it tells Oracle where to find the control files and how to size memory and processes.

Everything else is either optional or exists to support recovery rather than day-to-day operation: archived redo logs (only relevant in ARCHIVELOG mode), RMAN backup pieces and image copies, the password file (needed only for remote SYSDBA/SYSOPER connections), and temp files, which are needed for normal sort and temporary-segment activity but can always be recreated rather than backed up. None of this is new in 26ai; multitenant is simply the default architecture now, so the control file and online redo belong to the CDB as a whole, while each PDB contributes its own data files.

Most of the files covered so far make up what Oracle calls the database area: the data files, control files, and online redo logs that the running instance needs. Backup and recovery material is a separate concern, and Oracle gives you a dedicated place for it, the Fast Recovery Area, set with two initialization parameters: DB_RECOVERY_FILE_DEST, which points to a directory, file system, or Oracle ASM disk group, and DB_RECOVERY_FILE_DEST_SIZE, which caps how much space it can consume. You can't enable a Fast Recovery Area at the same time as the older LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters; use LOG_ARCHIVE_DEST_n instead if you need both an archive destination and a Fast Recovery Area. Oracle recommends using one, since it centralizes backup pieces, archived redo logs, and flashback logs, and takes over the manual work of tracking free space among them.

Flashback logs are worth calling out specifically for this course, since 26ai changed something here: in earlier releases, flashback database logs could only live inside the Fast Recovery Area. You can now point them at a separate location instead, which matters if your workload is write-heavy enough that flashback logging slows the database down when the Fast Recovery Area's underlying storage isn't fast enough. Moving flashback logs to faster disks on their own also means one less thing competing for space inside the Fast Recovery Area itself.

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 shown below.

SQL> SELECT name, dbid, log_mode
  2  FROM v$database;

SQL> SELECT file#, status, name
  2  FROM v$datafile;

These are simplistic examples from a small database, and V$DATABASE and V$DATAFILE are far from the only views worth knowing. V$CONTROLFILE lists your control file locations, and V$LOG and V$LOGFILE show the status and member files of every redo log group, both useful companions once you're troubleshooting rather than just reviewing. The importance of all of these becomes more relevant once we start talking about backup and recovery options later in this series of courses. If you have the opportunity, run these views against your own production databases, it's a good way to build a better feel for how your databases are actually structured. The next lesson wraps up this module.