Informix is this author's favorite database platform to backup and recover

Automating Informix startup

One may wonder why we're discussing startup in a chapter dedicated to backup. The reasons are actually very simple. We have already discussed that there is no global configuration file for Informix like there is for Oracle. This makes a lot of things more difficult, one of them being backups. When writing a shell script to backup all Informix instances on a given machine, how do you know what instances to backup? You can't ask Informix, because probably does not know about the other instances. I would like to suggest a different method for Informix start-up that would also make backups easier. Let's start with the basics.

In order for an Informix database to be started up after a system reboot, a start-up script must be placed somewhere in /etc. Start-up 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 an Informix startup script in Informix's home directory. However, this creates an extra task for both the SAs and DBAs. The Informix DBAs need to find a SA every time they want to add a new instance to the start-up 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 are also normally in charge of backup, and this is one way to find out that there is a new instance to backup.

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, in the oratab file is in a directory owned by Oracle. The start-up 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 it to that file. This also gives backup scripts a great place to start. For example, oraback.sh 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 for both start-up and backup of Oracle, they will get into the habit of putting new instances there. Otherwise, they will continually have to start the instances manually and will also never get them backed up. With a little practice, Informix DBAs can be shown to do the same thing. I present the inftab:

curtis$ cat inftab

# This file is used by the startup, shutdown and backup scripts

# Provided in "Backup & Recovery Handbook," O'Reilly Publishing

# Author: W. Curtis Preston

# Use a colon, ':', as the field terminator

# Lines that begin with a 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

This onconfig file has several comments, but only one actual configuration line. This line tells the startup scripts that there is in an Informix instance called "crash," its INFORMIXDIR is /informix, and it should be restarted after a reboot. It's onconfig file will be named crash, and its logical logs should go to /informix/logical. (The logical backup destination variable will be used for the backup scripts that will be covered later in this chapter.)

Dbstart.informix.sh

#!/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

WHO=`id | awk -F'(' '{print $2}' | awk -F')' '{print $1}' `

if [ "$WHO" = root ] ; then

su informix -c $0 $*

exit $?

grep -v '^#' $INFTAB \

|while read LINE

STARTUP=`echo $LINE|cut -d: -f3`

TBCONFIG=`echo $LINE|cut -d: -f1`

ONCONFIG=`echo $LINE|cut -d: -f1`

#INFORMIXSERVER and ONCONFIG can be different, so get it from the onconfig file

INFORMIXSERVER=`grep DBSERVERNAME $INFORMIXDIR/etc/$ONCONFIG|awk '{print $2}'`

INFORMIXDIR=`echo $LINE|cut -d: -f2`

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.)"

else

echo "Stopping Informix Instance $ONCONFIG"

[ -f $BINDIR/rclogs.sh ] && rclogs.sh stop

onmode -ky

done

This script acts very much like Oracle's dbstart script. 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 intances. Rclogs.sh, as will be discussed later, automatically starts continuous backups in the background.

An inftab 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 one file that they own, and the system will automatically start up, backup 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.

Backing up the instance

A backup of an Informix instance is often referred to as an archive in Informix documentation. To perform an archive of an Informix instance, run one of the following commands:

$ ontape -s # on a 5.x instance (will also work for 6.x and higher)

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

You may specify a 0, 1 or 2 for the level. Specifying a 0 will perform a full archive. Specifying a 1 will cause Informix to backup anything that has changed since the last level 0. Specifying a 2 will cause Informix to backup anything that is changed since the last level 1 or 0. If you do not specify the level using the -L option, Informix will prompt for it. Informix will also prompt you to ENTER once you have mounted the tape.

If ontape detects that it has reached the end of a volume, it will prompt for another tape. It supports three levels and multi-volume archives, but it 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.

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 must also restore the logical logs. In order for the logs to be available during a restore, you must backup them up. There are three options for doing this. You can back them up continuously, manually or not at all. The preferred method for almost all production environments is the continuous backup method, and development environments usually choose not to backup their logs. Very few environments use the manual method.

Send logical logs to /dev/null

