Online reorg issue: waiting due to active cursor...

[former_member84399]

Dear all,

I am doing an online reorganisation with brtools and in the log file I see the following messages

BR0280I BRSPACE thread 1: time stamp: 2007-07-24 12.28.13

BR1200I Thread 1: drop of interim table SAPR3.DYNPLOAD#$ waiting due to active cursor...

BR0280I BRSPACE thread 2: time stamp: 2007-07-24 12.28.22

BR1200I Thread 2: drop of interim table SAPR3.DYNPSOURCE#$ waiting due to active cursor...

BR0280I BRSPACE thread 1: time stamp: 2007-07-24 12.30.10

BR1200I Thread 1: drop of interim table SAPR3.DYNPLOAD#$ waiting due to active cursor...

BR0280I BRSPACE thread 2: time stamp: 2007-07-24 12.30.19

BR1200I Thread 2: drop of interim table SAPR3.DYNPSOURCE#$ waiting due to active cursor...

My reorg hasn't moved at all for 30 min now. Any suggestions on what I can do?

Many thanks

Andreas

[Former Member · Accepted solution]

The BR1200I waits were introduced because without waiting for active cursors it could happen (in rare cases) that the DROP of the source table results in ORA-08103 (object no longer exists) errors in the running system. But obviously there are some functional (and also performance) problems with the check and so it will be disabled in the next BRSPACE patches (6.40 (46); 7.00 (29)).

[former_member84399 · Accepted solution]

Martin, thank you very much for this. Assigned the points

Regards

Andreas

[Former Member]

hi,

determine the currently opened cursors which are in use by a user session using the following query:

SELECT sql_text, address, hash_value

FROM v$open_cursor a , v$session b

WHERE a.sid = b.sid

AND a.address = b.sql_address

AND a.hash_value = b.sql_hash_value

AND b.status = 'ACTIVE'

vinod

[former_member84399]

Hello Vinod,

Thank you for this useful query. I get the following output:

SQL_TEXT ADDRESS

-

---

-

---

HASH_VALUE

-

---

SELECT sql_text, address, hash_value FROM v$open_cursor a , 00000004CFC9B240

1512704568

select count(*) into :b0 from X$KGLOB where ((KGLHDADR in 00000004DBB73F60

2160465385

table_1_ff_10e_0_0_0 00000004E2721900

1906560325

SQL_TEXT ADDRESS

-

---

-

---

HASH_VALUE

-

---

select count(*) into :b0 from X$KGLOB where ((KGLHDADR in 00000004DBB73F60

2160465385

table_1_ff_10e_0_0_0 00000004E2721900

1906560325

What can I do from now on ,except of waiting . After a while my reorg moved on but it is halted again with the same message, for different tables this time:

BR1200I Thread 2: drop of interim table SAPR3.REPOSRC#$ waiting due to active cursor...

BR0280I BRSPACE thread 1: time stamp: 2007-07-24 13.32.36

BR1200I Thread 1: drop of interim table SAPR3.REPOLOAD#$ waiting due to active cursor...

Many thanks

Andreas

[Former Member]

Did you figure out what to do to release the active cursor so that the reorg operattion can continue?

[former_member204746]

if you can, stop SAP, this should release those tables.

[Former Member]

Actually, late yesterday, I did resolve the situation without an SAP restart. I really want to / need to avoid as much downtime as possible, so I am testing out online reorganization capabilities and hope to use such features as much as possible. Naturally, I will end up doing production database reorgs during slow times, but if I can keep SAP up as much as possible, that is seen as a distinct advantage.

In my particular situation, I was able to run the query above to determine the active cursors (I added b.username to the select list, just for fun and more info). This lead me to some more searching on the web for related issues and I would up deciding to perform an:

alter system flush shared_pool;

command. This freed up one of the active cursors I thought might have been the cause of my grief. It was. As soon as I did the above flushing, my reorg continued on and finished up processing almost immediately (in my particular situation).

Though I do not know for sure why this particular active cursor was there, I do note that I was hanging around in DB02 (and its child screens/transactions) in one of my sessions. I had closed all of my sessions prior to performing the flush and the flush was what seemed to get rid of the active cursor that was "blocking' the reorg activity from occuring.

Thanks for the response and I hope this posting helps others.

[former_member84399]

James, thanks for your input, it was very interesting, I awarded you some points

"Unfortunately", I had to conclude my reorganisations and I did the production system last Friday, so I didn't have much time to fiddle with it. SAP's reply has been standard and disappointing "apply the latest brtools patch and retry", which I did, but it didn't make much difference

Thank you all for your help

Andreas

[former_member204746]

you should have replied to SAP that you updated brtools and that you are still having the same problem. SAP will always ask you to use the latest patch set, latest kernel etc... this is normal

[former_member84399]

Hello Eric,

I did and they asked me to rerun the reorg with with trace (add -TRC 15 to the end of command line). Unfortunately, by the time they came back to me, I had my Dev, QA, and Production systems reorganised so I can not experiment any more with this

Regards

Andreas