By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,018 Members | 930 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,018 IT Pros & Developers. It's quick & easy.

Redirected Restore

P: n/a
Hi,
I am doing a redirected restore from an offline backup file. All
the paths and files have been created and I managed to do all the set
tablespaces commands. But once I do the "restore db <dbnamecontinue"
it gives me the following error

db2 =restore db cdbdwp1 continue
SQL2059W A device full warning was encountered on device "DATA32".
Do you want to continue(c), terminate this device only(d), abort the
utility(t) ? (c/d/t)

For data32 this was the command I had run:

db2 "set tablespace containers for 2 using (file
'/u09/db2data/cdbdwm1/data32' 1000)"

and yes, 2 is data 32

I am pasting the db2 list tablespaces out put from the original
database

************************************************** ************************************************** ***
Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 2
Name = DATA32
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 3
Name = INDX32
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 4
Name = TEMP32
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 5
Name = SYSTOOLSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 6
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 7
Name = DATA4
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 8
Name = TEMP4
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
************************************************** **************************************************

This error was occuring for a long time and thats why I am trying
data32 with just 1000 pages. Can anybody help me with this? Thanks in
advance.

Jul 4 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a

Look in the db2diag.log it should be an entry for the tablespace ie
which size it requires to be able and continue with the restore

Jul 4 '06 #2

P: 1
hiho,
did you check with your highwatermark?

if you do a "list tablespaces show detail", which looks like this:
Expand|Select|Wrap|Line Numbers
  1.  Tablespace ID                        = 0
  2.  Name                                 = SYSCATSPACE
  3.  Type                                 = Database managed space
  4.  Contents                             = Any data
  5.  State                                = 0x0000
  6.    Detailed explanation:
  7.      Normal
  8.  Total pages                          = 49384
  9.  Useable pages                        = 49360
  10.  Used pages                           = 36816
  11.  Free pages                           = 12544
  12.  High water mark (pages)              = 48825
  13.  Page size (bytes)                    = 4096
  14.  Extent size (pages)                  = 16
  15.  Prefetch size (pages)                = 4
  16.  Number of containers                 = 1
  17.  Minimum recovery time                = 2004-05-15-03.08.46.000000
... the significant lines are marked bold.

so if you do a redirected restore, you have to specify the new containers at least as big as the HWM.

godd luck!
Jul 4 '06 #3

P: n/a
can you provide the output of "db2 list tablespaces show detail"?

Asphalt Blazer 写道:
Hi,
I am doing a redirected restore from an offline backup file. All
the paths and files have been created and I managed to do all the set
tablespaces commands. But once I do the "restore db <dbnamecontinue"
it gives me the following error

db2 =restore db cdbdwp1 continue
SQL2059W A device full warning was encountered on device "DATA32".
Do you want to continue(c), terminate this device only(d), abort the
utility(t) ? (c/d/t)

For data32 this was the command I had run:

db2 "set tablespace containers for 2 using (file
'/u09/db2data/cdbdwm1/data32' 1000)"

and yes, 2 is data 32

I am pasting the db2 list tablespaces out put from the original
database

************************************************** ************************************************** ***
Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 2
Name = DATA32
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 3
Name = INDX32
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 4
Name = TEMP32
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 5
Name = SYSTOOLSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 6
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 7
Name = DATA4
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal

Tablespace ID = 8
Name = TEMP4
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
************************************************** **************************************************

This error was occuring for a long time and thats why I am trying
data32 with just 1000 pages. Can anybody help me with this? Thanks in
advance.
Jul 4 '06 #4

P: n/a
Yes here it is:

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 28351
Useable pages = 28351
Used pages = 28351
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

Tablespace ID = 2
Name = DATA32
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 640000
Useable pages = 639936
Used pages = 541312
Free pages = 98624
High water mark (pages) = 541312
Page size (bytes) = 32768
Extent size (pages) = 64
Prefetch size (pages) = 256
Number of containers = 1

