oracle时区问题

oracle时区问题

问题背景：

客户反馈数据库时区不一致，数据库本地登入和主机登入查看的时间与客户端远程连接查看的时间不一致

初步判断为时区问题，大佬检查后发现数据库集群env配置与主机配置不一致

问题处理：

首先进行检查数据库和主机时区的对应情况

数据库查看日期

select sysdate from dual;

主机查看日志

date -R

数据库日期与主机时间一致

查看主机环境变量

cat /etc/sysconfig/clock

发现主机是TZ=Asia/Shanghai  

grid 查看集群环境变量

cat $ORACLE_HOME/crs/install/s_crsconfig_$hostname_env.txt

发现集群环境变量为TZ=GMT+08:00

两者不一致，需要对集群的两个节点进行修改并重启，客户端连接后以此变量为标准，与数据库时区产生差别，导致出现问题

进行修改两节点参数，轮循停节点，杀掉节点会话使其漂移并关闭监听，停机停集群

!ps -ef | grep LOCAL=NO | grep -v grep | awk? '{print $2}' |xargs kill -9?

#{kill -9 `ps -ef | grep LOCAL=NO | grep -v grep |cut -c9-22`}

crsctl stat res -t

srvctl stop listener -n node1

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

alter system checkpoint;

create pfile='/home/oracle/bakpfile0207.ora' from spfile;

alter database backup controlfile to trace as '/home/oracle/conctlbak0207.ctl';

shutdown immediate

crsctl stat res -t

sudo -l

重启后客户检查时区，问题解决

环境对应情况

### This file can be used to modify the NLS_LANG environment variable, which determines the charset to be used for messages.

### For example, a new charset can be configured by setting NLS_LANG=JAPANESE_JAPAN.UTF8  

### Do not modify this file except to change NLS_LANG, or under the direction of Oracle Support Services

TZ=Asia/Shanghai

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

TNS_ADMIN=

ORACLE_BASE=

=================================================================================

## Path:                System/Environment/Clock

## Description:         Information about your timezone and time

## Type:                string(-u,--utc,--localtime)

## ServiceRestart:      boot.clock

## Command:             /sbin/refresh_initrd

#

# Set to "-u" if your system clock is set to UTC, and to "--localtime"

# if your clock runs that way.

#

HWCLOCK="-u"

## Type:                yesno

## Default:             yes

## Description: Write back system time to the hardware clock

#

# Is set to "yes" write back the system time to the hardware

# clock at reboot or shutdown. Usefull if hardware clock is

# much more inaccurate than system clock.  Set to "no" if

# system time does it wrong due e.g. missed timer interrupts.

# If set to "no" the hardware clock adjust feature is also

# skipped because it is rather useless without writing back

# the system time to the hardware clock.

#

SYSTOHC="yes"

## Type:                string(Europe/Berlin,Europe/London,Europe/Paris)

## ServiceRestart:      boot.clock

#

# Timezone (e.g. CET)

# (this will set /usr/lib/zoneinfo/localtime)

#

TIMEZONE="Asia/Shanghai"

DEFAULT_TIMEZONE="US/Eastern"