Changing timezone inside the Oracle database

6:55 PM

When the timezone is changed on the host it will not automatically change the time inside the database. In order for that to occur you need to plan a maintenance with the DBA so we can update the time and stop and start the database to reflect the change.
Same steps whether this is Linux or Windows standalone Oracle, if RAC, oracle and crs needs to be stopped on one node while host changes are made on both nodes so CRS won't evict the 2nd node when it discovers the timestamp does not match on the hosts during the change process:
1. Time is modified by SE/SA on the host, make sure SE/SA also changes clock
example - output of commands
[oracle@ashsetlkdb01p admin]$ date
Mon Aug  8 21:22:57 UTC 2011
[oracle@ashsetlkdb01p admin]$ cat /etc/sysconfig/clock
#ZONE="America/New_York"
ZONE="UTC"
UTC=true
ARC=false
2. DBA modifies time inside Oracle
example - SQL> alter database set time_zone='+00:00';
3. DBA stops and starts database
4. DBA verifies timezone
example -
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
--------------------------------------------
+00:00
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
--------------------------------------------
23-JUN-11 11.54.27.782000 PM +00:00
 5.  When RAC do also the following
 srvctl setenv database -d setlkprd -t TZ=UTC

You Might Also Like

0 comments

Contact Form

Name

Email *

Message *

Translate

Wikipedia

Search results