cancel
Showing results for 
Search instead for 
Did you mean: 

DB2 redirected restore hangs...

Former Member
0 Kudos

Hello All,

We are trying to move an SAP 45B system on DB2 Version 8 fixed pack 14 from one datacenter to another.

In the process, I have taken an online backup and then trying to do a redirected restore. The script is generated succesfully and when I try to run the restore on the target server...for some reason the restore hangs.

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

SET TABLESPACE CONTAINERS FOR 42 USING ( FILE L:\db2\NQ1\sapdata1\ZSAPBTAB8

I.container000 640000 ,FILE N:\db2\NQ1\sapdata2\ZSAPBTAB8I.

container001 640000 ,FILE P:\db2\NQ1\sapdata3\ZSAPBTAB8I.co

ntainer002 640000 ,FILE R:\db2\NQ1\sapdata4\ZSAPBTAB8I.cont

ainer003 640000 ,FILE T:\db2\NQ1\sapdata5\ZSAPBTAB8I.contai

ner004 640000 ,FILE U:\db2\NQ1\sapdata6\ZSAPBTAB8I.containe

r005 640000 )

DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

RESTORE DATABASE NQ1 CONTINUE

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

This is where it hangs. I have tried to kill this and rerun the restore but the same problem.

the db2diag.log has following entries:

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

2007-10-19-06.10.06.137000+000 I79285H352 LEVEL: Severe

PID : 4424 TID : 5048 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000 DB : NQ1

APPHDL : 0-51 APPID: *LOCAL.DB2.071019061006

FUNCTION: DB2 UDB, base sys utilities, sqleserl, probe:10

RETCODE : ZRC=0xFFFFFBA1=-1119

2007-10-19-06.10.06.231000+000 I79639H370 LEVEL: Warning

PID : 4424 TID : 5048 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000 DB : NQ1

APPHDL : 0-52 APPID: *LOCAL.DB2.071019061007

FUNCTION: DB2 UDB, base sys utilities, sqleCheckForBackupRestore, probe:10

RETCODE : ZRC=0xFFFFFBA1=-1119

2007-10-19-06.10.06.231000+000 I80011H371 LEVEL: Severe

PID : 4424 TID : 5048 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000 DB : NQ1

APPHDL : 0-52 APPID: *LOCAL.DB2.071019061007

FUNCTION: DB2 UDB, base sys utilities, sqlePrepareForSerialization, probe:20

RETCODE : ZRC=0xFFFFFBA1=-1119

2007-10-19-06.10.06.231000+000 I80384H352 LEVEL: Severe

PID : 4424 TID : 5048 PROC : db2syscs.exe

INSTANCE: DB2 NODE : 000 DB : NQ1

APPHDL : 0-52 APPID: *LOCAL.DB2.071019061007

FUNCTION: DB2 UDB, base sys utilities, sqleserl, probe:10

RETCODE : ZRC=0xFFFFFBA1=-1119

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

Hope someone could help.

-Madhu

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

The only time I've seen this sort of thing is when you are doing a redirected restore and renaming the SID. In this case, you have to do the "Continue" on the original DB name not on the new name.

Former Member
0 Kudos

Dan:

Yes we are doing a redirected restore. But in our case source and the target database ids are the same. We are just moving it from one datacenter to the other.

So no question of changin the SID in the Restore CONTINUE option.

-Madhu

Former Member
0 Kudos

Hi Madhu

I like you to start again and follow these steps

1. rename db2diag.log

2. drop the target database

3. run the restore

4. after the Restore Continue step......do a list utilities show detail....see if the bytes are increasing

If the restore hang again, then please send the diaglog and few oitputs of "db2 list utilities show detail".

Other thing is if any security application is running on ur target machine like Etrust etc

Amit

Former Member
0 Kudos

Hi

you can terminate the restore using

db2 restore db <dbname> abort

if you still get the same error in starting the new restore....drop the db and then rerun the restore.

FYI the restore will take time after this statement of urs

RESTORE DATABASE NQ1 CONTINUE

it actually doesnt HANG...it is pumping in the data during this command.

You can check the progress of your restore using this command

db2 list utilities show detail

The time to pump in all the data depends upon the size of ur source db and the speed.

Amit

Former Member
0 Kudos

Hi Amit:

All options have been tried. ABORTed the restore, dropped the database, even tried uninstalling and resintalling DB2 software - no luck

I know restore takes time after

RESTORE DATABASE NQ1 CONTINUE

