Audit trails and the importance of TIMEZONE

I threw this little puzzle out on Twitter just before the New Year

It was a little bit mischievous of me to post this because there are a number of ways in which you can get a ORA-46250.  Some of these are due to the way you audit is configured and others were due to bugs in older database versions, but these do not apply in this instance.

Similarly, it is probably a rare situation that you would want to limit your older trail to something as low as just 1 hour, but customers have often tried to do this as a quick method of purging the audit trail to reclaim space in the SYSAUX tablespace.

Side note: I’ll have more to say on audit and SYSAUX and why it might be critically important to you in an upcoming blog post.

But in this instance, the error is being correctly reported by the database. The value I am passing for last archive time is indeed invalid. Here are (paraphrased) some of the hypotheses posted on Twitter by  respondents to my original tweet as to why the value might be invalid:

• “systimestamp contains a timezone”. This is true, but running the routine with “localtimestamp” also gives the error
• “your database is in UTC but your session timezone is not”. Possibly a cause, but not in this case.
• “the 1/24 means the timestamp is converted to a date”. A valid statement, and to be truly correct I should have passed the expression using a interval, but that does not resolve the error.
• “your NLS parameters are not correct”. Nope, they were fine.
• “you have an old incarnation of the DBID”. Interesting theory, but not applicable in this case.

I’m not trying to rubbish anyone’s ideas here – this is more to show that when it comes to dealing with times, timestamps, timezones and the like, we always need to be very focussed on precision.

In fact, the resolution to this problem lies in (heaven forbid!) consulting the docs.

In most default configurations, when nominating a value for the LAST_ARCHIVE_TIME, that time must be nominated in UTC.

This is independent of what timezone your database is configured/running in. In my case, because I live in Perth, Australia, my database is 8 hours heads of UTC, and thus attempting to set a LAST_ARCHIVE_TIME of “systimestamp – 1/24” was in fact trying to set the time to 7 hours into the (UTC) future and hence the
error.

Clearly if you live (ie, run your database) anywhere on UTC or before, then (perhaps even worse) you are not going to have this error but you would not be achieving the archive timing you intended. Like our autonomous databases, if you are going to have an environment where you are expecting time-tagged data from anywhere in the world, then perhaps consider running your databases in UTC to avoid any sources of confusion.