之前在生产中遇到同样报错，用户在客户端查询表中数据，报如下错误： Errors in file /oratrace/xxx/diag/rdbms/xxx/xxx2/trace/xxx2_dbw0_8454382.trc: ORA-01157: cannot identify/lock data file 366 - see DBWR trace fi
之前在生产中遇到同样报错，用户在客户端查询表中数据，报如下错误：Errors in file /oratrace/xxx/diag/rdbms/xxx/xxx2/trace/xxx2_dbw0_8454382.trc:ORA-01157: cannot identify/lock data file 366 - see DBWR trace fileORA-01110: data file 366: '/dev/rrpt001vg05'
这个报错是由于，2节点重启后，包含/dev/rrpt001vg05这个lv的lv没有online导致。varyonvg vg后，还是不能访问这个vg下的lv。在数据库内执行alter system check datafiles命令后，可以访问所有的数据文件。该命令一般用于rac环境中，比如说其中一个节点无法访问某一个datafile（只有这个节点无法访问，其余节点是能访问的），无法访问的这个节点上就可以使用ALTER SYSTEM CHECK DATAFILES这个命令来更新实例的sga信息，更新信息的来源 是控制文件。
先做个实验将故障重演一遍：
1.停止实例2[Oracle@testdb2:/oracle] srvctl stop instance -d CQTEST -n testdb2[oracle@testdb2:/oracle] crs
NAME TARGET STATE SERVER STATE_DETAILS
Local Resources
ora.LISTENER.lsnrONLINE ONLINE testdb1 ONLINE ONLINE testdb2 ora.gsdOFFLINE OFFLINE testdb1 OFFLINE OFFLINE testdb2 ora.net1.networkONLINE ONLINE testdb1 ONLINE ONLINE testdb2 ora.onsONLINE ONLINE testdb1 ONLINE ONLINE testdb2 ora.registry.acfsOFFLINE OFFLINE testdb1 OFFLINE OFFLINE testdb2
Cluster Resources
ora.test.db1 ONLINE ONLINE testdb1 Open 2 OFFLINE OFFLINE Instance Shutdown ora.testdb1.vip1 ONLINE ONLINE testdb1 ora.testdb2.vip1 ONLINE ONLINE testdb2 ora.cvu1 ONLINE ONLINE testdb1 ora.oc4j1 OFFLINE OFFLINE
2.offline oradata04[oracle@testdb2:/oracle] exittestdb2:/#varyoffvg oradata04
3.重启实例2testdb2:/#su - oracle[oracle@testdb2:/oracle] srvctl start instance -d CQTEST -n cqtestdb2[oracle@testdb2:/oracle] crs
NAME TARGET STATE SERVER STATE_DETAILS
Local Resources
ora.LISTENER.lsnrONLINE ONLINE testdb1 ONLINE ONLINE testdb2 ora.gsdOFFLINE OFFLINE testdb1 OFFLINE OFFLINE testdb2 ora.net1.networkONLINE ONLINE testdb1 ONLINE ONLINE testdb2 ora.onsONLINE ONLINE testdb1 ONLINE ONLINE testdb2 ora.registry.acfsOFFLINE OFFLINE testdb1 OFFLINE OFFLINE testdb2
Cluster Resources
ora.test.db1 ONLINE ONLINE testdb1 Open 2 ONLINE ONLINE testdb2 Open ora.testdb1.vip1 ONLINE ONLINE testdb1 ora.testdb2.vip1 ONLINE ONLINE testdb2 ora.cvu1 ONLINE ONLINE testdb1 ora.oc4j1 OFFLINE OFFLINE
4.登录数据查询该文件上的表[oracle@testdb2:/oracle] sqlplus aa/aa
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 9 16:27:24 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options
SQL> select * from tab;
TNAME TABTYPE CLUSTERID------------------------------ ------- ----------AA TABLEB TABLE
SQL> select count() from aa;select count() from aa*ERROR at line 1:ORA-01157: cannot identify/lock data file 8 - see DBWR trace fileORA-01110: data file 8: '/dev/rtest001vg04'
SQL> select from b;select from b*ERROR at line 1:ORA-01157: cannot identify/lock data file 8 - see DBWR trace fileORA-01110: data file 8: '/dev/rtest001vg04'
SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options
5.online vg，查看lv状态为closed[oracle@testdb2:/oracle] lsvgrootvgaltinst_rootvghbvgoraclevgoradata01oradata02oradata03archvg1archvg2oradata04[oracle@testdb2:/oracle] lsvg -l oradata040516-010 : Volume group must be varied on; use varyonvg command.[oracle@cqtestdb2:/oracle] varyonvg -c oradata04ksh: varyonvg: cannot execute[oracle@cqtestdb2:/oracle] exitcqtestdb2:/#varyonvg -c oradata04cqtestdb2:/#lsvg -l oradata04oradata04:LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINTtest001vg04 jfs2 40 40 1 closed/syncd N/Atest002vg04 jfs2 40 40 1 closed/syncd N/Atest003vg04 jfs2 40 40 1 closed/syncd N/Atest004vg04 jfs2 40 40 1 closed/syncd N/A
6.加载vg后，登录数据库查看，发现还是不能访问相关数据文件。cqtestdb2:/#su - oracle[oracle@cqtestdb2:/oracle] sqlplus aa/aa
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 9 16:28:50 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options
SQL> select count() from aa;select count() from aa*ERROR at line 1:ORA-01157: cannot identify/lock data file 8 - see DBWR trace fileORA-01110: data file 8: '/dev/rtest001vg04'
7.执行alter system check datafiles;SQL> conn / as sysdbaConnected.
SQL> alter system check datafiles;
System altered.
SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options
8.查看lv状态已经open[oracle@cqtestdb2:/oracle] lsvg -l oradata04oradata04:LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINTtest001vg04 jfs2 40 40 1 open/syncd N/Atest002vg04 jfs2 40 40 1 closed/syncd N/Atest003vg04 jfs2 40 40 1 closed/syncd N/Atest004vg04 jfs2 40 40 1 closed/syncd N/A[oracle@cqtestdb2:/oracle] sqlplus aa/aa
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 9 16:29:41 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP, Data Miningand Real Application Testing options
SQL> select count(*) from aa;
COUNT(*)----------1924
更多Oracle相关信息见Oracle 专题页面 https://www.558idc.com/topicnews.aspx?tid=12
