restore document for db2
DESCRIPTION
Restore Doc for DB2TRANSCRIPT
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