shard node的路由方式有直接路由和代理路由，之前我们已经说过，由于我没有connection pool，我们只能来测试一下，在代理路由的情况下，连接shardcat的情况下，当shard node出现意外，连接在shardcat上的操作会发生什么问题。
这里我们要注意下，查询分如下几种情况：
1. 基于shard key的查询。
2. 不基于shard key的查询
3. multi shard的查询
4. 貌似基于shard key的查询。
（不喜欢看测试过程的，可以直接拖到文末看结果。^_^）
加载数据：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
insert into products select rownum,dbms_random.STRING('U',8),dbms_random.STRING('A',64),round(dbms_random.value(1,1000),2) from dual connect by level<=1000; begin for k in 1 .. 1000 loop insert into customers(custid,firstname,lastname,CLASS,geo,passwd) values (k, 'HE', 'Jimmy', 'A', 'CHINA', to_char(k+999)); end loop; commit; end; / begin for k in 1 .. 1000 loop insert into ORDERS(ORDERID,CUSTID,ORDERDATE,SUMTOTAL,STATUS) values (k+888, k, sysdate-k,mod(k,99),'SEND'); end loop; commit; end; / begin for k in 1 .. 1000 loop insert into LINEITEMS(ORDERID,CUSTID,PRODUCTID,PRICE,QTY) values (k+888, k, K+777,mod(k,99)*k/10.24,k+5); end loop; commit; end; / commit; |
加载之后我的shard table，customers表的情况是：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
=====> sh1: SQL> select CUSTID,passwd from customers where rownum<=15; CUSTID PASSWD ------------------------------ ------------------------------ 1 1000 2 1001 8 1007 29 1028 30 1029 40 1039 46 1045 50 1049 75 1074 78 1077 79 1078 117 1116 143 1142 148 1147 156 1155 15 rows selected. SQL> ====> sh2: SQL> select count(*) from customers; COUNT(*) ---------- 484 SQL> SQL> SQL> SQL> select CUSTID,passwd from customers where rownum<=15; CUSTID PASSWD ------------------------------ ------------------------------ 3 1002 5 1004 16 1015 28 1027 67 1066 69 1068 82 1081 87 1086 94 1093 133 1132 134 1133 135 1134 137 1136 141 1140 176 1175 15 rows selected. SQL> |
所以我的查询语句就是：
1. 基于shard key的查询。
select CUSTID,FIRSTNAME,LASTNAME from customers where custid=’1′;
select CUSTID,FIRSTNAME,LASTNAME from customers where custid=’3′;
2. 不基于shard key的查询
select CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD=’1000′;
select CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD=’1002′;
3. multi shard的查询
select CUSTID,FIRSTNAME,LASTNAME from customers where custid in (‘1′,’3’);
4. 貌似基于shard key的查询。
select CUSTID,FIRSTNAME,LASTNAME from customers where custid=1;
select CUSTID,FIRSTNAME,LASTNAME from customers where custid=3;
我们通过一个循环语句来测试：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
while true do echo "############################ `date` ############################" sqlplus -s app_schema/oracle<<EOF set line 1000 set feedback on set echo on select 'Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid='1'; select 'Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid='3'; select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1000'; select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1002'; select 'Using multi shard' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid in ('1','3'); select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=1; select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=3; exit EOF sleep 1 echo "" echo "" echo "" done |
场景1：在没有dataguard FSFO保护的情况下，shard node 1 database crash。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
############################ Sun Nov 20 00:10:00 CST 2016 ############################ TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using multi shard 1 HE Jimmy Using multi shard 3 HE Jimmy 2 rows selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 3 HE Jimmy 1 row selected. |
–sh1 shutdown abort
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 |
############################ Sun Nov 20 00:10:02 CST 2016 ############################ TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1000' * ERROR at line 1: ORA-03150: end-of-file on communication channel for database link ORA-03113: end-of-file on communication channel select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1002' * ERROR at line 1: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor select 'Using multi shard' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid in ('1','3') * ERROR at line 1: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=1 * ERROR at line 1: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=3 * ERROR at line 1: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor ############################ Sun Nov 20 00:10:03 CST 2016 ############################ select 'Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid='1' * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1000' * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1002' * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'Using multi shard' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid in ('1','3') * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=1 * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=3 * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 |
我们看到，在sh1 shutdown abort之后，在shardcat上的查询，出现短暂的一次db link连接失败的报错后，后面就都是：
1. 基于shard key的查询。–访问sh1的操作失败，访问sh2的操作正常
2. 不基于shard key的查询。–无论访问sh1还是sh2，都失败
3. multi shard的查询 –访问失败
4. 貌似基于shard key的查询 –无论访问sh2还是sh2，都失败。
这里我们看出，貌似基于shard key的访问，如上面进行隐式转换的语句，看上去像基于shard key，其实还是不能正常访问到对应的shard node的。
我们再re-startup sh1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
############################ Sun Nov 20 00:11:12 CST 2016 ############################ TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using multi shard 1 HE Jimmy Using multi shard 3 HE Jimmy 2 rows selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 3 HE Jimmy 1 row selected. |
re-startup sh1之后，所有的访问都恢复正常。
场景2：在没有dataguard FSFO保护的情况下，shard node 1 主机 power down。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 |
############################ Sun Nov 20 00:32:25 CST 2016 ############################ TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using multi shard 1 HE Jimmy Using multi shard 3 HE Jimmy 2 rows selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 3 HE Jimmy 1 row selected. -- shutdown sh1 server power ############################ Sun Nov 20 00:32:27 CST 2016 ############################ select 'Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid='1' * ERROR at line 1: ORA-12170: TNS:Connect timeout occurred TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1000' * ERROR at line 1: ORA-12543: TNS:destination host unreachable select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1002' * ERROR at line 1: ORA-12543: TNS:destination host unreachable select 'Using multi shard' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid in ('1','3') * ERROR at line 1: ORA-12543: TNS:destination host unreachable select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=1 * ERROR at line 1: ORA-12543: TNS:destination host unreachable select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=3 * ERROR at line 1: ORA-12543: TNS:destination host unreachable ############################ Sun Nov 20 00:33:43 CST 2016 ############################ select 'Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid='1' * ERROR at line 1: ORA-12543: TNS:destination host unreachable TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1000' * ERROR at line 1: ORA-12543: TNS:destination host unreachable select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1002' * ERROR at line 1: ORA-12543: TNS:destination host unreachable select 'Using multi shard' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid in ('1','3') * ERROR at line 1: ORA-12543: TNS:destination host unreachable select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=1 * ERROR at line 1: ORA-12543: TNS:destination host unreachable select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=3 * ERROR at line 1: ORA-12543: TNS:destination host unreachable ############################ Sun Nov 20 00:34:03 CST 2016 ############################ select 'Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid='1' * ERROR at line 1: ORA-12543: TNS:destination host unreachable TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1000' * ERROR at line 1: ORA-12543: TNS:destination host unreachable select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1002' * ERROR at line 1: ORA-12543: TNS:destination host unreachable select 'Using multi shard' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid in ('1','3') * ERROR at line 1: ORA-12543: TNS:destination host unreachable select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=1 * ERROR at line 1: ORA-12543: TNS:destination host unreachable select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=3 * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 ############################ Sun Nov 20 00:34:19 CST 2016 ############################ select 'Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid='1' * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1000' * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1002' * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'Using multi shard' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid in ('1','3') * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=1 * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=3 * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 ############################ Sun Nov 20 00:34:20 CST 2016 ############################ |
我们看到，在shard node 1 server power down之后，在shardcat上的查询，出现短暂的大约2分钟的hang，等待之后报错ORA-12543: TNS:destination host unreachable，再后面就和db shutdown abort的情况一下了：
1. 基于shard key的查询。–访问sh1的操作失败，访问sh2的操作正常
2. 不基于shard key的查询。–无论访问sh1还是sh2，都失败
3. multi shard的查询 –访问失败
4. 貌似基于shard key的查询 –无论访问sh2还是sh2，都失败。
–restart server之后，重启listener和db：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
############################ Sun Nov 20 00:37:48 CST 2016 ############################ TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using multi shard 1 HE Jimmy Using multi shard 3 HE Jimmy 2 rows selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 3 HE Jimmy 1 row selected. |
场景3：在dataguard FSFO保护的情况下，shard node 1 database crash。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 |
############################ Mon Nov 21 22:46:53 CST 2016 ############################ TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using multi shard 1 HE Jimmy Using multi shard 3 HE Jimmy 2 rows selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 3 HE Jimmy 1 row selected. --shutdown abort sh1 ############################ Mon Nov 21 22:46:55 CST 2016 ############################ select 'Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid='1' * ERROR at line 1: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1000' * ERROR at line 1: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1002' * ERROR at line 1: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor select 'Using multi shard' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid in ('1','3') * ERROR at line 1: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=1 * ERROR at line 1: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=3 * ERROR at line 1: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor ############################ Mon Nov 21 22:46:56 CST 2016 ############################ select 'Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid='1' * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1000' * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1002' * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'Using multi shard' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid in ('1','3') * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=1 * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'Look like use shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid=3 * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "1" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 --FSFO effect ############################ Mon Nov 21 22:47:46 CST 2016 ############################ TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using multi shard 3 HE Jimmy Using multi shard 1 HE Jimmy 2 rows selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 3 HE Jimmy 1 row selected. |
我们可以看到，在50秒的时间，就完成了FSFO。在FSFO切换期间内：
1. 基于shard key的查询。–访问sh1的操作失败，访问sh2的操作正常
2. 不基于shard key的查询。–无论访问sh1还是sh2，都失败
3. multi shard的查询 –访问失败
4. 貌似基于shard key的查询 –无论访问sh2还是sh2，都失败。
等FSFO完，所有的操作就都恢复正常。
注意，FSFO完之后，数据库在broker中的状态变成需要reinstated：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
DGMGRL> show configuration verbose Configuration - sh1 Protection Mode: MaxPerformance Members: sh3 - Primary database Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database sh1 - (*) Physical standby database (disabled) ORA-16661: the standby database needs to be reinstated (*) Fast-Start Failover target Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'SUPPORT' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' ConfigurationWideServiceName = 'sh1_CFG' Fast-Start Failover: ENABLED Threshold: 30 seconds Target: sh1 Observer: sdb1 Lag Limit: 30 seconds Shutdown Primary: TRUE Auto-reinstate: TRUE Observer Reconnect: (none) Observer Override: FALSE Configuration Status: ERROR DGMGRL> |
我们现在来把sh1恢复成初始状态。
(1) 启动sh1数据库，注意，reinstate需要在standby上做，在sh1上做是会报错的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
[oracle12c@sdb2 ~]$ dgmgrl sys/oracle@sh1 DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Nov 22 15:26:42 2016 Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Unable to connect to database using sh1 ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Failed. DGMGRL> exit [oracle12c@sdb2 ~]$ dgmgrl sys/oracle DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Nov 22 15:26:56 2016 Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected as SYSDG. DGMGRL> startup ORACLE instance started. Database mounted. ORA-16649: possible failover to another database prevents this database from being opened DGMGRL> DGMGRL> reinstate database sh1 ORA-16795: the standby database needs to be re-created Configuration details cannot be determined by DGMGRL DGMGRL> DGMGRL> DGMGRL> exit [oracle12c@sdb2 ~]$ |
(2) 在sh3上做reinstate，注意，做完reinstate之后，虽然不用re-create standby，但是sh3还是primary，sh1还是standby。另外，observer的状态还没有恢复。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
[oracle12c@sdb4 ~]$ dgmgrl sys/oracle@sh3 DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Nov 22 15:33:23 2016 Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "sh3" Connected as SYSDBA. DGMGRL> show configuration verbose Configuration - sh1 Protection Mode: MaxPerformance Members: sh3 - Primary database Error: ORA-16825: multiple errors or warnings, including fast-start failover-related errors or warnings, detected for the database sh1 - (*) Physical standby database (disabled) ORA-16661: the standby database needs to be reinstated (*) Fast-Start Failover target Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'SUPPORT' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' ConfigurationWideServiceName = 'sh1_CFG' Fast-Start Failover: ENABLED Threshold: 30 seconds Target: sh1 Observer: sdb1 Lag Limit: 30 seconds Shutdown Primary: TRUE Auto-reinstate: TRUE Observer Reconnect: (none) Observer Override: FALSE Configuration Status: ERROR DGMGRL> reinstate database sh1 Reinstating database "sh1", please wait... Reinstatement of database "sh1" succeeded DGMGRL> DGMGRL> DGMGRL> DGMGRL> DGMGRL> DGMGRL> show configuration verbose Configuration - sh1 Protection Mode: MaxPerformance Members: sh3 - Primary database Error: ORA-16820: fast-start failover observer is no longer observing this database sh1 - (*) Physical standby database Error: ORA-16820: fast-start failover observer is no longer observing this database (*) Fast-Start Failover target Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'SUPPORT' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' ConfigurationWideServiceName = 'sh1_CFG' Fast-Start Failover: ENABLED Threshold: 30 seconds Target: sh1 Observer: sdb1 Lag Limit: 30 seconds Shutdown Primary: TRUE Auto-reinstate: TRUE Observer Reconnect: (none) Observer Override: FALSE Configuration Status: ERROR DGMGRL> |
(3) 我们先来恢复一下observer。在observer server上，也就是sh1上重置observer状态和重启observer进程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 |
[oracle12c@sdb1 startObsrv_sh1]$ dgmgrl sys/oracle@sh1_obsrv DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Nov 22 15:39:09 2016 Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "sh1" Connected as SYSDBA. DGMGRL> show configuration verbose Configuration - sh1 Protection Mode: MaxPerformance Members: sh3 - Primary database Error: ORA-16820: fast-start failover observer is no longer observing this database sh1 - (*) Physical standby database Error: ORA-16820: fast-start failover observer is no longer observing this database (*) Fast-Start Failover target Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'SUPPORT' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' ConfigurationWideServiceName = 'sh1_CFG' Fast-Start Failover: ENABLED Threshold: 30 seconds Target: sh1 Observer: sdb1 Lag Limit: 30 seconds Shutdown Primary: TRUE Auto-reinstate: TRUE Observer Reconnect: (none) Observer Override: FALSE Configuration Status: ERROR DGMGRL> DGMGRL> DGMGRL> STOP OBSERVER ALL Observer stopped. DGMGRL> DGMGRL> DGMGRL> show configuration verbose Configuration - sh1 Protection Mode: MaxPerformance Members: sh3 - Primary database Warning: ORA-16819: fast-start failover observer not started sh1 - (*) Physical standby database Warning: ORA-16819: fast-start failover observer not started (*) Fast-Start Failover target Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'SUPPORT' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' ConfigurationWideServiceName = 'sh1_CFG' Fast-Start Failover: ENABLED Threshold: 30 seconds Target: sh1 Observer: (none) Lag Limit: 30 seconds Shutdown Primary: TRUE Auto-reinstate: TRUE Observer Reconnect: (none) Observer Override: FALSE Configuration Status: WARNING DGMGRL> exit [oracle12c@sdb1 startObsrv_sh1]$ [oracle12c@sdb1 startObsrv_sh1]$ cd /home/oracle12c/startObsrv_sh1 [oracle12c@sdb1 startObsrv_sh1]$ rm * [oracle12c@sdb1 startObsrv_sh1]$ [oracle12c@sdb1 startObsrv_sh1]$ nohup dgmgrl -silent sys/oracle@sh1_obsrv "start observer" & [oracle12c@sdb1 startObsrv_sh1]$ dgmgrl sys/oracle@sh1_obsrv DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Nov 22 15:46:41 2016 Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "sh1" Connected as SYSDBA. DGMGRL> show configuration verbose Configuration - sh1 Protection Mode: MaxPerformance Members: sh3 - Primary database sh1 - (*) Physical standby database (*) Fast-Start Failover target Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'SUPPORT' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' ConfigurationWideServiceName = 'sh1_CFG' Fast-Start Failover: ENABLED Threshold: 30 seconds Target: sh1 Observer: sdb1 Lag Limit: 30 seconds Shutdown Primary: TRUE Auto-reinstate: TRUE Observer Reconnect: (none) Observer Override: FALSE Configuration Status: SUCCESS DGMGRL> |
(4) 我们把主备切换回来，sh1切回成主。注意切换过程，shard node会不可访问。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
[oracle12c@sdb1 ~]$ dgmgrl sys/oracle@sh3_obsrv DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue Nov 22 15:58:49 2016 Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "sh3" Connected as SYSDBA. DGMGRL> show configuration verbose Configuration - sh1 Protection Mode: MaxPerformance Members: sh3 - Primary database sh1 - (*) Physical standby database (*) Fast-Start Failover target Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'SUPPORT' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' ConfigurationWideServiceName = 'sh1_CFG' Fast-Start Failover: ENABLED Threshold: 30 seconds Target: sh1 Observer: sdb1 Lag Limit: 30 seconds Shutdown Primary: TRUE Auto-reinstate: TRUE Observer Reconnect: (none) Observer Override: FALSE Configuration Status: SUCCESS DGMGRL> DGMGRL> switchover to sh1 Performing switchover NOW, please wait... Operation requires a connection to database "sh1" Connecting ... Connected to "sh1" Connected as SYSDBA. New primary database "sh1" is opening... Operation requires start up of instance "sh3" on database "sh3" Starting instance "sh3"... ORACLE instance started. Database mounted. Database opened. Connected to "sh3" Switchover succeeded, new primary is "sh1" DGMGRL> DGMGRL> show configuration verbose Configuration - sh1 Protection Mode: MaxPerformance Members: sh1 - Primary database sh3 - (*) Physical standby database (*) Fast-Start Failover target Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'SUPPORT' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' ConfigurationWideServiceName = 'sh1_CFG' Fast-Start Failover: ENABLED Threshold: 30 seconds Target: sh3 Observer: sdb1 Lag Limit: 30 seconds Shutdown Primary: TRUE Auto-reinstate: TRUE Observer Reconnect: (none) Observer Override: FALSE Configuration Status: SUCCESS DGMGRL> |
场景4：最后，我们来测试一下在FSFO保护的情况下，shard node 1 power down。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 |
############################ Tue Nov 22 16:08:36 CST 2016 ############################ TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using multi shard 1 HE Jimmy Using multi shard 3 HE Jimmy 2 rows selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 3 HE Jimmy 1 row selected. ############################ Tue Nov 22 16:08:37 CST 2016 ############################ select 'Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid='1' * ERROR at line 1: ORA-12543: TNS:destination host unreachable TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1000' * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "5" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'NOT Using shard_key' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where PASSWD='1002' * ERROR at line 1: ORA-02519: cannot perform cross-shard operation. Chunk "5" is unavailable ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18795 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18772 ORA-06512: at "GSMADMIN_INTERNAL.DBMS_GSM_POOLADMIN", line 18811 ORA-06512: at line 1 select 'Using multi shard' as TYPE,CUSTID,FIRSTNAME,LASTNAME from customers where custid in ('1','3') * ERROR at line 1: ORA-12543: TNS:destination host unreachable TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 3 HE Jimmy 1 row selected. ############################ Tue Nov 22 16:09:37 CST 2016 ############################ TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME --------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ NOT Using shard_key 3 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Using multi shard 3 HE Jimmy Using multi shard 1 HE Jimmy 2 rows selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 1 HE Jimmy 1 row selected. TYPE CUSTID FIRSTNAME LASTNAME ----------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ Look like use shard_key 3 HE Jimmy 1 row selected. |
可以看到，当server的down的时候，被ovserver观察到，立马做主备切换。切换时间大约1分钟。 切换期间，行为和之前的一样：
1. 基于shard key的查询。–访问sh1的操作失败，访问sh2的操作正常
2. 不基于shard key的查询。–无论访问sh1还是sh2，都失败
3. multi shard的查询 –访问失败
4. 貌似基于shard key的查询 –无论访问sh2还是sh2，都失败
主机重启后，后续的操作也是类似的，启动listener，启动数据库，到sh3上做reinstate database sh1，再做switchover，再重启observer。
总结：
1. 在没有ADG+FSFO的高可用保护下，当shard node宕掉的时候，只有基于shard key的访问才能正常，其他的访问都不正常。需要等shard node重新启动后，才能恢复正常访问。
2. 在没有ADG+FSFO的高可用保护下，当shard node主机宕掉的时候，中间会hang两分钟左右的时间(因为TNS:Connect timeout)，过了两分钟的停顿时间后，只有基于shard key的访问才能正常，其他的访问都不正常。需要等shard node重新启动后，才能恢复正常访问。
3. 在有ADG+FSFO的高可用保护下，当shard node宕掉的时候，只有基于shard key的访问才能正常，其他的访问都不正常。切换过程大约50秒，50秒之后，所有访问恢复正常访问。
4. 在有ADG+FSFO的高可用保护下，当shard node主机宕掉的时候，只有基于shard key的访问才能正常，其他的访问都不正常。切换过程大约50秒，50秒之后，所有访问恢复正常访问。
可以看到，有FSFO的保护，影响业务时间可以在一分钟以内。
另外，由于reinstate之后，还需要switchover，切换成本比较高，又是一次down库，所以如果有RAC，那么对业务的影响可以降至最低。