Data files are not recognized / locked by ORA-01157 and ORA-01110
Previously, the same error was reported in production. The user queried the data in the client table and reported the following error:
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'
This error is due to the fact that the lv containing the lv of / dev/rrpt001vg05 does not have online after the 2 nodes are restarted. After varyonvg vg, you still cannot access the lv under this vg. After executing the alter system check datafiles command in the database, you can access all the data files.
This command is generally used in the rac environment. For example, one of the nodes cannot access a certain datafile (only this node cannot access it, and the other nodes can). On the inaccessible node, you can use the command ALTER SYSTEM CHECK DATAFILES to update the sga information of the instance. The source of the update information is the control file.
Let's do an experiment to repeat the fault:
1. Stop instance 2
[oracle@testdb2:/oracle] srvctl stop instance-d CQTEST-n testdb2
[oracle@testdb2:/oracle] crsNAME TARGET STATE SERVER STATE_DETAILSLocal Resourcesora.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 testdb2Cluster 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. Restart instance 2
Testdb2:/#su-oracle
[oracle@testdb2:/oracle] srvctl start instance-d CQTEST-n cqtestdb2
[oracle@testdb2:/oracle] crsNAME TARGET STATE SERVER STATE_DETAILSLocal Resourcesora.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 testdb2Cluster 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. Log in data to query the table on this file
[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, check that the lv status is 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. After loading the vg, log in to the database and find that the relevant data files still cannot be accessed.
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. Execute 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. Check that the lv status has been 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
