Analysis of INFORMIX database backup and recovery tools

Informix is a relational database management system that can be used to develop powerful applications that can run on multiple platforms. Informix Database Dynamic Server (IDS) is the core of INFORMIX database technology products, featuring scalability, scalability, manageability, and efficient operation. Because Informix is mostly used to manage large applications, data backup and recovery is an important task for database administrators. Therefore, it is necessary to master several common traditional data backup and recovery methods for infomix dynamic servers. For Informix databases, online data stream backup has the highest efficiency and practicability as other databases. We recommend that you use the online data stream backup mode for commercial Informix databases. Other on-bar, ontape, dbexport, and onUnload tools are commonly used backup maintenance tools and can be used as supplementary backup methods.

Next we will talk about the backup and recovery tools on-bar, ontape, dbexport/dbimport, and onUnload/onload.

I. Use the on-bar tool for data backup and recovery
1. Introduction to the on-bar tool and its Functions
The on-bar tool backs up and recovers buckets and logical files by using the Storage Manager to trace backup and storage media. Its functions are as follows:
1) Select a specific bucket for backup or recovery
2) supports backup to specific time points
3) supports separate physical and logical recovery.
4) Multiple tape drives can be used for backup and recovery.
5) Import recovery is supported.
6) supports external backup and recovery

2. Execute the on-bar backup data syntax
Note: The [] content is optional and <> it is required.
1) onbar_d-B [-p] [-L <level>] [-w |-F <FILENAME> | <spaces>] [-O] [-CF Yes | no | only]
-B: Backup
-P: physical backup (only backup storage space, not logical logs)
-L: Backup level. Optional values: 0, 1, and 2. The default value is 0.
-W: entire system backup
-F: contains the complete file names of a series of database spaces.
-O: overwrite error check
Space: Specifies the space for standby databases.
-Cf: backs up important files.
2) onbar_d-B-f: executes a pseudo backup.
3) onbar_d-B-l [-c |-S] [-O]
-L: Full logical log file backup
-C: Back up the current logical Log File
-C: Perform continuous logical log backup.
-S: Rescue log
4) onbar_d-V [-W] [-p] [-T <time>] [-F <FILENAME> | <spaces>]
-V: Use the archecker utility to verify whether the backup is available
-W: Verify the backup of the entire system
-P: verify physical backup
-F: Verify the backup storage space listed in the file filename.
-T: execution time point verification

3. Execute the on-bar data recovery syntax.
1) onbar_d-R [-E] [-O] [-F <FILENAME> | <spaces>]
2) onbar_d-R [-E] [-T "<time>" |-n <log>] [-O]
3) onbar_d-r-p [-E] [-T "<time>"] [-O] [-F <FILENAME> | <spaces>]
4) onbar_d-R-l [-c |-x |-T "<time>" |-n <log>]
5) onbar_d-r-W [-E] [[-p] [-T "<time>"] |-n <log>] [-O]
6) onbar_d-Restart
7) onbar_d-R [-E]-Rename-F <FILENAME >|{-Rename-P <old_path>-O <old_offset>-n <new_path>-O <new_offset> .. .} [-W] [-p] [-T "<time>" |-n <log>] [-F <FILENAME> | <spaces>]
-R: data recovery options
-C: Start continuous Log Recovery
-E: External import recovery
-F: Specifies a file that contains a series of buckets to be restored.
-L: Perform logical log recovery.
-N: Restore the specified logical log.
-O: overwrite error check
-P: perform physical recovery.
-T: restore to the specified time point
-W: Restore the entire database system
-X: Terminate the continuous logical Log Recovery and set the database server to silent.

4. Notes for using the on-bar tool for data backup
1) when using the on-bar tool, you need to configure the Storage Manager, such as ism, TSM, or another third-party Storage Manager. how to configure the Storage Manager depends on the Storage Manager you use, for more information, see IBM's official document Informix backup and recovery guide.
2) The on-bar tool does not support backup or recovery from cloud storage. If you need this, we recommend that you use the ontape tool.
3) The on-bar tool using ism as the storage manager does not support remote backup devices on other hosts.

Ii. Use the ontape tool for data backup and recovery
1. ontape tool introduction and functions
The ontape tool can record, back up, and recover data, and can change the log record status of the database. You do not need to use the storage manager, but in general, you need to use the oncongif in the database configuration file. six parameters in the InstanceName file:
Tapedev: the absolute path name of the directory file system or tape device used for storage space backup. It specifies the destination location of the data written to the storage space by ontape during archiving, and the source location where ontape reads data during recovery.
Tapeblk: the size of the tape used for storage space backup (KB)
Tapesize: the size of the tape used for storage space backup (0-2097151kb)
Ltapedev: Logical log tape device or file system directory
Ltapeblk: the size of the tape used for logical log backup (KB)
Ltapesize: the size of the tape used for logical log backup (0-2097151kb)
The main functions are as follows:
1) Informix Database Backup (instance-level backup of the database, does not support selecting a storage space for backup)
2) INFORMIX database recovery (select to restore the specified bucket)
3) back up and restore logical logs
4) change the logging method of the database

