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

Great post!! It's funny how the optimizer has it's hooks in EVERYTHING, even 
backup and recovery!!

Robert

 Robert G. Freeman
Oracle ACE
Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide 
(Sybex)
Oracle Database 11g New Features (Oracle Press)
Portable DBA: Oracle  (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Oracle9i RMAN Backup and Recovery (Oracle Press)
Oracle9i New Features (Oracle Press)
Other various titles out of print now...
Blog: http://robertgfreeman.blogspot.com 
The LDS Church is looking for DBA's. You do have to be a Church member in
good standing. A lot of kind people write me, concerned I may be breaking
the law by saying you have to be a Church member. It's legal I promise! :-)
http://pages.sssnet.com/messndal/church/parachurch.pdf



----- Original Message ----
From: Andre van Winssen <dreveewee@xxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, August 17, 2009 8:26:10 AM
Subject: 11g: unable to use rman as side effect of having 
optimizer_capture_sql_plan_baselines=TRUE  (ORA-00376)

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
--
http://www.freelists.org/webpage/oracle-l

Other related posts: