Sometimes we might have different OS timezone settings while starting the database. Also, it could be possible that srvctl is using different timezone settings while starting the Database and this will impact the Database timezone used. You can use the below method to find currently used OS timezone for background processes
For 11.2 and up:
this will give the time and the OS timezone for the last 20 startups in descending order
conn / as sysdba
SET SPACE 1 LINESIZE 80 PAGES 1000
SELECT * FROM (
select to_char(ORIGINATING_TIMESTAMP,'YYYY/MM/DD HH24:MI:SS TZH:TZM')
from V$DIAG_ALERT_EXT
WHERE trim(COMPONENT_ID)='rdbms'
and MESSAGE_TEXT like ('PMON started with%')
order by originating_timestamp desc )
WHERE rownum < 20;
For 11.1 and lower
This will give the current OS setting of the database background processes ( this should match the OS timezone of the last database startup):
conn / as sysdba
spool /tmp/jobtime.txt
select to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;
select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual;
Drop table SYS.SYSTIME_TEST purge;
create table SYS.SYSTIME_TEST (col1 varchar2(30 char));
begin
DBMS_SCHEDULER.DROP_JOB ('SYSTIME_TEST_JOB');
end;
/
begin
dbms_scheduler.create_job('SYSTIME_TEST_JOB','PLSQL_BLOCK','begin insert into SYS.SYSTIME_TEST select to_char(systimestamp, ''DD/MM/YYYY HH24:MI:SS TZR'') from dual; insert into SYS.SYSTIME_TEST select to_char(sysdate, ''DD/MM/YYYY HH24:MI:SS'') from dual; insert into SYS.SYSTIME_TEST select SESSIONTIMEZONE from dual; commit; end;',start_date=>null, enabled=>true,repeat_interval => null);
end;
/
exec dbms_lock.sleep(5);
select * from SYS.SYSTIME_TEST;
Drop table SYS.SYSTIME_TEST purge;
spool off
The output of the select will give the *current* OS time and timezone seen by the oracle background processes.
The output from SYSTIME_TEST will give the timezone in which the database was started
Hope this article helps in troubleshooting the issues. Please do provide Feedback to improve
Related Article
how to check timezone in oracle database