Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Unanswered: help about full backup and full restore

    Good morning,

    I am new to db2, and I need to make a backup of an existing bank that is in production and restore it on another machine, plus I have no idea of certain commands, I have read the templates that the site of IBM shows, but without success.

    my situation is as follows I have a server with db2 Express 8.2 and would like to hold a general backup with layout of tables, logs, users, and restore the database on another machine with db2 express-c 9.5, a very precise help with that, I read many tutorials and manuals from IBM, they are much more generic and all procedures fail, then the low setting of the server that is running and the command used to perform the backup on it, followed by the configuration of the second machine with the error in the restore.

    if they can help me I am grateful, because I lost.

    conf. machine with db2 running:

    [db2inst1@localhost root]$ db2 "get database configuration for dadospre"

    Database Configuration for Database dadospre

    Database configuration release level = 0x0a00
    Database release level = 0x0a00

    Database territory = BR
    Database code page = 1252
    Database code set = IBM-1252
    Database country/region code = 55
    Database collating sequence = BINARY
    Alternate collating sequence (ALT_COLLATE) =
    Database page size = 4096

    Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

    Discovery support for this database (DISCOVER_DB) = ENABLE

    Default query optimization class (DFT_QUERYOPT) = 5
    Degree of parallelism (DFT_DEGREE) = 1
    Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
    Default refresh age (DFT_REFRESH_AGE) = 0
    Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
    Number of frequent values retained (NUM_FREQVALUES) = 10
    Number of quantiles retained (NUM_QUANTILES) = 20

    Backup pending = NO

    Database is consistent = NO
    Rollforward pending = NO
    Restore pending = NO

    Multi-page file allocation enabled = YES

    Log retain for recovery status = RECOVERY
    User exit for logging status = NO

    Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
    Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
    Data Links Number of Copies (DL_NUM_COPIES) = 1
    Data Links Time after Drop (days) (DL_TIME_DROP) = 1
    Data Links Token in Uppercase (DL_UPPER) = NO
    Data Links Token Algorithm (DL_TOKEN) = MAC0

    Database heap (4KB) (DBHEAP) = 1200
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 260
    Log buffer size (4KB) (LOGBUFSZ) = 99
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 66749
    Buffer pool size (pages) (BUFFPAGE) = 1000
    Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
    Number of extended storage segments (NUM_ESTORE_SEGS) = 0
    Max storage for lock list (4KB) (LOCKLIST) = 100

    Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 12296
    Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
    Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128

    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
    Sort list heap (4KB) (SORTHEAP) = 755
    SQL statement heap (4KB) (STMTHEAP) = 2048
    Default application heap (4KB) (APPLHEAPSZ) = 256
    Package cache size (4KB) (PCKCACHESZ) = 859
    Statistics heap size (4KB) (STAT_HEAP_SZ) = 8192

    Interval for checking deadlock (ms) (DLCHKTIME) = 10000
    Percent. of lock lists per application (MAXLOCKS) = 60
    Lock timeout (sec) (LOCKTIMEOUT) = -1

    Changed pages threshold (CHNGPGS_THRESH) = 60
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
    Number of I/O servers (NUM_IOSERVERS) = 4
    Index sort flag (INDEXSORT) = YES
    Sequential detect flag (SEQDETECT) = YES
    Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32

    Track modified pages (TRACKMOD) = OFF

    Default number of containers = 1
    Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

    Max number of active applications (MAXAPPLS) = 40
    Average number of active applications (AVG_APPLS) = 1
    Max DB files open per application (MAXFILOP) = 64

    Log file size (4KB) (LOGFILSIZ) = 1024
    Number of primary log files (LOGPRIMARY) = 100
    Number of secondary log files (LOGSECOND) = 70
    Changed path to log files (NEWLOGPATH) =
    Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file = S0000182.LOG
    Block log on disk full (BLK_LOG_DSK_FUL) = NO
    Percent of max active log space by transaction(MAX_LOG) = 0
    Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

    Group commit count (MINCOMMIT) = 1
    Percent log file reclaimed before soft chckpt (SOFTMAX) = 120
    Log retain for recovery enabled (LOGRETAIN) = RECOVERY
    User exit for logging enabled (USEREXIT) = OFF

    HADR database role = STANDARD
    HADR local host name (HADR_LOCAL_HOST) =
    HADR local service name (HADR_LOCAL_SVC) =
    HADR remote host name (HADR_REMOTE_HOST) =
    HADR remote service name (HADR_REMOTE_SVC) =
    HADR instance name of remote server (HADR_REMOTE_INST) =
    HADR timeout value (HADR_TIMEOUT) = 120
    HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC

    First log archive method (LOGARCHMETH1) = LOGRETAIN
    Options for logarchmeth1 (LOGARCHOPT1) =
    Second log archive method (LOGARCHMETH2) = OFF
    Options for logarchmeth2 (LOGARCHOPT2) =
    Failover log archive path (FAILARCHPATH) =
    Number of log archive retries on error (NUMARCHRETRY) = 5
    Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
    Vendor options (VENDOROPT) =

    Auto restart enabled (AUTORESTART) = ON
    Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
    Log pages during index build (LOGINDEXBUILD) = OFF
    Default number of loadrec sessions (DFT_LOADREC_SES) = 1
    Number of database backups to retain (NUM_DB_BACKUPS) = 12
    Recovery history retention (days) (REC_HIS_RETENTN) = 366

    TSM management class (TSM_MGMTCLASS) =
    TSM node name (TSM_NODENAME) =
    TSM owner (TSM_OWNER) =
    TSM password (TSM_PASSWORD) =

    Automatic maintenance (AUTO_MAINT) = OFF
    Automatic database backup (AUTO_DB_BACKUP) = OFF
    Automatic table maintenance (AUTO_TBL_MAINT) = OFF
    Automatic runstats (AUTO_RUNSTATS) = OFF
    Automatic statistics profiling (AUTO_STATS_PROF) = OFF
    Automatic profile updates (AUTO_PROF_UPD) = OFF
    Automatic reorganization (AUTO_REORG) = OFF



    commands used in the backup:

    db2 backup database DADOSPRE ONLINE to /home/db2inst1/backup "with 2 buffers buffer 1024" "INCLUDE LOGS" "without prompting"

  2. #2
    Join Date
    Jul 2009
    Posts
    8
    conf. target machine:

    [db2inst1@centosDB2 root]$ db2 "get database configuration for dadospre"

    Database Configuration for Database dadospre

    Database configuration release level = 0x0c00
    Database release level = 0x0c00

    Database territory = BR
    Database code page = 1208
    Database code set = UTF-8
    Database country/region code = 55
    Database collating sequence = IDENTITY
    Alternate collating sequence (ALT_COLLATE) =
    Number compatibility = OFF
    Varchar2 compatibility = OFF
    Database page size = 4096

    Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

    Discovery support for this database (DISCOVER_DB) = ENABLE

    Restrict access = NO
    Default query optimization class (DFT_QUERYOPT) = 5
    Degree of parallelism (DFT_DEGREE) = 1
    Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
    Default refresh age (DFT_REFRESH_AGE) = 0
    Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
    Number of frequent values retained (NUM_FREQVALUES) = 10
    Number of quantiles retained (NUM_QUANTILES) = 20
    Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN

    Backup pending = NO

    Database is consistent = YES
    Rollforward pending = NO
    Restore pending = NO

    Multi-page file allocation enabled = YES

    Log retain for recovery status = NO
    User exit for logging status = NO

    Self tuning memory (SELF_TUNING_MEM) = ON
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
    Database memory threshold (DB_MEM_THRESH) = 10
    Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC
    Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC
    Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC
    Sort list heap (4KB) (SORTHEAP) = AUTOMATIC

    Database heap (4KB) (DBHEAP) = AUTOMATIC
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 260
    Log buffer size (4KB) (LOGBUFSZ) = 98
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 12646
    Buffer pool size (pages) (BUFFPAGE) = 1000
    SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC
    Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC
    Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC
    Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC

    Interval for checking deadlock (ms) (DLCHKTIME) = 10000
    Lock timeout (sec) (LOCKTIMEOUT) = -1
    Changed pages threshold (CHNGPGS_THRESH) = 80
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC
    Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC
    Index sort flag (INDEXSORT) = YES
    Sequential detect flag (SEQDETECT) = YES
    Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC

    Track modified pages (TRACKMOD) = OFF

    Default number of containers = 1
    Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

    Max number of active applications (MAXAPPLS) = AUTOMATIC
    Average number of active applications (AVG_APPLS) = AUTOMATIC
    Max DB files open per application (MAXFILOP) = 61440

    Log file size (4KB) (LOGFILSIZ) = 1024
    Number of primary log files (LOGPRIMARY) = 13
    Number of secondary log files (LOGSECOND) = 4
    Changed path to log files (NEWLOGPATH) =
    Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00005/SQLOGDIR/
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file =
    Block log on disk full (BLK_LOG_DSK_FUL) = NO
    Percent max primary log space by transaction (MAX_LOG) = 0
    Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

    Group commit count (MINCOMMIT) = 1
    Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
    Log retain for recovery enabled (LOGRETAIN) = OFF
    User exit for logging enabled (USEREXIT) = OFF

    HADR database role = STANDARD
    HADR local host name (HADR_LOCAL_HOST) =
    HADR local service name (HADR_LOCAL_SVC) =
    HADR remote host name (HADR_REMOTE_HOST) =
    HADR remote service name (HADR_REMOTE_SVC) =
    HADR instance name of remote server (HADR_REMOTE_INST) =
    HADR timeout value (HADR_TIMEOUT) = 120
    HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
    HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0

    First log archive method (LOGARCHMETH1) = OFF
    Options for logarchmeth1 (LOGARCHOPT1) =
    Second log archive method (LOGARCHMETH2) = OFF
    Options for logarchmeth2 (LOGARCHOPT2) =
    Failover log archive path (FAILARCHPATH) =
    Number of log archive retries on error (NUMARCHRETRY) = 5
    Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
    Vendor options (VENDOROPT) =

    Auto restart enabled (AUTORESTART) = ON
    Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
    Log pages during index build (LOGINDEXBUILD) = OFF
    Default number of loadrec sessions (DFT_LOADREC_SES) = 1
    Number of database backups to retain (NUM_DB_BACKUPS) = 12
    Recovery history retention (days) (REC_HIS_RETENTN) = 366
    Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF

    TSM management class (TSM_MGMTCLASS) =
    TSM node name (TSM_NODENAME) =
    TSM owner (TSM_OWNER) =
    TSM password (TSM_PASSWORD) =

    Automatic maintenance (AUTO_MAINT) = ON
    Automatic database backup (AUTO_DB_BACKUP) = OFF
    Automatic table maintenance (AUTO_TBL_MAINT) = ON
    Automatic runstats (AUTO_RUNSTATS) = ON
    Automatic statement statistics (AUTO_STMT_STATS) = OFF
    Automatic statistics profiling (AUTO_STATS_PROF) = OFF
    Automatic profile updates (AUTO_PROF_UPD) = OFF
    Automatic reorganization (AUTO_REORG) = OFF

    Enable XML Character operations (ENABLE_XMLCHAR) = YES
    WLM Collection Interval (minutes) (WLM_COLLECT_INT) = 0


    command used to restore:

    db2 restore database dadospre from /home/db2inst1 TAKEN AT 20090714114910 INTO dadospre

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am assuming that the directory structure of both servers are the same. You want the database in the same containers on both production and restore servers. If this is true, then all you need to do is add the "LOGTARGET" clause to the restore command. Then after the restore, you will need to do a rollforward command.


    Andy

  4. #4
    Join Date
    Jul 2009
    Posts
    8
    good morning andy,

    could you give me an example of the command complete.

    already thanks.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look in the manuals for the syntax of the command. I cannot give you the exact command you need because I do not know your specific setup.

    Andy

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Check p.14 in the following doc:
    http://download.boulder.ibm.com/ibmd...RFAQ_0508I.pdf


    It has an example of how to restore/rollforward from an online image that contains logs

  7. #7
    Join Date
    Dec 2008
    Posts
    76
    It was my impression that you could not restore an online backup from v8 to v9.5 because the structure of the logs has changed. Is this incorrect?
    RD

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    That is a good catch. I think you are correct. versions are buried in his text.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Yes, you're right. Online backups can't be used for db migration....Sorry, didn't spend enough time reading the original post

  10. #10
    Join Date
    Jul 2009
    Posts
    8
    Quote Originally Posted by db2girl
    Yes, you're right. Online backups can't be used for db migration....Sorry, didn't spend enough time reading the original post

    as would be the procedure??? as I do????

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Remove "online" and "include logs" from the backup command.

    restore command would be: db2 restore db <db name> from .... taken at ....

  12. #12
    Join Date
    Dec 2008
    Posts
    76
    If your backup file from v8 is an online backup (as seems to be indicated by your post), you will need to get an offline backup to be able to restore to v9.5.

    Either get it from the original database; or, if that is production and up 24/7, do a redirected restore of your online copy to a new database then get an offline backup from it.
    RD

  13. #13
    Join Date
    Jul 2009
    Posts
    8
    What's happening?

    already ran the command: db2 force applications all

    and yet still giving the error of container


    [db2inst1@localhost db2dados]$ db2 backup database DADOSPRE to /backup/db2

    Backup successful. The timestamp for this backup image is : 20090717060732 (OK)


    [db2inst1@localhost db2dados]$ db2 restore database dadospre from /backup/db2 TAKEN AT 20090717060732 INTO DADOSTES
    SQL2529W Warning! Restoring to an existing database that is different from the backup image database, and the alias name "DADOSTES" of the existing database does not match the alias name "DADOSPRE" of the backup image, and the database name "DADOSTES" of the existing database does not match the database name "DADOSPRE" of the backup image. The target database will be overwritten by the backup version. The Roll-forward recovery logs associated with the target database will be deleted.
    Do you want to continue ? (y/n) y
    SQL0294N The container is already in use. SQLSTATE=42730 (fail :[ )
    Last edited by maricods; 07-17-09 at 09:32.

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you are backing up a DB and then trying to restore it to a new database on the same server, you HAVE to do a redirected restore. Otherwise you would end up with 2 DBs trying to use the same files for storing the data.

    Andy

  15. #15
    Join Date
    Jul 2009
    Posts
    8
    Quote Originally Posted by ARWinner
    If you are backing up a DB and then trying to restore it to a new database on the same server, you HAVE to do a redirected restore. Otherwise you would end up with 2 DBs trying to use the same files for storing the data.

    Andy




    Excuse my ignorance, I am a beginner in database, and my knowledge of simple selects are not, could you give me an example of command to do this redirect?

    already thanks!

Posting Permissions

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