Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > re-directed restore

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 10-01-08, 10:28
itsonlyme44 itsonlyme44 is offline
Registered User
  
Join Date: Dec 2007
Posts: 261
re-directed restore
This is what I came up with for doing a re-directed restore of a Database in our DB2 V9 on windows environment.

First create the empty database:
Code:
CREATE DATABASE JanPROD AUTOMATIC STORAGE NO  ON 'C:\' USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM PAGESIZE 4096 CATALOG TABLESPACE  MANAGED BY DATABASE USING ( FILE 'G:\DB2\NODE0000\JanPROD\CONTAINERS\catalog_tbsp' 1280000 )  USER TABLESPACE  MANAGED BY DATABASE USING ( FILE 'G:\DB2\NODE0000\JanPROD\CONTAINERS\user_tbsp' 1280000 )  TEMPORARY TABLESPACE  MANAGED BY DATABASE USING ( FILE 'G:\DB2\NODE0000\JanPROD\systemp_tbsp' 1280000 ) ;
Once the new database called JanPROD is created, I inspect the tablspaces of the DB that I want to restore FROM so that I can size the containers correctly and match up the tablespaces with their correct tablespace number..

I then came up with this for the restore:


Code:
RESTORE DATABASE PRODDM FROM "F:\Backups" TAKEN AT 20080927081115 TO "G:" INTO JanPROD NEWLOGPATH H:\DB2\NODE0000\JanPROD\NODE0000\
WITH 2 BUFFERS BUFFER 1024 REDIRECT PARALLELISM 1 WITHOUT PROMPTING;
SET TABLESPACE CONTAINERS FOR 0 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\CONTAINERS\Catalog_tbsp" 1280000 );
SET TABLESPACE CONTAINERS FOR 1 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\system_tbsp" 2560000 );
SET TABLESPACE CONTAINERS FOR 2 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\CONTAINERS\USER_TBSP" 6400000 );
SET TABLESPACE CONTAINERS FOR 3 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\INDSP16K_tbsp" 864000 );
SET TABLESPACE CONTAINERS FOR 4 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\INDSP32K_tbsp" 240000 );
SET TABLESPACE CONTAINERS FOR 5 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "C:\DB2\NODE0000\SQL00002\SYSTOOLSPACE" 8192 );
SET TABLESPACE CONTAINERS FOR 6 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\INDSP4K_tbsp" 1920000 );
SET TABLESPACE CONTAINERS FOR 7 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\TBLSP4K_tbsp" 256000 );
SET TABLESPACE CONTAINERS FOR 8 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\TBLSP16K_tbsp" 4160000 );
SET TABLESPACE CONTAINERS FOR 9 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\TBLSP32K_tbsp" 640000 );
SET TABLESPACE CONTAINERS FOR 10 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\Systools_temp_tbsp" 320768 );
SET TABLESPACE CONTAINERS FOR 11 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\JanPROD\Containers\Tempspace2_tbsp" 320768);
RESTORE DATABASE PRODDM CONTINUE;
Here is the output from the restore operation:

Code:
RESTORE DATABASE PRODDM FROM "F:\Backups" TAKEN AT 20080927081115 TO "G:" INTO CNVDM NEWLOGPATH H:\DB2\NODE0000\CNVDM\NODE0000\ WITH 2 BUFFERS BUFFER 1024 REDIRECT PARALLELISM 1 WITHOUT PROMPTING
SQL1277W  A redirected restore operation is being performed.  Table space 
configuration can now be viewed and table spaces that do not use automatic 
storage can have their containers reconfigured.
DB20000I  The RESTORE DATABASE command completed successfully.

SET TABLESPACE CONTAINERS FOR 0 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\CONTAINERS\Catalog_tbsp" 1280000 )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 1 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\system_tbsp" 2560000 )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 2 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\CONTAINERS\USER_TBSP" 6400000 )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 3 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\INDSP16K_tbsp" 864000 )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 4 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\INDSP32K_tbsp" 240000 )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 5 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "C:\DB2\NODE0000\SQL00002\SYSTOOLSPACE" 8192 )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 6 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\INDSP4K_tbsp" 1920000 )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 7 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\TBLSP4K_tbsp" 256000 )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 8 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\TBLSP16K_tbsp" 4160000 )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 9 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\TBLSP32K_tbsp" 640000 )
DB20000I  The SET TABLESPACE CONTAINERS command completed successfully.

