Exam 918 Backup And Restore

Overview

Backup

An IDS backup is a copy of one or more dbspaces and logical logs that the database server maintains. What gets backed up are pure IDS pages. Operating system files or IDS configuration files are not backed up. You will need a separate process in place to make a backup copy of un-backed-up files, outside of IDS backup process.

Restore

IDS restore re-creates database server data from backed-up storage spaces and logical-log files. A restore copies data from the backup media back to disk and brings the dbspaces to a consistent state by applying transactions in the logical logs backup.

Reasons to restore:

  • You need to replace a failed disk that contains database server data.
  • You have a corrupted or destroyed database data or page.
  • You need to move your database server data to a new computer.

Recovery tools and utilities available with IDS

OnBar

OnBar can perform backup and restore of both [[[dbspaces and logical logs. OnBar requires a storage manager.

Informix Storage Manager (ISM) is a limited storage manager shipped with the product. A third-party vendor storage manager can also be used. OnBar communicates with both the database server and the storage manager using an industry standard X/Open Backup Services Application Programmer's Interface (XBSA). Each storage manager vendor provides the (XBSA) shared library to be used with their storage manager. This is how OnBar is able to work with variety of storage managers software. OnBar and the XBSA shared library must be compiled on the same platform (32 bit or 64 bit).

Main OnBar features are:

  • Parallel backup and restore
  • Point in time restores
  • Integrated backup verification command
  • Support for external backup and restore
  • Support for Table Level restore

Ontape

The ontape utility is the oldest utility available and one of the simplest to use. It does not require a storage manager. It can backup the data from the database server to tape or disk. The ontape utility can perform physical and logical log backups and restores. The ontape utility performs backups and restores serially.

The main ontape features are:

  • Simplicity and ease of use
  • Backup capability to tape, file, or directory
  • Support for Table level restore
  • Support for External backup and restore

Backup types available with IDS

Full level 0 backup

A level 0 archive contains a copy of all data in the OnLine system at the time the archive started. Level 0 backups can be time-consuming because all the used disk pages need to be written to backup media. In addition to level 0 (full backup), IDS backup and restore utilities can do incremental backups (Level 1 and Level 2).

Level 1 backup

A level 1 backup takes less space and might take less time than a level 0 backup because only data that changed since the last level 0 backup is copied to the storage manager.

Level 2 backup

A level 2 backup takes less space and might take less time than a level 1 backup because only data that changed since the last level-1 backup is copied to the storage manager.

Serial vs. parallel backup

A serial backup can only backup one dbspace at a time (slow). A serial backup is the only kind of backup the ontape utility can perform. OnBar will backup dbspaces serially, meaning one at a time, when BAR_MAX_BACKUP ONCONFIG parameter is set to 1.

A parallel backup is where more than one dbspace is backed up at the same time. In 11.10, all OnBar backups are done in parallel unless the BAR_MAX_BACKUP ONCONFIG parameter is set to 1.

A standard OnBar backup (onbar -b) is a parallel backup of selected or all storage spaces.

In a standard onbar backup, the database server performs a checkpoint for each storage space as it is backed up. To restore from a standard OnBar backup, logical log backups are required.

A whole-system backup (when -w option is used) is a backup that automatically includes the necessary logical log records of the transactions open at the time of archive checkpoint, so that a whole system restore will restore to a consistent point without any explicit logical log backups and restore.

Logical log backup

A logical log backup is the process of copying the contents of a logical log file to secondary storage media.

The logical logs store records of checkpoints, administrative activities such as Data Definition Language (DDL) statements, and transaction activity for the databases in the OnLine instance. Every OnLine instance has a finite number of logical logs. OnLine uses the logical logs in a circular method. Records are written to the logical log files serially. When the first log fills up, OnLine begins writing to the second log, and so on. When all the logs have been used, OnLine will begin writing to the first log again. Before OnLine can reuse a log, all its data must be backed up.

For databases that perform buffered, unbuffered, or mode ANSI logging, all inserts, updates and deletes to the tables are recorded in the logical log. The record of this transaction activity, inserts, updates, and deletes must be preserved for two purposes:

First, in case of a system crash that requires data to be recovered from a backup, these transactions will be replayed to prevent the loss of work since the last backup.

Second, in the case of a power failure or other loss of data in memory, the logical logs will be replayed and rolled back to ensure that the data in the database is returned to a consistent state. This transaction activity must continually be recorded, and must be maintained until the next dbspace or whole system backup is performed. All dbspace backups require logical log backups for a successful restore, except whole-system backups.

Continuous, Automatic, and Manual logical log backups

If all logical logs are filled, the database server hangs until the logs are backed up. To free full logical-log files, back them up.

Logical log backups may be executed manually (on-demand) by an administrator or operator, or automatically triggered using the ALARMPROGRAM configuration parameter or by running a continuous log backup.

On-Demand Manual logical log backups are performed when an administrator or operator executes a log backup request using either OnBar or ontape. A manual logical-log backup backs up all the full logical-log files and stops at the current logical-log file.

Automatic logical log backups are configured by specifying a program, using the ALARMPROGRAM configuration parameter, that executes a logical log backup command whenever a log full event (event class 23) is issued by the server. Typically, if you choose to use OnBar as your backup utility, you will configure automatic logical log backups. IDS accomplishes this by executing the script pointed to by the ONCONFIG parameter ALARMPROGRAM. If alarmprogram.sh is used, then edit it to set BACKUPLOGS to Y. IDS provided scripts, log_full.sh and alarmprogram.sh are found in $INFORMIXDIR/etc. To turn off automatic backup of the logical logs, you may set ALARMPROGRAM to no_log.sh or set BACKUPLOGS to N if the alaramprogram.sh is used. If automatic backup of the logical logs is disabled, the DBA inherits the responsibility of ensuring regular and timely backups of the logical logs.

Continuous logical log backups are typically used if you use ontape as your backup and restore utility. The (ontape -c option) continuously backs up each logical log as it fills or as the server switches to the next log. Continuous logical log backups require a dedicated terminal and backup device.

IDS 11.10 has an ontape backup to directory feature that allows ontape backups to be automated with ALARMPROGRAM scripts using ontape -a -y if LTAPEDEV is set to a directory. If using this feature to do automated ontape logical log backups to directory, it is the DBA's responsibility to ensure that the ALARMPROGRAM script checks that LTAPEDEV is set properly to a directory. Otherwise, if LTAPEDEV is not set to a directory, but to a device, the successive logical log backups may be over-written with the previous backups.

Logical log salvage

In a log salvage, the database server accesses the log files directly from disk while the database server is off-line. The log salvage backs up any logical logs that have not yet been backed up and are not corrupted or destroyed enabling you to recover all of your data up to the last available and uncorrupted logical-log file and the last completed transaction. After a system failure, necessitating a restore, some logical log data may not have been backed up. This data must be salvaged because it will be required to recover the system to the point in time of the failure. A cold restore after a system failure will automatically attempt to salvage any logs, but the user also has the option of salvaging the logs prior to the cold restore.

The salvage log command is:

  • onbar -l -s
  • OR
  • ontape -S

The salvage log command may be useful if a device containing logical log files must be replaced prior to the cold restore process. If the logs on disk after a failure are not salvaged, the logical restore process will overwrite the log space, and the transactions previously recorded will be lost. The system will not be restored to the point in time of the failure.

The onbar -r command automatically salvages the logical logs. Use both the onbar -r -p and onbar -r -l commands, if you want to skip log salvage. With ontape, you can skip log salvage during a restore by answering No to the question "Do you want to backup the logs?"

Recovery strategies and scheduling backups

Recovery strategy requires planning with an understanding of the business critical data, and comprehension of the backup tools available and their capabilities.

The first step in planning your recovery is to outline recovery goals based on the understanding of the business data. Define what is a successful recovery for your system in terms of tolerance for data loss and acceptable time loss.

Restores with IDS

Physical vs. logical restore

The physical restore is the process of restoring the dbspaces and blobspaces backed up with level-0, level-1, and level-2 backups. The physical restore process is very efficient because it simply copies page images from backup media and places them on disk.

The logical restore is the process of restoring the server system to the time of failure using transactions found on logical-log backup tapes. The logical restore occurs after the physical restore. Since the logical restore does not copy pages, but rather replays transaction records, it is slower and less efficient that the physical restore process.

Cold restore vs. warm restore

Cold restore: A restore that occurs when the root dbspace or a dbspace that holds the physical or logical logs is inaccessible (critical dbspaces). The database server must be in off-line mode. Whole system restores are always cold restores, because all the dbspaces are being restored including the critical dbspaces, will always be cold restores.

Warm restore: A restore that occurs when the root dbspace and the dbspace(s) containing the physical and logical logs are accessible (critical dbspaces). The server must be in online or quiescent mode.

It is not considered critical to bring the database server online as it does not hold the data for the rootdbs, physical log or logical logs. So if you are to restore only the data dbspace while the engine is online, you will be doing a warm restore. A warm restore will require rolling forward of the logical logs (logical restore) to bring the restored dbspaces up to date and to a consistent state with the rest of the dbspaces.

Mixed restore: Cold restore of the critical dbspaces only, followed by a warm restore of the none-critical dbspaces. While a mixed restore makes the critical data available sooner, the complete restore takes longer because the logical logs are restored and replayed several times — once for the initial cold restore and once for each subsequent warm restore.

Sometimes, a mixed restore is desirable. Consider a server containing a root dbspace, a logical log dbspace, 3 (10 gigabyte) dbspaces containing business critical financial data, and 50 (10 gigabyte) dbspaces containing history data. It may be beneficial to quickly recover the critical dbspaces and the 3 dbspaces containing the business critical data using a cold restore. Once these dbspaces are restored and the system is available to users, the less important history data may be restored using a warm restore. While the total restore time for the system will take longer, (the logical logs will have to be applied twice and the warm restore will have to share hardware resources with active users) a mixed restore allows business to resume more quickly.

Parallel restore vs. serial restore

Parallel restore:With parallel restores multiple processes are spawned to restore multiple dbspaces in parallel. Parallel restores are also referred to as dbspace restores. Dbspace restores can be used to restore a single dbspace, multiple dbspaces, or an entire OnLine instance. Parallel restore is only available with OnBar. With OnBar only, dbspace restores will be performed in parallel (unless BAR_MAX_BACKUP is set to 1). If you perform a restore using the onbar -r command, OnBar restores the storage spaces in parallel and replays the logical logs once.

Serial restore: With ontape, all restores are serial restores as they restore one dbspace at a time. OnBar whole system restores are special purpose restores and require whole system backups. A whole system restore does not require a logical restore. If you do not maintain logical log backups, you will only be able to do whole system backups and restores.

Table level restores

A table level restore can be performed using the archecker utility. The archecker utility restores tables using a schema command file provided by the DBA. The schema command file has the source table to be extracted, the destination table[s]) where the data is to be restored to, and an INSERT statement that links the two tables.

Point-in-time or point-in-log restores

A point-in-time restore is a restore that recovers the data back from level 0,1,and 2 backup and roll forward the logical logs up to a specific point in time or up to a specific logical log.

A point-in-time restore enables you to restore the database server to the state it was in at a particular point in time. A point-in-time restore is typically used to recover from a mistake. A point-in-time restore is always a cold restore that you can use to undo mistakes that might otherwise not be fixable.

An example of such a mistake is dropping a table by mistake. A full restore restores the table during the physical restore but drops it again during the logical restore. A point-in-time restore lets you restore the data to the moment just before the table was dropped. When you restore the database server to a specific time, any transactions that were uncommitted at the specified point in time are lost. Also, all transactions after the point-in-time restore are lost.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.