Advance understanding of software development (34) Database maintenance

For some large software products, once commercial, will inevitably store a lot of user information, which is used in the product database (hereinafter referred to as business database) brought pressure. 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 backups of the business database are required

A regular backup of a business database can be quickly restored when the database is accidentally destroyed, and the backup strategy can be based on a weekly or monthly plan depending on the size of the data increment.

If you are backing up the database db_100, the actions 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 that will be unloaded.

Buffer: size of buffers.

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

Tables: List of table names that will be unloaded.

Rows: Indicates whether the row number of the table data is unloaded, and the default is "Y".

Compress: Indicates whether the table data is compressed into a zone during mount. If the parameter compress=y is specified when the data is unloaded, the data is compressed into an initial area when loaded. This selection retains the original size of the initialization area. The default is "Y".

LOG: Specifies a file to receive useful information and error messages.

(2) Backup of database 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:backupdev_db100.dat '
    
go
    
backup Database db_100 to dev_db100 go
    

2. Regular data cleanup of the business database, backup operations must be completed before cleanup

The mechanism for backing up tables for log tables that have increased daily records is to establish a backup table that is the same structure as the production table, and to determine how long the production table and backup table are retained, depending on the needs of the project.

Suppose that number 1000th business to deal with the main log Production table Tb_mainlog, the requirement is that the production table to keep 2 days of records, backup table to retain 90 days of records, filter the date of the field named StartDateTime, batch submitted interval of 60 minutes, there is a stored procedure Pr_ Backuptable for backup, you only need to 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: The number of days a backup table remains

Parameter 5: Filter Date field names

Parameter 6: Filter the format of date fields

Parameter 7: Interval submitted by batches (in minutes)

Parameter 8: Extension criteria for comparison

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/Programming/project/