Oracle Database time differ than HP-UX OS

[Akif_1]

The Oracle Database time is differ than the real UNIX TZ, Kindly follow below details for more info.
A connection establish via $sqlplus system@db11/db22 instance the time is differ than @sqlplus system,

OS:HP-UX 11.11
DB:Oracle RAC 10g R2

SQL> select to_char(sysdate,'dd:mon:yyyy ss:mi:hh24') from dual;


Unix time zone set as
Asia/Riyadh
/etc/TIMEZONE = WAT-3


SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;

TZ_OFFS TZ_OFFS
------- -------
+03:00 -04:00

==================================
login sqlplus@system time is (OK)
==================================
$ . ./.oraenv.db
$ sqlplus system

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 23 10:53:09 2010

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

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'D
------------------
23-JAN-10 10:53:45


========================================
login sqlplus@system@NPRD1 (time not-OK)
=========================================
$ . ./.oraenv.db
$ sqlplus system@NPRD1

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jan 23 10:55:25 2010

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

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'D
------------------
23-JAN-10 02:55:36


# Added a line " (ENVS='TZ=WAT-3') " and /home/oracle/.dtprofile after reboot issue not resolved.

I appreciate all who share their live experience to solve this problem............


Have a charming day !

[Horia Chirculescu]

Hello,

Maybe you have a problem with database Time Zone

select tzname,tzabbrev
from V$TIMEZONE_NAMES
where tzabbrev = 'UTC+3'

If returns Asia/Riyadh, you can use UTC.
If returns nothing, try:

select tzname,tzabbrev
from V$TIMEZONE_NAMES
where tzabbrev = 'GMT+3'
and below you should use GMT instead UTC.
The field TZNAME is the one you can use in ORACLE. (can be 1: Asia/Riyadh or 2: Etc/GTM+3)



select DBTIMEZONE from dual;

should return Asia/Riyadh or UTC+3 (GMT+3)

If not, set it to the right one, using

ALTER database SET TIME_ZONE = 'Asia/Riyadh';

or:

ALTER database SET TIME_ZONE = 'Etc/GMT+3';

Also, in Unix you should have the correct TZ for your area:

in /etc/TIMEZONE you should have:

TZ=UTC+3

or, better:

TZ=Asia/Riyadh


Remove the TZ var. from /home/oracle/.dtprofile. There is not necessary to reboot your server.

Afther that, you can check that the oracle DB time is the same as reported by unix date command by using:

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
27-JAN-10 08.57.29.133588 AM +02:00

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
$ date
Wed Jan 27 08:57:39 EET 2010
$

Best regards,
Horia.

[Akif_1]

Hi Horia,

Find below out put of above command's. The output of sysdate is wrong when i connected via sqlplus system@instance , If i connect via sqlplus system time is ok.


Alter DB tried on test based environment did not workout

Note: HP-UX time set via set_parms timezone Asia/Riyadh /etc/TIMEZONE TZ=WAT-2 (system defined)

SQL> select tzname,tzabbrev
from V$TIME 2 ZONE_NAMES
where tz 3 abbrev = 'UTC+3';

no rows selected

SQL> select tzname,tzabbrev
fr 2 om V$TIMEZONE_NAMES
where tza 3 bbrev = 'GMT+3';

TZNAME
--------------------------------------------------------
TZABBREV
--------------------------------------------------------
Etc/GMT+3
GMT+3


SQL> select DBTIMEZONE from dual;

DBTIME
------
-04:00


Rgd's

[Akif_1]

SQL> ALTER database SET TIME_ZONE = 'Asia/Riyadh';
ALTER database SET TIME_ZONE = 'Asia/Riyadh'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH
LOCAL TIME ZONE columns


SQL> ALTER database SET TIME_ZONE = 'Etc/GMT+3';
ALTER database SET TIME_ZONE = 'Etc/GMT+3'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH
LOCAL TIME ZONE columns

[Horia Chirculescu]

Hello,

You get that error becouse you do have colums with timestamps with local time zone.

The solution in this case is to actually drop the columns and to recreate those.

Read (in full):
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3083336233923


"Followup June 9, 2005 - 6pm Central time zone:

you can alter the database IF you don't have any timestamps with local time zone, you need not
recreate (that was my first comment above in fact) "

Best regards,
Horia.

[Horia Chirculescu]

Another observation:

WAT-3 = GMT+1-3=GMT-2

But you said that your server location is: Asia/Riyadh

meaning: GMT+3

So you should decide where your server is located.


Horia.

[Horia Chirculescu]

Playing around with TZ:

$ TZ=GMT+3
$ date
Wed Jan 27 06:19:37 GMT 2010
$ sqlplus /nolog;

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 27 06:19:49 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba;
Connected.
SQL> select DBTIMEZONE from dual;

DBTIME
------
+03:00

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
27-JAN-10 06.20.46.289103 AM -03:00

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Producti
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
$ TZ=WAT-3
$ date
Wed Jan 27 12:21:43 WAT 2010
$ sqlplus /nolog;

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Jan 27 12:22:23 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba;
Connected.
SQL> select DBTIMEZONE from dual;

