Re: wrong start_time and start_date in v$transaction

I’m not sure what happened but I think knowing the following could help

Are the instances that have the problem connected by DB links? What about the others?
Do you have similar wrongness with scn_to_timestamp? Can you replicate with a fresh instance using the same oracle home?

Thanks,
Andrew

On Tue, 1 Sep 2020 at 11:23, Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi Nenad,
>
> Is there something like faketime, libfaketime, strange LD_PRELOAD?
> https://m.habr.com/en/post/503804/
>
> вт, 1 сент. 2020 г., 13:05 Jonathan Lewis <jlewisoracle_at_gmail.com>:
>
>>
>> Anything in any of the alert logs about processes not starting up
>> properly, or using some specific function that doesn't get mentioned in
>> other alert logs?
>>
>> Are any of the funny instances running on virtual machines ? I recall a
>> case where running on vmware some processes would intermittently record a
>> "tim=" value in a trace file that was millions of centiseconds wrong.
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Tue, Sep 1, 2020 at 10:26 AM Noveljic Nenad <
>> nenad.noveljic_at_vontobel.com> wrote:
>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Jonathan,
>>>
>>>
>>> Clay,
>>>
>>>
>>>
>>>
>>>
>>> Only a minority of databases is affected by this problem. Other
>>> databases running on the same server and under the same
>>>
>>> Oracle home are healthy. I couldn’t find any difference between
>>> databases that work and those which don’t.
>>>
>>>
>>>
>>>
>>>
>>> I discovered that 12.2 is affected too (I originally reported the
>>> problem for 18c and 19c)
>>>
>>>
>>>
>>>
>>>
>>> v$transaction_time.start_date und start_time are constant for every
>>> transaction and every session on an affected database.
>>>
>>> This value doesn’t change over time. Consequently these it isn’t
>>> function of
>>>
>>> v$timer.hsecs.
>>>
>>>
>>>
>>>
>>>
>>> v$transaction_time.start_date is some arbitrary time higher than
>>> startup_time, for example.
>>>
>>>
>>>
>>>
>>>
>>> select
>>>
>>>
>>> 2 ( select start_date from v$transaction )
>>>
>>>
>>> 3 -
>>>
>>>
>>> 4 ( select startup_time from v$instance )
>>>
>>>
>>> 5 from dual ;
>>>
>>>
>>>
>>>
>>>
>>> (SELECTSTART_DATEFROMV$TRANSACTION)-(SELECTSTARTUP_TIMEFROMV$INSTANCE)
>>>
>>>
>>> ----------------------------------------------------------------------
>>>
>>>
>>> 70.2931366
>>>
>>>
>>>
>>>
>>>
>>> V$transaction.start_date is different on each database. Alert.log
>>> doesn’t show any anomalies around this time.
>>>
>>>
>>>
>>>
>>>
>>> After a database restart start_date gets populated correctly.
>>>
>>>
>>>
>>>
>>>
>>> Best regards,
>>>
>>>
>>>
>>>
>>>
>>> Nenad
>>>
>>>
>>>
>>>
>>>
>>> http://nenadnoveljic.com/blog/
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> *From:* oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>
>>>
>>> *On Behalf Of *Clay Jackson (cjackson)
>>>
>>>
>>> *Sent:* Dienstag, 1. September 2020 00:11
>>>
>>>
>>> *To:* jlewisoracle_at_gmail.com; ORACLE-L (oracle-l_at_freelists.org) <
>>> oracle-l_at_freelists.org>
>>>
>>>
>>> *Subject:* RE: wrong start_time and start_date in v$transaction
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> But, didn’t Nenad say “start_time doesn’t change over time on the
>>> affected databases”.
>>>
>>>
>>>
>>>
>>>
>>> Could it be as simple as the start time of the instance?
>>>
>>>
>>>
>>>
>>>
>>> But then why SOME transactions and not ALL? Perhaps something as simple
>>> as “the first transaction in each session”?
>>>
>>>
>>>
>>>
>>>
>>> Clay Jackson
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> *From:*
>>>
>>> oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>
>>>
>>> *On Behalf Of *Jonathan Lewis
>>>
>>>
>>> *Sent:* Monday, August 31, 2020 2:54 PM
>>>
>>>
>>> *To:* ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org>
>>>
>>>
>>> *Subject:* Re: wrong start_time and start_date in v$transaction
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> *CAUTION:* This email originated from outside of the organization.
>>>
>>> Do not follow guidance, click links, or open attachments unless you
>>> recognize the sender and know the content is safe.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Nenad,
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> How about one "what if" (based on a bug pattern seen in an older version
>>> of Oracle) before doing anything complicated.
>>>
>>>
>>>
>>>
>>>
>>>
>>> What if: the thing creating the timestamp for the transaction is
>>> dividing a counter by the wrong power of 10 before adding seconds to the
>>> database startup time. If you check v$instance.startup_time +
>>> v$timer.hsecs/(100
>>>
>>> * 86400) at the start of the transaction that should (I think) be very
>>> close to sysdate: can you get your transaction start time by dividing hsecs
>>> by a couple more powers of 10 ?
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Regards
>>>
>>>
>>>
>>>
>>>
>>>
>>> Jonathan Lewis
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> ____________________________________________________
>>>
>>>
>>> Please consider the environment before printing this e-mail.
>>>
>>>
>>> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Important Notice
>>>
>>>
>>>
>>>
>>> This message is intended only for the individual named. It may contain
>>> confidential or privileged information. If you are not the named addressee
>>> you should in particular not disseminate, distribute, modify or copy this
>>> e-mail. Please notify the sender immediately by e-mail, if you have
>>> received this message by mistake and delete it from your system.
>>>
>>>
>>> Without prejudice to any contractual agreements between you and us which
>>> shall prevail in any case, we take it as your authorization to correspond
>>> with you by e-mail if you send us messages by e-mail. However, we reserve
>>> the right not to execute orders and instructions transmitted by e-mail at
>>> any time and without further explanation.
>>>
>>>
>>> E-mail transmission may not be secure or error-free as information could
>>> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
>>> processing of incoming e-mails cannot be guaranteed. All liability of
>>> Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively
>>> referred to as "Vontobel Group") for any damages resulting from e-mail use
>>> is excluded. You are advised that urgent and time sensitive messages should
>>> not be sent by e-mail and if verification is required please request a
>>> printed version.
>>>
>>> Please note that all e-mail communications to and from the Vontobel
>>> Group are subject to electronic storage and review by Vontobel Group.
>>> Unless stated to the contrary and without prejudice to any contractual
>>> agreements between you and Vontobel Group which shall prevail in any case,
>>> e-mail-communication is for informational purposes only and is not intended
>>> as an offer or solicitation for the purchase or sale of any financial
>>> instrument or as an official confirmation of any transaction.
>>>
>>>
>>> The legal basis for the processing of your personal data is the
>>> legitimate interest to develop a commercial relationship with you, as well
>>> as your consent to forward you commercial communications. You can exercise,
>>> at any time and under the terms established under current regulation, your
>>> rights. If you prefer not to receive any further communications, please
>>> contact your client relationship manager if you are a client of Vontobel
>>> Group or notify the sender.
>>>
>>> Please note for an exact reference to the affected group entity the
>>> corporate e-mail signature.
>>>
>>> For further information about data privacy at Vontobel Group please
>>> consult www.vontobel.com.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>

--
http://www.freelists.org/webpage/oracle-l