Learning is not a spectator sport

If you’ve ever used a cloud database, then it is most likely that database was running in the “time zone” UTC. (UTC is not a time zone per se, but basically we’re running in GMT which means the database and server run at a zero time offset). I’ve done a video in the past about why this makes a whole lot of sense which you can view below

Of course, on cloud the provider of the database (us) has full control over the server and the database, so we can keep all things related to time zones in alignment. But when we provide a 23ai VM for you to use, then the “server” part of the control is now yours, namely, its your PC or laptop that is running the VM, and that could be running in any time zone of your preference. We still ship the database in UTC, which means during startup in the alert log, you might see something like this:

2023-09-07T10:37:08.518372         
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
2023-09-07T02:37:10.782525         

You can see the first line is the server time of my PC whilst the database instance is being started. The database isn’t “present” yet, so the only date/time information we have at our disposal is that of the VM, which defaults to the hosting machine. In my case, you can see I am booting this machine at about 10:37am in the morning in Perth, which is GMT+8. The moment the database gets a little further along the startup path, we now know that the database is running in UTC, so the time flips back to 02:37am, 8 hours earlier.

But think about why so many database professionals get anxious at the end of summer. They’re concerned about how their database and the applications that run on it will react when the time goes backwards by 60 mins at the end of “summer time” or “daylight savings time”. I know lots of customers that either take an outage, or use NTP drift to slow the server down just to avoid this “glitch” in their time recording. In this case, I’m grabbing your VM by the scruff of the neck and saying “Hey, lets go back in time 8 hours!“. It is little surprise that the next messages in the alert log are

Warning: VKTM detected a backward time drift.
Time drifts can result in unexpected behavior such as time-outs.

If you are working in a time zone earlier than GMT (eg Americas), then the clock will simply jump forward which is little cause for concern. If you live in Europe, then the clock may be fine or might jump backward an hour or so. But if you live anywhere east of Europe, especially in Asia Pacific, then that clock is going to jump backwards in time a long way. I’ve seen some very strange behaviours from database when you do that to them. Think about things like flashback query, undo retention, optimizer statistics histories … All of these things are time sensitive.

So if you are using our 23ai Free Database VM, I’d recommend you run the machine in UTC rather than your local time zone, or change the database itself to run in your local time zone. In either case, its just good practice to bring all the layers into alignment. Here’s how to make the VM run in UTC.

or use “alter database set time_zone” to modify the database.

Otherwise you might get some strange behaviours or even end up with the database not starting.