Log reader to get transactions - failed HDR Primar
After an HDR primary crashed and users failed over to the secondary, a user claimed the last hour of changes was missing, and Larry asked how to inspect the primary's logical logs to find them. Suggestions: Lintel's InfoTrace to read logs, or onstat -l plus onlog -n/-l (with -d for backups) and the documented log record formats, though partnums must be mapped to table names; bring the primary up with oninit -D first. Art explained the likely cause: BUFFERED LOG means COMMITs can sit unflushed and be rolled back at failover, so check the message log for rolled-back transactions, and avoid RTO/AUTO_CKPT with long checkpoint gaps. Madison suggested a background job committing a dummy row with unbuffered logging to force periodic flushes. No confirmed outcome for Larry's missing data is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Solaris 10 IDS 11.50.FC5 SPARC 64 I have a primary server and a secondary server. The primary server eventually crashed, and we failed over to the secondary server. I told everyone that the data should be current on the secondary, but now I am being told that it appears that information changed during the last hour before the death of the primary seems to be missing. Is there an elegant way to review the logs from the primary, which is currently back up but not being used to find all the transactions/data changes that took place during its final hour? Thank you. Larry
Lintel has a product, InfoTrace, that can display logical log contents in a
readable way:
http://www.lintel.co.uk/infotrace/index.php
You could use onlog to see the logical log records, but it is really
confusing and you have link up multiple records with multiple transactions
interleaved.
Bring the primary online with oninit -D so it doesn't try to connect to the
secondary and examine the logs. I can't imagine why your secondary would be
an hour out-of-sync though.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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, May 16, 2017 at 2:17 PM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote:
> Solaris 10
>
> IDS 11.50.FC5
>
> SPARC 64
>
> I have a primary server and a secondary server. The primary server
> eventually
> crashed, and we failed over to the secondary server. I told everyone that
> the
> data should be current on the secondary, but now I am being told that it
> appears that information changed during the last hour before the death of
> the
> primary seems to be missing.
>
> Is there an elegant way to review the logs from the primary, which is
> currently back up but not being used to find all the transactions/data
> changes
> that took place during its final hour?
>
> Thank you.
>
> Larry
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Not directly but...
- onstat -l to check the current state of the logical logs
- The current logical log will be marked with a "C" in the flags
- Check in the online.log for message about when each Logical Log completed.
- onlog -n logical-log-id > onlog.number.txt to dump each logical log
- The output would need parsing to convert from partnums to table/index names
- Logical log record formats are described at
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.adref.doc/
ids_adr_0358.htm
- Note the begin work (BEGIN) record contains date and time!!
- You can add -l to onlog to get more detail
- You can use -d device to analyse logical log backups
- onlog syntax is at
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.adref.doc/
ids_adr_0402.htm
Regards,
David.
> On 16 May 2017 at 20:17 LARRY SORENSEN <LSORENSEN25@msn.com> wrote:
>
>
> Solaris 10
>
> IDS 11.50.FC5
>
> SPARC 64
>
> I have a primary server and a secondary server. The primary server eventually
> crashed, and we failed over to the secondary server. I told everyone that the
> data should be current on the secondary, but now I am being told that it
> appears that information changed during the last hour before the death of the
> primary seems to be missing.
>
> Is there an elegant way to review the logs from the primary, which is
> currently back up but not being used to find all the transactions/data
changes
> that took place during its final hour?
>
> Thank you.
>
> Larry
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Thank you both. I didn't think that there was a real easy way to review the
logical logs.
I can't imagine how an hour's worth of data could me missing either. I swore
up and down that, that could not happen because the servers were actively
replicating. We just have one user that also swears that data is missing that
he/she entered. (unless it was right before the server died and the
transaction never completed)
Larry
________________________________
From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of
david@smooth1.co.uk <david@smooth1.co.uk>
Sent: Tuesday, May 16, 2017 1:38 PM
To: ids@iiug.org
Subject: Re: Log reader to get transactions - failed HD.... [39212]
Not directly but...
- onstat -l to check the current state of the logical logs
- The current logical log will be marked with a "C" in the flags
- Check in the online.log for message about when each Logical Log completed.
- onlog -n logical-log-id > onlog.number.txt to dump each logical log
- The output would need parsing to convert from partnums to table/index names
- Logical log record formats are described at
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.adref.doc/
ids_adr_0358.htm
IBM Knowledge
Center<https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.adr
ef.doc/ids_adr_0358.htm>
www.ibm.com
Welcome to IBM Knowledge Center: IBM's new home for technical product
documentation. You can find product documentation here from over 3000 IBM
products. In IBM Knowledge Center you can browse this documentation or search
it to find the answers you need.
- Note the begin work (BEGIN) record contains date and time!!
- You can add -l to onlog to get more detail
- You can use -d device to analyse logical log backups
- onlog syntax is at
https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.adref.doc/
ids_adr_0402.htm
The onlog Utility -
IBM<https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.adref.
doc/ids_adr_0402.htm>
www.ibm.com
The onlog utility displays the contents of a logical-log file, either on disk
or on backup.
Regards,
David.
> On 16 May 2017 at 20:17 LARRY SORENSEN <LSORENSEN25@msn.com> wrote:
>
>
> Solaris 10
>
> IDS 11.50.FC5
>
> SPARC 64
>
> I have a primary server and a secondary server. The primary server
eventually
> crashed, and we failed over to the secondary server. I told everyone that
the
> data should be current on the secondary, but now I am being told that it
> appears that information changed during the last hour before the death of
the
> primary seems to be missing.
>
> Is there an elegant way to review the logs from the primary, which is
> currently back up but not being used to find all the transactions/data
changes
> that took place during its final hour?
>
> Thank you.
>
> Larry
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
It is possible if the databases are all BUFFERED LOG and the logical log
buffers are fairly large that the COMMIT records from multiple transactions
never made it to disk and so were not forwareded to the secondary. If that
happened those transactions would have rolled back on the primary when it
was promoted. If you look in the message log from the time of the promotion
you will see a number of transactions rolled back listed there. If it isn't
zero then there is the possibility that one or more of those transactions
had been committed on the primary but the logical log buffer was never
flushed if your databases are BUFFERED LOG. That is the risk of buffered
logging versus UNBUFFERED LOG which flushes the logical log buffer
immediately when a COMMIT is written to it.
But for a COMMIT to sit in the log buffer for most of an hour? That's
unusual unless the transaction was committed during a very quiet time and
there were no checkpoints during that hour (checkpoints also force a log
buffer write out). Your CKPTINTVL should be far less than an hour (3600
sec) but with RTO or AUTO_CKPT set there is a possibility of no checkpoints
for long periods if the server is fairly quiet. Again a data loss risk and
the reason I encourage my clients to not use RTO or AUTO_CKPT.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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, May 16, 2017 at 2:45 PM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote:
> Thank you both. I didn't think that there was a real easy way to review the
> logical logs.
>
> I can't imagine how an hour's worth of data could me missing either. I
> swore
> up and down that, that could not happen because the servers were actively
> replicating. We just have one user that also swears that data is missing
> that
> he/she entered. (unless it was right before the server died and the
> transaction never completed)
>
> Larry
>
> ________________________________
> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of
> david@smooth1.co.uk <david@smooth1.co.uk>
> Sent: Tuesday, May 16, 2017 1:38 PM
> To: ids@iiug.org
> Subject: Re: Log reader to get transactions - failed HD.... [39212]
>
> Not directly but...
>
> - onstat -l to check the current state of the logical logs
>
> - The current logical log will be marked with a "C" in the flags
>
> - Check in the online.log for message about when each Logical Log
> completed.
>
> - onlog -n logical-log-id > onlog.number.txt to dump each logical log
>
> - The output would need parsing to convert from partnums to table/index
> names
>
> - Logical log record formats are described at
>
> https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.
> 50.0/com.ibm.adref.doc/ids_adr_0358.htm
> IBM Knowledge
> Center<https://www.ibm.com/support/knowledgecenter/en/
> SSGU8G_11.50.0/com.ibm.adref.doc/ids_adr_0358.htm>
> www.ibm.com
> Welcome to IBM Knowledge Center: IBM's new home for technical product
> documentation. You can find product documentation here from over 3000 IBM
> products. In IBM Knowledge Center you can browse this documentation or
> search
> it to find the answers you need.
>
> - Note the begin work (BEGIN) record contains date and time!!
>
> - You can add -l to onlog to get more detail
>
> - You can use -d device to analyse logical log backups
>
> - onlog syntax is at
>
> https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.
> 50.0/com.ibm.adref.doc/ids_adr_0402.htm
> The onlog Utility -
> IBM<https://www.ibm.com/support/knowledgecenter/en/
> SSGU8G_11.50.0/com.ibm.adref.doc/ids_adr_0402.htm>
> www.ibm.com
> The onlog utility displays the contents of a logical-log file, either on
> disk
> or on backup.
>
> Regards,
> David.
>
> > On 16 May 2017 at 20:17 LARRY SORENSEN <LSORENSEN25@msn.com> wrote:
> >
> >
> > Solaris 10
> >
> > IDS 11.50.FC5
> >
> > SPARC 64
> >
> > I have a primary server and a secondary server. The primary server
> eventually
> > crashed, and we failed over to the secondary server. I told everyone that
> the
> > data should be current on the secondary, but now I am being told that it
> > appears that information changed during the last hour before the death of
> the
> > primary seems to be missing.
> >
> > Is there an elegant way to review the logs from the primary, which is
> > currently back up but not being used to find all the transactions/data
> changes
> > that took place during its final hour?
> >
> > Thank you.
> >
> > Larry
> >
> >
> >
>
> ************************************************************
> *******************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
What a lot of people forget is that buffered snd unbuffered logging is not a
database property but rather a transactional property. The database propetty
is only the default transactional property and can be changed for any
transaction by using the set log command . I've recommend if you are using
buffered logging that you might want to have a background program always
running that updates a single row, uses unbuffered logging, commits, and then
sleeps for X seconds. That way you can ensure that any committed transaction
can remain in the buffer longer than X seconds.
Sent from Yahoo Mail on Android
On Tue, May 16, 2017 at 3:26 PM, Art Kagel<art.kagel@gmail.com> wrote: It is
possible if the databases are all BUFFERED LOG and the logical log
buffers are fairly large that the COMMIT records from multiple transactions
never made it to disk and so were not forwareded to the secondary. If that
happened those transactions would have rolled back on the primary when it
was promoted. If you look in the message log from the time of the promotion
you will see a number of transactions rolled back listed there. If it isn't
zero then there is the possibility that one or more of those transactions
had been committed on the primary but the logical log buffer was never
flushed if your databases are BUFFERED LOG. That is the risk of buffered
logging versus UNBUFFERED LOG which flushes the logical log buffer
immediately when a COMMIT is written to it.
But for a COMMIT to sit in the log buffer for most of an hour? That's
unusual unless the transaction was committed during a very quiet time and
there were no checkpoints during that hour (checkpoints also force a log
buffer write out). Your CKPTINTVL should be far less than an hour (3600
sec) but with RTO or AUTO_CKPT set there is a possibility of no checkpoints
for long periods if the server is fairly quiet. Again a data loss risk and
the reason I encourage my clients to not use RTO or AUTO_CKPT.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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, May 16, 2017 at 2:45 PM, LARRY SORENSEN <LSORENSEN25@msn.com> wrote:
> Thank you both. I didn't think that there was a real easy way to review the
> logical logs.
>
> I can't imagine how an hour's worth of data could me missing either. I
> swore
> up and down that, that could not happen because the servers were actively
> replicating. We just have one user that also swears that data is missing
> that
> he/she entered. (unless it was right before the server died and the
> transaction never completed)
>
> Larry
>
> ________________________________
> From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of
> david@smooth1.co.uk <david@smooth1.co.uk>
> Sent: Tuesday, May 16, 2017 1:38 PM
> To: ids@iiug.org
> Subject: Re: Log reader to get transactions - failed HD.... [39212]
>
> Not directly but...
>
> - onstat -l to check the current state of the logical logs
>
> - The current logical log will be marked with a "C" in the flags
>
> - Check in the online.log for message about when each Logical Log
> completed.
>
> - onlog -n logical-log-id > onlog.number.txt to dump each logical log
>
> - The output would need parsing to convert from partnums to table/index
> names
>
> - Logical log record formats are described at
>
> https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.
> 50.0/com.ibm.adref.doc/ids_adr_0358.htm
> IBM Knowledge
> Center<https://www.ibm.com/support/knowledgecenter/en/
> SSGU8G_11.50.0/com.ibm.adref.doc/ids_adr_0358.htm>
> www.ibm.com
> Welcome to IBM Knowledge Center: IBM's new home for technical product
> documentation. You can find product documentation here from over 3000 IBM
> products. In IBM Knowledge Center you can browse this documentation or
> search
> it to find the answers you need.
>
> - Note the begin work (BEGIN) record contains date and time!!
>
> - You can add -l to onlog to get more detail
>
> - You can use -d device to analyse logical log backups
>
> - onlog syntax is at
>
> https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.
> 50.0/com.ibm.adref.doc/ids_adr_0402.htm
> The onlog Utility -
> IBM<https://www.ibm.com/support/knowledgecenter/en/
> SSGU8G_11.50.0/com.ibm.adref.doc/ids_adr_0402.htm>
> www.ibm.com
> The onlog utility displays the contents of a logical-log file, either on
> disk
> or on backup.
>
> Regards,
> David.
>
> > On 16 May 2017 at 20:17 LARRY SORENSEN <LSORENSEN25@msn.com> wrote:
> >
> >
> > Solaris 10
> >
> > IDS 11.50.FC5
> >
> > SPARC 64
> >
> > I have a primary server and a secondary server. The primary server
> eventually
> > crashed, and we failed over to the secondary server. I told everyone that
> the
> > data should be current on the secondary, but now I am being told that it
> > appears that information changed during the last hour before the death of
> the
> > primary seems to be missing.
> >
> > Is there an elegant way to review the logs from the primary, which is
> > currently back up but not being used to find all the transactions/data
> changes
> > that took place during its final hour?
> >
> > Thank you.
> >
> > Larry
> >
> >
> >
>
> ************************************************************
> *******************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Interesting approach to the problem Madison. Sneaky.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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 Wed, May 17, 2017 at 9:25 AM, Madison Pruet <madison_pruet@yahoo.com>
wrote:
> What a lot of people forget is that buffered snd unbuffered logging is not
> a database property but rather a transactional property. The database
> propetty is only the default transactional property and can be changed for
> any transaction by using the set log command . I've recommend if you are
> using buffered logging that you might want to have a background program
> always running that updates a single row, uses unbuffered logging, commits,
> and then sleeps for X seconds. That way you can ensure that any committed
> transaction can remain in the buffer longer than X seconds.
>
> Sent from Yahoo Mail on Android
> <https://overview.mail.yahoo.com/mobile/?.src=Android>;
>
> On Tue, May 16, 2017 at 3:26 PM, Art Kagel
> <art.kagel@gmail.com> wrote:
> It is possible if the databases are all BUFFERED LOG and the logical log
> buffers are fairly large that the COMMIT records from multiple
> transactions
> never made it to disk and so were not forwareded to the secondary. If that
> happened those transactions would have rolled back on the primary when it
> was promoted. If you look in the message log from the time of the
> promotion
> you will see a number of transactions rolled back listed there. If it
> isn't
> zero then there is the possibility that one or more of those transactions
> had been committed on the primary but the logical log buffer was never
> flushed if your databases are BUFFERED LOG. That is the risk of buffered
> logging versus UNBUFFERED LOG which flushes the logical log buffer
> immediately when a COMMIT is written to it.
>
> But for a COMMIT to sit in the log buffer for most of an hour? That's
> unusual unless the transaction was committed during a very quiet time and
> there were no checkpoints during that hour (checkpoints also force a log
> buffer write out). Your CKPTINTVL should be far less than an hour (3600
> sec) but with RTO or AUTO_CKPT set there is a possibility of no
> checkpoints
> for long periods if the server is fairly quiet. Again a data loss risk and
> the reason I encourage my clients to not use RTO or AUTO_CKPT.
>
> Art
>
> Art S. Kagel, President and Principal Consultant
> ASK Database Management
> www.askdbmgt.com
>
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on 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, May 16, 2017 at 2:45 PM, LARRY SORENSEN <LSORENSEN25@msn.com>
> wrote:
>
> > Thank you both. I didn't think that there was a real easy way to review
> the
> > logical logs.
> >
> > I can't imagine how an hour's worth of data could me missing either. I
> > swore
> > up and down that, that could not happen because the servers were
> actively
> > replicating. We just have one user that also swears that data is missing
> > that
> > he/she entered. (unless it was right before the server died and the
> > transaction never completed)
> >
> > Larry
> >
> > ________________________________
> > From: ids-bounces@iiug.org <ids-bounces@iiug.org> on behalf of
> > david@smooth1.co.uk <david@smooth1.co.uk>
> > Sent: Tuesday, May 16, 2017 1:38 PM
> > To: ids@iiug.org
> > Subject: Re: Log reader to get transactions - failed HD.... [39212]
> >
> > Not directly but...
> >
> > - onstat -l to check the current state of the logical logs
> >
> > - The current logical log will be marked with a "C" in the flags
> >
> > - Check in the online.log for message about when each Logical Log
> > completed.
> >
> > - onlog -n logical-log-id > onlog.number.txt to dump each logical log
> >
> > - The output would need parsing to convert from partnums to table/index
> > names
> >
> > - Logical log record formats are described at
> >
> > https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.
> > 50.0/com.ibm.adref.doc/ids_adr_0358.htm
> > IBM Knowledge
> > Center<https://www.ibm.com/support/knowledgecenter/en/
> > SSGU8G_11.50.0/com.ibm.adref.doc/ids_adr_0358.htm>
> > www.ibm.com
> > Welcome to IBM Knowledge Center: IBM's new home for technical product
> > documentation. You can find product documentation here from over 3000
> IBM
> > products. In IBM Knowledge Center you can browse this documentation or
> > search
> > it to find the answers you need.
> >
> > - Note the begin work (BEGIN) record contains date and time!!
> >
> > - You can add -l to onlog to get more detail
> >
> > - You can use -d device to analyse logical log backups
> >
> > - onlog syntax is at
> >
> > https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.
> > 50.0/com.ibm.adref.doc/ids_adr_0402.htm
> > The onlog Utility -
> > IBM<https://www.ibm.com/support/knowledgecenter/en/
> > SSGU8G_11.50.0/com.ibm.adref.doc/ids_adr_0402.htm>
> > www.ibm.com
> > The onlog utility displays the contents of a logical-log file, either on
> > disk
> > or on backup.
> >
> > Regards,
> > David.
> >
> > > On 16 May 2017 at 20:17 LARRY SORENSEN <LSORENSEN25@msn.com> wrote:
> > >
> > >
> > > Solaris 10
> > >
> > > IDS 11.50.FC5
> > >
> > > SPARC 64
> > >
> > > I have a primary server and a secondary server. The primary server
> > eventually
> > > crashed, and we failed over to the secondary server. I told everyone
> that
> > the
> > > data should be current on the secondary, but now I am being told that
> it
> > > appears that information changed during the last hour before the death
> of
> > the
> > > primary seems to be missing.
> > >
> > > Is there an elegant way to review the logs from the primary, which is
> > > currently back up but not being used to find all the transactions/data
> > changes
> > > that took place during its final hour?
> > >
> > > Thank you.
> > >
> > > Larry
> > >
> > >
> > >
> >
> > ************************************************************
> > *******************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> > ************************************************************
> > *******************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> > ****************************