DB2 database uses backup to restore an instance from a different machine for analysis

A production DB2 database needs to back up all its data and then restore and import it to a test database. Some problems have been encountered and finally solved successfully, the steps are recorded for easy learning and analysis. System Environment: AIX5.

A production DB2 database needs to back up all its data and then restore and import it to a test database. Some problems have been encountered and finally solved successfully, the steps are recorded for easy learning and analysis. System Environment: AIX5.

A production DB2 database needs to back up all its data, the Hong Kong virtual host, and then restore and import it to a test database. Some problems encountered during the process finally solved the problem. The Hong Kong server, the steps are recorded for easy learning and analysis.

System Environment: AIX5.3

Database: DB2 V9.1

Two servers: production server 192.168.11.178

Test server 10.10.11.81

I. Backup

Back up the databases on the production server and test server respectively (the databases on the two servers are the same)

$ Db2 backup db LAW online to/basefsnew/db2bak0111 include logs # Production

The backup is successful. The time stamp of this backup image is: 20130111180236

$ Db2 backup db LAW online to/basefs/db2bak0111 include logs # Test

The backup is successful. The time stamp of this backup image is: 20130111190381

2. Transmit the backup of the production database to the test Server

You can use the SCP command or other file transmission software to transmit backups. The Hong Kong server reminds you that the uploaded backups must be changed to the owner and group and have permissions. Otherwise, errors may occur during restoration.

Iii. Specific restoration process and problem solving

$ Db2 force applications all (stop all application connections first)
DB20000I The force application command completed successfully.
DB21024I This command is asynchronous and may not be valid tive immediately.

Restore on the test Server
$ Db2 restore db LAW from/home taken at 20130111180236
SQL2539W Warning! Restoring to an existing database that is the same as the backup image database. The database files will be deleted.
Do you want to continue? (Y/n) y
DB20000I The restore database command completed successfully.
Restored successfully

However, an error is reported when you connect to the database.

$ Db2 connect to LAW
SQL1117N A connection to or activation of database "LAW" cannot be made
Because of ROLL-forward pending. SQLSTATE = 57019

This prompt indicates that logs during rollback are required to activate the database.

Execute the following sentence to roll forward

$ Db2 rollforward db LAW to end of logs and complete
SQL4970N Roll-forward recovery on database "LAW" cannot reach the specified
Stop point (end-of-log or point-in-time) because of missing log file (s) on
Node (s) "0 ".

The system prompts that the log is missing and cannot reach the end point.

Note: This is the status when a successful operation is successful.

$ Db2 rollforward db LAW to end of logs and complete

Rollforward Status

Input database alias = db
Number of nodes have returned status = 1

Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed =-
Last committed transaction = 2013-1-11-10.59.23.000000

DB20000I The ROLLFORWARD command completed successfully.

Run the following command to check the database status and prompt:
$ Db2 rollforward db LAW query status

Rollforward Status

Input database alias = LAW
Number of nodes have returned status = 1

Node number = 0
Rollforward status = DB working
Next log file to be read = s00020.9.log
Log files processed =-
Last committed transaction = 2012-08-24-12.02.32.000000
Through this command, we found that the next log is s00020.9.log,
Find the path of the file and synchronize or copy all the logs on the production server after the backup time point to the test server.
Find the DB2 log on the production server. The directory is/home/db2logs/db2inst2/LAW/NODE0000/C0000004.
Copy all logs from the backup time in this directory to the corresponding directory of the test server (which can be placed in the directory of the database backup, modify the owner, group, and permissions of log files to prevent permission inconsistency.

# Chown db2inst1: db2iadm1 S0003170.LOG
# Chown db2inst1: db2iadm1 S0003171.LOG
# Chown db2inst1: db2iadm1 S0003172.LOG
# Chown db2inst1: db2iadm1 S0003173.LOG
# Chown db2inst1: db2iadm1 S0003174.LOG
# Chown db2inst1: db2iadm1 S0003175.LOG
# Chown db2inst1: db2iadm1 S0003176.LOG
# Chmod 744 S0003170.LOG
# Chmod 744 S0003171.LOG
# Chmod 744 S0003172.LOG
# Chmod 744 S0003173.LOG
# Chmod 744 S0003174.LOG
# Chmod 744 S0003175.LOG
# Chmod 744 S0003176.LOG

Use the following command to roll forward logs and stop log overflow. Note that the "("/home ")" Format refers to the Log Path.

$ Db2 rollforward db LAW to end of logs and stop overflow log path "("/home ")"