• notice
  • Congratulations on the launch of the Sought Tech site

ORA-01157 and ORA-01110 cannot recognize/lock the data file

Previously encountered the same error in production, the user queries the data in the client table, and the following error is reported:
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 caused by the fact that the lv containing the lv of /dev/rrpt001vg05 is not online after the 2 nodes are restarted. After varyonvg vg, still can't access the lv under this vg. After executing the alter system check datafiles command in the database, all data files can be accessed.
This command is generally used in the rac environment.For example, one of the nodes cannot access a certain datafile (only this node is inaccessible, and the rest of the nodes can be accessed).On the inaccessible node, you can use the ALTER SYSTEM CHECK DATAFILES command to Update the sga information of the instance.The source of the updated information is the control file.

First do an experiment to repeat the failure again:

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.Login data query the table on the 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,查看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.After loading the vg, log in to the database to view, and found that the relevant data files are still not accessible.
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


Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+