Just experienced a really strange issue when opnening a standby database read-only. The instructions on how to open your standby database read-only are pretty clear and pretty straightforwards:
Cancel Redo Apply: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Open the database for read-only access: SQL> ALTER DATABASE OPEN;
To be clear, and a verbatim quote from the 10.2 documentation:
You do not need to shut down the instance to open it for read-only access.
I did this on an up-to-date 10.2.0.4 physical standby database (on RHEL 4 U3), but when I tried to log into the Standby using plain old sqlplus this is what I saw:
(jason@jason)$ sqlplus system@STANDBY SQL*Plus: Release 10.2.0.4.0 - Production on Tue Feb 3 07:57:09 2009 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Enter password: ERROR: ORA-06554: package DBMS_STANDARD must be created before using PL/SQL ERROR: ORA-06554: package DBMS_STANDARD must be created before using PL/SQL Error accessing package DBMS_APPLICATION_INFO
This standby was providing the customer with their business continuity and I was opening it to ensure read-only applications kept running while the customer performed a software upgrade to their internal systems. This was not just an issue related to sqlplus. We hooked up the read-only applications to the standby and they kept logging the exact same ORA-06554 error.
So I started thinking this standby was toast. So to metalink and the wonderfully redundant document 5944239.8. It basically says there is a bug, 5944239, and that 10.2.0.4 is known to be affected on all/most platforms. The issue is fixed in 11.2. And that is it. It has absolutely zero ammount of detail. No idea what causes it, no clue as to what to do should you encounter it! Utterly, utterly astonishing.
One thing I can tell you, is what NOT to do. While looking on metalink there were a few documents about that particular ORA error number that were pretty old, dating back to 2002, and 2001. If you are not on those versions, then ignore those notes. They talk about re-running catproc.sql to rebuild the dictionary, which of course should create the DBMS_STANDARD package.
So I basically thought the dictionary of the standby was corrupt/broken. The applications which recieve 10’s of Millions of queries per day were about to go offline for the customers so I was sorely tempted to say the standby was shafted lets just open the standby read-write and run catproc.sql and see if we can keep the apps going. Of course this was kissing business-continuity for this database goodbye, but then I thought the standby was shafted anyway.
Thankfully before going for the nuclear option I decided to shutdown the standby instance and then open it read-only again to see if that had any effect. Of course, this did the trick and the applications connected without issue and kept running.
But of course as the documentation says, you do not need to shutdown your standby before opening it read-only. I know what I’ll be doing in the future with this 10.2.0.4 standby.