Monday, July 26, 2010

Informix Backup and Restore - The Bare Minimum


Christian Szell: Is it safe?... Is it safe?

Babe: You're talking to me?

Christian Szell: Is it safe?

Babe: Is what safe?

Christian Szell: Is it safe?

Babe: I don't know what you mean. I can't tell you something's safe or not, unless I know specifically what you're talking about.

Christian Szell: Is it safe?

Babe: Tell me what the "it" refers to.

Christian Szell: Is it safe?

Babe: Yes, it's safe, it's very safe, it's so safe you wouldn't believe it.

Christian Szell: Is it safe?

Babe: No. It's not safe, it's... very dangerous, be careful.


- Marathon Man, 1976


Hopefully you will never need to restore from a backup, but chances are you will and it can be as scary as dental torture.  Disks fail, servers fail, disasters destroy data centers, users accidentally delete or update the wrong data and other generally sucky things happen to the databases we administer.  Day in and day out database backups are annoying little things that suck up I/O resources and storage space only to be discarded when the next backup comes along, but there is no better feeling than being able to restore the last backup and get your system back online with minimal data loss after a failure.  There is no worse feeling than needing a backup and not having one.  The good news is basic Informix backup and restore is pretty simple and straight forward in recent versions of Informix.

I'm going to assume you don't have or don't want to use a tape storage device and a Storage Manager.  If this does not describe you, then this isn't the post for you.  You'll want to take a look at onbar and a Storage Manager like ISM or TSM.  I'm assuming you just want to backup your data to the filesystem and you'll do the due diligence of moving these backup files off site or at least to a different server/disks to provide maximum recoverability.  For this we will use the ontape Informix utility and the (relatively) new feature of automatically backing up and restoring from a directory.

Some of what follows comes from a great blog by Fernando Nunes at Informix Technology that I used as a resource for understanding ontape backup and restore to directories.

There are 3 kinds of backups, System Backups, Logical Log Backups and Critical File Backups.

System Backups

A System Backup is a direct copy of the Informix pages that make up your engine.  This is done with the engine online and with no impact or blocking of users other than the additional I/O strain put on the system to read each page from the engine and write out each page to disk.

There are 3 levels of System Backup that can be taken:
  • A Level 0 backs up every page
  • A Level 1 backs up every page that has changed since the last Level 0 backup
  • A Level 2 backs up every page that has changed since the last Level 1 backup
If you use multiple backup levels, when it comes time to restore you would first restore the last Level 0 then apply the most recent Level 1 backup (if one exists) after the Level 0 and finally apply the most recent Level 2 backup (if one exists) after the Level 1 backup is restored.

Using Level 1 and Level 2 backups can reduce the size of your backups taken and the I/O resources required to write backups to disk (less data to write, we still have to read all of the data), but we're here for the bare minimum so I'm just going to use Level 0 backups and Logical Log backups to keep our data safe.

Using a System Backup to restore data is called a Physical Restore.

Logical Log Backups

If you are not familiar with Logical Logging check out a prior post on logging.

Each logical log should be backed up when it becomes full.  Not only because it will help you recover lost data but because Informix will BLOCK if it needs to reuse a logical log that has not been backed up (remember the logical logs are used in a cyclical fashion) and it will continue to block until the needed logical log is backed up.  Informix does this because it assumes data recoverability is more important than engine availability.  Whether you agree with this philosophy or not it is the way the engine works, so you will need to backup your logical logs to prevent the engine from blocking.

There is a work around, but it is not recommended.  You can set LTAPEDEV to /dev/null in the ONCONFIG file which will disable logical log backups and automatically mark a logical log as backed up when it stops being the current logical log.  If you do this your recovery from a failure will be limited to the data stored in your Level 0, 1 and 2 System Backups.

The logical log backups are used during a restore to replay transactions that have occurred since the last System Backup.  After the Physical Restore of a Level 0 (and optionally a Level 1 and 2) backup you perform a Logical Restore of the backed up logical logs.

Logical Log Salvage

If your engine crashes and needs to be restored there will be at least one logical log that has not been backed up yet, the current logical log.  It would be real nice to be able to backup this logical log before we try and restore so it can be in the list of logical log backups to restore our engine to the most up to date version before the crash.  Well the engineers that implemented Informix were real smart and gave us this ability.  Before a Physical Restore is attempted you will be asked if you want to backup the logical logs and salvage them before we overwrite them with a restore.  This works great as long as the disk that holds the logical logs isn't the thing causing you to restore.  See the logging post about sizing your logical logs appropriately to minimize data loss in this scenario.

Critical File Backups

