DB Creation   «Prev  Next»

Lesson 5Deciding on File Locations
ObjectiveChoose the location for the initial database files on your project database.

Deciding on File Locations

When you create an empty database, there are three types of files that you need to place. Oracle will create these when you create the database, but you need to tell Oracle where to put them.
When you create an empty database in Oracle, these are the three essential files that Oracle will create:
  1. Control Files: These files store crucial metadata about the database, including the structure of datafiles, redo logs, temporary segments, and other critical information for the database to function. Typically, multiple control files are created for redundancy in case one gets corrupted.
  2. The System Tablespace File: This file represents the system tablespace, which holds all the data dictionary objects and internal structures used by the database itself. It stores information about tables, users, schemas, privileges, and other core database components.
  3. Redo Log Files: These files are used for transaction logging. They record all the changes made to the database during transactions. This allows Oracle to recover the database to a consistent state in case of a system failure or crash. Multiple redo log files are typically created for improved performance and fault tolerance.

These three files form the foundation of an empty Oracle database. As you populate the database with tables and data, additional datafiles will be created to store the actual user data. The specific locations of these files will depend on your chosen file system structure, potentially following the recommendations of Optimal Flexible Architecture (OFA).

Control file

You need to have at least one control file. Oracle recommends that you maintain at least three control files, kept on separate disks in case one is lost. For purposes of the course, I am going to assume that you are experimenting on a Windows workstation, and that you only have one disk drive. We will still create three control files, just to be sure that you understand the syntax. First, place the following lines in the initCOIN.ora file.

If you are running under UNIX, change the directory path to one that is appropriate for your system. Next, go ahead and create the c:\oracle\oradata\COIN directory.

Datafile for System Tablespace

A datafile for the system tablespace is created when you issue the CREATE DATABASE command. We will not do that until the next module. For now, just keep in mind that the datafile will also go in the c:\oracle\oradata\COIN directory. The OFA recommendation for datafile names is to associate them with the tablespace, so we will use system01.dbf for the filename. The number is there in case we ever need to expand the tablespace by adding a second file.

Redo logs

Finally we come to the redo logs. Redo logs are heavily used, and should always go on separate disks, away from any other database files. We don't have that luxury for our experimental database, so they too will go into the c:\oracle\oradata\COIN directory. Placing all the database files on one disk is fine if all you need is a small database for experimentation, such as the one you will be building in this course. The default install of Oracle under Windows puts all the database files on one disk. However, in a production environment placing all the database files on one disk would be very dangerous.
You can read about where we might place the database files if we were creating a production database.

File Placement for a Producton Database

In a production database environment, you should never place all of your database files on one disk. There are two fundamental reasons for this:
  1. The safety of an Oracle database depends on having more than one copy of both the control files and the redo log files. These are placed on separate disks, so that if any one disk is lost, the database can be recovered using the other copies. Placing all the files on one disk ensures that losing the disk causes you to lose everything
  2. The second reason to use multiple disks involves performance. A single disk can only sustain so much throughput, and it probably won't be enough for any serious application. Spreading your database files around between disks and controllers distributes the I/O load and increases the throughput.

This lesson has you place all the control files for the COIN database on one disk. I did that because it's reasonable to assume that most of you are working through this course using a Windows workstation with only one disk drive. In a more realistic work situation, the configuration for the control files might look like this:
Control_files: c:\oracle\oradata\COIN\control01.ctl,

Notice that all the files are on separate disks. In this situation, if any one disk is lost, you still have two copies of your control files.
For those of you working in UNIX environments, it is important to work closely with your system administrator to understand how the physical disk is set up on your system. UNIX allows for a great deal of flexibility in how storage is used, and the relationship of a UNIX mount point to the underlying physical drives is not always obvious.

Optimal Flexible -Architecture - Quiz

Click the Quiz ilnk below to take a short quiz and test your knowledge.
Optimal Flexible Architecture - Quiz

SEMrush Software