Tablespace ID = 3
Name = INDX32
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 320000
Useable pages = 319936
Used pages = 291264
Free pages = 28672
High water mark (pages) = 291264
Page size (bytes) = 32768
Extent size (pages) = 64
Prefetch size (pages) = 256
Number of containers = 1

Tablespace ID = 4
Name = TEMP32
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) = 64
Prefetch size (pages) = 256
Number of containers = 1

Tablespace ID = 5
Name = SYSTOOLSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 2095
Useable pages = 2095
Used pages = 2095
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 64
Number of containers = 1

Tablespace ID = 6
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) = 64
Prefetch size (pages) = 128
Number of containers = 1

Tablespace ID = 7
Name = DATA4
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 173720
Useable pages = 173720
Used pages = 173720
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 64
Prefetch size (pages) = 256
Number of containers = 1

Tablespace ID = 8
Name = TEMP4
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) = 4096
Extent size (pages) = 64
Prefetch size (pages) = 256
Number of containers = 1

and here are the commands I used to create the new database:

************************************************** *****************************************
db2 "restore db cdbdwp1 user db2inst1 using figaro$ from
/u04/db2bkup/cdbdwp1 to /u01/db2cat into cdbdwm1 redirect without
rolling forward without prompting"

db2 "set tablespace containers for 0 using (path 'SQLT0000.0')"

db2 "set tablespace containers for 2 using (file
'/u09/db2data/cdbdwm1/data32' 640000)"

db2 "set tablespace containers for 3 using (file
'/u08/db2indx/cdbdwm1/indx32' 320000)"

db2 "set tablespace containers for 4 using (path
'/u09/db2temp/cdbdwm1/temp32')"

db2 "set tablespace containers for 5 using (path 'SYSTOOLSPACE')"

db2 "set tablespace containers for 6 using (path 'SYSTOOLSTMPSPACE')"

db2 "set tablespace containers for 7 using (path
'/u09/db2data/cdbdwm1/data4')"

db2 "set tablespace containers for 8 using (path
'/u09/db2temp/cdbdwm1/temp4')"

************************************************** ************************************************

thanks

Jul 4 '06 #5

P: n/a
m
Asphalt Blazer wrote:
Yes here it is:

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 28351
Useable pages = 28351
Used pages = 28351
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

Tablespace ID = 2
Name = DATA32
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 640000
Useable pages = 639936
Used pages = 541312
Free pages = 98624
High water mark (pages) = 541312
Page size (bytes) = 32768
Extent size (pages) = 64
Prefetch size (pages) = 256
Number of containers = 1

Tablespace ID = 3
Name = INDX32
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 320000
Useable pages = 319936
Used pages = 291264
Free pages = 28672
High water mark (pages) = 291264
Page size (bytes) = 32768
Extent size (pages) = 64
Prefetch size (pages) = 256
Number of containers = 1

Tablespace ID = 4
Name = TEMP32
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) = 64
Prefetch size (pages) = 256
Number of containers = 1

Tablespace ID = 5
Name = SYSTOOLSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 2095
Useable pages = 2095
Used pages = 2095
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 64
Number of containers = 1

Tablespace ID = 6
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) = 64
Prefetch size (pages) = 128
Number of containers = 1

Tablespace ID = 7
Name = DATA4
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 173720
Useable pages = 173720
Used pages = 173720
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 64
Prefetch size (pages) = 256
Number of containers = 1

Tablespace ID = 8
Name = TEMP4
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) = 4096
Extent size (pages) = 64
Prefetch size (pages) = 256
Number of containers = 1

and here are the commands I used to create the new database:

************************************************** *****************************************
db2 "restore db cdbdwp1 user db2inst1 using figaro$ from
/u04/db2bkup/cdbdwp1 to /u01/db2cat into cdbdwm1 redirect without
rolling forward without prompting"

db2 "set tablespace containers for 0 using (path 'SQLT0000.0')"