SET TABLESPACE CONTAINERS FOR 10 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\Systools_temp_tbsp" 320768 )
SQL0298N  Bad container path.  SQLSTATE=428B2

SET TABLESPACE CONTAINERS FOR 11 IGNORE ROLLFORWARD CONTAINER OPERATIONS USING (FILE "G:\DB2\NODE0000\CNVDM\Containers\Tempspace2_tbsp" 320768)
SQL0298N  Bad container path.  SQLSTATE=428B2

RESTORE DATABASE PRODDM CONTINUE
DB20000I  The RESTORE DATABASE command completed successfully
I do a rollforward once the restore is done and it works fine.

Problem is, in addition to my JANPROD tablespace container files and other files.... it also creates tablespace container files called PRODDM (and other files) which I have to back in and delete.

Does anyone know why it is creating the PRODDM folders and files?????? AM I performing the redirected restore incorrectly?????

I'm what you might call a UDB Newbie so any help would be much appreciated.
Last edited by itsonlyme44; 10-01-08 at 10:35.
Reply With Quote
  #2 (permalink)  
Old 10-03-08, 08:24
itsonlyme44 itsonlyme44 is offline
Registered User
  
Join Date: Dec 2007
Posts: 261
anyone have any experience with redirected restores?
Reply With Quote
  #3 (permalink)  
Old 10-03-08, 09:45
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
What are ALL of the tablespaces and containers for the DB in the backup image?

Andy
Reply With Quote
  #4 (permalink)  
Old 10-03-08, 10:13
itsonlyme44 itsonlyme44 is offline
Registered User
  
Join Date: Dec 2007
Posts: 261
I am only getting the 'bad container path' error on the two SMS tablespaces (10 and 11) .
Here are the tablespace details for the Source Database

Tablespaces for Current Database
Code:
 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1280000
 Useable pages                        = 1279968
 Used pages                           = 31808
 Free pages                           = 1248160
 High water mark (pages)              = 31808
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = Database managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 2560000
 Useable pages                        = 2559968
 Used pages                           = 64
 Free pages                           = 2559904
 High water mark (pages)              = 64
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 6400000
 Useable pages                        = 6399968
 Used pages                           = 241344
 Free pages                           = 6158624
 High water mark (pages)              = 687360
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Minimum recovery time                = 2008-10-03-06.40.44.000000

 Tablespace ID                        = 3
 Name                                 = INDSP16K
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 864000
 Useable pages                        = 863936
 Used pages                           = 549184
 Free pages                           = 314752
 High water mark (pages)              = 549184
 Page size (bytes)                    = 16384
 Extent size (pages)                  = 64
 Prefetch size (pages)                = 64
 Number of containers                 = 1
 Minimum recovery time                = 2008-09-30-12.21.53.000000

 Tablespace ID                        = 4
 Name                                 = INDSP32K
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 240000
 Useable pages                        = 239936
 Used pages                           = 49856
 Free pages                           = 190080
 High water mark (pages)              = 49856
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 64
 Prefetch size (pages)                = 64
 Number of containers                 = 1
 Minimum recovery time                = 2008-04-23-16.51.53.000000

 Tablespace ID                        = 5
 Name                                 = SYSTOOLSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 8192
 Useable pages                        = 8188
 Used pages                           = 160
 Free pages                           = 8028
 High water mark (pages)              = 160
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 4
 Number of containers                 = 1
 Minimum recovery time                = 2008-04-23-16.55.40.000000

 Tablespace ID                        = 6
 Name                                 = INDSP4K
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1920000
 Useable pages                        = 1919936
 Used pages                           = 62976
 Free pages                           = 1856960
 High water mark (pages)              = 62976
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 64
 Prefetch size (pages)                = 64
 Number of containers                 = 1
 Minimum recovery time                = 2008-07-07-17.40.51.000000

 Tablespace ID                        = 7
 Name                                 = TBLSP4K
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 256000
 Useable pages                        = 255936
 Used pages                           = 108864
 Free pages                           = 147072
 High water mark (pages)              = 108864
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 64
 Prefetch size (pages)                = 64
 Number of containers                 = 1
 Minimum recovery time                = 2008-09-27-19.27.15.000000

 Tablespace ID                        = 8
 Name                                 = TBLSP16K
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 4160000
 Useable pages                        = 4159936
 Used pages                           = 1907776
 Free pages                           = 2252160
 High water mark (pages)              = 3582080
 Page size (bytes)                    = 16384
 Extent size (pages)                  = 64
 Prefetch size (pages)                = 64
 Number of containers                 = 1
 Minimum recovery time                = 2008-09-27-19.25.04.000000

 Tablespace ID                        = 9
 Name                                 = TBLSP32K
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 640000
 Useable pages                        = 639936
 Used pages                           = 79744
 Free pages                           = 560192
 High water mark (pages)              = 79744
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 64
 Prefetch size (pages)                = 64
 Number of containers                 = 1
 Minimum recovery time                = 2008-09-27-19.49.25.000000

 Tablespace ID                        = 10
 Name                                 = SYSTOOLSTMPSPACE
 Type                                 = System managed space
 Contents                             = User Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 4
 Number of containers                 = 1
 Minimum recovery time                = 2008-08-18-13.41.16.000000

 Tablespace ID                        = 11
 Name                                 = TEMPSPACE2
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Minimum recovery time                = 2008-08-22-19.11.51.000000
Last edited by itsonlyme44; 10-03-08 at 10:24. Reason: add tablespace detail
Reply With Quote
  #5 (permalink)  
