Monday, September 27, 2010

ORA-01110 ORA-01187: cannot read from file because it failed verification tests

Problem Description
In the physical standby database alert log file shows following errors.
Errors in file /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_m001_19676.trc:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: '+DATA/bdafisdrs/tempfile/temp'

Following is the contents found in the generated trace file.

Trace file /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_m001_19676.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing option
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: DRS-DB-01
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine: x86_64
Instance name: bdafisdc1
Redo thread mounted by this instance: 1
Oracle process number: 77
Unix process pid: 19676, image: oracle@DRS-DB-01 (M001)


*** 2010-09-19 11:35:51.595
*** SESSION ID:(103.167) 2010-09-19 11:35:51.595
*** CLIENT ID:() 2010-09-19 11:35:51.595
*** SERVICE NAME:(SYS$BACKGROUND) 2010-09-19 11:35:51.595
*** MODULE NAME:(MMON_SLAVE) 2010-09-19 11:35:51.595
*** ACTION NAME:(Autotask Slave Action) 2010-09-19 11:35:51.595

DDE: Problem Key 'ORA 1110' was flood controlled (0x5) (no incident)
ORA-01110: data file 201: '+DATA/bdafisdrs/tempfile/temp'
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 201: '+DATA/bdafisdrs/tempfile/temp'
Dump of memory from 0x0000000363A3B078 to 0x0000000363A3B3EA
...
...
...
ket_get_active: error 1187
ket_aba_main[2] : error 1187
ket_aba_slave: clearing error 1187

Cause of the Problem
Scenario 01: If your Oracle database version is 8i and you are able to activate the standby database with no errors, however, each time you try to open the standby database in read only mode it fails with the above errors, then possibly it is due to READ_ONLY_OPEN_DELAYED parameter setting.

Scenario 02:
If database files reside on an ocfs and datafiles appear to corrupt to nodes other than "node1" (instance1) in a RAC then problem happened because filesystem was mounted with the "reclaimid" option.

Solution of the Problem
Solution for Scenario 01:
Set READ_ONLY_OPEN_DELAYED parameter to false and then you will be able to open the database in read only mode.
SQL> Alter system set READ_ONLY_OPEN_DELAYED = FALSE scope=spfile;
SQL> shut immediate
SQL> startup


Solution for Scenario 02:
While mounting do not use the reclaimid mount option for "normal" mounts. This option should only be used once after having to regenerate the guid of a node after ip address change.

No comments:

Post a Comment