Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.