Our source database is around 800 Gigabytes in size. We have tried the restore earlier on 35 Gig and 150 Gig database which took 5 minutes and 20 minutes respectively. On this calculation this restore should take maximum 4 hours to complete...but it didnt complete after 24hrs aswell.

Any more ideas?

-Madhu

Former Member
0 Kudos

Hello

please abort your restore , see "Restore database" command how to abort .

There is usually no need to uninstall your database software for cleanup .

Did you uninstall your database after unsuccessfull restore attempts by means of db2 ?

Please also provide your entire restore script/procedure for review .

Best regards

dirk

Former Member
0 Kudos

Dirk:

The normal abort for the restore didnt work...so I had to kill the RESTORE forcibly.

Now: After killing the restore, I deleted all the containers...that were created by the restore...uninstalled BD2 by windows procedure - Add remove programs from control panel and also removed db2 entried from the registry.

Redtore Scritp:

@@@@@@@@@@@@@@@@@@@@@

UPDATE COMMAND OPTIONS USING S ON Z ON NQ1.out V ON;

ECHO @.\NQ1.scr@;

-- ****************************************************************************

-- ** DB2-CLP script

-- ** created for database NQ1

-- **

-- ** Usage notes:

-- **

-- ** > In general all lines beginning with '--' are only comment lines

-- ** and will be ignored by the DB2 Command Line Processor

-- ** > Lines beginning with '-- **' are just comments and only for

-- ** your information

-- ** > Lines beginning with '-- S#' mark the beginning of a section

-- ** that can be edited

-- ** > Lines beginning with '-- E#' mark the end of a section that

-- ** can be edited

-- **

-- ** The script is subdivided into three major parts:

-- ** 1. The general redirected restore procedure

-- ** (for further information about this part see the

-- ** 'RESTORE DATABASE' command in the Command

-- ** Reference of DB2)

-- ** 2. One redefining part for each tablespace

-- ** (for further information about this part see the

-- ** 'SET TABLESPACE CONTAINERS' command in the Command

-- ** Reference of DB2)

-- ** 3. The closing part

-- ** (this part completes the restore procedure.

-- ** See also the 'ROLLFORWARD DATABASE' command in

-- ** Command Reference of DB2)

-- **

-- ****************************************************************************

-- ****************************************************************************

-- ** Part 1 : General redirected restore procedure

-- ****************************************************************************

RESTORE DATABASE NQ1

-- ** Instance owner user id ( db2<dbsid> )

-- S##############################

-- USER <user> USING <password>

-- E##############################

-- INCREMENTAL AUTOMATIC

-- ** Path or device where the backup image is stored

-- S##############################

FROM W:\250907\NQ1\

-- ** Timestamp (when was the backup image taken? )

-- ** use the given format: YYYYMMDDhhmmss

-- S##############################

TAKEN AT 20070925150731

-- E##############################

-- ** If you want to restore into a new database,

-- ** you can uncomment the following lines and specify

-- ** the drive where the new database files should be written

-- ** Replace the the drive with a full qualified drive name

-- ** This command will be ignored if you restore into

-- ** a database that already exists

-- S##############################

-- TO E:

-- E##############################

-- ** New Database name

-- S##############################

INTO NQ1

-- E##############################

-- ** If you want the logfiles to be be written to a new

-- ** directory, uncomment the following line and specify

-- ** the path name where the new primary logfiles should be written

-- ** Replace the log path with a full quallified path name

-- S##############################

NEWLOGPATH K:\db2\NQ1\log_dir\

-- E##############################

-- ** Specify the number of buffers to be used for the restore procedure

-- S##############################

WITH 2 BUFFERS

-- E##############################

-- ** Specify the size of the buffers used for the restore

-- S##############################

BUFFER 1024

-- E##############################

REDIRECT

-- ** Specify the degree of parallelism used for restore

-- S##############################

-- PARALLELISM 1

-- E##############################

-- ****************************************************************************

-- ** LOGPATH information

-- ****************************************************************************

-- ** current LOGFILSIZ (4KB) : 30000

-- ** current LOGPRIMARY : 60

-- ** current LOGSECOND : 40

-- ****************************************************************************

;

-- ****************************************************************************

-- ** Part 2 : Redefining of the tablespace containers for

-- ** each tablespace

-- ****************************************************************************

SET TABLESPACE CONTAINERS FOR 0

-- ****************************************************************************

-- ** 'IGNORE ROLLFORWARD' would specify that ALTER TABLESPACE operations

-- ** in the log are to be ignored when performing a roll forward.

-- S##############################

-- IGNORE ROLLFORWARD CONTAINER OPERATIONS

-- E##############################

