RelationalDBDesign RelationalDBDesign


DB Creation   «Prev  Next»
Lesson 7Deciding on File Sizes
ObjectiveSize the initial Database Files

Deciding on File Sizes

redo LogFile Sizes
The only files that we have to size right now are the SYSTEM tablespace datafile and the redo log files. That's because the CREATE DATABASE statement results in those files being created. We'll size the other files later as we create them.

Sizing system

The minimum size of the SYSTEM tablespace has risen dramatically since the days of Oracle7. The default size used for the starter database created when you first install Oracle is 140 megabytes (MB). Some options, such as the replication option, require an even larger SYSTEM tablespace. For our purposes, 140MBshould be fine. Oracle doesn't provide any specific guidance on sizing the SYSTEM tablespace. I usually start with the size used by the starter database for whatever release of Oracle that I'm working with, and increase it later if I need to.

Sizing redo log files

After sizing the tablespace, sizing the redo log files is the next task. Oracle does not provide a lot of specific guidance in this regard either. Sizing these files is as much a performance issue as anything else. Here are some considerations to keep in mind:
  1. checkpoint occurs at every redo log switch. Smaller redo log files translate into more frequent checkpointing.
  2. Redo log files are not archived until they are filled. Larger redo log files mean a longer interval between the time a redo log entry is first written and the time it is archived.
  3. Redo log files are used in a circular fashion. You want enough files so that the system never has to wait for a file to be archived before it can be reused. You also want checkpoints to be completed before Oracle needs to reuse a file.

If your are running a database in archive log mode, it's important to take into account the amount of time needed for the archiver to copy them. If you have a small number of small redo log files, a sudden burst of activity can cause Oracle to cycle through them faster than they can be copied. Database activity will come to halt while the archiver process catches up. There are two ways to combat this: create larger redo log files, or create more redo log groups.
When I create a new database, I often take an initial guess at the redo log file size, and then refine that after the database begins to be used. The starter database is created with two 1MB redo log groups. That is much bigger than we need or want for the course database.
Task: We want to be able to generate some archive log files down the road, without first having to change 2MB of data, so let's size our redo logs at 10K each.

Oracle Network Files Directory

Some Oracle utilities use the value TNS_ADMIN to locate network configuration files. This directory is defined as ORACLE_HOME/network/admin. It typically contains the tnsnames.ora and listener.ora Oracle Net files.

Tip: Sometimes DBAs will set TNS_ADMIN to point at one central directory location (such as /etc or /var/opt/oracle). This allows them to maintain one set of Oracle network files (instead of one for each ORACLE_HOME). This approach also has the advantage of not requiring the copying or moving of files when a database upgrade occurs, potentially changing the location of ORACLE_HOME.