Serious timezone confusion... I'm outta answers
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Serious timezone confusion... I'm outta answers

  1. #1
    Join Date
    Jun 2006
    Posts
    3

    Unhappy Serious timezone confusion... I'm outta answers

    Oracle 9.2.0.5
    HPUX 11x

    Okay, we are in the Central time zone. I need everything to return Central time. Please review these two scenarios for me, I just can't find out what is wrong.

    Connecting as a "bequeath" connection:

    hpux71:/home/oracle-> sqlplus "/ as sysdba"


    SQL> select dbtimezone from dual;

    DBTIME
    ------
    -05:00

    SQL> select sessiontimezone from dual;

    SESSIONTIMEZONE
    --------------------------------------
    -05:00

    SQL> select systimestamp from dual;

    SYSTIMESTAMP
    -------------------------------------
    24-APR-07 11.22.57.787696 AM -05:00

    SQL> select current_timestamp from dual;

    CURRENT_TIMESTAMP
    --------------------------------------
    24-APR-07 11.23.05.459675 AM -05:00

    SQL> select to_char(sysdate, 'hh:mi:ss') from dual;

    TO_CHAR(
    --------
    11:23:12

    Everything looks okay, huh? It all matches up. Well....

    Connecting via SQL*Plus:

    hpux71:/home/oracle-> sqlplus cms@cmsdb

    SQL> select dbtimezone from dual;

    DBTIME
    ------
    -05:00

    SQL> select sessiontimezone from dual;

    SESSIONTIMEZONE
    --------------------------------------
    -05:00

    SQL> select systimestamp from dual;

    SYSTIMESTAMP
    -------------------------------------
    24-APR-07 12.26.34.373524 PM -04:00 <-----<<< HUH?

    SQL> select current_timestamp from dual;

    CURRENT_TIMESTAMP
    --------------------------------------
    24-APR-07 11.26.39.214008 AM -05:00

    SQL> select to_char(sysdate, 'hh:mi:ss') from dual;

    TO_CHAR(
    --------
    12:26:45 <----<<< HUH?


    I checked with the UNIX guys and the OS TZ parameter is set to "CST6CDT" and the OS seems to check out. The Oracle session has the correctly sourced TZ parameter as CST6CDT as well.

    What am I missing? What else do I need to check? I'm outta answers.

    Thank you for any info you may have...

    Jody.

  2. #2
    Join Date
    Nov 2005
    Posts
    32
    Looks like you might have encountered this scenario:

    (1) TZ parameter was changed on the unix box
    (2) Database was restarted
    (3) Listener is running on non-default port (not 1521) and local_listener is not set within the init or pfile for automatic listener registration and the listener was NOT restarted after the database restart.

    If the listener was not restarted after the OS parameter change and if the listener uses static service registration then connections through the listener might not see the OS parameter change. Oracle's PMON process reads the environment variable during database startup and also registers the service name (....and uses the OS parameter during registration) if dynamic registration is enabled.

    Fix in your case might be as simple as restarting the listener......

    Good luck......

    http://www.dbaxchange.com

  3. #3
    Join Date
    Jun 2006
    Posts
    3
    Well....

    The listener is running on a non-default port.

    TZ parameter has not been changed on the server - CST6CDT.

    Database has been restarted a lot for backups (weekly) and this problem has been consistent.

    Listener gets restarted with the DB and registers just fine. From the init.ora:

    cms1.local_listener='LISTENER_hpux71'

    Anywhere else to look?? I'm tellin' you, this is a puzzler!!

    Jody.

  4. #4
    Join Date
    Nov 2005
    Posts
    32
    In that case I would check the database startup scripts to see if the TZ parameter's being explicitly set incorrectly or if the .profile for the machine login being used for starting up the database has the TZ parameter value set incorrectly. BEQ protocol gets influenced by the current shell setting of TZ whereas using the tns layer, the TZ settings set during the database / listener startups influences the date / time settings.

    For example on a solaris10 box :

    $ echo $TZ
    US/Eastern
    $ date
    Wed Apr 25 16:41:15 EDT 2007
    BEQ protocol
    $ sqlplus ods

    SQL> alter session set nls_date_format='DD-MON-YYYY:HH24:MI:SS';

    Session altered.

    SQL> select sysdate from dual;

    SYSDATE
    --------------------------
    25-APR-2007:16:44:06

    SQL> exit

    $ export TZ=US/Central

    $ echo $TZ
    US/Central

    BEQ protocol
    $ sqlplus ods

    SQL> alter session set nls_date_format='DD-MON-YYYY:HH24:MI:SS';

    Session altered.

    SQL> select sysdate from dual;

    SYSDATE
    --------------------------
    25-APR-2007:15:45:27 <= Took the TZ change

    SQL> exit

    $ echo $TZ
    US/Central

    -- Connection through the listener

    $ sqlplus ods@devdb

    SQL> alter session set nls_date_format='DD-MON-YYYY:HH24:MI:SS';

    Session altered.

    SQL> select sysdate from dual;

    SYSDATE
    --------------------------
    25-APR-2007:16:49:54 <= Took the TZ value during DB / listener startup

    Good luck.....

    http://www.dbaxchange.com

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Is it possible that to be a problem with the day light savings? I see exactly one hour of difference so I tought, if the client machine uses DLS but the server does not....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width