Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to analyze the recovery of DB2 database at a specified point in time. Many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something from this article.

The DB2 database on the AIX host of the company's production environment needs to be restored because of the misoperation of the developer, which results in the deletion of an important table. For the sake of safety, it cannot operate on the database of the production environment and needs to be restored in the test environment.

Asked the developer, the time the table was deleted was about 08:30 in the afternoon on May 31, it is now 10:50 in the evening, more than two hours have passed since the time of the accident, and it needs to be restored within one hour according to the security level. Recovery in this situation is a typical roll-forward recovery, which requires a combination of full database backup and logging, and then restore the database or selected tablespace to a specific point in time. If all log files are available from the time of backup to the time of failure, you can restore to any point in time covered in the log.

First of all, I checked the backup of the database. There was a full backup last Sunday, and all the logs from the full backup to the point of failure were intact, which made me feel relieved.

Next, find an AIX minicomputer in the test environment that has the same version as the production environment DB2 database, and transfer the full database backup and the corresponding logs. (note: the physical log format varies with different database versions, so it is easy to report SQL2547 errors during recovery, so the log cannot be rolled forward.) for full backups and logs transferred from the production environment to the test environment, you should pay attention to modify the owner and permissions of the file to avoid unreadable errors.

1. Restore a full backup

AIX minicomputer that uses SECURE CRT to enter the test environment

Db2 connect to banoab (the database name of the test environment is the same as that of the production environment)

Database Connection Information

Database server = DB2/AIX64 9.1.7

SQL authorization ID = DB2INST1

Local database alias = BANOAB

$db2 force applications all (kill all app connections)

DB20000I The FORCE APPLICATION command completed successfully.

DB21024I This command is asynchronous and may not be effective immediately.

$db2 drop db banoab (delete the library of the test environment)

DB20000I The DROP DATABASE command completed successfully.

(restore the full backup library from the location where the production warehouse is stored)

$db2 restore db banoab from / backup taken at 20130526190620 to / home/db2inst1

DB20000I The RESTORE DATABASE command completed successfully.

Roll forward the log to a specified point in time

$db2 connect to banoab

SQL1117N A connection to or activation of database "BANOAB" cannot be made

Because of ROLL-FORWARD PENDING. SQLSTATE=57019

Connect to the restored library and prompt to roll forward the log, and then roll forward the database to the point in time before deletion

/ backup/logs is the directory where the production database log is stored

$db2 "rollforward db banoab to 2013-05-31-20.00.00.000000 using local time and complete overflow log path (/ backup/logs)"

Rollforward Status

Input database alias = banoab

Number of nodes have returned status = 1

Node number = 0

Rollforward status = not pending

Next log file to be read =

Log files processed = S0001593.LOG-S0001683.LOG

Last committed transaction = 2013-05-31-20.00.00.000000 Local

DB20000I The ROLLFORWARD command completed successfully.

Roll forward the log successfully and inform the developer to check

After a while, the developer reported that no data was found and that it was still deleted.

This time a little wonder, how could not, time has passed half an hour, it is really anxious ah

I wonder if the time of the two computers is not the same, because the local time is used when rolling forward.

Check the time of two small machines immediately.

Sun Jun 4 15:43:47 BEIST 2013 (production machine time)

Sun Jun 4 15:44:01 CDT 2013 (tester time)

Notice the red part. BEIST and CDT are not in the same time zone, and there is an 8-hour difference between BEIST and CDT. Because the time zone is inconsistent, the time is not uniform, so there is a problem.

So how to change the CDT time display method of AIX to BEIST? The method is as follows

Smitty = > System Environments = > Change / Show Date and Time

= > Change Time Zone Using User Inputted Values

And then modify it like this:

Standard Time ID (only alpahabets) [BEIST]

* Standard Time Offset from CUT ([+ | -] HH:MM:SS) [- 8]

Day Light Savings Time ID (only alpahabets) []-- notice that it is empty

And then restore it again.

$db2 force applications all

DB20000I The FORCE APPLICATION command completed successfully.

DB21024I This command is asynchronous and may not be effective immediately.

$db2 drop db banoab

DB20000I The DROP DATABASE command completed successfully.

$db2 restore db banzub from / backup taken at 20130526190620 to / home/db2inst1

DB20000I The RESTORE DATABASE command completed successfully.

$id-db2inst

Uid=301 (db2inst1) gid=301 (db2grp1) groups=1 (staff)

$db2 connect to banoab

SQL1117N A connection to or activation of database "BANOAB" cannot be made

Because of ROLL-FORWARD PENDING. SQLSTATE=57019

$db2 "rollforward db banoab to 2013-05-31-20.00.00.000000 using local time and complete overflow log path (/ backup/logs)"

Rollforward Status

Input database alias = banoab

Number of nodes have returned status = 1

Node number = 0

Rollforward status = not pending

Next log file to be read =

Log files processed = S0001593.LOG-S0001679.LOG

Last committed transaction = 2013-05-31-20.00.00.000000 Local

DB20000I The ROLLFORWARD command completed successfully.

Check again, sure enough, the data is available, the table is restored, and everything OK

Summary: when doing data recovery, we must be calm and calm, encounter problems must be able to analyze, understand the technology and analysis in place in order to overcome the difficulties.

Attached: the concept and knowledge points of backup and recovery of DB2 database

Backup types: offline backup (also known as cold backup or offline backup), online backup (also known as hot backup or online backup), full backup, incremental backup (also known as cumulative backup), differential backup

Database backup file structure

Recovery types: crash recovery, version recovery, roll forward recovery (any point-in-time recovery, restore to the most recent point in time)

Recovery situation: full recovery, incomplete recovery

Restore the order of the database manually

Log type: circular log (default), archive log (activity log, online archive log, offline archive log)

Log types and recovery types: circular logs only support crash recovery and version recovery, and archive logs support all types of recovery

All backup sets generated by online backups need to be restored with archived logs, which is the only way to allow users to perform a roll forward (rollforward) recovery.

The roll forward time is after the minimum recovery time point and before the last transaction commit time point.

After reading the above, do you have any further understanding of how to perform the analysis of DB2 database recovery at a specified point in time? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.