12c 修改操作系統(tǒng)時(shí)間會(huì)數(shù)據(jù)庫有哪些影響？

有些時(shí)候可能會(huì)因?yàn)榘惭b數(shù)據(jù)庫不夠規(guī)范，導(dǎo)致系統(tǒng)時(shí)間設(shè)置的不正確，我們需要調(diào)整系統(tǒng)時(shí)間。但是調(diào)整系統(tǒng)時(shí)間到底會(huì)對(duì)數(shù)據(jù)庫造成什么影響呢。我們來做幾個(gè)小實(shí)驗(yàn)。

1.測試帶有日期字段的表插入操作。

--session 1

SQL> create table t1 (id int,dt date);

Table created.

SQL> insert into t1 values(1,sysdate);

1 row created.

SQL> select * from t1;

        ID DT
---------- -----------------------
         1 09-DEC-2017 10:21:16

--session 2
[root@roidb01 ~]# date
Sat Dec  9 10:21:40 CST 2017
[root@roidb01 ~]# date -s 20:00:00
Sat Dec  9 20:00:00 CST 2017
[root@roidb01 ~]# date
Sat Dec  9 20:00:02 CST 2017

--返回session1
SQL> insert into t1 values(1,sysdate);

1 row created.

SQL> select * from t1;

        ID DT
---------- -----------------------
         1 09-DEC-2017 10:21:16
         1 09-DEC-2017 20:00:09

SQL> 
SQL> insert into t1 values(1,sysdate);

1 row created.

SQL> select * from t1;

        ID DT
---------- -----------------------
         1 09-DEC-2017 10:21:16
         1 09-DEC-2017 20:00:09
         1 09-DEC-2017 19:00:10

這里某些表日期字段使用的sysdate時(shí)間的話，時(shí)間會(huì)根據(jù)操作系統(tǒng)時(shí)間來插入數(shù)據(jù)，日期肯定是會(huì)變化的，這樣很可能造成業(yè)務(wù)邏輯出現(xiàn)問題，需要注意。

2.測試AWR報(bào)告

--修改時(shí)間為前一天
[root@roidb01 ~]# date -s 01:00:00
Sat Dec  9 01:00:00 CST 2017
[root@roidb01 ~]# 
[root@roidb01 ~]# date -s "2017-12-08 11:00:00"
Fri Dec  8 11:00:00 CST 2017
[root@roidb01 ~]# date
Fri Dec  8 11:00:01 CST 2017

--AWR報(bào)告異常
SQL> @?/rdbms/admin/awrrpt.sql

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 1489897299 ORCL                1 orcl

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type: 

Type Specified:                  html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 1489897299        1 ORCL         orcl         roidb01

Using 1489897299 for database Id
Using          1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.

Enter value for num_days: 2

Listing the last 2 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
orcl         ORCL                 1 09 Dec 2017 10:14      1
                                  2 09 Dec 2017 10:14      1
                                  3 09 Dec 2017 10:14      1
                                  4 09 Dec 2017 10:14      1
                                  5 09 Dec 2017 19:00      1
                                  6 09 Dec 2017 19:01      1
                                  7 09 Dec 2017 01:02      1
                                  8 09 Dec 2017 01:02      1
                                  9 08 Dec 2017 11:00      1  --變成前一天，時(shí)間亂了
                                 10 08 Dec 2017 11:00      1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 

這里也說明了，修改時(shí)間對(duì)AWR會(huì)有一定影響，盡量時(shí)間往后調(diào)整。

3.對(duì)數(shù)據(jù)庫集群影響
對(duì)于Oracle RAC數(shù)據(jù)庫需要各實(shí)例時(shí)間一致，時(shí)間不一致會(huì)造成節(jié)點(diǎn)down機(jī)。

小結(jié)：在安裝數(shù)據(jù)庫的時(shí)候一定要調(diào)整好數(shù)據(jù)庫的時(shí)間和時(shí)區(qū)，這個(gè)也是必須檢查的項(xiàng)目，要按照規(guī)范安裝數(shù)據(jù)庫，避免不必要的麻煩。如果還有其他影響，大家可以留言，一起交流。