Topic
  • 5 replies
  • Latest Post - ‏2012-10-16T14:23:49Z by SystemAdmin
SystemAdmin
SystemAdmin
5837 Posts

Pinned topic About online backups

‏2012-10-16T09:50:17Z |
Hi folks,

To a person who has not attended DB2 DBA training, the process of enabling online backups (which in my opinion should be enabled by default) and then ensuring that all necessary files are properly backed up (including archive logs) and then used for a safe restore, can be a maze of doc files that point from one to the other. It's during such times that I think of MS SQL-Server with a sigh. I have no official training there either, but it's so much more intuitive.

Is there a guide for this process?

Thanks a lot,

George
Updated on 2012-10-16T14:23:49Z at 2012-10-16T14:23:49Z by SystemAdmin
  • ocgstyles
    ocgstyles
    472 Posts

    Re: About online backups

    ‏2012-10-16T13:09:43Z  
    Hi George,

    It quite easy to enable online backups. All you need is one database config parameter change. This change will save the archive logs to disk (you can also use tape).

    db2 "update db cfg for <dbname> using logarchmeth1 DISK:/home/db2inst1/archives"

    So for example, for a database named "SAMPL", you will find the archived log files in:

    /home/db2inst1/archives/db2inst1/SAMPL/NODE0000/C0000000

    After that config change, the database will be put into BACKUP PENDING mode, so just take a quick backup. That's really it. You will need to create some sort of maintenance procedure (shell script, etc) to clean this directory up, otherwise it will just fill up the underlying storage.

    Hope that helps.
    Keith
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: About online backups

    ‏2012-10-16T13:14:44Z  
    • ocgstyles
    • ‏2012-10-16T13:09:43Z
    Hi George,

    It quite easy to enable online backups. All you need is one database config parameter change. This change will save the archive logs to disk (you can also use tape).

    db2 "update db cfg for <dbname> using logarchmeth1 DISK:/home/db2inst1/archives"

    So for example, for a database named "SAMPL", you will find the archived log files in:

    /home/db2inst1/archives/db2inst1/SAMPL/NODE0000/C0000000

    After that config change, the database will be put into BACKUP PENDING mode, so just take a quick backup. That's really it. You will need to create some sort of maintenance procedure (shell script, etc) to clean this directory up, otherwise it will just fill up the underlying storage.

    Hope that helps.
    Keith
    Thanks Keith,

    All that logarchmeth and userexit and compile userexit.c had me confused.

    Two more questions:

    1. After taking an online backup, do I also need to backup that archive location in your example? All of it or just certain files out of the lot (which ones)?

    2. How can I know which ones I can remove to save space, at a given time?

    Thanks again,

    George
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: About online backups

    ‏2012-10-16T13:17:56Z  
    Thanks Keith,

    All that logarchmeth and userexit and compile userexit.c had me confused.

    Two more questions:

    1. After taking an online backup, do I also need to backup that archive location in your example? All of it or just certain files out of the lot (which ones)?

    2. How can I know which ones I can remove to save space, at a given time?

    Thanks again,

    George
    I'm not done! :-)

    Upon restore of an online backup, must I also "rerun" some archived logs? Will db2 know which ones?
  • ocgstyles
    ocgstyles
    472 Posts

    Re: About online backups

    ‏2012-10-16T13:58:47Z  
    I'm not done! :-)

    Upon restore of an online backup, must I also "rerun" some archived logs? Will db2 know which ones?
    Yea, logarchmeth made it really easy. When I started with DB2, we had to use the USEREXIT.

    1. No, you don't need to, but you are welcome to. If you have the space, or also have a tape library, you can also set LOGARCHMETH2 to another location, so you have 2 copies.

    2. You can query the SYSIBMADM.DB_HISTORY table to see a list of backups
    $ db2 "select start_time, firstlog, lastlog from sysibmadm.db_history where operation = 'B'"

    START_TIME FIRSTLOG LASTLOG
    20121004070023 S0019110.LOG S0019113.LOG

    So, to restore that backup, you need FIRSTLOG, LASTLOG, and everything in between

    Or you can use the list history command, which would look like this (trimmed):

    $ db2 list history backup all for sampl

    List History File for sampl

    Number of matching file entries = 13

    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
    -- ---
    • ---
    ------------
    B D 20121004070023001 N A S0019110.LOG S0019113.LOG

    Contains 7 tablespace(s):

    00001 SYSCATSPACE
    00002 USERSPACE1
    00003 SYSTOOLSPACE

    Comment: DB2 BACKUP SAMPL ONLINE
    Start Time: 20121004070023
    End Time: 20121004070034
    Status: E

    EID: 19360 Location: adsm/libtsm.a
    I would say you can write a query against the db_history table for X number of days to find the first log you need to save. For example:

    db2 "select start_time, firstlog, lastlog from sysibmadm.db_history where operation = 'B' and timestamp(start_time) > (current timestamp - 5 days) order by start_time"

    Just make sure all logs after and including that first entry exist...
    3. Yes. Once restored, DB2 knows the database is at an inconsistent point and will put the database into ROLLFORWARD PENDING state. You need to use the ROLLFORWARD DB command to apply the logs. To see the needed logs use the ROLLFORWARD DB <dbname> QUERY STATUS command.

    $ db2 rollforward db SAMPL query status

    Rollforward Status

    Input database alias = SAMPL
    Number of nodes have returned status = 1

    Node number = 0
    Rollforward status = DB working
    Next log file to be read = S0019110.LOG
    Log files processed = -
    Last committed transaction = 2012-10-16-02.07.13.000000

    You then will use the rollforward command. You can either specify to END OF LOGS or to a point in time. DB2 will look in the archive and active log paths for the needed logs. (If needed. you can specify an OVERFLOW LOG PATH to the rollforward command to have DB2 use another location too).

    • Keith
  • SystemAdmin
    SystemAdmin
    5837 Posts

    Re: About online backups

    ‏2012-10-16T14:23:49Z  
    • ocgstyles
    • ‏2012-10-16T13:58:47Z
    Yea, logarchmeth made it really easy. When I started with DB2, we had to use the USEREXIT.

    1. No, you don't need to, but you are welcome to. If you have the space, or also have a tape library, you can also set LOGARCHMETH2 to another location, so you have 2 copies.

    2. You can query the SYSIBMADM.DB_HISTORY table to see a list of backups
    $ db2 "select start_time, firstlog, lastlog from sysibmadm.db_history where operation = 'B'"

    START_TIME FIRSTLOG LASTLOG
    20121004070023 S0019110.LOG S0019113.LOG

    So, to restore that backup, you need FIRSTLOG, LASTLOG, and everything in between

    Or you can use the list history command, which would look like this (trimmed):

    $ db2 list history backup all for sampl

    List History File for sampl

    Number of matching file entries = 13

    Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
    -- ---
    • ---
    ------------
    B D 20121004070023001 N A S0019110.LOG S0019113.LOG

    Contains 7 tablespace(s):

    00001 SYSCATSPACE
    00002 USERSPACE1
    00003 SYSTOOLSPACE

    Comment: DB2 BACKUP SAMPL ONLINE
    Start Time: 20121004070023
    End Time: 20121004070034
    Status: E

    EID: 19360 Location: adsm/libtsm.a
    I would say you can write a query against the db_history table for X number of days to find the first log you need to save. For example:

    db2 "select start_time, firstlog, lastlog from sysibmadm.db_history where operation = 'B' and timestamp(start_time) > (current timestamp - 5 days) order by start_time"

    Just make sure all logs after and including that first entry exist...
    3. Yes. Once restored, DB2 knows the database is at an inconsistent point and will put the database into ROLLFORWARD PENDING state. You need to use the ROLLFORWARD DB command to apply the logs. To see the needed logs use the ROLLFORWARD DB <dbname> QUERY STATUS command.

    $ db2 rollforward db SAMPL query status

    Rollforward Status

    Input database alias = SAMPL
    Number of nodes have returned status = 1

    Node number = 0
    Rollforward status = DB working
    Next log file to be read = S0019110.LOG
    Log files processed = -
    Last committed transaction = 2012-10-16-02.07.13.000000

    You then will use the rollforward command. You can either specify to END OF LOGS or to a point in time. DB2 will look in the archive and active log paths for the needed logs. (If needed. you can specify an OVERFLOW LOG PATH to the rollforward command to have DB2 use another location too).

    • Keith
    You have been helpful beyond words!