Backup and RestoreThis document walks through some backup and restore test scenarios.Step

Team

User

Action Notes

1 SAPTCE

n/a Take a backup using DB13

2 SAPTCE

n/a Create a message #1 in SM02 note the creation date/time SAP message #1 created and time noted

3 SAPTCE

n/a Wait for 5 mins This is to make the PIT timing simple for test purposes

4 SAPTCE

n/a Create a message #2 in SM02 note the creation date/time SAP message #2 created and time noted

5 SAPTCE

db2hra

Perform a log switch: db2 archive log for database HRA

This is to ensure that the changes are moved from the online logs to the archive logs.

6 SAPTCE

db2hra

Make/cleanup relevant directories:

Note – may need to create the ‘restoredlogs’ directory elsewhere if there is not enough space in /backup/HRA to copy the logs/mirrored logs (see step 10)

mkdir -p /backup/HRA/restoredlogs/1 /backup/HRA/restoredlogs/2 /backup/HRA/restoredlogs/3rm -rf /backup/HRA/restoredlogs/1/* rm -rf /backup/HRA/restoredlogs/2/* rm -rf /backup/HRA/restoredlogs/3/*

1 = Used as a temporary store for log_archive2 = Used as a temporary store for log_archive23 = Used as a temporary store for logs contained in the backup file

7 SAPTCE

n/a [Optional depending upon what is being tested] Request to run adhoc NBU7 backup job.

Backups run daily to move files from disk to net backup. In most cases though the files you require for a restore should still be available on disk (/backup/HRA, /db2/HRA/log_archive and /db2/HRA/log_archive2

8 UNIX

n/a If this is a clustered database then the UNIX team will need to freeze the cluster now. Note: That the CI cluster will not need freezing as only fails over if the physical server dies, not if the CI is stopped

HRP Only (husapc09/c10)

9 SAPTCE

hraadmdb2hra

Shutdown Application Servers, CI and DatabaseCI (on husadv02)stopsapDB (on husapc11)db2stopIf it fails then you can use the commanddb2 list applications This shows what is running still. Then either stop those processes , or, issue the commanddb2stop force

Application, CI and Database servers shutdown

10

SAPTCE

db2hra

[Optional depending upon what is being tested]Move archive existing logs out of the way:mv /db2/HRA/log_archive/db2hra/HRA/NODE0000/* /backup/HRA/restoredlogs/1mv /db2/HRA/log_archive2/db2hra/HRA/NODE0000/* /backup/HRA/restoredlogs/2

This makes the current log chain off to a safe location ready for restore.

11

SAPTCE

db2hra

Delete the existing database:rm -rf /db2/HRA/sapdata1/sapdata1/db2hra;rm -rf /db2/HRA/sapdata1/sapdata2/db2hra;rm -rf /db2/HRA/sapdata1/sapdata3/db2hra;rm -rf /db2/HRA/sapdata1/sapdata4/db2hra;rm -rf /db2/HRA/sapdata1/sapdata5/db2hra;rm -rf /db2/HRA/sapdata1/sapdata6/db2hra;rm -rf /db2/HRA/sapdata1/sapdata7/db2hra;rm -rf /db2/HRA/sapdata1/sapdata8/db2hra;rm -rf /db2/HRA/sapdata1/sapdata9/db2hra;rm -rf /db2/HRA/sapdata1/sapdata10/db2hra;rm -rf /db2/HRA/saptemp1/NODE0000;rm -rf /db2/HRA/log_dir/NODE0000/*.LOG;rm -rf /db2/HRA/log_dir2/NODE0000/*.LOG;

In this example we are removing all database files

12 SAPTCE

[Optional: perform if testing restore from NBU]If the files you wish to restore from exists in /backup/HRA, for example, HRA.0.db2hra.NODE0000.CATN0000.20140604151431.001HRA.0.db2hra.NODE0000.CATN0000.20140604151431.002HRA.0.db2hra.NODE0000.CATN0000.20140604151431.003Then either delete or rename them and ask the net backup team to restore these files from tape.

This proves that the files taken away to tape can be restored and used to recover the database.

13 SAPTCE

db2hra

Restore SAP HRA system from Backup image taken:

db2 start dbm

timex db2 RESTORE DATABASE HRA FROM "/backup/HRA,/backup/HRA,/backup/HRA" TAKEN AT 20140604151431 logtarget "/backup/HRA/restoredlogs/3" with 10 buffers buffer 5120 parallelism 3

Restore initiated (eta 40-50 mins)The highlighted date/time stamp is the identification of the backup you wish to restore to. This can be obtained by looking at the file names in /backup/HRA or in net backup if they have been taken off to tape. For example:HRA.0.db2hra.NODE0000.CATN0000.20140515040011.001HRA.0.db2hra.NODE0000.CATN0000.20140515040011.002HRA.0.db2hra.NODE0000.CATN0000.20140515040011.003Note that the location specified in logtarget must be empty before you begin the restore.

14 SAPTCE

db2hra

Press Y to confirm deletion of database files when prompted

15 SAPTCE

n/a If restoring to a backup then follow step 16 onwardsIf restoring to a backup and then rolling forward to a point in time then follow step 18 onwards

16 SAPTCE

db2hra

RESTORING TO THE END OF A BACKUPAfter the “RESTORE DATABASE....” activity has completed:DB20000I The RESTORE DATABASE command completed successfully.

When we run the daily full online backup, any logs that are produced during the online backup are contained within the backup files. These logs will be restored to the ‘logtarget’ area ("/backup/HRA/restoredlogs/3")

So in this example – logs 55 and 56 were restored to "/backup/HRA/restoredlogs/3" as part of the overall restore:

Copy the logs that have been restored from directory :/backup/HRA/restoredlogs/3

to log_dir and log_dir2 directories :cp /backup/HRA/restoredlogs/3/*.LOG /db2/HRA/log_dir/NODE0000cp /backup/HRA/restoredlogs/3/*.LOG /db2/HRA/log_dir2/NODE0000

we then need to roll forward to the end of the backup:db2 "rollforward database HRA to END OF BACKUP and complete"

The output from a successful roll forward:

Rollforward Status

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

Node number= 0 Rollforward status= not pending Next log file to be read = Log files processed= S0000038.LOG - S0000038.LOG Last committed transaction= 2014-04-08-21.19.07.000000 UTC

DB20000I The ROLLFORWARD command completed successfully.

Now we need to activate the database:db2 activate database HRA

DB20000I The ACTIVATE DATABASE command completed successfully.

For info... the contents on ‘log_archive’ directory now has the contents....

.. and ‘log_dir’ has the contents:

And looks like two further logs have been produced as part of the rollforward and activation activity

17 SAPTCE

n/a Restore to backup complete - skip to step 19

18 SAPTCE

db2hra

RESTORING TO POINT IN TIME First you will need to check you have all the logs you think you might require.The logs you saved with the backup will have been restored into /backup/HRA/restoredlogs/3

Other logs you might need after this should be in either /backup/HRA/restoredlogs/1, /backup/HRA/restoredlogs/2or/db2/HRA/log_archive/db2hra/HRA/NODE0000/C00000*/* , /db2/HRA/log_archive2/db2hra/HRA/NODE0000/ C00000*/*

If one of the above locations has a copy of all of the logs required, even the log that was stored with the backup then you can use that as the ‘overflow log path’ below. Otherwise, copy the files into a single area, such as /backup/HRA/restoredlogs/3and use this as the overflow log path

db2 "rollforward db HRA to 2014-05-01-11.33.00 using local time and complete overflow log path (/backup/HRA/restoredlogs/3)"

Now we need to activate the database:db2 activate database HRA

DB20000I The ACTIVATE DATABASE command completed successfully.

The local time option is key for a point in time recovery, otherwise the default is to use UTC which may restore to a point further ahead than you wished.

19 SAPTCE

db2hrahraadm

Start SAP CI and Application Servers SAP Application servers started

20 SAPTCE

n/a Check if the SM02 Message #1 data is present, but #2 is not to confirm successful rollforward test.

Roll forward check completed successfully

21 UNIX

n/a Unfreeze the database cluster if this is a clustered system HRP Only (husapc09/c10)

22 SAPTCE

n/a End of test End of test