PART1 - DB2 restore/recover online backup on different server

DB2 restore/recover database to different server

Db2 version - DB2 v9.1.0.10 LUW, no DPF

OS – AIX 5.3

Below  is the part 1 of 2 which descibes the process of restoring db2 full online database backup to different server with different mount point definition. So this will be a redirected restore with redefining database containers.

BackupProcess

Database name – ebpp

 db2 “backup database ebpp online to /rsair_dbbackup/bifinst1_backup/EBPP compress include logs”;  

LST -01

Restore Process

 Assuming same instance level as the backup host is there on the restoration host,

Step 1 – Initiate a redirect restore start

Below is the command used.. Explanation follows,

 db2 terminate  
 db2 "restore database ebpp dbpath on '/db2_main_temp1' into ebppres logtarget '/db2_main_data2/ebpplog' redirect"  

LST -02

dbpath will be the database path for newly created database.

*** This need to be run from the directory which the backup image is located.

If not, need to use “FROM” clause to provide the location of backup image.

Also if there are more then one applicable backup images, to indicate the required one “TAKEN AT” clause need to be used with the correct timestamp value.

*** ebppres is the new name provided for the database which is created for restoration.

*** logtarget provide the location which will be used by the restore process to extract the transaction logs included in the backup image. (Because the backup was online and including logs). Given folder should not contain any other log files.

*** Redirect indicates that this need to be followed by “SET TABLESPACE” commands.

That is to define new containers for tablespace in the new host according to new mount point structure available which is discussed in step 2. If you are from an oracle background this is similar to “SET NEWNAME FOR DATAFILE” in RMAN.
Below is the output of above command,

Step 2 – Redefine containers to match the new host

Below shown is the example SET TABLESPACE commands script used for above example.

 set tablespace containers for 0 using(path '/db2_main_temp1/bifinst1/ebppdth/syscatspace',path '/db2_main_temp2/bifinst1/ebppdth/syscatspace',path '/db2_main_data2/bifinst1/ebppdth/syscatspace',path '/db2_active_log/bifinst1/ebppdth/syscatspace',path '/db2_archive_log/bifinst1/ebppdth/syscatspace');  
 set tablespace containers for 1 using(path '/db2_main_temp1/bifinst1/ebppdth/tempspace',path '/db2_main_temp2/bifinst1/ebppdth/tempspace');  
 set tablespace containers for 2 using(path '/db2_main_temp1/bifinst1/ebppdth/userspace',path '/db2_main_temp2/bifinst1/ebppdth/userspace');  
 set tablespace containers for 3 using(file '/db2_main_data2/bifinst1/ebppdth/ts2'700000,file '/db2_active_log/bifinst1/ebppdth/ts2'700000, file '/db2_archive_log/bifinst1/ebppdth/ts2'1000000, file '/db2_main_temp1/bifinst1/ebppdth/ts2'500000, file );  
 set tablespace containers for 4 using(file '/db2_main_data2/bifinst1/ebppdth/ts42'110000,file '/db2_active_log/bifinst1/ebppdth/ts42'220000,file '/db2_archive_log/bifinst1/ebppdth/ts42'110000, file '/db2_main_temp1/bifinst1/ebppdth/ts42'110000,file '/db2_main_temp2/bifinst1/ebppdth/ts42'110000);  
 set tablespace containers for 5 using(path '/db2_main_temp1/bifinst1/ebppdth/tmp',path '/db2_main_temp2/bifinst1/ebppdth/tmp');  
 set tablespace containers for 6 using(path '/db2_main_temp1/bifinst1/ebppdth/systooltmpspace',path '/db2_main_temp2/bifinst1/ebppdth/systooltmpspace');  
 set tablespace containers for 7 using(path '/db2_main_temp1/bifinst1/ebppdth/largesystool',path '/db2_main_temp2/bifinst1/ebppdth/largesystool');  

LST -03

Below is the process you need to follow for write these commands,

First get all information about the tablespaces in original host,

 bash-2.04$ db2pd -d EBPP –tablespaces  

 LST -04

Check the type of the tablespace from the output, this will be one of SMS or DMS,

(More on SMS/DMS - http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fc0004961.htm)

If a table space is SMS the container definition for those only require a location to store them. (Since the containers will be managed by system)

If it is DMS we need to define the files and the sizes of those files as well.

Below is the tablesapce detail from the original server for above example.

Here in the type column ‘path= SMS tablespace’ and ‘file=DMS tablespace’

If we take tablespace 0 (SYSCATSPACE) for example on SMS tables spaces, it contains two containers on two different mount points. But in the new host it is defined with 5 locations as below.

 set tablespace containers for 0 using(  
 path '/db2_main_temp1/bifinst1/ebppdth/syscatspace',  
 path '/db2_main_temp2/bifinst1/ebppdth/syscatspace',  
 path '/db2_main_data2/bifinst1/ebppdth/syscatspace',  
 path '/db2_active_log/bifinst1/ebppdth/syscatspace',  
 path '/db2_archive_log/bifinst1/ebppdth/syscatspace')  

LST -05

So the way you are going to setup containers in the new server is independent on the setup on original host.

Tablespace 4 will be the example for DMS table spaces. In original it is defined as two files of  315500 pages. (each page is 32K as table space is defined as 32K)

So in the new server we have defined 5 files as 4X110000 and 1X220000 pages as below,

 set tablespace containers for 4 using(  
 file '/db2_main_data2/bifinst1/ebppdth/ts42' 110000,  
 file '/db2_active_log/bifinst1/ebppdth/ts42' 220000,  
 file '/db2_archive_log/bifinst1/ebppdth/ts42' 110000,   
 file '/db2_main_temp1/bifinst1/ebppdth/ts42' 110000,  
 file '/db2_main_temp2/bifinst1/ebppdth/ts42' 110000)  

LST -06

So each 110000 page file would be ~3.6GB and only thing you need to consider is the space available for these files in the given locations.

So as shown if you want you can set new containers to be of different sizes as far as these new files cover the total size of the tablespace.

Below is the output of above two commands. To complete this step all containers should be set using above script [LST 03].

  
Step 3 Restore the database

Above two steps were to setup the background for restore. Now we can actually start it.

Use the below command. (better to run in background as it could take large time to complete)

 bash-2.04$ db2 "restore database ebpp continue"  

Continue indicates that this is a restore continuing after a redirection. Below is the output.

Note that the name used is that of original database, not the name given to restoration (not ebppres but ebpp)

Now the database is restored now we need to recover the database.
This will be described in PART 2 soon.