| Lesson 5 | Deciding on File Locations |
| Objective | Choose the location for the initial database files on your project database. |
Deciding on File Locations
When you create an empty database, there are files you need to place before Oracle can build them.
Oracle creates these for you at database creation time, but you have to tell Oracle where they go.
The essential set is:
These files form the foundation of an empty Oracle database. As you populate it with tables and data, additional datafiles get created to hold the actual user data. Where all of this actually lives depends on your chosen file system structure, ideally following Optimal Flexible Architecture (OFA), covered in the previous lesson.
- Control Files: store crucial metadata about the database, including the structure of datafiles, redo logs, temporary segments, and other information Oracle needs just to function. Multiple control files are typically maintained for redundancy, in case one is lost or corrupted.
- System and Sysaux Datafiles: hold the data dictionary objects and internal structures the database itself depends on, tables, users, schemas, privileges, and other core components. Since every Oracle database today is a multitenant container database, this isn't one flat file set: the CDB root gets its own SYSTEM/SYSAUX datafiles, and the seed pluggable database, PDB$SEED, which every new PDB gets cloned from, gets its own separate SYSTEM/SYSAUX datafiles too.
- Redo Log Files: record every change made during a transaction, letting Oracle recover the database to a consistent state after a crash or system failure. Multiple redo log files are typically created for performance and fault tolerance.
These files form the foundation of an empty Oracle database. As you populate it with tables and data, additional datafiles get created to hold the actual user data. Where all of this actually lives depends on your chosen file system structure, ideally following Optimal Flexible Architecture (OFA), covered in the previous lesson.
Control File
You need at least one control file, but Oracle recommends multiplexing multiple copies across
separate devices, or mirroring at the OS level, specifically so that losing one device doesn't cost
you your only copy. For purposes of this course, we'll assume you're experimenting on a single
mount point with no separate disks or ASM available. We'll still create three control files, purely
to make sure you understand the syntax, even though a real multiplexing benefit needs them on
genuinely separate storage. First, place the following lines in your
initCOIN.ora file:
control_files=('/u01/app/oracle/oradata/COIN/control01.ctl',
'/u01/app/oracle/oradata/COIN/control02.ctl',
'/u01/app/oracle/oradata/COIN/control03.ctl')C:\oracle\oradata\COIN\control01.ctl,
adjust to whatever's appropriate for your platform. Next, go ahead and create the
/u01/app/oracle/oradata/COIN directory.
Datafiles for the System and Sysaux Tablespaces
The datafiles for SYSTEM and SYSAUX get created when you issue the
CREATE DATABASE
command, which we won't do until the next module. For now, keep in mind they'll also land in
/u01/app/oracle/oradata/COIN, alongside a second set for PDB$SEED once the CDB is
created. OFA's naming recommendation is to associate a datafile's name with its tablespace, so we'll
use system01.dbf and sysaux01.dbf. The trailing number exists in case you
ever need to expand the tablespace with a second file later.
Redo Logs
Finally, the redo logs. They're heavily used and, in a real environment, should always go on separate
storage from your other database files. We don't have that luxury for our experimental database, so
they'll go into
You can read about where we might place the database files if we were building a production database instead.
/u01/app/oracle/oradata/COIN as well. Placing everything on one mount
point is fine for a small experimental database like the one you're building in this course, it's
even how many default, quick-start installs behave out of the box. In a production environment,
though, placing all your database files on one disk would be genuinely dangerous.
You can read about where we might place the database files if we were building a production database instead.
File Placement for a Production Database
In a production database environment, you should never place all your database files on one disk. There are two fundamental reasons for this:- Safety depends on having more than one copy of both the control files and the redo log files, on separate storage, so that if any one device is lost, the database can be recovered from the other copies. Placing everything on one disk means losing that one disk costs you everything.
- Performance is the second reason. A single disk can only sustain so much throughput, and it's rarely enough for any serious application. Spreading database files across disks and controllers distributes the I/O load and increases overall throughput.
This lesson has you place all the control files for the COIN database on one mount point, on the
assumption that most of you are working through this course on a single-disk training environment.
In a more realistic setup using separate physical disks, the configuration might look like this:
Most production environments today don't manage individual physical disks by hand at all, they run on Oracle ASM instead, and ASM has its own built-in answer to the same problem: failure groups. A Normal Redundancy disk group mirrors data two ways across a minimum of two failure groups, and is what Oracle recommends for most installations. A High Redundancy disk group mirrors three ways across a minimum of three failure groups, five recommended, specifically Oracle's recommendation for Exadata. A failure group does exactly what a separate physical disk did in the example above, isolates one copy of your data from a single point of failure, just abstracted behind ASM instead of something you track file by file.
For those of you working in Linux/UNIX environments generally, work closely with whoever manages your storage to understand how the underlying physical or virtual disks are actually laid out. Both plain file systems and ASM allow a lot of flexibility, and the relationship between a mount point (or an ASM disk group) and the physical storage underneath it isn't always obvious just from the path name.
control_files=('/u01/app/oracle/oradata/COIN/control01.ctl',
'/u02/app/oracle/oradata/COIN/control02.ctl',
'/u03/app/oracle/oradata/COIN/control03.ctl')Most production environments today don't manage individual physical disks by hand at all, they run on Oracle ASM instead, and ASM has its own built-in answer to the same problem: failure groups. A Normal Redundancy disk group mirrors data two ways across a minimum of two failure groups, and is what Oracle recommends for most installations. A High Redundancy disk group mirrors three ways across a minimum of three failure groups, five recommended, specifically Oracle's recommendation for Exadata. A failure group does exactly what a separate physical disk did in the example above, isolates one copy of your data from a single point of failure, just abstracted behind ASM instead of something you track file by file.
For those of you working in Linux/UNIX environments generally, work closely with whoever manages your storage to understand how the underlying physical or virtual disks are actually laid out. Both plain file systems and ASM allow a lot of flexibility, and the relationship between a mount point (or an ASM disk group) and the physical storage underneath it isn't always obvious just from the path name.
Optimal Flexible Architecture - Quiz
Click the quiz link below to take a short quiz and test your knowledge.
Optimal Flexible Architecture - Quiz
Optimal Flexible Architecture - Quiz