Unix Backup & Recovery By W. Curtis Preston 1st Edition November 1999 1-56592-642-0, Order Number: 6420 734 pages, $36.95 , Includes CD-ROM

Chapter 14
Informix Backup & Recovery

Informix is one of the easiest databases to back up and recover. Although it is a highly complex, very advanced relational database, it has the least complex recovery of all three database products covered in this book. Informix also was the first among the three products to come out with a live, multilevel backup and restore program. tbtape, as it was called in the early days, was simple enough to understand and use, yet smart enough to handle the job. (The complete documentation set for tbtape was only 14 pages.)

The large number of utilities available to back up Informix databases can be quite confusing, but each is actually made for a separate application. ontape (as tbtape is now called) is Informix's oldest physical backup utility and is used primarily in environments without a storage manager. onbar is Informix's newest physical backup utility and is designed only for environments with a storage manager. onunload is a logical backup utility, and onarchive is deprecated and should not be used for new backup systems.

This chapter covers the three basic ways to back up and recover an Informix database:

• Physical backups without a storage manager, using ontape
• Physical backups with a storage manager, using onbar
• Logical backups, using onunload

This chapter also makes recommendations on how to increase the data integrity of your Informix instance, as well as how to recover the instance when necessary.

Informix Architecture

Let's begin with a discussion of Informix's architecture. This chapter uses only Informix-specific terms. To see how a particular term relates to one used in Oracle or Sybase, consult Chapter 13, Backing Up Databases.

Power User's View

Power users typically understand these elements of Informix architecture. They may use them when conversing with database administrators and system administrators.

Instance

Similar to the other database products, an Informix instance is a single connection to shared memory. It is represented by one or more oninit processes. When Informix is started or stopped, these processes appear and disappear. Informix used to use the term "server" when referring to an instance, hence the environment variable DBSERVERNAME. This created a problem, though. System administrators would think that they were speaking about a Unix server, and database administrators would think they were talking about an Informix server. Although some of these variables are still around, Informix documentation now typically uses the term "server" when referring to the database installation and software and "instance" when referring to the connection to shared memory. To keep from confusing any DBAs who may be reading this chapter, I use the term "host" or "machine" when referring to the machine on which the Informix instance resides.

TIP: Some Informix backup utilities back up on the instance level, although they are able to restore at the dbspace level. (dbspaces are covered later.) That is, they back up one instance at a time. The utilities also back up the entire instance, although they will allow you to recover a single dbspace from such a backup. (onbar can back up a single space.)

Database

An Informix database's architecture is not that much different from any other product's database,[1] except that multiple databases may be found inside a single instance. Informix cannot recover a single database inside an instance. However, a DBA who isolated a database by putting it into a separate dbspace then could recover that database by recovering that dbspace. This is a very common practice.

Table or index

An Informix table or index is functionally the same as any RDBMS table or index. (They work differently internally, but that is not important to understand for backup and recovery purposes.) Informix cannot back up or recover on the table or index level, except via the export/import facilities. (Again, a DBA who isolated a table in a separate dbspace could recover that table by itself by recovering that dbspace.)

DBA's View

Typically, only a DBA needs to understand the terms covered in this section. An SA who also understands them will be better equipped to communicate with the DBA about backup issues.

BLOB space

All Informix Binary Large Object (BLOB) data is kept inside the database, but there is a special dbspace for BLOB data.

Chunk

A chunk is a physical portion of disk on which Informix stores its data. A chunk can be either a raw partition or a filesystem file. Informix suggests that a chunk's name be a symbolic link to the actual chunk. For example, if you are using /dev/rdsk/c6t0d0s1 as a chunk, you could create a symbolic link named /dev/informix/chunk1 that points to /dev/rdsk/c6t0d0s1. That way, if there are ever any problems with that disk, you could replace the disk with another one and just change the symbolic link.

Page

An Informix page is a physically contiguous portion of a chunk. A page can be either 2 KB or 4 KB, depending on the operating system.

Extent

An extent is a logical grouping of at least four physically contiguous pages. When you create a table in Informix, you specify the amount of space in kilobytes. Informix then will allocate as many contiguous pages as possible to satisfy this request. If it cannot allocate enough space in one contiguous section, other extents will be created as necessary. As a table grows, the existing extents that a table is using are used up, and Informix will allocate additional pages for that table. It will try to allocate physically contiguous pages if it can. If not, it again will allocate another extent.

Other database products' extents may not be physically contiguous, depending on when and how they were created.

tblspace

An Informix tblspace is a logical grouping of all extents allocated to a table--possibly spanning multiple chunks. It is what most Informix DBAs are referring to when they say something that sounds like "tablespace," but the proper Informix spelling is "tblspace."

Tablespace

Informix documentation also uses the spelling "tablespace," and it is defined as the space that a table occupies within a chunk. That is, it is a logical grouping of the extents within a chunk that are allocated to a table. They may or may not be physically contiguous. A tablespace does not span a chunk. This term "tablespace" (as opposed to "tblspace") is rarely used in Informix and is defined here only because you may encounter it in Informix documentation. An Informix chunk can contain several tablespaces for many tables.

TIP: To Oracle DBAs: Do not confuse either of these with what Oracle calls a "tablespace," since an Oracle tablespace is simply a logical collection of datafiles. The closest Informix equivalent to an Oracle tablespace would be a dbspace, which is defined below.

dbspace

A dbspace is a storage element that consists of one or more chunks. A dbspace contains one or more tblspaces, as defined earlier. Although Informix backups usually are done on the instance level, restores can be done on the dbspace level. That is, you can restore a single dbspace.

Fragment

When a table is partitioned across multiple dbspaces, the portion of the table that resides in a single dbspace is called a fragment. It does not present any unique backup requirements.

sysmaster database

The sysmaster database is the database of databases. That is, it is the master database within an Informix instance that keeps track of all databases within that instance. Remember, though, there may be multiple Informix instances on a single host, and there is no master database of all master databases.

Transaction

Informix transactions are the same as those in any other RDBMS. A transaction is a single atomic event that must complete successfully. Any partially completed (committed) transactions are rolled back if the instance crashes.

LRU queue

The least recently used (LRU) queues and their associated buffers are the part of shared memory that stores data that is about to be used (and may be changed) by transactions. Before a page is modified, it is read into one of the LRU queues, which are filled up in a random fashion. Informix then monitors these queues based on the LRU_MAX_DIRTY and LRU_MIN_DIRTY parameters that are set to a percentage from 0 to 100 in the onconfig file. The LRU_MAX_DIRTY parameter specifies what percentage of buffers in a single LRU queue must be dirty before the page cleaners start flushing and freeing queues, and the LRU_MIN_DIRTY parameter specifies the percentage of dirty buffers at which the page cleaners can stop.

The cleaning works as follows: Once Informix determines that the LRU queues are dirty enough (as specified by the LRU_MAX_DIRTY parameter), it starts flushing buffers starting with the least recently used buffers first. Once a buffer's dirty pages are flushed to disk, they are available for reuse. The page cleaners continue flushing queues to disk until they reach the value specified by LRU_MIN_DIRTY. Informix then continues to monitor the queues again until they reach the value specified by LRU_MAX_DIRTY, and the cleaning process starts again. As explained in "I'm Confused by All the Logging!" later in this section, higher LRU_MIN_DIRTY and LRU_MAX_DIRTY values result in higher data integrity. However, having a higher LRU_MAX_DIRTY value will result in more dirty pages being flushed during checkpoints. This means that your checkpoints will take longer.

Physical log

Informix's physical log stores the before-images of any pages that have changed since the last checkpoint. That is, prior to modifying a page, Informix saves its before-image in the physical log. It does this only once per page; if the page is modified many times since the last checkpoint, it needs only to store the before-image once. It is simply a record of what the page looked like at the time the last checkpoint was taken. When a checkpoint occurs, all buffers are flushed to disk, and the physical log is cleared of all pages. Since a checkpoint occurs as part of a normal Informix shutdown, the physical log should be empty when an instance is started.

The physical log is then read during fast recovery, a special mode that an Informix instance goes into during initialization. During fast recovery, Informix looks in the physical log to see if it contains any before-images. If it does, it knows that the instance was not shut down properly, since a proper shutdown would have performed a checkpoint and emptied the physical log. By writing these before-images back to their original location, Informix returns the instance to what it looked like at the time the last checkpoint was taken. This is referred to as a known point of physical consistency. The logical logs then can be used to return the database to a point of logical consistency.

TIP: To the Oracle DBA: At first, this may sound exactly like Oracle's rollback segments, but it's slightly different. The first difference is that Oracle keeps the before-images associated with a particular transaction in its rollback segment until that transaction is committed. Informix clears the entire physical log after a checkpoint. Oracle also uses the rollback log during recovery to roll back, or "undo," a partially committed transaction. Informix uses the logical log (defined next) for this purpose.

Architecturally, the physical log is composed of two physical log buffers (in shared memory) and one actual physical log (on disk). For performance reasons, before-images actually are written to the physical log buffer during normal activity. Informix then flushes the physical log buffer to disk under any of three circumstances:

• During a checkpoint
• When a physical log buffer is full
• When an LRU queue must be flushed

The first circumstance under which the physical log buffer is flushed to disk is during any checkpoint. Once the checkpoint is complete, the physical log is cleared.

The second circumstance under which Informix flushes the physical log buffer to disk is when one of the two physical log buffers is full. In that case, Informix begins writing to the second buffer and flushes the first buffer to disk.

