Managing Database Storage Structures

When looking at Oracle Architecture, we took a quick look at the database storage structures. In this section, we’re going to take a deep dive at these structures. In managing database storage structures, we need to understand that these structures are both physical (visible to the operating system) and logical (invisible to the operating system). We are going to begin with the physical structures.

Physical Storage Structures

Control File

This is a physical file that stores important information about the database for instance name of the database, name and location of data files, name and location of redo log files, timestamp and language of the database, checkpoint information, etc.

The information in this file is so important and without it, the database cannot start. Because of its importance, the control file is always multiplexed (making copies out of it). In case one of the copies is damaged, we can use another. This file is always updated by the CKPT process automatically. To view the name and location of control files in your database, we use the v$controlfile view.

SELECT name FROM v$controlfile;

Data File

This is a physical file that stores data inserted into tables permanently. Data files are stored in a tablespace (to be looked at in details soon). The data files are binary in nature and the DBWn process writes data to these files. The data dictionary view dba_data_files is used to show the data files associated with each tablespace.

SELECT tablespace_name, file_name FROM dba_data_files;

Redo Log FIle

This physical file is charged with storing the redo log buffer content. Because of the nature of its content, it is multiplexed. This file is stored in a redo log group and each group must have at least one redo-log file. A database must have at least two redo-log groups. The LGWR process is charged with writing the log buffer content to the active log file.

The LGWR writes the buffer content to the log file in a circular fashion i.e each redo log group has redo-log file members. Each log file is written on by the LGWR process at a time. When it gets full, the LGWR writes to another file. When the group is full, LGWR writes to another group. This continues until all log groups are full. When they’re all full, content of the first log files is transferred to the archived redo-log files in the flash back recovery area (more on this later). The LGWR then writes on the cleaned files.

SELECT group#, member FROM v$logfile;

Parameter File

This file stores information about the initialisation parameters (settings) used when the instance is starting. Oracle uses this file’s information to determine how to start the instance. This file can be a plain file i.e. P file or a binary file ie. SP file. You can use either files to configure both instance and database options. SP file is the most recommended file for database startup due to its dynamic nature.

There are a number of settings (parameters) in this file that can be re-configured e.g. memory_target, control_files, db_block_size, db_name, instance_number, processes, sga_target, etc. To. have a look at all the parameters in this file, we use the code below:

SHOW parameters;

Alert Log File

This is an XML file also known as alert.log charged with storing a chronological log of messages and faults written out by the database. Database startup, shutdown, log switches, space issues, and other alerts are common in this file. This file should be checked on a regular basis for any unusual messages or corruptions. When the old alert log file is destroyed, Oracle will immediately create a new one. To view this file, we can use the code below.

SELECT value from v$diag_info WHERE name='Diag Trace';

Logical Storage Structures

Tablespace.

A tablespace is a logical storage structure in the database charged with storing data files. Each tablespace must at least have one data file. A data file can only belong to one tablespace but multiple data files can belong to one tablespace. Tablespaces help greatly in organising data files for example, all data files storing employee records could be put in the employees_tablespace. This simplifies the work of DBAs.

Oracle comes with default tablespaces to include SYSTEM tablespace (stores data dictionary—to look at this soon), SYSAUX tablespace (it works with the SYSTEM tablespace to reduce the overload on the SYSTEM tablespace for example, tracking the database objects taht work with SYSTEM), TEMP tablespace (stores query results after sorting operations), USERS tablespace (stores user objects and data), and UNDO tablespace (stores undo information).

Apart from the default tablespaces, we can as well create our own tablespaces using the CREATE TABLESPACE statement. For example, we can create a tablespace called customers that will be used to store customers data. We can as well create one data file along with it called customer_details. To do that, we use the code below.

CREATE TABLESPACE customers datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\customer_details.DBF' size 20M;

Segments. This is a set of extents which in turn is made of blocks.

Extents. This is logical storage structure composed of blocks.

Blocks. This is the smallest storage unit in Oracle database. The size of the block is based on db_block_size parameter determined during database creation.

An illustration showing how the storage structures relate with the instance.

Summary

1. Database storage is divided into two i.e. physical storage and logical storage.

2. Physical storage include data file, redo log file, control file, parameter files, etc.

3. Logical storage include the tablespace, segments, extents and blocks.