ORA-01157、ORA-01110無法識別/鎖定數據文件

之前在生產中遇到同樣報錯，用戶在客戶端查詢表中數據，報如下錯誤：
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 file
ORA-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.lsnr
ONLINE ONLINE testdb1
ONLINE ONLINE testdb2
ora.gsd
OFFLINE OFFLINE testdb1
OFFLINE OFFLINE testdb2
ora.net1.network
ONLINE ONLINE testdb1
ONLINE ONLINE testdb2
ora.ons
ONLINE ONLINE testdb1
ONLINE ONLINE testdb2
ora.registry.acfs
OFFLINE OFFLINE testdb1
OFFLINE OFFLINE testdb2

Cluster Resources

ora.test.db
1 ONLINE ONLINE testdb1 Open
2 OFFLINE OFFLINE Instance Shutdown
ora.testdb1.vip
1 ONLINE ONLINE testdb1
ora.testdb2.vip
1 ONLINE ONLINE testdb2
ora.cvu
1 ONLINE ONLINE testdb1
ora.oc4j
1 OFFLINE OFFLINE

2.offline oradata04
[oracle@testdb2:/oracle] exit
testdb2:/#varyoffvg oradata04

3.重啟實例2
testdb2:/#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.lsnr
ONLINE ONLINE testdb1
ONLINE ONLINE testdb2
ora.gsd
OFFLINE OFFLINE testdb1
OFFLINE OFFLINE testdb2
ora.net1.network
ONLINE ONLINE testdb1
ONLINE ONLINE testdb2
ora.ons
ONLINE ONLINE testdb1
ONLINE ONLINE testdb2
ora.registry.acfs
OFFLINE OFFLINE testdb1
OFFLINE OFFLINE testdb2

Cluster Resources

ora.test.db
1 ONLINE ONLINE testdb1 Open
2 ONLINE ONLINE testdb2 Open
ora.testdb1.vip
1 ONLINE ONLINE testdb1
ora.testdb2.vip
1 ONLINE ONLINE testdb2
ora.cvu
1 ONLINE ONLINE testdb1
ora.oc4j
1 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 Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
AA TABLE
B 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 file
ORA-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 file
ORA-01110: data file 8: '/dev/rtest001vg04'

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

5.online vg，查看lv狀態為closed
[oracle@testdb2:/oracle] lsvg
rootvg
altinst_rootvg
hbvg
oraclevg
oradata01
oradata02
oradata03
archvg1
archvg2
oradata04
[oracle@testdb2:/oracle] lsvg -l oradata04
0516-010 : Volume group must be varied on; use varyonvg command.
[oracle@cqtestdb2:/oracle] varyonvg -c oradata04
ksh: varyonvg: cannot execute
[oracle@cqtestdb2:/oracle] exit
cqtestdb2:/#varyonvg -c oradata04
cqtestdb2:/#lsvg -l oradata04
oradata04:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
test001vg04 jfs2 40 40 1 closed/syncd N/A
test002vg04 jfs2 40 40 1 closed/syncd N/A
test003vg04 jfs2 40 40 1 closed/syncd N/A
test004vg04 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 Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and 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 file
ORA-01110: data file 8: '/dev/rtest001vg04'

7.執行alter system check datafiles;
SQL> conn / as sysdba
Connected.

SQL> alter system check datafiles;

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

8.查看lv狀態已經open
[oracle@cqtestdb2:/oracle] lsvg -l oradata04
oradata04:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
test001vg04 jfs2 40 40 1 open/syncd N/A
test002vg04 jfs2 40 40 1 closed/syncd N/A
test003vg04 jfs2 40 40 1 closed/syncd N/A
test004vg04 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 Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select count(*) from aa;

COUNT(*)
----------
1924