cancel
Showing results for 
Search instead for 
Did you mean: 

Recover DB2 database and rolforward

Former Member
0 Kudos

Hallo,

Maybe a very simple answer or solution but I couldnu2019t find a good answer.

I have recently migrated my Oracle database to a IBM DB2 9.7 database. Now I have to create and test a recovery plan for a system crash. This mean that I have to restore a offline backup of the SAP system including the database environment and roll the database forward. The restored database doesnu2019t know or has logging information of coming backup or logging information. This of restoring a full-off line backup.

I found the commands to restore the database backup and the redo log files which are written to a disk partition.

To restore the backup I could use the command:

Db2 restore database <SID> from <local-path> taken at <T1>

And to read the redologs with:

Db2 rollforward db <SID> to <T2> using local time.

So far I understand the redologs must be in the directory where parameter u201CLOGARCHMETH1u201D is pointing. These redologs are written to tape daily and removed from the directory. Could I simple restore the redologs from tape to the original directory of the parameter u201CLOGARCHMETH1u201D and start the roll-forward.

If not what do I have to do to roll the database forward from a complete off-line database backup?

Kind regards,

Richard Meijn

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hallo,

Alright. I understand on paper how to rollforward the database.

But I'm already stucked by recovering my databas.

I create a online database backup with including logs. If I look in the backup log for the last days I see the following log:

db2 list history backup since 20120109 for db PI3

List History File for PI3

Number of matching file entries = 12

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID

-- --- -


-


--- -


-


-


B D 20120109213045001 N D S0000200.LOG S0000201.LOG

-


Contains 34 tablespace(s):

The backup file name is: PI3.0.db2pi3.NODE0000.CATN0000.20120109213045.001

Buth when I want to restore the database with the following command:

db2 restore db PI3 from /backup/sap_db_PI3/PI3.0.db2pi3.NODE0000.CATN0000.20120109213045.001 taken at 20120109213045

I get the following error:

SQL2036N The path for the file or device

"/backup/sap_db_PI3/PI3.0.db2pi3.NODE0000.CATN0000.20120109213045.001" is not

valid.

What's wrong for restoring the database PI3?

Kind regards,

Richard

former_member188883
Active Contributor
0 Kudos

Hi Richard,

Please use the following command

db2 restore db PI3 from /backup/sap_db_PI3 taken at 20120109213045

Do not use backup filename in the command for restore.

Regards,

Deepak Kori

Answers (6)

Answers (6)

Former Member
0 Kudos

Hi,

To recover you corrupt DB2 database you can use the inbuilt Recover Command in your DB2 database.

RECOVER DATABASE db_name TO point_in_time

The best thing about this command is that you don’t have to tell where to get the backup image. For more information about this post you can visit the below post.


http://db2.filerepairtool.net/blog/recover-db2-database-using-recover-command

Former Member
0 Kudos

Hello,

I found may be the problem. I'm missing one log file in the sequence. That must be the reason why he doesnu2019t read in all the log files.

Kind regards,

Richard Meijn

Former Member
0 Kudos

Hello,

I'm getting forward to recover the DB2 database.

I'm able to restore the database now with the command: db2 restore db PI3 from /backup/sap_db_PI3 taken at 20120109213045

after I restored the online backup in the directory /backup/sap_db_PI3 from tape.

Now I have to roll forward.

The original location where the log's are archived by DB2 is /backup/sap_arch_PI3/

This is the parameter: (LOGARCHMETH1) = DISK:/backup/sap_arch_PI3/

If I restore the redo logs from tape in to this location and enter the following roll forward command:

db2 "rollforward db PI3 to end of logs and complete overflow log path (/backup/sap_arch_PI3/db2pi3/PI3/NODE0000/C0000000)"

The archive logs aren't processed. What's wrong in my roll forward command?

If I do a list command ls -l /backup/sap_arch_PI3/db2pi3/PI3/NODE0000/C0000000 all the logs are listed.

Do I have to move the log's to another directory to roll forward.

Kind regards,

Richard Meijn

former_member188883
Active Contributor
0 Kudos

Hi Riachard,

Use the following command

db2 rollforward db PI3 to end of logs and complete overflow log path (/backup/sap_arch_PI3)

Else you can specify timestamp of the last log file and use the command below

db2 rollforward db PI3 to <1998-04-03-14.21.56> and stop overflow log path (/backup/sap_arch_PI3)

Regards,

Deepak Kori

Former Member
0 Kudos

Thanks for the answers but the logpath parameter is that the parameter:

Path to log files = /db2/PI3/log_dir/NODE0000/

Or the parameter :

First log archive method (LOGARCHMETH1) = DISK:/backup/sap_arch_PI3/

Thanks,

Richard Meijn

former_member189725
Active Contributor
0 Kudos

db2 "rollforward db <SID> to end of logs and complete overflow log path (<directory_location>)"

you can specify any directory as the overflow log path , once you copy the logs from /backup/sap_arch_PI3/ to this directory.

Else you can move the required log files which were generated during the online backup from /backup/sap_arch_PI3/ to /db2/PI3/log_dir/NODE0000/ and just issue the following command.

db2 "rollforward db <SID> to end of logs and complete"

This would basically read the files from the "Path to log files".

former_member189725
Active Contributor
0 Kudos

You are on the right track. You can restore it to the location from which it was backuped up to tape .

The other way round could be , you restore the files into a location and then rollforward with the following command .

db2 "rollforward db <SID> to end of logs and complete overflow log path (<directory_location>)"

former_member188883
Active Contributor
0 Kudos

Hi Richard,

Before performing a rollforward on a database, make sure to have one of the following authorities on DB2:

SYSADM

SYSCTRL

SYSMAINT

There is no need to connect to the database before performing the rollforward procedure since the command establishes a database connection.

Note: In a multinode environment, this command can only be issued from a catalogued node. Also, if the database is in rollforward pending state and the command is successfully performed, the database manager will take the database out of rollforward pending state.

Example 1 - Offline rollforward database to a point in time

db2 "rollforward database <sample> user db2admin using db2admin to 2001-02-22-14.54.21.253422 and stop"

This command will rollforward all logs located in the log folder specified in the database configuration file for Sample up to and including the above stated point-in-time*. The "and stop" key phrase completes the rollforward recovery process by rolling back incomplete transactions and turning off the rollforward pending state of the database.

  • The database configuration file contains information regarding the path to the database logfiles. To view this path, enter the following command:

db2 "get database configuration for <sample>"

Then look for the parm name - logpath. This is the path where the database log files are stored.

Hope this is useful.

Regards,

Deepak Kori