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