Change Oracle database TIME_ZONE

Oracle allows you to specify the database timezone using a time zone name, listed in the V$TIMEZONE_NAMES view, or using a UTC offset (+/-HH:MI). 

For performance reasons, Oracle recommends setting the database time zone to UTC (0:00), as no conversion of time zones will be required.

The multitenant architecture allows you to specify a different database time zone for each pluggable database, with the time zone of the container database being used as the default.

When we change   Database Timezone   , we also need to update cluster and  database  Scheduler : 

On standby we  just need to  make changes on Crs .

Note 

--A PDB can have a different time zone.

--You can only change the database time zone if you have no TSLTZ columns

To locate the tables that uses the data type LOCAL TIME ZONE WITH LOCAL TIMESTAMP:

select t.owner, t.table_name, t.column_name, t.data_type

  from dba_tab_cols t, dba_objects o

 where t.data_type like '%WITH LOCAL TIME ZONE'

   and t.owner=o.owner

   and t.table_name = o.object_name

   and o.object_type = 'TABLE'

order by 1

/

 

Changing Time Zone at database level 

We can do only at Pdb level in case of     Pluggable database environment 

alter database set TIME_ZONE='+00:00'; 

ALTER DATABASE SET TIME_ZONE='Europe/London';

 alter pluggable database <pdb1> set time_zone = '00:00';  

Update Clusterware with  Timezone Data:

Please note to change   for Listener only if  we have dedicated listener for your database other wise no need 

During the GI installation, Oracle saves Timezone information in $CRS_HOME/crs/install/s_crsconfig_hostname_env.txt file, that makes TZ not to change for CRS even it is changed on OS level.

Timezone can be changed for the database using srvctl:

 timedatectl status|grep zone

 srvctl setenv database -d rspaws -t   'TZ=America/Phoenix'

 srvctl setenv listener -l LISTENER -t 'TZ=America/Phoenix'

 restart database and listener 

 srvctl getenv database -d rspaws

 srvctl getenv listener

Changing Database  Scheduler TimeZone :

select dbms_scheduler.stime from dual;

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','EUROPE/LONDON')

select dbms_scheduler.stime from dual;

Changing  Time Zone In environment Variable : 

We also need   check  environment variable ORA_TZFILE  as  that also influence  Db timezone . 

eg :   export ORA_TZFILE = America/Phoenix

Changing  Timezone for sql developer and  Windows  Registry : 

If you need to change the time zone of Oracle SQL Developer (or Oracle Data Modeler), then this is how to do it:

Go to the installation directory of Oracle SQL Developer.

Open the file located at: sqldeveloper/bin/sqldeveloper.conf.

At the end of file, add the following line: AddVMOption -Duser.timezone=GMT-4.

Restart your Oracle Sql Developer.

Verifying  Timezone in Oracle Client 

Oracle client uses your TimeZone environment  set at OS 

Use same Timezone for Database and Oracle Client .   Oracle Instant Client comes  with its own timezone  file imbedded in it 

We can check Timezone for Oracle client using ./genezi utility  under $ORACLE_HOME/bin/  .  

We will  see Oracle errors like ORA-01805 and ORA-01804  if  Timezone for Cleint  doesnt matches  database Timezone 

If we are using Full Oracle Client , sometime copying  files under $ORACLE_HOME/oracore/zoneinfo  folder  from Database server to Client home  does fix   for ORA-01804

 ORA-01804  is also reported due to missing libociei.dylib into /usr/locale/lib folder.  

In worse case , to fix    ORA-01804     reinstall Oracle client after fixing  Timezone Environment  at OS level 

To Verify  Environment Variable  for Windows Registry 

Registry Key HKCU\SOFTWARE\ORACLE\KEY_{Oracle Home Name}\ORA_SDTZ

Registry Key HKLM\SOFTWARE\ORACLE\KEY_{Oracle Home Name}\ORA_SDTZ

(resp. HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{Oracle Home Name}\ORA_SDTZ)

Environment variable ORA_SDTZ

Current locale settings of your machine (most likely).

Database time zone if none from above is found (just an assumption)

Change the time zone at session level

-- use alter session commands

ALTER SESSION SET TIME_ZONE=local;

ALTER SESSION SET TIME_ZONE=dbtimezone;

ALTER SESSION SET TIME_ZONE='Asia/Hong_Kong';

ALTER SESSION SET TIME_ZONE='+10:00';

  SELECT sessiontimezone FROM DUAL;

 

   

  col SYSTIMESTAMP for a50

  col STIME for a50

 select (select TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi') from dual) sdate,SYSTIMESTAMP, (select dbms_scheduler.stime from dual) STIME, DBTIMEZONE,( SELECT TZ_OFFSET( SESSIONTIMEZONE ) FROM DUAL) SESSIONTIMEZONE,

(SELECT TZ_OFFSET( 'EUROPE/LONDON' ) FROM DUAL) "specific_time_zone" from dual;

Views :

SELECT dbtimezone FROM DUAL;

 select value$ from props$ where name = 'DBTIMEZONE';

select systimestamp from dual;

SELECT tz_version FROM registry$database;

COLUMN property_name FORMAT A30

COLUMN property_value FORMAT A20

SELECT property_name, property_value

FROM   database_properties

WHERE  property_name LIKE 'DST_%'

ORDER BY property_name;

set lines 240

col SYSTIMESTAMP forma a40

col CURRENT_TIMESTAMP forma a40

alter session set NLS_DATE_FORMAT='dd-MON-yyyy:hh24:mi:ss';

select dbtimezone,systimestamp,sysdate,current_timestamp,localtimestamp from dual;

References :

Configure Different Timezones per PDB (Doc ID 2143879.1)

ORA-1804: Failure to Initialize Timezone Information (Doc ID 365558.1)