The output is :-
List History File for db1
Number of matching file entries = 3
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20050810095601001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 6 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 RRSONLTB
00004 RRSONLIDX
00005 RRSOFFTB
00006 RRSOFFIDX
----------------------------------------------------------------------------
Comment: DB2 BACKUP db1 OFFLINE
Start Time: 20050810095601
End Time: 20050810095705
----------------------------------------------------------------------------
00001 Location: E:\DB2\db1.0\DB2\NODE0000\CATN0000\20050810
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20050829164024001 N D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 6 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 RRSONLTB
00004 RRSONLIDX
00005 RRSOFFTB
00006 RRSOFFIDX
----------------------------------------------------------------------------
Comment: DB2 BACKUP db1 ONLINE
Start Time: 20050829164024
End Time: 20050829164140
----------------------------------------------------------------------------
00002 Location: e:\test\db1.0\DB2\NODE0000\CATN0000\20050829
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
R D 20050829164811001 F 20050829164024
----------------------------------------------------------------------------
Contains 6 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 RRSONLTB
00004 RRSONLIDX
00005 RRSOFFTB
00006 RRSOFFIDX
----------------------------------------------------------------------------
Comment: RESTORE db1 WITH RF
Start Time: 20050829164811
End Time: 20050829164838
----------------------------------------------------------------------------
00003 Location:
-----Original Message-----
From: "McNeer Mike " <Mike.[Email address protected]
Sent: Monday, August 29, 2005 6:36 PM
To: <[Email address protected]
Subject: RE: Problem while rollforward
**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as normal
**Anything below this line is the original email text
Can you forward the output of below?
db2 "list history backup all for database dbnamehere"
on the server you took the backup from.
Thanks,
Mike
Original Message
From: Iswariya Vasan
[Email address protected]
Sent: Monday, August 29, 2005 10:51 AM
To: LazyDBA Discussion
Subject: RE: Problem while rollforward
I did that also. and got the following error. Please help me out
SQL4970N Roll forward recovery on database "DB1" cannot reach the
specified stop point (end of log or point in time) because of missing
log file(s) on node(s) "0".
Only 10 log files are available in the server and I copied all the 10
files and I've put them in default log path, overflowlog path, new log
path locations.
Original Message
From: "ganesh" <ganesh.[Email address protected]
Sent: Monday, August 29, 2005 5:47 PM
To: <[Email address protected]
Subject: RE: Problem while rollforward
**A LazyDBA.com subscriber has responded to your lazydba.com
post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
Copy the log file to the logfile location .
use get db cfg for dbname to get the default log file location.
copy the log file to that location.
issue the command
ROLLFORWARD DATABASE DBNAME TO END OF LOGS AND STOP.
Regards,
Ganesh Murugesan
570 341 6221
"Iswariya Vasan "
[Email address protected] To:
"LazyDBA Discussion"
om>
[Email address protected]
cc:
Subject: RE: Problem while rollforward
Monday August 29, 2005 11:42 AM
Thanks for all your responses.
I did a roll forward with the Query Status option and found the
following
output. What does it indicate ?
Rollforward Status
Input database alias = db1
Number of nodes have returned status = 1
Node number = 0
Rollforward status = DB working
Next log file to be read = S0000000.LOG
Log files processed =
Last committed transaction = 2005 08 01 17.24.50.000000
My first log file is S0000000.LOG and it matches with the
output.
Please guide me for the next level.
Original Message
From: "Iswariya Vasan " <db2udbdba ezmlmshield x6922854.[Email address
protected]
Sent: Monday, August 29, 2005 5:25 PM
To: "LazyDBA Discussion" <[Email address protected]
Subject: RE: Problem while rollforward
NewLOGPATH parameter was null.
I tried to update the parameter as:
db2 update cfg for db1 using newlogpath c:\test\logs\
its says that it has been updated
But when I put a : get db cfg for db1
the NEWLOGPARAMERTER is NULL ..ie. the value I gave to update
was not
reflecting.
can you tell me how to set the query status option from the
wizard ? or we
need to do it from the CLP ?
Original Message
From: "ganesh" <ganesh.[Email address protected]
Sent: Monday, August 29, 2005 5:21 PM
To: <[Email address protected]
Subject: Re: Problem while rollforward
**A LazyDBA.com subscriber has responded to your
lazydba.com
post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
1.Check your log path directory
get db cfg for dbname. NEWLOGPATH PARAMETER
2.Do the ROLLFORWARD DATABASE command with the QUERY STATUS option
to determine which log files are missing.
This will tell you log files that the database manager has rolled
forward, the next archive file required, and the time stamp (in CUT) of
the last committed transaction since rollforward processing began.
Regards,
Ganesh Murugesan
570 341 6221
"Iswariya Vasan "
[Email address protected] To:
"LazyDBA Discussion"
om>
[Email address protected]
cc:
Subject: Problem while rollforward
Monday August 29, 2005 11:05 AM
Thanks for all your inputs.
I enabled the User exit routine and set the Overflow Log Path parameter
to
the place where I've my archived logs.
Now I took the database back up (DB1) which is having
Archival as
its
logging type and Also User Exit routine is ON from Server A.
I restored the same DB1 in the same name (DB1) in
Server B. the
restoration
was successfull. And now in Server B when I tried to connect to the
database DB1, it throwed me the following error :
SQL1117N A connection to the or activation of database
"DB1"
cannot be made
because of ROLL FORWARD PENDING. SQLSTATE = 57019
So I went to the control center. Right Click on the
database "DB1"
and
clicked "Roll Forward".
In the wizard, I selected "RollForward to the end of
Logs", and
for the
Retrival of archived Logs I specified the place "E:\test\Logs\NODE0000\"
where I have the Archived Logs .
Also I copied the Log files which I took from Server A into the place in
Server B where the database DB1, files reside..ie
</DIV>
E:\DB2\NODE0000\SQL00003\SQLOGDIR
so now the same set of all log files is available in
the default
location
as well as in the Over Flow Log Path.
In the next page, Final state of the database I clicked
"Complete
the roll
forward and return to active state" and clicked Finish.
The command text is:
ROLLFORWARD DATABASE RRSUATDB TO END OF LOGS AND
COMPLETE OVERFLOW
LOG PATH
("E:\TEST\LOGS\NODE0000\");
But Iam getting the following error:
Please help me to rectify the same.
SQL4970N Roll forward recovery on database "RRSUATDB"
cannot
reach the specified stop point (end of log or point in time)
because of missing log file(s) on node(s) "0".
Explanation:
This message is returned in the following situations:
o a request was made to bring the specified database
out of
roll forward pending state by specifying caller action
SQLUM_ROLLFWD_STOP, SQLUM_STOP, SQLUM_ROLLFWD_COMPLETE, or
SQLUM_COMPLETE but the Rollforward Database utility cannot
find the necessary archive log file(s) in the database log
directory or the overflow log directory on the specified
node(s) to reach the stopping point from the previous
ROLLFORWARD DATABASE command.
o in a multi node environment, if the Rollforward
Database
utility cannot find the necessary archive log file(s) to
bring a node into synchronization with the catalog node.
If ",..." is displayed at the end of the node list,
see the
administration notification log for the complete list of nodes.
Roll forward recovery has stopped. The database is
left in
roll forward pending state.
Note : if you are using a partitioned database server,
the node
number(s) indicate which node(s) the error occurred on.
Otherwise, it is not pertinent and should be ignored.
User Response:
Use the ROLLFORWARD DATABASE command with the QUERY
STATUS option
to determine which log files are missing.
Do one of the following:
o Make sure that all archive log files are available
in the
database log directory or the overflow log path, and issue
the ROLLFORWARD DATABASE command again.
o If you cannot find the missing log files, restore
the
database/table space on all nodes, then do point in time
recovery using a timestamp that is earlier than that of the
earliest missing log file.
Thanks and Regards
Iswariya Vasan
Original Message
From: "Rai Amit " <Amit.[Email address protected]
Sent: Friday, August 26, 2005 2:30 PM
To: <[Email address protected]
Subject: RE: Back Up and Logging
**A LazyDBA.com subscriber has responded to your
lazydba.com
post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text
Hi All,
I've few doubts over this back up recovery and
logging
types.
Can anybody clarify me on this?
1) I've set my production database logging
type as
"Archival".
And for archiving logs I've set the Manual archival process. This means
that periodically I've to go to that log file folder take the old logs
and put it in another folder? Is this what it means?
If you are not using Userexit prog. , yes you
need to
manually
move old log files.
2) Can anybody send me links for examples on
writing User
Exit
routine, which will automate the process of archiving old log files.
You can take a look at "SQLLIB\samples\c" ,
there you
will find
sample codes for userexit prog(archival to disk or tsm), in prog.
Samples it is mentioned what you need to change . Pretty simple steps ,
mostoly you need to change archival path and log file path. You would
require a C compiler to comlile it , IBM or MS 'c' compiler would work.
Take a look at this link
http://www 128.ibm.com/developerworks/db2/library/techarticle/0307kline/
0307kline.html , explains in detail how to setup userexit.
3) In both the cases(manual archival or user
exit
routine), the
old log files will be moved to another location. So when I do a restore
of the latest back up of the production database and rollforward to the
end of logs, should I also specify the archived log files location since
only from the archival log file folder the log files starts . Iam a bit
confused in how DB2 will work on rolling forward transaction upto the
end of logs in cases where I've moved my log files.
if you are using userexit it will take care of
fetching
old log
files , if not then you need to specify OVERFLOWLOGPATH db config
parameter.
4) Also, say for todays transaction there are
5 log files
generated. I restore yesterdays backup version and now I want to
rollforward to 1,2,3 and 5th log file alone. In this case, While rolling
forward to the end of logs, will it not throw an error saying that "Logs
Missing" ? How can I do this ?
You can specify time to which you want to
rollforward,
better
specify in CUT, or you can specify using LOCAL TIME also. Take a look at
this link
http://www.ibm.com/developerworks/db2/library/techarticle/pworld/0112mcc
luney.html
5) Roll forward to a specific time. Should I
enter the
Log file
saved time which appears when I put a ls l command in the unix shell
prompt ? How should I give the time in 'local" or "GMT" the options
specified in the wizard.?
Again better use CUT .... After restore you
can issue db2
rollforward db <db2name> and stop ... It will tell you minimum time that
you can rollforward to ....
Please clarify my doubts regarding the 4
points mentioned
above.
Regards
Iswariya Vasan
PLEASE CLICK REPLY ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
PLEASE CLICK REPLY ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
***National City made the following annotations
This communication is a confidential and proprietary
business
communication. It is intended solely for the use of the designated
recipient(s). If this communication is received in error, please
contact
the sender and delete this communication.
========================================================================
===================
PLEASE CLICK REPLY ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
PLEASE CLICK REPLY ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
PLEASE CLICK REPLY ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
PLEASE CLICK REPLY ALL TO SEND A REPLY TO EVERYONE
website: http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page