Let you know in advance. Software Development (34): Database Maintenance

2nd part Database SQL language

Maintenance of the database

For some large software products, once commercial, it is necessary to store a lot of user information, which brings pressure on the database used by the product ( hereinafter referred to as the business Database ) . In order to ensure the smooth execution of the business database, we need to do some major maintenance operations on the database in daily work.

The maintenance rules for the business database are as follows:

1. Regular full-scale backups must be made to the business database

Regular backups of the business database can be restored at high speed when the database is accidentally destroyed, and the backup strategy can be based on the data increment size of the weekly or monthly plan.

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

(1) Backup of Oracle user mode

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

Description of the parameters:

owner: The list of username that will be unloaded.

Buffer: The size of the buffers.

file: The name of the output file created by export .

Tables: List of table names that will be unloaded.

rows: Indicates whether the number of rows for the table data is unloaded, and the default is "y".

Compress: Indicates whether table data is compressed into a zone during the mount.

Assume that the specified number of parameters is compress=ywhen the data is unloaded. Then, when loaded, the data is compressed into an initial region.

This option preserves the original size of the initialized area.

The default is "y".

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

(2) backup of databases in Sybase

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

(3) backup of databases 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 cleanup of the business database, the backup operation must complete before cleaning

The mechanism for using Backup tables for log tables with a larger daily record is to create a backup table of the same structure as the production table, and to determine the time that the production table and backup table are retained depending on the needs of the project.

Assuming that the service to process the main log Production table Tb_mainlog, the requirement is that the production table retains 2 days of records, the backup table retains a record of four days, the filter date field is named StartDateTime. The interval for batch commits is five minutes, and there is one stored procedure pr_backuptable for backup, just such as the following call:

exec pr_backuptable ' tb_mainlog ', ' Tb_mainlog_bak ', 2,90, ' startdatetime ', ' yyyy.mm.dd hh:mi:ss ', 60, '

Description of the parameters:

Number 1: Production table name

Parameter 2: Backup table name

Number 3: Number of days the production table is retained

4: Number of days to keep a backup table

Parameter 5: Filter Date field names

Parameter 6: Filter The format of date fields

Number 7: Interval of batches submitted ( in minutes )

Number 8: Used for comparison of extended conditions

3. assume that the database does not have a truncation log set ( or no archive mode ). Regular maintenance of database logs is required

The settings for the archiving mode of Oracle logs are generally required for installation, the option "trunc Log on chkpt" of theSybase database is set to true, or if this option is set to false, the log truncation of the database is required periodically, and the option " autoshrink" of the SQL Server database is set to true.

(1) set the log for Oracle database to not archive mode

The operation steps are:

1) Close the database

2) launch instance according to startup Mount mode

3) switch mode

ALTER DATABASE Noarchivelog;

ALTER DATABASE open;

(2) "trunc Log on chkpt" is true in Sybase settings database db_100

Use master

Go

sp_dboption db_100, ' trunc Log on chkpt ', true

Go

Use db_100

Go

Checkpoint

Go

(3) "autoshrink" is true in SQL Server settings database db_100

sp_dboption db_100, ' autoshrink ', true

4. Regular maintenance of database indexes

For Sybase, the indexes in the tables in theSQL Server database are periodically updated statistics maintained.

Assume that the index is invalid. The index is rebuilt.

For example, to update the statistics for all the columns in all indexes in the tb_userinfo table. The following statements can be used:

Update index Statistics Tb_userinfo

5. periodically view the execution results log for database tasks and the maximum available space for the database

During database execution, it is often the log to see if the results of the related tasks are performing properly. and view the number of remaining space in the database. Suppose the available space is already very small. A certain file cleanup operation is needed.

6. We recommend consistency check and index rebuilding of the database after large batch increment, delete and change operation.

In large-scale operations, it is possible to inadvertently destroy the structure of the data table. It is therefore necessary to check the consistency of the table after the operation is complete. If you see an exception to the table structure, you need to rebuild the table and the corresponding index.

In some business software products, the maintenance of data is a complex, heavy and long-term work, the relevant maintenance experience is only in the actual operation of the ability to accumulate enough.

(I Weibo: Http://weibo.com/zhouzxi?topnav=1&wvr=5, No.: 245924426, welcome attention!) )

Let you know in advance. Software Development (34): Database Maintenance