2. Execute the ontape data backup syntax
Note: The [] content is optional and <> it is required.
1) ontape-A [-D]
2) ontape-C
3) ontape-L [-c |-x] [-D]
4) ontape-s [-D]
5) ontape-s [[-l archive_level] [-F] [-A database_list] [-B database_list]
[-N database_list] [-u database_list] [-T tape_device_path [-v] [-D]}

-S: Creates a backup.
-L: Backup level. Optional values: 0, 1, and 2.
-F: perform a pseudo backup.
-A: change the status of the specified database to a log record that complies with the AISI standard.
-B: change the status of the specified database to buffer log records.
-D: indicates that ontape is executed without interaction prompt information.
-N: indicates that ontape terminates the log records of the specified database.
-T: indicates that ontape is the current backup or recovery using another tape device. This parameter overwrites the tapedev value in the configuration file.
The-T stdio option indicates that the ontape tool backs up to the standard output or recovers from the standard input.
-U: changes the status of the specified database to unbuffered log records.
-V: Write reference information to stderr when backing up to standard output

Example: ontape-a-d: Back up all database logs in a non-interactive environment
Ontape-s-d-l 0: performs a backup of level 0 on the database in a non-interactive environment
Ontape-s-d-l 0-T c: \ informixdk: performs a backup of level 0 on the database in a non-interactive environment, and stores the backup files in c: \ informixdb,
Note: The backup file generated by ontape backup is hostname_servernumber_ln and the log file is hostname_servernumber_lognnnnnnnnnn. You can overwrite the prefix of the permanent file name by setting the environment variable ifx_ontape_file_prefix)

3. Execute the ontape data restoration syntax.
1) ontape-P [-E] [-Rename {-F <FILENAME>
-P <old path>-O <old Offset>-n <New Path>-O <New Offset>...}]
[-T tape_device_path [-v] [-D]
2) ontape-L [-c |-x] [-D]
3) ontape-s [-D]
4) ontape-R [-Rename {-F <FILENAME>
-P <old path>-O <old Offset>-n <New Path>-O <New Offset>...}]
[-D dbspace_list] [-T tape_device_path [-v] [-D]

-L: Logical recovery options
-R: perform full physical recovery and logical recovery on the data of the entire database server.
-C: Restores logs from the current logical log tape without sending a prompt for tape installation.
-E: perform external recovery.
-F: Specifies a file that contains the name and offset of the block to be renamed.
-P: physical recovery options
-P: Specify the block to be renamed and its new location
-D: Restore the selected Bucket
-Rename: Rename the bucket during restoration
-S: Perform logical log collection.
-X: pauses the server in the logical recovery pending state.
-V: writes reference information to stderr during standard input restoration.
Example: ontape-p-D-T c: \ informixbk

4. Notes for using the ontape tool for data backup
1) Before performing an ontape backup, set the tapedev, tapeblk, tapesize, ltapedev, ltapeblk, and ltapesize parameters in the onconfig file correctly.
2) In order to shorten the data recovery time, priority should be given to level 0 and backup level 1 and level 2 should be minimized. Data of important departments such as banks should be backed up at level 0 every day.
3) if you need to recover the data backed up by ontape to another server, the quantity and size of the disk space of the two server models, operating systems, onconfig configuration files, and non-temporary dbspace must be consistent; otherwise, the data cannot be restored normally.
4) Make sure there are sufficient logical log files available. If the remaining logical log space is less than 50% of the size of a single logical log, Informix-Online rejects the backup operation. You must back up the used logical logs before backing up data.
5) keep a copy of The onconfig file. After level 0 backup, The onconfig file should be backed up, because the onconfig file information needs to be read when the level 0 backup is restored. If the online configuration is modified, data recovery will fail, if this level 0 backup is restored, the current configuration must be restored.
6) backup can be performed online or in static mode. The online operation mode cannot be changed during the backup process. Otherwise, the backup will be interrupted.
7) do not run the BACKUP command in the background (under UNIX &), because the backup is performed in interactive mode.

Iii. Use dbexport/dbimport to back up and restore Data
1. Introduction to dbexport and dbimport tools
The dbexport tool writes data to a file or backup media (such as tape) in an ASCII format ). Dbexport files include database mode files and data files.
The dbimport tool imports the database unloaded by the dbexport tool to a new database instance or the original database instance. The database is created during import, therefore, make sure that the target database instance does not have a database with the same name as the database to be imported. Otherwise, the Import fails.

