月份：2020年1月

Oracle数据块格式

Posted on by YongJing(敬勇)

数据块结构
Oracle 数据块有三部分:
Cache layer
Transaction layer
Data layer

Oracle Data Block的结构简图如下，其中从Data header到Row Data部分合称Data Layer:

———————
– Cache Layer –
———————
– Transaction Layer –
———————
– Data Header –
———————
– Table Directory –
———————
– Row Directory –
———————
– Free Space –
———————
– Row Data –
———————
– Tailchk –
———————
下面将使用bbed工具来显示相关结构信息

BBED> map
 File: /u01/app/oracle/oradata/shardcat/users01.dbf (7)
 Block: 135                                   Dba:0x01c00087
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0

 struct ktbbh, 72 bytes                     @20

 struct kdbh, 14 bytes                      @100

 struct kdbt[1], 4 bytes                    @114

 sb2 kdbr[8]                                @118

 ub1 freespace[7983]                        @134

 ub1 rowdata[71]                            @8117

 ub4 tailchk                                @8188

数据块组件
Oracle数据块三层C结构，它被映射到SGA kcbh(内核缓存数据块头)中的数据块。Cache Layer包含关于块格式,类型(数据，索引，头等)信息和序列数据。

BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub2 wrp2_kcbh                            @2        0x0000
   ub4 rdba_kcbh                            @4        0x01c00087
   ub4 bas_kcbh                             @8        0x01286184
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x0137
   ub2 spare3_kcbh                          @18       0x0000

Cache Layer：Block的第一部分，长度为20字节，内部数据结构名为kcbh，包括
type_kcbh:块类型(table/index,rollback segment,temporary segment等)
frmt_kcbh:块格式(v6,v7,v8)
rdba_kcbh:块地址DBA
bas_kcbh/wrp_kcbh:SCN
seq_kcbh:块的序列号
flg_kcbh:块的标志

事务层存储了关于数据块的事务信息
Transaction Layer：内部结构名ktbbh。分成两部分，第一部分为固定长度，长度为24字节，包含事务相关的一些基本信息。第二部分为可变长度，包含itl，长度根据itl条目的个数变化，每个itl长度为24字节，内部结构名ktbbhitl

BBED> p ktbbh
struct ktbbh, 72 bytes                      @20
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24
      ub4 ktbbhsg1                          @24       0x0001af27
      ub4 ktbbhod1                          @24       0x0001af27
   struct ktbbhcsc, 8 bytes                 @28
      ub4 kscnbas                           @28       0x01286182
      ub2 kscnwrp                           @32       0x8000
      ub2 kscnwrp2                          @34       0x0000
   sb2 ktbbhict                             @36       7938
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x01c00080
   struct ktbbhitl[0], 24 bytes             @44
      struct ktbitxid, 8 bytes              @44
         ub2 kxidusn                        @44       0x0006
         ub2 kxidslt                        @46       0x0012
         ub4 kxidsqn                        @48       0x000038f6
      struct ktbituba, 8 bytes              @52
         ub4 kubadba                        @52       0x010002d9
         ub2 kubaseq                        @56       0x10c9
         ub1 kubarec                        @58       0x09
      ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
      union _ktbitun, 2 bytes               @62
         sb2 _ktbitfsc                      @62      -32768
         ub2 _ktbitwrp                      @62       0x8000
      ub4 ktbitbas                          @64       0x01228dbb
   struct ktbbhitl[1], 24 bytes             @68
      struct ktbitxid, 8 bytes              @68
         ub2 kxidusn                        @68       0x0008
         ub2 kxidslt                        @70       0x001b
         ub4 kxidsqn                        @72       0x00003a9a
      struct ktbituba, 8 bytes              @76
         ub4 kubadba                        @76       0x010002b5
         ub2 kubaseq                        @80       0x114f
         ub1 kubarec                        @82       0x20
      ub2 ktbitflg                          @84       0x2001 (KTBFUPB)
      union _ktbitun, 2 bytes               @86
         sb2 _ktbitfsc                      @86       10
         ub2 _ktbitwrp                      @86       0x000a
      ub4 ktbitbas                          @88       0x01286184

这种结构出现在数据库每个数据块的开头部分。它甚至出现在不由redo改变的排序块中。它也会出现在数据文件头块和控制文件头块的开头部分。缓存层提供了对坏数据的规模。它也用来确保正确的数据块被读取并且数据块没有破裂或损坏。所谓破裂的数据块就是只有一部分被写入磁盘，数据块的一部分保留了之前的版本。

Data Layer：包括Data Header，Table Directory，Row Directory，Free Space和Row Data。

Data Header：长度14字节，内部数据结构名kdbh

BBED> p kdbh
struct kdbh, 14 bytes                       @100
   ub1 kdbhflag                             @100      0x00 (NONE)
   sb1 kdbhntab                             @101      1
   sb2 kdbhnrow                             @102      8
   sb2 kdbhfrre                             @104     -1
   sb2 kdbhfsbo                             @106      34
   sb2 kdbhfseo                             @108      8017
   sb2 kdbhavsp                             @110      7977
   sb2 kdbhtosp                             @112      7989

其中kdbhnrow是存储在数据块中的记录数为8,而表中确实有8条记录。(从ROWID可以判断出来)

SQL> select dbms_rowid.rowid_block_number(rowid),t1.t_id,t1.t_name from jy.t1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)       T_ID T_NAME
------------------------------------ ---------- ----------------------------------------
                                 134          9 YYL
                                 135          1 A
                                 135          2 B
                                 135          3 C
                                 135          4 D
                                 135          5 E
                                 135          6 F
                                 135          7 JYHY
                                 135          8 JYYYL

9 rows selected.

Table Directory: 一般table只有一个条目，cluster则有一个或多个条目。每个条目长4字节，内部数据结构名kdbt

BBED> p kdbt
struct kdbt[0], 4 bytes                     @114
   sb2 kdbtoffs                             @114      0
   sb2 kdbtnrow                             @116      8

Row Directory：数目由块中数据的行数决定，每个条目长2字节，内部数据结构名kdbr

BBED> p kdbr
sb2 kdbr[0]                                 @118      8080
sb2 kdbr[1]                                 @120      8072
sb2 kdbr[2]                                 @122      8064
sb2 kdbr[3]                                 @124      8056
sb2 kdbr[4]                                 @126      8048
sb2 kdbr[5]                                 @128      8040
sb2 kdbr[6]                                 @130      8029
sb2 kdbr[7]                                 @132      8017

查看表中的记录数据,一个重复计数也可以被指定用来重复执行examine命令来检查后续的行记录。下面的例子显示了先使用print命令来设置最后一行记录的偏移量，然后检查后面的8行记录的操作.

BBED> p *kdbr[7]
rowdata[0]
----------
ub1 rowdata[0]                              @8117     0x2c

BBED> x /8rnc
rowdata[0]                                  @8117
----------
flag@8117: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8118: 0x02
cols@8119:    2

col    0[2] @8120: 8
col    1[5] @8123: JYYYL

rowdata[12]                                 @8129
-----------
flag@8129: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8130: 0x00
cols@8131:    2

col    0[2] @8132: 7
col    1[4] @8135: JYHY

rowdata[23]                                 @8140
-----------
flag@8140: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8141: 0x00
cols@8142:    2

col    0[2] @8143: 6
col    1[1] @8146: F

rowdata[31]                                 @8148
-----------
flag@8148: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8149: 0x00
cols@8150:    2

col    0[2] @8151: 5
col    1[1] @8154: E

rowdata[39]                                 @8156
-----------
flag@8156: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8157: 0x00
cols@8158:    2

col    0[2] @8159: 4
col    1[1] @8162: D

rowdata[47]                                 @8164
-----------
flag@8164: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8165: 0x00
cols@8166:    2

col    0[2] @8167: 3
col    1[1] @8170: C

rowdata[55]                                 @8172
-----------
flag@8172: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8173: 0x00
cols@8174:    2

col    0[2] @8175: 2
col    1[1] @8178: B

rowdata[63]                                 @8180
-----------
flag@8180: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8181: 0x00
cols@8182:    2

col    0[2] @8183: 1
col    1[1] @8186: A

Free Space：表示数据块中可用空间，内部数据结构名freespace

Row Data：表示实际的数据，内部数据结构名rowdata

BBED> p rowdata[0]
ub1 rowdata[0]                              @8117     0x2c

BBED> d /v offset 8117
 File: /u01/app/oracle/oradata/shardcat/users01.dbf (7)
 Block: 135     Offsets: 8117 to 8191  Dba:0x01c00087
-------------------------------------------------------
 2c020202 c109054a 5959594c 2c000202 l ,......JYYYL,...
 c108044a 5948592c 000202c1 0701462c l ...JYHY,......F,
 000202c1 0601452c 000202c1 0501442c l ......E,......D,
 000202c1 0401432c 000202c1 0301422c l ......C,......B,
 000202c1 02014101 068461            l ......A...a

 <16 bytes per line>

Tailchk:保存在块结尾用于校验的数据，长度4个字节，内部结构名tailchk。所有Oracle块的最后四个字节都是tail check(结尾检查)。
对于一个Oracle 8以上版本的数据块的tail它是由SCN base的低位两字节的内容，数据块的类型与SCN序列号组成的。例如，如果SCN base为 0x01286184，数据块类型为0x06，SCN序列号为0x01，那么tail check将是0x61840601

BBED> p tailchk
ub4 tailchk                                 @8188     0x61840601

BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub2 wrp2_kcbh                            @2        0x0000
   ub4 rdba_kcbh                            @4        0x01c00087
   ub4 bas_kcbh                             @8        0x01286184
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x0137
   ub2 spare3_kcbh                          @18       0x0000

虽然tail check的值通常是由这三个组件级成，Oracle会对最终的值作为一个值(4字节)以单无符号整数来存储。在小字节序编码(little-endian)的构架机器中，比如Intel，这个值将以低位字节优先的方式来存储。因此如果使用标准块编辑器或dump命令来检查数据块的tail check时，字节顺序可能不一样。一个tail check为0x61840601，在Intel机器上它将以”01068461″的形式存储在磁盘。

BBED> dump /v offset 8188
 File: /u01/app/oracle/oradata/shardcat/users01.dbf (7)
 Block: 135     Offsets: 8188 to 8191  Dba:0x01c00087
-------------------------------------------------------
 01068461                            l ...a

 <16 bytes per line>

IPv6 for MySQL

Posted on by YongJing(敬勇)

MySQL对IPv6的支持包括这些功能:
.MySQL服务器可以接受客户端通过IPv6进行TCP/IP连接。例如下面的命令是在本机通过IPv6来连接MySQL服务器:

[mysql@localhost ~]$ mysql -h ::1 -uusername -ppassword

要使用这个功能必须满足以下两个条件;
.服务器操作系统必须被配置支持IPv6
.MySQL服务器缺省配置必须除了支持IPv4的连接之外还要支持IPv6的连接。为了改变缺省配置可以在启动MySQL服务器时使用合适的–bind-address选项。

.MySQL账号名允许使用IPv6地址能让DBA为通过IPv6连接到服务器的客户端指定权限。例如:

mysql> create user 'v6root'@'::1' identified by "abcd";
mysql> grant select on mysql.* to 'v6root'@'::1';

.IPv6功能支持在字符串和内部格式IPv6地址格式之间进行转换并且检查其是否为有效的IPv6地址。例如
INET6_ATON()和INET6_NTOA()类似于INET_ATON()和INET_NTOA()，但除了能处理IPv4地址之外还能处理IPv6地址。

验证操作系统是否支持IPv6
在MySQL服务器可以接受IPv6的连接之前，服务器所在操作系统主机必须支持IPv6。例如在Linux主机上执行以下命令:

