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

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
    ACCEPTED ANSWER

    Re: About online backups

    ‏2012-10-16T13:09:43Z  in response to SystemAdmin
    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
      ACCEPTED ANSWER

      Re: About online backups

      ‏2012-10-16T13:14:44Z  in response to ocgstyles
      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
        ACCEPTED ANSWER

        Re: About online backups

        ‏2012-10-16T13:17:56Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

          Re: About online backups

          ‏2012-10-16T13:58:47Z  in response to SystemAdmin
          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