History | Log In     View a printable version of the current page.  
   HOME       BROWSE PROJECT       FIND ISSUES   
    QUICK SEARCH:  
Issue Details (XML | Word | Printable)

Key: QA-37
Type: Oracle - Administration Oracle - Administration
Status: Closed Closed
Resolution: Answered
Priority: Major Major
Assignee: ubTools Support
Reporter: ubTools Support
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Questions & Answers

"ORA-01187: cannot read from file" in one of the RAC Node.

Created: 08/May/08 12:27 PM   Updated: 12/May/08 12:37 PM
Fix Version/s: None

Product Version: Oracle 10g 10.2.0.3
Operating System: Linux
Operating System Version: RHEL 4


 Description  « Hide
The one of RAC Nodes encounters the following error codes while no problem occurs on the other node:

From ALERT LOG:

Generic Alert Log Error May 2, 2008 11:21:30 PM ORA-12012: error on auto execute of job 8913
ORA-01187: cannot read from file ORA-01187: cannot read from file 96 because it failed verification tests
ORA-01110: data file 96: '/u64/oradata/DMSDB/LBPRD_IDX_SKU_005DMSDB.dbf'
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1347
ORA-06512: at "SYS.DBMS_SPACE", line 1566
because it failed verification tests
Trace File:  /u00/app/oracle/oracle/admin/DMSDB/bdump/dmsdb2_j000_21660.trc

From dmsdb2_j000_21660.trc:

*** 2008-04-07 23:03:34.750
GATHER_STATS_JOB: GATHER_TABLE_STATS('"LOADOWNER"','"MARKEDPRODUCT"','"MARKEDPRODUCT_20080406"', ...)
ORA-01187: cannot read from file 88 because it failed verification tests
ORA-01110: data file 88: '/u64/oradata/DMSDB/MRK_IDX_004DMSDB.dbf'


 All   Comments   Change History      Sort Order: Ascending order - Click to sort in descending order
[ Permlink | « Hide ]
ubTools Support - 08/May/08 12:32 PM
From the trace file, the problem can be reproduced by DBMS_STATS.GATHER_TABLE_STATS().

The following strace output will give the system calls:

strace -f -o strace.log sqlplus / as sysdba <<EOF
exec DBMS_STATS.GATHER_TABLE_STATS('<owner>','<tableName>','<partitionName',1,DEGREE=>2);
exit;
EOF

[ Permlink | « Hide ]
ubTools Support - 08/May/08 12:35 PM
An excerpt from strace.log that the db files were opened with O_DIRECT flag: 
16822 open("/u51/oradata/DMSDB/system001DMSDB.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LAR
GEFILE) = 14
...
16822 open("/u31/oradata/DMSDB/ctl1DMSDB.ctl", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFIL
E) = 15
16822 open("/u32/oradata/DMSDB/ctl2DMSDB.ctl", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFIL
E) = 16
16822 open("/u33/oradata/DMSDB/ctl3DMSDB.ctl", O_RDWR|O_SYNC|O_DIRECT|O_LARGEFIL
E) = 17
16822 open("/u52/oradata/DMSDB/undotbs002DMSDB2.dbf", O_RDWR|O_SYNC|O_DIRECT|O_L
ARGEFILE) = 18
...
16822 open("/u70/oradata/DMSDB/TEMPDMSDB_002.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LARG
EFILE) = 20
16822 open("/u70/oradata/DMSDB/TEMPDMSDB_002.dbf", O_RDWR|O_DIRECT|O_LARGEFILE)
= 21
...
16822 open("/u52/oradata/DMSDB/sysaux001DMSDB.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LAR
GEFILE) = 28
16822 open("/u51/oradata/DMSDB/system002DMSDB.dbf", O_RDWR|O_SYNC|O_DIRECT|O_LAR
GEFILE) = 29
16822 open("/u55/oradata/DMSDB/undotbs001DMSDB1.dbf", O_RDWR|O_SYNC|O_DIRECT|O_L
ARGEFILE) = 30
16822 open("/u61/oradata/DMSDB/MRK_IDX_001DMSDB.dbf", O_RDWR|O_SYNC|O_DIRECT|O_L
ARGEFILE) = 31
...

The second excerpt from strace.log that the db files were opened without O_DIRECT flag:

16822 open("/u65/oradata/DMSDB/dor_data_200805_001_DMSDB.dbf", O_RDWR|O_SYNC|O_L
ARGEFILE) = 19
16822 open("/u65/oradata/DMSDB/dor_data_200805_002_DMSDB.dbf", O_RDWR|O_SYNC|O_L
ARGEFILE) = 27
...
16822 open("/u64/oradata/DMSDB/MRK_IDX_004DMSDB.dbf", O_RDWR|O_SYNC|O_LARGEFILE)
 = 32

