ORA-01157、ORA-01110无法识别/锁定数据文件

发布时间:2020-03-16 07:39:16 作者:yoyo918
来源:网络 阅读:306

之前在生产中遇到同样报错,用户在客户端查询表中数据,报如下错误:
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

推荐阅读:
  1. 如何配置sysctl.conf文件来优化系统
  2. css如何改变hr标签颜色

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

ora-01157 ora-01110 ora-

上一篇:搭建苹果超级签名系统需要准备的工作

下一篇:巨杉Tech | SequoiaDB数据域及存储规划

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》