DBTIME
------
+03:00

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
27-JAN-10 12.22.40.064240 PM +03:00

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
$


Horia.

[Akif_1]

Hi Hori,

The GMT of Riyadh is GMT+3 , But when i go through in HP-UX with setting parameters for timezone #set_params timezone - > Asia ->Riyadh , UNIX itself assing WAT-3 under /etc/TIMEZONE. It means time zone (WAT-3)is ok.

Now The sysdate is also ok when i connect via sqlplus sys as sysdba , But if i connect via listener_SID or instance ID (i.e sqlplus system@db1, The time is not correct .

[Horia Chirculescu]

Pay attention to this Mike Roger's problem:

http://forums11.itrc.hp.com/service/forums/questionanswer.do?threadId=322646

read Bill Hassell's posts.

Horia.

[Dennis Handly]

>The GMT of Riyadh is GMT+3

So if you don't like WAT-3, you can use "TZ=Riyadh-3".

>Horia: in /etc/TIMEZONE you should have: TZ=UTC+3
>better: TZ=Asia/Riyadh

The first is never a good idea and it is 6 hours off. Using GMT-3 gives you the right time but it says you are in GMT, London.
The second will not work, it's the same as GMT0.

>Another observation:
>WAT-3 = GMT+1-3=GMT-2

No, WAT-3 is WAT-3, 3 hours ahead of UTC/GMT, not 2.

>read Bill Hassell's posts.

You need to be careful because Bill has it backwards, GMT3 is in the Atlantic or Brazil.

[Dennis Handly]

If you are on 11.31 and want it to display GMT+3, you can "quote" the timezone:
$ TZ=WAT-3 date
Wed Jan 27 15:20:33 WAT 2010
$ TZ="-3" date
Wed Jan 27 15:20:38 GMT+3 2010

[Horia Chirculescu]

Hello,

Denis,

>Horia: in /etc/TIMEZONE you should have: TZ=UTC+3
>better: TZ=Asia/Riyadh

"The first is never a good idea and it is 6 hours off. Using GMT-3 gives you the right time but it says you are in GMT, London.
The second will not work, it's the same as GMT0."

GMT-3 does not give the right time for Asia/Riyadh!

Asia/Riyadh is the same as GMT+3.
There is only a split second difference between UTC and GMT, so for most of the situations (like this one) UTC and GMT is the same.

read:

http://en.wikipedia.org/wiki/Coordinated_Universal_Time



>Another observation:
>WAT-3 = GMT+1-3=GMT-2

"No, WAT-3 is WAT-3, 3 hours ahead of UTC/GMT, not 2."

WAT is undefined in /usr/lib/tztab, so Unix would default to GMT. Practically, if Akif would write on /etc/TIMEZONE TZ=WAT-3 as instructed, his system would use GMT-3, which is wrong. Akif should check of course if WAT is defined on tztab on his system.


>read Bill Hassell's posts.

"You need to be careful because Bill has it backwards, GMT3 is in the Atlantic or Brazil. "

You are wrong.
GMT3 means GMT+3; notice the plus "+" sign

From manual pages (man environ):

"If offset is preceded by a -, the time zone is east of the Prime Meridian. A + preceding offset indicates that the time zone is west of the Prime Meridian. The default case is west of the Prime Meridian.
"

"Atlantic or Brasil" is east of the Prime Meridian, not west. This means GMT-3 for this area.

Best regards,
Horia.

[Dennis Handly]

>Horia: GMT-3 does not give the right time for Asia/Riyadh!

That's TZ=GMT-3 and it matches:
http://www.timeanddate.com/worldclock/city.html?n=214

>Asia/Riyadh is the same as GMT+3.

I'm not talking about Windows terms but HP-UX and Posix definition of the TZ variable.

>There is only a split second difference between UTC and GMT,

For HP-UX, they are the same. :-)

>WAT is undefined in /usr/lib/tztab, so Unix would default to GMT.

I assumed you were talking about TZ=WAT-3. And that says to use 3 hours before UTC.

>You are wrong.

I'm not sure why you think so?

>GMT3 means GMT+3; notice the plus "+" sign

Exactly.

>"Atlantic or Brazil" is east of the Prime Meridian, not west. This means GMT-3 for this area.

I'm not sure what world you live on but in my western hemisphere, the Posix standard chauvinistically declared west as positive in TZ and yours negative.

Last time I looked Brazil was west. But there are small parts of the Atlantic that are east, none more than 3 hours.

[Akif_1]

Hi,

Thanks for your link info, Problem is same as with Mr.Rogger,

But still unable to solve the issue. other details are:

# date
Sat Jan 30 09:20:35 WAT 2010
# date -u
Sat Jan 30 06:20:37 UTC 2010
# more /etc/default/tz
EST5EDT
#more/etc/TIMEZONE
TZ=WAT-3

The actual time is 09:20AM as of today.

Thanks & Rgd's

[Jupinder Bedi]

TZ=WAT-3