db2 "set tablespace containers for 2 using (file
'/u09/db2data/cdbdwm1/data32' 640000)"

db2 "set tablespace containers for 3 using (file
'/u08/db2indx/cdbdwm1/indx32' 320000)"

db2 "set tablespace containers for 4 using (path
'/u09/db2temp/cdbdwm1/temp32')"

db2 "set tablespace containers for 5 using (path 'SYSTOOLSPACE')"

db2 "set tablespace containers for 6 using (path 'SYSTOOLSTMPSPACE')"

db2 "set tablespace containers for 7 using (path
'/u09/db2data/cdbdwm1/data4')"

db2 "set tablespace containers for 8 using (path
'/u09/db2temp/cdbdwm1/temp4')"

************************************************** ************************************************

thanks
Your SET TABLESPACES has to be at LEAST equal to the number of used
pages for the tablespace you are restoring - and a couple extra would be
better. to DATA32 needs to be 541312 pages - and the disk you are
creating this on has to have enough space....

M
Jul 5 '06 #6

P: n/a
m
Asphalt Blazer wrote:
Yes here it is:

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 28351
Useable pages = 28351
Used pages = 28351
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 128
Number of containers = 1

Tablespace ID = 2
Name = DATA32
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 640000
Useable pages = 639936
Used pages = 541312
Free pages = 98624
High water mark (pages) = 541312
Page size (bytes) = 32768
Extent size (pages) = 64
Prefetch size (pages) = 256
Number of containers = 1

Tablespace ID = 3
Name = INDX32
Type = Database managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 320000
Useable pages = 319936
Used pages = 291264
Free pages = 28672
High water mark (pages) = 291264
Page size (bytes) = 32768
Extent size (pages) = 64
Prefetch size (pages) = 256
Number of containers = 1

Tablespace ID = 4
Name = TEMP32
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) = 64
Prefetch size (pages) = 256
Number of containers = 1

Tablespace ID = 5
Name = SYSTOOLSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 2095
Useable pages = 2095
Used pages = 2095
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 64
Number of containers = 1

Tablespace ID = 6
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) = 64
Prefetch size (pages) = 128
Number of containers = 1

Tablespace ID = 7
Name = DATA4
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 173720
Useable pages = 173720
Used pages = 173720
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 64
Prefetch size (pages) = 256
Number of containers = 1

Tablespace ID = 8
Name = TEMP4
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) = 4096
Extent size (pages) = 64
Prefetch size (pages) = 256
Number of containers = 1

and here are the commands I used to create the new database:

************************************************** *****************************************
db2 "restore db cdbdwp1 user db2inst1 using figaro$ from
/u04/db2bkup/cdbdwp1 to /u01/db2cat into cdbdwm1 redirect without
rolling forward without prompting"

db2 "set tablespace containers for 0 using (path 'SQLT0000.0')"

db2 "set tablespace containers for 2 using (file
'/u09/db2data/cdbdwm1/data32' 640000)"

db2 "set tablespace containers for 3 using (file
'/u08/db2indx/cdbdwm1/indx32' 320000)"

db2 "set tablespace containers for 4 using (path
'/u09/db2temp/cdbdwm1/temp32')"

db2 "set tablespace containers for 5 using (path 'SYSTOOLSPACE')"

db2 "set tablespace containers for 6 using (path 'SYSTOOLSTMPSPACE')"

db2 "set tablespace containers for 7 using (path
'/u09/db2data/cdbdwm1/data4')"

db2 "set tablespace containers for 8 using (path
'/u09/db2temp/cdbdwm1/temp4')"

************************************************** ************************************************

thanks
Besides the tablespace size and disk space used - there is another
possibility - the filesystem needs to be "large file enabled" in order
to create a singe file (DMS) that is 20GB (640000 * 32768) - OR - you
will need to create multiple containers if you have to stay under 2GB
per file

M
Jul 5 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.