Re: 11g: unable to use rman as side effect of having optimizer_capture_sql_plan_baselines=TRUE (ORA-00376)
Date: Mon, 17 Aug 2009 08:44:18 -0700 (PDT)
Great post!! It's funny how the optimizer has it's hooks in EVERYTHING, even backup and recovery!!
Robert G. Freeman
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_at_gmail.com> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> 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 18.104.22.168 + 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 22.214.171.124.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:
workaround was to set optimizer_capture_sql_plan_baselines to FALSE and then restart
Thanks to Oracle support for this suggestion that worked.