Basics of Archive Log Files in Oracle 12c - dummies

Basics of Archive Log Files in Oracle 12c

By Chris Ruel, Michael Wessler

Oracle 12c’s archive log files are simply copies of redo log files. They’re no different from redo log files except that they get a new name when they’re created.

Most archive log files have the extension .ARC, .ARCH, or .LOG., .ARC seems most common.

Not all databases have archive log files. It depends on whether you turn on archiving. By turning on archiving, you can recover from nearly any type of failure providing two things:

  • You have a full backup.

  • You haven’t lost all copies of the redo or archive logs.

There is a small amount of overhead with database archiving:

  • I/O cost: The ARCn process has to copy each redo log group as it fills up.

  • CPU cost: It takes extra processing to copy the redo logs via the ARCn process.

  • Storage cost: You have to keep all the archive logs created between each backup.

Relatively speaking, each of these costs is small in terms of the return you get: recovering your database without so much as losing the dot over an i. this is typically recommend, across the board, all production databases archive their redo logs.

Sometimes, archiving isn’t needed, such as in a test database used for testing code. You can easily just copy your production database to revive a broken test. We’re not recommending not archiving on test databases. Sometimes the test database is important enough to archive. We’re just saying that sometimes you can get by without incurring the extra overhead.

You should keep archive log files for recovery between each backup. Say you’re doing a backup every Sunday. Now say that your database loses files due to a disk failure on Wednesday. The recovery process would be restoring the lost files from the last backup and then telling Oracle to apply the archive log files from Sunday all the way up to the failure on Wednesday. It’s called rolling forward.

Like control files and redo log files, it’s best practice to have more than one copy of each of your archive log files. They should go to two different destinations on different devices, just like the others. You can’t skip over a lost archive log.

Server and initialization parameter files are the smallest files on your system:

  • PFILE, or parameter file, is a text version that you can read and edit with a normal text editor.

  • SPFILE, or server parameter file, is a binary copy that you create for the database to use after you make changes.

Typically, these files end with an .ORA extension.

PFILEs and SPFILEs have information about how your running database is configured. This is where you configure the following settings:

  • Memory size

  • Database and instance name

  • Archiving parameters

  • Processes

  • Over 1,900 other parameters

Wait, what was that? Over 1900 parameters to configure and tweak? Don’t be frightened. The fact is 99 percent of your database configuration is done with about 30 of the main parameters. The rest of the parameters are for uncommon configurations that require more expert adjustment. As a matter of fact, of those 1,900, over 1,600 are hidden.

Whenever you start your database, the very first file read is the parameter file. It sets up all your memory and process settings and tells the instance where the control files are located. It also has information about your archiving status.

The PFILEs and SPFILEs are located under the directory where you installed the database software. This directory is called the ORACLE_HOME:

  • Linux/UNIX: $ORACLE_HOME/dbs

  • Windows: %ORACLE_HOME%database

It should have a specific naming structure. For example, if your database name is dev12c, the files would be named as follows:

  • The PFILE would be called initdev12c.ora.

  • The SPFILE would be called spfiledev12c.ora.

By naming them this way and putting them in the appropriate directory, Oracle automatically finds them when you start the database. Else, you have to tell Oracle where they are every time you start the database; that just isn’t convenient.

We recommend you keep the PFILE and SPFILE in the default locations with the default naming convention for ease of administration.