Home » RDBMS Server » Server Administration » Connect to Specific instance (10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
Connect to Specific instance [message #437591] Tue, 05 January 2010 03:09 Go to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
All,

In case of multiple instances running on Server How to connect to specific instance ?

-Rahul

[Updated on: Tue, 05 January 2010 03:18]

Report message to a moderator

Re: Connect to Specific instance [message #437593 is a reply to message #437591] Tue, 05 January 2010 03:18 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
especially on Administering Oracle Real Application Clusters with SQL*Plus

sriram Smile

Report message to a moderator

icon12.gif  Re: Connect to Specific instance [message #437662 is a reply to message #437591] Tue, 05 January 2010 07:35 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
1) You need to know the instance name (ORACLE_SID), to find out the names on Unix servers look at file /etc/oratab (or /var/opt/oracle/oratab).

2) Set the environment:
$ export ORACLE_SID=MYDB  #<= This is the instance name
$ ORAENV_ASK=NO
$ . /usr/local/bin/oraenv


3) Connect to the database:
$ sqlplus username/passwd

[Updated on: Tue, 05 January 2010 08:15] by Moderator

Report message to a moderator

Re: Connect to Specific instance [message #437729 is a reply to message #437662] Tue, 05 January 2010 20:32 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
LKBrwn_DBA,

Quote:


2) Set the environment:

$ export ORACLE_SID=MYDB #<= This is the instance name
$ ORAENV_ASK=NO
$ . /usr/local/bin/oraenv



I presume ORACLE_SID is physical database name. base on your suggestion So shall we say that each instance running on server has different ORACLE_SID ? [* in this case when we run multiple instances we have same number ORACLE_SID to be specified! ]

if it is very true then In above case we have multiple database names for same set of data!!!

-Rahul

Report message to a moderator

Re: Connect to Specific instance [message #437730 is a reply to message #437729] Tue, 05 January 2010 20:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>if it is very true then In above case we have multiple database names for same set of data!!!

What does this mean?
If possible, provide specific example(s).

Report message to a moderator

Re: Connect to Specific instance [message #437734 is a reply to message #437730] Tue, 05 January 2010 21:47 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
BlackSwan wrote on Tue, 05 January 2010 20:52
>
What does this mean?
If possible, provide specific example(s).


I mean to say that if we have one sid for one specific instance then there are multiple sids for one database.

Ex.

Database name = XYZ ( on box A )

on same box A we have say 5 instances running XYZ_1...XYZ_5
so in above example it is true that we can se XYZ_1 or XYZ_n as oracle_sid and connect it to the Database.

-------------------

so my next question is can we run multiple instances on same box for one database. and how can we connect to one these instacne?

Why I am asking this question is :- I want to create separate instance for different deptpartments and pin separate set of tables ( as per department ) in respective instance memory.

is it a valid scenario ?

-Rahul

Report message to a moderator

Re: Connect to Specific instance [message #437735 is a reply to message #437734] Tue, 05 January 2010 22:01 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
It's the time to read this..
The Instance and the Database
All the your doubts are clarified here..



sriram Smile

Report message to a moderator

Re: Connect to Specific instance [message #437737 is a reply to message #437735] Tue, 05 January 2010 22:02 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
More over these article will help you..
Read this

This too

Goodluck

sriram Smile

Report message to a moderator

Re: Connect to Specific instance [message #437739 is a reply to message #437734] Tue, 05 January 2010 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I mean to say that if we have one sid for one specific instance then there are multiple sids for one database.

SID is same as DB_NAME & is used to identify an instance.
A "database" is a collection of data files.
An instance is an open database.
With RAC, multiple instances can be running against single database


>Why I am asking this question is :- I want to create separate instance for different deptpartments and pin separate set of tables ( as per department ) in respective instance memory.
>is it a valid scenario ?
Valid as in can it be done? Then answer is YES.
But you really should avoid doing this.

You really need to Read The Fine Manual in URL below
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/toc.htm

Report message to a moderator

Re: Connect to Specific instance [message #437901 is a reply to message #437734] Wed, 06 January 2010 08:19 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
rahulvb wrote on Tue, 05 January 2010 22:47

Why I am asking this question is :- I want to create separate instance for different deptpartments and pin separate set of tables ( as per department ) in respective instance memory.


I think this is a bad design. What I would do is have a different schema for each department in a single database. You may come from a Microsoft world where what we call schemas are separate databases.

Report message to a moderator

Re: Connect to Specific instance [message #437909 is a reply to message #437901] Wed, 06 January 2010 08:55 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
joy_division wrote on Wed, 06 January 2010 08:19

... You may come from a Microsoft world ......


Good caught Joy ..

Yes I was MS SQL DBA Cool ..and may be I am prejudise about oracle.

-Rahul Cool

[Updated on: Wed, 06 January 2010 08:57]

Report message to a moderator

Re: Connect to Specific instance [message #437915 is a reply to message #437909] Wed, 06 January 2010 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
In this case, you should read Database Concepts then we'll talk the same language.

Regards
Michel

[Updated on: Wed, 06 January 2010 09:06]

Report message to a moderator

Re: Connect to Specific instance [message #437927 is a reply to message #437591] Wed, 06 January 2010 09:35 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
If you are on the server, set ORACLE_SID (as already suggested)
or
Use a tnsstring to connect to the specific database (server or client).

sqlplus scott/tiger@xyz1

[Updated on: Wed, 06 January 2010 09:35]

Report message to a moderator

Previous Topic: Error when start DB
Next Topic: How to copy the Oracle database structure to new server?
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 29 11:50:56 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.