Re: More on tns configurations in the context of Dataguard
- From: Martin Bach <development@xxxxxxxxxxxxxxxxx>
- To: kat.axe@xxxxxxxxx
- Date: Tue, 13 Apr 2010 12:07:06 +0100
Dear all,
reviving this old thread... Was on holidays and found this quite
interesting.
On 04/02/2010 08:02 PM, kathryn axelrod wrote:
> Ah...Sorry..I misunderstood. :)
>
> My first question would be - out of curiosity - why would you need ten
> service names?
We are using even more services here-a consolidated RAC database for a
large number of business units. Accounting on the service level allows
the infrastructure dept to charge the business for use of the cluster.
You could also tie in services with resource manager and have different
mappings of service to resource consumer group... The possibilities are
endless :)
With RAC and Oracle Restart services are defined through srvctl but the
basic technique remains the same (and no more "alter system set
service_names=...")
>
> And second question - how would you plan on keeping the primary service
> names up to date? I'm thinking that you're going to have to hardcode the
> multiple names into the trigger anyway (for the primary), so you might
> as well write them in both the stop and start section...
> For the standby, you could of course do the loop you mentioned, or
> perhaps do an alter system set service_names. But if you have to list
> them anyway for the primary...
You could query v$active_services for the active services on the
primary, and DBA_SERVICES lists all services defined in the database-a
hack as you said. I doubt this view is populated on the standby though,
and you can't acess DBA% views when the database is mounted.
>
>
> On Fri, Apr 2, 2010 at 10:23 AM, Charles Schultz <sacrophyte@xxxxxxxxx
> <mailto:sacrophyte@xxxxxxxxx>> wrote:
>
> Kathryn, what if you have 10 services and 2 databases (one primary,
[snip]
>
> Here is where I am going with this. Does not Oracle provide
> a way to disable *_all_* services that are registered to the
> listener when the database is started as a standby? One
> could add a simple loop in the documented manage_OCI trigger
> to stop each entry in SERVICE_NAMES, but that seems like a
> hack. But maybe it is the only hack available to us at this
> point in time? The Oracle online documentation does not
> spell out any options for ALL services (too
> bad DBMS_SERVICE.STOP_SERVICE.SERVICE_NAME cannot be null
> *grin*). I realize you could do some magic with
> local_listener, but that seems to overly complicate matters,
> IMO.
>
Depending on version it might be possible to define services based on
the database role. In Grid Infrastructure's srvctl add service -h output
I found this:
[oracle@node1 ~]$ srvctl add service -h
Adds a service configuration to the Oracle Clusterware.
Usage: srvctl add service -d <db_unique_name> -s <service_name> {-r
"<preferred_list>" [-a "<available_list>"] [-P {BASIC | NONE |
PRECONNECT}] | -g <server_pool> [-c {UNIFORM | SINGLETON}] } [-k
<net_num>] [-l
[PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] [-y
{AUTOMATIC | MANUAL}] [-q {TRUE|FALSE}] [-x {TRUE|FALSE}] [-j
{SHORT|LONG}] [-B {NONE|SERVICE_TIME|THROUGHPUT}] [-e
{NONE|SESSION|SELECT}] [-m {NONE|BASIC}] [-z <failover_retries>] [-w
<failover_delay>]
...
-l <role> Role of the service (primary,
physical_standby, logical_standby, snapshot_standby)
...
"You use this option to indicate that the service should should only be
automatically started when the Oracle Data Guard database role matches
one of the specified service roles."
I have to admit that I need to do some testing with this though...
I found the use of FAN events can also help you with the same, again
Grid Infrastructure (and Oracle Restart) can send out FAN events for
UP/DOWN events and (new in 11.2) even for data guard broker managed
failover operations.
Best regards,
Martin
--
Martin Bach
OCM 10g
http://martincarstenbach.wordpress.com
http://www.linkedin.com/in/martincarstenbach
--
//www.freelists.org/webpage/oracle-l
Other related posts:
