Let you know software development in advance (34): Database Maintenance

Let you know software development in advance (34): Database Maintenance

Part 1 Database SQL language

Database Maintenance

 

For some large software products, once commercialized, a lot of user information will be stored, which is the database used by the product (hereinafter referred toBusiness Database) Brings pressure. In order to ensure the stable operation of the business database, we need to perform some basic maintenance operations on the database in daily work.

The maintenance rules of the business database are as follows:

1. Regular full backup of business databases is required

Regular backups of business databases can be quickly restored when the database is accidentally damaged. Backup policies can adopt weekly or monthly plans based on the incremental data size.

If you back up the database db_100, the operations in Oracle, Sybase, and SQL Server are as follows:

(1) Oracle user mode backup

Exp db_100/db_100 owner = db_100 buffer = 8192 (or 64000)

File = exp_db100.dmp (or tape device/dev/rmt0)

Tables =

Rows = y

Compress = n

Log = exp_db100.log

 

Parameter description:

Owner: list of user names to be detached.

Buffer: the buffer size.

File: name of the output file created by export.

Tables: List of table names to be detached.

Rows: Specifies whether to unload the table data rows. The default value is "y ".

Compress: Specifies whether to compress table data into a region during the loading process. If compress = y is specified when data is detached, the data is compressed to an initial region. This option can keep the original size of the initialization area. The default value is "y ".

Log: Specifies a file that receives useful information and error messages.

 

(2) database backup in Sybase

Dump database db_100 with/home/data/db100.dmp (or tape device/dev/rmt0)

 

(3) database backup in SQL Server

Use master

Go

Exec sp_addumpdevice 'disk', 'dev _ db100 ',

Disk = 'C: \ backup \ dev_db100.dat'

Go

Backup database db_100 to dev_db100

Go

 

2. Regular data cleaning of business databases. Backup operations must be completed before cleaning.

A backup table mechanism is used for log tables with a large number of records per day. That is, a backup table with the same structure as the production table is created. The retention time of the production table and backup table is determined based on the project requirements.

Assume that the business no. 1000 processes the master log production table tb_mainlog, which requires that the production table record be retained for 2 days, the backup table record be retained for 90 days, and the field name of the filter date is startdatetime, the batch submission interval is 60 minutes. If a stored procedure pr_backuptable is used for backup, you only need to call it as follows:

Exec pr_backuptable 'tb _ mainlog', 'tb _ mainlog_bak ', 'startdatetime', 'yyyy. mm. dd hh: mi: ss', 60 ,''

Parameter description:

Parameter 1: name of the production table

Parameter 2: Backup table name

Parameter 3: retention days of the production table

Parameter 4: Number of days the backup table is retained

Parameter 5: Filter Date Field Names

Parameter 6: Format of the filter Date Field

Parameter 7: Batch submission interval (unit: minutes)

Parameter 8: Expansion condition used for comparison

 

3. If no log truncation (or non-archiving mode) is set for the database, regular maintenance of database logs is required.

The archive mode of Oracle logs is usually required during installation. The "trunc log on chkpt" option of the Sybase Database is set to true, or if this option is set to false, regular Database Log truncation is required. The SQL Server database option "autoshrink" is set to true.

(1) set Oracle database logs to non-archive Mode

The procedure is as follows:

1) shut down the database

2) Start the instance in startup mount mode.

3) Switch Mode

Alter database noarchivelog;

Alter database open;

 

(2) Set "trunc log on chkpt" to true in database db_100 in Sybase

Use master

Go

Sp_dboption db_100, 'trunc log on chkpt', true

Go

Use db_100

Go

Checkpoint

Go

 

(3) set "autoshrink" in database db_100 to true in SQL Server

Sp_dboption db_100, 'autowrite', true

 

4. Regular maintenance of database Indexes

For Sybase and SQL Server databases, indexes in tables must be regularly updated and maintained. If the index fails, the index is rebuilt.

For example, to update the statistics of all columns in all indexes in the tb_userinfo table, use the following statement:

Update index statistics tb_userinfo

 

5. Regularly view the running result log of database tasks and the maximum available database space

During database operation, you should often use logs to check whether the running results of related tasks are normal, and check the remaining space of the database. If the available space is small, to clear the file.

 

6. We recommend that you check the table consistency and rebuild the index after adding, deleting, and modifying databases in large batches.

During large-scale operations, the structure of the data table may be accidentally damaged. Therefore, you need to check the consistency of the table after the operation is completed. If the table structure is abnormal, You need to recreate the table and the corresponding index.

 

In some business software products, data maintenance is a complex, heavy, and long-term task. Relevant maintenance experience can be accumulated only in the actual operation process.

 

 

(My microblogging: http://weibo.com/zhouzxi? Topnav = 1 & wvr = 5, No.: 245924426, welcome !)