There are some files that you should backup each time you take a System Backup, these files will help you recreate your Informix install in the event that the filesystem that holds your Informix install and config files becomes unavailable
  • $INFORMIXDIR/etc/$ONCONFIG
  • $INFORMIXSQLHOSTS or $INFORMIXDIR/etc/sqlhosts
  • $INFORMIXDIR/etc/oncfg_*
  • onstat -d output, to give you a listing of the chunk paths if you need to recreate them
Unfortunately there is no Informix utility for this, you should write a script to tar.gz these files and move them somewhere safe each time a System Backup is taken.  Something like this should get you pointed in the right direction.

informix> onstat -d > /home/informix/tmp/onstat.d.out
informix> tar -czf /home/informix/backup/files/criticalfiles.$(date "+%Y%m%d.%H%M").tar.gz $INFORMIXDIR/etc/$ONCONFIG $INFORMIXSQLHOSTS $INFORMIXDIR/etc/oncfg_* /home/informix/tmp/onstat.d.out

Taking a System Backup

We will be using the Informix utility ontape and its ability to backup to a directory for all of our System and Logical Log backup needs.

To enable ontape System Backups to a directory, modify the ONCONFIG parameter TAPEDEV to a directory name that is owned by user informix and group informix and has read, write, execute permissions for user and group.  This is where the System Backups will live.

informix> mkdir /home/informix/backup
informix> mkdir /home/informix/backup/system
informix> chmod 770 /home/informix/backup/system
informix> vi $INFORMIXDIR/etc/$ONCONFIG

TAPEDEV /home/informix/backup/system

The most recent System Backup will have a name of DBSERVERNAME_SERVERNUM_L[0, 1 or 2].  A Level 0 backup for our blogsvr01 engine will be /home/informix/backup/system/blogsvr01_0_L0

Each time you execute a new System Backup the old backup is renamed with a timestamp inserted between SERVERNUM and L[0, 1 or 2]

A listing of all the backups of my blogsvr01 engine looks like this

blogsvr01_0_20100723_133319_L0
blogsvr01_0_20100725_135325_L0
blogsvr01_0_L0

To actually take a Level 0 backup

informix> ontape -s -L 0 -d

-s tells ontape we want a System Backup, -L 0 asks for a Level 0 (-L 1 and -L 2 for Level 1 or 2 backups) and -d says we're backing up to a directory and prevents ontape from prompting us for any input.

I recommend executing this in a script that also backs up the critical files via cron or some other scheduling mechanism, simply remembering to take a backup every day or week and kicking it off manually isn't good enough.

Taking Logical Log Backups

There are multiple ways to use ontape to backup logical logs to a directory, but I think the best way is to use the ALARMPROGRAM, a script called by Informix when events occur, to back them up as they fill.

Your ONCONFIG ALARMPROGRAM parameter should already be set to $INFORMIXDIR/etc/alarmprogram.sh, all we have to do slightly modify this IBM supplied script and set LTAPEDEV in the ONCONFIG to a directory.

informix> mkdir /home/informix/backup/llog
informix> chmod 770 /home/informix/backup/llog
informix> vi $INFORMIXDIR/etc/$ONCONFIG

LTAPEDEV /home/informix/backup/llog

informix> vi $INFORMIXDIR/etc/alarmprogram.sh

# line 31, change flag from N to Y
BACKUPLOGS=Y

# line 62, change onbar -b -l to ontape -a -d
BACKUP_CMD="ontape -a -d"

That's is, you shouldn't have to do anything else.  Each time a logical log fills up alarmprogram.sh will be called and any unbacked up logical logs will be backed up to your backup/llog directory.

Restoring an Engine

There are many flavors of restore and you should get familiar with them all in the Backup and Restore guide contained in the free online documentation.  I will focus on a Cold Restore, which requires the engine to be offline.  Presumably because something terrible happened and you need to restore to recover from a server or disk problem.

Using the output from the onstat -d you saved during your last system backup, verify that the files for your chunks exist.  If you replaced a failed drive or are restoring to a different server they may not exist, in this case you can use the touch command to recreate a 0 byte file placeholder with the appropriate ownership and permissions.  The restore will do the rest.

Using the ontape utility, invoke the restore command with the -d directory option.  When -d is used you are not prompted for any decisions, ontape figures out what to do based on what is in the backup directories specified in TAPEDEV and LTAPEDEV.

informix> ontape -r
Restore is using file /home/informix/backup/data/blogsvr01_0_L0 ...

Archive Tape Information

Tape type:      Archive Backup Tape
Online version: IBM Informix Dynamic Server Version 11.50.UC7IE
Archive date:   Sun Jul 25 16:24:17 2010
User id:        informix
Terminal id:    /dev/pts/0
Archive level:  0
Tape device:    /home/informix/backup/data/
Tape blocksize (in k): 32
Tape size (in k): system defined for directory
Tape number in series: 1

