在RAC环境中,Cluster各节点之间需要保持时间同步,所以在安装过程中要注意同步时钟。
我们可以通过date -s来修改系统时间
[root@smsdbrac1 root]# date
Sat Dec 29 09:25:25 CST 2007
[root@smsdbrac1 root]# date -s "Sat Dec 29 09:37:50 CST 2007"
Sat Dec 29 09:37:50 CST 2007
但是注意,系统的硬件时钟可能于此不同:
[root@smsdbrac2 ~]# date
Sat Dec 29 09:38:39 CST 2007
[root@smsdbrac2 ~]# clock --show
Sat 29 Dec 2007 09:27:43 AM CST -0.021796 seconds
注意如果系统时钟和硬件时钟不一致,那么在重新启动过程中,系统时钟会从硬件时钟获得时间信息。
所以需要同步硬件时钟:
[root@smsdbrac1 root]# hwclock --show
Sat 29 Dec 2007 09:32:41 AM CST -0.019265 seconds
[root@smsdbrac1 root]# date
Sat Dec 29 09:44:13 CST 2007
[root@smsdbrac1 root]# hwclock --systohc
[root@smsdbrac1 root]# date
Sat Dec 29 09:44:25 CST 2007
[root@smsdbrac1 root]# hwclock --show
Sat 29 Dec 2007 09:44:29 AM CST -0.015972 seconds
命令hwclock --systohc指定将系统时间同步至硬件时钟。
始终同步之后,RAC各节点之间应该从ntp服务器同步时间,或者将某个节点作为主节点,其他节点依据其时间进行同步。
最简单的是建立一个crontab任务:
[root@smsdbrac2 ~]# crontab -l
0 1,4,8,13,17,20 * * * /etc/rsyncdate.sh
[root@smsdbrac2 ~]# cat /etc/rsyncdate.sh
rdate -s 192.168.200.11
如果存在防火墙,你还需要在节点间开通rdate所需要的37端口:
-A RH-Firewall-1-INPUT -m state --state NEW -m udp -p udp --dport 37 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 37 -j ACCEPT
oracle dbtimezone与os时区不一致的解决办法
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+00:00
//查看当前时间和时区
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
12-4月 -11 02.39.49.421000 下午 +08:00
//我明明是在东8区,怎么数据库时区显示为0呢?
SQL> alter database set time_zone='+8:00';
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 tsltz_column
2 from sys.obj$ o,sys.col$ c,sys.user$ u
3 where c.type#=231 and
4 o.obj#=c.obj# and
5 u.user#=o.owner#;
TSLTZ_COLUMN
--------------------------------------------------------------------------------
OE.ORDERS.ORDER_DATE
//我们找到了,是oe用户下orders表下的列order_date。
SQL> desc oe.orders;
Name Type Nullable Default Comments
------------ --------------------------------- -------- ------- -----------------------------------------------------------
ORDER_ID NUMBER(12) PRIMARY KEY column.
ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE column, NOT NULL constraint.
ORDER_MODE VARCHAR2(8) Y CHECK constraint.
CUSTOMER_ID NUMBER(6)
ORDER_STATUS NUMBER(2) Y 0: Not fully entered, 1: Entered, 2: Canceled - bad credit,-
3: Canceled - by customer, 4: Shipped - whole order, -
5: Shipped - replacement items, 6: Shipped - backlog on items, -
7: Shipped - special delivery, 8: Shipped - billed, 9: Shipped - payment plan,-
10: Shipped - paid
ORDER_TOTAL NUMBER(8,2) Y CHECK constraint.
SALES_REP_ID NUMBER(6) Y References hr.employees.employee_id.
PROMOTION_ID NUMBER(6) Y Sales promotion ID. Used in SH schema
//将其删除
SQL> alter table oe.orders drop column order_date;
Table altered
//这样我们才可以修改时区
SQL> alter database set time_zone='+8:00';
Database altered
//关闭数据库
//SHUTDOWN is not an SQL command, it is an SQL*Plus command.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
//启动数据库
SQL> startup;
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 7877988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
//查看时区
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+08:00
我们可以通过date -s来修改系统时间
[root@smsdbrac1 root]# date
Sat Dec 29 09:25:25 CST 2007
[root@smsdbrac1 root]# date -s "Sat Dec 29 09:37:50 CST 2007"
Sat Dec 29 09:37:50 CST 2007
但是注意,系统的硬件时钟可能于此不同:
[root@smsdbrac2 ~]# date
Sat Dec 29 09:38:39 CST 2007
[root@smsdbrac2 ~]# clock --show
Sat 29 Dec 2007 09:27:43 AM CST -0.021796 seconds
注意如果系统时钟和硬件时钟不一致,那么在重新启动过程中,系统时钟会从硬件时钟获得时间信息。
所以需要同步硬件时钟:
[root@smsdbrac1 root]# hwclock --show
Sat 29 Dec 2007 09:32:41 AM CST -0.019265 seconds
[root@smsdbrac1 root]# date
Sat Dec 29 09:44:13 CST 2007
[root@smsdbrac1 root]# hwclock --systohc
[root@smsdbrac1 root]# date
Sat Dec 29 09:44:25 CST 2007
[root@smsdbrac1 root]# hwclock --show
Sat 29 Dec 2007 09:44:29 AM CST -0.015972 seconds
命令hwclock --systohc指定将系统时间同步至硬件时钟。
始终同步之后,RAC各节点之间应该从ntp服务器同步时间,或者将某个节点作为主节点,其他节点依据其时间进行同步。
最简单的是建立一个crontab任务:
[root@smsdbrac2 ~]# crontab -l
0 1,4,8,13,17,20 * * * /etc/rsyncdate.sh
[root@smsdbrac2 ~]# cat /etc/rsyncdate.sh
rdate -s 192.168.200.11
如果存在防火墙,你还需要在节点间开通rdate所需要的37端口:
-A RH-Firewall-1-INPUT -m state --state NEW -m udp -p udp --dport 37 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 37 -j ACCEPT
oracle dbtimezone与os时区不一致的解决办法
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+00:00
//查看当前时间和时区
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
12-4月 -11 02.39.49.421000 下午 +08:00
//我明明是在东8区,怎么数据库时区显示为0呢?
SQL> alter database set time_zone='+8:00';
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 tsltz_column
2 from sys.obj$ o,sys.col$ c,sys.user$ u
3 where c.type#=231 and
4 o.obj#=c.obj# and
5 u.user#=o.owner#;
TSLTZ_COLUMN
--------------------------------------------------------------------------------
OE.ORDERS.ORDER_DATE
//我们找到了,是oe用户下orders表下的列order_date。
SQL> desc oe.orders;
Name Type Nullable Default Comments
------------ --------------------------------- -------- ------- -----------------------------------------------------------
ORDER_ID NUMBER(12) PRIMARY KEY column.
ORDER_DATE TIMESTAMP(6) WITH LOCAL TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE column, NOT NULL constraint.
ORDER_MODE VARCHAR2(8) Y CHECK constraint.
CUSTOMER_ID NUMBER(6)
ORDER_STATUS NUMBER(2) Y 0: Not fully entered, 1: Entered, 2: Canceled - bad credit,-
3: Canceled - by customer, 4: Shipped - whole order, -
5: Shipped - replacement items, 6: Shipped - backlog on items, -
7: Shipped - special delivery, 8: Shipped - billed, 9: Shipped - payment plan,-
10: Shipped - paid
ORDER_TOTAL NUMBER(8,2) Y CHECK constraint.
SALES_REP_ID NUMBER(6) Y References hr.employees.employee_id.
PROMOTION_ID NUMBER(6) Y Sales promotion ID. Used in SH schema
//将其删除
SQL> alter table oe.orders drop column order_date;
Table altered
//这样我们才可以修改时区
SQL> alter database set time_zone='+8:00';
Database altered
//关闭数据库
//SHUTDOWN is not an SQL command, it is an SQL*Plus command.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
//启动数据库
SQL> startup;
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 7877988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.
//查看时区
SQL> select dbtimezone from dual;
DBTIMEZONE
----------
+08:00
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31136666/viewspace-2121166/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31136666/viewspace-2121166/