The first (and least preferred) method of backing up the logical logs is to not back them up at all. Setting the LTAPEDEV value to /dev/null causes Informix to mark the logs as backed up as soon as they become full. (See the previous explanation under the heading "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 will only allow you to recover from your latest archive. You'll not be able to redo any transactions since the time of that archive. This option is typically used only for development instances, or instances where data is only loaded via batch loads. If you are considering using this option for a "batch load" instance, you should experiment with this option and the continuous backup option. Find out the impact of both on your environment, including performance during loads and recovery speed. This is the Informix equivalent to running and Oracle database in NOARCHIVELOG mode.

Backup logical logs continuously

The second way to backup your logical logs is to do so continuously. When continuous backups are running, logical logs are automatically backup 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 will prompt you to ENTER once you have mounted the tape. Once you have done this, ontape will not return the prompt to you. It will then perform a continuous backup of the logical logs to the storage media until you enter a <CTL> c or kill the process. It will continue appending logical log backups to a particular piece of media until it is full.

Backup logical logs manually

The final, although rarely used, method of backing up logical logs is to perform a manual backup only when required. It differs from continuous backups into ways. The first is that 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. The second, and perhaps most important, difference is that 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 them up. The problem with this is that it takes longer than the continuous backup method. If continuous backups are running, logs are backed up as soon as they are full. Monitoring the status of the logs and running an ontape -a command would introduce an unnecessary delay into this process.

Automating Archives with infback.sh

The ontape utility is simple and flexible. Automating it has always been a little difficult, though, because it is a little picky about how it received the answers to its questions. For example, some versions of ontape would not accept a typical "here document":

LEVEL=0

ontape -s <<EOF >/tmp/logfile

$LEVEL

EOF

The above method is often mentioned in FAQs and other books, but it does not work on every version of Informix. The method that always seems to work is the following:

LEVEL=0

echo "$LEVEL

"| ontape -s | head -100 > /tmp/logfile

There are two differences between this command and the previous one. The first is the way the answers are passed to ontape. This method seems to be a little more robust. The second difference is the addition of the head command. This is a precaution in case the backup device becomes full. This is because once Informix detects that the backup device is full, it prompts for a second tape. It is not possible to answer this prompt without using expect, so the system continues to prompt for the second tape. Since these prompts are logged to /tmp/logfile, this can result in /tmp running out of space. The head command keeps this from happening, since it truncates the output to 100 lines.

Infback.sh is a shell script that automates performing archives with ontape. (Its one limitation is that it cannot handle multi-volume archives.) It is based on the same logic as oraback.sh and syback.sh:

Uses a global tabulation file (inftab) to list all Informix instances

Uses a global config file (infback.conf) to configure backups

Automatically performs full or incremental backup of all instances listed

Optionally performs full or incremental backup of any listed on the command line

Archives can be performed to disk or tape

Mail-based success or error notification

Backs up databases on raw partitions or file systems

An overview

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

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 it does this depends on which arguments (if any) it receives:

$ infback.sh

If called with no arguments, it looks at the third field in infback.conf to determine what level to run. That field specifies the full backup day. If it is today, then it will perform a level 0. If not, it will perform a level 1. It then looks at fields six and seven of infback.conf to get the name of the device server and device that it should backup to. Finally, it determines the list of instances to backup by listing each instance listed in inftab.

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

If called with these arguments, infback.sh will determine its level and device from the arguments. If it also receives a list of instances to backup, it will use that as the list of instances. If not, it determines the list of instances to backup 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 can also be a 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 backup all instances listed in inftab.

$ infback.sh at

If called with at as an argument (this is what would be in cron or at), the script determines the level, device and instance list based on the other argument it receives (as discussed previously). If it is a level 0 backup, it 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. The script then schedules an at job that will run infback.sh at the time specified by $TIME. The scheduled run of infback.sh will receive the $LEVEL and $DEVICE values as arguments.

The rest of the script is pretty straightforward. It checks to see if the database is up. If not, it complains and exits. If it is up, it checks to see if there is enough space to make a change to the config file. If the device is remote, it sets LTAPEDEV to hostname:devicename. If it is a local device, LTAPEDEV is set to devicename. It uses a here document and onmonitor to do this, so the change will be effective immediately. It then calls either ontape or tbtape depending on the version of Informix you are running, passing it the appropriate level. It checks the status of the backup command and, if backing up to disk, compresses the backup file.

Install the files

Copy infback.sh, localpath.sh, rempath.sh and config.guess into the same directory (e.g. /informix/local/bin). Make sure that they are all executable. Copy inftab and infback.conf into a directory that informix 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.)

Edit inftab

If you have not already done so, edit the file $INFORMIXDIR/etc/inftab. This is the global tabulation file for infback.sh and rclogs.sh, and works basically like Oracle's oratab. 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 are also using dbstart.informix.sh (discussed previously), placing a Y in the third field will cause dbstart to automatically start this instance after a reboot.

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 infback.conf

Infback.conf is the configuration file for inback.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 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

Edit infback.sh

There are a few minor changes that you need to make to infback.sh as well. Locate the following variable and function declarations in the top of the script, and set them to the appropriate value for your environment.

DEBUG

Set this to "Y" if you want to see debug information for all functions (i.e. turns on set -x).

BINDIR

Set this to the directory where you copied the backup programs (e.g. /informix/local/bin)

LOGDIR

Set this to the directory where you want infback.sh to log its information (e.g. /informix/log).

LOG

Set this to the full path of the name of the logfile you want it to use (e.g. $LOGDIR/infback.log).

INFCONF

Set this to the full path of infback.conf (e.g. $INFORMIXDIR/etc/infback.conf).

INFORMIX

Set this to the UID that owns the informix instance.

INFTAB

Set this to the full path of inftab (e.g. $INFORMIXDIR/etc/inftab)

TMP

Set this to the directory you want infback.sh to use for temporary files.

Preback

Anything that is put inside this function will be executed prior to running the backup.

Postback

Anything that is put inside this function will be executed when the backups are finished.

Centralized configuration

Just like oraback.sh, the configuration file infback.conf can be shared via NFS. If multiple servers share the same configuration file, and you are using the at argument, you could make changes to all Informix backups by changing one file. Suppose that Informix backups are currently setup to run a full backup on Sunday at 1900. However, you know that the network will be down this Sunday. Without most home-grown backup scripts, you would need to log into several servers and change multiple cron entries. With a centralized infback.conf file, you would only need to change field three for each server from "Sun" to "Mon." If you know that there will be some intensive processing this evening, you could also skip all Informix backups by putting the word "skip" in field two.

Cron entry

To use all features of infback.sh, create a cron entry that calls infback.sh at a time earlier than the earliest time specified in fields four and five in infback.sh. Since most backups are run after 6 P.M., a common entry would be like the following:

0 17 * * * /informix/local/bin/infback.sh at

This specifies to run the script with the at argument at 5 P.M. This causes infback.sh to look at the infback.conf file to determine what level of backup to run, and what time to run it. It will then schedule an at job for the appropriate time.

The reason for keeping the entry as late as possible is to allow the DBAs to make changes to infback.conf. Since infback.sh does not look at the file until 5 P.M., they can change tonight's backup time or skip tonight's backups by changing the file any time before then.