Deciding on File Sizes

I know that it sounds unscientific to say that I guess at redo log file sizes, but I'm not making random guesses, pulling values out of thin air. When I create a new database, I look at other, working, databases where the size and transaction rate is about the same. I use those working databases as a pattern when I create the new one. Oracle's starter database is created with only two redo log groups. I've never found that to be adequate, even for lightly used databases (< i.e., more than 10 users) databases. At a minimum, I use at least four redo log groups with a file size of 1MB each. If I see

							 
checkpoint not complete

messages in my database alert log, I will add even more groups. For larger databases, with more users, I will increase the redo log file size as well as add more groups.

Try to size the online redo logs so that they switch anywhere from two to six times per hour. The V$LOG_HISTORY contains a history of how frequently the online redo logs have switched. Execute this query to view the number of log switches per hour:

select count(*)
,to_char(first_time,'YYYY:MM:DD:HH24')
from v$log_history
group by to_char(first_time,'YYYY:MM:DD:HH24')
order by 2;

Here is a snippet of the output:

COUNT(*) TO_CHAR(FIRST
---------- -------------
1 2012:10:23:23
3 2012:10:24:03
28 2012:10:24:04
23 2012:10:24:05
68 2012:10:24:06
84 2012:10:24:07
15 2012:10:24:08

From the previous output, you can see that a great deal of log switch activity occurred from approximately 4:00 am to 7:00 am. This could be due to a nightly batch job or users in different time zones updating data. For this database the size of the online redo logs should be increased. You should try to size the online redo logs to accommodate peak transaction loads on the database. The V$LOG_HISTORY derives its data from the control file. Each time there is a log switch, an entry is recorded in this view that details information such as the time of the switch and the system change number (SCN). As stated, a general rule of thumb is that you should size your online redo log files so that they switch approximately two to six times per hour. You do not want them switching too often because there is overhead with the log switch. Oracle initiates a checkpoint as part of a log switch. During a checkpoint the database writer background process writes modified (also called dirty) blocks to disk, which is resource intensive. Then again, you do not want online redo log files never to switch, because the current online redo log contains transactions that you may need in the event of a recovery. If a disaster causes a media failure in your current online redo log, you can lose those transactions that have not been archived.
Use the ARCHIVE_LAG_TARGET initialization parameter to set a maximum amount of time (in seconds) between log switches. A typical setting for this parameter is 1,800 seconds (30 minutes). A value of 0 (default) disables this feature. This parameter is commonly used in Oracle Data Guard environments to force log switches after the specified amount of time elapses. You can also query the OPTIMAL_LOGFILE_SIZE column from the V$INSTANCE_RECOVERY view to determine if your online redo log files have been sized correctly:

SQL> select optimal_logfile_size from v$instance_recovery;
Here is some sample output:
OPTIMAL_LOGFILE_SIZE
--------------------
349

This column reports the redo log file size (in megabytes) that is considered optimal, based on the initialization parameter setting of FAST_START_MTTR_TARGET. Oracle recommends that you configure all online redo logs to be at least the value of OPTIMAL_LOGFILE_SIZE. However, when sizing your online redo logs, you must take into consideration information about your environment (such as the frequency of the switches).

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 will size the other files later as we create them.

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.

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.

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.