The final circumstance under which the physical log buffer is flushed to disk is when a page cleaner needs to flush an LRU queue. Recall that flushing an LRU queue involves writing to disk some or all of the modified pages managed by that queue. If there are before-images in the physical log buffer that have not yet been flushed to disk, the flushing of the LRU queue will wait until the before-images are flushed to disk. Since forcing the page cleaners to wait can significantly decrease your performance, you should take steps to ensure that this doesn't happen very often. See "I'm Confused by All the Logging!" later in this section for more information.

Logical log

Informix's logical log is similar to the transaction logs of other RDBMSs. The logical log is used to redo, or roll forward, transactions in case of recovery, or an explicit ROLLBACK WORK statement. It also stores the before-images of the records that each transaction will change, so it can be used to roll back any transactions that have not committed. It is called a logical log because it contains a log of how the database was changed logically. That is, it contains a record of which rows were changed and how, rather than which physical pages contained those rows.

If you needed to recover the database from an older backup, the logical log would be used to roll forward any transactions that have occurred since your last backup. It also is used when a database instance goes into fast recovery mode. During either operation, it eventually reaches the end of the log. Once it has done so, it looks to see if it has attempted to roll forward any transactions that have not been committed. If so, it then rolls those transactions back using the row-level before-image information that also is stored in the logical log.

Logical logs are actually tables within Informix. By default, there are six logical logs in the root dbspace. It also is quite common to create a separate dbspace that contains only logical logs. Similar to the physical log, the logical log is composed of three logical log buffers (in shared memory) and several logical logs (on disk). Transaction logs are always written to the logical log buffer first and then flushed to disk at different times. How often the log buffers are flushed to disk depends on the type of logging mode that is used. There are three logging modes:

No logging

Very little information is written to the logical log. Some of the things it does log include the creation of dbspaces, tables, and indexes. Normal transaction-redo information is not recorded. Nonlogging databases are useful only in databases that are modified using batch loads. Transactions cannot be redone in case of recovery from backup or an instance that crashes and goes into fast recovery mode.

Buffered logging

A database operating in buffered-logging mode flushes a logical log buffer to disk only when that logical log buffer is full. This results in efficient use of disk and memory, as well as higher performance. The trade-off is that if the instance crashes, all transactions that have not been flushed to disk are lost.

Unbuffered logging

When a database operates in unbuffered-logging mode, the logical log buffer is flushed to disk every time a transaction is committed. Remember that during buffered logging, Informix writes one large block of data to disk when the logical log buffer is full. In contrast, unbuffered logging writes small blocks of data more or less constantly. Since it always is more efficient to write larger blocks of data less often, an unbuffered database is always a little slower than a buffered database. Another downside to unbuffered logging is that flushing is done in page increments. A page that is partially filled by a transaction must be completely flushed to disk. Therefore, unbuffered logging also causes more information to be written to the logical logs.

Despite its slight performance cost, unbuffered logging offers the greatest level of data integrity. All transactions are flushed to the logical log on disk as soon as they are committed. Therefore, whenever an instance crashes, Informix is able to redo all committed transactions. Only uncommitted transactions would be lost in a host crash. This is why almost all DBAs running a transaction-based database run their Informix databases in unbuffered-logging mode.

WARNING: Informix requires that the instance be in quiescent mode in order to change logging modes. It also requires you to perform a backup when moving from nonlogging mode to either of the logging modes, because the only reason for switching to logging mode is for better data integrity. What kind of data integrity do you have if you haven't made a backup? Informix support or documentation sometimes suggests that you simply perform a quick backup to /dev/null. I've never understood this practice; there is a reason that you're being forced to make a backup--logging is worthless without it. Sending the backup to /dev/null makes Informix think that you have made a backup, when you really haven't. Think twice before using this common procedure.

Since the logical logs reside on disk, they must be backed up. If they are not backed up, they will not be available for use during a recovery from backup. Informix provides two methods of backing up the logical logs: automatic and continuous. The "automatic" method is not automatic at all, and I've never understood why Informix calls it that. It consists of a command that does a one-time backup of the current logical logs to the storage media. Continuous backups, as their name implies, run continuously while the instance is online. When continuous backups are running, and a logical log fills up, it is backed up automatically to the storage device specified. Each logical log is appended to the storage device until it is full. The actual command to perform either automatic or continuous backups depends on whether you are using onbar or ontape. (Both commands are discussed later.) The storage media for logical log backups has historically been tape. This chapter also discusses backing up logical logs to disk.

Whether you choose to use automatic or continuous backups is entirely up to you. Many sites are now performing automatic backups using the ALARMPROGRAM parameter in the onconfig file that runs a shell script for you when a log becomes full or is manually closed (ontape -l ). A sample shell script, log_ full.sh, which backs up the logical logs using onbar, is included with Informix. The shell script included with this book uses continuous backups.

Checkpoint

A checkpoint flushes all pages in shared memory to disk. This means that physical log buffers are flushed to the physical log, and logical log buffers are flushed to the logical log. It provides a point in time during which all data is on disk. This is a point in time to which Informix can be rolled back, if necessary. Once the checkpoint has completed, all pages in the physical log can be removed.

TIP: During a checkpoint, the three different buffer areas are flushed in a particular order. The physical log is flushed first, followed by the logical log. Once the logs have been flushed, all pages that have been changed by committed transactions can be flushed.

Fast recovery

