Re: Data Guard Hanging
- From: "Alex Fatkulin" <afatkulin@xxxxxxxxx>
- To: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- Date: Fri, 5 Dec 2008 12:23:25 -0500
Tom,
v$streams_apply_server.message_sequence -- does this move or not (for apply
server which is executing transaction)?
Also, take a look at your v$streams_transaction to get an idea what kind of
transaction you currently have.
v$streams_apply_reader.total_messages_[dequeued/spilled] -- does that moves
anywhere?
On Fri, Dec 5, 2008 at 10:35 AM, Mercadante, Thomas F (LABOR) <
Thomas.Mercadante@xxxxxxxxxxxxxxxxx> wrote:
> Sql_id from v$session is null;
>
>
>
> Yes I enabled trace and all I can see are a couple of alter session sql
> statements (like "skip disabled indexes") and then the "rdbms ipc" messages
> below. It does not seem to be doing anything.
>
>
> ------------------------------
>
> *From:* Alex Fatkulin [mailto:afatkulin@xxxxxxxxx]
> *Sent:* Friday, December 05, 2008 10:29 AM
> *To:* Mercadante, Thomas F (LABOR)
> *Subject:* Re: Data Guard Hanging
>
>
>
> Ok, EXECUTING TRANSACTION is a status of APPLY process, that is --
> v$streams_apply_process.status
>
> Can you please get SID from v$streams_apply_process where status is EXECUTE
> TRANSACTION and then check v$session.sql_id for that SID?
>
> Also, am I understanding correctly that you have enabled trace for the
> APPLY server with a status of EXECUTE TRANSACTION?
>
> On Fri, Dec 5, 2008 at 9:52 AM, Mercadante, Thomas F (LABOR) <
> Thomas.Mercadante@xxxxxxxxxxxxxxxxx> wrote:
>
> Alex,
>
>
>
> The sql statement that gets returned when I run that query says "EXECUTING
> TRANSACTION'. Not very helpful.
>
> I have traced the APPLY process per Oracle instructions. The only thing I
> am seeing is:
>
>
>
> *** 2008-12-05 09:48:10.795
>
> WAIT #0: nam='rdbms ipc message' ela= 81 timeout=500 p2=0 p3=0 obj#=-1
> tim=40075115529914
>
> *** 2008-12-05 09:49:09.807
>
> WAIT #0: nam='rdbms ipc message' ela= 74 timeout=500 p2=0 p3=0 obj#=-1
> tim=40075173159416
>
> *** 2008-12-05 09:50:06.069
>
> WAIT #0: nam='rdbms ipc message' ela= 86 timeout=500 p2=0 p3=0 obj#=-1
> tim=40075228102633
>
>
>
> It just looks like it is waiting on something. The Log File Details page
> in Grid shows that log files that need to be applied. Whenever we stop and
> start the Apply process, is restarts back to the same place (Log # 167).
>
>
>
> So something is wacked (a technical term).
>
>
> Thanks for any hints you can give.
>
>
>
> Tom
>
>
>
>
> ------------------------------
>
> *From:* Alex Fatkulin [mailto:afatkulin@xxxxxxxxx]
> *Sent:* Friday, December 05, 2008 9:33 AM
> *To:* Mercadante, Thomas F (LABOR)
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *Subject:* Re: Data Guard Hanging
>
>
>
> Tom,
>
> if I understood correctly, in case you take sid from v$streams_apply_server
> and join it to v$session the sql_id is empty?
>
> if it is, try enable sql trace with wait events and see what session is
> doing. starce/truss might provide some low-level related information as
> well (though sql trace should be enough).
>
> On Fri, Dec 5, 2008 at 8:36 AM, Mercadante, Thomas F (LABOR) <
> Thomas.Mercadante@xxxxxxxxxxxxxxxxx> wrote:
>
> All,
>
> I am running a Data Guard with a Logical Standby on Aix V 10.2.0.4.
>
> This is our first run at this so we are still learning.
>
>
> During my first large data load in the primary database, I ran out of room
> in my tablespaces. The load died and I restarted it after fixing the
> tablespaces. I then noticed that the DDL did not change the tablespaces in
> the Logical Standby. I realized that this does not happen automatically
> because
> the Apply died. I changed the tablespaces in the Standby and restarted
> Apply. Everything seemed to work fine. The data all got propagated over.
> But now the Apply process seems to be hung – or very slow. We are about
> 150 archivelogs behind. All of the monitoring sql's that Oracle Metalink
> gives us shows that the apply process is just running "EXECUTE
> TRANSACTION" – I can't see any sql statements being run.
>
> Anybody seen anything like this? We have an SR submitted with Oracle and
> the have been actively asking questions but nothing so far.
>
>
> Thanks
>
>
>
> Tom
>
>
>
>
> --
> Alex Fatkulin,
> http://www.linkedin.com/in/alexfatkulin
>
>
>
>
> --
> Alex Fatkulin,
> http://www.linkedin.com/in/alexfatkulin
>
--
Alex Fatkulin,
http://www.linkedin.com/in/alexfatkulin
Other related posts:
