Daily Management and Maintenance of Informix [ZT]

Routine Management and Maintenance of Informix Online Database

1. Start and close the Informix Database

1.1 automatic start of Informix

Start the online command: oninit
However, in general, the startup command is created in the/etc/rc2.d directory and named s96informix, so that online starts when the operating system is started.
Edit the/etc/rc2.d/s96informix file as follows:
Informixdir =/usr/Informix
Informixserver = picc_online
Onconfig = onconfig. PICC
Export informixdir nformixserver onconfig
Informixdir/bin/oninit
Modify the file attributes as follows: $ chmod + x/etc/rc2.d/s96informix

1.2 disable Informix automatically

The onmode-ky command is used to disable online.
However, as follows, the close command is created in the/etc/rc0.d directory and named k01informix. In this way, when the operating system is disabled, online is disabled.
Edit the/etc/rc0.d/k01informix file as follows:
Informixdir =/usr/Informix
Informixserver = picc_online
Onconfig = onconfig. PICC
Export informixdir informixserver onconfig
Informixdir/bin/onmode-ky
Modify the file attributes as follows: $ chmod + x/etc/rc0.d/k01informix

2. Informix Online common working modes

Offline)
Quiescent (system maintenance mode)
Online)

3. Manage Informix Online disk space
During online initialization, A dbspace named rootdbs is automatically created. This rootdbs stores online management information, including physical and logical logs. If dbspace is not specified when you create a database or table, the database or table is created in rootdbs as the default value. Therefore, if you want to create a database or table in a dbspace, you must specify the dbspace name in the SQL statement. For example, if the database name is 'stores', we create the database in 'workdbs 'dbspace. The SQL statement is as follows:
Create database stores in workdbs;
In addition, when creating a chunk or dbspace, you must specify the path of the original disk device name, the size of the required disk space, and the offset of the disk space in the original disk device. The offset is critical and must be carefully set. Otherwise, the chunk may overlap and overwrite space.
For example, assume that the original disk device/informixdbs1 has 100 MB space, and rootdbs use the first 100 mb. If you want to create a new chunk in/informixdbs1, the offset should be greater than MB.
1) Use the onspaces command to create dbspace
$ Onspaces-c-d dbspace name-P disk device-O offset-s size
-C: Indicates creating a new dbspace.
-D: dbspace name
-P: full path name of the original disk device, for example,/informixdbs1
-O: offset, in K bytes
-S: The first chunk size in dbspace, in K bytes
2) use the onspaces command to add chunk
$ Onspaces-A dbspace name-P original disk device-O offset-s size
-A: adds a chunk to a dbspace, followed by the dbspace name.
-P: full path name of the original disk device, for example,/informixdbs1
-O: offset, in K bytes
-S: chunk size, in K bytes
For example, a database system creates three dbspaces on the original disk device/informixdbs1:
Rootdbs: default value for online initialization. The size of the first chunk is 100 m, and the offset is 0;
Workdbs: stores application database data. The size of the first chunk is 100 MB, and the offset is 100 MB;
Tmpdbs: stores temporary system file data. The size of the first chunk is 50 MB, and the offset is 200 MB;
* Tmpdbs must be created before online initialization;
The command is as follows:
$ Onspaces-c-d workdbs-P/informixdbs1-O 100000-s 100000;
$ Onspaces-c-d tmpdbs-P/informixdbs1-O 200000-s 50000;

4. Informix Online log management
4.1 Database Log method:

No log mode (corresponding to non-transactional applications)
No buffer log Mode
Log mode with buffer
Compliant with ANSI standard log Methods
For the characteristics of the insurance business, we recommend that you use the method of no buffer log to create a database to ensure that transaction transactions will not be lost when the machine crashes. Compared with the method with the buffer log, the parallel processing speed of the database is slow.
You can use the ontape command to modify the Database Log mode.
Use ontape to open transaction logs:
$ Ontape-S-u stores; open the database stores log, the log mode is no buffer log;
End log with ontape:
$ Ontape-N lifeins; change the database lifeins to the log-free mode.

4.2. Management of physical logs

Physical logs Save the image before the data is modified. The location and size of physical logs can be changed. Use the onparams command to change the location and size of the physical log, and run the following command after entering the static mode online:
$ Onparams-p-s size-D dbspace-y
-P indicates physical logs.
-S: new size, followed by the size of physical logs in K bytes
-D: Specifies the dbspace name for storing physical logs.
-Y: "Yes" is used to answer all questions.
Note: the space allocated to physical logs must be continuous. Therefore, before changing the storage space, make sure that dbspace has enough space to store physical logs.

