Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Apr 2008
    Posts
    32

    Unanswered: Restore db backup to different machine

    Daers

    I am performing restor db to different machine with different instance file system path as the following :


    Restore db [IEABDB5U]from different machine(iwmbuat) to (wmbint):

    source:
    Instance_home:/var/mqsi/iwmbuat

    Destaination:
    Instance_home:/var/mqsi/wmbint

    I have to create same path same as source system mentioned in backup image name :
    1. >cd /var/mqsi
    2. mkdir iwmbuat
    3.chmod 750 iwmbuat


    Perform actual Restore:
    1.db2 RESTORE DATABASE IEABDB5U FROM '/var/mqsi/wmbint/backup' INTO IEABDB5U REDIRECT WITHOUT PROMPTING;

    2.db2 RESTORE DATABASE IEABDB5U CONTINUE;


    Restorer success but once i check the tables , no thing restore

    db2 list tables

    0 selcted.

    Every thing same fine in db2diag but i don't know why it's not restoring perfectly.

    Regards,
    Nasser

  2. #2
    Join Date
    Jan 2010
    Posts
    335
    If you keep the original fileystem structure you don't need the redirect option.
    It's only need when you want to restore the database into another filesystem-structure. Have a look at the generated scripts from the redirect option. You have to define tablespace containers and storage pathes.

    If you have the same pathes try:
    db2 RESTORE DATABASE IEABDB5U FROM '/var/mqsi/wmbint/backup' WITHOUT PROMPTING

  3. #3
    Join Date
    Apr 2008
    Posts
    32
    thanks nvk@vhv for your reply.

    I am restoring to another filesystem-structure

    And in destaination i create the source filesystem-structure.

    Regarding table space it's atutomatic storage for all table spaces so i am unable to redirect.

    restoreing db successed but tables for user table space empty space empty.

  4. #4
    Join Date
    Apr 2008
    Posts
    32
    gnerated script :

    "iwmbuat_script" 86 lines, 4901 characters
    -- ************************************************** ***************************
    -- ** automatically created redirect restore script
    -- ************************************************** ***************************
    UPDATE COMMAND OPTIONS USING S ON Z ON IEABDB5U_NODE0000.out V ON;
    SET CLIENT ATTACH_DBPARTITIONNUM 0;
    SET CLIENT CONNECT_DBPARTITIONNUM 0;
    -- ************************************************** ***************************
    -- ** automatically created redirect restore script
    -- ************************************************** ***************************
    RESTORE DATABASE IEABDB5U
    -- USER <username>
    -- USING '<password>'
    FROM '/var/mqsi/wmbint/backup'
    TAKEN AT 20100714130833
    -- ON '/var/mqsi/iwmbuat'
    -- DBPATH ON '<target-directory>'
    INTO IEABDB5U
    -- NEWLOGPATH '/var/mqsi/iwmbuat/iwmbuat/NODE0000/SQL00001/SQLOGDIR/'
    -- WITH <num-buff> BUFFERS
    -- BUFFER <buffer-size>
    -- REPLACE HISTORY FILE
    -- REPLACE EXISTING
    REDIRECT
    -- PARALLELISM <n>
    WITHOUT ROLLING FORWARD
    -- WITHOUT PROMPTING
    ;
    -- ************************************************** ***************************
    -- ** table space definition
    -- ************************************************** ***************************
    -- ************************************************** ***************************
    -- ** Tablespace name = SYSCATSPACE
    -- ** Tablespace ID = 0
    -- ** Tablespace Type = Database managed space
    -- ** Tablespace Content Type = All permanent data. Regular table space.
    -- ** Tablespace Page size (bytes) = 4096
    -- ** Tablespace Extent size (pages) = 4
    -- ** Using automatic storage = Yes
    -- ** Auto-resize enabled = Yes
    -- ** Total number of pages = 16384
    -- ** Number of usable pages = 16380
    -- ** High water mark (pages) = 9544
    -- ************************************************** ***************************
    -- ************************************************** ***************************
    -- ** Tablespace name = TEMPSPACE1
    -- ** Tablespace ID = 1
    -- ** Tablespace Type = System managed space
    -- ** Tablespace Content Type = System Temporary data
    -- ** Tablespace Page size (bytes) = 4096
    -- ** Tablespace Extent size (pages) = 32
    -- ** Using automatic storage = Yes
    -- ** Total number of pages = 1
    -- ************************************************** ***************************
    -- ************************************************** ***************************
    -- ** Tablespace name = USERSPACE1
    -- ** Tablespace ID = 2
    -- ** Tablespace Type = Database managed space
    -- ** Tablespace Content Type = All permanent data. Large table space.
    -- ** Tablespace Page size (bytes) = 4096
    -- ** Tablespace Extent size (pages) = 32
    -- ** Using automatic storage = Yes
    -- ** Auto-resize enabled = Yes
    -- ** Total number of pages = 8192
    -- ** Number of usable pages = 8160
    -- ** High water mark (pages) = 4576
    -- ************************************************** ***************************
    -- ************************************************** ***************************
    -- ** Tablespace name = SYSTOOLSPACE

  5. #5
    Join Date
    Jan 2010
    Posts
    335
    -- ON '/var/mqsi/iwmbuat'
    -- DBPATH ON '<target-directory>'

    is your friend for Automated Storage

  6. #6
    Join Date
    Apr 2008
    Posts
    32
    Dear nvk@vhv


    Sorry I don't get it ,What do you mean?


    Regards,
    Nasser

  7. #7
    Join Date
    Jan 2010
    Posts
    335
    Since you are using automated storage, you have to specify a new storage path in the restore command. The specific option is
    -- ON '/var/mqsi/iwmbuat'
    uncomment this line and specify a valid path

    Cheers
    nvk

  8. #8
    Join Date
    Apr 2008
    Posts
    32
    Dear nvk@vhv

    After I mofify thw script still i can't restore tables.


    Here is detalied commands :

    wmbint:/var/mqsi/wmbint/backup> db2 RESTORE DATABASE IEABDB5U FROM '/var/mqsi/wmbint/backup' ON '/var/mqsi/wmbint' INTO IEABDB5U REDIRECT 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.
    wmbint:/var/mqsi/wmbint/backup> db2 restore db IEABDB5U continue
    DB20000I The RESTORE DATABASE command completed successfully.
    wmbint:/var/mqsi/wmbint/backup> db2 connect to IEABDB5U

    Database Connection Information

    Database server = DB2/AIX64 9.1.3
    SQL authorization ID = WMBINT
    Local database alias = IEABDB5U

    wmbint:/var/mqsi/wmbint/backup> db2 list tables

    Table/View Schema Type Creation time
    ------------------------------- --------------- ----- --------------------------

    0 record(s) selected.



    Thanks in advance.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    With automatic storage, you don't use redirect option in the restore. You just have to specify the automatic storage path (which it appears you have already done).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Apr 2008
    Posts
    32

    Unhappy

    Dear Marcus_A

    Thanks for your help.


    I already tried to restore with redirect/without but still same problem i am not getting tables restored in user table space.


    Regards,
    Nasser

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by nasali View Post
    Dear Marcus_A

    Thanks for your help.


    I already tried to restore with redirect/without but still same problem i am not getting tables restored in user table space.


    Regards,
    Nasser
    Try:
    db2 list tables for all
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Apr 2008
    Posts
    32
    I try it it return all tables of system tables + my 24 tables needed but under source schema which is IWMBUAT for user table space.

    Table/View Schema Type Creation time
    ------------------------------- --------------- ----- --------------------------
    BACLENTRIES IWMBUAT T 2010-06-30-15.26.37.002364
    BAGGREGATE IWMBUAT T 2010-06-30-15.26.36.461493
    BCLIENTUSER IWMBUAT T 2010-06-30-15.26.37.138401
    BGROUPNAME IWMBUAT T 2010-06-30-15.26.37.753074
    BMQPSTOPOLOGY IWMBUAT T 2010-06-30-15.26.37.409800
    BMULTICASTTOPICS IWMBUAT T 2010-06-30-15.26.37.520082
    BNBRCONNECTIONS IWMBUAT T 2010-06-30-15.26.36.685292
    BPUBLISHERS IWMBUAT T 2010-06-30-15.26.36.874559
    BRETAINEDPUBS IWMBUAT T 2010-06-30-15.26.37.269999
    BRMINFO IWMBUAT T 2010-06-30-15.26.38.006515
    BRMPHYSICALRES IWMBUAT T 2010-06-30-15.26.38.261566
    BRMRTDDEPINFO IWMBUAT T 2010-06-30-15.26.38.090297
    BRMRTDINFO IWMBUAT T 2010-06-30-15.26.37.852380
    BRMWFDINFO IWMBUAT T 2010-06-30-15.26.38.178951
    BROKERAA IWMBUAT T 2010-06-30-15.26.36.125248
    BROKERAAEG IWMBUAT T 2010-06-30-15.26.35.721026
    BROKERRESOURCES IWMBUAT T 2010-06-30-15.26.36.161594
    BSCADADEST IWMBUAT T 2010-06-30-15.26.38.721014
    BSCADAMSGIN IWMBUAT T 2010-06-30-15.26.38.407538
    BSCADAMSGOUT IWMBUAT T 2010-06-30-15.26.38.575213
    BSUBSCRIPTIONS IWMBUAT T 2010-06-30-15.26.36.756811
    BTOPOLOGY IWMBUAT T 2010-06-30-15.26.36.607330
    BUSERMEMBERSHIP IWMBUAT T 2010-06-30-15.26.37.804440
    BUSERNAME IWMBUAT T 2010-06-30-15.26.37.691005



    Also With >db2 list tables
    0 selected



    Backup source shema :iwmbuat
    Current schema : wmbint
    Do you think issue with source schema?


    Regards,
    Nasser
    Last edited by nasali; 08-02-10 at 04:36.

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by nasali View Post
    Do you think issue with source schema?

    Regards,
    Nasser
    I think you are totally confused.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Apr 2008
    Posts
    32
    Dear Marcus_A

    Can You help me to know what is going on?

    "db2 list tables"

    return 0 selected

    "db2 list tables for all"
    return restored user table space under source schema as above which is iwmbuat.

    Regards,
    Nasser
    Last edited by nasali; 08-02-10 at 04:43.

  15. #15
    Join Date
    Jan 2010
    Posts
    335
    Within a restore of a database the logical Structure of the database does not change. You can not change the Schema for a table. You have to drop and recreate the tables and view!

Posting Permissions

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