Características nutricionales de las bebidas de soya fermentadas

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.

Page 377 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.

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,* except that multiple databases may be found inside a single instance. Informix cannot recover a single database inside an instance. However, a

* Informix would, of course, dispute this! I am merely saying that the term database refers to the same general structure in Informix that it does in other databases.

Page 378 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

Page 379 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.

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.

Page 380 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,

Page 381 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.

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

Page 382 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 buffers are 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

Page 383 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.

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

Page 384 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