4.3. Logical log management

The size of the logical log space is the product of the size of the logical log file and the number of logical log files. After online initialization, the size of the existing logical log file cannot be modified. To change the size of the logical log space, you can only modify the number of logical log files. The size of the new logical log file can be set to a new value, the number of logical log files must be at least three.
(1) Use onparams to add logical log files
$ Onparams-a-d rootdbs
The status of the newly added logical file is a (A indicates the newly added logical file ). A new logical log file can be used only after a backup of rootdbs level 0 is created.
(2) Add a new logical log file.
$ Onparams-a-d rootdbs-s new log size
(3) Use onparams to cancel a logical Log File
$ Onparams-d-1 logid
Note :. Before canceling a logical log file, you must back up rootdbs at level 0.
$ Ontape-S.; this command can be used for level 0 backup
* Only logical log files in the "F (free)" or "A (Added) state can be canceled.
* The logid of the logical log to be canceled must be known.
$ Onstat-1; this command allows you to view the status and ID of the logical log file.

5. Informix online database backup and recovery

Backup content and purpose:
Data backup: backs up the data status of a database at a certain time point. It is used to restore the system in case of a System accident;
Logical log backup: After the logical log file is fully written, it must be backed up before it can be cleared and continue to be used. If all the logical log files are full, the system will crash. In addition, the logical log records the changes in the database after the last data backup. Therefore, the logical log backup is also used to restore the system when the system encounters an accident.

5.1. Data Backup:

(1) Online provides level 0, level 1, and level 2 Incremental backup;
Level 0: Back up the entire database system;
Level 1: Back up data that has changed since level 0 backup;
Level 2: Back up data that changes after Level 1 backup;
(2) Use the ontape command to back up data
$ Ontape-S
Run this command, and the system will prompt you to enter Level 1, 2 backup level
(3) Perform a level-0 backup before the entire system is officially running. Data Backup should be performed after the business system completes work every day. If the data volume is small, you can perform level 0 backup every day.
(4) manage backup tapes and mark them properly, such as date, time, backup level, and logical log number.

5.2 logical log file backup

(1) Regular backup of logical log files
Regular logical log file backup, the system only backs up the full logical log file to the tape, and then clears and releases these log files. Regular backup must be performed before all logical log files are fully written. Therefore, if regular backup is used, observe the usage of logical logs. In addition, if long transactions often occur, you should avoid using regular backup, and it is safer to use continuous backup.
Command: ontape-
(2) continuous backup of logical log files
Continuous backup means that when the logical log file is full, one is backed up, and the log file is deleted and released. Therefore, to perform continuous backup, you must ensure that there are tapes in the tape drive. Continuous backup requires one screen or terminal display. When the tape is full, the system will prompt you to change the tape.
Command: ontape-C
We recommend that you use continuous backup for Informix, but continuous backup will always occupy the tape drive. If the system has two tape drives, they can be used for data backup and logical log backup respectively. If the system only has one tape drive, it will conflict with the data backup. You can switch according to the following table:
Time-based operation
Make sure that there is a tape in the tape drive during daily business start and running, and there is a screen or terminal running: ontape-C
1) Stop ontape-C before the end of daily business, record the number of logical logs stored in the tape
2) Change tape
3) Run ontape-s (0-level backup is recommended when the business volume is small ).
Mark the date, time, backup level, and logical log number on the tape
4) Change tape
5) Run ontape-C

5.3 System Recovery

When a system error occurs or you want to restore the system to a certain point in time, you must use the data backup tape and logical log backup to restore the system.
Data backup restores the system to the data status at the time of data backup. Logical log backup restores the system to the data status after the data backup time.
Command: ontape-R
After you press this command, you can restore the last 0, 1, and 2 data backup tapes and the logical log tapes after the data backup tapes according to the system prompts.

6. System Diagnosis

Online. Log File
The database transaction log information is recorded in the $ informixdir/online. log file. You should check the online. Log File Information frequently to check whether the database has any exceptions. When a database error occurs, view the online. Log File to find out the cause and solution of the database system error. Online. log is very useful and should be well maintained and regularly cleared to prevent files from being too large to be viewed.
Finderr command
When a database error occurs, it generally carries an error number. The finderr command can be used to identify the cause of the database system error and the handling suggestions.