I think my idea for services alone would not handle that, because I believe
dbms_job internally spawns a sid,serial#, rather than routing through a
listener. But you could use the instance parameter for those jobs (using a
value different from 0).
IF you looked up your active instance (in a table, for example, that you
changed if you switched the instance you wanted to use), read that value and
apply that via dbms_job.instance, that coupled with the preferred service
should work.
The question is whether or not it is easy or difficult with your application to
inject that dbms_job.instance assignment. Presumably you want to use ‘FORCE’ so
it errors if the desired instance is not available.
Service orientation is available in DBMS_SCHEDULER. I *suppose* you could
submit your dbms_job activations via DBMS_SCHEDULER which would look up the
preferred node for the service and then then as part of submitting the DBMS_JOB
it would first look at the current instance and add that parameter to the
DBMS_JOB call.
Maybe ensuring that only one instance is up is easier after all. I *think* my
jury rig above could be made to work, but I’m not sure it is worth the trouble.
mwf
From: Martin Klier - Performing Databases GmbH
[mailto:martin.klier@xxxxxxxxxxxxxxxxx] ;
Sent: Friday, July 07, 2017 3:06 AM
To: Mark Farnham
Cc: Ethan Post; Martin Berger; Oracle-L oracle-l
Subject: Re: RAC One Node - Instances not open at the same time!
Hi Mark,
thanks for your reply. Will all the service-centered approaches work for
dbms_jobs?
Thanks
Martin
--
Martin Klier | Performing Databases GmbH
Managing Partner | Senior DB Consultant
Oracle ACE
martin.klier@xxxxxxxxxxxxxxxxx | https://www.performing-databases.com
_____
Von: "Mark Farnham" <mwf@xxxxxxxx>
An: "martin klier" <martin.klier@xxxxxxxxxxxxxxxxx>, "Ethan Post"
<post.ethan@xxxxxxxxx>
CC: "Martin Berger" <martin.a.berger@xxxxxxxxx>, "Oracle-L oracle-l"
<oracle-l@xxxxxxxxxxxxx>
Gesendet: Donnerstag, 6. Juli 2017 14:16:42
Betreff: RE: RAC One Node - Instances not open at the same time!
Did I miss a memo and service connection node preference is not available on
“One Node?”
My initial reaction is that having one or the other node as preferred and then
restarting the service the opposite if a node is intentionally down would be a
reasonable solution that, for example, allows non-application work to reside in
the instance shared pool and possibly buffer cache of the non-preferred
instance.
Your mileage may vary.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Martin Klier - Performing Databases GmbH
Sent: Tuesday, July 04, 2017 5:24 AM
To: Ethan Post
Cc: Martin Berger; Oracle-L oracle-l
Subject: Re: RAC One Node - Instances not open at the same time!
Ethan,
thank you very much.
--
Martin Klier | Performing Databases GmbH
Managing Partner | Senior DB Consultant
Oracle ACE
martin.klier@xxxxxxxxxxxxxxxxx | https://www.performing-databases.com
_____
Von: "Ethan Post" <post.ethan@xxxxxxxxx>
An: "martin klier" <martin.klier@xxxxxxxxxxxxxxxxx>, "Martin Berger"
<martin.a.berger@xxxxxxxxx>
CC: "Oracle-L oracle-l" <oracle-l@xxxxxxxxxxxxx>
Gesendet: Montag, 3. Juli 2017 16:22:22
Betreff: RE: RAC One Node - Instances not open at the same time!
2 cents
My recommendation would be to set up an alert that opens ticket, sends
email/message etc…when two instances are running with an explanation of the
reasons both should not be open unless the potential for problems is truly high
risk issue.
The “trigger” solution is good but if you are in a high turnover environment,
moving to cloud someday, cloning, replicating system, doing upgrades etc…there
is a chance this will come back to bite someone someday. It isn’t obvious
enough, whereas the alert would be very obvious.
So if you have big data/job corruption issue then it needs to be enforced
strictly, otherwise go with an notify solution.
Thanks
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Martin Klier - Performing Databases GmbH
Sent: Monday, July 3, 2017 5:53 AM
To: Martin Berger <martin.a.berger@xxxxxxxxx>
Cc: Oracle-L oracle-l <oracle-l@xxxxxxxxxxxxx>
Subject: Re: RAC One Node - Instances not open at the same time!
Martin,
sounds very cool. Thanks!
--
Martin Klier | Performing Databases GmbH
Managing Partner | Senior DB Consultant
Oracle ACE
martin.klier@xxxxxxxxxxxxxxxxx | https://www.performing-databases.com
_____
Von: "Martin Berger" <martin.a.berger@xxxxxxxxx>
An: "Martin Klier" <martin.klier@xxxxxxxxxxxxxxxxx>
CC: "Oracle-L oracle-l" <oracle-l@xxxxxxxxxxxxx>
Gesendet: Freitag, 30. Juni 2017 14:32:00
Betreff: Re: RAC One Node - Instances not open at the same time!
Hi Martin,
as this is an open discussion what's about this concept:
you write a startup trigger which immediately set
alter system enable restricted session;
then it spools gv$instance until it's sure the other instance is gone,
at the end do
alter system disable restricted session;
that's maybe not a very elegant method, but it might solve your request?
(everything is untested right now on my side)
Martin
2017-06-30 13:22 GMT+02:00 Martin Klier - Performing Databases GmbH
<martin.klier@xxxxxxxxxxxxxxxxx>:
Why? It's an open concept discussion, no system was configured yet.
--
Martin Klier | Performing Databases GmbH
Managing Partner | Senior DB Consultant
Oracle ACE
martin.klier@xxxxxxxxxxxxxxxxx | https://www.performing-databases.com
_____
Von: "Mladen Gogala" <gogala.mladen@xxxxxxxxx>
An: oracle-l@xxxxxxxxxxxxx
Gesendet: Freitag, 30. Juni 2017 00:21:30
Betreff: Re: RAC One Node - Instances not open at the same time!
On 06/29/2017 07:24 AM, Martin Klier - Performing Databases GmbH wrote:
Hi Listers,
when running Oracle RAC One Node, is there a way to avoid both instances being
open at the same time?
Background:
I have an application that's by no way ready for RAC (using dbms_jobs and much
more). But they want to use the benefits of One Node for operating the cluster.
All that's needed is a failover cluster.
Please post the output of srvctl config db -d <DB name>.
--
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