Old 10-03-08, 10:24
itsonlyme44 itsonlyme44 is offline
Registered User
  
Join Date: Dec 2007
Posts: 261
Here are the container details for the source DB:

Code:
            Tablespace Containers for Tablespace 0
 Container ID                         = 0
 Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\catalog_tbsp
 Type                                 = File
            Tablespace Containers for Tablespace 1
 Container ID                         = 0
 Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\systemp_tbsp
 Type                                 = File
            Tablespace Containers for Tablespace 2
 Container ID                         = 0
 Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\user_tbsp
 Type                                 = File
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\INDSP16K_tbsp
 Type                                 = File

            Tablespace Containers for Tablespace 4
 Container ID                         = 0
 Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\INDSP32K_tbsp
 Type                                 = File
            Tablespace Containers for Tablespace 5
 Container ID                         = 0
 Name                                 = C:\DB2\NODE0000\SQL00003\SYSTOOLSPACE
 Type                                 = File
           Tablespace Containers for Tablespace 6
 Container ID                         = 0
 Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\INDSP4K_tbsp
 Type                                 = File

            Tablespace Containers for Tablespace 7
Container ID                         = 0
 Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\TBLSP4K_tbsp
 Type                                 = File
            Tablespace Containers for Tablespace 8
 Container ID                         = 0
 Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\TBLSP16K_tbsp
 Type                                 = File
 Container ID                         = 0
 Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\TBLSP32K_tbsp
 Type                                 = File
 Container ID                         = 0
 Name                                 = C:\DB2\NODE0000\SQL00003\SYSTOOLSTMPSPACE
 Type                                 = Path
            Tablespace Containers for Tablespace 11
 Container ID                         = 0
 Name                                 = E:\DB2\NODE0000\PRODDM\CONTAINERS\TEMP2
 Type                                 = Path
Reply With Quote
  #6 (permalink)  
Old 10-03-08, 12:40
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
The reason you are setting PRODDM folders is because you are doing the redirected restore incorrectly for tablespaces 10 and 11 which are SMS. Your redirected restore for them is like they were DMS hence the error you get when you try to SET CONTAINERS on them. Since those fail, you are getting the containers that are in the backup image which you know is PRODDM.

Fix the set containers for TS 10 and 11.

Andy
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.