11g: unable to use rman as side effect of having optimizer_capture_sql_plan_baselines=TRUE (ORA-00376)

From: Andre van Winssen <dreveewee_at_gmail.com>
Date: Mon, 17 Aug 2009 16:26:10 +0200
Message-ID: <9b46ac490908170726j57636461j444c8aafdc2f3178_at_mail.gmail.com>

Hi list members,

just want to make you aware of the following issue that might occur when optimizer_capture_sql_plan_baselines is set to TRUE in 11g. My case was RAC with 11.1.0.7 + cpuJuly2009 + Linux_x86_64

At some point I noticed a ORA-376 appearing and all dictionary queries failing Even the use of rman with nocatalog failed:

mad> rman target / nocatalog

Recovery Manager: Release 11.1.0.7.0 - Production on Mon Aug 17 10:09:17 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed RMAN-06003: ORACLE error from target database: ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '+DATA/jaja/datafile/sysaux.275.693506063'
.

running rman with debug turned on (rman target / nocatalog debug trace=rman.trc) showed it was failing on   select decode(archiver, 'FAILED', 1, 0) into :archstuck from v$instance:

DBGSQL:          sqlcode = 376 [15:50:45.008]
DBGSQL:       error: ORA-00376: file 2 cannot be read at this time
[15:50:45.008] (krmkosqlerr)
DBGSQL:       ORA-01110: data file 2:

'+DGRP_DATA/jaja/datafile/sysaux.275.693506063' (krmkosqlerr)
.

workaround was to set optimizer_capture_sql_plan_baselines to FALSE and then restart
.

Thanks to Oracle support for this suggestion that worked.

Regards,
Andre

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Aug 17 2009 - 09:26:10 CDT