Two instances with same SID in a same machine
FirehawkŽ
I am debating this question with a job partner.
My opinion is if I use two diferents Oracle users like oracleA and oracleB
and make two instalations wholly separated, it's possible and to attach to
instances use differents listeners ports.
Am I correct ?
Thanks,
Marcelo
Howard J. Rogers
I suppose knowing the proposed platform and O/S would be out of the
question?
Instance names must be unique on a host. How else can Oracle distinguish
between them? How else can your O/S cope?
For example, the rule about what file Oracle looks for at instance startup
is that it searches ORACLE_HOME/dbs (or O_H\database) for a file called
initSID.ora, or spfileSID.ora. Now, if you've two instances of the same
name, that can only be because the ORACLE_SID environment variable is set
identically... care to speculate on how one could find one initSID.ora, and
the other could find a completely different initSID.ora, yet of the same
name and in the same directory???
Nice idea, but your opinion is, in this case, ill-founded.
Regards
HJR
Hans Forbrich
You do not seem to understand that the initial listening port is only used
for the Listener. After the listener has answered, the actual database
communication with the instance _may_ be on a completely different port
.... remember that one Listener can support several instances.
Two people caled Marcelo in the same room. Someone yells 'Hey Marcelo, come
here' - which one is supposed to answer?
/Hans
Mark Bole
Well, let's ask some questions. Could two different OS login users
each have their own shared memory and semaphores? (assume some flavor of
Unix, maybe under Windows too?) Check.
Could two different OS users each have an ORACLE_HOME (base directory)
at different places in the server filesystem, independent of each other?
Check.
Could one physical server have several TCP/IP addresses? Check.
Could two listeners run on the same machine, each using port 1521 but a
different TCP/IP address? Check.
I don't have the means readily at hand to test this right now (oh, where
have you heard that before? ;-) ), but I think it can be done.
--Mark Bole
Howard J. Rogers
>>
>
> Well, let's ask some questions. Could two different OS login users
> each have their own shared memory and semaphores? (assume some flavor of
> Unix, maybe under Windows too?) Check.
>
> Could two different OS users each have an ORACLE_HOME (base directory)
> at different places in the server filesystem, independent of each other?
> Check.
>
> Could one physical server have several TCP/IP addresses? Check.
>
> Could two listeners run on the same machine, each using port 1521 but a
> different TCP/IP address? Check.
>
> I don't have the means readily at hand to test this right now (oh, where
> have you heard that before? ;-) ), but I think it can be done.
>
> --Mark Bole
You can check all you want Mark. It's not going to happen.
Your questions can be simplified as:
On the one machine:
1. Can multiple distinct areas of shared memory be set up
2. Can you install Oracle multiple times
3. Can there be multiple IP addresses
4. Can there be multiple listeners.
No-one would have any qualms about agreeing with ANY of those.
My answers would be:
1. What do you think running two instances on a machine does?
2. People have been doing it for ages
3. Inevitably so... RAC wouldn't have much future otherwise
4. Obviously, though we would ordinarily question the point of doing it
That still doesn't address the actual question asked, however: can two areas
of shared memory be referenced by exactly the same identifier (SID)? To
which the answer is, try as you might, no.
As a VMware buff, of course, I would point out that it is utterly trivial to
have identically-named instances on the one laptop, and I do it all the
time. But each one of those instances has to be wrapped inside its own
virtual machine... which is not quite what the OP had in mind, I suspect.
Regards
HJR
Igor Laletin
> Is it possible ?
> I am debating this question with a job partner.
> My opinion is if I
> use two diferents Oracle users like oracleA and oracleB
I don't think it's nessesary.
> and make two instalations wholly separated, it's possible
It's a must. You must have two separate oracle homes. Lock file names
gonna be identical so they must live in different places.
> and to attach to instances use differents listeners ports.
I suspect even this is not nessesary but don't have time to check.
> Am I correct ?
Possible on Unix but why not check it by yourself?
> Thanks,
> Marcelo
"Howard J. Rogers" <h...@dizwell.com> wrote in message news:<412ea3d7$0$27732$afc3...@news.optusnet.com.au>...
> can two areas of shared memory be referenced by exactly the same identifier
> (SID)? To which the answer is, try as you might, no.
Don't shared memory segments are referenced by unique _numbers_, not
strings? Also instance can allocate several shmem segments to satisfy
sga requirements. They couldn't all be referenced by the same SID if
SID indeed was used.
Hans Forbrich <forb...@yahoo.net> wrote in message news:<aRuXc.56501$X12.21029@edtnps84>...
> Two people caled Marcelo in the same room. Someone yells 'Hey Marcelo, come
> here' - which one is supposed to answer?
What if two people (listeners) called two different Marcelo's
(instances with the same SID) in two different rooms (oracle homes)?
Cheers,
Igor
Sybrand Bakker
>What if two people (listeners) called two different Marcelo's
>(instances with the same SID) in two different rooms (oracle homes)?
>
>Cheers,
>Igor
It will be an administration nightmare wouldn't it? And sooner or
later account 1 will screw up the database located in account 2.
--
Sybrand Bakker, Senior Oracle DBA
Brian Lesker | Impulse Info Systems
Like:
1. svrmgrl
2. connect internal
3. startup mount pfile=location
4. alter database backup controlfile to trace resetlogs
5. find the trace file created (.trc) and open it
6. Change the following line:
CREATE CONTROLFILE REUSE DATABASE "oldname" RESETLOGS
to
CREATE CONTROLFILE REUSE set DATABASE "newname" RESETLOGS
Save the file like ccf.sql
7. connect internal
8. startup nomount pfile=location
9. run ccf.sql (@ccf.sql)
10. when needed, run alter database backup controlfile to trace resetlogs
And ready, your database has a new sid.
Sybrand Bakker schreef:
Howard J. Rogers
> On 27 Aug 2004 01:33:43 -0700, ilal...@usa.net (Igor Laletin) wrote:
>
>>What if two people (listeners) called two different Marcelo's
>>(instances with the same SID) in two different rooms (oracle homes)?
>>
>>Cheers,
>>Igor
>
> It will be an administration nightmare wouldn't it?
It will be an administration impossibility.
You and I are both in agreement, I think: it's not actually *possible*.
Never mind would it be a good idea.
If someone wants to jump through the necessary hoops to prove me wrong, be
my guest: in the meantime, it is impossible to create two instances on the
same machine with the same ORACLE_SID, and I don't care how clever you get
in distinguishing between different installations.
Regards
HJR
FM
Maybe I misunderstood but this is a period where I have to test machine
and features and I considered this a good challenge so I tried and here
is the result:
oracle 9938 1 0 10:39 ? 00:00:00 ora_pmon_TEST9I
oracle 9943 1 0 10:39 ? 00:00:00 ora_dbw0_TEST9I
oracle 9952 1 0 10:39 ? 00:00:00 ora_lgwr_TEST9I
oracle 9954 1 0 10:39 ? 00:00:00 ora_ckpt_TEST9I
oracle 9962 1 0 10:39 ? 00:00:00 ora_smon_TEST9I
oracle 9967 1 0 10:39 ? 00:00:00 ora_reco_TEST9I
oracle 9969 1 0 10:39 ? 00:00:00 ora_cjq0_TEST9I
oracle 9972 1 0 10:39 ? 00:00:00 ora_qmn0_TEST9I
oracle 9976 1 0 10:39 ? 00:00:00 ora_s000_TEST9I
oracle 9981 1 0 10:39 ? 00:00:00 ora_d000_TEST9I
oracle 10011 1 0 10:39 ? 00:00:00 ora_p000_TEST9I
oraclet 13382 1 0 14:08 ? 00:00:00 ora_pmon_TEST9I
oraclet 13384 1 0 14:08 ? 00:00:00 ora_mman_TEST9I
oraclet 13386 1 0 14:08 ? 00:00:00 ora_dbw0_TEST9I
oraclet 13388 1 0 14:08 ? 00:00:00 ora_lgwr_TEST9I
oraclet 13390 1 0 14:08 ? 00:00:00 ora_ckpt_TEST9I
oraclet 13392 1 0 14:08 ? 00:00:00 ora_smon_TEST9I
oraclet 13394 1 0 14:08 ? 00:00:00 ora_reco_TEST9I
oraclet 13396 1 0 14:08 ? 00:00:00 ora_cjq0_TEST9I
oraclet 13398 1 0 14:08 ? 00:00:00 ora_d000_TEST9I
oraclet 13400 1 0 14:08 ? 00:00:00 ora_s000_TEST9I
oraclet 13402 1 0 14:08 ? 00:00:00 ora_p000_TEST9I
Two different DBs on the same host (a linux system).
I needed two different users and two different ORACLE_BASE.
But maybe the main topic was another and my coompreension of the topic
was messy...
--
Fabrizio Magni
replace mycontinent with europe
Hans Forbrich
> What if two people (listeners) called two different Marcelo's
> (instances with the same SID) in two different rooms (oracle homes)?
>
Your attempt to twist this {home=room} analogy is not quite appropriate to
the situation ... in Oracle's case {server=room} and {home=chair}
Try again, keeping the analogy relevant to the environment.
Jeff
If you call the dog, which flea jumps? *grin*
In article <5GFXc.19626$A8.2621@edtnps89>, Hans Forbrich <forb...@yahoo.net>
wrote:
Hans Forbrich
Yes, that is the correct way to do it ...
These days, the typical scenario (especially since Oracle introduced the
concept of ORACLE_HOME) is to use one *nix userid to install and manage
_all_ instances.
If you want the confusion of two identically-named instances on the same
server, you _must_ separate them by *nix userid.
I've not used this trick since Oracle8, late '90s, and had totally forgotten
about it. DOH!
To express a concern - I hope you did not _need_ to do this because of
hard-coded SID in the application!
FM
>
> Yes, that is the correct way to do it ...
>
> These days, the typical scenario (especially since Oracle introduced the
> concept of ORACLE_HOME) is to use one *nix userid to install and manage
> _all_ instances.
>
> If you want the confusion of two identically-named instances on the same
> server, you _must_ separate them by *nix userid.
>
> I've not used this trick since Oracle8, late '90s, and had totally forgotten
> about it. DOH!
>
> To express a concern - I hope you did not _need_ to do this because of
> hard-coded SID in the application!
>
>
I did it only as an answer to this topic... to show that to have two
DB/instances with the same name on the same host machine was indeed
possible.
In "real life" I prefer to use different names... as well as different
oracle users if I have more the one oracle version installed.
But probably this last assertion show my lack of experience with the
oracle world.
Hans Forbrich
> I say "fleas on the same dog" makes for an apt analogy apropos of this
> thread.
> If you call the dog, which flea jumps? grin
>
Since you haven't given either an ID, you can't tell them apart. You need
to dig into your kitbag to eliminate the duplicates & redesign your model.
So much silliness, so little time! Sigh!!!!
/Hans
Igor Laletin
> Sybrand Bakker wrote:
>
> > On 27 Aug 2004 01:33:43 -0700, ilal...@usa.net (Igor Laletin) wrote:
> >
> >>What if two people (listeners) called two different Marcelo's
> >>(instances with the same SID) in two different rooms (oracle homes)?
> >
Yes, of course, but OP didn't talk about real life, right? It's a
theoretical question. Nobody wants to inflict such a situation on
himself.
> It will be an administration impossibility.
> You and I are both in agreement, I think: it's not actually *possible*.
> Never mind would it be a good idea.
It's actually *possible*, at least on Unix. See Fabrizio's email. Yes,
it's not a good idea but posssible.
> If someone wants to jump through the necessary hoops to prove me wrong, be
> my guest:
Nothing to do with the hoops. Why take it personally? It's not about
_you_. It's about SID's.
> in the meantime, it is impossible to create two instances on the
> same machine with the same ORACLE_SID, and I don't care how clever you get
> in distinguishing between different installations.
Well, it's possible on Unix and it doesn't involve words 'clever' and
'jumping'.
Regards,
Igor
> Regards
> HJR
Igor Laletin
> If you want the confusion of two identically-named instances on the same
> server, you _must_ separate them by *nix userid.
Can't check it at the moment but there are no obvious reasons to
separate them by uid. You must separate them by oracle home.
Regards,
Igor
Mark Bole
> Sybrand Bakker wrote:
>
>
>>On 27 Aug 2004 01:33:43 -0700, ilal...@usa.net (Igor Laletin) wrote:
>>
>>
>>>What if two people (listeners) called two different Marcelo's
>>>(instances with the same SID) in two different rooms (oracle homes)?
>>>
>>>Cheers,
>>>Igor
>>
>>It will be an administration nightmare wouldn't it?
>
>
> It will be an administration impossibility.
>
> You and I are both in agreement, I think: it's not actually *possible*.
> Never mind would it be a good idea.
>
> If someone wants to jump through the necessary hoops to prove me wrong, be
> my guest: in the meantime, it is impossible to create two instances on the
> same machine with the same ORACLE_SID, and I don't care how clever you get
> in distinguishing between different installations.
>
> Regards
> HJR
>
Ok, consider yourself proven wrong. Fabrizio beat me to it, but I
tested and received the same result. No RAC, no VMware, no tricks up my
sleeve -- just a plain vanilla Solaris 8 box.
I read the OP as an academic exercise, not a "is this a good idea in the
real world" question. But then some people in this newsgroup don't even
like the idea of running two DIFFERENTLY named instances on the same
box, so it's not surprising the level of resistance to even debating the
possibility.
It's due to good design decisions made a long time ago in the area of
Unix shared memory (System V IPC). (And Howard, I know you're an
advocate of good design above all else!) The Unix philosophy is to avoid
arbitrary restrictions, and it applies here. Just as two users can each
have an identically named file in their home directory, so can two users
have identical lock name spaces in their respective shared memory segments.
The ipcs output shows the two independent shm segments:
% ipcs -bm
IPC status from <running system> as of Fri Aug 27 11:02:15 PDT 2004
T ID KEY MODE OWNER GROUP SEGSZ
Shared Memory:
m 640 0x99a80894 --rw-r----- oracle1 dba1 390070272
m 130 0xf1278a80 --rw-r----- oracle dba 390070272
And this shows two identically named lock files in different Oracle
homes, owned by different users:
-rw-rw---- 1 oracle1 dba1 24 Aug 27 10:30
/data2/oracle1/product/9.2.0/dbs/lkBCEQA3
-rw-rw---- 1 oracle dba 24 Apr 4 12:00
/u01/app/oracle/product/9.2.0/dbs/lkBCEQA3
I'm quite willing to believe that it won't work under Windows, but then
fork() won't work under Windows either, last time I checked.
Setting up a separate listeners (or separate service names in the same
listener) for each instance is left as an exercise for the reader. ;-)
--Mark Bole
Igor Laletin
> Igor Laletin wrote:
>
> > What if two people (listeners) called two different Marcelo's
> > (instances with the same SID) in two different rooms (oracle homes)?
> >
>
> Your attempt to twist this {home=room} analogy is not quite appropriate to
> the situation ... in Oracle's case {server=room} and {home=chair}
Not a twist. The analogy is quite appropriate to the listeners. For
them oracle home is a room. Everything listener needs is (or could be)
in OH.
It's actually quite appropriate to the database engine itself. You can
keep almost everything in separate rooms for them. Some things like
/tmp/.oracle seems to be hardcoded but again I never tried to change
TMP/TMP_DIR. There is a chance even this could be kept separate.
In short, on Unix (server != room && OH == room:)
Regards,
Igor
Howard J. Rogers
> Ok, consider yourself proven wrong.
Lest there be any doubt, I do. And I've posted as such elsewhere. It
happens.
> Fabrizio beat me to it, but I
> tested and received the same result. No RAC, no VMware, no tricks up my
> sleeve -- just a plain vanilla Solaris 8 box.
>
> I read the OP as an academic exercise, not a "is this a good idea in the
> real world" question.
So did I, actually.
> But then some people in this newsgroup don't even
> like the idea of running two DIFFERENTLY named instances on the same
> box, so it's not surprising the level of resistance to even debating the
> possibility.
I wasn't resistant, actually. I was 100% convinced it couldn't be done. It's
what coming from a Windows background does for you (where it can't be done.
Famous last words, I realise!).
> It's due to good design decisions made a long time ago in the area of
> Unix shared memory (System V IPC).
I believe you. I don't know what it means, but I believe you!
[snip much good technical stuff]
> I'm quite willing to believe that it won't work under Windows, but then
> fork() won't work under Windows either, last time I checked.
>
> Setting up a separate listeners (or separate service names in the same
> listener) for each instance is left as an exercise for the reader. ;-)
>
> --Mark Bole
Regards
HJR
Howard J. Rogers
> "Howard J. Rogers" <h...@dizwell.com> wrote in message
> news:<412f17f2$0$9811$afc3...@news.optusnet.com.au>...
>> Sybrand Bakker wrote:
>>
>> > On 27 Aug 2004 01:33:43 -0700, ilal...@usa.net (Igor Laletin) wrote:
>> >
>> >>What if two people (listeners) called two different Marcelo's
>> >>(instances with the same SID) in two different rooms (oracle homes)?
>> >
>> > It will be an administration nightmare wouldn't it?
>
> Yes, of course, but OP didn't talk about real life, right? It's a
> theoretical question. Nobody wants to inflict such a situation on
> himself.
>
>> It will be an administration impossibility.
>
>> You and I are both in agreement, I think: it's not actually *possible*.
>> Never mind would it be a good idea.
>
> It's actually *possible*, at least on Unix. See Fabrizio's email. Yes,
> it's not a good idea but posssible.
>
>> If someone wants to jump through the necessary hoops to prove me wrong,
>> be my guest:
>
> Nothing to do with the hoops. Why take it personally? It's not about
> _you_. It's about SID's.
I didn't take it personally in the sense you imply. I made a statement that
it wasn't actually possible. So whoever made it happen would have proven
me, and my statement, wrong. It's basic grammar, really.
And I believe that having to have two ORACLE_BASEs and two different Oracle
users to be jumping through hoops. You are, of course, free to differ. And
to try the same trick on a Windows machine.
>> in the meantime, it is impossible to create two instances on the
>> same machine with the same ORACLE_SID, and I don't care how clever you
>> get in distinguishing between different installations.
>
> Well, it's possible on Unix and it doesn't involve words 'clever' and
> 'jumping'.
That's fair enough, as you can read in my reply to Fabrizio's post. The day
we stop learning things here is the day I stop posting.
And whilst you might not think it clever or hoop-jumping. I do.
Regards
HJR
Howard J. Rogers
[snip]
> Two different DBs on the same host (a linux system).
> I needed two different users and two different ORACLE_BASE.
>
> But maybe the main topic was another and my coompreension of the topic
> was messy...
Your comprehension was spot on, and you've proved it's possible, and
accordingly I was 100% wrong. So my apologies for making a mistake, and
congratulations for proving the point so nicely again. Once again,
practical testing beats theorising, hands down.
Regards
HJR
FM
Thank you. I feared to have completly misunderstood the subject of the
discussion.
In my mind it was theoretically possible but I was quite unsure about
the practical part.
I was lucky to have a test machine ready at the moment.
However, pondering, I believe possible to achieve the same result even
with the one user and, likely, with the same oracle engine.
My idea is to keep the PATH variable pointing at the right binaries
(where the oracle installation reside) and to change the ORACLE_HOME and
ORACLE_BASE in the environment. Of course, you have to create the right
directory trees:
$ORACLE_BASE/amind/<SID>/etc
$ORACLE_HOME/dbs
$ORACLE_HOME/network/etc
(Ok, I have doubts regarding libraries and messages files).
I don't see any advantage to have a similar situation and it would be an
administration hell.
I don't think I shall test it... at least not during the weekend.
On windows, I fear, the matter is much more complicated and I'm not a
windows guru.
The most complicated part is the services part.
Is it possible to rename a service? Does really the service name matter
for oracle?
On this newsgroup there are tons of persons more skilled and able to
answer accordingly.
I apologize for the lack of competencies.
Regards
FM
I believe you are right.
Hans Forbrich
Now you have me wondering ... especially since one LISTENER, regardless of
which home it comes from, can service all the instances on the machine,
regardless of which homes they belong to.
So perhaps you could help with this test I've got here .... one userid, two
oracle homes, two listeners.
Both instances are up, both listeners are up (one on port 1521 the other on
1531)
Oracle9i R2, 9.0.4
SuSE Linux Enterprise 8
user=oracle
ORACLE_HOME=/opt/oracle/product/ora904
ORACLE_HOME=/opt/oracle/product/ora904b
both SID=PTS1
From my remote Oracle Networking client's TNSNAMES.ora,
PTS1A looks at port 1521
PTS1B looks for the service on port 1531
Both apparently ONLY talk to the second instance - any changes I make in the
instance are identical regardless of whether I connect to PTS1A or PTS1B.
What tests, or configuration files, would you suggest? Since you sound so
positive, I'm sure I just misconfigured something ...
/Hans
oracle@fcc02:~> ps -ef | grep pts
oracle 26267 1 0 13:48 ? 00:00:00 ora_pmon_pts1
oracle 26269 1 0 13:48 ? 00:00:00 ora_dbw0_pts1
oracle 26271 1 0 13:48 ? 00:00:00 ora_lgwr_pts1
oracle 26273 1 0 13:48 ? 00:00:05 ora_ckpt_pts1
oracle 26275 1 0 13:48 ? 00:00:00 ora_smon_pts1
oracle 26277 1 0 13:48 ? 00:00:00 ora_reco_pts1
oracle 26279 1 0 13:48 ? 00:00:00 ora_cjq0_pts1
oracle 26281 1 0 13:48 ? 00:00:06 ora_qmn0_pts1
oracle 26283 1 0 13:48 ? 00:00:00 ora_s000_pts1
oracle 26285 1 0 13:48 ? 00:00:00 ora_d000_pts1
oracle 8999 1 0 14:43 ? 00:00:00 ora_pmon_pts1
oracle 9004 1 0 14:43 ? 00:00:00 ora_dbw0_pts1
oracle 9006 1 0 14:43 ? 00:00:00 ora_lgwr_pts1
oracle 9008 1 0 14:43 ? 00:00:03 ora_ckpt_pts1
oracle 9010 1 0 14:43 ? 00:00:00 ora_smon_pts1
oracle 9012 1 0 14:43 ? 00:00:00 ora_reco_pts1
oracle 9014 1 0 14:43 ? 00:00:00 ora_cjq0_pts1
oracle 9016 1 0 14:43 ? 00:00:05 ora_qmn0_pts1
oracle 9018 1 0 14:43 ? 00:00:00 ora_s000_pts1
oracle 9020 1 0 14:43 ? 00:00:00 ora_d000_pts1
oracle 13580 1 0 19:44 pts/6 00:00:00
/opt/oracle/product/ora904/bin/tnslsnr LISTENER -inherit
oracle 14135 1 0 19:48 pts/7 00:00:00
/opt/oracle/product/ora904b/bin/tnslsnr LISTENER -inherit
oracle 14730 12881 0 19:53 pts/7 00:00:00 grep IS
oracle@fcc02:~>
FirehawkŽ
> But maybe the main topic was another and my coompreension of the topic
> was messy...
No, main topic was exactly this.
Yes, it's possible then.
Hans Forbrich
Yes, it is possible.
However, based on my current tests, configuring the listener(s) to keep the
access correct to two concurrently running instances is going to be messy -
especially if all is under one userid.
My current config - 2 instances (both DBs called PTS1), two different
Oracle_Homes, one userid ...
- point to Oracle_Home1, bring instanceA up
- run instance A OK
- point to Oracle_Home2, bring instanceB up
- all accesses to the SID seem to go to instanceB (even admin)
- bring down instanceB,
all requests for A are OK,
all requests for B say it's not up.
It might be possible to get discrimination through config - I haven't found
it yet.
/Hans
Igor Laletin
> So perhaps you could help with this test I've got here .... one userid, two
> oracle homes, two listeners.
Without looking at listener.ora and unix environments it would be just
guessing. Any chance you forget to change ORACLE_HOME in SID_DESC?
Also did you install the second OH from scratch or copied it from the
first one? Shouldn't actually matter with the right environment but
again I can't see yours. If latter, try to relink it with the right
LD_LIBRARY_PATH or set it explicitly.
> /Hans
/Igor
Hans Forbrich
> Hans Forbrich <forb...@yahoo.net> wrote in message
> news:<RXaYc.71105$X12.53526@edtnps84>...
>> So perhaps you could help with this test I've got here .... one userid,
>> two oracle homes, two listeners.
>
> Without looking at listener.ora and unix environments it would be just
> guessing. Any chance you forget to change ORACLE_HOME in SID_DESC?
No - the OH is correct. That can be verified by the fact that when only the
one instance is up (either one), things access the instance correctly and
attempt to access the other instance fails with a 'not there' message.
When both are up, only the last one started is accessible (regardless of
which order in which they are started.)
>
> Also did you install the second OH from scratch or copied it from the
> first one? Shouldn't actually matter with the right environment but
> again I can't see yours. If latter, try to relink it with the right
> LD_LIBRARY_PATH or set it explicitly.
Completely separate install and DBCA.
listener.ora for OH 'a'
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = fcc02)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/ora904)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = pts1.xyz.ca)
(ORACLE_HOME = /opt/oracle/product/ora904)
(SID_NAME = pts1)
)
)
listener.ora for OH 'B',
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = fcc02)(PORT = 1531))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = pts1.xyz.ca)
(ORACLE_HOME = /opt/oracle/product/ora904b)
(SID_NAME = pts1)
)
)
Note the IPC protocol on OH 'B' is removed because second listeners
complains that IPC is already on use.
Comment:
I would actively discourage duplication of an instance name on a single
machine in any case. This test is only to verify whether it's possible,
not whether it's a good idea.
I still stick with my original analogy. <g> Wanna continue?
/Hans
Hans Forbrich
PTS1A =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = fcc02)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pts1.xyz.ca)
)
)
PTS1B =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = fcc02)(PORT = 1531))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pts1.xyz.ca)
)
)
Based on your suggestion, the only discrimination that should be necessary
is the port.
If I understand the listener correctly, it listens on a specific port for a
request for a specific service. It will then look up the relevant
information from the service or sid being passed from the client.
I believe that your suggestion of discrimination by Oracle Home would
require the Oracle Home be [optionally] passed from the client. So far I
have not found any way of entering the Oracle Home into the TNSNAMES.ora
file on the client.
BTW: the listener.ora files were originally created as part of the dbca
database create, and only 'B' was manually edited to remove the
IPC/PLSextprc and change the port to 1531. The above tnsnames.ora was
manually created.
Any other suggestions?
/Hans
Jeff
>Jeff wrote:
>
>> I say "fleas on the same dog" makes for an apt analogy apropos of this
>> thread.
>> If you call the dog, which flea jumps? grin
>>
>
>Since you haven't given either an ID, you can't tell them apart. You need
>to dig into your kitbag to eliminate the duplicates & redesign your model.
You don't name your dog's fleas?! :-)
Hans Forbrich
Please, please, please,
Of course I name my dog's fleas!
Yup, that's right - I sure do
I name them Flea-One and Flea-Two.
And when I call to those fleas,
I hope you will notice, please,
that Flea-One does not come when I call
"Flea-Two" ... and that's not all.
When I don't want to talk to Flea-Two,
as on occasion I'm sure you do to,
I simply call Flea-One by name
and he'll answer, alone, just the same.
But if I just simply called out "hey you",
then both would respond, and then two
fleas would try to cross o'er the line
e'en when only one would do just fine.
What would really cause me much grief,
and everyone else disbelief,
is the total chaos and confusion
and utter mental contusion
that would arise if I called them the same
or even worse, gave them no name,
'cause then I couldn't tell 'em apart
or fix 'em when the dog did ... scratch?
Joel Garry
Maybe there is "too much" intelligence in the listener. Please post
output of lsnrctl status, both homes.
jg
--
@home.com is bogus.
Would someone explain the find command to Bill Gates?
http://www.zdnet.com.au/insight/software/0,39023769,39157671,00.htm
Hans Forbrich
> Maybe there is "too much" intelligence in the listener. Please post
> output of lsnrctl status, both homes.
>
> jg
I think the Listener has just the right amount of intelligence. More will
make configuring even harder :-Q
You will note from the log file from home 'B' that the listener simply does
not have enough information to determine which specific instance ... there
is NO HOME information transmitted from the client. And also note that the
GLOBAL Database Name (and therefore the service) is identical for both
instances.
Remember that a listener has the ability to listen on behalf of all services
regardless of home (even non-DB services). Therefore, any environment that
uses duplicate SIDs *and* duplicate Global DB Name, is asking for this kind
of situation. Highly _not_ recomended.
However, here you go:
------------------------------------------------
Listener 'A' from OH=ora904
oracle@fcc02:~> lsnrctl status
LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 30-AUG-2004 18:52:03
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date 28-AUG-2004 19:44:34
Uptime 1 days 23 hr. 7 min. 28 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File
/opt/oracle/product/ora904/network/admin/listener.ora
Listener Log File
/opt/oracle/product/ora904/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fcc02)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fcc02)(PORT=8080))(Presentation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fcc02)(PORT=2100))(Presentation=FTP)(Session=RAW))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
service...
Service "pts1.xyz.ca" has 3 instance(s).
Instance "pts1", status UNKNOWN, has 1 handler(s) for this service...
Instance "pts1", status READY, has 1 handler(s) for this service...
Instance "pts1", status READY, has 1 handler(s) for this service...
Service "pts1XDB.xyz.ca" has 2 instance(s).
Instance "pts1", status READY, has 1 handler(s) for this service...
Instance "pts1", status READY, has 1 handler(s) for this service...
The command completed successfully
= = = = = = = = = = = = = = = = = =
Listener 'B' from OH=ora904b
------------------------------------------------
oracle@fcc02:~> lsnrctl status
LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 30-AUG-2004 18:55:14
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=fcc02)(PORT=1531)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date 28-AUG-2004 19:48:26
Uptime 1 days 23 hr. 6 min. 48 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File
/opt/oracle/product/ora904b/network/admin/listener.ora
Listener Log File
/opt/oracle/product/ora904b/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fcc02)(PORT=1531)))
Services Summary...
Service "pts1.forbrichcomputing.ca" has 1 instance(s).
Instance "pts1", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
= = = = = = = = = = = = = = = = = = = = =
And, for your amusement, the log from 'B' ... (replacing IP and domain info)
-----------------------------------------
Started with pid=13977
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adapter error
TNS-00512: Address already in use
Linux Error: 98: Address already in use
TNSLSNR for Linux: Version 9.2.0.4.0 - Production on 28-AUG-2004 19:48:26
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
System parameter file is
/opt/oracle/product/ora904b/network/admin/listener.ora
Log messages written to /opt/oracle/product/ora904b/network/log/listener.log
Trace information written to
/opt/oracle/product/ora904b/network/trace/listener.trc
Trace level is currently 0
Started with pid=14135
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=fcc02)(PORT=1531)))
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
28-AUG-2004 19:48:26 *
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=fcc02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153093
120)) * status * 0
28-AUG-2004 19:50:35 *
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PTS1.XYZ.CA)(CID=(PROGRAM=C:\oracle\ora92\bin\sqlplusw.exe
)(HOST=FCC001L)(USER=Hans))) *
(ADDRESS=(PROTOCOL=tcp)(HOST=?.?.?.88)(PORT=1177)) * establish *
PTS1.XYZ.CA * 0
28-AUG-2004 20:01:29 *
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=PTS1.XYZ.CA)(CID=(PROGRAM=C:\oracle\ora92\bin\sqlplusw.exe
)(HOST=FCC001L)(USER=Hans))) *
(ADDRESS=(PROTOCOL=tcp)(HOST=?.?.?.88)(PORT=1182)) * establish *
PTS1.XYZ.CA * 0
30-AUG-2004 18:55:14 *
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=fcc02)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153093
120)) * status * 0
oracle@fcc02:/opt/oracle/product/ora904b/network/log>
Igor Laletin
> I believe that your suggestion of discrimination by Oracle Home would
> require the Oracle Home be [optionally] passed from the client. So far I
> have not found any way of entering the Oracle Home into the TNSNAMES.ora
> file on the client.
My understanding is the listener gets a service name or sid from
client and look in its description for this service (SID_DESC). It
uses ORACLE_HOME from this description to spawn
$ORACLE_HOME/bin/oracle (for dedicated connections). No need to pass
OH from a client.
> Any other suggestions?
When I posted on Friday I had two databases with the same SID and two
listeners for them on the same host. I was able to connect to the
right instances but software/databases were installed in different
accounts. It shouldn't matter but to make sure I repeated this in one
account. Setup is similar to yours and it still works (Oracle 9.2.4,
HP-UX 11i). I actually copied soft, not installed it from scratch, but
I relinked it and check that the search order for the shared libraries
is a correct one.
Your setup looks OK to me (is sid pts1 in lowcase though? it is in
your listener.ora). I would double check unix environment for the
sessions you start the listeners from: ORACLE_HOME, TNS_ADMIN, PATH,
LD_LIBRARY_PATH (not set in my case).
Some of your problems may be because you run one of the listeners on a
default port 1521. Both instances with identical SID's get registered
with the listener.
> /Hans
/Igor
Jeff
"Dr. Zeuss, I presume?" :-)
In article <ODGYc.74758$X12.67381@edtnps84>, Hans Forbrich
Hans Forbrich
> Stepping out of the jungle gloom:
> "Dr. Zeuss, I presume?" :-)
Nah - 'forbrich', just a poor imitation of our good, late Dr.
/Hans
Joel Garry
> Joel Garry wrote:
>
> > Maybe there is "too much" intelligence in the listener. Please post
> > output of lsnrctl status, both homes.
> >
> > jg
>
> I think the Listener has just the right amount of intelligence. More will
> make configuring even harder :-Q
>
> You will note from the log file from home 'B' that the listener simply does
> not have enough information to determine which specific instance ... there
> is NO HOME information transmitted from the client. And also note that the
> GLOBAL Database Name (and therefore the service) is identical for both
> instances.
>
> Remember that a listener has the ability to listen on behalf of all services
> regardless of home (even non-DB services). Therefore, any environment that
> uses duplicate SIDs *and* duplicate Global DB Name, is asking for this kind
> of situation. Highly _not_ recomended.
>
So don't use duplicate Global DB Name?
http://download-west.oracle.com/docs/cd/B10501_01/rac.920/a96600/undrstnd.htm#RACIN608
Sorry I don't have the resources to try this, hope it helps, I'm
hoping that the failover enhancements mysteriously enable it to work,
simply because you can specify home in the listener.ora, and ports (at
least) to get to different listeners in client tnsnames.
jg
--
@home.com is bogus.
"Where did this backup copy thing come from? A digital thing lasts
forever." - Jack Valenti
Hans Forbrich
>
> So don't use duplicate Global DB Name?
That's my point as well.
It is my belief that Global DB name (and therefore Service) _must_ be unique
in the environment. I'd think you get sufficient separation as all shared
memory and semaphore capabilities are tied to a userid. My understanding,
by changing userid, you change environment.
/Hans
Jeff
as a writer of books for childish DBAs: "The Cat and the RAC", "Horton Tunes
a Query", "Green Eggs and Ham." Well, maybe not that last one, but then I'm
not the writer here, hey? :-)
In article <pZ%Yc.35476$A8.32832@edtnps89>, Hans Forbrich <forb...@yahoo.net>
wrote:
Hans Forbrich
> Yes, yes... but don't sell yourself short, my boy! You'd have quite a
> future
> as a writer of books for childish DBAs: "The Cat and the RAC", "Horton
> Tunes
> a Query", "Green Eggs and Ham." Well, maybe not that last one, but then
> I'm
> not the writer here, hey? :-)
Thanks, but at 50 I may be too old for another part-time career in the arts.
<g>
I note that some of the Seuss stories had good advice under the covers, and
some seemed just plain fun. I can't help it, but I think the titles should
be
- RedHat Cat and the RAC
- Burleson Tunes a Query
- Fox on locks, Latches and batches
- Green omelets and spam
/Hans
Dislaimer: None of the above implying anything about the quality or
capability of any person, product or thing.
Howard J. Rogers
> Jeff wrote:
>
>> Yes, yes... but don't sell yourself short, my boy! You'd have quite a
>> future
>> as a writer of books for childish DBAs: "The Cat and the RAC", "Horton
>> Tunes
>> a Query", "Green Eggs and Ham." Well, maybe not that last one, but then
>> I'm
>> not the writer here, hey? :-)
>
> Thanks, but at 50 I may be too old for another part-time career in the
> arts. <g>
>
> I note that some of the Seuss stories had good advice under the covers,
> and
> some seemed just plain fun. I can't help it, but I think the titles
> should be
>
> - RedHat Cat and the RAC
> - Burleson Tunes a Query
So this is indeed a work of fantasy and fiction, Hans?
;-)
Regards
HJR
Hans Forbrich
> So this is indeed a work of fantasy and fiction, Hans?
> ;-)
a) I KNEW you'd bite
b) thinking of multiple works
c) could be either depending on collaborator <g>
d) see my disclaimer!
/Hans
Howard J. Rogers
> Howard J. Rogers wrote:
>
>> So this is indeed a work of fantasy and fiction, Hans?
>> ;-)
>
>
> a) I KNEW you'd bite
Hopeless, aren't I?
> b) thinking of multiple works
> c) could be either depending on collaborator <g>
> d) see my disclaimer!
>
> /Hans
I remember at the end of 'Titanic', the credits rolled and up popped the
words 'Any resemblance to actual people or events is purely
coincidental...'. Some disclaimers should be, er... disclaimed!
Regards
HJR
iggy_fe...@hotmail.com
joel garry
> Greetings from 2013. I've discussed this topic in detail athttp://iggyfernandez.wordpress.com/2013/01/08/two-instances-with-the-...
Normally we'd discourage newbies from revivifying old threads, but
since you're not a newbie and you acknowledged the timewarp and it is
actually interesting, I say, well played. But Sybrand's observation
ought to be explicit in any such exposition, random people seeing cool
things like this sometimes don't get that because you can do
something, doesn't mean you should.
http://www.cbs8.com/story/20559037/national-math-contest-held-in-san-diego
Sybrand Bakker
<joel-...@home.com> wrote:
>On Jan 10, 12:41�pm, iggy_fernan...@hotmail.com wrote:
>> Greetings from 2013. I've discussed this topic in detail athttp://iggyfernandez.wordpress.com/2013/01/08/two-instances-with-the-...
>
>Normally we'd discourage newbies from revivifying old threads, but
>since you're not a newbie and you acknowledged the timewarp and it is
>actually interesting, I say, well played. But Sybrand's observation
>ought to be explicit in any such exposition, random people seeing cool
>things like this sometimes don't get that because you can do
>something, doesn't mean you should.
>
>jg
his blog entry.
------
Sybrand Bakker
Senior Oracle DBA
Eric
> On Fri, 11 Jan 2013 08:58:33 -0800 (PST), joel garry
><joel-...@home.com> wrote:
>
>>> Greetings from 2013. I've discussed this topic in detail at
>>> http://iggyfernandez.wordpress.com/2013/01/08/two-instances-with-the-...
>>
>>Normally we'd discourage newbies from revivifying old threads, but
>>since you're not a newbie and you acknowledged the timewarp and it is
>>actually interesting, I say, well played. But Sybrand's observation
>>ought to be explicit in any such exposition, random people seeing cool
>>things like this sometimes don't get that because you can do
>>something, doesn't mean you should.
>>
>>jg
>
> I just commented on the blog of Mr Fernandsz and asked hin to remove
> his blog entry.
However, he does say "As an academic exercise just to prove the point,",
so he's not really encouraging anybody. Maybe he should emphasize that
phrase, and add that there's no real-world reason why you should ever
want to do that (and several why you shouldn't).
You really have no right to expect people to remove blog posts merely
at your request.
Eric
--
ms fnd in a lbry
Mladen Gogala
>
> You really have no right to expect people to remove blog posts merely
> at your request.
--
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
Eric
> On Sat, 12 Jan 2013 10:29:43 +0000, Eric wrote:
>
>>
>> You really have no right to expect people to remove blog posts merely
>> at your request.
>
> But he has the full right to make such request.
John Hurley
# I just commented on the blog of Mr Fernandsz and asked hin to remove
his blog entry.
The whole blog entry ... when he made it clear that he was documenting
some weird fringe case about how oracle memory allocation works?
I can understand if one has posted a comment on a blog that one later
wants removed ... asking to get that done ... more than fair to me.
I can understand blogging yourself about what one considers a terrible
idea ( and this idea is more than horrible for sure ).
Asking someone though to remove a whole blog post about a technical
and esoteric subject ... thats a little ... different.