USING (

-- ** Container information for DMS tablespace [0] SYSCATSPACE

-- ****************************************************************************

-- ** current total pages : 120000

-- ** currently used pages : 101120

-- ** current high water mark : 101120

-- ** current page size (bytes) : 4096

-- ** current extent size (pages) : 32

-- ****************************************************************************

-- ** Container information

-- ** Type of containers can be changed. Valid modifications

-- ** are the both types FILE and DEVICE

-- ** If you want to add a container separate the new

-- ** container line by a comma.

-- **

-- ** type | name | size

-- S##############################

FILE L:\db2\NQ1\sapdata1\SYSCATSPACE.container000 60000

,FILE N:\db2\NQ1\sapdata2\SYSCATSPACE.container001 60000

--,FILE <new file> <size>

-- E##############################

);

SET TABLESPACE CONTAINERS FOR 1

-- ****************************************************************************

-- ** 'IGNORE ROLLFORWARD' would specify that ALTER TABLESPACE operations

-- ** in the log are to be ignored when performing a roll forward.

-- S##############################

-- IGNORE ROLLFORWARD CONTAINER OPERATIONS

-- E##############################

USING (

-- ** Container information for DMS tablespace [1] ZSAPBTAB7D

-- ****************************************************************************

-- ** current total pages : 15360000

-- ** currently used pages : 8431360

-- ** current high water mark : 8845568

-- ** current page size (bytes) : 8192

-- ** current extent size (pages) : 64

-- ****************************************************************************

-- ** Container information

-- ** Type of containers can be changed. Valid modifications

-- ** are the both types FILE and DEVICE

-- ** If you want to add a container separate the new

-- ** container line by a comma.

-- **

-- ** type | name | size

-- S##############################

FILE P:\db2\NQ1\sapdata3\ZSAPBTAB7D.container002 2560000

,FILE R:\db2\NQ1\sapdata4\ZSAPBTAB7D.container003 2560000

,FILE T:\db2\NQ1\sapdata5\ZSAPBTAB7D.container004 2560000

,FILE U:\db2\NQ1\sapdata6\ZSAPBTAB7D.container005 2560000

,FILE N:\db2\NQ1\sapdata2\ZSAPBTAB7D.container001 2560000

,FILE L:\db2\NQ1\sapdata1\ZSAPBTAB7D.container000 2560000

--,FILE <new file> <size>

-- E##############################

);

@@@@@@@@@@@ Tablespace for 40 containers and then @@@@

-- ****************************************************************************

-- ** Part 3 : Complete the restore (and rollforward the database)

-- ****************************************************************************

RESTORE DATABASE NQ1 CONTINUE ;

-- ****************************************************************************

-- ** If you want to rollforward the database you have to

-- ** uncomment the 'ROLLFORWARD DATABASE ...' line(s) below.

-- ** For more information about the rollforward process see

-- ** the documentation for BRDB6BRT-Tool or the

-- ** 'ROLLFORWARD DATABASE' command in the Command Reference of DB2

-- ****************************************************************************

-- S##############################

-- ROLLFORWARD DATABASE NQ1 TO END OF LOGS;

-- E##############################

ECHO ***********************************************************;

ECHO ** THE RESTORE PROCEDURE HAS NOW FINISHED SUCCESSFULLY **;

ECHO ***********************************************************;

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Former Member
0 Kudos

Hello Madhu

RETCODE : ZRC=0xFFFFFBA1=-1119

means SQL1119N

 db2 '? SQL1119N'


SQL1119N A connection to or activation of database "<name>"
          cannot be made because a previous restore is
          incomplete.

Explanation:

The database is in an inconsistent state because a system error
occurred during a restore function.

 No connection was made.

 Federated system users: this situation can be detected by the
data source.

User Response:

Issue the RESTORE command and then try the command again.

 Federated system users: if necessary isolate the problem to the
data source rejecting the request (see the problem determination
guide for procedures to follow to identify the failing data
source) and issue the RESTORE command against that data source
prior to trying the command again.

 sqlcode :  -1119

 sqlstate :  57019

It could be possible , that you had an previous unsuccessfull restore try not properly cleaned up ?

Best regards

dirk

Former Member
0 Kudos

Dirk:

Thanks for the response.

I did look into the explaination of the error code: SQL1119N, but not sure of how to find the datasource to find the root cause.

You suggest to restart the restore after succesfull clean up. I have tried earlier, with deleting the restored files and also uninstalling the whole DB2 softare and reinstalling DB2 and then trying the restore.

But, still the problem persists.

What next?

-Madhu