Re: Connect time failover question(non-rac)

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Mon, 27 Jul 2009 11:16:48 -0500
Message-ID: <7b8774110907270916s3dea4901lcf8416c3b478e7b9_at_mail.gmail.com>

I recently did the same
thing<http://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_at_nationwide.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 27 2009 - 11:16:48 CDT