Solution is to use the TZ environment variable. This is applicable for both single instance and RAC databases. This post gives an example having two databases with different timezones in the same server.
First up is the single instance case. The two databases are std11g2 and ent11g2 (both 11.2.0.3 databases). The timezone of the std11g2 will be changed to GMT+5 while the timezone of the ent11g2 will remain unaffected. As it is now both databases have the same timezone
SQL> select dbtimezone from dual; DBTIME ------ +00:00Set the TZ to desired timezone and restart the database that requires timezone to be changed
export TZ=Etc/GMT+5There was no need to restart the listener. In fact in this case three databases were running in the same server and listener was running out of a 12.1.0.2 Oracle home and the two 11.2 databases used for this post registered with this listener. Even after the database is restarted the timezone will still show as before the restart. But querying the systimestamp will show the time according to the timezone used.On std11g2
SQL> select dbtimezone from dual; DBTIME ------ +00:00 SQL> SELECT systimestamp from dual; SYSTIMESTAMP ----------------------------------- 19-NOV-14 12.09.57.837228 PM -05:00On ent11g2
SQL> select dbtimezone from dual; DBTIME ------ +00:00 SQL> SELECT systimestamp from dual; SYSTIMESTAMP ----------------------------------- 19-NOV-14 05.09.57.694861 PM +00:00All the remote connections to the database will use the respective timezones
unset TZ sqlplus sys@std11g2 as sysdba SQL> select systimestamp from dual; SYSTIMESTAMP ----------------------------------- 19-NOV-14 07.26.24.918270 AM -05:00 sqlplus sys@ent11g2 as sysdba SQL> select systimestamp from dual; SYSTIMESTAMP ----------------------------------- 19-NOV-14 12.26.45.653530 PM +00:00For RAC databases where the start and stop of database is managed by the clusterware the timezone information is specified using the setenv. In this case two databases (std12c1 and tzdb both 12.1) reside in same cluster nodes and it's expected that tzdb to have a different timezone. Both databases were using the same listeners (SCAN, Listener). Query the current environment setting for any timezone information using getenv
srvctl getenv database -d std12c1 std12c1: srvctl getenv database -d tzdb tzdb:Set the timezone information using setenv for the tzdb database
srvctl setenv database -d tzdb -T 'TZ=Etc/GMT+5'Verify the setting
srvctl getenv database -d tzdb tzdb: TZ=GMT+5Stop and restart the database
srvctl stop database -d tzdb srvctl start database -d tzdbQuery the databases for timestamp
sqlplus asanga@tzdb SQL> select systimestamp from dual; SYSTIMESTAMP ----------------------------------- 19-NOV-14 11.22.50.685116 AM -05:00 sqlplus asanga@std12c1 SQL> select systimestamp from dual; SYSTIMESTAMP ----------------------------------- 19-NOV-14 04.23.08.054139 PM +00:00To remove timezone information on RAC use
srvctl unsetenv database -d tzdb -envs TZ srvctl getenv database -d tzdb tzdb:
A test cases was used to simulate how a application server that connects to the database via JDBC would see the time values. The java code is given at the end of the post. The output resulted from running this code against the RAC databases is given below.
2014-11-19 16:44:10.542481 xxxx 2014-11-19 16:44:10.542481 +0:00 xxxx 16:44:10 2014-11-19 16:44:10.0 xxxx 2014-11-19 16:44:10 xxxx 16:44:10 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 2014-11-19 16:44:10.606114 xxxx 2014-11-19 11:44:10.606114 -5:00 xxxx 16:44:10 2014-11-19 11:44:10.0 xxxx 2014-11-19 11:44:10 xxxx 11:44:10The program queries the database for systimestmap and sysdate. The top half before the divider (xxxxx) represent the output from the std12c1 database (which didn't change the timezone). The bottom half shows the output from the database tzdb which had the timezone changed.
On each half there are two lines. First line represent getting the systimestamp from the JDBC resultset using getTimestamp,getString and getTime methods. The second line represent getting sysdate from the resultset using the same set of methods. The machine that ran the java program had the same timezone as the std12c1 database.
From the output it could be seen that querying the systimestamp and getting the result using either getTimestamp and getTime methods loses the timezone information and shows the incorrect time. On the other hand getting the results using the getString method preserves the timezone information.
However querying the sysdate and obtaining the result from any of the aforementioned methods doesn't have this problem and time with respect to the timezone used is given. Therefore application using this method could run into problems if the client side timezone is different to that of the database timezone and how systimestmap results are obtained.
To overcome this problem change the timezone on the application servers to match the database timezone. If multiple applications are running out of same server use "user.timezone" to set the timezone for each application server based on the database it is connecting to.
Java code used for the test case
Public class Test {
public static void main(String[] args) throws SQLException {
tz1();
System.out.println("\nxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\n");
tz2();
}
public static void tz1() throws SQLException {
OracleDataSource ds = new OracleDataSource();
ds.setUser("asanga");
ds.setPassword("asa");
ds.setURL("jdbc:oracle:thin:@192.168.0.66:1521:ent11g2");
Connection con = ds.getConnection();
PreparedStatement pr = con.prepareStatement("select systimestamp,sysdate from dual");
ResultSet rs = pr.executeQuery();
while (rs.next()) {
System.out.println(rs.getTimestamp(1) + " xxxx " + rs.getString(1) + " xxxx " + rs.getTime(1));
System.out.println(rs.getTimestamp(2) + " xxxx " + rs.getString(2) + " xxxx " + rs.getTime(2));
}
rs.close();
pr.close();
con.close();
}
public static void tz2() throws SQLException {
OracleDataSource ds = new OracleDataSource();
ds.setUser("asanga");
ds.setPassword("asa");
ds.setURL("jdbc:oracle:thin:@192.168.0.66:1521:std11g2");
Connection con = ds.getConnection();
PreparedStatement pr = con.prepareStatement("select systimestamp,sysdate from dual");
ResultSet rs = pr.executeQuery();
while (rs.next()) {
System.out.println(rs.getTimestamp(1) + " xxxx " + rs.getString(1) + " xxxx " + rs.getTime(1));
System.out.println(rs.getTimestamp(2) + " xxxx " + rs.getString(2) + " xxxx " + rs.getTime(2));
}
rs.close();
pr.close();
con.close();
}
}
