Issue #12 new

Problem with RAC (11g) and "ALTER SESSION"

Josh Smeaton
created an issue

I've just began using oraclepool in an internal production site, and am receiving the following error about 2 or 3 times per day.

Exception: ORA-25408: can not safely replay call due to query:ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD' NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'

The above is run so that django doesn't have to pass a format string in for each date/datetime/timestamp query. During a RAC cutover, the above error message is displayed.

http://docs.oracle.com/cd/E11882_01/appdev.112/e10764/performance.htm describes why this happens. Basically, a SET SESSION (obviously) doesn't persist through a switch.

The fix should be to detect the error message, and replay the ALTER SESSION.

Are you running a RAC environment, and have you seen this before? If you don't have access to RAC, I can probably fork/change/test, so let me know.

Comments (5)

  1. Ed Crewe repo owner

    (Reply via Ed.C...@bristol.ac.uk):

    Hi Josh,

    Thanks for the input wrt. this, unfortunately I don't have access to a RAC environment in my workplace, so I would be grateful if you would look into the issue. I have another outstanding fork which I need to merge as well, so this may be a good time to contribute a fix.

    From the link you have provided, replaying alter session may be all thats required.

    However if RAC is essentially starting a connection to a new database as failover, then it may be simpler to just expire the connection when the TAF notify occurs.

    Then a fresh one can be created in the pool when required and that will run all the initialisation code anyway, including the alter session.

    From (a very quick) Google of this, I found this presentation from someone at Pixar which suggests that RAC and the TAF notify work fine with cx_Oracle - which django-oraclepool is a wrapper for.

    But that you should add code to check for the oracle errors that indicate TAF to help control your connection creation / expiry.

    NB: Of course whatever fix works that is the simplest is probably best. If this is only happening a few times a day then the new connection overhead, will not be an issue though.

    Thanks, Ed


    Ed Crewe ( Web Developer, ITS R&D ) ed.crewe@bris.ac.uk University of Bristol IT Services +44 117 3314236 8-10 Berkeley Square, Bristol, BS8 1HH http://www.edcrewe.com

  2. Josh Smeaton reporter

    I glanced through that presentation too, which led me to what the actual problem was. I think you're right about expiring and creating the new connection. I'll try to work on a fix during the week, but getting time to test next week might be challenging. I'll see what I can do though.

    Cheers

  3. Josh Smeaton reporter

    Just a heads up, I had a look into this some more today, and it appears that it's only happening when django-debug-toolbar sql panel is enabled. I assume it has something to do with the way it wraps the cursor.

  4. Ed Crewe repo owner

    Hi,

    Given that this is only an issue with the debug toolbar and that the toolbar should never be used in production, I have downgraded this as a 'nice to have' i.e. a trivial priority enhancement.

    Ed

  5. Josh Smeaton reporter

    (Reply via josh...@gmail.com):

    Hi, Totally agree with you. Some added information though - the error manifests nearly every request with the debug toolbar sql panel enabled, so I don't think it's the cluster switchover causing the problem at all.

    Regards,

  6. Log in to comment