In this second part of a five-part series on Oracle Database XE administration, you'll learn about physical database structures. This article is excerpted from chapter 28 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
From the perspective of building queries, developing applications, and running reports, regular users, managers, and developers don’t need to know much about the underlying physical structure of the database on disk. However, even a part-time DBA does need to understand these database structures. For example, you need to know where the database’s datafiles reside on disk and how to best optimize their placement when performance becomes an issue.
The physical structure of the Oracle database consists of datafiles, redo log files, and control files. On a day-to-day basis, the DBA will deal most often with the datafiles, since this is where all of the user and system objects, such as tables and indexes, are stored. Figure 28-2 shows the physical structure and its relationship to the Oracle memory and logical storage structures.
The datafiles in a database contain all of the database data that the users of the database add, delete, update, and retrieve. A single datafile is an operating system file on a server’s disk drive. This disk may be local to the server or a drive on a shared storage array. Each datafile belongs to only one tablespace; a tablespace can have many datafiles associated with it.
There are seven physical datafiles in the database in Figure 28-2. There are two for theSYSTEMtablespace; one for theSYSAUXtablespace; one for theTEMPtablespace; two for theUSERStablespace; and one for theUNDOtablespace.
Redo Log Files
The Oracle mechanism to recover from an instance failure or a media failure uses redo log files. When users or system processes make changes to the database, such as updates to data or creating or dropping database objects, the changes are recorded to the redo log files first. A database has at least two redo log files. Oracle best practices recommend that you store multiple copies of the redo log files on different disks; Oracle automatically keeps the multiple copies in sync. If the instance fails, any unrecorded changes to database blocks not yet written to the datafiles are retrieved from the redo log files and written to the datafiles when the instance starts again; this process is known as instance recovery. By default, Oracle does not mirror the redo log files on different disks; we’ll show you how to do that in Chapter 40. For development, the default redo log configuration is sufficient.
The control file maintains the metadata for the physical structure of the entire database. It stores the name of the database, the names and locations of the tablespaces in the database, the locations of the redo log files, information about the last backup of each tablespace in the database, and much more. Because of the importance of this file, Oracle best practices recommend that you keep a copy of the control file on at least three different physical disks. As with the redo log files, Oracle keeps all copies of the control file in sync automatically. We’ll show you how to move the control files to different disks in Chapter 40.
The control file and redo log file contents do not map directly to any database objects, but their contents and status are available to the DBA by accessing virtual tables called data dictionary views, which are owned by theSYS schema. We cover data dictionary views and many other types of views in Chapter 34.