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.
Location: Sri Lanka

Comments

  1. Nina HolmJuly 20, 2017 at 8:57 AM

    This comment has been removed by the author.

    ReplyDelete
  2. Nina HolmJuly 20, 2017 at 8:58 AM

    We provide software solutions for clients in various industries. Most
    customers are time poor and they are so busy with their day-to-day
    task management that it can be easy for them to forget to perform
    a manual backup. For more: Online Backup Services

    ReplyDelete
  3. AnonymousJanuary 11, 2018 at 2:05 AM

    Thanks for sharing information about Online Backup ,it was great and seems to be good information.

    ReplyDelete
  4. Jerry SinghMarch 9, 2023 at 9:06 PM

    EXCELLENT BLOG data center infrastructure management

    ReplyDelete

Post a Comment

Popular posts from this blog

ORA-16433: The database or pluggable database must be opened in read/write

-
Image
ORA-16433 is an error returned when a database cannot be opened. This can be a result of multiple conditions including, The inconsistency of the database Physical corruption Loss of dependent files or access issues etc.. More often than not the solution is to recover from available backups, but there can be situations where backups are not available, specially on UAT or Test setups. Although not the ideal scenario, in this type of situation you might want to open the databases even with inconsistency.  In Comes --> _ALLOW_RESETLOGS_CORRUPTION First, if you are in a position to use _ALLOW_RESETLOGS_CORRUPTION=TRUE, that means it has to be a critical moment, and when you come across more ora- errors while you are trying to reopen the databsae, that can be annoying. Note - _ALLOW_RESETLOGS_CORRUPTION=TRUE must only be used after Oracle support confirmation as a last resort. Also make sure to take cold backup before proceeding. Basic steps to be followed to resolve ORA 16433 af...
Read more

Oracle Multitenant - Create new service for PDB using DBMS_SERVICE

-
Image
When an Oracle PDB is created it starts up a default service with the PDB name. For direct connections to    PDB this service can be used. But there are situations where new service name is required in PDB level, common example is after a PDB restoration/clone, in order to enable application connections without changes in connection string. Setting service_names parameter in PDB level is not possible hence it is not an option in multitenant setup. How to create a new PDB service on Oracle 12C Check the current services, here only the default service is running, SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 NFACTORPDB READ WRITE NO SQL> select service_id, name, pdb from dba_Services; SERVICE_ID NAME PDB ---------- ---------------------------------------- ---------- 7 nfactorpdb NFACTORPDB SQL> ...
Read more

Wait for unread message on broadcast channel - Blocking Sessions

-
Image
What is Wait for unread message on broadcast channel wait event? Wait for unread message on broadcast channel is a wait event where source destination communication is involved and the database is waiting for a reply from remote party. Mostly this wait is seen related to data pump export (expdp) or import (impdp) operations. Although categorized in idle wait class "Wait for unread message on broadcast channel" can become the blocking session for some other sessions. Common causes for stuck sessions waiting on this wait event includes, Broken databases links, where remote database is not responding. Abnormally killed remote processes causing the sessions to wait. When a session is waiting on "Wait for unread message on broadcast channel" it can become a blocking session for dependent sessions. Most probably these sessions will be waiting on "library cache pin" or related concurrency wait events. The blocking_session column of (g)v$session view...
Read more