Re: Starting named services at DB start
- From: Steve Baldwin <stbaldwin@xxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 18 Aug 2010 14:23:11 +1000
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 @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@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@opbld05 ~]$ srvctl stop service -d rac01d -s bb
PRKP-1063 : Service bb is already stopped.
[oracle@opbld05 ~]$ lsnrctl status | grep bb
Service "bb.build.multiservice.com" has 1 instance(s).
[oracle@opbld05 ~]$ rlwrap sqlplus stbaldwindba/xxx@xxxxxxxx
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@opbld05 ~]$ srvctl stop service -d rac01d -s bb -i rac01d1
PRKP-1065 : Service bb is already stopped on instance rac01d1.
[oracle@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@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@opbld05 ~]$ crs_stat ora.rac01d.bb.cs
NAME=ora.rac01d.bb.cs
TYPE=application
TARGET=ONLINE
STATE=ONLINE on opbld05
[oracle@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@opbld05 ~]$ crs_stat ora.rac01d.bb.cs
NAME=ora.rac01d.bb.cs
TYPE=application
TARGET=OFFLINE
STATE=OFFLINE
[oracle@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@xxxxxx> 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
>>
> --
> //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.
---------------------------------------------------------------------------------------
Other related posts:
