Re: Connect time failover question(non-rac)
- From: Charles Schultz <sacrophyte@xxxxxxxxx>
- To: TESTAJ3@xxxxxxxxxxxxxx
- Date: Mon, 27 Jul 2009 11:16:48 -0500
I recently did the same
thing<//www.freelists.org/post/oracle-l/Trying-to-wrap-my-head-around-TAF,4>.
The trick is to create a database trigger that turns off the service for the
standby database.
For example:
SQL> CREATE OR REPLACE TRIGGER manage_OCIservice
after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('failover');
ELSE
DBMS_SERVICE.STOP_SERVICE('failover');
END IF;
END;
Still waiting for Oracle to make this a bit more clear; sometimes the
documentation stumbles over itself in being too verbose. =) I also had
Oracle Support tell me that there were issues when you explicitly put the
LOAD_BALANCE statement in there like that. But we never did figure out
exactly what those "issues" were.
On Mon, Jul 27, 2009 at 11:07, <TESTAJ3@xxxxxxxxxxxxxx> wrote:
>
> Ok so I'm finishing up with the experimentation for 11g Data guard,
> 11.1.0.7 on solaris right now, not RAC.
>
> Here is what I want to do but can't seem to get to work:
>
> using tnsnames.ora, I want to put both the standby and primary database in
> a single entry to do so connect time failover, try testdg1 (its normally the
> primary), if it fails(let's assume we had to do a switchover), then try
> testdg2 on different host.
>
> Sounds straightforward right? So I thought, what appears to be happening
> is since the standby is mounted, sqlnet won't return back a failure so i
> just end up with this error:
>
> ORA-01033: ORACLE initialization or shutdown in progress
> Process ID: 0
> Session ID: 0 Serial number: 0
>
> Why because its trying to connect to what is now the standby but its FIRST
> in the entries for addresses, here is my tnsnames entries
>
> TESTDG =
> (DESCRIPTION =
> (ADDRESS_LIST=
> (FAILOVER=ON)
> (LOAD_BALANCE=OFF)
> (ADDRESS = (PROTOCOL = TCP)(HOST = unixtest06)(PORT = 1526))
> (ADDRESS = (PROTOCOL = TCP)(HOST = unixtest02)(PORT = 1526))
> )
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = testdg1)
> (FAILOVER_MODE =
> (BACKUP=testdg2)(type=session)(method=basic)(retries=12)(delay=5))
> )
> )
>
> primary is on unixtest02 and standby is on unixtest06, so I put 06 first
> on purpose for testing. But it appears sqlnet is NOT returning a failure
> just what you see above.
>
> I got to be missing something simple here but I just don't see it.
>
> The whole reason for this exercise is during a switchover/failover I don't
> want to have to make changes to OID/tnsnames.ora for app server to
> reconnect, if they always connect to testdg, we'd be good.
>
> Someone please enlighten me in the error of my ways.
>
> thanks, joe
>
>
> _______________________________________
> Joe Testa, Oracle Certified Professional
> (Work) 614-677-1668
> (Cell) 614-312-6715
>
> Interested in helping out your marriage?
> Ask me about "Weekend to Remember"
> Dec 11-13, 2009 here in Columbus.
>
--
Charles Schultz
Other related posts:
