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
<https://support.oracle.com/epmos/faces/DocumentDisplay?parent=SrDetailText&sourceId=3-22146533561&id=1274075.1>
)
Flashback Database Best Practices & Performance ( Doc ID 565535.1
<https://support.oracle.com/epmos/faces/DocumentDisplay?parent=SrDetailText&sourceId=3-22146533561&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
