您好,登录后才能下订单哦!
之前在生产中遇到同样报错,用户在客户端查询表中数据,报如下错误:
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信息,更新信息的来源 是控制文件。
先做个实验将故障重演一遍:
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
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
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。