Solved

DB2 online backup/restore

Posted on 2002-07-26
5
26,197 Views
Last Modified: 2012-05-05
I'm configuring online backup for my DB2 using Tivoli Storage Manager. Following DB2 and TSM document, I think I got it right.
In my testing the backup(online) and restore, this is what I did.
1. created a test table named TESTB and inserted some data.
2. performed a online backup successfully.
3. cleared table TESTB.
4. performed the resotre. The process finished succefully.
5. query table TESTB. The data should come back. right?
my result is not. The table is still cleared.

Is my procedure proper?

BTW, I enabled the USEREXIT and LOGRETAIN parameter, built the user exit executable for TSM, did whatever need on TSM side.

Thanks.

Bill
0
Comment
Question by:aicc
  • 3
  • 2
5 Comments
 
LVL 7

Expert Comment

by:waynezhu
ID: 7197087
I do not have TSM and I use script to do online
backup and recovery at either database or tablespace
level.

Based on what you've describe, I think you need to make
sure if the backup image contains the data or not. For example, instead of using TSM, you can perrform a manual recovery to confirm if the data are within the backup
image or not. In this way, you can isolate the problem
to see if it happens in the backup stage or recovery
stage.
0
 

Author Comment

by:aicc
ID: 7197783
wayne,
Thank you for your response.
I can see something in TSM. But I can't read the content from TSM. By reading the size of the object in TSM, I guess it contains the image. I can use "db2adutl" to query the it.
Sure, I can use tape drive or a file to subtitute TSM.

How you do a manual recovery?

What I did is
db2 restore db devdb use tsm taken at xxxxxxxxx(the timestamp)
and then
db2 rollforward db devdb to end of logs and stop

I think I need to understand how online backup/restore works.
To my understood, when the USEREXIT parameter is turned on, if a tape drive is used, the database is brought into rollforward mode and the full logfiles, which no longer contain active transactions, will automatically be moved off the system to the tape drive. the logfiles in the tape media will be fept updated as the transactions are commited.
the tape media contains both backup media and the archived logs. when recovery is performed, the db2 will first restore the image (when the restore command is issured) and then apply the archived logs against the database to a point in time or end of logs (when the rollforward command is issured).
In my case, when I do the table clear, this action is actually recorded to the logs in TSM. When I restored the database, the image with data I inserted to table testb was copied back to db2. I can't query it even it is there(I think) since the db2 is in rollforward pending mode. So I have to do a rollforward and to apply the logs. The logs contain my clear action and table testb is cleared again when I do the rollforward.

Is my understanding correct? what is the right way to verify a online backup/recovery?

Thanks.

Bill
0
 
LVL 7

Accepted Solution

by:
waynezhu earned 500 total points
ID: 7198208
Bill,
Your backup and restore is fine.

The rollforward causes the confusion: when you specify
"db2 rollforward db devdb to end of logs and stop",
db2 rolls forward to the end of logs, and database is restored to the most recent state. If you want to see
the data before the deletion, you need to perform
a point-in-time rollforward.
The following is a demo for performing a point-in-time
recovery manually. Hope it helps.
Best,
Wayne
==========================================================
#Listing 1: Take an online backup
$ db2 "backup db sample online to /tmp"

Backup successful. The timestamp for this backup image is : 20020803124122


#Listing 2: Connect to the db, query the table, delete the rows, and query the table again
$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/6000 7.2.4
 SQL authorization ID   = DB2INST3
 Local database alias   = SAMPLE

$ db2 "select * from t0"
EMPNO  SALARY    
------ -----------
000010    52750.00
000020    41250.00
000030    38250.00
000050    40175.00
000060    32250.00
000070    36170.00
000090    29750.00
000100    26150.00
000110    46500.00
000120    29250.00
  10 record(s) selected.

$ db2 "delete from t0"
DB20000I  The SQL command completed successfully.

$ db2 "select * from t0"
EMPNO  SALARY    
------ -----------
  0 record(s) selected.

#Listing 3: Restore the db
$ db2 "restore db sample from /tmp"                        
SQL2539W  Warning!  Restoring to an existing database that is the same as the backup image database.  The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I  The RESTORE DATABASE command completed successfully.

#Listing 4: Roll forward to a point-in-time
$ db2 "rollforward db sample stop"                        
SQL1276N  Database "SAMPLE" cannot be brought out of rollforward pending state
until roll-forward has passed a point in time greater than or equal to
"2002-08-03-16.41.43.000000", because node "0" contains information later than
the specified time.

$ db2 "rollforward db sample to 2002-08-03-16.41.43.000000 and stop"

                                 Rollforward Status

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

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000003.LOG - S0000005.LOG
 Last committed transaction             = 2002-08-03-16.41.43.000000

DB20000I  The ROLLFORWARD command completed successfully.

#Listing 5: query the table again
$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/6000 7.2.4
 SQL authorization ID   = DB2INST3
 Local database alias   = SAMPLE

$ db2 "select * from t0"

EMPNO  SALARY    
------ -----------
000010    52750.00
000020    41250.00
000030    38250.00
000050    40175.00
000060    32250.00
000070    36170.00
000090    29750.00
000100    26150.00
000110    46500.00
000120    29250.00

  10 record(s) selected.


0
 

Author Comment

by:aicc
ID: 7198744
Wayne,

Perfect!

I wish I had known I could do
db2 "rollforward db sample to 2002-08-03-16.41.43.000000 and stop"

That is a big help.

I have tried to understand this since two weeks ago.

Thank you very much!

Bill
0
 
LVL 7

Expert Comment

by:waynezhu
ID: 7199133
Bill,
Thank you.
Wayne
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now