| client failure failover/switchover standby configuration [message #506602] |
Tue, 10 May 2011 05:00  |
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 #506659 is a reply to message #506644] |
Tue, 10 May 2011 07:18  |
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 #506676 is a reply to message #506674] |
Tue, 10 May 2011 08:13 |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #506679 is a reply to message #506676] |
Tue, 10 May 2011 08:25 |
oracleraj
Messages: 13
Registered: November 2009
|
Junior Member |
|
|
Michel Cadot wrote on Tue, 10 May 2011 08:13Yes 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 #506780 is a reply to message #506683] |
Wed, 11 May 2011 05:25 |
oracleraj
Messages: 13
Registered: November 2009
|
Junior Member |
|
|
Michel Cadot wrote on Tue, 10 May 2011 08:42Quote: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 
Kind Regards
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
