Backup and recovery of DB2 databases

DB2 database backup and Recovery 1. Backup 1.1 offline backup (must operate on the PC where the database is located) STEP 1 connect to the database to be backed up

C:\Documents and SETTINGS\ADMINISTRATOR>DB2 connect to NCC user Nccwykpi using NCCWYKPI

STEP 2 shows the status of the database application

C:\Documents and SETTINGS\ADMINISTRATOR>DB2 list applications

STEP 3 Stop database application

C:\Documents and SETTINGS\ADMINISTRATOR>DB2 force applications All

Note: Because the command is asynchronous, the following error message occurs when you make a backup. You can now execute the DB2 force applications all command again.

STEP 4 Backup of the database in offline state

C:\Documents and SETTINGS\ADMINISTRATOR>DB2 Backup database NCC to D:\db2\backup

Note: The output directory of the specified backup file must be created, or an error will be made.

STEP 5 View Backup History

C:\Documents and SETTINGS\ADMINISTRATOR>DB2 list history backup all for NCC

1.2 Online Backup

Note: If you want to make an online backup, you need to modify the two parameters: USEREXIT and Logretain , modify it to on , the default is off , the log mode is used by default, after modifying the parameters, it can be changed to archive log mode, enabling the UserExit do the archiving of database logs.

STEP 1 View the current properties of the database

C:\Documentsand SETTINGS\ADMINISTRATOR>DB2 Get DB cfg

STEP 2 Sets the status of the UserExit and Logretain parameters on

C:\Documentsand SETTINGS\ADMINISTRATOR>DB2 Update db cfg for nccusing userexit on

C:\Documentsand SETTINGS\ADMINISTRATOR>DB2 Update db cfg for nccusing Logretain on

Note: Modifying the configuration parameters must be a database administrator, and the changes will not take effect until all apps have been deactivated.

STEP 3 Connect the database that needs to be backed up (note)

Note: At this point because the database is in backup pending status, you must do an offline full standby to connect to the database.

STEP 4 Backup of the database in the online state

C:\Documentsand SETTINGS\ADMINISTRATOR>DB2 Backup Database NCC online to D:\db2\backup

1.3 Incremental backup

Note: You must modify the Trackmod is on status, and has a full backup.

STEP 1 Modify the Trackmod parameter to the on state

C:\Documentsand SETTINGS\ADMINISTRATOR>DB2 Update db cfg for nccusing trackmod Yes

STEP 2 Stop database application

C:\Documentsand SETTINGS\ADMINISTRATOR>DB2 Force Applicationsall

STEP 3 Incremental backup of the database

C:\Documentsand SETTINGS\ADMINISTRATOR>DB2 Backup DATABASE NCC online increment

Aldelta to D:\db2\backup

Note: You need to do an offline full backup before you can make incremental backups. Otherwise it is the backup pending state.

If you do not perform an offline full standby at this time, the following error will occur.

So

2. Recover 2.1 Restore Full backup Step 1 view backup History

C:\Documentsand settings\administrator>db2 List History backupall for NCC

STEP 2 Stop database application

C:\Documentsand SETTINGS\ADMINISTRATOR>DB2 Force Applicationsall

STEP 3 restores data at a point in time

C:\Documentsand settings\administrator>db2 Restore Database Nccfrom d:\db2\backup taken at 20120206094204

STEP 4 rollback to the end of the log (can not be executed, that is, the log is not restored)

C:\Documentsand settings\administrator>db2 Rollforward DATABASENCC to end of logs and stop

2.2 Restore Incremental Backup Step 1 Connection Database

C:\Documentsand SETTINGS\ADMINISTRATOR>DB2 Connect to NCC USERNCCWYKPI using NCCWYKPI

STEP 2 shows the status of the database application

C:\Documentsand SETTINGS\ADMINISTRATOR>DB2 List Applications

STEP 3 Stop database application

C:\Documentsand SETTINGS\ADMINISTRATOR>DB2 Force Applicationsall

STEP 4 Restore Incremental backup

C:\Documentsand settings\administrator>db2 Restore Database Nccincremental automatic from d:\db2\backup taken at 20120206133457

STEP 5 rollback to the end of the log (can not be executed, that is, the log is not restored)

C:\Documentsand settings\administrator>db2 Rollforward DATABASENCC to end of logs and stop

3. Check Backup integrity

When the backup is complete, use the DB2CKBKP command not only to check the integrity of the DB2 database backup file, but also to query the metadata of the DB2 database backup file. You can also use the db2ckbkp–h< backup file > To check the type of DB2 database backup.

3.1 Use the db2ckbkp–h< backup file > command to view the type of DB2 database backup

C:\Documentsand Settings\administrator>db2ckbkp-hd:\db2\backup\ ncc.0.db2.node0000.catn0000.20120206133232.001

Note: We can use the above output to

To determine the type of backup. The three keywords are described below:

Incremental and Delta are described below:

3.2 Rolling back logs

When the rollback log fails to find the log file, the database is in Roll-forward pending status, the database cannot be connected or activated at this time. The solution is as follows:

3.3 Database Recovery

If archive logging is enabled for a database, it is automatically placed in the rollforwardpending when the database is restored state. You can use the following command to tell DB2 not to put the database in the rollforwardpending state.

C:\Documentsand Settings\administrator>restore Database NCC Fromd:\db2\backup taken at 20120206151346 Withoutrolling forward

If the database has archive logging enabled, it is in rollforwardpending status, the connection to the database will prompt

You can use the following command to restore the database state to normal

The connection database can then be successfully connected.

Backup and recovery of DB2 databases