Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2006
    Location
    Amsterdam
    Posts
    9

    Unanswered: Cloning Database errors

    Hi y'all,

    I've searched, found and read many issues regarding this mather, but I haven't been able to find a solution yet. Perhaps this will help.

    I've been trying to clone my production database into a (extra) test database. All are situated on the same instance on the same volume. I've used the Control Center 'wizard' to backup the production DB and restore as new into a new DB. The backup was created with no errors. But when trying to restore I've encountered two errors.

    First I got error messg SQL0294N, saying: "The container is already in use". Fair enough, I understand I have to use the redirect option as well, as far as I understand to copy the existing tablespaces (which are used by the production DB) into new ones (which are to be used by the testDB). But when trying to redirect these containers, it gives me an "unknown system error", over and over again. This error occurs when trying to select the appropriate backup image in the Control Center wizard.

    I've yet created several backup images, using Control Center or using a script, but none seem to give the right solution. I understand it is not an issue regarding the backup file.

    Any ideas?

    Im working on DB2 Enterprise Server Edition, version 8.2.
    Extra information: Db2 V8.1.8.870, fixpack 8.

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow Detail

    What is your OS ?

    The following works on UNIX 5.2.0.0.:

    "

    restore db ebs use tsm taken at 20051014172247 into sumweb newlogpath '/db2aux/sumweb/' replace existing redirect

    set tablespace containers for 0 using (path '/db2aux/sumweb/c0')
    set tablespace containers for 1 using (path '/db2aux/sumweb/c1')
    set tablespace containers for 2 using (path '/db2aux/sumweb/c2')
    set tablespace containers for 3 using (DEVICE '/dev/rlsumweb01' 256000)

    restore db ebs continue

    "

    Remeber to run it line by line

    Hope this helps
    Last edited by Tank; 07-05-06 at 09:55.
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Jul 2006
    Location
    Amsterdam
    Posts
    9
    My bad.. apparently forgot something.
    The server is an Windows 2003, having Service Pack 1

    I just tried a work around, by first creating the new table, followed by the command to fill it with the backup image (as generated by Command Editor and slightly edited by me).

    RESTORE DATABASE DM_IDB user **** using **** FROM "D:\DB2 Backup\(DM_iDB) 2006-07-05" TAKEN AT 20060705084631 TO DM_KBD WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
    SET TABLESPACE CONTAINERS FOR 0 USING (FILE "D:\IDB\NODE0000\DM_IDB\SYSCATSPACE.DAT" 25600);
    SET TABLESPACE CONTAINERS FOR 1 USING (PATH "D:\IDB\NODE0000\DM_IDB\TEMPSPACE1");
    SET TABLESPACE CONTAINERS FOR 2 USING (FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_1.DAT" 4194304,FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_0.DAT" 4194304,FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_2.DAT" 4194304,FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_3.DAT" 4194304);
    SET TABLESPACE CONTAINERS FOR 3 USING (PATH "D:\IDB\NODE0000\SQL00002\SYSTOOLSPACE");
    SET TABLESPACE CONTAINERS FOR 4 USING (PATH "D:\IDB\NODE0000\DM_IDB\SYSTOOLSTMPSPACE");
    RESTORE DATABASE DM_IDB CONTINUE;

    When I state 'TO DM_KBD' it generates the warning 2539, which says that it had to replace the existing DB. The result is I do have all the systables, but none of the other tables which are in the backup image.

    When I state INTO DM_KBD (with or without the DB actually present) it just doesnt work.

    Thanks for your help by the way!

  4. #4
    Join Date
    Jun 2006
    Posts
    471

    restore

    RESTORE DATABASE DM_IDB user **** using **** FROM "D:\DB2 Backup\(DM_iDB) 2006-07-05" TAKEN AT 20060705084631 TO DM_KBD WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;

    1 : the redirect is missing
    2 : to d:\xxx\cc : the complete path
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  5. #5
    Join Date
    Jul 2006
    Location
    Amsterdam
    Posts
    9
    Thanks!

    Since I'm using a Windows envirenment I've read that I should enter de drive as a path, inserting a directory structure would end in an error messg.
    So I've inserted the drive as a path and inserted the redirect. It's now telling me the restore was succesfull, though with the famous warning:
    Restore has detected that one or more table space containers are
    inaccessible, or has set their state to 'storage must be defined'.

    Watching the DB tells me that it has only got all the 94 systables, none of the user tables.

    Also: I was in the assumption that when using INTO a path wasnt necessary. Just a DB alias. It would create one when the stated alias wasnt present. Trying this gives me the same error messg.

    So another step closer (it's at least telling me the restore is succesfull.. although it isnt ), but not quite there yet..

  6. #6
    Join Date
    Jun 2006
    Posts
    471

    restore

    the problem is when restoring a database into another db on different system or same system the restore wants to use the same path for the containers for tablespaces (not tables). as this path/file is used by original db or not existing in new server, you have to redirect the restore.
    gather source info : db2 connect to sourcedb
    db2 list tablespaces (returns the tablespaceid)
    db2 list tablespace containers for id (repeat this for each ts/container
    create redirect stmt for each path and restore the db
    have a look in sql/command reference for restore and there is a sample of redirected restore.
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  7. #7
    Join Date
    Jul 2006
    Location
    Amsterdam
    Posts
    9
    Whow.. fast reaction.. Thnx

    As you said: it will use the same tablespaces, which it can't. As far as my knowledge goes (and seeing the examples), my set statements are in order.

    First I start the restore (I manualy created the destination DB called DM_KBD) and want to create it as a clone from DM_IDB.
    Statement:

    RESTORE DATABASE DM_IDB user *** using *** FROM "D:\DB2 Backup\(DM_iDB) 2006-07-05" TAKEN AT 20060705084631 TO "D:\DM_KBD"
    REDIRECT WITHOUT PROMPTING ;


    Then, I redirect the Tablespaces / containers (or at least trying to )
    There are 4 tablespaces, which are individually called and replaced. They are placed on the D volume of the disk, and so will the new ones be. Do I have to select the destination path, or will it use the path specified in the restore statement?

    SET TABLESPACE CONTAINERS FOR 0 REPLAY ROLLFORWARD CONTAINER OPERATIONS USING (FILE "D:\IDB\NODE0000\DM_IDB\SYSCATSPACE.DAT" 25600);
    SET TABLESPACE CONTAINERS FOR 1 USING (PATH "D:\IDB\NODE0000\DM_IDB\TEMPSPACE1");
    SET TABLESPACE CONTAINERS FOR 2 USING (FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_1.DAT" 4194304,FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_0.DAT" 4194304,
    FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_2.DAT" 4194304,FILE "D:\IDB\NODE0000\DM_IDB\USERSPACE1_3.DAT" 4194304);
    SET TABLESPACE CONTAINERS FOR 3 USING (PATH "D:\IDB\NODE0000\SQL00001\SYSTOOLSPACE");
    SET TABLESPACE CONTAINERS FOR 4 USING (PATH "D:\IDB\NODE0000\DM_IDB\SYSTOOLSTMPSPACE");


    Of course, I end it all with the continue statement.
    RESTORE DATABASE DM_IDB CONTINUE;

    What am i missing here?

    I must say, quite a challenge...

  8. #8
    Join Date
    Jun 2006
    Posts
    471

    restore

    very simple
    so what is the error in this case
    the to in the restore command is only for the catalog destination
    it should take the new path/file statements
    are the directory existing ? I think you should create them
    always indicate the errors if any - also have a look in db2diag.log
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  9. #9
    Join Date
    Jul 2006
    Location
    Amsterdam
    Posts
    9
    latest error was:

    Restore has detected that one or more table space containers are
    inaccessible, or has set their state to 'storage must be defined'.


    It said the restore was succesful, but it didn't showed me all the tables. In fact, when I access the DB in Control Center, it says the restore is incomplete.
    All the 'set commands' are called to be completed successfully.

    I have created the DB and the path (D:\DM_KBD). I haven't created the full path to the locations of the needed containers. Should I define these too?

  10. #10
    Join Date
    Jun 2006
    Posts
    471

    restore

    seems clear - user error
    check in source all containers (( I mean all ))
    list tablespaces show detail - check status
    list tablespace containers for x
    in target do the same
    copy the output in a file from source and from restore - so it will be clear for others not sitting in front of your terminal.
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  11. #11
    Join Date
    Jul 2006
    Location
    Amsterdam
    Posts
    9
    Sorry, wasn't realising that I wasn't making myselve clear enough.

    List of Tablespaces of my source DB: (I removed all the page info as it doesnt seem to be that important)

    DM_IDB
    Tablespace ID = 0
    Name = SYSCATSPACE
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Number of containers = 1

    Tablespace ID = 1
    Name = TEMPSPACE1
    Type = System managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Number of containers = 1

    Tablespace ID = 2
    Name = USERSPACE1
    Type = Database managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Number of containers = 4

    Tablespace ID = 3
    Name = SYSTOOLSPACE
    Type = System managed space
    Contents = Any data
    State = 0x0000
    Detailed explanation:
    Normal
    Number of containers = 1

    Tablespace ID = 4
    Name = SYSTOOLSTMPSPACE
    Type = System managed space
    Contents = User Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Number of containers = 1

    The destination DB isnt accessible because of
    "a previous restore is incomplete."

  12. #12
    Join Date
    Jun 2006
    Posts
    471

    restore

    and what about the list tablespace containers for x (for each tablespace)
    after this I leave work and go home...
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  13. #13
    Join Date
    Jul 2006
    Location
    Amsterdam
    Posts
    9
    No worries, I appreciate all the help I can get. I'll be online tomorrow again
    But adding to answer your request:

    Tablespace Containers for Tablespace 0
    Container ID = 0
    Name = D:\IDB\NODE0000\DM_IDB\SYSCATSPACE.DAT
    Type = File

    Tablespace Containers for Tablespace 1
    Container ID = 0
    Name = D:\IDB\NODE0000\DM_IDB\TEMPSPACE1
    Type = Path

    Tablespace Containers for Tablespace 2
    Container ID = 0
    Name = D:\IDB\NODE0000\DM_IDB\USERSPACE1_1.DAT
    Type = File

    Container ID = 1
    Name = D:\IDB\NODE0000\DM_IDB\USERSPACE1_0.DAT
    Type = File

    Container ID = 2
    Name = D:\IDB\NODE0000\DM_IDB\USERSPACE1_2.DAT
    Type = File

    Container ID = 3
    Name = D:\IDB\NODE0000\DM_IDB\USERSPACE1_3.DAT
    Type = File

    Tablespace Containers for Tablespace 3
    Container ID = 0
    Name = D:\IDB\NODE0000\SQL00001\SYSTOOLSPACE
    Type = Path

    Tablespace Containers for Tablespace 4
    Container ID = 0
    Name = D:\IDB\NODE0000\DM_IDB\SYSTOOLSTMPSPACE
    Type = Path

  14. #14
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    restore

    answering from home
    D:\IDB\NODE0000\DM_IDB\SYSCATSPACE.DAT
    D:\IDB\NODE0000\DM_IDB\SYSCATSPACE.DAT

    these are both the same source container and set ablespace container command

    the idea is to redirect old containers to new direction.....and not the same
    that is the reason why it is in use... also for the other
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  15. #15
    Join Date
    May 2002
    Posts
    43
    I did not read all the responses, but I am not a DBA and hit a similar problem. Instead of selecting the backup file, you must select the backup directory the file is in...

    Hope this is what you are looking for....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •