Informix Online Database Maintenance Skills

I. database query User Creation

The bank Informix_on_line database stores a large amount of important information from depositors. To ensure database security, strict regulations must be imposed on data operations. For example, to enter the on_line database, strict procedures must be performed, in some cases, it is inconvenient to search for problems. Therefore, it is necessary to create a dynamic query user who has only the read permission to the database. The specific method is:

1. Create a query user, which should have a database environment

2. Connect the query user to the database (for example, the work user)
Ln-s/homel/work/homel/read (connect the query User read to the Database User );

3. The work user uses the database and grants the connect permission to the read user grant connect to read;

4. Grant the select permission to the read user for each table in the database.
Grant select on abc to read. (grant the select permission to the read user)

In this way, you can register with a read user to perform read operations on the database, which facilitates searching for problems.

Ii. Database consistency check

A. Log On with informix
B. Set the database status to off_line.
Onmode-ky
Use onstat to check that the database status is off_line
C. Set the database status to single-user mode
Onmode-s
Use onstat-check that the database status is quiesent
D. Check the database retention Page Status
Oncheck-cr 1>/tmp/oncheck. cr 2> & 1
E. Check database directory page consistency
Oncheck-cc 1>/tmp/oncheck. cc 2> & 1
F. Check Database Data Consistency
Oncheck-cD workdb 1>/tmp/oncheck. cd 2> & 1
G. Check database index consistency
Oncheck-cI workdb 1>/tmp/oncheck. ci 2> & 1
H. Check the oncheck. cr, oncheck. cc, oncheck. cd, and oncheck. ci files in/tmp to check whether there are any error messages. If not, the database status is normal and vice versa.
I. Set the database status to online
Onmode-m
Use onstat-check that the database status is online

Iii. database backup and recovery

1. dbexport backup and dbexport recovery

Dbexport backup is a style file backup that saves the information in the database as a text file. Note that there must be no access to the database during Backup, otherwise, dbexport backup will not succeed. The general format of dbexport backup is (taking the database workdb as an example)
Dbexport workdb-d-s workdbs/path
2. dbimport recovery refers to restoring the files backed up with dbexport to the database.
A. Stop all database operations → delete a database;
B. $ dbimport workdb-d workdbs-I/path;
C. Use onmonitor to change the parameter TAPEDEV to/dev/null;
D. ontap-s-u workdb.
E. Check whether the workdb status is changed to U.
F. Change the TAPEDEV value back to the original value.
It should be noted that a large amount of information should be written in the logical log file during the dbimport recovery process. The above method can avoid writing the logical log file and speed up dbimport.

3. Zero-level database backup
Zero-level database backup is an important means of backup. tape backup is usually used for routine backup before major operations, and data is often recovered. Due to the large amount of data, the zero-level backup data on tape, it takes a long time to recover the data. Therefore, you can use the zero-level backup method on the hard disk.
A. Draw a sufficient space on the hard disk for storing backup files.
B. Use onmonitor to change the parameter TAPEDEV to a zero-level backup file.
For example, if/cs2000 is used as the storage space of the zero-level backup file oback, you can change the parameter to TAPEDEV =/cs2000/oback. This can be used as a zero-level backup of the hard disk, and the Backup recovery time is only 1/6 of the tape drive. Use tail-f online. log monitoring during recovery. The recovery process continues until the database status changes to online ..
C. Change the parameter TAPEDEV =/cs2000/oback to TAREDEV =/dev/rmt/0 m;

Iv. troubleshooting of Common Database faults

1 Check

Use onstat _-1 to check the usage of logical logs and whether the process is aborted. for a general check of database faults, you must first check the database status, often use onstat_de to find possible errors, and check online. whether the log reports an error.

2. Database Table tracking

When a large scale operation is performed on a database table, sometimes we do not know whether the operation on the table can be continued. Because isql enters and operates on the table, the database reports "this table has been locked" information, and the query statement is available:
First: set retrieved to drity read
Then: select count (*) from abc,
The abc table is continuously counted. If the number of statistics increases, the operation on the table is still in progress. Otherwise, the operation on the table is stopped. Also, when you execute SQL commands in batches, such as update ,...... If the insert statement cannot be executed successfully, you can use the method of adding judgment conditions and narrowing down the scope to execute the statement. If the records involved in some statements are locked during processing, when the processing process ends, the system may lock the table at the same time. In this case, you can lock the table at the beginning of processing.

3. troubleshooting example

Fault description: informix sqlcode error code-239 is displayed when records are inserted into the settlement data table in batches by bank interest settlement.
Fault check: According to the investigation, there seems to be repeated records inserted into the table. However, after checking the data, it is certain that there are no repeated records of the data. Considering the long-term use of the table files, the related information of the table file is damaged. To do this, do the following:
A. unload to "/tmp/abc.txt" select * from abc. unload all data in abc;
B. drop table abc. (delete table file abc );
C. create tabk abc
(Abc_swo mteger;
Abc_ano smallint)
...
); Create a database table file abc.
D. load form "tmp/abc.txt" insert into abc.
(Load the original data into the table file abc ).
E. Re-execute the interest settlement operation. The newly generated interest data is successfully loaded into the table file abc and the fault can be eliminated.

As a computer technician, familiar with database operations and mastering some operation skills and methods are very helpful for us to solve problems and find errors.