Physical standby with same SID [message #476241] |
Wed, 22 September 2010 04:57 |
paradoxkhan
Messages: 92 Registered: April 2008 Location: Pakistan
|
Member |
|
|
Hi,
I have to implement Physical standby using same SID. Any one can help me with parameters required to set on Primary and standby.
Also what entries are required to do in TNS file. Recently we have faced hardware failure. If anyone can respond immediately.
Thanks in advance.
Regards,
Yasir
|
|
|
|
Re: Physical standby with same SID [message #476254 is a reply to message #476241] |
Wed, 22 September 2010 06:24 |
paradoxkhan
Messages: 92 Registered: April 2008 Location: Pakistan
|
Member |
|
|
I need to configure dataguard with same SID.In-case of switchover users don't require to change SID(application level).
Currently i have configured as.
Primary
=======
>Database sid=orcl
/u01/app/oracle/oradata/orcl/datafiles
>TNS
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
)
)
drorcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
)
(CONNECT_DATA =
(SID = drorcl)
)
)
Standby
=======
Database created with SID drorcl
/u01/app/oracle/oradata/orcl/datafiles
TNS
same as above
What changed i required to do so the SID remains the same on both cases. I hope i have communicated my issue.
Thanks
|
|
|
|
Re: Physical standby with same SID [message #476266 is a reply to message #476262] |
Wed, 22 September 2010 06:59 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You are taking the wrong approach, using SIDs where you should be using services. In your database, create services and a trigger such as this
exec dbms_service.create_service('prod','prod');
exec dbms_service.create_service('standby','standby');
create trigger role_registration 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 ('prod');
elsif role = 'PHYSICAL STANDBY' then
dbms_service.start_service ('standby');
end if;
end;
/
Then use a single tnsnames entry such as this
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(failover=on)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
)
(CONNECT_DATA =
(service_name = prod)
)
)
This way, your users will always connect to the primary database, whichever it is.
|
|
|
Re: Physical standby with same SID [message #476268 is a reply to message #476266] |
Wed, 22 September 2010 07:10 |
paradoxkhan
Messages: 92 Registered: April 2008 Location: Pakistan
|
Member |
|
|
John
Thanks for your post. Really good if i can implement the same. Please clarify
1)
exec dbms_service.create_service('prod','prod'); primary instance or both
exec dbms_service.create_service('standby','standby'); primary instance or both
2) tns entry on both would be the same as above? no need to define as orcl and drorcl?
Thanks in advance..
|
|
|
Re: Physical standby with same SID [message #476295 is a reply to message #476268] |
Wed, 22 September 2010 09:28 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
For your first question: if you think about where a service is defined, and the relationship between a primary database and a physical standby database, the answer will be obvious.
I don't understand your second question, perhaps someone else can help.
|
|
|
|
Re: Physical standby with same SID [message #477903 is a reply to message #476378] |
Tue, 05 October 2010 03:18 |
paradoxkhan
Messages: 92 Registered: April 2008 Location: Pakistan
|
Member |
|
|
I have implemented as suggested by john in oracle 10g. Now in another treasury database 9i(9.2.0) required the same implementation.
I have tried the same steps but couldn't succeed please help me in implementing TAF in 9i DG. Thanks
|
|
|
|
Re: Physical standby with same SID [message #478131 is a reply to message #478103] |
Wed, 06 October 2010 08:40 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>I have tried the same steps but couldn't succeed please help me in implementing TAF in 9i DG
Did Data Guard exist as a product for V9?
Both V9 & V10 are no longer supported & it is past due to join the 21st Century.
|
|
|