Home » Server Options » Data Guard » client failure failover/switchover standby configuration (Oracle 10g2.. windows 2003sever)
client failure failover/switchover standby configuration [message #506602] Tue, 10 May 2011 05:00 Go to next message
oracleraj
Messages: 13
Registered: November 2009
Junior Member
After switchover the primary is now standby and standby is now primary, the clients are unable to connect to new primary database.

TNSNAMES.ora file at client side...

--------------------------------------------------------------------------------

prim.world=
(DESCRIPTION_LIST=
(FAILOVER=true)
(LOAD_BALANCE=no)
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST= test9)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=primary)
)
)
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=standby)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=standby )
)
)
)



--------------------------------------------------------------------------------
SQL> conn iq/iq@prim.world
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress

SQL> conn sys/ora123@prim.world as sysdba
Connected.
SQL> select open_mode from v$database;

OPEN_MODE



--------------------------------------------------------------------------------
MOUNTED

SQL> select database_role from v$database;

DATABASE_ROLE

--------------------------------------------------------------------------------
PHYSICAL STANDBY

SQL>

--------------------------------------------------------------------------------
It's Oracle 10.2.0.1.0 version....


Re: client failure failover/switchover standby configuration [message #506607 is a reply to message #506602] Tue, 10 May 2011 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 67136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the clients are unable to connect to new primary database.

Did they change their connection string to point to the standby?

Regards
Michel
Re: client failure failover/switchover standby configuration [message #506629 is a reply to message #506607] Tue, 10 May 2011 06:20 Go to previous messageGo to next message
oracleraj
Messages: 13
Registered: November 2009
Junior Member
NO, in TAF (Transparent application failover in dataguard) the one entry is sufficient and if either database is unavailable/down....the connection request is diverted to the other database mentioned same in TNSentry.
Re: client failure failover/switchover standby configuration [message #506644 is a reply to message #506629] Tue, 10 May 2011 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
oracleraj wrote on Tue, 10 May 2011 13:20
NO, in TAF (Transparent application failover in dataguard) the one entry is sufficient and if either database is unavailable/down....the connection request is diverted to the other database mentioned same in TNSentry.

You didn't tell us you use TAF.
Maybe if you show us your configuration we can investigate your problem.

Regards
Michel

Re: client failure failover/switchover standby configuration [message #506659 is a reply to message #506644] Tue, 10 May 2011 07:18 Go to previous messageGo to next message
oracleraj
Messages: 13
Registered: November 2009
Junior Member
Below is my primary & standby database configuration ...

------------------------
primary------ machine: test9 sid: primary
standby------machine: standby sid: standby

-----------------------------------
SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
--------------------------------------------------------------------------------
Primary database init file :
*.control_files='D:\oracle\product\10.2.0/oradata/primary/\control01.ctl','D:\oracle\product\10.2.0/oradata/primary/\control02.ctl',' D:\oracle\product\10.2.0/oradata/primary/\control03.ctl'
*.db_name='primary'
*.db_unique_name='PRIMARY'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'
*.log_archive_dest_1='location=D:\oracle\product\10.2.0\oradata\archive'
*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.log_archive_dest_2='SERVICE=standby LGWR ASYNC'
*.log_archive_format='arc_%s_%t_%r.arc'
*.fal_client='PRIMARY'
*.fal_server='STANDBY'

--------------------------------------------------------------------------------
standby database init file :
*.control_files='D:\oracle\product\10.2.0/oradata/primary/STANDBY_CONTROL.CTL'
*.db_name='primary'
*.db_unique_name='STANDBY'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primaryXDB)'
*.log_archive_dest_1='location=D:\oracle\product\10.2.0\oradata\archive'
*.log_archive_format='arc_%s_%t_%r.arc'
*.log_archive_dest_state_2=ENABLE
*.fal_server='PRIMARY'
*.fal_client='STANDBY'
*.standby_file_management=auto

----------------------------------
primary TNSNAMES.ora:

primary =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = TCP)
          (Host = test9)
          (Port = 1521)
        )
    )
    (CONNECT_DATA = (SID = primary)
    )
  )
standby =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = TCP)
          (Host = standby)
          (Port = 1521)
        )
    )
    (CONNECT_DATA = (SID = STANDBY)
    )
  )

-------------------------------------
Standby TNSNAMES.ora:

standby =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = TCP)
          (Host = standby)
          (Port = 1521)
        )
    )
    (CONNECT_DATA = (SID = STANDBY)
    )
  )

primary =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = TCP)
          (Host = test9 )
          (Port = 1521)
        )
    )
    (CONNECT_DATA = (SID = primary)
    )
  )

