分享程序员开发的那些事...
更新时间：2022-09-02 10:01:10
select c.cn as
cn, c.uin as uin from (select cn from test_cn_bind where enabled='Y' group by cn
having count(cn)>1) t, test_cn_bind c where t.cn = c.cn and c.enabled='Y' order
by cn
所以这个问题和开发同事沟通了一下，才得知了缘由。原来test_cn_bind这个表有一个字段cn,目前是nonunique的，业务上需要为unique的，但是在线修改目前还是没有机会的，如果真是出现了这种情况，会在业务上有一些问题，但是几率很低，所以开发部门最后建议在备库上执行这么一个查询。
这么来看，原来在设计阶段这个问题就没有考虑完善，后续就会有各种的补救措施。
既然业务上确实需要，但是目前的改进空间还比较大，比如走个并行，比如更快的存储介质上。
因为这是个一主两备的环境，第二个备库是最近申请的，里面有SSD，所以在满足同样需求的情况下，可以考虑把语句挪到SSD所在的备库上来。
那么先来看看在SSD上执行的效果。结果运行竟然还报错了。
SQL> select count(*) from (select c.cn as cn,c.uin from test_cn_bind c where enabled='Y' group by c.cn,c.uin having count(c.cn) >1);
select count(*) from (select c.cn as cn,c.uin from test_cn_bind c where enabled='Y' group by c.cn,c.uin having count(c.cn) >1) *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1003 - see DBWR trace file
ORA-01110: data file 1003: '/U01/app/oracle/oradata/testmdb/temp03.dbf'
Elapsed: 00:00:07.81
根据提示说在/U01下没有这个临时数据文件。在文件系统中查看，确实是不存在，但是在这个备库有路径映射，是/U01 映射 /U03,临时数据文件在/U03下面。临时数据文件确实在/U03下面。
使用dg broker把备库置为online，然后开始尝试offline drop.
SQL> alter database datafile '/U01/app/oracle/oradata/testmdb/temp01.dbf' offline drop;
alter database datafile '/U01/app/oracle/oradata/testmdb/temp01.dbf' offline drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile "/U01/app/oracle/oradata/testmdb/temp01.dbf"
看来只能做rename了。
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database rename file '/U01/app/oracle/oradata/testmdb/temp01.dbf' to '/U03/app/oracle/oradata/testmdb/temp01.dbf';
alter database rename file '/U01/app/oracle/oradata/testmdb/temp01.dbf' to '/U03/app/oracle/oradata/testmdb/temp01.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.
修改失败，发现备库文件管理有auto的影响，限制为manual修改。
SQL> show parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> alter system set standby_file_management=manual;
System altered.
再次修改就没有问题了，三个临时数据文件都修改一下。
SQL> alter database rename file '/U01/app/oracle/oradata/testmdb/temp01.dbf' to '/U03/app/oracle/oradata/testmdb/temp01.dbf';
Database altered.
SQL> alter database rename file '/U01/app/oracle/oradata/testmdb/temp02.dbf' to '/U03/app/oracle/oradata/testmdb/temp02.dbf';
Database altered.
SQL> alter database rename file '/U01/app/oracle/oradata/testmdb/temp03.dbf' to '/U03/app/oracle/oradata/testmdb/temp03.dbf';
Database altered.
修改完成之后再置为auto
SQL> alter system set standby_file_management=auto;
System altered.
当然这种变化，备库中肯定会有影响。
DGMGRL> show configuration;
Configuration
Name: testmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
testmdb - Primary database
stestmdb2 - Physical standby database
stestmdb - Physical standby database
Current status for "testmdb":
Warning: ORA-16607: one or more databases have failed
$ dgmgrl /
Connected.
DGMGRL> show database verbose stestmdb2;
Database
Name: stestmdb2
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
testmdb
Properties:
InitialConnectIdentifier = 'stestmdb2'
ObserverConnectIdentifier = ''
LogXptMode = 'ARCH'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/U01/app/oracle/oradata/testmdb, /U03/app/oracle/oradata/testmdb'
LogFileNameConvert = '/U01/app/oracle/oradata/testmdb, /U02/app/oracle/oradata/testmdb'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'acc223.cyou.com'
SidName = 'testmdb'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=TCP)(HOST=10.127.65.223)(PORT=1521))'
StandbyArchiveLocation = '/U01/app/oracle/admin/testmdb/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "stestmdb2":
Error: ORA-16766: Redo Apply unexpectedly offline
需要手动开启日志应用。
DGMGRL> edit database stestmdb2 set state='READ-ONLY';
Succeeded.
然后再次尝试就可以了，通过下面的top信息可以看到在执行并行的大查询过程中，io wait是相对很低的。
top - 19:29:59 up 310 days, 8:48, 2 users, load average: 1.04, 0.53, 0.20
Tasks: 488 total, 2 running, 486 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.6%us, 0.4%sy, 0.0%ni, 96.7%id, 1.3%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 132050764k total, 131731368k used, 319396k free, 789588k buffers
Swap: 16771776k total, 399208k used, 16372568k free, 125815688k cached
使用MegaCli来查看一下SSD的配置信息
/opt/MegaRAID/MegaCli/MegaCli64 -PDList -aAll -NoLog |less
Raw Size: 745.211 GB [0x5d26ceb0 Sectors]
Non Coerced Size: 744.711 GB [0x5d16ceb0 Sectors]
Coerced Size: 744.625 GB [0x5d140000 Sectors]
Sector Size: 0
Firmware state: Online, Spun Up
Device Firmware Level: 0270
Shield Counter: 0
Successful diagnostics completion on : N/A
SAS Address(0): 0x4433221102000000
Connected Port Number: 2(path0)
Inquiry Data: BTTV428102EV800JGN INTEL SSDSC2BA800G3 5DV10270
FDE Capable: Not Capable
FDE Enable: Disable
Secured: Unsecured
Locked: Unlocked
Needs EKM Attention: No
Foreign State: None
Device Speed: 6.0Gb/s
Link Speed: 6.0Gb/s
Media Type: Solid State Device
Drive: Not Certified
Drive Temperature :26C (78.80 F)
PI Eligibility: No
Drive is formatted for PI information: No
PI: No PI
Drive's NCQ setting : N/A
Port-0 :
Port status: Active
Port's Linkspeed: 6.0Gb/s
Drive has flagged a S.M.A.R.T alert : No
这样问题就基本能够定位了，在备库2中运行，发现效率确实好了很多。所以就给开发同学提出了四点建议。
#1建议语句修改为
select c.cn as cn,c.uin from cn_bind c where enabled='Y' group by c.cn,c.uin having count(c.cn) >1;
#帮忙查看一下调用逻辑是否有问题
同时之前的查询会有多个会话（差不多8个这样的会话）进行同样的查询，请帮忙看一下是否存在调用问题，
数据库用户 TEST_SHINK 客户端用户为：webadmin
#在备库2（5.23）中进行同类查询。
因为存在两个备库，在备库2(5.23 使用SSD)上对比发现，同样的语句在备库2上效果要好很多，可以使用备库2来做此类查询
数据库IP 为10.127.5.23:1523 数据库为：testmdb 需要开通防火墙权限，请备注。
#降低查询频率
查看数据查询的情况，时间可以从37分钟改进到近12分钟左右，可以看到目前为止不存在这类问题（查询结果为0），是否可以考虑把执行频率也降低，比如一周一次。
--SSD的备库
SQL> select count(*) from (select c.cn as cn,c.uin from test_cn_bind c where enabled='Y' group by c.cn,c.uin having count(c.cn) >1);
COUNT(*)
----------
0
Elapsed: 00:12:11.73
--普通盘的备库
SQL> select count(*) from (select c.cn as cn,c.uin from test_cn_bind c where enabled='Y' group by c.cn,c.uin having count(c.cn) >1);
COUNT(*)
----------
0
Elapsed: 00:37:08.85
所以这么一个案例从头到尾详细分析了一遍，感觉还是牵扯到方方面面。对于抖动的情况，在业务需求之外的，还是需要引起重视。我喜欢跟开发的同事说，报错不是关键，没有报错不一定没问题。