Skip to Main Content
  • Questions
  • Error while relocating database service

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: December 06, 2019 - 11:20 pm UTC

Last updated: December 17, 2019 - 3:34 am UTC

Version: 18.6

Viewed 1000+ times

You Asked

Hello, Ask Tom Team.

My environment:

I have a database running on 2-node RAC. I created a database service with TAF and transaction guard

srvctl add service -db dbprod -service dbprod1_xa -preferred dbprod1 -available dbprod2 -failover_restore LEVEL1 -failoverretry 30 -failoverdelay 10 -commit_outcome TRUE -failovertype SELECT -retention 86400 -notification TRUE -drain_timeout 300 -stopoption IMMEDIATE -role PRIMARY

SQL> grant execute on dbms_app_cont to appuser;

Test case:
I ran a query select * from schema.table where rownum<=30000 (it runs for ~30 secs).

Then I try to relocate my service with:
srvctl relocate service -database dbprod -s dbprod1_xa -oldinst dbprod1 -drain_timeout 10 -stopoption immediate -force


Result:

The query keeps running, then the query stop for a few seconds, then it keeps running fine.

My problem:

I created the same service now with -dtp option (to allow distributed transactions).

srvctl add service -db dbprod -service dbprod1_xa -preferred dbprod1 -available dbprod2 -dtp TRUE -failover_restore LEVEL1 -failoverretry 30 -failoverdelay 10 -commit_outcome TRUE -failovertype SELECT -retention 86400 -notification TRUE -drain_timeout 300 -stopoption IMMEDIATE -role PRIMARY

Test case:
I ran a query select * from schema.table where rownum<=30000 (it runs for ~30 secs).

Then I try to relocate my service with:
srvctl relocate service -database dbprod -s dbprod1_xa -oldinst dbprod1 -drain_timeout 10 -stopoption immediate -force


Result:

Immediately I got the error:

ERROR:
ORA-30006: resource busy; acquire with WAIT timeout expired

1. Why this behavior? I was expecting same result as above: a little pause because the relocating command but I wanted the query to keep running.

Thanks in advanced.

and Connor said...

See MOS note:

DTP Service Failover fails with ORA-30006 when a sql session has executed long-running DML (Doc ID 1263014.1)

Bug 9220961 was closed as "not a bug", but you might want to raise it again with Support.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database