Spaces to restore:1 [rootdbs                                                                                                                         ]
2 [llogdbs01                                                                                                                       ]

Archive Information

IBM Informix Dynamic Server Copyright 2001, 2010  IBM Corporation.
Initialization Time       07/23/2010 15:50:16
System Page Size          2048
Version                   18
Index Page Logging        OFF
Archive CheckPoint Time   07/25/2010 16:13:05

Dbspaces
number   flags    fchunk   nchunks  flags    owner                            name
1        40001    1        1        N  B     informix                         rootdbs                                                                                                                        
2        40001    2        1        N  B     informix                         llogdbs01                                                                                                                      


Chunks
chk/dbs offset   size     free     bpages   flags pathname
1   1   0        1048576  518117            PO-B  /home/informix/chunks/ROOTDBS.01
2   2   0        1048576  425969            PO-B  /home/informix/chunks/LLOGDBS01.01

Continue restore? (y/n)y
Do you want to back up the logs? (y/n)y
File created: /home/informix/backup/llog/blogsvr01_0_Log0000000040
Log salvage is complete, continuing restore of archive.
Restore a level 1 archive (y/n) y
Ready for level 1 tape
Restore is using file /home/informix/backup/data/blogsvr01_0_L1 ...
File /home/informix/backup/data/blogsvr01_0_L1 not found, continuing ...
Do you want to restore log tapes? (y/n)y

Roll forward should start with log number 39
Restore is using file /home/informix/backup/llog/blogsvr01_0_Log0000000039 ...
Rollforward log file /home/informix/backup/llog/blogsvr01_0_Log0000000039 ...
Rollforward log file /home/informix/backup/llog/blogsvr01_0_Log0000000040 ...

Program over.

After ontape -r -d completes the engine will be in Quiescent (Single User) mode, resolve this by executing onmode -m to switch to Multi-User Mode and finalize the restore.

informix> onmode -m

Keeping the Size of Backups In Check

If you would like to reduce the amount of space these backups are taking you're probably thinking about compressing them with gzip or something similar and decompressing the required files before a restore.  Well those Informix guys and gals did it again by adding 2 ONCONFIG parameters that allow you to automatically compress and decompress these backup files on the fly via backup and restore filters.

If I would like to use gzip to compress my backups and gunzip to decompress I would modify the BACKUP_FILTER and RESTORE_FILTER ONCONFIG parameters.  I will also be required to change LTAPESIZE from the default of 0 (unlimited) to something not 0.  Not sure exactly why this is required, but the engine complains if LTAPESIZE is 0.

informix> vi $INFORMIXDIR/etc/$ONCONFIG

LTAPESIZE 2097152
BACKUP_FILTER /bin/gzip
RESTORE_FILTER /bin/gunzip

Click on the mug to buy some crap on Amazon and support this site!

8 comments:

  1. Hello,
    I have two node (windows server 2008 r2 standard) running informiix 1170. HDR is configured with a primary and a secondary. The primary server went down, I was able to bring the secondary and configure it to run in "STANDARD" mode but I am unable to bring back the secondary??
    I took a level 0 zero backup on the secondary when I try to restore ontape -r to bring back the OLD primary I get the error: cannot initialize security subsystem, ensure that the account has necessary privileges. I am logged in as iformix which is also a local admin of the server??

    Any idea???

    ReplyDelete
  2. check if the onconfig parameters are identical between the primary and the secondary
    also check if the informix account is configured the same way on the two servers

    ReplyDelete
  3. and ensure that no informix process are running on the primary, rebooting the server would be better

    ReplyDelete
  4. Hi guys, we are about to migrate an old ERP system with Informix DB to new windows server. Our only concern is how are we going to back it up. Since our service supplier doesn't support Informix DB backups, only SQL or Oracle, they can't do live backup of the DB, but what they can do is that if we provide them with a DB dump file, they can add that to their backup job... can someone help me to understand what it takes to create a dump file from Informix DB? Can it be scheduled as a task? Thanks in advance...

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. Hello, good day,
    I have 2 questions
    Why use ontape instead of onbar ? And what is onbar compression ratio ?

    ReplyDelete
  7. Some of them are not ready for any emergency or disaster as it has not happened to them before. Some businesses have not even thought about precautions for data protection and recovery so far.
    joe's review

    ReplyDelete
  8. Dear All,

    If I backup the logical files to disk, is there any way to automatically, remove the old logical files from disk once new level0 archive is done?

    Thank you.
    Aleksandar

    ReplyDelete