How to Diagnose Wrong Time -Wrong Time Zone- Oracle database

How to Diagnose Wrong Time

Issue Details:-

We had faced an issue of Wrong time zone in RAC environment after switchover done and application team informed that time was lagging behind by 5 hours as this application uses TZ=Europe/Berlin time zone but it was taking TZ=America/New_York

We started investigation:- 

ORCLDB:

SQL> SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;

DBTIME  SESSIONTIMEZONE  

------  ------------------

+00:00   +01:00

sqlplus sys/******@ORCLDB as sysdba 

[oracle@bms04dbaora02t admin]$ sqlplus sys/******@ORCLDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 10:49:12 2024

Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP

---------------------------------------------------------------------------

07-MAR-24 10.49.30.206771 AM +01:00

SQL> select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') , to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;

TO_CHAR(SYSDATE,'DD/MM/YYYYHH24:MI:SS')  TO_CHAR(SYSTIMESTAMP,'DD/MM/YYYYHH24:MI:SSTZR')

--------------------------------------   ------------------------------------------

07/03/2024 04:50:42                      07/03/2024 04:50:42 -05:00 ------------------ >>>> Issue

We have below two nodes in RAC

Node 1 :- ora04dba05p

Node 2 :- ora04dba06p

I went to below location and checked s_crsconfig_ora04dba05p_env.txt file  and could see the TZ=America/New_York, this was the issue ..

cd /applications/oracle/g19.3.0/grid_home/crs/install

[grid@ora04dba05p install]$ cat s_crsconfig_ora04dba05p_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

#   characterset used for messages. For example, a new value can be

#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

#   the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

#   can be specified by modifying the appropriate entries.

#

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=America/New_York

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=2048

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

[grid@ora04dba06p install]$ cat s_crsconfig_ora04dba06p_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

#   characterset used for messages. For example, a new value can be

#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

#   the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

#   can be specified by modifying the appropriate entries.

#

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=America/New_York

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=2048

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

Solution :-

1) Modify the files with TZ=Europe/Berlin time 

2) Restart the clusterware one by one

/applications/oracle/g19.3.0/grid_home/crs/install

[grid@ora04dba05p install]$ cat s_crsconfig_ora04dba05p_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

#   characterset used for messages. For example, a new value can be

#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

#   the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

#   can be specified by modifying the appropriate entries.

#

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=Europe/Berlin

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=2048

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

root#ora04dba05p] crsctl stop crs

root#ora04dba05p] crsctl start crs

ora04dba06p:-

/applications/oracle/g19.3.0/grid_home/crs/install

[grid@ora04dba06p install]$ cat s_crsconfig_ora04dba06p_env.txt

#########################################################################

#This file can be used to set values for the NLS_LANG and TZ environment

#variables and to set resource limits for Oracle Clusterware and

#Database processes.

#1. The NLS_LANG environment variable determines the language and

#   characterset used for messages. For example, a new value can be

#   configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8

#2. The Time zone setting can be changed by setting the TZ entry to

#   the appropriate time zone name. For example, TZ=America/New_York

#3. Resource limits for stack size, open files and number of processes

#   can be specified by modifying the appropriate entries.

#

#Do not modify this file except as documented above or under the

#direction of Oracle Support Services.

#########################################################################

TZ=Europe/Berlin

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

CRS_LIMIT_STACK=2048

CRS_LIMIT_OPENFILE=65536

CRS_LIMIT_NPROC=65536

TNS_ADMIN=

root#ora04dba06p] crsctl stop crs

root#ora04dba06p] crsctl start crs

now we are getting correct value

[oracle@bms04dbaora02t admin]$ sqlplus sys/*****#1234@ORCLDB as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 11:27:07 2024

Version 19.10.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') , to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;

TO_CHAR(SYSDATE,'DD/MM/YYYYHH24:MI:SS')  TO_CHAR(SYSTIMESTAMP,'DD/MM/YYYYHH24:MI:SSTZR')

--------------------------------------   ------------------------------------------

07/03/2024 11:27:12                      07/03/2024 11:27:12 +01:00 -------------------->>>> Issue Resolved

How to Diagnose Wrong Time ( SYSDATE and SYSTIMESTAMP) After DST Change , Server Reboot , Database Restart or Installation When Connecting to a Database on an Unix Server (Doc ID 1627439.1)

select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') , to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;

select current_timestamp from dual;

select systimestamp from dual;

SELECT DBTIMEZONE FROM DUAL;

SELECT SESSIONTIMEZONE FROM DUAL;

SELECT DBTIMEZONE FROM DUAL

SELECT SESSIONTIMEZONE FROM DUAL

SELECT DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file - Temp file issue

We were facing below error :- 

SQL> select file_name from dba_temp_files;

select file_name from dba_temp_files

                                   *

ERROR at line 1:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '+DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf'

SQL> select tablespace_name,sum(bytes)/(1024*1024) Temp_Size_MB from dba_temp_files group by tablespace_name;

select tablespace_name,sum(bytes)/(1024*1024) Temp_Size_MB from dba_temp_files group by tablespace_name

                                                                                                      *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 202 - see DBWR trace file

ORA-01110: data file 202: '+DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf'

Above datafile is dedicated for Tivoli user at database level.

SQL> select username, temporary_tablespace, default_tablespace from dba_users where username='TIVOLI';

username    temporary_tabespace   default_tablespace

------------   ------------------------   ---------------------

TIVOLI         TIVOILTEMPPT              TIVOLIORTS

Before proceeding let's create new temporary tablespace for TIVOLI user:-

SQL> create temporary tablespace IVOLITEMPT1 tempfile '+DATA/' size 2G;

Tablespace created.

Assigned IVOLITEMPT1 temp tablespace to user TIVOLI:-

SQL> alter user tivoli temporary tablespace TIVOLITEMPT1;

User altered.

You can confirm by executing below query:-

SQL> select temporary_tablespace from dba_users where username='TIVOLI';

TEMPORARY_TABLESPACE

------------------------------

TIVOLITEMPT1

1 row selected.

SQL>  select file#,status,name from v$tempfile;

     FILE# STATUS  NAME

---------- ------- --------------------------------------------------

         1 ONLINE  +DATA/ORCLDB/TEMPFILE/temp.308.1160136011

         2 ONLINE  +DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf

         3 ONLINE  +DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

3 rows selected.

SQL> select username,TEMPORARY_TABLESPACE,DEFAULT_TABLESPACE from dba_users where username='TIVOLI';

USERNAME           TEMPORARY_TABLESPACE       DEFAULT_TABLESPACE

-------------------- ------------------------------ ------------------------------

TIVOLI                   TIVOLITEMPT1                      TIVOLIORTS

tivoliortstempt01.dbf --This is culprit datafile so let's drop it 

SQL> alter database tempfile '+DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf' offline ;

Database altered.

SQL> select file#,status,name from v$tempfile;

     FILE# STATUS  NAME

---------- ------- --------------------------------------------------

         1 ONLINE  +DATA/ORCLDB/TEMPFILE/temp.308.1160136011

         2 OFFLINE +DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf

         3 ONLINE  +DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

3 rows selected.

SQL>

SQL>

SQL> select tablespace_name, file_name from dba_temp_files;

TABLESPACE_NAME FILE_NAME

--------------- --------------------------------------------------

TEMP            +DATA/ORCLDB/TEMPFILE/temp.308.1160136011

TIVOLITEMPT1    +DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

TIVOLITEMPT     +DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf

3 rows selected.

SQL>  select file#,status,name from v$tempfile;

     FILE# STATUS  NAME

---------- ------- --------------------------------------------------

         1 ONLINE  +DATA/ORCLDB/TEMPFILE/temp.308.1160136011

         2 OFFLINE +DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf

         3 ONLINE  +DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

3 rows selected.

SQL>

SQL> alter database tempfile '+DATA/ORCLDB/TEMPFILE/tivoliortstempt01.dbf' drop including datafiles;

Database altered.

SQL>

SQL>

SQL>  select file_name from dba_temp_files;

FILE_NAME

-------------------------------------------------------------------------------------

+DATA/ORCLDB/TEMPFILE/temp.308.1160136011

+DATA/ORCLDB/TEMPFILE/tivolitempt1.312.1163002691

2 rows selected.

SQL>

SQL>

SQL>  select * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE SHARED           INST_ID

--------------- --------------- --------------- ---------- ------------- ----------

TEMP                       3.4084E+10        65011712 3.4069E+10 SHARED

TIVOLITEMPT1         2147483648         1048576 2146435072 SHARED

2 rows selected.

SQL>

SQL>

SQL>