新搭建的一套PXC集群，采用了Percona的最新版本，如下所示：
|
1 2 |
[root@mysqldb2:0 /mysql/soft/bak]# mysql --version mysql Ver 8.0.21-12.1 for Linux on x86_64 (Percona XtraDB Cluster (GPL), Release rel12, Revision 4d973e2, WSREP version 26.4.3) |
需要修改系统配置为大小写不敏感，因此需要修改配置参数lower_case_table_names=1。
修改以后需要重启集群，这里涉及到一系列问题的发生，具体如下所述。
只在节点1修改后立即启动失败，需要3个节点都改完，都关闭，再次启动才可以。
如下可见，最后关闭的[……]
某项目急需一个MySQL库，于是在CentOS 7.5下配置了系统自带的mariadb server，正常启动是没有问题的。
但是我想把相关的数据文件放置到专门的文件系统，这样就修改了配置，也确认目录是可写的，但是一直无法正常启动！
具体的报错如下:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
210202 04:25:42 mysqld_safe Starting mysqld daemon with databases from /u01/mysql 210202 4:25:42 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 28897 ... 210202 4:25:42 [Warning] Can't create test file /u01/mysql/localhost.lower-test 210202 4:25:42 InnoDB: The InnoDB memory heap is disabled 210202 4:25:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins 210202 4:25:42 InnoDB: Compressed tables use zlib 1.2.7 210202 4:25:42 InnoDB: Using Linux native AIO 210202 4:25:42 InnoDB: Initializing buffer pool, size = 128.0M 210202 4:25:42 InnoDB: Completed initialization of buffer pool 210202 4:25:42 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 210202 04:25:42 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended |
意思就是无法在那个目录下写！
查遍了所有的日志文件，再没有有用的信息了。
只好网搜[……]
19.9 ADG环境，查看Flash Recovery Area Usage发现FLASHBACK LOG占用了90%多的FRA空间，而ARCHIVED LOG仅占用不到10%，这还了得，这是一个繁忙的生产环境，一般是不需要长时间的Flash Back的，得想办法清理一下，以免影响归档。【注：其实是不会影响归档的，具体的实践观察见后】
如下是清理前的占用情况:
[crayon-6045f4d691[……]
在CentOS7.6下配置oracle 19.9时，如果没有显式设置HugePages为SGA所需的合适的值大小，而是设置了较大的值，尽管有时可以分配一些HugePages，但是一般不会是所设置的数目，结论是，必须显式设置合适的值。
如下关于wydb两个实例的alertlog输出，最后一次输出是正确设置并重启之后的：
|
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 |
---wydb1 2021-01-14T15:38:39.837037+08:00 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) 4K Configured 12 58126 NONE 2048K 65425 65537 65423 NONE RECOMMENDATION: 2021-01-14T18:21:50.540222+08:00 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) 4K Configured 12 18146062 NONE 2048K 95616 65537 30095 NONE 2021-01-26T11:03:07.510699+08:00 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) 4K Configured 12 12 NONE 2048K 65537 65537 65537 NONE ---wydb2 2021-01-20T15:48:21.293976+08:00 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) 4K Configured 12 58126 NONE 2048K 65536 65537 65423 NONE RECOMMENDATION: 2021-01-20T16:32:32.496017+08:00 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) 4K Configured 12 58126 NONE 2048K 65536 65537 65423 NONE RECOMMENDATION: 2021-01-26T11:26:36.610035+08:00 PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s) 4K Configured 12 12 NONE 2048K 65537 65537 65537 NONE |
以下关于zzj[……]
一体机的19c新数据库，由于项目人员修改undo，导致出现有的pdb无法open的情况：
|
1 2 3 4 5 6 7 8 9 |
2021-01-26T12:03:43.957991+08:00 PDBAPP(3):Endian type of dictionary set to little PDBAPP(3):Undo initialization recovery: err:0 start: 2574325 end: 2574327 diff: 2 ms (0.0 seconds) 2021-01-26T12:03:44.456447+08:00 PDBAPP(3):Undo initialization online undo segments: err:30013 start: 2574327 end: 2574579 diff: 252 ms (0.3 seconds) Pdb PDBAPP hit error 30013 during open read write (1) and will be closed. 2021-01-26T12:03:44.457035+08:00 Errors in file /u01/app/oracle/diag/rdbms/wydb/wydb2/trace/wydb2_ora_23282.trc: ORA-30013: undo tablespace 'UNDOTBS2' is currently in use |
oerr查看报错信息:
|
1 2 3 4 |
// *Cause: the specified undo tablespace is currently used by another // instance. // *Action: Wait for the undo tablespace to become available or // change to another name and reissue the statement. |
可见问题比较清楚，undo被别的实例占用了，这样的话，原因基本确认是修改undo配置时出了问题。
因此需确认目前的配置，改正即可。
当前数据库建库时采用了[……]
■2021-01-14 15:07
存储开始出现问题，主数据库2个节点(node1、node2)均报共享存储’I/O error’
|
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 |
-------node1 alert 2021-01-14T15:07:24.122930+08:00 WARNING: Write Failed. group:1 disk:3 AU:81 offset:2022912 size:4096 path:/dev/asmdisks/su001_lun04 incarnation:0xf0f0720e asynchronous result:'I/O error' subsys:System krq:0x7fd35c49c928 bufp:0x2060dcb800 osderr1:0x69b5 osderr2:0x0 IO elapsed time: 0 usec Time waited on I/O: 0 usec ---此处省略若干 2021-01-14T15:23:38.253848+08:00 NOTE: updating disk modes to 0x7 from 0x5 for disk 3 (DATA3_0003) in group 3 (DATA3): lflags 0x0 NOTE: updating disk modes to 0x7 from 0x5 for disk 5 (DATA3_0005) in group 3 (DATA3): lflags 0x0 NOTE: updating disk modes to 0x7 from 0x5 for disk 7 (DATA3_0007) in group 3 (DATA3): lflags 0x0 NOTE: updating disk modes to 0x7 from 0x5 for disk 33 (DATA3_0033) in group 3 (DATA3): lflags 0x0 NOTE: updating disk modes to 0x7 from 0x5 for disk 46 (DATA3_0046) in group 3 (DATA3): lflags 0x0 NOTE: disk 3 (DATA3_0003) in group 3 (DATA3) is online for reads NOTE: disk 5 (DATA3_0005) in group 3 (DATA3) is online for reads NOTE: disk 7 (DATA3_0007) in group 3 (DATA3) is online for reads NOTE: disk 33 (DATA3_0033) in group 3 (DATA3) is online for reads NOTE: disk 46 (DATA3_0046) in group 3 (DATA3) is online for reads ---此处省略若干 2021-01-14T15:31:09.600620+08:00 NOTE: ASMB process initiating disk discovery for grp 1 (reqid:4751423187424713884) NOTE: Found /dev/asmdisks/su001_lun02 for disk DATA1_0001 SUCCESS: disk DATA1_0001 (1.4042289684) replaced in diskgroup DATA1 path: /dev/asmdisks/su001_lun02 SUCCESS: completed reconfiguration of group 1 (DATA1) NOTE: updating disk modes to 0x5 from 0x1 for disk 1 (DATA1_0001) in group 1 (DATA1): lflags 0x0 NOTE: disk 1 (DATA1_0001) in group 1 (DATA1) is online for writes 2021-01-14T15:31:25.524942+08:00 NOTE: updating disk modes to 0x7 from 0x5 for disk 1 (DATA1_0001) in group 1 (DATA1): lflags 0x0 NOTE: disk 1 (DATA1_0001) in group 1 (DATA1) is online for reads -------node2 alert 2021-01-14T15:07:23.320330+08:00 PDBAPP(3):WARNING: Read Failed. group:1 disk:5 AU:138139 offset:3162112 size:8192 PDBAPP(3):path:/dev/asmdisks/su001_lun06 PDBAPP(3): incarnation:0xf0f0510c synchronous result:'I/O error' PDBAPP(3): subsys:System krq:0x7f8f2bd86ec0 bufp:0x1f5a5de000 osderr1:0x69c0 osderr2:0x0 PDBAPP(3): IO elapsed time: 0 usec Time waited on I/O: 0 usec ---此处省略若干 2021-01-14T15:37:12.581586+08:00 IO elapsed time: 0 usec Time waited on I/O: 0 usec ---此处省略若干 2021-01-14T15:37:45.068876+08:00 WARNING: Read Failed. group:1 disk:20 AU:7 offset:16384 size:16384 path:/dev/asmdisks/su003_lun01 incarnation:0xf0f0511e synchronous result:'I/O error' subsys:System krq:0x7ff4b2ec9b10 bufp:0x7ff4b2c43000 osderr1:0x69c0 osderr2:0x0 (mirrored read) IO elapsed time: 0 usec Time waited on I/O: 0 usec |
■2021-01-14 15:37
备库监控开始报错，主库长时间失联，导致主备切换
|
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 |
-------1 adg 2021-01-14T15:37:30.328+08:00 Fast-Start Failover cannot proceed because: "primary last contacted this standby within FastStartFailoverThreshold seconds" primary last contacted to instance 1 17 seconds ago 2021-01-14T15:37:33.343+08:00 Fast-Start Failover cannot proceed because: "primary last contacted this standby within FastStartFailoverThreshold seconds" primary last contacted to instance 1 20 seconds ago 2021-01-14T15:37:36.356+08:00 Fast-Start Failover cannot proceed because: "primary last contacted this standby within FastStartFailoverThreshold seconds" primary last contacted to instance 1 23 seconds ago 2021-01-14T15:37:39.370+08:00 Fast-Start Failover cannot proceed because: "primary last contacted this standby within FastStartFailoverThreshold seconds" primary last contacted to instance 1 26 seconds ago 2021-01-14T15:37:42.383+08:00 Fast-Start Failover cannot proceed because: "primary last contacted this standby within FastStartFailoverThreshold seconds" primary last contacted to instance 1 29 seconds ago 2021-01-14T15:37:46.854+08:00 FAILOVER TO wydbadg Starting FAILOVER -------2 adg alert 2021-01-14T15:37:45.397868+08:00 Attempting Fast-Start Failover because the threshold of 30 seconds has elapsed. 2021-01-14T15:37:46.514371+08:00 Errors in file /u01/app/oracle/diag/rdbms/wydbadg/wydb1/trace/wydb1_ora_120118.trc: 2021-01-14T15:37:46.847916+08:00 Attempting Fast-Start Failover because the threshold of 30 seconds has elapsed. 2021-01-14T15:37:46.890811+08:00 Beginning Fast-Start Failover to database wydbadg. 2021-01-14T15:37:46.915192+08:00 ALTER DATABASE FAILOVER TO wydbadg -------3 node1 alert 2021-01-14T15:38:30.185383+08:00 Starting background process FSFP 2021-01-14T15:38:30.214348+08:00 FSFP started with pid=87, OS id=205120 2021-01-14T15:38:31.184950+08:00 Primary has heard from neither observer nor target standby within FastStartFailoverThreshold seconds. It is likely an automatic failover has already occurred. Primary is shutting down. |
■分析结论
当时厂家人员做心跳故障模拟测试，可能有相[……]
关闭网络心跳ip后，此时磁盘心跳正常，数据库主集群的2个节点发生脑裂，导致数据库重新配置。
重新配置的基本原则：节点数多的子集群存活，如果子集群包含的节点数相同，那么包含最小编号节点的子集群存活。
所以2节点集群，如果停掉所有的心跳ip后，node1存活，继续对外提供服务。
实际日志顺序如下：
1、node2的数据库实例被强行终止
2、node1确认检测到node2实例终止后，自我调整后继续提供数[……]
1962分，总算打破自己尘封已久的1862分记录了，看来是水平有所长进，不容易啊，时隔太久了，截图记录一下。
前期19c上出现的这个问题，后期在操作ASM磁盘时，再次复发：
ORA-15137: The ASM cluster is in rolling patch state
此时确认两个节点的patch level完全一致，如下：
|
1 2 3 4 |
[root@wydb01:0 ~]$ crsctl query crs softwarepatch wydb01 Oracle Clusterware patch level on node wydb01 is [376483838]. [root@wydb01:0 ~]$ crsctl query crs softwarepatch wydb02 Oracle Clusterware patch level on node wydb02 is [376483838]. |
解决办法，只需在任一节点执行：
crsctl stop rollingpatch
如下操作：
[crayo[……]
在查看补丁情况及冲突检查时，相关操作结果异常，如下
|
1 2 3 4 5 6 7 |
[oracle@wydb02:2 ~]$ $ORACLE_HOME/OPatch/opatch lsinv List of Homes on this system: Home name= OraGI19Home1, Location= "/u01/app/12.2.0/grid" LsInventorySession failed: RawInventory gets null OracleHomeInfo OPatch failed with error code 73 |
可见节点2的oraInventory异常，会导致无法继续操作打补丁。
根据相关的错误提示，检查oraInventory配置文件，如下
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[root@wydb02:2 /]$ cat /u01/app/oraInventory/ContentsXML/inventory.xml <?xml version="1.0" standalone="yes" ?> <!-- Copyright (c) 1999, 2020, Oracle and/or it/s affiliates. All rights reserved. --> <!-- Do not modify the contents of this file by hand. --> <INVENTORY> <VERSION_INFO> <SAVED_WITH>12.2.0.7.0</SAVED_WITH> <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER> </VERSION_INFO> <HOME_LIST> <HOME NAME="OraGI19Home1" LOC="/u01/app/12.2.0/grid" TYPE="O" IDX="1" CRS="true"/> </HOME_LIST> <COMPOSITEHOME_LIST> </COMPOSITEHOME_LIST> </INVENTORY> |
对比节点1的配置内容:
[crayon-6045f4d69a01932162249[……]
©1996-2020 Liking's Oracle Blog, All Rights Reserved