2. dbexport command syntax
Dbexport <database> [-x] [-C] [-q] [-D] [-ss [-Si]
[{-O <dir> |-T <tapedev>-B <blksz>-S <tapesz> [-F <SQL-command-File>]}] [-NW]
[-No-data-tables [= table name {, table name}]
[-No-data-tables-accessmethods [= access method name {, access method name}]

Database: Specifies the name of the backup database.
-X: hexadecimal data
-C: the database is exported unless a fatal error occurs.
-Q: no error messages, warnings, and SQL data definition statements are displayed on the standard output device.
-D: only the Blob descriptor is output, and no BLOB data is output.
-SS: outputs server-specific information for creating databases and tables in the mode file, for example: the size of the initial extent and the attached extent, the locking method, and the dbspace where the table resides.
-O: Specifies the disk directory of the data output file.
-T: Specifies the backup media name of the data output file.
-B: Specifies the size of the backup media block.
-S: specifies the maximum storage capacity of the backup media.
-F: Specifies the mode file name stored on the backup media.

By default:-No-data-tables = tsinstancetable
-No-data-tables-accessmethods = ts_rtl_vtam, ts_vtam
Example: dbexport eisoodb-c-o c: \ informixbk
 
3. dbimport command syntax
Dbimport <database> [-x] [-C] [-q] [-D <dbspace>]
[-L [{buffered}] [-ANSI] [-CI]
[{-I <dir> |-T <tapedev> [-B <blksz>-S <tapesz>] [-F <script-File>]}]

-D: Specifies the storage space for Database Import. The default value is the root database space.
-L: Specifies the log record method for Database Import.
-I: the parent directory of the directory where the database mode files and data files are located
(The dbexport tool creates a database. Exp directory to store database mode files and data files under the directory specified by the-O parameter. dbimport requires the directory where database. Exp is located)
For other parameters with the same name, see dbexport.
Example: dbimport eisoodb-c-l buffered-I c: \ informixbk

4. Precautions for using dbexport/dbimport
1) The dbexport command must be an Informix user or a user with DBA access permissions;
2) data backed up using dbexport must be restored using the dbimport command;
3) You can edit the mode file to change the database generated by dbimport, such as changing the database name or related table information;
4) dbexport occupies the database in exclusive mode. During the backup, other users cannot query, modify, or insert the database;
5) the mode file does not retain the Database Log Files. When you use the dbimport command to create a database, You must select a log method (ANSI, buffered, nobuffered) for the new database );
6) You can use the dbexport/dbimport command to convert data under Informix-online and Informix-se. You can also convert data between different versions of Informix-Online, this type of conversion has no special requirements on the structure of the database server and the configuration of Informix;
7) using the dbexport/dbimport command to back up/restore data can eliminate space fragments allocated to data and improve the overall operational efficiency of the database.
8) the directory specified with the-O option must exist and the current user has the write permission.

4. Use the onUnload/onload tool for data backup and recovery

1. Introduction to onUnload and onload tools
The onUnload tool unloads data in binary format in units of pages. The backup efficiency is higher than that of dbexport.
The onload tool loads the data unloaded by onUnload to the specified database.

2. onUnload command syntax
OnUnload [-L] [-T <tape device>] [-B <block size>] [-S <tape size>]
<Database> [: [<owner>.] <Table>]
The preceding parameters indicate the following meanings:
-L: indicates that onUnload reads the tape device, block size, and backup device capacity values from the configuration parameters tapedev, tapeblk, and tapesize respectively.
-T: Specifies the backup device name.
-B: Specify the block size of the backup device.
-S: Specifies the storage capacity of the backup device.
Database: Specifies the database to be backed up.
Owner: Specifies the owner of the table.
Table: Specifies the table to be detached.

3. onload command syntax
Onload [-L] [-T <tape device>] [-B <block size>] [-S <tape size>]
[-D <dbspace>] <database> [: [<owner>.] <Table>]
[{-I <old index Name> <New Index Name>}]
[{-FD old-dbspace-name New-dbspace-name}]
[{-Fi index-name old-dbspace-name New-dbspace-name}]
[{-C <old constraint name> <new constraint name >}]
-D: Specify to restore to the target Bucket
-I: Rename the table Index
-FD: rename a bucket
-Fi: Add Table indexes and rename the bucket
-C: Rename Constraints
For other parameters with the same name, refer to onUnload

4. Notes for using onUnload/onload
1) The onUnload backup data must be restored using the onload command;
2) onUnload/onload cannot implement data transfer between different versions of online;
3) onUnload/onload is the binary data stored in units of disk page size. The computer for picking and receiving data must have the same disk page size;
4) Only the onUnload unload data can be loaded into the online management database or table;
5) The user must have the DBA access permission to execute the onUnload/onload command;
6) onUnload does not retain the access privileges defined in the initial table;
7) onUnload does not retain synonyms defined in the initial table.

Summary: The above four data backup and recovery methods have their own characteristics. On-bar is undoubtedly the most powerful tool, but it is also the most complex configuration and use; the ontape tool requires high consistency before and after database parameter configuration, but the data backup and recovery operations are simple and the backup and recovery speed is fast; the backup made by dbexport can realize data conversion between different versions and different Informix products, and the data transfer is flexible and convenient. The onUnload backup is in the form of pages and binary backups, which is highly efficient, when data is restored, the size of the computer disk page must be consistent. To ensure secure data recovery, You must select at least two backup methods. By analyzing and comparing the four common backup methods of the Informix database, you can select a Backup recovery method that meets your requirements based on the actual situation, and develop an appropriate backup solution to ensure data security.