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,
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
5 replies Latest Post - 2012-10-16T14:23:49Z by SystemAdmin
Pinned topic About online backups
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-10-16T14:23:49Z at 2012-10-16T14:23:49Z by SystemAdmin
ocgstyles 060000N6VY472 PostsACCEPTED ANSWER
Re: About online backups2012-10-16T13:09:43Z in response to SystemAdminHi 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:
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.
Re: About online backups2012-10-16T13:14:44Z in response to ocgstylesThanks 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?
ocgstyles 060000N6VY472 PostsACCEPTED ANSWER
Re: About online backups2012-10-16T13:58:47Z in response to SystemAdminYea, 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):
Comment: DB2 BACKUP SAMPL ONLINE
Start Time: 20121004070023
End Time: 20121004070034
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
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).