Re: Starting named services at DB start

From: Steve Baldwin <stbaldwin_at_multiservice.com>
Date: Wed, 18 Aug 2010 14:23:11 +1000
Message-ID: <AANLkTinn_SLCJuVkE5nspoen+Mo93UHKwwQqafEes-WY_at_mail.gmail.com>



I seem to have hit a snag with the trigger approach.

A quick recap on our config :

11.1.0.7 Standard Edition 2 node cluster - rac01d1 (host=opbld05) and rac01d2 (host=opbld06)

1 service - bb created with :

srvctl add service -s bb -d rac01d -r rac01d1 -a rac01d2 -P BASIC

My tnsnames.ora entry looks like this :

bb.build =

    (DESCRIPTION =

        (ADDRESS_LIST =
            (LOAD_BALANCE = on)
            (FAILOVER = on)
            (ADDRESS =
                (PROTOCOL = TCP)
                (HOST = opbld05-vip.xxx.com)
                (PORT = 1521)
            )
            (ADDRESS =
                (PROTOCOL = TCP)
                (HOST = opbld06-vip.xxx.com)
                (PORT = 1521)
            )
        )
        (CONNECT_DATA =
            (SERVER = shared)
            (SERVICE_NAME = bb.build.xxx.com)
        )

    )

Here's the issue :

If I start the service with 'srvctl start service -d rac01d -s bb', any client connecting to _at_bb.build lands on node 1 (rac01d1) as expected. If node 1 goes down, the service is migrated to node 2 - again, as expected.

If however I start the service using dbms_session.start_service, I have to direct it to the correct node (unlike srvctl). At that point the service can no longer be stopped or relocated with srvctl.

For example :

[oracle_at_opbld05 ~]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Aug 17 23:05:51 2010

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production With the Real Application Clusters option

SQL> exec dbms_service.start_service('bb')

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
With the Real Application Clusters option
[oracle_at_opbld05 ~]$ srvctl stop service -d rac01d -s bb
PRKP-1063 : Service bb is already stopped.
[oracle_at_opbld05 ~]$ lsnrctl status | grep bb
Service "bb.build.multiservice.com" has 1 instance(s).
[oracle_at_opbld05 ~]$ rlwrap sqlplus stbaldwindba/xxx_at_bb.build

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Aug 17 23:07:12 2010

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production With the Real Application Clusters option

SQL> Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
With the Real Application Clusters option
[oracle_at_opbld05 ~]$ srvctl stop service -d rac01d -s bb -i rac01d1
PRKP-1065 : Service bb is already stopped on instance rac01d1.
[oracle_at_opbld05 ~]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Aug 17 23:08:00 2010

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production With the Real Application Clusters option

SQL> exec dbms_service.stop_service('bb', dbms_service.all_instances)

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 11g Release 11.1.0.7.0 - 64bit Production
With the Real Application Clusters option
[oracle_at_opbld05 ~]$ srvctl start service -d rac01d -s bb

However the bigger problem is that if I start the service using dbms_service.start_service and node 1 goes down, the service is not automatically migrated to node 2.

I'm assuming the problem is that dbms_service.start_service is not registering with crs. If I start the service with srvctl, here's what I see with crs_stat :

[oracle_at_opbld05 ~]$ crs_stat ora.rac01d.bb.cs
NAME=ora.rac01d.bb.cs
TYPE=application
TARGET=ONLINE
STATE=ONLINE on opbld05

[oracle_at_opbld05 ~]$ crs_stat ora.rac01d.bb.rac01d1.srv
NAME=ora.rac01d.bb.rac01d1.srv
TYPE=application
TARGET=ONLINE
STATE=ONLINE on opbld05

However if I start the service with dbms_service.start_service, I see this :

[oracle_at_opbld05 ~]$ crs_stat ora.rac01d.bb.cs
NAME=ora.rac01d.bb.cs
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
[oracle_at_opbld05 ~]$ crs_stat ora.rac01d.bb.rac01d1.srv
NAME=ora.rac01d.bb.rac01d1.srv
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE Does this sound like a bug, intended functionality (???), or do I need to do something else along with dbms_service.start_service in order to have the service registered properly with crs and thereby migrated in case of node failure.

Thanks again.

Steve

On Tue, Aug 17, 2010 at 6:01 PM, Timo Raitalaakso <rafu_at_iki.fi> wrote:

>
> Hi!
>
> About the issue in Morgan's Library hci page. It is using database startup
> trigger as Mark suggested.
>
> http://morganslibrary.org/hci/hci012.html
>
> --
> Rafu
> http://rafudb.blogspot.com/
>
>
> On 17.8.2010 5:07, Bobak, Mark wrote:
>
>> How about a database startup trigger that calls
>> dbms_service.start_service?
>>
>
>  I see that in 11.2 the srvctl add service command takes a '-y AUTOMATIC'
>> to start the service when the DB starts. Is there an option in 11.1 that
>> I've missed or is it just not there?
>>
>> Steve
>>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


---------------------------------------------------------------------------------------
This email is intended solely for the use of the addressee and may contain information that is confidential, proprietary, or both. If you receive this email in error please immediately notify the sender and delete the email.
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Aug 17 2010 - 23:23:11 CDT

Original text of this message