[ Permlink | « Hide ]
ubTools Support - 08/May/08 12:52 PM - edited
The customer uses OCFS2.

O_DIRECT flag of open() system call bypasses File System(FS) cache; and DISK-IO occurs between user address space and disk.

OCFS opens dbfiles with O_DIRECT flag to eliminate inconsistency among FS caches of nodes. Since RAC provides consistency among SGAs and there will be no db buffers is FS cache, no consistency problem occurs.

From Ref: Oracle Note:391771.1:

48. Any special flags to run Oracle RAC?
OCFS2 volumes containing the Voting diskfile (CRS), Cluster registry (OCR),
Data files, Redo logs, Archive logs and Control files must be mounted
with the datavolume and nointr mount options.
The datavolume option ensures that the Oracle processes opens these files
with the o_direct flag.
The nointr option ensures that the ios are not interrupted by signals.

# mount -o datavolume,nointr -t ocfs2 /dev/sda1 /u01/db

The customer was not using the datavolume,nointr option. After mounting with the datavolume,nointr, the problem has been solved.


Change by ubTools Support - 08/May/08 12:52 PM
Field Original Value New Value
Status Open [ 1 ] Closed [ 6 ]
Resolution Answered [ 10 ]

Change by ubTools Support - 11/May/08 08:22 AM
Resolution Answered [ 10 ]
Status Closed [ 6 ] Reopened [ 4 ]

Change by ubTools Support - 11/May/08 08:23 AM
Status Reopened [ 4 ] Closed [ 6 ]
Resolution Answered [ 10 ]

Change by ubTools Support - 12/May/08 12:36 PM
Resolution Answered [ 10 ]
Status Closed [ 6 ] Reopened [ 4 ]

Change by ubTools Support - 12/May/08 12:37 PM
Description The one of RAC Node encounters the following error codes while no problem occurs on the other node:

From ALERT LOG:
{noformat}
Generic Alert Log Error May 2, 2008 11:21:30 PM ORA-12012: error on auto execute of job 8913
ORA-01187: cannot read from file ORA-01187: cannot read from file 96 because it failed verification tests
ORA-01110: data file 96: '/u64/oradata/DMSDB/LBPRD_IDX_SKU_005DMSDB.dbf'
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1347
ORA-06512: at "SYS.DBMS_SPACE", line 1566
because it failed verification tests
Trace File: /u00/app/oracle/oracle/admin/DMSDB/bdump/dmsdb2_j000_21660.trc
{noformat}

From dmsdb2_j000_21660.trc:

{noformat}
*** 2008-04-07 23:03:34.750
GATHER_STATS_JOB: GATHER_TABLE_STATS('"LOADOWNER"','"MARKEDPRODUCT"','"MARKEDPRODUCT_20080406"', ...)
ORA-01187: cannot read from file 88 because it failed verification tests
ORA-01110: data file 88: '/u64/oradata/DMSDB/MRK_IDX_004DMSDB.dbf'
{noformat}
 The one of RAC Nodes encounters the following error codes while no problem occurs on the other node:

From ALERT LOG:
{noformat}
Generic Alert Log Error May 2, 2008 11:21:30 PM ORA-12012: error on auto execute of job 8913
ORA-01187: cannot read from file ORA-01187: cannot read from file 96 because it failed verification tests
ORA-01110: data file 96: '/u64/oradata/DMSDB/LBPRD_IDX_SKU_005DMSDB.dbf'
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1347
ORA-06512: at "SYS.DBMS_SPACE", line 1566
because it failed verification tests
Trace File: /u00/app/oracle/oracle/admin/DMSDB/bdump/dmsdb2_j000_21660.trc
{noformat}

From dmsdb2_j000_21660.trc:

{noformat}
*** 2008-04-07 23:03:34.750
GATHER_STATS_JOB: GATHER_TABLE_STATS('"LOADOWNER"','"MARKEDPRODUCT"','"MARKEDPRODUCT_20080406"', ...)
ORA-01187: cannot read from file 88 because it failed verification tests
ORA-01110: data file 88: '/u64/oradata/DMSDB/MRK_IDX_004DMSDB.dbf'
{noformat}

Change by ubTools Support - 12/May/08 12:37 PM
Status Reopened [ 4 ] Closed [ 6 ]
Resolution Answered [ 10 ]


Powered by Atlassian JIRA the Professional Issue Tracker. (Standard Edition, Version: 3.12.3-#302) - Bug/feature request - Atlassian news - Contact Administrators