A client recently realized that over the years, they had timezone inconsistencies across their database systems. They had more or less adopted and learned to live with them. While this can be done, complications can arise when using Data-guard, or doing timestamp recovery.
As they were migrating to Oracle Cloud Infrastructure and CloudExa, I explained this would be an excellent time to make all of their systems consistent.
They had already built out their test system using an inherited timezone, so we had to figure out how to change it. There were no cloud-specific documents in the support area on this issue, so we decided to open a Support Request with Oracle. This is not always known, but an "SR" can be opened to document or verify a procedure when the platform is newer and documents do not specifically mention it.
It took a few days, but after some research and re-checking with development, a support analyst said that it was believed that an on-premise document and procedure could be followed, but he will leave the request open should we run into problems. This document link is HERE.
Unfortunately, while very close to our situation, we found an anamoly or difference on Cloud Exadata that made it impossible to follow verbatim. It required the existence of the file:
/etc/sysconfig/clock
Our system did not have this file on either of it's two nodes!
So we notified Oracle. Once again, they went back to cloud development to seek direction due to this unplanned situation. Development was unaware that cloud systems could be delivered without this file, and they considered it a bug and will be opening one to address this.
So now we needed a new plan. Between development, the analyst and ourselves, we checked on the format of the file (it was actually very simple). it consists of this verbiage for our case:
ZONE="America/New_York" Oracle support did not give us this exact syntax, however, there is a group of files and directories with examples in /usr/share/zoneinfo. Under subdirectory "America" is a file named "New_York", and that exact set of characters was consistent with the example in the documentation referenced above.
The system is currently running in Mountain Time, which can be seen by executing this command:
$ date
Tue Jul 25 07:57:53 MDT 2023
So, nothing is changed yet. We just needed to verify how to proceed. After arranging for a complete database outage with interested parties, the next step was to shutdown all services in the Grid Infrastructure stack as user root:
$ crsctl stop crs
Messages returned:
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'exa-skyze1'
.........
CRS-4133: Oracle High Availability Services has been stopped.
Next we should verify everything is stopped:
$ crsctl stat res -t
Messages returned:
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.
Above errors are expected results - the CRS is not running!
Now onto the timezone specific changes:
As root, the file /etc/sysconfig/clock was created with the "ZONE=America/New_York":
[root@exa ~]# date
Tue Jul 25 07:38:51 MDT 2023
[root@exa- ~]# vi /etc/sysconfig/clock
[root@exa ~]# date
Tue Jul 25 07:39:31 MDT 2023
[root@exa- ~]# cat /etc/sysconfig/clock
ZONE="America/New_York"
(please note full system name is obfuscated to maintain client confidentiality).
This next command will actually change the timezone on the nodes:
ln -sf /usr/share/zoneinfo/America/New_York /etc/localtime
Now for the results!
[root@exa ~]]# date
Tue Jul 25 09:45:14 EDT 2023
Above steps must be repeated on other node(s).
Once both nodes show the correct local time, we can restart services:
# crsctl start crs
CRS-4123: Oracle High Availability Services has been started.
(We had to wait several minutes until all services were restared).
Now the OS and all services are running on the new timezone.
However, when we logged into the oracle account and the databases, we found the task was still incomplete!!!
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 25 07:54:59 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
25-JUL-23 07.55.08.703743 AM -06:00
The above time is still in the Mountain Timezone.
What could cause this? The OS shows the correct time.
Looking at the environment for the "oracle" user account discloses the reason:
cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export PATH
export TZ=US/Mountain
At this point, we realize the database is only reflecting a local environment variable setting, so we modify the TZ attribute to "US/Eastern" with the desired result.
$ vi .bash_profile
[oracle@exa ~]$ date
Tue Jul 25 07:57:53 MDT 2023
[oracle@exa ~]$ source .bash_profile
Enter DB Name (TOOLUAT/ACMUAT/FMUAT): TOOLUAT
[oracle@exa1 ~]$ date
Tue Jul 25 09:58:03 EDT 2023
[oracle@exa ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 25 09:58:10 2023
Version 19.18.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
25-JUL-23 09.58.16.964305 AM -04:00
So as we can see in this vivid example, Oracle Support can only help so much when you have a process change. They do not know about localized information for your environment. They can only provide general guidelines. In our case, we had to look at several configurable attributes to complete the effort.
So far, testing with the timezone change is going well. We may still run into an issue or two with the data and backups. Time will tell, but at least, going forward, all of the systems will be running the same.
This is a good argument for running all systems in the UTC (greenwich mean time) zone. However, that would have required massive application changes and procedural adjustments for this particular system.