means In this case 3 hours must be subtracted to get UTC

so it means there is no any problem in your system .

Good Luck

[Jupinder Bedi]

Here is the output of my server


TZ=CST6CDT
export TZ
# date
Sat Jan 30 00:45:25 CST 2010

# date -u
Sat Jan 30 06:45:30 UTC 2010


so dont be confuse and enjoy.

[Akif_1]

Hi,

If you look my real problem with TIMZONE it is a bit messup.

Kinly follow below repeated details:

#TIME is ok if connect Oracle DB via sqlplus system/manager

#Display incorrect TIME if i connect via sqlplus system@Inst1(Instance).

for indepth info follow below details:

The GMT of Riyadh is GMT+3 , But when i go through in HP-UX with setting parameters for timezone #set_params timezone - > Asia ->Riyadh , UNIX itself assing WAT-3 under /etc/TIMEZONE. It means time zone (WAT-3)is ok.

Now The sysdate is also ok when i connect via sqlplus sys as sysdba , But if i connect via listener_SID or instance ID (i.e sqlplus system@db1, The time is not correct .

Rgd's

[Dennis Handly]

>Problem is same as with Mr. Roger,

I'm not sure why? He didn't mention Oracle.

>The actual time is 09:20AM as of today.

Your date command says that, so what else do you want?

>UNIX itself using WAT-3 under /etc/TIMEZONE. It means time zone (WAT-3) is ok.

Yes.

>But if I connect via listener_SID or instance ID, The time is not correct.

This most likely means the TZ variable isn't set to WAT-3 for these processes.

[Jupinder Bedi]

I dont know but I believe at the time of oracle installation , you have mentioned different timezone. can you check at the oracle installation logs or any configuration file .

[Akif_1]

Hi Dennis,

Under RAC listener 'ENVS='TZ=WAT-3' parameter set and restarted the listener/DB , But no effect same problem.

Rgd's

[Jeeshan]

You have to set the SETENV TZ=WAT-3 using srvctl command.


But before doing this, you must have to shutdown your CRS cluster, Listener and all oracle related services.

I have faced the same problem and resolved like the above way.

[Akif_1]

Hi UNIX,

After updating listener need to SETENV on database of listener confirm this.

Listerner info:
===============
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /opt/oracle/product/10.2.0/db_1)
(ENVS='TZ=WAT-3')
(SID_NAME = mydbname)
)
)

For RAC, you may also need to do the following:
====================================
srvctl setenv database -d mydbname -t TZ=WAT-3
srvctl stop database -d mydbname
srvctl start database -d mydbname

Kindly confirm command srvctl setenv database or srvctl setenv listener to solve this problem.....

Thanks ,

Have a wonderful day!

[Akif_1]

Hello UNIX,

Any update on this issue.


Rgd's

[Horia Chirculescu]

Hello, Akif.

When you do: login sqlplus@system@NPRD1 (time not-OK), you use tnsnames.ora.

When you are logged in as the user that actually starts the database (not root!, check $) do

set

and check for TNS_ADMIN variable. Also it is time to, double-check TZ variable.

(or

set |grep TZ
set |grep TNS
)

"
If TWO_TASK isn't set, Oracle uses the TZ variable from the local environment.

If TWO_TASK IS set, Oracle uses the TZ variable of the listener process
running on the server.

You could have multiple listeners, each with their own TZ, and assign users to the appropriate listener by setting TNS_ADMIN to a directory that contains a tnsnames.ora file that points to the port that their listener is on."

From AskTom:

"
If you are in a Unix environment, you can normally set 2 environment variables:

ORACLE_HOME
ORACLE_SID

that will connect you to a local database identified by that home and sid. If you wanted
to be able to connect to a remote database or a database on that machine via SQLNet, you
can also set:

TWO_TASK =


The setting of TWO_TASK overrides the ORACLE_SID when set. You will not connecting to a
local database with two_task but rather using sqlnet to connect to a remote database.

So, instead of:

$ sqlplus scott/tiger@some_db

I can:

$ setenv TWO_TASK some_db
$ sqlplus scott/tiger

and the @some_db is implied.
"


You should check for multiple declarations of TZ variable. Check the UNIX environment and the listeners.

Horia.

[Akif_1]

Hi Horia,

I appreciate your response, But iam not trying to connect reomote DB as Mr.TOM info.

Check below my set environment variables info.

$. ./.oraenv.db
$ set |grep TNS
TNS_ADMIN=/u01/app/oracle/oracle/product/10.2.0/db_1/network/admin
$ set |grep TZ
ORA_TZFILE=/u01/app/oracle/oracle/product/10.2.0/db_1/oracore/zoneinfo/timezlrg.dat
TZ=WAT-3
$ sqlplus system@DB2

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 1 11:28:54 2010

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

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL> select to_char(sysdate, 'HH:MI:SS') from dual;

TO_CHAR(
--------
03:29:28

$ date
Mon Feb 1 11:30:05 WAT 2010

$ date -u
Mon Feb 1 08:30:11 UTC 2010

Rgd's