[root@localhost ~]# ping6 ::1
PING ::1(::1) 56 data bytes
64 bytes from ::1: icmp_seq=1 ttl=64 time=0.100 ms
64 bytes from ::1: icmp_seq=2 ttl=64 time=0.056 ms
64 bytes from ::1: icmp_seq=3 ttl=64 time=0.035 ms
64 bytes from ::1: icmp_seq=4 ttl=64 time=0.039 ms
....

为了对操作系统网络接口生成详细的描述，执行ifconfig -a并查看输出中的IPv6地址。

[root@localhost ~]# ifconfig -a
docker0: flags=4163  mtu 1500
        inet 172.17.0.1  netmask 255.255.0.0  broadcast 172.17.255.255
        inet6 fe80::42:15ff:fe1d:e05a  prefixlen 64  scopeid 0x20
        ether 02:42:15:1d:e0:5a  txqueuelen 0  (Ethernet)
        RX packets 15  bytes 3638 (3.5 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 33  bytes 3825 (3.7 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens160: flags=4163  mtu 1500
        inet 192.168.1.250  netmask 255.255.255.0  broadcast 192.168.1.255
        inet6 fe80::250:56ff:fea3:90e6  prefixlen 64  scopeid 0x20
        ether 00:50:56:a3:90:e6  txqueuelen 1000  (Ethernet)
        RX packets 5634313  bytes 364548809 (347.6 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 19054  bytes 2337541 (2.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

如果你的操作系统不支持IPv6，查看操作系统文档查找相关指令来启用IPv6。这可能只需要重新配置现有的网络接口来增加一个IPv6地址。或者需要执行更多的修改，比如使用IPv6选项来重建内核。

配置MySQL服务器允许IPv6连接
MySQL服务器对于TCP/IP连接会监听单个网络套接字。这种套接字被绑定到单个地址，但是可能一个地址映射到多个网络接口。为了指定一个地址，在服务器启动时使用–bind-address=addr选项，addr是IPv4或IPv6地址或主机名。(在MySQL 5.5.3之前不支持IPv6地址)。如果addr是主机名，服务器会解析这个主机名为一个IP地址并绑定到这个地址。

服务器对不同类型地址的处理如下:
.如果地址为*，如果服务器主机支持IPv6那么服务器可以接受来自所有服务器上所有IPv6和IPv4网络接口的TCP/IP连接，或者否则只会接受来自所有服务器上IPv4地址的TCP/IP连接。使用这个地址允许接受来自所有服务器网络接口的IPv4和IPv6连接。这个值是缺省值。

.如果地址为0.0.0.0，服务器只能接受来自所有服务器上IPv4网络接口的TCP/IP连接。

.如果地址为::，服务器接受来自所有服务器上的IPv4和IPv6网络接口的TCP/IP连接，这使用这个地址允许接受来自所有服务器网络接口的IPv4和IPv6连接。

.如果地址为IPv4映射地址，服务器接受来自这个地址的TCP/IP连接，这个地址是IPv4或IPv6格式。例如，如果服务器被绑定到::ffff:127.0.0.1，客户端可以使用–host=127.0.0.1或–host=::ffff:127.0.0.1来进行连接。

.如果地址为常规的IPv4或IPv6地址(比如127.0.0.1或::1)，服务器只能接受IPv4或IPv6地址的TCP/IP连接。

如果倾向于绑定服务器到一个特定地址，确保mysql.user授权表包含一个有管理权限的用户可以使用这个地址进行连接。否则你将不能关闭服务器。例如，如果你倾向绑定服务器到*,你可以使用现有的所有账号来连接。但是如果你绑定服务器到::1，它只能接受这个地址的连接。在这种情况下，首先确保’root’@’::1’账号在mysql.user表中存在，所以可以仍然连接到服务器并关闭它。

使用IPv6本地主机地址连接MySQL服务器
下面介绍如何配置MySQL允许客户端通过IPv6(::1本地主机地址)连接到本地服务器。
1.在启动MySQL服务器时使用合适的–bind-address选项来允许服务器接受IPv6连接。例如，在my.cnf选项文件中设置bind-address并重启服务器

[mysqld]
......
bind-address=*
......

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl status mysqld
* mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since 二 2020-01-14 16:56:48 CST; 5s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 23257 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
    Tasks: 28
   Memory: 168.2M
   CGroup: /system.slice/mysqld.service
           |-23273 /bin/sh /mysqlsoft/mysql/bin/mysqld_safe --datadir=/mysqldata/mysql --pid-file=/mysqldata/mysql/mysqld.pid
           `-23591 /mysqlsoft/mysql/bin/mysqld --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql --plugin-dir=/mysqlsoft/mysql/lib/plugin --user=mysql --log-error=/mysqldata/mysql/mysql.err --pid-file=/mysqldata/mysql/mysqld.pid ...

1月 14 16:56:46 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
1月 14 16:56:48 localhost.localdomain mysqld[23257]: Starting MySQL.. SUCCESS!
1月 14 16:56:48 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.

另外，你也可以绑定服务器到::1，但这会使用服务器更加受限于TCP/IP连接。它只能接受单个IPv6地址的连接并且会拒绝IPv4的连接。

2.作为管理员，连接到服务器创建一个本地用户账号它将使用::1本地IPv6主机地址进行连接

mysql> create user 'ipv6root'@'::1' identified by "abcd";
Query OK, 0 rows affected (0.01 sec)


mysql> grant select on *.* to 'ipv6root'@'::1';
Query OK, 0 rows affected (0.02 sec)

3.调用mysql客户端工具使用新创建的账号连接服务器

[mysql@localhost mysql]$ mysql -h ::1 -uipv6root -pabcd mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | csroot        |
| %         | root          |
| ::1       | ipv6root      |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+
5 rows in set (0.00 sec)

4.执行status命令来显示连接信息

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using  EditLine wrapper

Connection id:          7
Current database:       mysql
Current user:           ipv6root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.26-log Source distribution
Protocol version:       10
Connection:             ::1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gb2312
Conn.  characterset:    gb2312
TCP port:               3306
Uptime:                 11 min 47 sec

Threads: 1  Questions: 263  Slow queries: 0  Opens: 336  Flush tables: 1  Open tables: 329  Queries per second avg: 0.371
--------------

mysql> SELECT CURRENT_USER(), @@bind_address;
+----------------+----------------+
| CURRENT_USER() | @@bind_address |
+----------------+----------------+
| ipv6root@::1   | *              |
+----------------+----------------+
1 row in set (0.00 sec)

使用IPv6非本地主机地址连接MySQL服务器
下面将介绍如何配置MySQL服务器允许远程主机通过IPv6来连接MySQL服务器。这类似于在本地主机使用IPv6连接MySQL服务器一样，但服务器与客户端在不同的主机上，每台主机有不同的IPv6地址。
服务器主机的IPv6地址为2018::1

[root@shard1 network-scripts]# ifconfig -a
ens160: flags=4163  mtu 1500
        inet6 2018::1  prefixlen 64  scopeid 0x0
        ether 00:50:56:a0:51:0c  txqueuelen 1000  (Ethernet)
        RX packets 27089788  bytes 2770300693 (2.5 GiB)
        RX errors 0  dropped 6  overruns 0  frame 0
        TX packets 6720001  bytes 532979878 (508.2 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

客户端主机的IPv6地址为2018::2

ens160: flags=4163  mtu 1500
        inet6 2018::2  prefixlen 64  scopeid 0x0
        ether 00:50:56:a0:4e:69  txqueuelen 1000  (Ethernet)
        RX packets 25337245  bytes 1913014541 (1.7 GiB)
        RX errors 0  dropped 127  overruns 0  frame 0
        TX packets 1536650  bytes 166036937 (158.3 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

具体操作如下:
1.在启动MySQL服务器时使用合适的–bind-address选项来允许服务器接受IPv6连接。例如，在my.cnf选项文件中设置bind-address并重启服务器

[mysqld]
......
bind-address=*
......

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl status mysqld
* mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
   Active: active (running) since 二 2020-01-14 16:56:48 CST; 5s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 23257 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
    Tasks: 28
   Memory: 168.2M
   CGroup: /system.slice/mysqld.service
           |-23273 /bin/sh /mysqlsoft/mysql/bin/mysqld_safe --datadir=/mysqldata/mysql --pid-file=/mysqldata/mysql/mysqld.pid
           `-23591 /mysqlsoft/mysql/bin/mysqld --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql --plugin-dir=/mysqlsoft/mysql/lib/plugin --user=mysql --log-error=/mysqldata/mysql/mysql.err --pid-file=/mysqldata/mysql/mysqld.pid ...

1月 14 16:56:46 localhost.localdomain systemd[1]: Starting LSB: start and stop MySQL...
1月 14 16:56:48 localhost.localdomain mysqld[23257]: Starting MySQL.. SUCCESS!
1月 14 16:56:48 localhost.localdomain systemd[1]: Started LSB: start and stop MySQL.

另外，可以绑定服务器到2018:db1:0:f01::1，但这会使用服务器更加受限于TCP/IP连接。它只能接受单个IPv6地址的连接并且会拒绝IPv4的连接。

2.在服务器中（2018::1)创建一个账号将从客户端主机(2018::2)上连接服务器

mysql> create user 'csroot'@'%' identified by "123456";
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'csroot'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

3.在客户端主机上(2018::2)，调用mysql客户端使用创建的新账号来连接服务器(我这时使用MariaDB作为客户端工具)

-bash-4.2$ mysql -h 2018::1 -ucsroot -p123456 mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [mysql]>

4.执行status命令来显示连接信息

MySQL [mysql]> status
--------------
mysql  Ver 15.1 Distrib 10.0.38-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          6
Current database:       mysql
Current user:           csroot@2018::2
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MySQL
Server version:         5.6.23 MySQL Community Server (GPL)
Protocol version:       10
Connection:             2018::1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    gb2312
Conn.  characterset:    gb2312
TCP port:               3306
Uptime:                 34 days 50 min 24 sec

Threads: 2  Questions: 175  Slow queries: 0  Opens: 87  Flush tables: 1  Open tables: 80  Queries per second avg: 0.000
--------------

MySQL [mysql]> select current_user(),@@bind_address;
+----------------+----------------+
| current_user() | @@bind_address |
+----------------+----------------+
| csroot@%       | *              |
+----------------+----------------+
1 row in set (0.00 sec)

MySQL Strict SQL MODE

Posted on by YongJing(敬勇)

严格SQL模式控制MySQL如何处理数据改变语句(insert或update)中的无效或缺失值。一个值可能由于各种原因而无效。例如，它对于列来说有错误的数据类型，或者超过了列的范围。当新记录被插入而对于非NULL且没有显式在定义时指定DEFAULT子句的列没有包含值就会出现缺失值的情况。(对于一个NULL列，如果缺失值就会插入NULL值)严格SQL模式也会影响DDL语句比如create table。

如果严格SQL模式没有生效，MySQL对于无效或者缺失值会插入调整值并生成一个警告。在严格SQL模式中，可以通过使用insert ignore或udpate ignore来产生这种行为。

对于select这样不改变数据的语句，在严格SQL模式中无效值会生成一个警告而不是错误。

严格SQL模式对于试图创建一个键值而超过列的最大键值长度时会产生一个错误。当严格SQL模式没有启用时，会产生一个警告并且截断键值的长度使其满足最大键值长度。

严格SQL模式不影响是否对外键约束执行检查。foreign_key_checks可以被使用。

如果STRICT_ALL_TABLES或STRICT_TRANS_TABLES被启用严格SQL模式就会生效，但这些模式的影响会有不同:
.对于事务表来说，当STRICT_ALL_TABLES或STRICT_TRANS_TABLES被启用后当在数据出现无效或丢失值就会出现错误。语句就会被终止与回滚。

.对于非事务表，如果在插入或更新语句中第一行记录出现坏值这些模式的行为是一样的:语句被终止并且表仍然保持不变。如果语句插入或修改多行记录并且在第二行或之后的行记录中出现坏值，那么结果依赖于严格SQL模式是否被启用。
-对于STRICT_ALL_TABLES，MySQL会返回一个错误并忽略剩余的行记录。然而，因为早些的行记录已经被插入或被更新，会导致部分更新。为了避免这个问题，使用单行语句，就会终止而不会改变表数据。

-对于STRICT_TRANS_TABLES，MySQL会将一个无效的值转换成一个最接近的有效值并插入这个调整值。如果这个值将丢失，MySQL插入这个隐式缺省值。在这种情况下，MySQL生成一个敬告而不是一个错误并继续处理语句。

严格SQL模式对除零，零日期和日期中出现零的处理如下:
.严格SQL模式影响对除零的处理，它包括MOD(N,0):对于数据改变操作(insert,update):
-如果严格SQL模式没有被启用，除零会插入NULL并生成一个警告。
-如果严格SQL模式被启用，除非指定了ignore否则除零操作会生成一个错误。对于insert ignore和update ignore操作，除零操作会插入NULL并生成一个警告。

对于select，除零操作会返回NULL。启用严格SQL模式会导致一个警告。
.严格SQL模式会影响服务器是否允许’0000-00-00’为一个有效日期:
-如果严格SQL模式没有被启用，’0000-00-00’被允许并且插入操作不会产生警告。
-如果严格SQL模式被启用,’0000-00-00’不被允许并且插入操作会产生错误，除非你指定ignore。对于insert ignore或update ignore，’0000-00-00’被允许并且插入操作会产生警告。

.严格SQL模式影响服务器是否允许在日期中的年部分为非零但月和日部分允许为零(比如’2010-00-01’或’2010-01-00′):
-如果严格SQL模式没有被启用，有零的日期被允许并且插入操作不产生警告。
-如果严格SQL模式被启用,有零的日期不被允许并且插入操作产生错误，除非指定ignore。对于insert ignore或update ignore来说，有零的日期将以’0000-00-00’形式被插入并产生一个警告。

IGNORE关键字与严格SQL模式的对比
这里将介绍在语句执行时IGNORE关键字(它降级错误为警告)和严格SQL模式(它升级警告为错误)的对比。描述它们影响那些语句以及应用它们有那些错误。

IGNORE对语句执行的影响
MySQL中的一些语句支持可选的IGNORE关键字。此关键字将导致服务器降级某些类型的错误并生成警告。对于多行语句，IGNORE会导致语句跳到下一行，而不是中止。

例如，如果表t2有一个主键列i,试图在多行记录中插入相同的i值正常来说会产生一个重复键错误:

mysql> insert into t2(i) values(1),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

当使用IGNORE关键字时，包含重复键值的记录仍然不会被插入，但会使用警告来代替错误:

mysql> insert ignore into t2(i) values(1),(1);
Query OK, 1 row affected, 1 warning (0.15 sec)
Records: 2  Duplicates: 1  Warnings: 1

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
| Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+---------+------+---------------------------------------+
2 rows in set (0.00 sec)

以下语句支持IGNORE关键字:
.create table… select:ignore不能应用到语句的create table或select部分但对于由select语句所提供记录来执行插入语句可以应用。对于唯一键值重复的记录会被丢弃。

.delete:ignore会导致MySQL在处理删除记录时忽略错误。

.insert:使用ignore，对于唯一键值重复的记录会被丢弃。对于重复键值的行记录会导致数据转换为最接近的有效值被插入。

对于分区表当没有匹配指定值的分区被找到时，ignore会导致包含那些不匹配值的记录的插入操作失败。
.load data,load xml:使用ignore，对于唯一键值重复的记录会被丢弃。

.update:使用ignore，对于在唯一键值出现重复键值冲突的记录不会被更新。被更新的记录可能导致数据转换为最接近的有效值被插入。

ignore关键字应用到以下错误:
ER_BAD_NULL_ERROR
ER_DUP_ENTRY
ER_DUP_ENTRY_WITH_KEY_NAME
ER_DUP_KEY
ER_NO_PARTITION_FOR_GIVEN_VALUE
ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT
ER_NO_REFERENCED_ROW_2
ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET
ER_ROW_IS_REFERENCED_2
ER_SUBQUERY_NO_1_ROW
ER_VIEW_CHECK_FAILED

严格SQL模式对语句执行的影响
MySQL服务器可以以不同的SQL模式进行操作并且可以应用这些不同模式到不同的客房端，这依赖于sql_mode系统变量。在严格SQL模式中，服务器会将特定的警告升级成错误。

例如，在非严格SQL模式中，向整数类型列插入字符串’abc’的结果是将这个字符串值转换为0并生成一个警告:

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t2(i) values('abc');
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'abc' for column 'i' at row 1 |
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

在严格SQL模式下，无效值会被拒绝并生成错误:

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t2(i) values('abc');
ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'i' at row 1

在某些条件下，某些值可能超出范围或将无效行插入或从表中删除，严格SQL模式适用于以下语句:
alter table
create table
create table … select
delete(单表和多表)
insert
load data
load xml
select sleep()
update(单表和多表)
在存储程序中，如果程序是在严格模式生效时定义的，则刚才列出的类型的各个语句将以严格SQL模式执行。

严格SQL模式应用于以下错误，代表输入值可能无效或丢失这类错误。如果对于列值使用了错误数据类型或超过了值的
范围那么值就是无效的。如果被插入的新行不包含NOT NULL列值但除了在列定义时显式指定了DEFAULT子句的那么就
是值丢失。
ER_BAD_NULL_ERROR
ER_CUT_VALUE_GROUP_CONCAT
ER_DATA_TOO_LONG
ER_DATETIME_FUNCTION_OVERFLOW
ER_DIVISION_BY_ZERO
ER_INVALID_ARGUMENT_FOR_LOGARITHM
ER_NO_DEFAULT_FOR_FIELD
ER_NO_DEFAULT_FOR_VIEW_FIELD
ER_TOO_LONG_KEY
ER_TRUNCATED_WRONG_VALUE
ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
ER_WARN_DATA_OUT_OF_RANGE
ER_WARN_NULL_TO_NOTNULL
ER_WARN_TOO_FEW_RECORDS
ER_WRONG_ARGUMENTS
ER_WRONG_VALUE_FOR_TYPE
WARN_DATA_TRUNCATED

MySQL SQL模式

Posted on by YongJing(敬勇)

MySQL服务器可以以不同的SQL模式来进行操作，并且依赖于sql_mode系统变量的值对不同的客户端可以应用这些不同的SQL模式。DBA可以设置全局SQL模式来匹配服务器操作要求，并且每种应用程序可以设置它的会话SQL模式来满足它的要求。

SQL模式会影响MySQL支持的SQL语法和数据验证检查。这可以在不同环境中让MySQL与其它数据库一起使用变得更容易。

当使用InnoDB表时，可以考虑使用innodb_strict_mode系统变量，它可以对InnoDB表启用额外的错误检查。

设置SQL模式
在MySQL 5.7中缺省的SQL模式包含:ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,和 NO_ENGINE_SUBSTITUTION。ONLY_FULL_GROUP_BY和STRICT_TRANS_TABLES是在MySQL 5.7.5中加入的。NO_AUTO_CREATE_USER是在MySQL 5.7.7中加入的。ERROR_FOR_DIVISION_BY_ZERO，NO_ZERO_IN_DATE和NO_ZERO_DATE是在MySQL 5.7.8中加入的。

为了在服务器启动时设置SQL模式，可以在命令行中使用–sql-mode=”modes”选项或在选项文件比如Unix中的my.cnf或Windows上的my.ini文件中使用sql-mode=”modes”选项。modes是用逗号分的不同模式列表。为了显式的清除SQL模式，可以在命令行中使用–sql-mode=””选项将SQL模式设置为空字符串，或者在选项文件中使用sql-mode=””。

MySQL安装程序可以在安装过程中会配置SQL模式。例如，mysql_install_db将在基本的安装目录中创建一个命名为my.cnf的缺省选项文件。这个文件包含设置SQL模式的记录。

如果SQL模式不同于缺省SQL模式或你所期待的SQL模式，可以检查服务器在启动时所读取的选项文件。

为了在运行时改变SQL模式，可以使用set语句来设置全局或会话级的sql_mode系统变量:
set global sql_mode=’modes’;
set session sql_mode=’modes’;

设置global变量需要有super权限并且影响所有连接的客户端操作。设置session变量只影响当前客户端。每个客户端可以在任何时间改变它会话的sql_mode值来达到改变SQL模式的目的。

为了判断当前全局或session级的sql_mode值，执行以下命令:

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

注意在创建分区表并插入数据之后改变服务器的SQL模式会对表的行为造成重大改变并且可能导致丢失或损坏数据。所以强烈建议在创建用户定义的分区表之后不要修改SQL模式。

当复制分区表时，在主从服务器之间不同的SQL模式也会导致一些问题。所以最好的结果就是在主从服务器上使用相同的SQL模式。

最重要的SQL模式
MySQL中最重要的sql_mode值可能是这些:
.ANSI
这种SQL模式改变语法和行为使其更接近标准SQL。它是一种特定的组合模式列表。

.STRICT_TRANS_TABLES
如果一个值不能以指定的方式插入到一个事务表，终止这个语句。对于非事务表，如果这个值在一个单行记录语句或在多行记录语句第一个出现时终止语句。从MySQL 5.7.5开始缺省的SQL模式包括STRICT_TRANS_TABLES。

.TRADITIONAL
使用MySQL行为像一个传统的SQL数据库系统。简单来说这种模式对于将一个不正确值插入到一个列中时抛出一个错误来代替一个警告。它是特定组合模式中的一种。insert或update一旦发现错误会立即终止。如果您正在使用非事务性存储引擎，这可能不是您想要的，因为在错误之前所做的数据更改可能无法回滚，从而导致“部分完成”的更新。

当提到”strict mode”严格模式时，它意味着是STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的一种或两种都被启用。

完整的SQL模式列表
下面是所有支持的SQL模式列表:
.ALLOW_INVALID_DATES
不对日期执行完全检查。只对月份的范围从1到12和日期的范围从1到31执行检查。这对于Web应用程序非常方便，您可以在三个不同的字段中获得年、月和日，并且希望准确地存储用户插入的内容(不执行日期校验)。这种SQL模式应用于date和datetime列。它不应用于timestamp列，因为它总是请求一个合法的日期值。

这种SQL模式要求月分与每天的取值是合法值，并且范围分别不能超过1到12和1到31。当严格模式被禁用时，一个无效的日期值比如’2014-04-31’将被转换成’0000-00-00’并且生成一个警告。当使用严格模式时，一个无效的日期值会生成一个错误。为了允许这样的日期值，启用ALLOW_INVALID_DATES模式。

.ANSI_QUOTES
将”作为标识引用字符(像`引用字符)而不是字符串引用字符。当使用这种SQL模式时仍然可以使用`作为引用标识符，当ANSI_QUOTES被使用时，不能使用双引号来引用文本字符串，因为它被解析作标识符了。

.ERROR_FOR_DIVISION_BY_ZERO
ERROR_FOR_DIVISION_BY_ZERO模式影响对除以零的处理，这包含MOD(N,0)。对于数据修改操作(insert,update)，它的影响也依赖于是否启用了严格SQL模式。
-如果这种模式没有被启用，除以零会插入NULL值并且没有警告。
-如果这种模式被启用，除以零会插入NULL值并生成警告。
-如果这种模式与严格SQL模式被启用，除以堆生成一个错误，除非IGNORE也被指定。对于insert ignore和update ignore，除以零会插入NULL值并生成警告。

对于select语句，除以零会返回NULL值。启用ERROR_FOR_DIVISION_BY_ZERO不管是否启用严格SQL模式会导致生成一个警告。

在MySQL 5.7.4中，ERROR_FOR_DIVISION_BY_ZERO被丢弃，在MySQL 5.7.4到5.7.7中当显式命名时ERROR_FOR_DIVISION_BY_ZERO不会做任何事。代替的是，它的影响被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中，当显式命名时ERROR_FOR_DIVISION_BY_ZERO会有影响并且没有包含在严格SQL模式中，就像MySQL5.7.4之前的版本一样。然而缺省情况下当严格模式启用下应该与它联合使用。如果ERROR_FOR_DIVISION_BY_ZERO被启用而没有启用严格模式或者当启用严格模式而没有启用ERROR_FOR_DIVISION_BY_ZERO时会出现这个警告。

因为ERROR_FOR_DIVISION_BY_ZERO被丢弃，它将在将来的版本中被删除并作为一个单独的模式名并且它的影响被包含在严格SQL模式。

.HIGH_NOT_PRECEDENCE
NOT操作的优先级就像NOT a between b and c会被解析成NOT (a between b and c)一样。在一些旧版本的MySQL中，表达式会被解析成(NOT a) between b and c。旧版本更高优先级行为可以通过启用HIGH_NOT_PRECEDENCE模式来获得。

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT NOT 1 BETWEEN -5 AND 5;
+------------------------+
| NOT 1 BETWEEN -5 AND 5 |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'HIGH_NOT_PRECEDENCE';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT NOT 1 BETWEEN -5 AND 5;
+------------------------+
| NOT 1 BETWEEN -5 AND 5 |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

.IGNORE_SPACE
允许在函数名与(符号之间存在空格。这会造成内置函数名被作为保留关键字对待。因此标识符与函数名相同必须有引号。例如，因为有一个count()函数，因此在下面的语句中使用count作为表名就会出错:

mysql> CREATE TABLE count (i INT);
Query OK, 0 rows affected (0.13 sec)

mysql> drop table count cascade;
Query OK, 0 rows affected (0.09 sec)

mysql> SET sql_mode = 'IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count (i INT)' at line 1

mysql> create table `count` (i INT);
Query OK, 0 rows affected (0.16 sec)

IGNORE_SPACE模式应用到内置函数，而不是用户定义的函数或存储过程。它总是允许在用户定义的函数或存储过程名后有空格而不管是否启用了IGNORE_SAPCE模式。

.NO_AUTO_CREATE_USER
除非指定了身份验证信息，否则将阻止GRANT语句自动创建新用户帐户。这个语句必须使用identified by来指定非空密码或使用identified with来使用一种验证插件。

最好使用create user来创建MySQL账号，然后使用Grant语句。NO_AUTO_CREATE_USER已经被丢弃并且缺省的SQL模式包含了NO_AUTO_CREATE_USER模式。将sql_mode修改为NO_AUTO_CREATE_USER模式会生成一个警告，除了指定sql_mode为DEFAULT.NO_AUTO_CREATE_USER将会在将来的版本中被删除，到时它的影响将会一直被启用。

之前，在NO_AUTO_CREATE_USER被丢弃之前，一个不启用它的原因是它是不安全的复制。现在它可以被启用并且使用create user if not exists,drop user if exists和alter user if exists而不是grant语句来执行安全复制管理。这些语句当从服务器相对于主服务器有不同的授权时可以启用安全复制。

.NO_AUTO_VALUE_ON_ZERO
NO_AUTO_VALUE_ON_ZERO影响对AUTO_INCREMENT列的处理。正常来说通过向访列插入NULL或0来为列生成下一个序列值。NO_AUTO_VALUE_ON_ZERO会抑制0的行为因此只有插入NULL时会生成下一个序列值。如果0已经被存储到表的AUTO_INCREMENT列中这个SQL模式可能是有用的。(存储0是不建议的)例如，如果使用mysqldump来dump表并且然后再加载它，当遇到值0时MySQL正常来说会生成一个新的序列值，因此表的内容不同于被dump的内容。在加载dump文件之前启用NO_AUTO_VALUE_ON_ZERO来解决这个问题。mysqldump现在在它的输出中自动包含一个语句来启用NO_AUTO_VALUE_ON_ZERO来避免这个问题。

.NO_BACKSLASH_ESCAPES
禁用在字符串中使用反斜杠字符(\)作为转义字符。启用此模式后，反斜杠将成为与其他字符一样的普通字符。

.NO_DIR_IN_CREATE
创建表时，忽略所有索引目录和数据目录指令。此选项在从复制服务器上非常有用。

.NO_ENGINE_SUBSTITUTION
当一个语句比如create table或alter table指定一个存储引擎已经被禁用或没有被编译时控制缺省存储引擎的自动替换。缺省的SQL模式中包含了NO_ENGINE_SUBSTITUTION。因为存储引擎可以在运行时被附加进来，不可以存储引擎也以相同方式被对待:
当NO_ENGINE_SUBSTITUTION被禁用，对于create table的缺省存储引擎被使用并且如果期待的存储引擎不可用会出现一个警告。对于alter table，会出现一个警告并且表不能被修改。
当NO_ENGINE_SUBSTITUTION被启用时，如果期待的存储引擎不可用会出现一个警告并且表不会被创建或被修改。

.NO_FIELD_OPTIONS
在show create table输出中不打印特定MySQL列选项。这种SQL模式被mysqldump以可移植模式来使用。

.NO_KEY_OPTIONS
在show create table输出中不打印特定MySQL索引选项。这种SQL模式被mysqldump以可移植模式来使用。

.NO_TABLE_OPTIONS
在show create table输出中不打印特定MySQL表选项(比如ENGINE)。这种SQL模式被mysqldump以可移植模式来使用。

.NO_UNSIGNED_SUBTRACTION
两个整数相减，这里一种类型UNSIGNED，缺省情况下生成一个没有符号的结果。如果结果出现负数将会出现错误:

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

如果NO_UNSIGNED_SUBTRACTION模式被启用，结果将是负数:

mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+
1 row in set (0.00 sec)

如果使用此类操作的结果更新无符号整数列，则将该结果裁剪为该列类型的最大值，如果启用no_unsigned_subtract，则将其裁剪为0。如果严格SQL模式被启用，则会出现错误并且列会保持不变。

当no_unsigned_subtraction被启用时，就算任何操作数据是无符号的相减的结果是有符号的。例如比较表t1中的c2列与表t2中的c2列:

mysql> SET sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);
Query OK, 0 rows affected (0.20 sec)


mysql> CREATE TABLE t1 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c2    | bigint(21) unsigned | NO   |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2 SELECT c1 - 1 AS c2 FROM test;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c2    | bigint(21) | NO   |     | 0       |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

这意味着bigint unsigned在所有上下文中不是100%可以使用。

.NO_ZERO_DATE
NO_ZERO_DATE模式影响服务器是否允许’0000-00-00’作为一种有效的日期。它的影响也依赖于是否启用了严格SQL模式。
-如果模式没有被启用，’0000-00-00’被允许并且插入不会产生警告。
-如果模式被启用，’0000-00-00’被允许并且插入会产生警告。
-如果模式和严格SQL模式被启用，’0000-00-00’不被允许并且插入会出现错误，除非指定IGNORE选项。对于insert ignore和update ignore来说，’0000-00-00’被允许并且插入会产生警告。

在MySQL 5.7.4中，NO_ZERO_DATE被丢弃。在MySQL 5.7.4到5.7.7中，NO_ZERO_DATE当显式指定时不会工作。代替地是它的影响已经被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中，当显式地指定NO_ZERO_DATE模式时它会工作并且它不是严格SQL模式的一部分就像MySQL5.7.4版本之前作用一样。然而，缺省情况下它应该与严格SQL模式联合使用。如果启用NO_ZERO_DATE而没有启用严格SQL模式就是出现警告或者反之亦然。

因为NO_ZERO_DATE已经被丢弃，因此它会在将来的版本中作为单独的模式名被删除并且它的影响会包含在严格SQL模式中。

.NO_ZERO_IN_DATE
NO_ZERO_IN_DATE模式影响服务器是否允许日期中的年部分为非0但月或日部分为0。(这种模式影响日期比如’2010-00-01’或’2010-01-00’，而不是’0000-00-00’。为了控制服务器是否允许’0000-00-00’，使用NO_ZERO_DATE模式)。NO_ZERO_IN_DATE模式的影响也依赖于是否启用了严格SQL模式。
-如果这种模式没有启用，有为0部分折日期被允许并且插入不产生警告。
-如果这种模式被启用，有为0部分的日期值将以’0000-00-00’格式被插入并且生成一个警告。
-如果这种模式与严格SQL模式被启用，有为0部分的日期不被允许并且插入会产生一个错误，除非你指定ignore。对于insert ignore和update ignore来说，有为0部分的日期将以’0000-00-00’格式被插入并且生成一个警告。

在MySQL 5.7.4中，NO_ZERO_IN_DATE被丢弃。在MySQL 5.7.4到5.7.7中，NO_ZERO_DATE当显式指定时不会工作。代替地是它的影响已经被包含在严格SQL模式中。在MySQL 5.7.8和以后的版本中，当显式地指定NO_ZERO_IN_DATE模式时它会工作并且它不是严格SQL模式的一部分就像MySQL5.7.4版本之前作用一样。然而，缺省情况下它应该与严格SQL模式联合使用。如果启用NO_ZERO_IN_DATE而没有启用严格SQL模式就是出现警告或者反之亦然。

因为NO_ZERO_IN_DATE已经被丢弃，因此它会在将来的版本中作为单独的模式名被删除并且它的影响会包含在严格SQL模式中。

.ONLY_FULL_GROUP_BY
拒绝那些select list、HAVING condition或ORDER BY list引用非聚合列的查询，这些列既不在GROUP BY子句中命名，也不依赖于GROUP BY列(由GROUP BY列唯一确定)。

从MySQL 5.7.5开始，缺省的SQL模式包含ONLY_FULL_GROUP_BY模式。(在5.7.5之前，MySQL没有检测到功能依赖并且缺省情况下ONLY_FULL_GROUP_BY模式没有启用。

MySQL扩展了标准SQL来允许在having子句中引用select列表中的别名表达式。在MySQL 5.7.5之前，启用ONLY_FULL_GROUP_BY模式会禁用这种扩展，因此要求having子句以非别名表达式来书写。从MySQL5.7.5开始，这种限制被取消了因此having子句可以引用别名而不用管ONLY_FULL_GROUP_BY模式是否启用了。

.PAD_CAHR_TO_FULL_LENGTH
默认情况下，在检索时从CHAR列值中裁剪尾随空格。如果PAD_CHAR_TO_FULL_LENGTH被启用，裁剪不会发生并且在检索CHAR列值时填充到它的完整长度。这种模式不应用于varchar列。检索时为其保留尾随空格。

mysql> CREATE TABLE t1 (c1 CHAR(10));
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO t1 (c1) VALUES('xy');
Query OK, 1 row affected (0.03 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------+-----------------+
| c1   | CHAR_LENGTH(c1) |
+------+-----------------+
| xy   |               2 |
+------+-----------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT c1, CHAR_LENGTH(c1) FROM t1;
+------------+-----------------+
| c1         | CHAR_LENGTH(c1) |
+------------+-----------------+
| xy         |              10 |
+------------+-----------------+
1 row in set (0.00 sec)

.PIPES_AS_CONCAT
将||作为字符串连接操作符(与concat()一样)而不是作为OR的同义词。

.REAL_AS_FLOAT
将REAL作为FLOAT的同义词。缺省情况下，MySQL将REAL作为DOUBLE的同义词。

.STRICT_ALL_TABLES
对所有的存储引擎启用严格SQL模式。无效的数据会被拒绝。从MySQL 5.7.4到5.7.7，STRICT_ALL_TABLES模式包含了ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式的影响。

.STRICT_TRANS_TABLES
对事务型存储引擎启用严格SQL模式并且在可能的情况下使用非事务型存储引擎。从MySQL 5.7.4到5.7.7，STRICT_TRANS_TABLES模式包含了ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE和NO_ZERO_IN_DATE模式的影响。

Oracle Linux 6.7中 Oracle 11.2.0.4 RAC集群CRS异常处理

Posted on by YongJing(敬勇)

最近一个月单位上的Oracle RAC集群CRS异常导致集群命令无法使用，执行crsctl stat res -t命令出现如下错误:

[grid@db1 ~]$ crsctl stat res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.

[grid@db2 ~]$ crsctl stat res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.

但是数据库可以正常访问，业务系统也运行正常

[root@db1 ~]# ps -ef | grep pmon
root       8024 204594  0 21:11 pts/0    00:00:00 grep pmon
grid      77120      1  0 Dec21 ?        00:04:21 asm_pmon_+ASM1
oracle    77790      1  0 Dec21 ?        00:05:18 ora_pmon_CAIWU1
oracle    77794      1  0 Dec21 ?        00:05:08 ora_pmon_dadb1
oracle    77848      1  0 Dec21 ?        00:05:39 ora_pmon_chdyl1
oracle    77910      1  0 Dec21 ?        00:07:47 ora_pmon_RLZY1

[root@db2 ~]# ps -ef | grep pmon
grid      27745      1  0 Dec21 ?        00:04:21 asm_pmon_+ASM2
oracle    28393      1  0 Dec21 ?        00:05:21 ora_pmon_dadb2
oracle    28569      1  0 Dec21 ?        00:04:58 ora_pmon_CAIWU2
oracle    28573      1  0 Dec21 ?        00:05:36 ora_pmon_chdyl2
oracle    28583      1  0 Dec21 ?        00:07:49 ora_pmon_RLZY2

查看ASM磁盘组的状态，发现OCR磁盘组确实offline了

[grid@db1 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576   3072000  2679522                0         2679522              0             N  ARCH/
MOUNTED  EXTERN  N         512   4096  1048576    204800   151138                0          151138              0             N  CWDATA/
MOUNTED  EXTERN  N         512   4096  1048576    512000   472546                0          472546              0             N  DADATA/
MOUNTED  EXTERN  N         512   4096  1048576   3072000   595334                0          595334              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576   1843200   609953                0          609953              0             N  SBDATA/

[grid@db2 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576   3072000  2679522                0         2679522              0             N  ARCH/
MOUNTED  EXTERN  N         512   4096  1048576    204800   151138                0          151138              0             N  CWDATA/
MOUNTED  EXTERN  N         512   4096  1048576    512000   472546                0          472546              0             N  DADATA/
MOUNTED  EXTERN  N         512   4096  1048576   3072000   595334                0          595334              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576   1843200   609953                0          609953              0             N  SBDATA/

手工将crsdg上线，命令能够执行成功，但执行crsctl stat res -t命令仍然报错。

[grid@db1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 30 21:15:33 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup ocr mount;

Diskgroup altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

[grid@db1 ~]$ crsctl stat res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.

[grid@db2 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 30 21:15:05 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup ocr mount;

Diskgroup altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

[grid@db2 ~]$ crsctl stat res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.

检查节点db1的alert_+ASM1.log有如下报错，说是不能访问OCR磁盘组的相关磁盘而强制dismount了OCR磁盘，但使用dd命令测试是能够访问的

Errors in file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_ora_77212.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
WARNING: requested mirror side 1 of virtual extent 0 logical extent 0 offset 102400 is not allocated; I/O request failed
WARNING: requested mirror side 2 of virtual extent 0 logical extent 1 offset 102400 is not allocated; I/O request failed
Errors in file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_ora_77212.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
ORA-15078: ASM diskgroup was forcibly dismounted
Sat Dec 28 05:30:48 2019
SQL> alter diskgroup OCR check /* proxy */ 
ORA-15032: not all alterations performed
ORA-15001: diskgroup "OCR" does not exist or is not mounted
ERROR: alter diskgroup OCR check /* proxy */
NOTE: client exited [77184]
Sat Dec 28 05:30:49 2019
NOTE: [crsd.bin@db1 (TNS V1-V3) 35285] opening OCR file
Sat Dec 28 05:30:51 2019
NOTE: [crsd.bin@db1 (TNS V1-V3) 35305] opening OCR file
Sat Dec 28 05:30:53 2019
NOTE: [crsd.bin@db1 (TNS V1-V3) 35322] opening OCR file
Sat Dec 28 05:30:55 2019
NOTE: [crsd.bin@db1 (TNS V1-V3) 35346] opening OCR file
Sat Dec 28 05:30:57 2019
NOTE: [crsd.bin@db1 (TNS V1-V3) 35363] opening OCR file
Sat Dec 28 05:31:00 2019
NOTE: [crsd.bin@db1 (TNS V1-V3) 35459] opening OCR file
Sat Dec 28 05:31:02 2019
NOTE: [crsd.bin@db1 (TNS V1-V3) 35481] opening OCR file
Sat Dec 28 05:31:04 2019
NOTE: [crsd.bin@db1 (TNS V1-V3) 35520] opening OCR file
Sat Dec 28 05:31:06 2019
NOTE: [crsd.bin@db1 (TNS V1-V3) 35539] opening OCR file
Sat Dec 28 05:31:08 2019
NOTE: [crsd.bin@db1 (TNS V1-V3) 35557] opening OCR file
Sat Dec 28 21:00:10 2019
Warning: VKTM detected a time drift.
Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.

检查错误日志

[root@db1 ~]# more /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_ora_77212.trc
Trace file /u01/app/grid/diag/asm/+asm/+ASM1/trace/+ASM1_ora_77212.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
ORACLE_HOME = /u01/app/11.2.0/grid
System name:    Linux
Node name:      db1
Release:        3.8.13-68.3.4.el6uek.x86_64
Version:        #2 SMP Tue Jul 14 15:03:36 PDT 2015
Machine:        x86_64
Instance name: +ASM1
Redo thread mounted by this instance: 0 
Oracle process number: 24
Unix process pid: 77212, image: oracle@db1 (TNS V1-V3)


*** 2019-12-28 05:30:44.894
*** SESSION ID:(2929.3) 2019-12-28 05:30:44.894
*** CLIENT ID:() 2019-12-28 05:30:44.894
*** SERVICE NAME:() 2019-12-28 05:30:44.894
*** MODULE NAME:(crsd.bin@db1 (TNS V1-V3)) 2019-12-28 05:30:44.894
*** ACTION NAME:() 2019-12-28 05:30:44.894
 
Received ORADEBUG command (#1) 'CLEANUP_KFK_FD' from process 'Unix process pid: 35253, image: '

*** 2019-12-28 05:30:44.895
Finished processing ORADEBUG command (#1) 'CLEANUP_KFK_FD'

*** 2019-12-28 05:30:48.235
WARNING:failed xlate 1 
ORA-15078: ASM diskgroup was forcibly dismounted
ksfdrfms:Mirror Read file=+OCR.255.4294967295 fob=0x9b00e5d8 bufp=0x7f5dd012ba00 blkno=25 nbytes=4096
WARNING:failed xlate 1 
WARNING: requested mirror side 1 of virtual extent 0 logical extent 0 offset 102400 is not allocated; I/O request failed
ksfdrfms:Read failed from mirror side=1 logical extent number=0 dskno=65535
WARNING:failed xlate 1 
WARNING: requested mirror side 2 of virtual extent 0 logical extent 1 offset 102400 is not allocated; I/O request failed
ksfdrfms:Read failed from mirror side=2 logical extent number=1 dskno=65535
ORA-15078: ASM diskgroup was forcibly dismounted
ORA-15078: ASM diskgroup was forcibly dismounted

检查节点db1的alertdb1.log有如下报错,也是说不能访问OCR磁盘组的相关磁盘

2019-12-28 05:30:48.468: 
[/u01/app/11.2.0/grid/bin/oraagent.bin(77466)]CRS-5822:Agent '/u01/app/11.2.0/grid/bin/oraagent_grid' disconnected from server. Details at (:CRSAGF00117:) {0:1:4} in /u01/app/11.2.0/grid/log/db1/agent/crsd/oraagent_grid/oraagent_grid.lo
g.
2019-12-28 05:30:48.468: 
[/u01/app/11.2.0/grid/bin/oraagent.bin(77684)]CRS-5822:Agent '/u01/app/11.2.0/grid/bin/oraagent_oracle' disconnected from server. Details at (:CRSAGF00117:) {0:7:332} in /u01/app/11.2.0/grid/log/db1/agent/crsd/oraagent_oracle/oraagent_o
racle.log.
2019-12-28 05:30:48.471: 
[/u01/app/11.2.0/grid/bin/orarootagent.bin(77482)]CRS-5822:Agent '/u01/app/11.2.0/grid/bin/orarootagent_root' disconnected from server. Details at (:CRSAGF00117:) {0:5:11497} in /u01/app/11.2.0/grid/log/db1/agent/crsd/orarootagent_root/
orarootagent_root.log.
2019-12-28 05:30:48.480: 
[ohasd(33022)]CRS-2765:Resource 'ora.crsd' has failed on server 'db1'.
2019-12-28 05:30:50.003: 
[crsd(35285)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:30:50.021: 
[crsd(35285)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:30:50.520: 
[ohasd(33022)]CRS-2765:Resource 'ora.crsd' has failed on server 'db1'.
2019-12-28 05:30:51.918: 
[crsd(35305)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:30:51.929: 
[crsd(35305)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:30:52.557: 
[ohasd(33022)]CRS-2765:Resource 'ora.crsd' has failed on server 'db1'.
2019-12-28 05:30:53.945: 
[crsd(35322)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:30:53.956: 
[crsd(35322)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:30:54.595: 
[ohasd(33022)]CRS-2765:Resource 'ora.crsd' has failed on server 'db1'.
2019-12-28 05:30:55.976: 
[crsd(35346)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:30:55.988: 
[crsd(35346)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:30:56.633: 
[ohasd(33022)]CRS-2765:Resource 'ora.crsd' has failed on server 'db1'.
2019-12-28 05:30:58.010: 
[crsd(35363)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:30:58.020: 
[crsd(35363)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:30:58.669: 
[ohasd(33022)]CRS-2765:Resource 'ora.crsd' has failed on server 'db1'.
2019-12-28 05:31:00.043: 
[crsd(35459)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:31:00.054: 
[crsd(35459)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:31:00.706: 
[ohasd(33022)]CRS-2765:Resource 'ora.crsd' has failed on server 'db1'.
2019-12-28 05:31:02.093: 
[crsd(35481)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:31:02.103: 
[crsd(35481)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:31:02.742: 
[ohasd(33022)]CRS-2765:Resource 'ora.crsd' has failed on server 'db1'.
2019-12-28 05:31:04.109: 
[crsd(35520)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:31:04.119: 
[crsd(35520)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:31:04.777: 
[ohasd(33022)]CRS-2765:Resource 'ora.crsd' has failed on server 'db1'.
2019-12-28 05:31:06.141: 
[crsd(35539)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:31:06.151: 
[crsd(35539)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:31:06.810: 
[ohasd(33022)]CRS-2765:Resource 'ora.crsd' has failed on server 'db1'.
2019-12-28 05:31:08.181: 
[crsd(35557)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:31:08.191: 
[crsd(35557)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db1/crsd/crsd.log.
2019-12-28 05:31:08.846: 
[ohasd(33022)]CRS-2765:Resource 'ora.crsd' has failed on server 'db1'.
2019-12-28 05:31:08.847: 
[ohasd(33022)]CRS-2771:Maximum restart attempts reached for resource 'ora.crsd'; will not restart.
2019-12-28 05:31:08.848: 
[ohasd(33022)]CRS-2769:Unable to failover resource 'ora.crsd'.

检查节点db1的oraagent_grid.log文件有如下报错，显示OCR磁盘组的状态被改变为offline了。

2019-12-28 05:30:16.531: [    AGFW][511039232]{1:30746:2} Agent received the message: AGENT_HB[Engine] ID 12293:113720
2019-12-28 05:30:37.808: [    AGFW][511039232]{1:30746:9373} Agent received the message: RESOURCE_STOP[ora.OCR.dg db1 1] ID 4099:113730
2019-12-28 05:30:37.808: [    AGFW][511039232]{1:30746:9373} Preparing STOP command for: ora.OCR.dg db1 1
2019-12-28 05:30:37.808: [    AGFW][511039232]{1:30746:9373} ora.OCR.dg db1 1 state changed from: ONLINE to: STOPPING
2019-12-28 05:30:37.809: [ora.OCR.dg][513140480]{1:30746:9373} [stop] (:CLSN00108:) clsn_agent::stop {
2019-12-28 05:30:37.810: [ora.OCR.dg][513140480]{1:30746:9373} [stop] DgpAgent::stop: enter { 
2019-12-28 05:30:37.810: [ora.OCR.dg][513140480]{1:30746:9373} [stop] getResAttrib: attrib name USR_ORA_OPI value true len 4
2019-12-28 05:30:37.810: [ora.OCR.dg][513140480]{1:30746:9373} [stop] Agent::flagUsrOraOpiIsSet(true) reason not dependency
2019-12-28 05:30:37.810: [ora.OCR.dg][513140480]{1:30746:9373} [stop] DgpAgent::stop: tha exit }
2019-12-28 05:30:37.810: [ora.OCR.dg][513140480]{1:30746:9373} [stop] DgpAgent::stopSingle status:2 }
2019-12-28 05:30:37.810: [ora.OCR.dg][513140480]{1:30746:9373} [stop] (:CLSN00108:) clsn_agent::stop }
2019-12-28 05:30:37.810: [    AGFW][513140480]{1:30746:9373} Command: stop for resource: ora.OCR.dg db1 1 completed with status: SUCCESS
2019-12-28 05:30:37.810: [ora.OCR.dg][513140480]{1:30746:9373} [check] CrsCmd::ClscrsCmdData::stat entity 1 statflag 33 useFilter 0
2019-12-28 05:30:37.811: [    AGFW][511039232]{1:30746:9373} Agent sending reply for: RESOURCE_STOP[ora.OCR.dg db1 1] ID 4099:113730
2019-12-28 05:30:37.838: [ora.OCR.dg][513140480]{1:30746:9373} [check] DgpAgent::runCheck: asm stat asmRet 0
2019-12-28 05:30:37.839: [ora.OCR.dg][513140480]{1:30746:9373} [check] DgpAgent::getConnxn connected
2019-12-28 05:30:37.844: [ora.OCR.dg][513140480]{1:30746:9373} [check] DgpAgent::queryDgStatus excp no data found
2019-12-28 05:30:37.844: [ora.OCR.dg][513140480]{1:30746:9373} [check] DgpAgent::queryDgStatus no data found in v$asm_diskgroup_stat
2019-12-28 05:30:37.844: [ora.OCR.dg][513140480]{1:30746:9373} [check] DgpAgent::queryDgStatus dgName OCR ret 1
2019-12-28 05:30:37.845: [    AGFW][511039232]{1:30746:9373} ora.OCR.dg db1 1 state changed from: STOPPING to: OFFLINE
2019-12-28 05:30:37.845: [    AGFW][511039232]{1:30746:9373} Agent sending last reply for: RESOURCE_STOP[ora.OCR.dg db1 1] ID 4099:113730
2019-12-28 05:30:43.889: [ora.asm][503641856]{1:30746:2} [check] CrsCmd::ClscrsCmdData::stat entity 1 statflag 33 useFilter 0
2019-12-28 05:30:43.920: [ora.asm][503641856]{1:30746:2} [check] AsmProxyAgent::check clsagfw_res_status 0
2019-12-28 05:30:48.465: [ CRSCOMM][521545472] IpcC: IPC client connection 6c to member 0 has been removed
2019-12-28 05:30:48.465: [CLSFRAME][521545472] Removing IPC Member:{Relative|Node:0|Process:0|Type:1}
2019-12-28 05:30:48.465: [CLSFRAME][521545472] Disconnected from CRSD:db1 process: {Relative|Node:0|Process:0|Type:1}
2019-12-28 05:30:48.474: [   AGENT][511039232]{0:1:4} {0:1:4} Created alert : (:CRSAGF00117:) :  Disconnected from server, Agent is shutting down.
2019-12-28 05:30:48.474: [    AGFW][511039232]{0:1:4} Agent is exiting with exit code: 1

检查节点db2的alert_+ASM2.log有如下报错，出现了类似的”Waited 15 secs for write IO to PST disk 0 in group 1″信息，这说明对OCR磁盘组执行写操作时超时了15秒.

Sat Dec 28 03:02:51 2019
WARNING: Waited 15 secs for write IO to PST disk 0 in group 1.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 1.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 3.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 3.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 5.
WARNING: Waited 15 secs for write IO to PST disk 1 in group 5.
WARNING: Waited 15 secs for write IO to PST disk 2 in group 5.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 5.
WARNING: Waited 15 secs for write IO to PST disk 1 in group 5.
WARNING: Waited 15 secs for write IO to PST disk 2 in group 5.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 6.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 6.
Sat Dec 28 03:02:51 2019
NOTE: process _b000_+asm2 (45488) initiating offline of disk 0.3916160907 (OCR1) with mask 0x7e in group 5
NOTE: process _b000_+asm2 (45488) initiating offline of disk 1.3916160906 (OCR2) with mask 0x7e in group 5
NOTE: process _b000_+asm2 (45488) initiating offline of disk 2.3916160905 (OCR3) with mask 0x7e in group 5
NOTE: checking PST: grp = 5
GMON checking disk modes for group 5 at 19 for pid 27, osid 45488
ERROR: no read quorum in group: required 2, found 0 disks
NOTE: checking PST for grp 5 done.
NOTE: initiating PST update: grp = 5, dsk = 0/0xe96bdf8b, mask = 0x6a, op = clear
NOTE: initiating PST update: grp = 5, dsk = 1/0xe96bdf8a, mask = 0x6a, op = clear
NOTE: initiating PST update: grp = 5, dsk = 2/0xe96bdf89, mask = 0x6a, op = clear
GMON updating disk modes for group 5 at 20 for pid 27, osid 45488
ERROR: no read quorum in group: required 2, found 0 disks
Sat Dec 28 03:02:51 2019
NOTE: cache dismounting (not clean) group 5/0x8F5B2F9F (OCR) 
NOTE: messaging CKPT to quiesce pins Unix process pid: 45490, image: oracle@db2 (B001)
Sat Dec 28 03:02:51 2019
NOTE: halting all I/Os to diskgroup 5 (OCR)
Sat Dec 28 03:02:52 2019
NOTE: LGWR doing non-clean dismount of group 5 (OCR)
NOTE: LGWR sync ABA=23.100 last written ABA 23.100
WARNING: Offline for disk OCR1 in mode 0x7f failed.
WARNING: Offline for disk OCR2 in mode 0x7f failed.
WARNING: Offline for disk OCR3 in mode 0x7f failed.
Sat Dec 28 03:02:52 2019
kjbdomdet send to inst 1
detach from dom 5, sending detach message to inst 1
Sat Dec 28 03:02:52 2019
List of instances:
 1 2
Dirty detach reconfiguration started (new ddet inc 1, cluster inc 36)
 Global Resource Directory partially frozen for dirty detach
* dirty detach - domain 5 invalid = TRUE 
 0 GCS resources traversed, 0 cancelled
Dirty Detach Reconfiguration complete
Sat Dec 28 03:02:52 2019
WARNING: dirty detached from domain 5
NOTE: cache dismounted group 5/0x8F5B2F9F (OCR) 
SQL> alter diskgroup OCR dismount force /* ASM SERVER:2405117855 */ 
Sat Dec 28 03:02:52 2019
NOTE: cache deleting context for group OCR 5/0x8f5b2f9f
GMON dismounting group 5 at 21 for pid 28, osid 45490
NOTE: Disk OCR1 in mode 0x7f marked for de-assignment
NOTE: Disk OCR2 in mode 0x7f marked for de-assignment
NOTE: Disk OCR3 in mode 0x7f marked for de-assignment
NOTE:Waiting for all pending writes to complete before de-registering: grpnum 5
Sat Dec 28 03:03:03 2019
WARNING: Waited 27 secs for write IO to PST disk 0 in group 1.
WARNING: Waited 27 secs for write IO to PST disk 0 in group 1.
WARNING: Waited 24 secs for write IO to PST disk 0 in group 2.
WARNING: Waited 24 secs for write IO to PST disk 0 in group 2.
WARNING: Waited 27 secs for write IO to PST disk 0 in group 3.
WARNING: Waited 27 secs for write IO to PST disk 0 in group 3.
WARNING: Waited 21 secs for write IO to PST disk 0 in group 4.
WARNING: Waited 21 secs for write IO to PST disk 0 in group 4.
WARNING: Waited 27 secs for write IO to PST disk 0 in group 6.
WARNING: Waited 27 secs for write IO to PST disk 0 in group 6.
Sat Dec 28 03:03:03 2019
ASM Health Checker found 1 new failures
Sat Dec 28 03:03:22 2019
SUCCESS: diskgroup OCR was dismounted
SUCCESS: alter diskgroup OCR dismount force /* ASM SERVER:2405117855 */
SUCCESS: ASM-initiated MANDATORY DISMOUNT of group OCR
Sat Dec 28 03:03:22 2019
NOTE: diskgroup resource ora.OCR.dg is offline
Sat Dec 28 03:03:22 2019
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
Sat Dec 28 05:30:34 2019
WARNING: Waited 15 secs for write IO to PST disk 0 in group 1.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 1.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 6.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 6.
Sat Dec 28 05:30:37 2019
 Received dirty detach msg from inst 1 for dom 5
Sat Dec 28 05:30:37 2019
List of instances:
 1 2
Dirty detach reconfiguration started (new ddet inc 2, cluster inc 36)
 Global Resource Directory partially frozen for dirty detach
* dirty detach - domain 5 invalid = TRUE 
 0 GCS resources traversed, 0 cancelled
freeing rdom 5
Dirty Detach Reconfiguration complete
Sat Dec 28 05:30:37 2019
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
WARNING: requested mirror side 1 of virtual extent 5 logical extent 0 offset 704512 is not allocated; I/O request failed
WARNING: requested mirror side 2 of virtual extent 5 logical extent 1 offset 704512 is not allocated; I/O request failed
Errors in file /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_27831.trc:
ORA-15078: ASM diskgroup was forcibly dismounted
ORA-15078: ASM diskgroup was forcibly dismounted
Sat Dec 28 05:30:37 2019
SQL> alter diskgroup OCR check /* proxy */ 
ORA-15032: not all alterations performed
ORA-15001: diskgroup "OCR" does not exist or is not mounted
ERROR: alter diskgroup OCR check /* proxy */
Sat Dec 28 05:30:44 2019
WARNING: Waited 20 secs for write IO to PST disk 0 in group 2.
WARNING: Waited 20 secs for write IO to PST disk 0 in group 2.
Sat Dec 28 05:30:48 2019
NOTE: client exited [27819]
Sat Dec 28 05:30:49 2019
NOTE: [crsd.bin@db2 (TNS V1-V3) 142641] opening OCR file
Sat Dec 28 05:30:51 2019
NOTE: [crsd.bin@db2 (TNS V1-V3) 142660] opening OCR file
Sat Dec 28 05:30:53 2019
NOTE: [crsd.bin@db2 (TNS V1-V3) 142678] opening OCR file
Sat Dec 28 05:30:55 2019
NOTE: [crsd.bin@db2 (TNS V1-V3) 142696] opening OCR file
Sat Dec 28 05:30:57 2019
NOTE: [crsd.bin@db2 (TNS V1-V3) 142723] opening OCR file
Sat Dec 28 05:30:59 2019
NOTE: [crsd.bin@db2 (TNS V1-V3) 142744] opening OCR file
Sat Dec 28 05:31:01 2019
NOTE: [crsd.bin@db2 (TNS V1-V3) 142773] opening OCR file
Sat Dec 28 05:31:03 2019
NOTE: [crsd.bin@db2 (TNS V1-V3) 142792] opening OCR file
Sat Dec 28 05:31:05 2019
NOTE: [crsd.bin@db2 (TNS V1-V3) 142806] opening OCR file
Sat Dec 28 05:31:07 2019
NOTE: [crsd.bin@db2 (TNS V1-V3) 142821] opening OCR file
Sat Dec 28 06:18:42 2019
WARNING: Waited 15 secs for write IO to PST disk 0 in group 1.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 1.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 2.
WARNING: Waited 15 secs for write IO to PST disk 0 in group 2.

检查节点db2的alertdb2.log有如下报错,也是说不能访问OCR磁盘组的相关磁盘，但使用dd命令测试是可以访问的。

2019-12-28 05:30:48.019: 
[/u01/app/11.2.0/grid/bin/oraagent.bin(28268)]CRS-5822:Agent '/u01/app/11.2.0/grid/bin/oraagent_oracle' disconnected from server. Details at (:CRSAGF00117:) {0:7:73} in /u01/app/11.2.0/grid/log/db2/agent/crsd/oraagent_oracle/oraagent_oracle.log.
2019-12-28 05:30:48.019: 
[/u01/app/11.2.0/grid/bin/scriptagent.bin(37953)]CRS-5822:Agent '/u01/app/11.2.0/grid/bin/scriptagent_grid' disconnected from server. Details at (:CRSAGF00117:) {0:9:8} in /u01/app/11.2.0/grid/log/db2/agent/crsd/scriptagent_grid/scriptagent_grid.log.
2019-12-28 05:30:48.020: 
[/u01/app/11.2.0/grid/bin/oraagent.bin(28009)]CRS-5822:Agent '/u01/app/11.2.0/grid/bin/oraagent_grid' disconnected from server. Details at (:CRSAGF00117:) {0:1:10} in /u01/app/11.2.0/grid/log/db2/agent/crsd/oraagent_grid/oraagent_grid.log.
2019-12-28 05:30:48.021: 
[/u01/app/11.2.0/grid/bin/orarootagent.bin(28025)]CRS-5822:Agent '/u01/app/11.2.0/grid/bin/orarootagent_root' disconnected from server. Details at (:CRSAGF00117:) {0:5:373} in /u01/app/11.2.0/grid/log/db2/agent/crsd/orarootagent_root/orarootagent_root.log.
2019-12-28 05:30:48.024: 
[ohasd(13034)]CRS-2765:Resource 'ora.crsd' has failed on server 'db2'.
2019-12-28 05:30:49.410: 
[crsd(142641)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:30:49.420: 
[crsd(142641)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:30:50.063: 
[ohasd(13034)]CRS-2765:Resource 'ora.crsd' has failed on server 'db2'.
2019-12-28 05:30:51.442: 
[crsd(142660)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:30:51.451: 
[crsd(142660)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:30:52.100: 
[ohasd(13034)]CRS-2765:Resource 'ora.crsd' has failed on server 'db2'.
2019-12-28 05:30:53.471: 
[crsd(142678)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:30:53.480: 
[crsd(142678)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:30:54.138: 
[ohasd(13034)]CRS-2765:Resource 'ora.crsd' has failed on server 'db2'.
2019-12-28 05:30:55.507: 
[crsd(142696)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:30:55.517: 
[crsd(142696)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:30:56.176: 
[ohasd(13034)]CRS-2765:Resource 'ora.crsd' has failed on server 'db2'.
2019-12-28 05:30:57.551: 
[crsd(142723)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:30:57.560: 
[crsd(142723)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:30:58.216: 
[ohasd(13034)]CRS-2765:Resource 'ora.crsd' has failed on server 'db2'.
2019-12-28 05:30:59.592: 
[crsd(142744)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:30:59.602: 
[crsd(142744)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:31:00.253: 
[ohasd(13034)]CRS-2765:Resource 'ora.crsd' has failed on server 'db2'.
2019-12-28 05:31:01.627: 
[crsd(142773)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:31:01.636: 
[crsd(142773)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:31:02.290: 
[ohasd(13034)]CRS-2765:Resource 'ora.crsd' has failed on server 'db2'.
2019-12-28 05:31:03.658: 
[crsd(142792)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:31:03.668: 
[crsd(142792)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:31:04.327: 
[ohasd(13034)]CRS-2765:Resource 'ora.crsd' has failed on server 'db2'.
2019-12-28 05:31:05.701: 
[crsd(142806)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:31:05.711: 
[crsd(142806)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:31:06.365: 
[ohasd(13034)]CRS-2765:Resource 'ora.crsd' has failed on server 'db2'.
2019-12-28 05:31:07.726: 
[crsd(142821)]CRS-1013:The OCR location in an ASM disk group is inaccessible. Details in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:31:07.735: 
[crsd(142821)]CRS-0804:Cluster Ready Service aborted due to Oracle Cluster Registry error [PROC-26: Error while accessing the physical storage
]. Details at (:CRSD00111:) in /u01/app/11.2.0/grid/log/db2/crsd/crsd.log.
2019-12-28 05:31:08.402: 
[ohasd(13034)]CRS-2765:Resource 'ora.crsd' has failed on server 'db2'.
2019-12-28 05:31:08.402: 
[ohasd(13034)]CRS-2771:Maximum restart attempts reached for resource 'ora.crsd'; will not restart.
2019-12-28 05:31:08.403: 
[ohasd(13034)]CRS-2769:Unable to failover resource 'ora.crsd'.

检查节点db2的oraagent_grid.log文件有如下报错:

2019-12-28 05:29:59.329: [    AGFW][3601811200]{2:6928:2} Agent received the message: AGENT_HB[Engine] ID 12293:273304
2019-12-28 05:30:17.162: [ora.LISTENER_SCAN2.lsnr][3592312576]{1:34166:403} [check] Utils:execCmd action = 3 flags = 38 ohome = (null) cmdname = lsnrctl. 
2019-12-28 05:30:17.267: [ora.LISTENER_SCAN2.lsnr][3592312576]{1:34166:403} [check] execCmd ret = 0
2019-12-28 05:30:17.267: [ora.LISTENER_SCAN2.lsnr][3592312576]{1:34166:403} [check] CrsCmd::ClscrsCmdData::stat entity 5 statflag 32 useFilter 1
2019-12-28 05:30:17.298: [ora.LISTENER_SCAN2.lsnr][3592312576]{1:34166:403} [check] ScanLsnrAgent::checkDependentVipResource: scanVipResource = ora.scan2.vip, statRet = 0
2019-12-28 05:30:17.881: [ora.LISTENER_SCAN3.lsnr][2950686464]{1:34166:403} [check] Utils:execCmd action = 3 flags = 38 ohome = (null) cmdname = lsnrctl. 
2019-12-28 05:30:17.986: [ora.LISTENER_SCAN3.lsnr][2950686464]{1:34166:403} [check] execCmd ret = 0
2019-12-28 05:30:17.987: [ora.LISTENER_SCAN3.lsnr][2950686464]{1:34166:403} [check] CrsCmd::ClscrsCmdData::stat entity 5 statflag 32 useFilter 1
2019-12-28 05:30:18.019: [ora.LISTENER_SCAN3.lsnr][2950686464]{1:34166:403} [check] ScanLsnrAgent::checkDependentVipResource: scanVipResource = ora.scan3.vip, statRet = 0
2019-12-28 05:30:27.292: [ora.asm][2950686464]{2:6928:2} [check] CrsCmd::ClscrsCmdData::stat entity 1 statflag 33 useFilter 0
2019-12-28 05:30:27.319: [ora.asm][2950686464]{2:6928:2} [check] AsmProxyAgent::check clsagfw_res_status 0
2019-12-28 05:30:34.522: [ora.ons][2950686464]{2:6928:2} [check] getOracleHomeAttrib: oracle_home = /u01/app/11.2.0/grid
2019-12-28 05:30:34.522: [ora.ons][2950686464]{2:6928:2} [check] Utils:execCmd action = 3 flags = 6 ohome = /u01/app/11.2.0/grid/opmn/ cmdname = onsctli. 
2019-12-28 05:30:34.627: [ora.ons][2950686464]{2:6928:2} [check] (:CLSN00010:)ons is running ...
2019-12-28 05:30:34.627: [ora.ons][2950686464]{2:6928:2} [check] (:CLSN00010:)
2019-12-28 05:30:34.628: [ora.ons][2950686464]{2:6928:2} [check] execCmd ret = 0
2019-12-28 05:30:37.858: [ USRTHRD][3575973632]{1:30748:9373} Processing the event CRS_RESOURCE_STATE_CHANGE
2019-12-28 05:30:38.652: [ora.LISTENER.lsnr][3594413824]{2:6928:2} [check] Utils:execCmd action = 3 flags = 38 ohome = (null) cmdname = lsnrctl. 
2019-12-28 05:30:38.757: [ora.LISTENER.lsnr][3594413824]{2:6928:2} [check] execCmd ret = 0
2019-12-28 05:30:48.017: [ CRSCOMM][3612317440] IpcC: IPC client connection 6c to member 0 has been removed
2019-12-28 05:30:48.017: [CLSFRAME][3612317440] Removing IPC Member:{Relative|Node:0|Process:0|Type:1}
2019-12-28 05:30:48.017: [CLSFRAME][3612317440] Disconnected from CRSD:db2 process: {Relative|Node:0|Process:0|Type:1}
2019-12-28 05:30:48.020: [   AGENT][3601811200]{0:1:10} {0:1:10} Created alert : (:CRSAGF00117:) :  Disconnected from server, Agent is shutting down.
2019-12-28 05:30:48.020: [    AGFW][3601811200]{0:1:10} Agent is exiting with exit code: 1

检查2个节点的/var/log/messages日志，发现2个节点均有多路径相关的错误信息，但相关的磁盘是用于备份的，不是用于生产数据库所使用的

Dec 30 05:25:31 db1 multipathd: backup2: sdcr - emc_clariion_checker: query command indicates error
Dec 30 05:25:31 db1 multipathd: checker failed path 69:240 in map backup2
Dec 30 05:25:31 db1 kernel: device-mapper: multipath: Failing path 69:240.
Dec 30 05:25:31 db1 multipathd: backup2: remaining active paths: 3
Dec 30 05:25:37 db1 multipathd: backup2: sdcr - emc_clariion_checker: Active path is healthy.
Dec 30 05:25:37 db1 multipathd: 69:240: reinstated
Dec 30 05:25:37 db1 multipathd: backup2: remaining active paths: 4
Dec 30 05:25:37 db1 kernel: sd 5:0:3:2: emc: ALUA failover mode detected
Dec 30 05:25:37 db1 kernel: sd 5:0:3:2: emc: at SP A Port 5 (owned, default SP A)
Dec 30 05:26:03 db1 kernel: qla2xxx [0000:05:00.1]-801c:5: Abort command issued nexus=5:3:4 --  1 2002.
Dec 30 06:03:35 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 06:15:23 db1 multipathd: backup3: sdcq - emc_clariion_checker: Read error for WWN 600601608b203300d563752524c1e611.  Sense data are 0x0/0x0/0x0.
Dec 30 06:15:23 db1 kernel: qla2xxx [0000:05:00.1]-801c:5: Abort command issued nexus=5:3:1 --  1 2002.
Dec 30 06:15:23 db1 kernel: device-mapper: multipath: Failing path 69:224.
Dec 30 06:15:23 db1 multipathd: checker failed path 69:224 in map backup3
Dec 30 06:15:23 db1 multipathd: backup3: remaining active paths: 3
Dec 30 06:15:28 db1 multipathd: backup3: sdcq - emc_clariion_checker: Active path is healthy.
Dec 30 06:15:28 db1 multipathd: 69:224: reinstated
Dec 30 06:15:28 db1 multipathd: backup3: remaining active paths: 4
Dec 30 06:15:28 db1 kernel: sd 5:0:3:1: emc: ALUA failover mode detected
Dec 30 06:15:28 db1 kernel: sd 5:0:3:1: emc: at SP A Port 5 (owned, default SP A)
Dec 30 06:59:29 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 07:53:22 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 07:55:11 db1 multipathd: sdct: couldn't get asymmetric access state
Dec 30 07:55:11 db1 multipathd: backup4: load table [0 2147483648 multipath 2 queue_if_no_path retain_attached_hw_handler 1 emc 2 1 round-robin 0 2 1 70:16 1 66:240 1 round-robin 0 2 1 8:64 1 67:80 1]
Dec 30 07:55:11 db1 kernel: sd 5:0:3:4: emc: ALUA failover mode detected
Dec 30 07:55:11 db1 kernel: sd 5:0:3:4: emc: at SP A Port 5 (owned, default SP A)
Dec 30 07:55:11 db1 kernel: sd 4:0:3:4: emc: ALUA failover mode detected
Dec 30 07:55:11 db1 kernel: sd 4:0:3:4: emc: at SP A Port 4 (owned, default SP A)
Dec 30 07:55:35 db1 multipathd: backup2: sdcr - emc_clariion_checker: Read error for WWN 600601608b203300d663752524c1e611.  Sense data are 0x0/0x0/0x0.
Dec 30 07:55:35 db1 multipathd: checker failed path 69:240 in map backup2
Dec 30 07:55:35 db1 multipathd: backup2: remaining active paths: 3
Dec 30 07:55:35 db1 kernel: device-mapper: multipath: Failing path 69:240.
Dec 30 07:55:40 db1 multipathd: backup2: sdcr - emc_clariion_checker: Active path is healthy.
Dec 30 07:55:40 db1 multipathd: 69:240: reinstated
Dec 30 07:55:40 db1 multipathd: backup2: remaining active paths: 4
Dec 30 07:55:40 db1 kernel: sd 5:0:3:2: emc: ALUA failover mode detected
Dec 30 07:55:40 db1 kernel: sd 5:0:3:2: emc: at SP A Port 5 (owned, default SP A)
Dec 30 08:39:47 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 08:43:36 db1 multipathd: mpathb: load table [0 20971520 multipath 2 queue_if_no_path retain_attached_hw_handler 1 emc 2 1 round-robin 0 2 1 69:208 1 66:176 1 round-robin 0 2 1 8:0 1 67:16 1]
Dec 30 08:43:36 db1 kernel: sd 5:0:3:0: emc: ALUA failover mode detected
Dec 30 08:43:36 db1 kernel: sd 5:0:3:0: emc: at SP A Port 5 (owned, default SP A)
Dec 30 08:43:36 db1 kernel: sd 4:0:3:0: emc: ALUA failover mode detected
Dec 30 08:43:36 db1 kernel: sd 4:0:3:0: emc: at SP A Port 4 (owned, default SP A)
Dec 30 09:24:04 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 10:13:09 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 11:06:07 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 12:07:36 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 13:08:58 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 14:00:19 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 14:52:20 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 15:40:45 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 16:34:38 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 17:09:56 db1 auditd[15975]: Audit daemon rotating log files
Dec 30 17:38:16 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 18:59:38 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.
Dec 30 19:54:43 db1 CLSD: The clock on host db1 has been updated by the Cluster Time Synchronization Service to be synchronous with the mean cluster time.

经查看集群相关日志可以确定，由于存储磁盘出现IO问题（或光线闪断、或IO延迟），导致集群CRS异常宕机。但是，比较奇怪的是，虽然CSR掉线了，ASM实例和DB实例却好好的，还可以正常使用。查询oracle support发现一篇文章1581864.1?提到ASM CRS仲裁盘访问超时与隐藏参数_asm_hbeatiowait有关系，而ASM的隐藏参数_asm_hbeatiowait由于操作系统多路径Multipath配置的polling_interval有关，具体的故障原因是操作系统盘的判断访问超时远大于数据库ASM仲裁盘访问超时，导致ORACLE RAC判定ASM中仲裁盘无法访问从而将仲裁盘强制Offline。解决的思路是：首先，确定操作系统polling_interval参数与数据库ASM隐藏参数值_asm_hbeatiowait，将_asm_hbeatiowait的值调整到比polling_interval值大即可。
下面是具体的解决操作:
1、查看数据库RAC ASM的_asm_hbeatiowait值（默认是15秒）：

SQL> col ksppinm for a20
SQL> col ksppstvl for a40
SQL> col ksppdesc for a80
SQL> SELECT   ksppinm, ksppstvl, ksppdesc
  2     FROM   x$ksppi x, x$ksppcv y
  3    WHERE   x.indx = y.indx AND  ksppinm = '_asm_hbeatiowait';

KSPPINM              KSPPSTVL                                 KSPPDESC
-------------------- ---------------------------------------- --------------------------------------------------------------------------------
_asm_hbeatiowait     15                                       number of secs to wait for PST Async Hbeat IO return

2、查看操作存储盘访问超时时间（Oracle Linux 6.7默认是30秒）

[root@db1 ~]# cat /sys/block/sdb/device/timeout 
30

[root@db2 ~]# cat /sys/block/sdb/device/timeout 
30

3、将_asm_hbeatiowait 的值调整为45秒（该参数是静态参数，需要重启集群）

SQL>  alter system set "_asm_hbeatiowait"=45 scope=spfile sid='*';

System altered.

4.重启集群

5.检查集群状态

[grid@db1 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ARCH.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.CWDATA.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.DADATA.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.DATA.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.LISTENER.lsnr
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.OCR.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.SBKDATA.dg
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.asm
               ONLINE  ONLINE       db1                      Started             
               ONLINE  ONLINE       db2                      Started             
ora.gsd
               OFFLINE OFFLINE      db1                                          
               OFFLINE OFFLINE      db2                                          
ora.net1.network
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
ora.ons
               ONLINE  ONLINE       db1                                          
               ONLINE  ONLINE       db2                                          
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       db2                                          
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       db1                                          
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       db1                                          
ora.caiwu.db
      1        ONLINE  ONLINE       db1                      Open                
      2        ONLINE  ONLINE       db2                      Open                
ora.chdyl.db
      1        ONLINE  ONLINE       db1                      Open                
      2        ONLINE  ONLINE       db2                      Open                
ora.cvu
      1        ONLINE  ONLINE       db1                                          
ora.dadb.db
      1        ONLINE  ONLINE       db1                      Open                
      2        ONLINE  ONLINE       db2                      Open                
ora.db1.vip
      1        ONLINE  ONLINE       db1                                          
ora.db2.vip
      1        ONLINE  ONLINE       db2                                          
ora.oc4j
      1        ONLINE  ONLINE       db1                                          
ora.rlzy.db
      1        ONLINE  ONLINE       db1                      Open                
      2        ONLINE  ONLINE       db2                      Open                
ora.scan1.vip
      1        ONLINE  ONLINE       db2                                          
ora.scan2.vip
      1        ONLINE  ONLINE       db1                                          
ora.scan3.vip
      1        ONLINE  ONLINE       db1

到此处理完成。

RMAN删除归档日志出现RMAN-0813错误的处理

Posted on by YongJing(敬勇)

数据库备份日志报错：RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process，DB版本为11.2.0.4，根据备策略，每天备份归档，并且自动删除档，保留一个星期的归档，手功执如下命令：

delete noprompt archivelog until time 'sysdate-7'；

依然报错，信息如下：

archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_24/thread_1_seq_2328.1241.1027887401 thread=1 sequence=2328
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_24/thread_1_seq_2329.1242.1027887719 thread=1 sequence=2329
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_24/thread_1_seq_2330.1243.1027887813 thread=1 sequence=2330
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_24/thread_1_seq_2331.1244.1027887873 thread=1 sequence=2331
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_25/thread_1_seq_2332.1245.1027902623 thread=1 sequence=2332
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_25/thread_1_seq_2333.1246.1027902719 thread=1 sequence=2333
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_25/thread_1_seq_2334.1247.1027908493 thread=1 sequence=2334
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_25/thread_1_seq_2335.1248.1027911721 thread=1 sequence=2335
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_26/thread_1_seq_2336.1249.1027989005 thread=1 sequence=2336
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_26/thread_1_seq_2337.1250.1027989067 thread=1 sequence=2337
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_26/thread_1_seq_2338.1251.1027990617 thread=1 sequence=2338
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_26/thread_1_seq_2339.1252.1028041713 thread=1 sequence=2339
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_27/thread_1_seq_2340.1253.1028075413 thread=1 sequence=2340
...............

在MOS搜索发现这是11g中的一个bug，描述的原因是在11g中，RMAN查看的是v$database中的MIN_REQUIRED_CAPTURE_CHANGE#，而不是DBA_CAPTURE。默认情况下，该值每6小时更新一次。因此，在默认情况下，v$database中的值将比当前值晚6个小时。
给出的解决方法是需要在delete 后面加上force。
手动执行delete noprompt force archivelog until time ‘sysdate-7’, 成功将归档删除。

deleted archived log
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_24/thread_1_seq_2330.1243.1027887813 RECID=18420 STAMP=1027887814
deleted archived log
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_24/thread_1_seq_2331.1244.1027887873 RECID=18421 STAMP=1027887875
deleted archived log
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_25/thread_1_seq_2332.1245.1027902623 RECID=18422 STAMP=1027902628
deleted archived log
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_25/thread_1_seq_2333.1246.1027902719 RECID=18423 STAMP=1027902721
deleted archived log
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_25/thread_1_seq_2334.1247.1027908493 RECID=18424 STAMP=1027908500
deleted archived log
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_25/thread_1_seq_2335.1248.1027911721 RECID=18425 STAMP=1027911722
deleted archived log
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_26/thread_1_seq_2336.1249.1027989005 RECID=18426 STAMP=1027989010
deleted archived log
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_26/thread_1_seq_2337.1250.1027989067 RECID=18427 STAMP=1027989069
deleted archived log
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_26/thread_1_seq_2338.1251.1027990617 RECID=18428 STAMP=1027990618
deleted archived log
archived log file name=+CWDATA/sjjhdg/archivelog/2019_12_26/thread_1_seq_2339.1252.1028041713 RECID=18429 STAMP=1028041715
...........
Deleted 800 objects
Proudly powered by WordPress | Indrajeet by Sus Hill.