Re: V$FLASHBACK_DATABASE_LOG oldest_flashback_time reporting future time

From: Leng <lkaing_at_gmail.com>
Date: Sun, 26 Jan 2020 07:11:12 +1100
Message-Id: <0AC32E93-D619-4380-B9C1-5B6212E85564_at_gmail.com>



Are you doing a connection via Sql*Net? If not, try your query again via Sql*net first. Also, add current_time to your query. Is your db using fixed_date or something like that? if you do Ls on the flashback log file, what time stamp do you get? Db time vs tz vs os time can get very confusing.

I vaguely recall that there is a hidden parameter to change the frequency in which flashback logs are written but that’s probably not what the problem is.

Cheers,
Leng

> On 25 Jan 2020, at 2:19 am, Rakesh Ra <rakeshra.tr_at_gmail.com> wrote:
>
> 
> Hi Listers,
>
> I have database with version 12.1.0.2 26635845;Database PSU 12.1.0.2.171017, Oracle JavaVM Component (OCT2017). This is running on a restart environment. The database is reporting oldest_flashback_time as a future time and hence we are continuously getting alerted for the same. I have checked the cluster time zone settings and this matches with the OS server timezone which is US/Central timezone.
>
> SELECT to_char(sysdate, 'YYYY-MM-DD HH24:MI') CURRENT_TIME
> ,to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI') OLDEST_FLASHBACK_TIME
> ,(sySQL> SQL> SQL> SQL> 2 3 sdate - f.oldest_flashback_time) * 24 * 60 HIST_MIN
> FROM v$database d
> ,V$FLASHBACK_DATABASE_LOG f;
> 4 5
> CURRENT_TIME OLDEST_FLASHBACK_TIME HIST_MIN
> ------------------------------------ ------------------------------------ -------------
> 2020-01-24 09:13 2020-01-24 12:26 -192.72
>
> The sysdate time is 09:13 AM CST and oldest_flashback_time is 12:26 PM CST. Which is technically not possible as per me. I raised an SR with Oracle and they came up with below statement which I am not in a position to agree.
>
> Statement from Oracle support as below:
>
> "The oldest flashback time is determined by the oldest flashback marker record written in the flashback logs. When the flashback log generation reaches a new maximum we can see that $flashback_database_log.oldest_flashback_time is more recent than sysdate minus db_flashback_retention_target minutes and we cannot flash back the full amount of time specified by db_flashback_retention_target.
>
> The explanation for this is that one flashback marker record is written by default once every 30 minutes. When flashback log generation reaches a new maximum the logs are reused and the oldest flashback marker record is overwritten.
>
> Note that the DB_FLASHBACK_RETENTION_TARGET is a target and there is no guarantee that you can flashback the database that far. In some cases if there is space pressure in the flash recovery area where the flashback logs are stored then the oldest flashback logs may be deleted. For a detailed explanation of the flash recovery area deletion rules see the Database Backup and Recovery User's Guide, Maintaining the Fast Recovery Area section. To guarantee a flashback point-in-time you must use guaranteed restore points (GRP). With GRP, the required flashback logs will never be recycled or purged until GRP is dropped. You can hang the database if you have a GRP and there’s insufficient space; so you need allocate more space in the FRA depending on the intended duration of the GRP.
>
> Have you reviewed below notes:
>
> References:
>
> Flashback logs are not kept for the full retention period even though space is available ( Doc ID 1274075.1 )
> Flashback Database Best Practices & Performance ( Doc ID 565535.1 )"
>
> There is no clarity on what is complex algorithm being used to retrieve oldest_flashback_time. Does this make any sense or is this something normal?
>
> BR,
> Rakesh RA
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Jan 25 2020 - 21:11:12 CET

Original text of this message