Hi
I have a question regarding oracle db failover connectstring (to standby_host) for .NET application web.config file.
We have 2 node RAC databases with single node standby database. DB version 11.2.0.4.0
My goal is that while we are doing scheduled switchover (or for failover, if something goes wrong at the primary site), then we don't have to change application web.config file to change connect_string's HOST from: primary_hostname1/2 ---> standby_hostname.
So that it is switching over automatically after the database switchover/failover has been performed.
At the moment our web.config file looks like this.
<add key="DatabaseSettings.connectionString" value="Data Source=ettest; User Id=<username>;Password=<password>" />
.......
<dataSource alias="ettest" descriptor="
(DESCRIPTION=
(FAILOVER=on)
(LOAD_BALANCE=off)
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=primary_host_1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=primary_host_2)(PORT=1521)))
(CONNECT_DATA=
(SERVER = DEDICATED)
(SERVICE_NAME=ettestfo)
(FAILOVER_MODE= (TYPE=SELECT) (METHOD=BASIC) (RETRIES=120) (DELAY=5))))"/>
So basically we want that all the traffic from application goes to specific node, usually first node (primary_host_1). If that fails, then it can failover to second node (primary_host_2). At least that what this connect string should do or am I missing something..?
We don't want to use LOAD_BALANCE, because there can occur some wait events like gc current block 2-way, because 2 instances are querying same blocks.
Our second node (primary_host_2) is used for other background application tasks. And usually their load is pretty much the same on primary node1 and node2.
Also, the service_name for this database is always the same in primary hosts and also in standby hosts, because of the following trigger. If switchover/failover is performed then standby database (new primary) role has changed and this service will start the service on old standby.
CREATE OR REPLACE TRIGGER ETTESTFO_TRIGG after startup on database
declare
v_role varchar(30);
begin
select database_role into v_role from v$database;
if v_role = 'PRIMARY' then
DBMS_SERVICE.START_SERVICE('ettestfo');
else
DBMS_SERVICE.STOP_SERVICE('ettestfo');
end if;
end;
/
But now comes the main question, how can I add standby_host also to this connect string, so when both primary hosts not available (because of switchover/failover) then connections go to standby_host...?
"<dataSource alias="ettest" descriptor="
(DESCRIPTION=
.....
Thanks
Regards
Raul Kaubi