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 commercially, 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 operation of the business database, it is necessary to do some basic 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 quickly restored when the database is accidentally destroyed, and the backup strategy can be based on a weekly or monthly schedule depending on the data increment size.

If you are backing up a database db_100, here's how to do it in Oracle,Sybase , and SQL Server :

(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

Parameter description:

owner: The list of user names 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. If the parameter compress=yis specified when the data is unloaded, the data is compressed into an initial region when it is loaded. This option preserves the original size of the initialized area. The default is "y".

Log: Specifies a file that receives useful 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 cleansing of the business database, the backup operation must be completed before cleanup

The mechanism of backing up tables for log tables with a higher growth rate per day is to create a backup table of the same structure as the production table, and to determine the time that production tables and backup tables 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, a batch commit interval of five minutes, and a stored procedure pr_backuptable for backup, just call the following:

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

Parameter description:

Parameter 1: Production table name

Parameter 2: Backup table name

Parameter 3: Number of days the production table is retained

Parameter 4: Number of days to keep a backup table

Parameter 5: Filter Date field names

Parameter 6: Filter The format of date fields

Parameter 7: Interval of batches submitted ( in minutes )

Parameter 8: Extended Condition for comparison

3. If the database does not have a truncation log set ( or no archive mode ), periodic maintenance of the database log 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 SQL Server database option " autoshrink" 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. If the index is invalidated, the index is rebuilt.

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

Update index Statistics Tb_userinfo

5. periodically review the database task's run results log and database free space maximum

In the process of running the database, you should always check the log to see if the result of the task is working properly, and look at the number of remaining space in the database, if the available space is already very small, some file cleanup should be done.

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

In a large-scale operation, it is possible to inadvertently destroy the structure of the data table, so you need to check the table consistency after the operation is completed. If you find 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 can only be accumulated in the actual operation process.

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