--------------------------------------
LISTNER.ora at PRIMARY :

# listener.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test9)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

-----------------------------
LISTENER.ora at STANDBY :
# listener.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = standby)
      (ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
      (sid_name = standby)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

----------------------------------------
TNSNAMES.ora at client

prim.world=
  (DESCRIPTION_LIST=
    (FAILOVER=true)
    (LOAD_BALANCE=no)
    (DESCRIPTION=
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST= test9)
        (PORT=1521)
      )
      (CONNECT_DATA=
        (SERVER=dedicated)
        (SERVICE_NAME=primary)
      )
    )
    (DESCRIPTION=
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=standby)
        (PORT=1521)
      )
      (CONNECT_DATA=
        (SERVER=dedicated)
        (SERVICE_NAME=standby )
      )
    )
  )

--------------

[Updated on: Tue, 10 May 2011 07:33] by Moderator

Report message to a moderator

Re: client failure failover/switchover standby configuration [message #506666 is a reply to message #506659] Tue, 10 May 2011 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

I think what happens is that the (previous) primary instance is not down but just not open, so the listener does not transfer the connection to the other node (remember that TAF was designed for RAC not Data Guard).

Regards
Michel
Re: client failure failover/switchover standby configuration [message #506674 is a reply to message #506666] Tue, 10 May 2011 08:09 Go to previous messageGo to next message
oracleraj
Messages: 13
Registered: November 2009
Junior Member
sir I have searched (transparent application failover data guard) its possible in data guard too.

Check below posts :

http://uhesse.wordpress.com/2009/08/19/connect-time-failover-transparent-application-failover-for-data-guard/#comment-1102

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:52258813563764

I am having some problem, I know I am missing something .
Re: client failure failover/switchover standby configuration [message #506676 is a reply to message #506674] Tue, 10 May 2011 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but either it is a FAILOVER not a SWITCHOVER, either the listener of the old primary is down.
In your case, it is a SWITCHOVER and the listener of the old primary is up.

As Tom said:
Quote:
if we cannot
connect to the first listed listener, we'll go for the second.


And you will have to configure a failover time-out.

Regards
Michel

[Updated on: Tue, 10 May 2011 08:16]

Report message to a moderator

Re: client failure failover/switchover standby configuration [message #506677 is a reply to message #506676] Tue, 10 May 2011 08:16 Go to previous messageGo to next message
oracleraj
Messages: 13
Registered: November 2009
Junior Member
okay, but what happens when we do SWITCHOVER ?

How do clients get connecttion to the new primary database ?
Re: client failure failover/switchover standby configuration [message #506679 is a reply to message #506676] Tue, 10 May 2011 08:25 Go to previous messageGo to next message
oracleraj
Messages: 13
Registered: November 2009
Junior Member
Michel Cadot wrote on Tue, 10 May 2011 08:13
Yes but either it is a FAILOVER not a SWITCHOVER, either the listener of the old primary is down.
In your case, it is a SWITCHOVER and the listener of the old primary is up.

As Tom said:
Quote:
if we cannot
connect to the first listed listener, we'll go for the second.


And you will have to configure a failover time-out.

Regards
Michel



I didnt get your/tom's point..
ONE: How can client get connection to second listner ?
TWO: Where should I configure failover time-out in my configuration ?
Re: client failure failover/switchover standby configuration [message #506683 is a reply to message #506679] Tue, 10 May 2011 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 67136
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ONE: How can client get connection to second listner ?

It is in his tnsnames.ora

Quote:
Where should I configure failover time-out in my configuration

It is in red in the first link you posted.

Regards
Michel
Re: client failure failover/switchover standby configuration [message #506780 is a reply to message #506683] Wed, 11 May 2011 05:25 Go to previous message
oracleraj
Messages: 13
Registered: November 2009
Junior Member
Michel Cadot wrote on Tue, 10 May 2011 08:42
Quote:
ONE: How can client get connection to second listner ?

It is in his tnsnames.ora

Quote:
Where should I configure failover time-out in my configuration

It is in red in the first link you posted.

Regards
Michel


Thank you so much Michel, I was not getting, I thought the service created with Oracle ( OracleServicePRIMARY ) will work at primary and ( OracleServiceSTANDBY ) will work at standby database, But after creating the service, and trigger as Uwe Hesse has created, it worked for me.

I have tested both switchover & failover, both worked for clients

Thanks you so much sir for helping me Smile
Kind Regards
Previous Topic: ORA-00274: illegal recovery option SESSION physical stdby db
Next Topic: How to delete archivelog in Standby Database when it is applied successfully
Goto Forum:
  


Current Time: Fri May 29 16:11:00 CDT 2020