How long did the last level 0 backup take?
Andy wanted to measure how long the last 'ontape -s -L 0' took, after a script mistakenly backed up to /dev/null (fast, but unrestorable), and asked how to monitor backups. Replies: grep the online message log for the 'Level 0 Archive started'/'Archive ... Completed' lines (a sample awk script was posted) or query sysmaster:sysonlinelog, which Art noted is read as an external table at query time, so no extra memory or locking cost. Richard observed that a direct /dev/null backup writes nothing to the message log. John Miller suggested a better check: a fake backup doesn't update the dbspace last-backup time, so use 'onstat -g arc', 'oncheck -pr', or select level0 from sysmaster:sysdbstab.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello,
Can anyone tell me if it is possible to find out how long the last 'ontape -s
-L 0' took?
We are trying to monitor that our backups are ok.
We had a situation where (by mistake) the backup script was sending the backup
to /dev/null !!!
So whilst the backup worked fine, the backup process was not because we have
no file to restore.
The notable difference here is that the backup to /dev/null runs in under a
minute, where a backup to disk takes a couple of hours.
Therefore we would like to track that backups take at least 1 hour etc.
We would welcome any other suggestions on how we can monitor/protect against
this happening again?
Thanks for your time.
Regards, Andy.
Andrew
The online log records start time and end time for archives. Suibtract one
from the other and you get the time taken.
try this script to see the last few days.
logfile=`onstat -m | grep "Message Log File" | awk '{ print $4 }' `
echo
echo "Ontape Archive Log"
echo "======================================================================="
echo "LEVEL START DATE/TIME FINISH DATE/TIME STATUS
DURATION"
echo "
HH:MM:SS"
echo "======================================================================="
year=`date '+%Y'`
grep -e Archive -e 20[0,1][0-9]$ $logfile |
tail -200 |
grep -v "Process exited" |
nawk -v YEAR="$year" '{
if ( $5 == YEAR )
{ t_date=$1" "$2" "$3 }
if ( $2 == "Level" )
{ printf("\\
%1s %-10s %8s ", $3, t_date, $1)
s_time=$1 }
if ( $2 == "Archive" )
{ printf("%-10s %8s %-10s", t_date, $1, $NF)
f_time=$1
#Following is to get archive duration
#generate start time info
s_hh=substr(s_time,1,2)
s_mm=substr(s_time,4,2)
s_ss=substr(s_time,7,2)
s_h2s= s_hh * 60 * 60
s_m2s= s_mm * 60
s_s2s= s_ss
s_totsec= s_h2s + s_m2s + s_s2s
#generate finish time info
f_hh=substr(f_time,1,2)
f_mm=substr(f_time,4,2)
f_ss=substr(f_time,7,2)
if ( f_hh < s_hh )
{ f_hh= f_hh + 24 }
f_h2s= f_hh * 60 * 60
f_m2s= f_mm * 60
f_s2s= f_ss
f_totsec= f_h2s + f_m2s + f_s2s
#Take difference between start time and end time
d_totsec= f_totsec - s_totsec
#Calculate the difference in HH:MM:SS
d_hh= int( d_totsec / 3600 )
d_totsec= d_totsec - d_hh * 3600
d_mm= int( d_totsec / 60 )
d_totsec= d_totsec - d_mm * 60
d_ss=d_totsec
if ( length(d_hh) < 2 )
{ d_hh="0"d_hh }
if ( length(d_mm) < 2 )
{ d_mm="0"d_mm }
if ( length(d_ss) < 2 )
{ d_ss="0"d_ss }
d_time=d_hh":"d_mm":"d_ss
printf(" %8s",d_time) }
}' | tail -15
(Adapted from Prasad Mahale)
Keith
On 21/09/2010, ANDREW LEMIN <a_lemin@hotmail.com> wrote:
> Hello,
>
> Can anyone tell me if it is possible to find out how long the last 'ontape -s
> -L 0' took?
>
> We are trying to monitor that our backups are ok.
>
> We had a situation where (by mistake) the backup script was sending the
backup
> to /dev/null !!!
>
> So whilst the backup worked fine, the backup process was not because we have
> no file to restore.
>
> The notable difference here is that the backup to /dev/null runs in under a
> minute, where a backup to disk takes a couple of hours.
>
> Therefore we would like to track that backups take at least 1 hour etc.
>
> We would welcome any other suggestions on how we can monitor/protect against
> this happening again?
>
> Thanks for your time.
> Regards, Andy.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
You can grep the message log for the start and end times of the archive (or
select the lines from the sysmaster:sysonlinelog:
select * from sysonlinelog where line matches '*Archive*';
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Sep 21, 2010 at 7:06 AM, ANDREW LEMIN <a_lemin@hotmail.com> wrote:
> Hello,
>
> Can anyone tell me if it is possible to find out how long the last 'ontape
> -s
> -L 0' took?
>
> We are trying to monitor that our backups are ok.
>
> We had a situation where (by mistake) the backup script was sending the
> backup
> to /dev/null !!!
>
> So whilst the backup worked fine, the backup process was not because we
> have
> no file to restore.
>
> The notable difference here is that the backup to /dev/null runs in under a
> minute, where a backup to disk takes a couple of hours.
>
> Therefore we would like to track that backups take at least 1 hour etc.
>
> We would welcome any other suggestions on how we can monitor/protect
> against
> this happening again?
>
> Thanks for your time.
> Regards, Andy.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636457d787948be0490c3cd2b
Hi Andrew,
see below:
ANDREW LEMIN schrieb:
> Hello,
>
> Can anyone tell me if it is possible to find out how long the last 'ontape -s
> -L 0' took?
>
on 11.50.UC7DE (retested right during the last minutes)
it depends.... you should see line like these:
ontape -s -L 0 -t STDIO >/dev/null
gives me:13:53:09 Level 0 Archive started on rootdbs, d0
13:53:10 Archive on rootdbs, d0 Completed.
Even ontape -s -L 0 -F -t STDIO >/dev/null gives:
13:54:33 Level 0 Archive started on rootdbs, d0
13:54:34 Archive on rootdbs, d0 completed without being recorded.
note: ---> ^^^^^^^^^^^
BUT if you write so to /dev/null, that the engine 'knows' it:
(same might be true, if TAPEDEV in the $ONCONFIG ist set to /dev/null, but
this is
assumeing, not tested)
informix@linux-l4du:~> ontape -s -L 0 -F -t /dev/null
Archive to tape device '/dev/null' is complete.
Program over.
does NOT write into the message logfile, only the terminal session has the
lines
indicating output is /dev/null
same goes w/o '-F' (for fake backup)
informix@linux-l4du:~> ontape -s -L 0 -t /dev/null
Archive to tape device '/dev/null' is complete.
Program over.
and NO entry in the message logfile
[ the message logfile you can always find using:
grep "^MSG" $INFORMIXDIR/etc/$ONCONFIG (on a *NIX machine, that is) ]
I also did not investigate if there is a Codenumber reaching the alarmscript.sh
as this might give you the chance to see ALL backups and match this with
entries
in the message logfile.
dic_k
> We are trying to monitor that our backups are ok.
>
> We had a situation where (by mistake) the backup script was sending the
backup
> to /dev/null !!!
>
> So whilst the backup worked fine, the backup process was not because we have
> no file to restore.
>
> The notable difference here is that the backup to /dev/null runs in under a
> minute, where a backup to disk takes a couple of hours.
/dev/null is way faster than my SSDs :) *joke*
The manual states, that if writing log backup or backup to /dev/null,
then the server does near to nothing, especially not much I/O
>
> Therefore we would like to track that backups take at least 1 hour etc.
>
> We would welcome any other suggestions on how we can monitor/protect against
> this happening again?
>
> Thanks for your time.
> Regards, Andy.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe
Thanks Keith and Art for your prompt responses.
We were aware that you can iterate through the logs but were wondering if
there was any data relating to this stored in sysmaster etc.
When you query sysonlinelog does the engine read the online log 'file' at that
time when the transaction is called?
OR does the engine hold a copy of the online log in memory?
We are concerned about performance/locking issues when reading sysonlinelog.
This is what I have so far;
SELECT SKIP 1 TRIM(line) AS line, next_offset
FROM sysmaster:sysonlinelog
WHERE offset > (-1 * 10240)
INTO TEMP util_check_ids_onlinelog;
SELECT FIRST 2 SUBSTR(line, 0, 8) AS line
FROM util_check_ids_onlinelog
WHERE line like '%Level 0 Archive started on%' or line like '%Archive
on%Completed%'
order by next_offset desc;
drop table util_check_ids_onlinelog;
Sysonline log is treated as an external table, so the message log is opened
and scanned at the time you query it. No data from that file is brought
into the engine itself and no storage is used except the file itself. The
offset and next_offset columns are virtual columns.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Sep 21, 2010 at 8:42 AM, ANDREW LEMIN <a_lemin@hotmail.com> wrote:
> Thanks Keith and Art for your prompt responses.
>
> We were aware that you can iterate through the logs but were wondering if
> there was any data relating to this stored in sysmaster etc.
>
> When you query sysonlinelog does the engine read the online log 'file' at
> that
> time when the transaction is called?
> OR does the engine hold a copy of the online log in memory?
>
> We are concerned about performance/locking issues when reading
> sysonlinelog.
>
> This is what I have so far;
> SELECT SKIP 1 TRIM(line) AS line, next_offset
> FROM sysmaster:sysonlinelog
> WHERE offset > (-1 * 10240)
> INTO TEMP util_check_ids_onlinelog;>
> SELECT FIRST 2 SUBSTR(line, 0, 8) AS line
> FROM util_check_ids_onlinelog
> WHERE line like '%Level 0 Archive started on%' or line like '%Archive
> on%Completed%'
> order by next_offset desc;>
> drop table util_check_ids_onlinelog;>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e659fec6f659eb0490c4970c
Thank you :) Cheers, Andy.
Andrew:
I would like to suggest a different solution to your problem. If a user
backups to
/dev/null directly this is considered a "fake backup". Fake backups to not
update
the last backup time of a dbspace. I would suggest looking at the last
backup time.
You can use oncheck -pr, onstat -g arc or sysmaster query. See examples
below.
onstat -g arc will product a list of times each
Dbspaces - Archive Status
name number level date log log-position
rootdbs 1 0 09/21/2010.09:00 317 0xad6018
sbspace 2 0 09/21/2010.09:00 317 0xad6018
dbs1 3 0 09/21/2010.09:00 317 0xad6018
dbs2 4 0 09/21/2010.09:00 317 0xad6018
dbs3 5 0 09/21/2010.09:00 317 0xad6018
You can also run this as a query.
select dbsnum, name, dbinfo('UTC_TO_DATETIME',level0) fromsysmaster:sysdbstab
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 09/21/2010 04:06:52 AM:
> [image removed]
>
> How long did the last level 0 backup take? [21378]
>
> ANDREW LEMIN
>
> to:
>
> ids
>
> 09/21/2010 04:09 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Hello,
>
> Can anyone tell me if it is possible to find out how long the last
'ontape -s
> -L 0' took?
>
> We are trying to monitor that our backups are ok.
>
> We had a situation where (by mistake) the backup script was sending
> the backup
> to /dev/null !!!
>
> So whilst the backup worked fine, the backup process was not because we
have
> no file to restore.
>
> The notable difference here is that the backup to /dev/null runs in under
a
> minute, where a backup to disk takes a couple of hours.
>
> Therefore we would like to track that backups take at least 1 hour etc.
>
> We would welcome any other suggestions on how we can monitor/protect
against
> this happening again?
>
> Thanks for your time.
> Regards, Andy.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>