How to backup IBM DB2 Database online?

I tried to backup the IBM DB2 (LUW) Database using this command:

./db2 backup database DBEMP to /home/user1/db2bkup

But I got this message:


SQL1035N The database is currently in use. SQLSTATE=57019

Then I tried this:

./db2 backup database DBEMP online to /home/user1/db2bkup

And I got this message:


SQL2413N Online backup is not allowed because the database is not
recoverable or a backup pending condition is in effect.

What does it mean? Is there alternative way to backup the database online? I can't stop the database because it's being used.

Unfortunately, it's not possible to take an online backup of a DB2 database if it's in circular logging mode, which is the default for DB2 databases when they are created.

You can check whether your database is using circular logging by issuing:

./db2 get db cfg for dbemp | grep LOGARCH

If both LOGARCHMETH options are switched off...

First log archive method       (LOGARCHMETH1) = OFF
Second log archive method      (LOGARCHMETH2) = OFF

... then you'll need to change your database configuration to use archive logging before backups can be taken. I'm not 100% sure of this but I believe you'll need to restart your database and take an offline backup before you can start taking online backups.

There's an overview of how to do this in IBM's "configuring database logging options" documentation.

Note that if you change from CIRCULAR LOGGING to ARCHIVE LOGGING, you'll need to figure out what to do with your archived log files. Storing them on SAN/NAS/Tape to allow roll-forward recovery can be a really good idea if this data is important to you.

StackExchange Database Administrators Q#42214, answer score: 9