Re: Can ORA-01013 error cause by Temp TS contention?
Date: Mon, 10 Nov 2014 15:12:08 +0100 (CET)
Message-ID: <340395435.548190.1415628729000.open-xchange_at_app01.ox.hosteurope.de>
Hi Karth,
my assumption was based on your previous description. At first i would verify, that we (and your DBAs) are talking about the same before implementing any changes. The easiest way would be to generate a raw SQL trace from a failing session or use Snapper v4 ( http://blog.tanelpoder.com/files/scripts/snapper.sql ), which got RAC support nowadays.
If your issue is really caused by TS/SS enqueue (and DFS lock handle), i would go for that separate temp tablespace for reporting purpose as you already have more than the suggested 1 temp file per RAC instance.
You don't need to move the reporting to your DG instance anyway. You just need to create a SINGLETON service (in case of policy managed RAC databases), a reporting user with that separate temp tablespace and run the reports over that SINGLETON service. (disregarding PX right now, as it may need further adjustments)
Best Regards
Stefan Koehler
Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: _at_OracleSK
> Karth Panchan <keyantech_at_gmail.com> hat am 10. November 2014 um 14:39 geschrieben:
>
>
> Stefan
>
> Thanks very much for your response with details.
>
> I forget to mentioned we are using ASM and our database have 10 Temp db files with each 30720 MB in size.
>
> Thanks for Riyaj blog entry. Very useful for next debug session.
>
> Provided below recommendation to overcome this issue.
> Create new user for Reporting with own temp.
> Move Reporting to DataGuard instance.
>
> Meanwhile trying to find root cause.
>
> Stephan>> to answer your question we are not running behind firewall.
>
> Karth
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Nov 10 2014 - 15:12:08 CET