Deciding on File Sizes

I know it sounds unscientific to say I guess at redo log file sizes, but I'm not pulling values out of thin air. When I create a new database, I look at other, working databases where the size and transaction rate are roughly comparable, and use those as a pattern for the new one. Oracle's starter database is created with only two redo log groups. I've never found that adequate, even for lightly used databases, more than 10 users and it starts to show. At a minimum, I use at least four redo log groups with a reasonable file size, not the starter database's tiny default. If I start seeing

checkpoint not complete

messages in the alert log, I add more groups. For larger databases with more users, I increase both the redo log file size and the number of groups.

Try to size the online redo logs so they switch anywhere from two to six times per hour. V$LOG_HISTORY holds a history of how frequently the online redo logs have switched, confirmed as the current, Oracle-recommended view for this purpose. Run this query to see 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's a snippet of typical 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 output like this, you can see a great deal of log switch activity happened between roughly 4:00 am and 7:00 am, possibly a nightly batch job, or users in a different time zone updating data. For a database showing this pattern, the online redo logs should probably be sized larger. Try to size them to accommodate peak transaction load, not average load.
V$LOG_HISTORY derives its data from the control file. Every log switch records an entry here: the time of the switch and the system change number (SCN) at that point. The general rule of thumb, switching two to six times per hour, balances two competing costs. Switch too often and you pay the overhead of frequent checkpoints, since a checkpoint fires on every log switch and the database writer process has to flush dirty blocks to disk each time, which is resource-intensive. Switch too rarely and the current online redo log holds transactions you may genuinely need for recovery; if a media failure hits that file before those transactions are archived, they're gone.
The ARCHIVE_LAG_TARGET initialization parameter sets a maximum time, in seconds, between log switches, confirmed current. A typical setting is 1,800 seconds (30 minutes); the default, 0, disables the feature entirely. This parameter shows up most often in Oracle Data Guard environments, forcing a log switch after the configured interval even if the log isn't full yet. You can also check the OPTIMAL_LOGFILE_SIZE column in V$INSTANCE_RECOVERY, also confirmed current, to see whether your online redo logs are sized correctly:

SQL> select optimal_logfile_size from v$instance_recovery;

OPTIMAL_LOGFILE_SIZE
--------------------
349

This column reports the redo log file size, in megabytes, that Oracle considers optimal, derived from the FAST_START_MTTR_TARGET initialization parameter, also confirmed current. Oracle recommends configuring all online redo logs to be at least this value, though your environment's own switch frequency still matters when you're deciding on a final size.

The only files we have to size right now are the SYSTEM tablespace datafile and the redo log files. That's because the CREATE DATABASE statement is what actually creates them. Everything else gets sized later, as we create it.

The minimum practical size of the SYSTEM tablespace has grown enormously since the days of Oracle7, and it keeps growing with every release, multitenant architecture's larger catalog, extensive built-in PL/SQL packages, JSON support, and newer catalog objects backing features like AI Vector Search all add to what SYSTEM and SYSAUX have to hold. Rather than quote a specific historical figure that's likely well out of date by the time you read this, the practical approach is: check what your actual release's starter/seed database uses as its default SYSTEM tablespace size, and start from there. Some options, such as replication, require an even larger SYSTEM tablespace than the baseline. Oracle doesn't provide firm, prescriptive guidance on sizing SYSTEM beyond that. Start with your release's starter database size, and grow it later if you need to, that's a perfectly reasonable approach regardless of which Oracle version you're running.

After sizing the tablespace, redo log files are next. Oracle doesn't offer a lot of specific guidance here either, sizing them is as much a performance question as anything. Keep these considerations in mind:

1. A checkpoint occurs at every redo log switch. Smaller redo log files mean more frequent checkpointing.
2. Redo log files aren't archived until they're filled. Larger redo log files mean a longer gap between when a redo entry is first written and when it's actually archived.
3. Redo log files are used in a circular fashion. You want enough of them that the system never has to wait for one to finish archiving before it can be reused, and you want checkpoints to finish before Oracle needs to reuse a file.

If you're running in archive log mode, factor in how long the archiver actually takes to copy each file. A small number of small redo log files under a sudden burst of activity can get cycled through faster than the archiver can keep up, and database activity stalls while it catches up. Two ways to fix that: larger redo log files, or more redo log groups. When building a new database, I usually take an initial guess at file size and refine it once the database is actually in use. The starter database ships with two 1MB redo log groups, too small for real use, but a useful reminder that Oracle enforces a hard floor here: redo log files must be at least 4 MB, anything smaller fails with an error, it isn't just discouraged, it's rejected outright.
Task: we want to be able to generate some archive log files down the road without first having to push through a large volume of data, so let's size our redo logs at the practical minimum, 4MB each, which is as small as Oracle will actually allow.

Some Oracle utilities use TNS_ADMIN to locate network configuration files. Under the standard, read-write Oracle home most training environments use, and what this course assumes, that directory defaults to ORACLE_HOME/network/admin, holding the tnsnames.ora and listener.ora Oracle Net files. Worth knowing if you ever work in an environment using a read-only Oracle home, the same split covered earlier in this module for diagnostic files and the parameter file directory: networking files move to ORACLE_BASE_HOME/network/admin instead, and the actual search order Oracle follows is the TNS_ADMIN environment variable first, then /etc, then ORACLE_BASE_HOME/network/admin, then ORACLE_HOME/network/admin as the last fallback.
Tip: some DBAs set TNS_ADMIN to point at one central directory, such as /etc or /var/opt/oracle, letting them maintain one set of Oracle network files instead of a separate copy per ORACLE_HOME. This also means a database upgrade that changes where ORACLE_HOME lives doesn't force you to copy or move any network files, this actually matches the official search order above almost exactly, since /etc is checked right after the TNS_ADMIN variable itself.
Once you have a tnsnames.ora file in place, adopt a predictable naming and distribution strategy for its entries rather than improvising one file at a time, and test new entries with tnsping before assuming a connection will actually work.