修改主機時區對Oracle的影響分析

談談主機修改時區對Oracle的影響

    Oracle在啟動例項時，由pmon程式將讀取作業系統相關環境（如系統時區）進記憶體區域，並在該例項的生命週期內一直儲存。
    監聽啟動時，首先會讀取作業系統系統時區，但如果資料庫監聽採用動態註冊，那pmon程式會將資料庫系統時區資訊動態註冊至監聽。
    所以，當作業系統時區發生更改，如果透過監聽連線的業務，會讀取監聽中的時區，所以仍將採用更改前的時區，這將導致資料庫時間和作業系統時間不一致，此時進行資料插入，資料將採用監聽的時區進行資料插入。
    所以為了使得資料庫時間和作業系統時間一致性，Oracle官方推薦當作業系統更改時區之後，將資料庫進行重啟，由pmon程式將修改後的新時區，重新註冊至監聽。但是如果資料庫是7*24小時環境，重啟資料庫需要付出相當大的代價。那能不能不重啟資料庫就能達到資料庫時間和作業系統時間一致的狀態呢？
    透過以上的討論，我們可以得出以下結論：
    1、如果業務程式不透過監聽連線至資料庫，那麼資料庫和主機時間應當一致。
    2、如果監聽是靜態註冊，Pmon程式不動態註冊相關資訊至監聽器裡，那麼將監聽瞬間重啟之後，監聽將讀取修改後的時區，這樣透過監聽連線的業務程式，也將讀取修改後的時區。
    但是問題又來了，如果資料庫監聽埠處於非預設埠（即1521埠），那麼只要不設定local_listener，那將不會進行動態註冊。那如果是預設監聽埠呢？
    這裡有個小技巧只要將local_listener設為其他埠即可
    alter system set local_listener="(address=(protocol=tcp)(host=172.16.4.163)(port=1531))";
    如果作業系統時區不修改，我們可以透過修改監聽的時區，達到修改時區的目的，即只要修改listsner.ora，增加ENVS='TZ=CST6CDT。
    SID_LIST_LISTENER =
      (SID_LIST =
          (SID_DESC =
          (GLOBAL_DBNAME = mcstar)
          (ORACLE_HOME = /ora10g/oracle/product/10.2.0/db_1)
          (SID_NAME = mcstar)
          #(ENVS='TZ=CST6CDT')
        )
      )
        
    LISTENER =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.4.163)(PORT = 1521))
        )
        )
    以下為一個客戶修改了作業系統時區，導致資料庫時間和作業系統時間不一致的解決過程：
    可以看到在主機上連線資料庫，即不透過監聽連線資料庫時，系統時間和資料庫時間處於一致狀態
    SQL> conn agent/***
    Connected.
    SQL> select to_char(sysdate,'yyyy-mm-dd hh24 i:ss') from dual;

    TO_CHAR(SYSDATE,'YY
    ——————-
    2011-05-20 15:23:50
    但透過監聽連線，再顯示資料庫時間，發現相差14個小時
    $ sqlplus "agent/***@zjdw"

    SQL*Plus: Release 9.2.0.8.0 – Production on Fri May 20 15:25:34 2011

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

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.8.0 – Production

    SQL> select to_char(sysdate,'yyyy-mm-dd hh24 i:ss') from dual;

    TO_CHAR(SYSDATE,'YY
    ——————-
    2011-05-20 01:25:39
    檢視監聽狀態，可以發現監聽已經執行178天，且預設監聽埠號為1521，但並未出現動態註冊
    $ lsnrctl status

    LSNRCTL for HPUX: Version 9.2.0.8.0 – Production on 20-MAY-2011 15:26:00

    Copyright (c) 1991, 2006, Oracle Corporation.  All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.202.3.8)(PORT=1521)))
    STATUS of the LISTENER
    ————————
    Alias                     LISTENER
    Version                   TNSLSNR for HPUX: Version 9.2.0.8.0 – Production
    Start Date                22-NOV-2010 12:42:41
    Uptime                    178 days 11 hr. 43 min. 18 sec
    Trace Level               off
    Security                  OFF
    SNMP                      OFF
    Listener Parameter File   /oradata/ora9208/product/db_1/network/admin/listener.ora
    Listener Log File         /oradata/ora9208/product/db_1/network/log/listener.log
    Listening Endpoints Summary…
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=***.***.***.***)(PORT=1521)))
    Services Summary…
    Service "zjdw" has 1 instance(s).
      Instance "zjdw", status UNKNOWN, has 1 handler(s) for this service…
    The command completed successfully

    進一步檢視Oracle引數，local_listener引數並未見異常，於是再次檢查alert日誌，可以看到listener.ora地址配置錯誤，導致pmon註冊監聽出錯，於是也就好理解了為什麼監聽長期處於靜態註冊
    Mon Nov 22 12:40:57 2010
    Starting ORACLE instance (normal)
    LICENSE_MAX_SESSION = 0
    LICENSE_SESSIONS_WARNING = 0
    SCN scheme 3
    Using log_archive_dest parameter default value
    LICENSE_MAX_USERS = 0
    SYS auditing is disabled
    Starting up ORACLE RDBMS Version: 9.2.0.8.0.
    System parameters with non-default values:
      processes                = 1000
      timed_statistics         = TRUE
      shared_pool_size         = 1056964608
      sga_max_size             = 8398007384
    。。。。。。
    PMON started with pid=2, OS id=14867
    Mon Nov 22 12:41:01 2010
    ORA-00130: invalid listener address '(ADDRESS=(PROTOCOL=TCP)(HOST=geosoft-)(PORT=1521))'

    既然監聽處於靜態註冊狀態，pmon不會將保留在記憶體區域裡的老時區動態註冊至監聽中，所以只要將監聽重啟，讓監聽重新獲取新主機時區即可。
    可以看到重啟監聽之後，再次透過監聽連線資料庫，資料時間已經恢復正常。
    $ sqlplus "agent/***@zjdw"

    SQL*Plus: Release 9.2.0.8.0 – Production on Fri May 20 15:27:26 2011

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

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.8.0 – 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.8.0 – Production

    SQL> select to_char(sysdate,'yyyy-mm-dd hh24 i:ss') from dual;

    TO_CHAR(SYSDATE,'YY
    ——————-
    2011-05-20 15:27:32

    這時又引申出另外一個問題，作業系統時區修改之後，應不應該修改Oracle時區？
    顯而易見，如果資料庫列儲存方式並沒有採用timezone儲存（最常用的有TIMESTAMP，TIMESTAMP WITH TIME ZONE，IMESTAMP WITH LOCAL TIME ZONE），作業系統時區修改顯然不用修改資料庫時區。
    資料庫的時區，可以用檢視database_properties檢視獲得，可以看到目前資料庫時區為+0:00，即預設和主機時區一致。
    SQL>  select * from database_properties where property_name='DBTIMEZONE';

    PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
    —————————— ——————– ——————————
    DBTIMEZONE                     +0:00                DB time zone
    可以用以下命令修改資料庫時區，重啟資料庫才能生效
    ALTER DATABASE SET TIME_ZONE = '+10:00';
    需要注意的是，修改資料庫時區僅適用於資料庫沒有TIMESTAMP WITH LOCAL TIME ZONE欄位時才生效。且不會修改已儲存在資料庫中的時區列，僅對未來資料生效。

    Oracle除了資料庫時區，還提供了會話級時區，檢視會話級時區時將忽略資料庫級時區，預設保持和操作時區一致。
    SQL> SELECT SESSIONTIMEZONE FROM dual;

    SESSIONTIMEZONE
    —————————————————————————
    +08:00
    可以採用如下命令，修改之後會話級別實時生效，
    SQL> alter session set time_zone='+10:00';

    Session altered.

    SQL>  SELECT SESSIONTIMEZONE FROM dual;

    SESSIONTIMEZONE
    —————————————————————————
    +10:00

    如前所述Timestamp With local Time Zone 在客戶端取資料的時候，會自動轉為客戶端的時區時間，所以修改會話級時區將影響Timestamp With local Time Zone的取值。
    SQL> alter session set time_zone='+10:00';

    Session altered.

    SQL> select TIMESTP_LTZ  from zhoul.TIMESTAMP_TEST;

    TIMESTP_LTZ
    —————————————————————————
    23-MAY-11 04.47.18.000 PM

    SQL>  alter session set time_zone='+8:00';

    Session altered.

    SQL> select TIMESTP_LTZ  from zhoul.TIMESTAMP_TEST;

    TIMESTP_LTZ
    —————————————————————————
    23-MAY-11 02.47.18.000 PM

    當資料庫已有TIMESTAMP WITH LOCAL TIME ZONE欄位時，將出現以下錯誤。
    SQL> ALTER DATABASE SET TIME_ZONE = '+10:00';
    ALTER DATABASE SET TIME_ZONE = '+10:00'
    *
    ERROR at line 1:
    ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns
    當出現這種錯誤時，可以透過以下指令碼檢視哪些列是TIMESTAMP WITH LOCAL TIME ZONE
    SQL> select u.name || '.' || o.name || '.' || c.name TSLTZcolumn
      2    from sys.obj$ o, sys.col$ c, sys.user$ u
      3   where c.type# = 231
      4     and o.obj# = c.obj#
      5     and u.user# = o.owner#;
    TSLTZCOLUMN
    ——————————————————————————–
    ZHOUL.BIN$o+3YOK3rqpLgQBCsowRAmg==$0.TS_LTZ

    那麼TIMESTAMP WITH LOCAL TIME ZONE是什麼玩意呢？
    Timestamp With local Time Zone型別和Timestamp with time zone類似。內部程式碼是231。和TimpStamp With Time Zone不同的是，這種資料型別會自動把時間轉換成伺服器的時區時間進行儲存。在客戶端取資料的時候，會自動轉為客戶端的時區時間。
    TIMESTAMP WITH TIME ZONE型別資料會儲存客戶端的時區資訊，如果指定時區資訊（如timestamp '2010-02-01 09:00:00 +09:00'），則按指定時區儲存，如果不指定時區（如timestamp '2010-02-01 09:00:00'）預設採用會話時區儲存。
    TIMESTAMP WITH LOCAL TIME ZONE型別資料不會儲存客戶單的時區資訊，它根據資料庫時區對客戶端發來的時間進行轉換，基於統一的資料庫時區儲存時間資訊，如果使用者沒有指定時區資訊同TIMESTAMP WITH TIME ZONE一樣預設採用會話時區。當使用者檢視該型別資料時，伺服器根據會話所屬時區對儲存的時間資料進行轉換，不同時區的會話將返回不同的時間資料。