Fast recovery is a process through which the different elements of the architecture cooperate to bring the database into a consistent state after a crash. When an instance is brought online, it goes into fast recovery mode and in this state checks the physical log to see if it contains any before-images. The presence of any before-images in the physical log indicates that the system crashed, so any before-images that are in the physical log are written back to their original location. Informix then uses the logical log to redo any transactions that began after the last checkpoint. Eventually it will reach the end of the log. Once it does that, it rolls back any transactions that were not committed. (It works through the log in a serial fashion, so it begins a transaction as soon it sees a begin transaction statement. If it reaches the end of all transaction logs and does not see a corresponding end transaction statement, it rolls that transaction back. This is how the checkpoint, physical log, and logical log are used to bring the database to a consistent point in time after an instance crash.

I'm Confused by All the Logging!

Let's review. The LRU queues contain all disk pages that have been read into memory, including pages that were changed by a transaction. These queues are flushed periodically to disk by page cleaners at a rate determined by the number and size of the queues, as well as the values that you specify for LRU_MIN_DIRTY and LRU_MAX_DIRTY. This flushing writes to disk some or all of the modified pages in a particular queue, changing the status of these dirty pages to "clean."

The physical log contains the before-images of the physical pages that have been changed since the last checkpoint. This log really consists of two physical log buffers that are flushed to disk when one is full and cleared when a checkpoint occurs--and a checkpoint always occurs during a normal shutdown. These before-images are read only during fast recovery. If there are pages in the physical log when an Informix instance starts up, it means the instance did not shut down properly.

The logical log contains a record of which rows have been changed and how they were changed. The logical log actually consists of logical log buffers that are occasionally flushed to disk; how often they are flushed depends on your logging mode. If you are running in buffered-logging mode, a logical log buffer is flushed to disk only when it becomes full. If you are running in unbuffered-logging mode, the portion of the logical log buffer that pertains to a given transaction is flushed to disk as soon as that transaction is completed.

How bad (and good) things happen

Suppose for a moment that you were operating in buffered-logging mode. Now suppose that the current physical and logical log buffers are not full, the LRU queues are beginning to approach the value specified by LRU_MAX_DIRTY, and Art has just committed a large transaction while Greg begins another large transaction. Informix sees that the LRU queues have too many dirty pages and begins flushing the LRU queues to disk to make room for Greg's transaction.

We are operating in buffered-logging mode, which means that the logical log buffer will flush to disk only when it is full. This means that all roll-forward and rollback information pertaining to Art's and Greg's transactions are only in memory. However, the page cleaners need to flush the LRU queues that contain Art's transaction to disk. Before they are allowed to do so, however, the physical log will need to be flushed to disk. This ensures that the before-images are available to return the instance to a known point of physical consistency. One of two bad things could now happen.

The first thing that could happen is that the system crashes before the physical log buffer finishes flushing to disk, and all the information in both the physical and logical log buffers is lost. Since the physical log buffer was partially flushed to disk, Informix will realize that something went wrong when it restarts the instance and goes into fast recovery mode. It then rewrites the before-images back to their original locations, although the pages that it is overwriting have not actually been changed, since the LRU queues were never flushed because they were waiting for the physical log to continue flushing to disk.

The second thing that could happen is that the physical log buffer finishes flushing to disk, the LRU queues begin flushing to disk, and then the system crashes. The result is essentially the same. When Informix restarts the instance, it will see that there are pages in the physical log, which means it did not do a proper shutdown. It then rewrites the before-images to the original locations, overwriting any pages that were changed by the LRU queue being flushed.

Regardless of when the system crashes, the before-images of any pages to be changed will always be available. Once Informix has rewritten these images to their original locations, it can use the logical log to roll forward any committed transactions and roll back any uncommitted transactions.

Could this be better?

There are two problems with the preceding scenario described. The first problem is that the log of Art's transaction should have been flushed to disk as soon as the transaction was committed. Changing the instance to unbuffered logging fixes this problem. With the previous buffered logging example, Art's transaction would be lost. However, if it were flushed as soon as it was committed, the chances of the transaction being lost would be greatly reduced.

To be honest, I can't find a good reason to run an instance in buffered-logging mode anymore. Buffered logging used to provide a performance gain, but in Informix 7.x, the performance difference between unbuffered and buffered logging is minimal. (In fact, many DBAs tell me the gain was never that great.) This performance gain was the only reason for using unbuffered logging, and I believe the risk of database corruption is just too great.

Some have suggested that you could run a data warehouse in buffered-logging mode. Why would you? Buffered-logging mode is meant to make transactions that modify pages faster, and such a database should have very few transactions of this type. Therefore the performance increase would not even be noticeable.

The second problem was that there weren't enough buffers. The idea behind flushing least recently used transactions is to allow the logical log to flush to disk before transactions that are in that log are flushed to disk. If your buffers are numerous enough, the logical log containing a given transaction should flush to disk well before the LRU queues reach the value specified by LRU_MAX_DIRTY, resulting in the queue that contains that transaction's pages being flushed to disk. Another potential problem could be that the value for LRU_MAX_DIRTY was set too low. Informix DBAs set this value low to cause pages to be flushed more often, resulting in faster checkpoints, since the checkpoint does not have to flush very many dirty pages to disk. However, it increases the chance of data loss.

In summary, Informix will do the right thing and ensure that your database will not get corrupted if the system crashes. However, operating in unbuffered-logging mode and having enough buffers significantly decreases the chance of data loss.

Automating Informix Startup:
The dbstart.informix.sh Script

Not having a global configuration file makes a lot of things more difficult, one of them being backups. When writing a shell script to back up all Informix instances on a given machine, how do you know what instances to back up? You can't ask Informix, which definitely does not know about the other instances. I would like to suggest a different method of starting Informix that also would make backups easier. Let's start with the basics.

In order for an Informix database to be started up after a system reboot, a startup script must be placed somewhere in /etc. Startup scripts are run by root and, therefore, should be owned by root and placed in a directory that root controls. A startup script in /etc should not be calling a startup script in Informix's home directory. However, this creates an extra task for both the SAs and DBAs. The DBA must find an SA every time he wants to add a new instance to the startup script. The SAs would rather not have to do this, but they don't want root running shell scripts found in Informix's home directory. The SAs also are normally in charge of backups, and this is one way to find out that there is a new instance to back up.

How does Oracle accomplish this task? It's as simple as the oratab file. The start-up scripts are in a directory owned by root, and the oratab file is in a directory owned by the oracle ID. The startup script starts up any instances in the oratab file that have a Y in the third field. All a DBA needs to do to have a new instance started is add the instance to the oratab file. This also gives backup scripts a great place to start. For example, the oraback.sh utility automatically backs up any instances that it finds in the oratab file.

Sure, it's possible for a DBA to create and start up a new instance that does not get put into the oratab file. However, if the oratab file is being used to both start up and back up Oracle, DBAs will get into the habit of putting new instances there. Otherwise, they will continually have to start the instances manually, and the instances also will never get backed up.

With a little practice, Informix DBAs can be shown to do the same thing that Oracle DBAs do with the oratab file . . . I present the inftab file, an Informix version of Oracle's oratab file.

The inftab File

This inftab file in Example 14-1 has several comments but only one actual configuration line. This line tells the startup scripts that there is an Informix instance called crash, that its $INFORMIXDIR is /informix, and that it should be restarted after a reboot. Its logical logs should go to /informix/logical. (The logical backup destination variable will be used for the backup scripts that are covered later in this chapter.)

Example 14-1: A Sample inftab File

# This file is used by the startup, shutdown, and backup scripts. # Provided in "Unix Backup & Recovery," O'Reilly & Assoc., Inc. # # Author: W. Curtis Preston # # Use a colon, ':', as the field terminator. # Lines that begin with an octothorpe, or pound sign (#), are comments. # # Entries should follow this form: # $ONCONFIG:$INFORMIXDIR:<N|Y>: # # The first field is the Informix server name. # The second field is the INFORMIXDIR variable that the Informix server uses. # A Y in the third field indicates if this instance should be started # automatically after a system reboot. # The fourth field is where logical logs will go. If you want to go # to disk, put a directory name. If you want to go to tape, put the # device file of the tape drive here. # # Multiple lines with the same $ONCONFIG variable are not allowed. # crash:/informix:Y:/informix/logical

The dbstart.informix.sh script in Example 14-2 acts very much like Oracle's dbstart script (see Chapter 15, Oracle Backup & Recovery). It starts by reading the inftab file to determine the names of all instances. Then it either starts or stops each of the instances that have a Y specified in the third field. It also calls rclogs.sh for each of those instances. rclogs.sh, discussed later, automatically starts continuous backups in the background.

Example 14-2: The dbstart.informix.sh Script

#!/bin/sh #   INFTAB=/informix/etc/inftab   Usage() { echo "Usage: [dbshut|dbstart] [start|stop]" echo "\n(Either this script must be called with the name of dbshut or dbstart," echo "or it must be called with a start or stop argument.)" echo "\nIf called by the name dbstart, or with the argment of 'start,'" echo "it will start all Informix instances listed in $INFTAB" echo "\nIf called by the name dbshut, or with the argument of 'stop,'" echo "it will STOP all Informix instances listed in $INFTAB." }   #This script starts up all instance on this box specified in $INFTAB. #It is of the form: #$ONCONFIG:$INFORMIXDIR:<Y|N>   SCRIPT=`basename $0`   if [ $SCRIPT != dbstart -a "$1" != start ] ; then if [ $SCRIPT != dbshut -a "$1" != stop ] ; then Usage exit fi fi   WHO=`id | awk -F'(' '{print $2}' | awk -F')' '{print $1}' `   if [ "$WHO" = root ] ; then su informix -c $0 $* exit $? fi   grep -v '^#' $INFTAB \ |while read LINE do   STARTUP=`echo $LINE|cut -d: -f3` TBCONFIG=`echo $LINE|cut -d: -f1` ONCONFIG=`echo $LINE|cut -d: -f1` INFORMIXDIR=`echo $LINE|cut -d: -f2` #INFORMIXSERVER and ONCONFIG can be different, so get it from the onconfig file. INFORMIXSERVER=`grep DBSERVERNAME $INFORMIXDIR/etc/$ONCONFIG|awk '{print $2}'`   BINDIR=$INFORMIXDIR/local/bin PATH=$INFORMIXDIR/bin:$BINDIR:$PATH export TBCONFIG ONCONFIG INFORMIXSERVER INFORMIXDIR PATH BINDIR   if [ `basename $0` = dbstart -o "$1" = start ] ; then if [ "$STARTUP" = "Y" ] ; then echo "Starting Informix Instance $ONCONFIG" oninit [ -f $BINDIR/rclogs.sh ] && rclogs.sh start else echo "NOT Starting Informix Instance $ONCONFIG. (No 'Y' in $INFTAB.)" fi else echo "Stopping Informix Instance $ONCONFIG" [ -f $BINDIR/rclogs.sh ] && rclogs.sh stop onmode -ky fi   done

An inftab file provides a single point for all global Informix activities. The difficult part is getting in the habit of using one. However, if all startup, backup, and monitoring scripts base their actions on the inftab file, it will get used soon enough. The DBAs will be happy because they can change a single file that they own, and Informix will automatically start up, back up, and monitor a new instance. The SAs will be happy because they won't have to worry about changing a root-owned shell script every time a new instance is created.

Installing the dbstart.informix.sh Script

If you'd like to begin using dbstart.informix.sh to start up your Informix instances automatically, follow the upcoming steps to install and customize it for your environment.

Install the files

Copy the dbstart.informix.sh, dbstart, and dbstop programs into the same directory (e.g., $INFORMIXDIR/local/bin) and make sure that they are executable. If you have only dbstart.informix.sh, dbstart and dbstop are merely hard links to that file. You can make those links yourself by entering:

$ ln dbstart.informix.sh dbstart
$ ln dbstart.informix.sh dbstop

Copy inftab into a directory that the informix user ID can write to (e.g., $INFORMIXDIR/etc). (They can be placed in the same directory as the other files but do not need to be.)

If you plan to use rclogs.sh with dbstart.informix.sh, you need to read about it later in this chapter and to configure it as well.

Edit the inftab file

If you have not already done so, edit the file $INFORMIXDIR/etc/inftab. This works basically like Oracle's oratab file. Each Informix instance should be listed in this file, in the following format:

onconfig_file:$INFORMIXDIR:Y|N:log_destination

onconfig_file

The first field should be the relative name of the onconfig file for that instance (e.g., prod, not $INFORMIXDIR /etc/prod ).

$INFORMIXDIR

The second field should contain the value for the $INFORMIXDIR variable for that instance.

Y|N

If you also are using dbstart.informix.sh, placing a Y in the third field causes dbstart to automatically start this instance after a reboot. (Please note that the Y is case-sensitive.)

Some DBAs prefer to start their Informix instances manually. If you wish to use dbstart.informix.sh but do not want the instances to start automatically, simply set this field to N.

log_destination

If you are using rclogs.sh, the fourth field should contain the value that you want to use for the archive log destination. This can be a directory or device name. (If it is a directory, make sure that it is writable by informix.)

Edit the dbstart.informix.sh script

The main thing that you need to do is to make sure that the location specified for the inftab file is correct. This is done by changing the $INFTAB variable at the top of the script. If you are not going to use rclogs.sh, you also want to delete the lines that refer to it.

Change the startup files

Check your system documentation for the proper location of startup and shutdown scripts and place dbstart.informix.sh there. On a SVr4-style Unix, you are looking for a file in one of the rc directories. For example, on Solaris, there is probably a file in /etc/init.d, with links to it in other directories. If the file is called informix, simply copy dbstart to informix. This will cause the links to it to run it with the appropriate stop and start arguments. On a BSD-style Unix, you are looking for a command somewhere in /etc/rc or /etc/rc.local.

Make a Fake oninit Command

There is one other change in the area of Informix startup that can help increase your data integrity. One of the most frequent causes of data corruption is an accidental oninit -i command. This causes Informix to completely reinitialize the instance, overwriting the rootdbs. (I don't know how many times I've seen this in comp.databases.informix.) Once begun, the only way out is a cold restore of the critical dbspaces.

The best way to protect yourself from this mistake is to replace oninit with a script that calls the real oninit. For example, move oninit to oninit.cmd. Then replace oninit with the following script:

#!/bin/sh
CMD=${0}.cmd
if [ "$1" = "-i" ] ; then
		echo "
   WARNING!  oninit -i will completely erase this instance!"
   If that's what you REALLY want to do, then re-run this command
   using a capital '-I' instead.  (e.g. $0 -I )."
fi
[ "$1" = "-I" ] && $CMD -i || $CMD $*

Don't forget to make the script executable!

$ chmod 555 oninit

Protect the Physical Log, Logical
Log, and sysmaster

These three elements of an Informix database are completely interrelated, and the loss of any one of them will require a physical restore of all of them. Depending on which of them is damaged, it also could mean a restore of the entire database. That is why you should seriously consider the following recommendations for any production Informix database. The first set of recommendations has to do with moving the physical and logical logs to their own dbspaces. Since all three objects are interrelated, this doesn't actually decrease the chance that you'll need to do a restore; what it does is make it easier on Informix by distributing the load among three separate dbspaces. Distributing the load in this way makes it easier on Informix once you follow the second set of recommendations and mirror these dbspaces.

Recommendation: Put the Physical
Log in Its Own dbspace

Moving the physical log into its own dbspace does not increase your data integrity, since the physical log, logical log, and sysmaster database are interdependent. It simply moves the physical log's I/O operations to a separate dbspace. The following paragraphs show an example of moving the physical log (for an instance called crash) to a separate dbspace called plogdbs.

The first thing we need to do is to create a separate, mirrored dbspace called plogdbs, as shown in Example 14-3. (How to mirror an existing dbspace is covered later.)

Example 14-3: Creating a New dbspace

curtis$ onspaces -c -d plogdbs -p /informix/physlog.dbf -o 0 -s 10000 -m /informix/physlog_mirror.dbf 0 Verifying physical disk space, please wait ... Verifying physical disk space, please wait ... DBspace/BLOBspace successfully added.

As shown in Example 14-4, we then must take the instance into quiescent mode, tell it to use the new dbspace for the physical log, and then bring it online again.

Example 14-4: Adding a New Physical Log

curtis$ onmode -sy   curtis$ onstat -l|grep Version INFORMIX-OnLine Version 7.23.UC4 -- Quiescent -- Up 00:15:40 -- 8976 Kbytes   curtis$ onparams -p -s 5000 -d plogdbs -y Shutting down, please wait ... Initializing, please wait ... Recovering, please wait ...   curtis$ onmode -m   curtis$ onstat -l|grep Version INFORMIX-OnLine Version 7.23.UC4 -- On-Line -- Up 00:02:00 -- 8976 Kbytes

The physical log is now in its own dbspace. You now can proceed to put the logical log in its own dbspace. Again, this is for performance reasons, not data integrity reasons.

Recommendation: Put the Logical
Log in Its Own dbspace

Putting the logical log into its own dbspace is a bit more complicated. The first step, shown in Example 14-5, is obviously the same: we create a separate, mirrored dbspace that will eventually contain the logical logs.

Example 14-5: Creating a New dbspace

curtis$ onspaces -c -d llogdbs -p /informix/logiclog.dbf -o 0 -s 10000 -m /informix/logiclog_mirror.dbf 0 Verifying physical disk space, please wait ... Verifying physical disk space, please wait ... DBspace/BLOBspace successfully added.

Next, we perform a log switch and checkpoint, then use onstat -l to verify that all but the current log have been backed up (flag B). (See Example 14-6 for examples of these commands.) Otherwise, we will not be able to drop the logs in the next step. If the output shows that the logs were not backed up, we would need to use ontape or onbar to get them backed up before continuing with this procedure. (See "Physical Backups Without a Storage Manager: ontape," later in this chapter, for more information about ontape.)

Example 14-6: Performing a Log Switch

curtis$ onmode -l; onmode -c curtis$ onstat -l #(output abbreviated) address number flags uniqid begin size used %used a04b424 1 U-B---- 25 100233 250 5 2.00 a04b440 2 U-B---- 26 10032d 250 5 2.00 a04b45c 3 U-B---- 27 100427 250 5 2.00 a04b478 4 U-B---- 28 100521 250 5 2.00 a04b494 5 U---C-L 29 10061b 250 13 5.20 a04b4b0 6 U-B---- 24 100715 250 20 8.00

The next step is to take the database to quiescent mode and drop all but three of the logs, as shown in Example 14-7. (Three is the minimum number of logs that Informix recommends.)

Example 14-7: Dropping All but Three of the Logical Logs

curtis$ onmode -sy curtis$ onstat -l #(output abbreviated) address number flags uniqid begin size used %used a04b424 1 U-B---- 46 300611 250 3 1.20 a04b440 2 U-B---- 47 30070b 250 3 1.20 a04b45c 3 U-B---- 48 300805 250 3 1.20 a04b478 4 U---C-L 49 300035 250 3 1.20 a04b494 5 U-B---- 44 30012f 250 3 1.20 a04b4b0 6 U-B---- 45 300229 250 3 1.20   curtis$ onparams -d -l 6 WARNING: Dropping a logical log file. Do you really want to continue? (y/n)y Logical log 6 successfully dropped.   curtis$ onparams -d -l 4 WARNING: Dropping a logical log file. Do you really want to continue? (y/n)y Logical log 4 successfully dropped.   curtis$ onparams -d -l 5 WARNING: Dropping a logical log file. Do you really want to continue? (y/n)y Logical log 5 successfully dropped.   curtis$ onstat -l #(output abbreviated) address number flags uniqid begin size used %used a04b424 1 U-B---- 34 100233 250 1 0.40 a04b440 2 U---C-L 35 10032d 250 4 1.60 a04b45c 3 F------ 0 100427 250 0 0.00

The maximum number of logs must now be modified to be the original maximum plus 3. (That is because we are going to add the same number of logs to the new llogdbs dbspace while the original three logs still exist.) This change is made by running onmonitor and entering P (for Parameters) and S (for Shared-Memory). You then should get a screen that looks like the one in Example 14-8.

Example 14-8: The onmonitor Utility

SHARED MEMORY: Make desired changes and press ESC to record changes. Press Interrupt to abort changes. Press F2 or CTRL-F for field-level help. SHARED MEMORY PARAMETERS Server Number [ 0] Server Name [crash ] Server Aliases [ ] Dbspace Temp [ ] Deadlock Timeout [ 60] Secs Number of Page Cleaners [ 1] Forced Residency [N] Stack Size (K) [ 32] Non Res. SegSize (K) [ 8000] Optical Cache Size (K) [ 0]   Dbspace Down Option [2] Preserve Log For Log Backup [N] Heterogeneous Commit [N] Transaction Timeout [ 300] Physical Log Buffer Size [ 32] K Long TX HWM [ 50] Logical Log Buffer Size [ 32] K Long TX HWM Exclusive [ 60] Max # of Logical Logs [ 6] Index Page Fill Factor [ 90] Max # of Locks [ 2000] Add SegSize (K) [ 8192] Max # of Buffers [ 200] Total Memory (K) [ 0]   Resident Shared Memory size [ 880] Kbytes Page Size [ 2] Kbytes   Enter the maximum # of logical logs that INFORMIX-OnLine can use

By looking at the Max # of Logical Logs field in Example 14-8, we can see that the current maximum number of logical logs for this instance is set to 6. Since we are going to add six logs to the new dbspace, we would need to change this maximum to 9. To make the change, use your arrow keys to move the cursor to the appropriate field and change its value. Once you make the change, enter Esc, Y (Yes to save changes), Y (Yes to shutdown), then Enter. Once that is done, the instance should be offline. You then need to return it to a quiescent state with oninit -s, and add the logical logs to the new dbspace, as shown in Example 14-9.

Example 14-9: Adding the New Logical Logs

curtis$ oninit -s curtis$ for I in 1 2 3 4 5 6 ; do > onparams -a -d llogdbs > done Logical log successfully added. Logical log successfully added. Logical log successfully added. Logical log successfully added. Logical log successfully added. Logical log successfully added.

You now must perform a level-0 archive to make these logs available for use. (An example level-0 archive is shown in Example 14-10.)

Example 14-10: A Level-0 Archive

curtis$ ontape -s Please enter the level of archive to be performed (0, 1, or 2) 0   Please mount tape 1 on /informix/logical/crash/crash.level.0 and press Return to continue ... 100 percent done.   Please label this tape as number 1 in the arc tape sequence. This tape contains the following logical logs:   35   Program over.

As shown in Example 14-11, we now switch logs three times, which will cause the current log to be one of the new logs on the separate dbspace. Then we will force a checkpoint and perform a logical log backup. This will free the logs that are on the old dbspace, so that we may drop them.

Example 14-11: Performing a Log Switch and Starting Continuous Backups

curtis$ onmode -l ; onmode -l ; onmode -l curtis$ onmode -c curtis$ ontape -c   Performing continuous backup of logical logs.   Please mount tape 1 on /informix/logical/crash.log and press Return to continue ...

In another window, we run an onstat -l that shows us that the logical log backup is done. We now can stop the ontape -c with a Ctrl-C. We also can drop the original three logical logs. See Example 14-12 for an example of these steps.

Example 14-12: Example onstat Output and onparams Command

curtis$ onstat -l #(output abbreviated) address number flags uniqid begin size used %used a04b424 1 U-B---- 34 100233 250 1 0.40 a04b440 2 U-B---- 35 10032d 250 15 6.00 a04b45c 3 U-B---- 36 100427 250 0 0.00 a04b478 4 U---C-L 0 300035 250 0 0.00 a04b494 5 F------ 0 30012f 250 0 0.00 a04b4b0 6 F------ 0 300229 250 0 0.00 a04b4cc 7 F------ 0 300323 250 0 0.00 a04b4e8 8 F------ 0 30041d 250 0 0.00 a04b504 9 F------ 0 300517 250 0 0.00   curtis$ onparams -d -l 1 WARNING: Dropping a logical log file. Do you really want to continue? (y/n)y Logical log 1 successfully dropped.   curtis$ onparams -d -l 2 WARNING: Dropping a logical log file. Do you really want to continue? (y/n)y Logical log 2 successfully dropped.   curtis$ onparams -d -l 3 WARNING: Dropping a logical log file. Do you really want to continue? (y/n)y Logical log 3 successfully dropped. curtis$ onstat -l #(output abbreviated) address number flags uniqid begin size used %used a04b478 4 U---C-L 37 300035 250 7 2.80 a04b494 5 F------ 0 30012f 250 0 0.00 a04b4b0 6 F------ 0 300229 250 0 0.00 a04b4cc 7 F------ 0 300323 250 0 0.00 a04b4e8 8 F------ 0 30041d 250 0 0.00 a04b504 9 F------ 0 300517 250 0 0.00

At this point, we have successfully moved all logical logs into the new dbspace. The following steps are purely a matter of personal preference. I like my logs to be numbered starting with one. In order to do that, I have to add three more and drop the last three, as demonstrated in Example 14-13. The new logs will be given log numbers 1-3. Then we drop logs 7-9 in reverse order. (It's less confusing to delete them in reverse order.)

Example 14-13: Dropping and Adding Logical Logs

curtis$ onparams -a -d llogdbs Logical log successfully added. curtis$ onparams -a -d llogdbs Logical log successfully added. curtis$ onparams -a -d llogdbs Logical log successfully added. curtis$ onparams -d -l 9 WARNING: Dropping a logical log file. Do you really want to continue? (y/n)y Logical log 9 successfully dropped.   curtis$ onparams -d -l 8 WARNING: Dropping a logical log file. Do you really want to continue? (y/n)y Logical log 8 successfully dropped.   curtis$ onparams -d -l 7 WARNING: Dropping a logical log file. Do you really want to continue? (y/n)y Logical log 7 successfully dropped.   curtis$ onstat -l #(output abbreviated) address number flags uniqid begin size used %used a04b424 1 A------ 0 300611 250 0 0.00 a04b440 2 A------ 0 30070b 250 0 0.00 a04b45c 3 A------ 0 300805 250 0 0.00 a04b478 4 U---C-L 37 300035 250 16 6.40 a04b494 5 F------ 0 30012f 250 0 0.00 a04b4b0 6 F------ 0 300229 250 0 0.00

Recommendation: Mirror Essential dbspaces

If you have the space, it is best to mirror the entire database. However, many places do not have that luxury. It doesn't take up much disk space to mirror the essential dbspaces, though. The essential dbspaces are those that contain the sysmaster database and the physical and logical logs. Of these, the most essential dbspace would be the one that contains the logical logs. That is because all other dbspaces can be recovered up to the point of failure--if the logical logs are intact.

When creating dbspaces, they can be set up as mirrored dbspaces. (This is demonstrated in the previous example about moving your logs to dedicated dbspaces.) However, it also can be done after the dbspace is created. Since we've already covered creating a mirrored dbspace, this section covers turning on mirroring for an existing dbspace. Assume for this example that we have a dbspace called plogdbs, and that we wish to mirror it to /informix /physlog_mirror.dbf. We would run the commands shown in Example 14-14.

Example 14-14: Adding Mirroring to an Existing dbspace

$ onspaces -m plogdbs -f /informix/physlog_mirror.dbf -y $ onstat -d # (output abbreviated) a12a728 3 2 3 1 M informix plogdbs (The M flag above says that the plogdbs is mirrored.) address chk/dbs offset size free bpages flags pathname (It lists two chunks below. One is primary and the other secondary.) a12a358 2 2 0 5000 2447 PO- /informix/physlog.dbf a12a508 2 2 0 5000 0 MO- /informix/physlog_mirror.dbf

Recommendation: Leave Room for the Logs

Remember that the logical logs are absolutely essential for maintaining the integrity of your database. A common problem with many installations is that the logs become full. This is caused by either not backing up the logs or a long transaction that keeps too many logs open.

Back up your logical logs

Informix instances must have their logical logs backed up. If you do not back them up, all logical logs eventually become full. Once this happens, all activity on the instance stops, and you are forced to do a logical log backup. The problem is that backing up the logical logs requires creating more logical log entries. If all logs are full, you can't back up the logs.

The only option that remains is a special tool that can be run only by Informix support. They have to be able to dial or telnet into your system. They then transfer the file to your system, run it, and remove it. They do not allow you to keep the tool, and there is no way for you to get it without Informix accessing your system. In short, this is not a situation you want to be in.

Informix recently introduced a new parameter that prevents logical log entries from becoming full. Unfortunately, it is turned off by default.[2] The LBU_PRESERVE parameter (found in the onconfig file) needs to be set to 1 to reserve the last logical log for administrative activity, such as logical log backups.

Long transactions

Another "log full" problem is caused by a long transaction. A long transaction occurs when a user or application starts a transaction and does not commit it within a reasonable time. The problem with this is that a log cannot be freed if it contains an uncommitted transaction. Suppose there are 10 logical logs, and a long transaction is started in the first log. Once the first log is full, Informix moves on to the next log, but it cannot free the first log for reuse until the long transaction is completed and that log file has been backed up. If the transaction were allowed to remain open until the 10th log was full, there would be no more free logs. One transaction has managed to span the entire set of logical logs. This is why it is called a long transaction. One might think that the transaction could be rolled back. However, rolling back a transaction requires creating more logical log entries. No more logical log entries can be created because there are no more free logical logs. Does that sound familiar?

The long-transaction problem is why they introduced the high-water-mark parameters that are found in the onconfig file. The first is the long-transaction high-water mark (LTXHWM), and its default value is 50. Once the percentage of full logical logs reaches this value, the transaction is automatically aborted and rolled back. However, since other transactions are allowed to continue, the logical logs are still in danger of filling up. The second parameter, the long-transaction exclusive-access high-water mark (LTXEHWM), is designed to protect the instance from this; its default value is 60. If the percentage of full logs reaches this value, most database activity is suspended and the transaction is given "exclusive access" to the remaining logical logs so that it can complete its rollback. The values of 50 and 60 are the new default values for these parameters, and oninit complains if you attempt to initialize an instance with values higher than these.

In summary, leave the LTXHWM and LTXEHWM values where they are and turn on LBU_PRESERVE by changing its value to 1 in the onconfig file. For more details about these values, consult the IDS Administration Guide.

Which Backup Utility Should I Use?

Before examining how to perform Informix backups, we need to understand the various backup options that are available to an Informix DBA. There are three backup utilities available with Informix: onbar, onarchive, and ontape.

A Quick History of onarchive, onbar, and ontape

Around 1993 and 1994, very large Informix databases started to appear. (Back then, "large" meant anything greater than 100 GB.) The only utility that Informix customers had was ontape. ontape's one big flaw is that it is single-threaded. How do you back up 100 GB to a single device, especially when the devices available could back up only at rates ranging from 500 KB/s to 3 MB/s? Informix customers started screaming for a multithreaded backup system. (I was one of them.)

Informix, in an attempt to meet these demands, purchased the rights to a third-party program that became known as onarchive. Its syntax was arcane, complex, and completely unlike any other command on Unix. (It wasn't as complex as sendmail, but it was close!) The forward-slash method of passing options to a command seemed bizarre to most Unix system administrators, and it came with a manual that is almost the size of this book.

I was there

By the time in my career that I met onarchive, I had learned dozens of products just by browsing the manuals. I read the onarchive manual cover to cover four times and attended the Informix onarchive class. I was still confused. The most disconcerting part of the class was how the instructor kept talking about this thing called onbar that would soon replace onarchive.

I remember having 15 open bug reports for onarchive at one time. I remember calling support and getting the feeling that they didn't want to support the product any more than I wanted to use it. I remember spending months configuring and testing our onarchive setup. Informix came and saw what we did and thought it was one of the most impressive onarchive installations they had ever seen.

Then we tested the restore. It didn't work. No matter what we did, we couldn't restore the instance that we had backed up with onarchive. It just wouldn't work. Suddenly we received approval to buy a much faster tape drive and go back to ontape.

As a result of the input that they received from the field, Informix began work on a replacement for onarchive almost immediately. onbar was released with Version 7.21. Unfortunately, this created a problem. Users who need the multithreaded capabilities of onbar were required to purchase an interface to their storage manager--at a cost of more than $5000 per system. To avoid that cost, people who needed a multithreaded backup product were still being driven toward onarchive ; this product, despite its incredible complexity and horrible reputation, remained the only choice available for some customers.

Informix wanted to fix this problem, so they negotiated an arrangement with Legato Systems that allowed them to bundle a scaled-down version of Legato NetWorker with IDS 7.3. This product is known as Informix Storage Manager, or ISM. It allows you to send up to four data streams simultaneously to four storage devices (i.e., one data stream per drive). This is enough parallelism to back up a relatively large instance. Assuming that each data stream can "stream" each of four DLT 7000s, that is a total potential throughput of 40 MB/s, 144 GB/hr, or 1.1 TB in an eight-hour period. (Unfortunately, on many systems, you'll need more than one thread to stream a device; this reduces the overall effective throughput of an ISM/onbar combination, but it is still going to be faster than ontape.)

Don't use onarchive

If you downloaded both the Archive and Backup Guide and the Backup and Restore Guide from http://www.informix.com/answers, you might get the impression that there are three viable options for backing up Informix databases: ontape, onbar, and onarchive. The manuals do explain that onbar is designed to work with storage managers, and ontape and onarchive are not. What the documentation does not explain is why two products (ontape and onarchive) that appear to perform the same function exist. Further, you might get the impression after reading the manual that onarchive is the way to go. It's not.

The release of ISM means that there remains no valid reason for configuring a new backup system using onarchive. If you can live without multithreaded backup and recovery, use ontape and infback.sh (or something similar). If you need multi-threaded capability or want some other advanced feature, use onbar and ISM. If you can afford an Informix server that needs more than four backup threads, you can afford a full-featured storage manager and its interface to onbar. If you're having trouble convincing your management to spend that money, show them this chapter.

Informix plans to phase out onarchive over time. You can already see references that say things like "onbar is the preferred solution for 7.3," and "ontape and onarchive are supported for now." The only reason that they don't drop onarchive today is there are still people using it. I wouldn't be surprised if a future release of Informix configures onarchive in such a way that you would no longer be able to make backups with it. The restore functionality will be left for those who still have onarchive backup tapes. (I doubt that Informix will drop ontape, though, because it's so easy to use and support. They aren't adding any new features to it, but that could be considered a feature in itself.)

Some people reading this section may disagree with my assessment of onarchive 's value. They like the flexibility that such a complex product offers them. They don't care that they need to read almost 500 pages of documentation just to figure out how to back up their Informix database. They've figured it out now--and they don't want to have to figure out another product. If that is how you feel, please know that you are in the minority. The whole reason that onbar exists is that the Informix user community was in an uproar about onarchive. Remember also that a product that is difficult to use is difficult to support. Informix is probably doing everything it can to put this utility behind them. If you're using onarchive today, you should seriously consider looking at onbar as soon as possible or going back to ontape if you can. Trust me: onbar is as easy as it gets.

Pick a Utility, but Not Any Utility

Here is a quick summary of the three utilities available for Informix backups:

onbar

onbar is the latest in Informix backup technology and is used only for interfacing with storage managers like the Informix Storage Manager (ISM). (onbar is covered in "Physical Backups with a Storage Manager: onbar.") If you are running Informix 7.3 or greater, onbar offers enhanced functionality and flexibility.

onarchive

onarchive was Informix's first attempt at an enhanced product, but its incredible complexity caused it to be just a stopgap measure until onbar was available. onarchive will not be supported in the future and therefore should not be used as the basis for any new backup system. (See "Don't use onarchive," earlier in this chapter, for more information.)

ontape

ontape is Informix's oldest backup utility, but it's still going strong. It does not interface with storage managers, therefore it is the easiest and best way to perform physical backups without a storage manager.

In summary, if you do not have a storage manager, you really should be using ontape if you can. If your database is really large, and you need the multithreaded capabilities of onbar, you should either purchase a third-party storage manager or upgrade to a recent version of Informix that includes a free (albeit scaled-down) storage manager. Once you have done that, you can use the advanced functionality of onbar. I would not advise designing any new backup systems around onarchive. It is too difficult to learn, and it is being phased out.

Physical Backups Without a Storage Manager: ontape

If you do not have access to a storage manager, ontape might be the perfect tool for you. This section covers ontape 's basic usage, then shows you how to automate it using shell scripts. Many environments without storage managers use ontape to automate their backups.

Despite its age, many people continue to use ontape for several reasons:

Informix version

ontape does not require a storage manager. If you are not running Informix 7.3 or greater, you do not have ISM. That means that you will need to purchase a storage manager and its interface to onbar. If you are running a version older than Informix 7.21, you don't even have access to onbar.

Cost

ontape is free. Although onbar itself is free, it will cost as much as $7000 per host [3] to purchase the interface to onbar, even if you already have one of the commercial storage management applications (e.g., those discussed in Chapter 5, Commercial Backup Utilities).

Ease of use

ontape is a breeze to learn. It has four required options, -s for archive,[4] -c for continuous backups, -a for automatic backups, and -r for restore. That's it. In contrast, onarchive is a nightmare of complexity. Its manual spans 436 pages, compared to ontape 's 49 pages or onbar 's 125 pages. onbar is much simpler to use than onarchive, but adding a storage manager into the picture still makes it more complicated than ontape. (The slight increase in complexity does come with greatly increased functionality, though.)

ontape is easy to use. Even more importantly, ontape recoveries work! ontape was developed many years ago, before the advent of backup automation in the Unix world. It was ahead of its time with multilevel backups, but it does have one design flaw: it was designed to be run by an operator on a console. It prompts the operator for information even during normal operations. It was not designed for automated backups, but that doesn't mean you can't use it that way. Many scripts have been written that answer its prompts, providing a level of functionality never envisioned by the original programmers. Before looking at how to automate the use of ontape, though, we cover its basic usage.

Configuring ontape

Prior to running ontape, you must configure some parameters using the onmonitor utility. This utility changes these parameters in the file $INFORMIXDIR/etc/$ONCONFIG, where $INFORMIXDIR is a required environment variable specifying the Informix directory, and $ONCONFIG is a required environment variable specifying the name of the Informix configuration file. It is an editable text file, but not all changes will go into effect until you restart the instance if you edit it manually. Specifically, the LTAPE parameters must be changed using onmonitor if you want them to go into effect immediately. To configure ontape, run the onmonitor utility, choosing menu option A (for Archive) and T (for Tape Parameters). (An ontape backup of an instance is referred to as an archive.) This takes you into a screen that looks like Figure 14-1.

Figure 14-1. onmonitor tape parameters menu

The values in Figure 14-1 should be changed to ones appropriate for your environment. The appropriate values are also listed in Table 14-1.

Table 14-1: Tape Configuration Values

Variable in the $ONCONFIG file	Value in onmonitor Screen	Purpose
TAPEDEV	Tape Device	Device to which archives (backups) are sent
TAPEBLK	Block Size	Block size of tape device in kilobytes
TAPESIZE	Tape Size	Size of tape in kilobytes
LTAPEDEV	Log Tape Device	Device to which logical log backups are sent
LTAPEBLK	Block Size	Block size of log device in kilobytes
LTAPESIZE	Tape Size	Specifies the tape device for logical log backups.

These values usually are set once and should be changed only if your configuration changes. One of the best ways to do this is to make the device name to which you are backing up a symbolic link to the real device. For example, you can specify that the tape device is /informix/tapedev and make that a symbolic link to /dev/rmt/0cn. That would allow you to change the actual tape device without accessing onmonitor. Although optimum block size settings are determined by your tape drive manufacturer, a value of 64 or 128 is quite common. You should experiment with different values to see how they affect backup performance.[5]

I like to set the TAPESIZE parameter to something slightly larger than the instance size but less than the actual tape size. For example, suppose you have a 16-GB tape but a 4-GB instance. One option would be to set the tape size to 15 GB and not worry about changing it again for a long time. You also could set it at 5 GB, and then watch the logs to make sure that backups complete. Once backups start failing because the tape is filling up, change the TAPESIZE parameter to 8 GB. This method lets you to see how big the instance is growing over time, allowing you to document when the instance is going to exceed the size of your 16-GB tape drive so you can purchase a bigger tape drive long before it ever becomes a problem. Setting the TAPESIZE to 15 GB would result in successful archives for a long time, followed by a failure once the database got bigger than 15 GB. Unfortunately, though, you would have no idea when it is going to reach 16 GB and have very little time to purchase a bigger tape drive.

Some DBAs prefer to set the TAPESIZE parameter to the size of the tape and then chart the growth of the instance with sysdbspaces. You should do whatever works best for you!

TIP: It should be noted that Informix backups can span tapes. It will prompt the operator for a second tape if the first one fills up. However, if you are automating your backups with shell scripts, the prompt probably will not get answered, and the backup will fail.

Tape or Disk?

Informix assumes that the devices that you specify for TAPEDEV and LTAPEDEV are tape drives, as evidenced by the variable names. It is common, however, to use files on disk instead. To back up to disk, Informix needs an empty disk file to which it can write. This usually is done with the touch command. For example, the previous example window shows the Log Tape Device set to /informix/logical/crash.log. This is a disk file created by issuing the following command:

$ touch /informix/logical/crash.log

What's wrong with tape?

One of the most difficult things about ontape 's continuous backups is that they are designed to back up the logical logs to a single tape. Once the tape fills up, the logical log backups stop, and the logical logs begin to fill up. If all logical logs fill up during this time, all activity on the instance is suspended. This means that the DBA must constantly watch several tape drives, swapping tapes throughout the day. Nor can he tell how full a tape is getting. (A DBA could write a separate script to monitor how many logs have been written to a device and use that to estimate how full the tape is getting. However, different compression ratios may cause this estimate to be way off.) It doesn't help that Informix requires a separate tape device for each instance. What does a DBA do with a machine that has several Informix instances on it?

WARNING: If you are archiving to tape, make sure that your scripts rewind the tape before starting an archive. Failure to do so may result in unexpected results!

Backing up to disk can help

The alternative is to back up the logical logs to disk. All of the logical log backups on a single host can share a single /logical filesystem. For each Informix instance, you'll need a script that:

1. Stops continuous backups for a moment
2. Moves the current logical log backup file to a date/timestamped file
3. Creates another logical log backup file
4. Starts continuous backups again

Once this has been done, the only management required is monitoring the available space in the filesystems where you're sending your logical logs and deleting old logical log files that have been backed up.

Is it OK to back up to disk?

Some DBAs feel that the logical logs are extremely important and that backing them up to disk places them at risk. Disks can fail. If the disk drive that contains your logical logs fails at the same time as the disk drive that contains your database, you are out of luck. They feel it is safer to get the backups out to tape.

There are strong arguments for backing up to disk. Proponents say the value added by backing up to disk far outweighs the added risk. They also say that backing up to tape has risks as well. Their reasoning is as follows:

• Backing up to disk allows complete automation. No one has to remember to swap tapes, no one needs to be available to swap tapes, continuous backups just happen. It's always a good thing to automate backups. Why have operators swapping tapes when a simple shell script will do the job--day in and day out?
• The slowest part of an Informix database restore is the reading of the logical logs. Placing the logical logs on disk significantly increases the speed of this critical path of a restore.
• The all-your-eggs-in-one-basket problem is solved by using remote devices. For example, elvis logical logs can back up to a filesystem on apollo--all you need to do is specify apollo:/logical/servername/logfile as the logical tape device. This significantly reduces the chance that both the logical tape device and the database instance will be damaged at the same time.
• The proponents of logical log backups to tape also would contend that backing up to disk would not protect the data from a fire that destroys both systems. Don't forget that a fire also would destroy any tapes that are in the drives.
• Tape is safer than disk, right? The quicker the logical logs get to tape, the safer they are. Performing logical log backups to disk actually makes this easier. Why is that? If the logical logs are being backed up to disk, logging can be stopped and started throughout the day, creating multiple log files. These log files can be immediately backed up using a homegrown or commercial backup system. They can even be backed up or copied multiple times. This is something that is impossible with tape backups. As long as the tape device is open by Informix, it can't be accessed. Talk about putting all your eggs in one basket! Instead, you could easily have multiple copies of the disk backup, as well as tape copies that can be sent off-site without disturbing Informix.

Doesn't Informix try to rewind the tape?

A common misconception is that Informix rewinds the tape before and/or after a backup or restore. This is what is commonly used as an argument against disk backups. It also is commonly used to explain why you are not supposed to put more than one archive on a tape device.

Informix does not rewind the tape at any time. It expects the tape to be rewound already. This allows you to back up to disk or to put multiple archives on a single tape by using the no-rewind device. Recent changes to the Informix FAQ and discussions in the comp.databases.informix newsgroup have confirmed this.

If you do back up to disk, all "tape" management is up to you. ontape will not know that the backup really went to disk. It is up to you to track that information and to manipulate disk devices during a restore so that the "tape" is always where ontape expects it to be.

WARNING: However, if you do put multiple archives or log backups on a tape by using the no-rewind device, a restore from those backups will not work. This is because Informix opens and closes the tape several times during a restore, expecting it to rewind. If you have multiple backups on a tape, you must copy each backup to a separate tape in order to do a restore.

The two-gigabyte file size limit

There is one downside to backing up to disk. Informix still has a 2-GB file-size limit, so you will not be able to create a disk archive of a database that results in a file larger than 2 GB. There are two options that you can explore to get around the 2-GB file-size limitation:

• Use expect (or the expect module of perl ) to respond to the prompts from ontape.
• Don't use ontape ; use onbar.

Use expect to swap tapes

The first option is to use expect or perl to respond to the multiple prompts from ontape. You then could set the TAPESIZE parameter to something just short of 2 GB. Once ontape reaches this size, it will prompt you for a second tape. You then could use expect to move the first file from its original location, create another file, and respond to ontape 's prompt for a second tape. This method will allow you to make an archive as large as the available space on the filesystem.

Use a storage manager and onbar

The second option, and one you should seriously consider, is to use Informix Storage Manager (or some other storage manager) and onbar. This combination would work around the 2-GB limit, still allow you to back up to disk if you wish, while providing you with the features that are available only in onbar.

I'll Use Disk for Continuous Backups . . . I remember once managing five or six Informix instances and having to run down the hall into the server room to swap tapes because a logical log tape was full, causing our production Informix instance to hang. (We used TK70s for this.) I had to open five or six windows every time we rebooted an instance, just so we could start continuous backups to tape. Then I remember the day that we wrote rclogs.sh. Suddenly, we had a program that would start logical logging for us. We could reboot a host, and continuous backups would just magically turn on. We went from a few instances to well over 50 and never had to swap a tape again. (We also never had to explain to an angry manager why her Informix database was temporarily unavailable during the busiest time of her day.) I can't imagine going back to the old way.

Backing Up the Instance

The Informix term for a backup of an instance is an archive. First, you must configure TAPEDEV as explained in "Configuring ontape," earlier in this chapter. Then to perform an archive, run one of the following commands:

$ ontape -s            # on a 5.x instance
$ ontape -s <-Llevel>  # on a 6.x and higher instance

You may specify a 0, 1, or 2 for level. Specifying a 0 performs a full archive. Specifying a 1 causes Informix to back up anything that has changed since the last level 0. Specifying a 2 causes Informix to back up anything that has changed since the last level 1 or 0. If you do not specify the level using the -L option, ontape prompts for it. ontape also prompts you to hit Enter once you have mounted the tape.

If ontape detects that it has reached the end of a volume, it prompts for another tape. ontape supports three levels and multivolume archives but cannot use more than one device at a time; you must use the same device throughout the archive. Once the volume on that device is full, you must swap that volume and answer ontape 's prompts for the next tape. If you are backing up to disk, this means that you have to move the original backup file to another location.

Backing Up the Logical Logs

Recovering an Informix instance from an archive recovers the database to the point in time that the archive was taken. In order to redo any transactions that have occurred since then, you also must restore the logical logs. In order for the logs to be available during a restore, you must back them up. You can:

Back up the logs continuously

ontape offers a particular type of logical log backup, referred to as "continuous backups," by using the -c option of ontape. ontape runs continuously in the foreground, appending logical logs to the storage media as it becomes full.

Back up the logs manually

A "manual backup" is a one-time backup performed by the -a option of ontape. It overwrites the storage media with all full logical logs and the current logical log.

Back up the logs with a script and the ALARMPROGRAM parameter

As of 7.xx, there is a new way to back up your logs. You can use the ALARMPROGRAM parameter to automatically run a shell script for you when a log is full or is manually closed.

Elect not to back up the logs

You could decide not to back up the logical logs. This is done by setting the backup device to /dev/null.

Backing up logical logs continuously

The historically preferred way to back up your logical logs is to use the continuous backup option of ontape. When continuous backups are running, logical logs are automatically backed up to the specified device as soon as they are full. The logs are appended to the backup device, which is held open by Informix as long as continuous backups are running.

To perform a continuous backup of the logical logs, run the following command:

$ ontape -c

This command prompts you to hit Enter once you have mounted the tape; from then on, it performs a continuous backup of the logical logs to the storage media until you enter Ctrl-C or kill the process. It continues to append logical log backups to a particular piece of media until the media is full. A tape is "full" when ontape can longer write to it, or the amount of data that has been written to it exceeds the value specifid by the TAPESIZE parameter in the onconfig file.

Backing up logical logs manually

Another method of backing up logical logs is to perform a manual backup only when required. This method differs from continuous backups in two ways. First, it is a onetime backup that backs up full logs, along with the current logical log. It opens the device once, writes these logs to it, and closes the device. Second, and perhaps more important, a manual backup overwrites any backups found on the storage media. To perform a manual backup of the logical logs, run the following command:

$ ontape -a

There are some environments that have written automation scripts around this command. They monitor the logs to see how many are full. Once a certain percentage of them are full, they run the ontape -a command to back up the logs. A script that operates in this way is simulating the continuous backup operation of ontape -c. It requires more tape or disk space than the continuous backup method, because it backs up the current log whether it is full or not, whereas continuous backups back up logs only when they are full.

There is nothing wrong with simulating continuous backups using scripts and ontape -a, and it is slightly easier to automate than ontape -c. This is exactly what the ALARMPROGRAM is for. You specify the script that Informix should run whenever a log is full.

Electing no backup of logical logs

The least preferred method of backing up the logical logs is to not back them up at all. You do this by setting the LTAPEDEV value to /dev/null, which causes Informix to mark the logs as backed up as soon as they become full. (See the earlier explanation under "Configuring ontape" for details on how to do this.) Since no actual backup occurs, this is not an option to use if you will need point-in-time recovery. Using this option allows you to recover only from your latest archive. You will not be able to redo any transactions since the time of that archive. This option typically is used only for development instances or instances in which data is loaded only via batch loads. If you are considering using this option for a batch-load instance, you should experiment with this option and with the continuous backup option. Find out the impact of both on your environment, including performance during loads and recovery speed.

TIP: To Oracle DBAs: This is the Informix equivalent to running an Oracle database in NOARCHIVELOG mode.

Automating Archives with ontape:
the infback.sh Utility

infback.sh is a shell script that automates performing archives with ontape. Before examining how infback.sh works, we explain why automating ontape backups is difficult.

Why automating ontape is difficult

The ontape utility is simple and flexible, but automating it has always been somewhat difficult, because it is a little picky about how it receives the answers to its questions. For example, some versions of ontape do not accept a typical "here document,"[6] as shown in Example 14-15.

Example 14-15: Calling ontape with a Here Document

LEVEL=0 ontape -s <<EOF >/tmp/logfile $LEVEL   EOF

ontape now accepts the use of a -L level argument, but it still requires you to respond to a prompt after executing the command. ontape can still give you difficulties if you use the syntax in Example 14-15.

The method shown in Example 14-15 is often mentioned in FAQs and other books, but it does not work on every version of Informix. The method that does always seem to work is shown in Example 14-16.

Example 14-16: A More Reliable Way to Call ontape

LEVEL=0 echo "$LEVEL   "| ontape -s | head -100 > /tmp/logfile

There are two differences between the methods shown in Example 14-15 and Example 14-16. The first difference is the way in which the answers are passed to ontape ; the second method seems to be a little more robust. The second difference between the two methods is the addition of the head command in the second one. The head command is not required but is a good precaution in case the backup device becomes full. Here's why: once ontape detects that the backup device is full, it prompts for a second tape. It is not very easy to provide a second tape and answer this prompt without using a program such as expect.[7] (There is a way to answer prompts like this by redirecting stdin and stdout to named pipes, but that can be really tricky with ontape. ontape prompts repeatedly for the second tape and things get pretty ugly very quickly.) The head command keeps this repeated prompting from happening, since it truncates the output to 100 lines, but once the 100 lines have been reached, ontape aborts.

How infback.sh works

infback.sh is a shell script that automates performing archives with ontape. (As discussed earlier, its one limitation is that it cannot handle multivolume archives.)

infback.sh consists of several files:

infback.sh

This is the main backup program that looks at the configuration files to decide how and what to back up.

rclogs.sh

This program is another main program that automates continuous backups of the logical logs using ontape -c.

infback.conf

This file is used by infback.sh to specify how, when, and what to back up.

inftab

This file lists all Informix instances on a machine. It is used by dbstart.informix.sh, rclogs.sh, and infback.sh to determine what instances are on the machine.

localpath.sh, rempath.sh, config.guess

These files accompany these other programs and assist them in determining what commands to run on various Unix platforms.

infback.sh is based on the same logic as Oracle's oraback.sh and Sybase's syback.sh. infback.sh:

• Uses a global tabulation file (inftab) to list all Informix instances
• Uses a global configuration file (infback.conf5 ) to configure backups
• Automatically performs full or incremental backups of all instances found in inftab
• Optionally performs full or incremental backups of any instances listed on the command line
• Can write archives to disk or tape
• Sends email, notifying of the success or failure of the backup
• Backs up databases that reside on raw partitions or filesystems

infback.sh can be called in one of three ways:

With no arguments

If you call infback.sh with no arguments, it decides what to back up and what device to use by looking at infback.conf.

With arguments specifying backup parameters

You can tell infback.sh which device and level to use by giving them as arguments. If you do this without giving it a list of instances to back up, it will back up any instances it finds in inftab. If you specify instances to back up, it will back up only the instances that you specify.

With at as the first argument, with or without backup parameter arguments

Specifying at as the first argument causes infback.sh to look at infback.conf to determine what time to back up. It then schedules an at job using the same arguments given after the at argument. (That means that the scheduled backup will figure out what and how to back up via either infback.conf or the arguments that were given.)

When infback.sh runs

One of the first things that infback.sh does is to check if the word "skip" is in field 2 of infback.conf. If so, it skips the backup once and removes the word "skip" from infback.conf. (This allows a DBA to manually skip tonight's backup, but does not allow the DBA to accidentally disable backups forever.)

If infback.sh determines that it is not supposed to skip the backup, it then needs to determine what instances to back up ($INSTANCE_LIST ), what level backup to run ($LEVEL ), and what device to use ($DEVICE ). How infback.sh does this depends on which arguments (if any) it receives.

If infback.sh is called with no arguments

If called with no arguments, like this:

$ infback.sh

infback.sh looks at the third field in infback.conf to determine what level of backup to run. That field specifies the full backup day. If today is the full backup day, then it performs a level-0 archive. If it is not today, it performs a level-1 archive. infback.sh then looks at fields 6 and 7 of infback.conf to get the name of the device server and device to which it should back up. Finally, it looks at inftab for a list of instances to back up.

If infback.sh is called with arguments specifying backup parameters

If infback.sh is called with arguments as follows:

$ infback.sh level [device|host:device] [instancea instanceb ...]

it will determine its level and device from the arguments. If it also receives a list of instances to back up, it will use that as the list of instances. If not, it determines the list of instances to back up by listing each instance list in inftab. The arguments and their possible values are as follows:

level

This should be a valid level for an Informix archive (0, 1, or 2).

device | host:device

This should be a valid device name or directory name. It also can be hostname : device (e.g., apollo:/dev/rmt/0cbn).

[instancea instanceb ...]

These arguments are optional. You may specify one or more Informix instances to back up. Specifying no instances causes infback.sh to back up all instances listed in inftab.

If infback.sh is called with at as the first argument

If infback.sh is called with at as the first argument (as it would be called in cron or at):

$ infback.sh at 

infback.sh sets the $TIME variable so that the backups take place at a specified time. If it is a level-0 backup, infback.sh sets the $TIME variable to the value in field 4. If it is a level-1 backup, it sets the $TIME variable to the value in field 5. infback.sh then schedules an at job that will perform the backup at the time specified by $TIME. If there were other arguments given after the at argument, those arguments are passed to the scheduled run of infback.sh. When the at job runs infback.sh, it will then determine what and how to back up based on the arguments (if any) that it was given.

The backup begins

Once the actual backup is supposed to run, infback.sh checks to see if the database is up. If it is not, infback.sh complains and exits. If it is up, infback.sh checks to see if there is enough space to make a change to the onconfig file.[8] What it does next depends on whether you specified that compression should be used.

If there is a value specified in infback.conf for $COMPRESS, infback.sh creates a named pipe called $INFORMIXDIR/$INSTANCE.level.$LEVEL.fifo, and changes TAPEDEV to that filename. It does this using onmonitor, so the change will be effective immediately. After creating the named pipe and telling ontape to use it, it calls compress and tells it to read from the named pipe, compress the data, and write it to the location specified as the original device. infback.sh then calls either ontape or tbtape, depending on the version of Informix you are running,[9] passing it the appropriate level. It checks the status of the backup command and, if backing up to disk, compresses the backup file.

If there is no value specified for $COMPRESS, infback.sh sets TAPEDEV to $DEVICE_SERVER:$DEVICE_FILE using onmonitor. It then calls ontape to perform the archive to the specified device.

Installing infback.sh and its configuration files

If you'd like to use infback.sh, you'll need to install the files in the appropriate directory, configure inftab and infback.conf, and customize infback.sh to your environment.

Copy infback.sh, localpath.sh, rempath.sh, and config.guess into the same directory (e.g., $INFORMIXDIR /local/bin) and make sure that they are executable. Copy inftab and infback.conf into a directory to which Informix can write (e.g., $INFORMIXDIR/etc). infback.sh needs to be able to write to this directory for the "skip" feature to work. inftab and infback.conf can be placed in the same directory as the other files, but they do not need to be.

Editing the infback.sh configuration files

If you have not already done so, you should configure inftab for use on this system. For details on how to do that, see "Edit the inftab file" earlier in this chapter.

Editing infback.conf

infback.conf is the configuration file for infback.sh and rclogs.sh. It should contain one line for each machine in the following format:

hostname.master::full_day:full_time:inc_time:device_server:archive_dest:allow_ids:compress:mail_ids

hostname.master

Replace hostname with the base hostname. For example, if the hostname is apollo.domain.com, this field should contain apollo.master.

full_day

This is the day of the week that full backups should run. (All other days of the week will receive level-1 backups.)

full_time

If using the at argument, this is the time that the program should schedule level-0 backups. Suppose that it says 1900, and the full backup day is Mon. At 1500 on Monday, there is a cron job that runs infback.sh at. That will see that Monday is a full backup day and schedule an at job to run at 1900.

inc_time

If using the at argument, this is the time that the program should schedule level-1 backups.

device_server

Set this to the hostname of the server that contains the backup device. If it is the same as this host, enter the base hostname only (just like in the first field). For all other hosts, use the Fully Qualified Domain Name (FQDN) if it is appropriate.

archive_dest

This should be set to a device or directory that will contain all archives. If it is a directory, make sure that it is writable by Informix. Also, if it is a directory, infback.sh will create a subdirectory for each instance inside this directory.

allow_ids

This is a |-separated list of user IDs that are allowed to run infback.sh. This is usually only Informix.

mail_ids

This is a comma-separated list of user IDs that should receive mail about the success of infback.sh.

Editing infback.sh

There are a few minor changes that you need to make to the infback.sh file itself. Locate the following variable and function declarations in the top of the script,