Be Careful how you open your Physical Standby

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.

About these ads
Leave a comment

6 Comments

  1. Patrick

     /  February 3, 2009

    Wow..good one.

    What OS?

    Ok…going back to the Metalink…what in the world are they thinking…a SIMPLE, “bounce” your standby database..goodness…

    again, good one thx.

    P

    Reply
  2. Patrick

     /  February 3, 2009

    ooppss…I see from another article you’re on Linux …

    Reply
  3. jarneil

     /  February 3, 2009

    Hi Patrick,

    Thanks for reading! This issue seems to be generic to most platforms on 10.2.0.4, but yes this was running on Linux.

    Reply
  4. Jason, this is really a good catch.

    The behavior you described reminds me of an article that Tanel Poder has written about row cache internals. http://www.tanelpoder.com/files/oracle_rowcache.pdf

    Parsing a statement which references database objects makes Oracle to search for these objects in the dictionary cache (rowcache) and get object information from there. If the information is not found, a cache miss happens and Oracle does a dictionary lookup to find the objects required by the parsed statement and updates the rowcache with the relevant data.

    Now, if the database is in MOUNT mode, which is case with the standby databases, the dictionary lookup might fail because the database is closed. In that case Oracle creates an entry for this object in the row cache and marks it with EXISTS=N. (Tanel has included a script that actually checks the rowcache entries.)

    When you open the database, and try to use the same object, Oracle looks in the cache first, but since the object has EXISTS=N it returns an error.

    The solution is to flush the shared pool and force Oracle to look for the database object in the dictionary and update the rowcache with “valid” information.

    The reason for this behavior is Bug#2365821 described in Metalink Note 296235.1.

    I was able to reproduce the same issue (example below) that you have described when I tried to describe DBMS_STANDARD package before I stopped the redo apply.

    It might be the case that some PL/SQL code has been ran while the database was mounted that forced Oracle to do a dictionary lookup for DBMS_STANDARD which failed and the non-existent information was set in the rowcache.

    Example:

    SQL> desc DBMS_STANDARD
    ERROR:
    ORA-04043: object DBMS_STANDARD does not exist

    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

    Database altered.

    SQL> ALTER DATABASE OPEN;

    Database altered.

    Now lets try to log in. It looks like log in process invokes DBMS_APPLICATION_INFO package that is somehow dependant on DBMS_STANDARD package (even though I cannot find a dependency in sys.dependency$).

    SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64
    bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    (/home/oratest)–> sqlplus system

    SQL*Plus: Release 10.2.0.4.0 – Production on Tue Feb 3 14:12:40 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

    Now lets flush the shared pool.

    SQL>connect /as sysdba
    SQL> alter system flush shared_pool;

    System altered.

    SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64
    bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    (/home/ oratest)–> sqlplus system

    SQL*Plus: Release 10.2.0.4.0 – Production on Tue Feb 3 14:14:17 2009

    Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

    Enter password:

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL>

    … and the issue is gone. J

    By the way, can you reproduce the problem?

    Regards,
    Mihajlo Tekic

    P.S. I don’t know how all this code stuff will be formated.

    Reply
  5. AndyH

     /  February 4, 2009

    Sounds like you’re running oracle on windows – restart and everything works again!

    Reply
  1. Log Buffer #134: A Carnival of the Vanities for DBAs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 51 other followers

%d bloggers like this: