Kalpesh Patel, April 03, 2001 - 10:26 am UTC
Hari, May 08, 2001 - 10:26 am UTC
Clear and "clean" answer!
Elka, August 28, 2001 - 1:32 am UTC
At some point I was confused too. I am glad for verification on that matter. Is oracle_sid is = to instance name? I think it simply allies for network use.
August 28, 2001 - 7:10 am UTC
A sid is "sort of" the same as the instance name -- if you:
$ echo $ORACLE_SID
ora8i
ops$tkyte@ORA8I.WORLD> select instance_name from v$instance;
INSTANCE_NAME
----------------
ora8i
you'll see them as the same. I would go a step further -- the combination of:
o ORACLE_HOME plus
o ORACLE_SID plus
o HOSTNAME
identifies the instance (i can have two instances in different homes on a single machine with the same sid).
Some more clarification
Niraj, March 27, 2002 - 6:48 am UTC
Hi Tom,
The explanation is clear, But I just need your help in understanding how exactly we can implement it. A brief example will help a lot.
Thanks in advance.
March 27, 2002 - 8:33 am UTC
No, I'd need clarification from you. What is "it". There are lots of "its" on this page.
Implement "what"?
Don Stamps, November 17, 2002 - 1:20 am UTC
The reason so many of us ask about putting a standby database on the same server as the primary database, is that it is homework for Oracle DB Admin courses, which require the creation of a standby DB, and most of us only have access to a single system.
In my case, I am trying to create a standby DB on the same PC (Windows 2000). Not easy. Not there yet.
There is a document on your site about doing so on a Unix system, but doesn't quite get me there on my Windows System.
Any points with respect to primary / standby on a PC running Windows 2000 would be greatly appreciated.
Thank you very much!
database
mo, November 17, 2002 - 11:37 am UTC
Tom:
1. Would "select * from v$database" give you the database name. It seems that usually it is the same as the instance name.
2. When you connect in sql*plus is host the database or intance name? I think it is database.
3. for the scott/tiger is not there a host?
THank you,
November 17, 2002 - 1:24 pm UTC
1) "usually" -- good choice of words. not "always", "usually"
2) you think wrong. it is a tns connect string which might be connecting you to a service (a colleciton of instances all serving the same database -- in this case you would be right). or it might be connecting you to a specific instance, in which case you are not being connected to a database but to an instance and whatever database it just happens to have mounted (in that case you would be wrong).
You would need to read the networking guides in order to understand the subtle difference.
3) huh? makes no sense to me. scott/tiger is a username/password.
HOST really has nothing to do with any of these questions.
instance
mo, November 17, 2002 - 4:07 pm UTC
TOm:
I know that scott/tiger is a userid/password but I noticed in your book that when you do
@connect scott/tiger --->missing @host
you do not specify a host or tns connect string? So which instance/database will it use?
November 17, 2002 - 5:41 pm UTC
the local one as defined by your oracle_sid and oracle_home or the two_task environment variable (local on windoze).
OPS-RAC
ram, January 14, 2003 - 3:15 am UTC
Great explanation!
<quote>
A database may be mounted and opened by many INSTANCES (Parallel Server) concurrently <unquote>
does RAC work similar to OPS with regard to mounting/opening the db by multiple instances (besides the new cache fusion) ?
on Windows
r, January 20, 2003 - 12:25 pm UTC
Hi Tom,
I use DBCA on Windows to create additional databases. Now, when I create a database I think it also creates an instance(correct me if I am wrong) and hence I am able to get both the databases up & running at the same time. I understand that an instance can only mount/open 1 database at a given point. On windows do we have an option(using DBCA) to create only the database and not a new instance so that I could use the existing instance to mount either of the databases.
Thanks very much indeed.
January 20, 2003 - 12:35 pm UTC
DBCA doesn't create an instance.
An instance is a set of processes + memory. when you stop the service, the INSTANCE is gone.
So, DBCA isn't creating an instance, it creates a database. The instance is fleeting, do a shutdown and goodbye instance.
So, just don't start the service (which is just a definition -- the service itself consumes no resources) and you'll have no instance.
Follow up question.
Saibabu Devabhaktuni, January 20, 2003 - 1:34 pm UTC
Hi Tom,
You said sid/instance is a combination of ORACLE_HOME plus, ORACLE_SID plus, HOSTNAME. You also said "i can have two instances in different homes on a single machine with the same sid".
Each Oracle instance have background processes something like this "ora_pmon_TEST".
Single Oracle unix account can have two different ORACLE_HOME's...but they cannot have two sid's with same name. If it is possible what will the output of "ps -ef|grep pmon"....
Thanks
Sai
January 20, 2003 - 1:58 pm UTC
the output -- a list of processes of course?
Something more ..
r, January 20, 2003 - 3:15 pm UTC
Hi Tom,
No, I don't understand. Please bear with me.
Okay ..when I create a database using DBCA I get an additional service called "OracleService<SID>" for the new database. So, I have got 2 services running each for the database. Now when I shutdown one of the services that doesn't mean that the other database will also be shutdown. Am I right ? If yes, then how come you say "just don't start the service and you'll have no instance" , as stopping one of the services does not mean that the instance is gone since the other database is still operational.
Thanks for your patience.
January 20, 2003 - 3:40 pm UTC
an instance = set of running processes + memory. an instance is transient.
a dataqbase = set of files. a database is forever.
A service = something windows artificially introduces to confuse us.
Now, in your case, you have TWO instances and TWO databases. If you stop one of the services -- you'll have ONE instance and TWO databases. If you stop the other service, you'll have ZERO instances and TWO databases.
You'll always have TWO databases (unless you erase them).
You'll have as many instances as you want at any point in time by starting and stopping these services.
So, to answer "when I shutodnw one of the services that doesn't mean that the other INSTANCE <not database> will also be shutdown" -- that is correct, they are independent of eachother.
"If yes, then how come you say 'just don't .....'" -- I said that cause if you don't start the service, the instance won't be running.
I'm not understanding your confusion. Each instance is separate and distinct, you start them independent of eachother. They have nothing whatsoever to do with eachother.
The instance IS in fact gone -- since an INSTANCE is just processes+memory. When you stop the service -- goodbye instance. Database -- oh, that is still there, instance -- gone.
So -- what exactly is the question?
more ...
r, January 20, 2003 - 4:19 pm UTC
Tom,
This is the confusion. In your earlier reply you said that the DBCA does not create an instance but only database. That's fine. But when I said that I see two services you said "Now, in your case, you have TWO instances and TWO databases".
Secondly, if I shutdown one of the services it means that there is 1 instance and 2 database. Now, this means that I can shutdown the database for which I service is currently running and startup the other database for which I just shutdown the service. Is this correct ?
Thanks again for your patience. I appreciate it.
January 20, 2003 - 4:24 pm UTC
I was responding to your comment:
So, I have got 2 services running
each for the database.
The services are running you said. Hence you have two instances going.
You do not shutdown a database -- you shutdown an instance, but anyway... For practicality sake think of it this way:
o service running = processes+memory up = some database available.
I don't know how to say it. An instance is just a process running. stop the process and that instance "disappears" -- never ever to be seen again - NEVER.
For each database you want accessible on your machine, you will have an instance running.
If you do not want to have access to that datbase -- just stop that instance, easiest way on windows, stop the service.
An instance doesn't "exist" anywhere really -- it is just processes+memory.
Don't let the service confuse you. It is nothing, a windows artifact, not meaningful.
dear me ..
r, January 20, 2003 - 5:55 pm UTC
Tom,
I totally understand what you are saying about an instance and the database but this Windows services has confused me a lot.
Okay ... one last try. Hopefully this will clear the confusion.
I said "So, I have got 2 services running each for the database" because when I create a database using a DBCA it by default creates a service for the new database. And hence I can assume that DBCA does create an instance. I infer this from you following statement.
"The services are running you said. Hence you have two instances going."
So, finally back to my original question. "Does DBCA create an instance ?" and you said "DBCA doesn't create an instance".
If your answer to the question is that the DBCA always creates a new instance then I would ask "Is there any way that I can create a database without an instance using DBCA".
Do you see where I am coming from ?
I would be very thankful to you for clarifying this confusion.
January 20, 2003 - 6:11 pm UTC
services are a "windoze" neccessary evil. As they say in the wizard of oz "ignore that man behind the curtain".
They are meaningless -- they are just things windows needs to start these things up in the background.
A service is a windoze thing - it is not anything to do with the database other than on that platform we need them in order to get started up in the background....
DBCA doesn't create an instance. It creates a database (set of files) and on windows it registers the necessary services that can be used to start an instance when you want.
Is there anyway to create a database without dbca? Yes, definitely -- you use oradim directly.
</code>
http://docs.oracle.com/docs/cd/A87860_01/doc/win.817/a73008/toc.htm <code>
see chapter 6
Documentation conflicts with what you say
A reader, June 14, 2003 - 11:00 pm UTC
Hi tom,
You have quoted in your response above as
<quote>
I would go a step further -- the combination of:
o ORACLE_HOME plus
o ORACLE_SID plus
o HOSTNAME
identifies the instance (i can have two instances in different homes on a single
machine with the same sid).
</quote>
However when I went thru the docs i found this...
<quote>
If you attempt to mount two Oracle8i databases with the same database name, you receive the following error during the second mount:
ORA-01102: cannot mount database in EXCLUSIVE mode
If there are two or more Oracle8i databases on the same computer, but located in different Oracle homes, the following rules apply:
*Each database name must be unique
*Each SID must be unique
</quote>
which essentially states that on the same machine the SID "must" be unique. Can you please clarify??
June 15, 2003 - 9:40 am UTC
that is probably true on windoze with the registry. good point.
Having same SID for Primary & Standby
Mohammed Osman, September 09, 2003 - 4:08 am UTC
Dear Tom,
Greetings
Thanks for answering my previous questions. I have quite a similar question concerning SID & Services. Would appreciate your advise.
I am in process of implementing Standby Database (Oracle Release 8.1.7.0.0) - Mangement is considering for Oracle 9i but it will take quite some time.
Primary Database and Standby Database will be on a Different Node with same Directory Structure.
The Database Name, Instance Name of Primary & Standby will be same but the Service Name in Standby will be different from Primary.
Init.ora (Primary)
db_name = PROD
instance_name = PROD
service_names = PROD
Init.ora (Standby)
db_name = PROD
instance_name = PROD
service_names = STBY
Is it possible to have these settings for Standby DB ?
Oradim -NEW -SID PROD -SRVC OracleServiceSTBY -INTPWD <Password> -MAXUSERS 5
1) Can I have the same SID for Primary and Standby ? When Primary fails I can activate the Standby and change the IP Address of the Standby DB Server, thereby users will use the same Tnsnames alias to connect the Database.
2) How do I get the data from Primary DB Redo Logs when it is not archived yet ? In 9i LGWR can write directly to the Standby Database, hence in 8i I have to copy the Redo Logs from Primary Database and apply the Logs after activating the Standby Database IF Primary Server can be accessable.
With best regards
Mohammed Osman
September 09, 2003 - 11:38 am UTC
1) same sid is fine. but you don't need a sid to connect remotely -- it is all done by service these days (preferably anyway)
2) that is something you sort of have to do yourself. if the primary fails, you have to either
a) make the redo available to standby using any technique you want
b) lose the data in the redo logs
different SID on the same machine
dash, October 13, 2003 - 9:27 pm UTC
OS : windows2000 Oracle : 8.1.7
Hi,
I have two database(sid) on same computer
(ex: DB1, DB2)
I tried to change initDB1.ora and initDB2.ora
so I tried shutdown DBx. It's good.
but startup option is fail.
ORACLE_HOME is same PATH(D:\oracle\ora81)
ORACLE_SID is different(DB1, DB2)
DB_NAME is different too.(DB1, DB2)
I tried to set ENV ORACLE_SID=DB1(or ORACLE_SID=DB2)
on windows prompt and
$> sqlplus internal@db1(or @db2)
sqlplus> startup mount
ora-01102: cannot mount database in EXCLUSIVE mode
My system is not OPS.
Windows registry :
ORACLE_DB1_PFILE = D:\oracle\admin\DB1\pfile\init.ora
ORACLE_DB2_PFILE = D:\oracle\admin\DB2\pfile\init.ora
ORACLE_HOME = D:\oracle\ora81
ORACLE_SID = DB1(only)
How can i startup DB1, DB2.
thanks
October 14, 2003 - 6:29 am UTC
[tkyte@localhost tkyte]$ oerr ora 1102
01102, 00000, "cannot mount database in EXCLUSIVE mode"
// *Cause: Some other instance has the database mounted exclusive or shared.
// *Action: Shutdown other instance or mount in a compatible mode.
[tkyte@localhost tkyte]$
your database is already running.
ok for standby & primary to have same sid and service_name ?
Susan, October 27, 2003 - 11:13 am UTC
We have a standby on a different server (host) than the primary. TNSNAMES for standby has the same sid and service_name for standby and primary. We also use the same internal passwords. This has been working fine, but, now we're setting up a recovery catalog database on the same machine as the standby. I'm wondering how RMAN (and Net8 for that matter) knows to resolve @larg to the primary and not the standby. Thanks.
LARG_PRIM.NERI.ORG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = ORACLE))
)
(CONNECT_DATA =
(SERVICE_NAME = LARG)
)
)
LARG.NERI.ORG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = ORACLE))
)
(CONNECT_DATA =
(SERVICE_NAME = LARG)
)
)
LARG_STBY.NERI.ORG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(HOST = TROY))
)
(CONNECT_DATA =
(SERVICE_NAME = LARG)
)
)
October 27, 2003 - 11:16 am UTC
the @larg is going to be larg.neri.org and it'll use that connect string after that says "connect to the listener on the host ORACLE, port 1521"..
ORACLE_HOME and HOSTNAME
A reader, October 31, 2003 - 9:14 am UTC
Tom,
On the Title 'Clear and "clean" answer!', you mentioned that
<quote>
you'll see them as the same. I would go a step further -- the combination of:
o ORACLE_HOME plus
o ORACLE_SID plus
o HOSTNAME
identifies the instance (i can have two instances in different homes on a single
machine with the same sid).
</quote>
Question
What is the purpose of Oracle home and how do I find out what oracle_home is?
What is the purpose of hostname and how do I find out what hostname is?
Thank you for your time.
November 01, 2003 - 11:44 am UTC
oracle home is where Oracle lives -- its the root directory for the install. It'll have a bin directory (software) a lib directory, and so on. Its "oracle's home" -- literaly
hostname is what your SA named the machine. on unix, you simply type "hostname".
oracle home and host name
A reader, November 02, 2003 - 11:37 am UTC
Is there a SQL etc to find out oracle_home and hostname?
Thank you
November 02, 2003 - 12:29 pm UTC
select host_name from v$instance;
the oracle_home really isn't "in the database" anywhere. It is where you installed the software. It might be inferred from some init.ora parameters, but not necessarily.
same sid on the
Reader, December 06, 2003 - 2:02 pm UTC
Following is an extract from above discussions in this thread.
*Each database name must be unique
*Each SID must be unique
which essentially states that on the same machine the SID "must" be unique. Can
you please clarify??
Followup:
that is probably true on windoze with the registry. good point.
Tom, can we have two databases with the same name on Unix platform if the two databases are in different ORACLE_HOME? Thanks.
December 06, 2003 - 2:14 pm UTC
each sid in each oracle_home must be unique actually. but it would be a really bad practice to have the same sids in different oracle homes.
On windows, it is probably 100% true that the sid must be unique on a machine.
On unix -- everything is possible. The two oracle homes are totally unaware of eachothers existence, totally separate.
Windows, with its copy protection device -- opps meant "registry", makes everything "shared"
don't have any windows installed, cannot verify, but would not doubt it.
Duplicate SIDs on Windows
Mark A. Williams, December 06, 2003 - 2:56 pm UTC
Oracle is implemented as a service on Windows... meaning that a sid is "connected" to a service. If you have a SID called 'TEST' then you will have a service called OracleServiceTEST. For that reason you can not have identical SIDs on a Windows server - it is not possible to have 2 services with the same name. If you try to create a SID that is a duplicate (even if you use oradim from a different Oracle Home) you will receive:
DIM-00020: A service for this SID is already created. Please enter a different SID name. No action has been taken
O/S-Error: (OS 1073) The specified service already exists.
- Mark
Multiple instances, single ORACLE_HOME, different oracle startup user
Tommy, February 11, 2004 - 10:28 am UTC
Hi Tom,
I have an Oracle installation with one ORACLE_HOME and multiple Oracle databases (different ORACLE_SID). Is it possible to create different Unix user to startup different ORACLE instances?
February 11, 2004 - 11:22 am UTC
the "dba" group is burned into the binary -- however
using SYSDBA (and not giving people access to the "oracle software account") you can let people
connect u/p as sysdba
without being in the "dba" group to manage the database.
so - no you cannot do what you ask in the way you ask
however, you can accomplish your goal (of having different people being able to start/stop individual databases)
Difference Between SID and Service Name
Dhrubo, March 26, 2004 - 7:29 am UTC
Tom a silly question ... so i wanted to know whats the difference between SID and service name?
March 26, 2004 - 9:29 am UTC
a service name is possibly a collection of instances. many instances can register as the same service. we use it for load balancing in RAC.
a sid+oracle_home on a HOST identifies an instance on that host.
the sid is used to find default configuration files on that host.
basic how to
Fernando Sanchez, March 31, 2004 - 3:14 pm UTC
Hello Tom.
I'm working with version 8.1.7 and I wonder how I can connect my windows sqlplus to an instance running on a remote solaris machine whose ip address I know but whose ORACLE_SID is equal to another ORACLE_SID running on a different machine that I also need to connect to. I have written two entries in my windows tnsnames.ora whose only difference is the host parameter. But when trying to start a new sqplus session, how can I choose the machine?
Thanks in advance.
March 31, 2004 - 3:21 pm UTC
you would have two tnsnames entries, one each for each instance you want to connect to.
then
connect scott/tiger@string1
vs
connect scott/tiger@string2
Service Name and SID
YenYang, July 01, 2004 - 12:14 am UTC
While connecting to database ABC giving service_name = ABC in tnsnames it worked. But while connecting to database PQR I need to give SID. Why it is so? For both the DBs I was given the host ip address and service name by the client.
Can you tell to find out whether SERVICE_NAME will work or SID ? Are both interchangeable?
Both the dbs are Oracle 9.2 on windows
Here are the details Im sending you,
For ABC,
global_name = abc.us.oracle.com
db_domain=null
global_names=false
instance_name =ABC
For PQR,
global_name = pqr.world
db_domain=world
global_names=false
instance_name =pqr
In sqlnet.ora I didnt set any of the values
NAMES.DEFAULT_DOMAIN
SQLNET.AUTHENTICATION_SERVICES
NAMES.DIRECTORY_PATH= (TNSNAMES)
July 01, 2004 - 10:27 am UTC
you need to connect to the listener using lsnrctl and do
$ lsnrctl services
to see what services are actually registered and available with that listener. if you had to use a sid, that means that service name was NOT registered with that listener (eg: the configuration on the server is botched, nothing on the client machine)
Changing ORACLE_HOME
Varun, July 15, 2004 - 2:08 pm UTC
I have by mistake installed oracle 9i in the home of oracle. /home/oracle. And also created db under proper folders for the .dbf files. Is it now possible to move the oracle installation to the usual /u01/app/... without in any way effecting my db? How do I go about it?
Thanks.
July 15, 2004 - 6:11 pm UTC
well, you would have to track down everywhere a path might be referenced -- your listener.ora, maybe tnsnames.ora's, where ever and fix them up. (init.ora might have some as well, there could be others depending on the features you use)
and then you might have to relink on some OS's (to burn the right -L oracle_home into the binaries).
but it is ultimately doable on unix
Varun, July 16, 2004 - 8:24 am UTC
"and then you might have to relink on some OS's (to burn the right -L oracle_home
into the binaries)."
What is meant by burn the right -L into the binaris? Kindly explain also the relink process.
In Unix, is there any thing similar to Windows registry where the ORACLE_HOME and others.. are registered.
Will Unix allow me to re-install Oracle on a separate path without touching the previous installation.
Thanks a lot for your time and effort.
July 16, 2004 - 11:20 am UTC
your dba that installed Oracle knows how to relink -- it is part of the installation. -L is an option to ld to burn a library path into a binary so it knows where to find them at runtime.
unix fortunately does not have that copy protection scheme called "the registry"
Yes, Unix is very flexible in that you can install into another path without touching the existing one.
Oracle Developer
Dawar, July 21, 2004 - 4:18 pm UTC
Tom,
Version: Oracle Database 7.3.4.
when I run following statement
select instance_name from v$instance;
I got values as:
Key Value
------------------------------- -----------
RESTICTED MODE 0
SHUTDOWN PENDING 0
STARTUP TIME - JULIAN 2453202
STARTUP TIME - SECONDS 19803
desc v$instance
Name Null? Type
------------------- ---- ----------
KEY VARCHAR2(64)
VALUE NUMBER
But I wanted to see instance names.
Any help will be appreciated.
cheers,
Dwaar
July 21, 2004 - 7:07 pm UTC
it wasn't avaible from within the database way back in the day.
I do not recall a method to get it from the database -- only the OS.
Connecting to a remote Oracle server from a client using sysdba priviledges
Rohan Pradhan, July 30, 2004 - 1:21 am UTC
Hi Tom,
I have been trying to connect to a remote Oracle server from a client as sysdba. I am able to connect to it as scott/tiger...but when I try to connect as sysdba, I get a TNS error message. Is it possible to connect from a client to a remote server using the sysdba priviledge? I asked one of the DBA's in the office and he said it was not possible.I think it is possible to connect as sysdba but I really don't know how to go about with it.I tried a lot but did not succeed.If it is possible to connect as sysdba from client to remote server,could you help...?
Regards
Rohan
July 30, 2004 - 7:51 am UTC
yes it is, the dba would have to set it up and permit it.
they need to use orapwd, setup a password file and have the listener running and have a tns entry that uses a dedicated server setup for you.
DB_UNIQUE_NAME
Jim, March 21, 2005 - 1:33 pm UTC
I understand the difference between db_name and instance_name. Now, why there is another parameter dn_unique_name? Is it there to confuse oracle users? What is its significance and how doe db_unique_name differs from db_name and instance_name? Thanks.
March 22, 2005 - 10:58 am UTC
ops$tkyte@ORA9IR2> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string ora9ir2
global_names boolean FALSE
instance_name string ora9ir2
lock_name_space string
log_file_name_convert string
oracle_trace_collection_name string
oracle_trace_facility_name string oracled
plsql_native_make_file_name string
service_names string ora9ir2
what parameter?
DB_UNIQUE_NAME
Jim, March 22, 2005 - 3:45 pm UTC
sorry Tom. I should have mentioned it. It is a new parameter in 10g...
March 22, 2005 - 6:11 pm UTC
seems clear from the documentation what it is? did you see it in the Reference guide -- with the advent of data guard and many duplication db_names, this is for a globally unique database name within an enterprise.
Most people won't need it. See the data guard guide as well -- the "whats new" chapter explains the "why"
Multiple Oracle SID + Instance on same box
Saeed Dashti, May 24, 2005 - 6:27 am UTC
Hi Tom,
Greeting
My Versions: Oracle Database 9.2.0 & 10.1.0
Following is an extract from above discussions in this thread:
Followup:
each sid in each oracle_home must be unique actually. but it would be a really bad practice to have the same sids in different oracle homes.
On windows, it is probably 100% true that the sid must be unique on a machine.
On UNIX -- everything is possible. The two oracle homes are totally unaware of eachothers existence, totally separate.
Windows, with its copy protection device -- opps meant "registry", makes everything "shared"
don't have any windows installed, cannot verify, but would not doubt it.
and also:
Followup:
your dba that installed Oracle knows how to relink -- it is part of the installation. -L is an option to ld to burn a library path into a binary so it knows where to find them at runtime.
Confusion still stays where it started:
Can we have same name for those simultaneously running databases, does Oracle really support running multiple databases with same name on a single machine?
means.. Using same ORACLE_SID, INSTANCE_NAME (Which is always the oracle_id) and DB_NAME BUT different ORACLE_HOME? If yes then does this implies on only UNIX or UNIX & windows both?
If yes then how can we install the oracle software multiple times and create instances and database? How we can link the instances with databases? What should be consider while installation? especially while giving DB_NAME, ORACLE_SID and INSTANCE_NAME etc.
Any link to documentation would be highly appreciated coz I tried searching for it but cant get it.
In case we want move the files in unix envr. how can we relink the binaries? Can u provide a link or something to the documentation which clear this concept?
I'll be thankful for your advise and any help for these issues will be highly appreciated.
Regards,
Saeed Dashti
May 24, 2005 - 8:08 am UTC
Does Oracle support instances with the same SID on the same machine?
YES - on unix.
NO - on windows, the services are named after the sid. OracleService%ORACLE_SID%
but -- it would be a really bad idea to have two of them with the same sid.
So, that is all I have to say on it, don't do it, it would not be a good idea.
Use DBCA and OUI (database configuration assistant and oracle universal installer) to manage the software, it will relink anything that is necessary for you.
Metalink disagrees about what you said :(
Nabeel Khan, May 25, 2005 - 6:42 am UTC
Dear Tom,
Greetings,
I had open up a tar and they have totally disagreed with what you said. following is my question and thier answer,
Im really very confused now about this matter....
QUESTION
=========
Can we have same name for those simultaneously running databases, does Oracle really support running multiple
databases with same name on a single machine? means.. Using same ORACLE_SID, INSTANCE_NAME (Which is always the oracle_id) and DB_NAME BUT different ORACLE_HOME?
If yes then does this impiles on only unix or unix & windows both? If yes then how can we install the oracle software multiple times and create instances and database? How we can link the instances with databases?
What should be consider while installation? especially while giving DB_NAME, ORACLE_SID and INSTANCE_NAME etc.
eos (end of section)
ANSWER
=======
No.
On any platform(UNIX/Windows/VMS), DB_NAME and SID_NAME must be unique in the system.
Oracle doesn't support multiple databases running with same name on any platform.
Oracle uses these uinque names to derive names for oracle background process's in the system.
It is not possible to have multiple background process's running with same process name in any system.
================================
Please help me to clear and sort this out, i will be very greatful for your kind favor for hlepin me clearin this concept.
I can provide you with the detail of TAR if required.
May 25, 2005 - 8:07 am UTC
bottom line here - no matter which is true -- you have been told over and over "really utterly bad idea, don't go there, so it doesn't really matter which is true"
However.
easy to prove "not correct" on the "sid must be unique". the ORACLE_HOME+ORACLE_SID must be unique on unix (it is the key to the shared memory segment), but that is it.
facts:
o 9ir1 in /home/ora9ir1
o 9ir2 in /home/ora9ir2
Had an existing database in both. Oracle_sid is ora9ir2 in the oracle 9ir2 install. I want (for whatever reason) two database instances up and running with that sid.
So, I log into the ora9ir1 account.
I export ORACLE_SID=ora9ir2.
I then:
[ora9ir1@xtkyte-pc dbs]$ mv spfileora9ir1.ora spfileora9ir2.ora
[ora9ir1@xtkyte-pc dbs]$ mv orapwora9ir1 orapwora9ir2
[ora9ir1@xtkyte-pc ora9ir1]$ cd $ORACLE_HOME/admin
[ora9ir1@xtkyte-pc admin]$ ln -s ora9ir1/ ora9ir2
(and that is it -- really, nothing more)
Startup both 9ir1 and 9ir2 and lets see what we have:
[tkyte@xtkyte-pc admin]$ su -
Password:
[root@xtkyte-pc root]# ps -auxwwee | grep pmon_ora9ir2
ora9ir1 31262 0.0 0.3 201056 7400 ? S 07:57 0:00 ora_pmon_ora9ir2 HOSTNAME=xtkyte-pc.us.oracle.com SHELL=/bin/bash TERM=xterm HISTSIZE=1000 LD_PRELOAD=/home/ora9ir1/libcwait.so USER=ora9ir1 LD_LIBRARY_PATH=/home/ora9ir1/lib ORACLE_SID=ora9ir2 PATH=/home/tkyte/bin:/home/ora9ir1/bin:/home/tkyte/bin:/home/ora9ir1/bin:/bin:/usr/bin:/usr/ccs/bin:/usr/ucb::/usr/X11R6/bin:/home/ora9ir1/bin MAIL=/var/spool/mail/ora9ir1 _=/usr/bin/rlwrap PWD=/home/ora9ir1 INPUTRC=/etc/inputrc LANG=en_US.UTF-8 SQLPATH=/home/tkyte/src/sqlstuff HOME=/home/ora9ir1 SHLVL=2 LOGNAME=ora9ir1 ORACLE_PROC_MAKEFILE=/home/ora9ir1/precomp/demo/proc/demo_proc.mk ORACLE_HOME=/home/ora9ir1 ORA_NET2_DESC=7,10
ora9ir2 31312 0.0 0.4 402660 8232 ? S 07:57 0:00 ora_pmon_ora9ir2 HOSTNAME=xtkyte-pc.us.oracle.com SHELL=/bin/bash TERM=xterm HISTSIZE=1000 LD_PRELOAD=/home/ora9ir2/libcwait.so USER=ora9ir2 LD_LIBRARY_PATH=/home/ora9ir2/lib ORACLE_SID=ora9ir2 PATH=/home/tkyte/bin:/home/ora9ir2/bin:/bin:/usr/bin:/usr/ccs/bin:/usr/ucb::/usr/X11R6/bin:/usr/java/j2sdk1.4.2_04/bin/:/home/ora9ir2/bin MAIL=/var/spool/mail/ora9ir2 _=/home/ora9ir2/bin/sqlplus PWD=/home/ora9ir2 INPUTRC=/etc/inputrc LANG=en_US.UTF-8 SQLPATH=/home/tkyte/src/sqlstuff HOME=/home/ora9ir2 SHLVL=2 LD_ASSUME_KERNEL=2.4.19 LOGNAME=ora9ir2 ORACLE_PROC_MAKEFILE=/home/ora9ir2/precomp/demo/proc/demo_proc.mk ORACLE_HOME=/home/ora9ir2 ORA_NET2_DESC=7,10
[root@xtkyte-pc root]#
Now, that aside, you do NOT want to install the software multiple times A version should be INSTALLED EXACTLY ONCE on a machine. A single software install can support multiple instances and databases -- each with their own SID (hey, they could have the same db name if you wanted but that was be a really bad idea too -- the goal is to organize things, not to make things more confusing)
Why do you believe you want to install multiple times???
Cool
Kevin John, May 26, 2005 - 12:50 am UTC
Hi Tom,
Can I ask whats ur OS? I mean what OS do u usually use? or Installed on your PC?
Regards,
Kevin
May 26, 2005 - 8:43 am UTC
Linux -- Red Hat AS 3.0
A reader, August 05, 2005 - 10:54 am UTC
test
August 05, 2005 - 1:54 pm UTC
it worked.
A reader, August 05, 2005 - 11:25 am UTC
Tom,
Your site is very helpful to me. Thank you for sparing your time to response for each our questions.
I'm new to oracle so don't laugh if I asked stupid question :-)
As you have said, INSTANCE = ORACLE_HOME + ORACLE_SID + HOST. And instance can start only ONE database at the time.
Unix environment,
When you installed oracle product, you installed oracle_home
(ORACLE_HOME=/u01/apps/oracle/OraHome). Then you created database (datafiles are located /u2/oradata/admin/1stnewdb)
ORACLE_SID=1stnewdb
HOST=myhost
My question is, if I want to create 2nd database, same oracle_home and host
ORACLE_HOME=/u01/apps/oracle/OraHome
ORACLE_SID=2ndnewdb
HOST=myhost
Can I have my datafile in /u02/oradata/admin/2ndnewdb?? or should I have it in seperate dir /u03/oradata/admin/2ndnewdb ??
Does it mean 1 oracle_home = 1 instance?? If it does, how can I make both databases (1stnewdb and 2ndnewdb) be available at the same time?? what environment?? or what steps should I do?
Thanks for your response
August 05, 2005 - 1:59 pm UTC
you can have them in the same mount point, it is all about "even IO" though, so, beware that while you can do it, depending on your physical disk layout, it might be "not optimal"
1 oracle home may support as many instances as you like.
But, I always say the only right number of instances running on a server is "1"
you just need to set your oracle_sid before starting each one. set the oracle_sid to "newdb2", run dbca - create it and you'll have two of them.
Parallel Server/RAC
Reader, September 06, 2005 - 3:14 pm UTC
Hi Tom,
Just wondering what is the difference between parallel server and real application clusters?
Thanks
September 06, 2005 - 9:05 pm UTC
The major difference - the 'biggest thing' -- is a technology refered to as cache fusion, the change in the way blocks are shared between the nodes.
In OPS, a "ping" of a block required a write to disk on node 1 and a read on node 2.
In RAC, that "ping" is done by sending the block over an interconnect, a high speed private network for keeping the caches in step with eachother.
See the RAC concepts guide, useful for getting an overview of "whats new"
how to know database name
Alay, September 16, 2005 - 10:39 am UTC
Hi Tom,
I have login into the remot database using my username and password. I don't have any data dictionary access. I don't access v$ tables. How can i know that at which database i had connected(means name of the database and instance)?
September 16, 2005 - 1:48 pm UTC
... I don't have any data dictionary access. ...
then you are not in a supported oracle database ;) the dictionary is publically available, as is global_name;
so are many packages
ops$tkyte@ORA10G> drop user a cascade;
User dropped.
ops$tkyte@ORA10G> create user a identified by a;
User created.
ops$tkyte@ORA10G> grant create session to a;
Grant succeeded.
ops$tkyte@ORA10G> @connect a/a
ops$tkyte@ORA10G> set termout off
a@ORA10G> @login
a@ORA10G> set termout off
a@ORA10G>
a@ORA10G> set termout on
a@ORA10G> select * from global_name;
GLOBAL_NAME
-------------------------------------------------------------------------------
ORA10G
a@ORA10G>
a@ORA10G> variable x number
a@ORA10G> variable dbname varchar2(30);
a@ORA10G> variable instance varchar2(30);
a@ORA10G> begin
2 :x:=dbms_utility.GET_PARAMETER_VALUE( 'db_name', :x, :dbname );
3 :x:=dbms_utility.GET_PARAMETER_VALUE( 'instance_name', :x, :instance );
4 end;
5 /
PL/SQL procedure successfully completed.
a@ORA10G> print dbname
DBNAME
--------------------------------
ora10g
a@ORA10G> print instance
INSTANCE
--------------------------------
ora10g
ALT, September 17, 2005 - 10:48 am UTC
HOW I CAN KNOW HOW MANY INSTANCES ARE RUNNING IN MY DATEABASE?
September 17, 2005 - 11:36 am UTC
select * from gv$instance;
but that probably isn't what you meant.
A database is a set of files.
An instance is a collection of processes and memory.
A database may be mounted and opened by one or more instances (RAC)
An instance may only ever mount and open a single database in its entire life, you have to kill the instance (shutdown the instance) and create a new instance to open another database.
do you really mean "how many instances am I running on my server"?
on unix I might use ps -aef | grep pmon
On windows, use the net services command or the gui to view the running Oracle instances.
difference between database and instance
Eileen, October 06, 2005 - 8:29 pm UTC
Your answer is very helpful, however, it's even better if you give us the result of "ps -aef | grep mon" at your server as an example to help us regconize how many instance since after excuting ps command I saw many listed but can't tell whether all of them are instances.
October 06, 2005 - 8:58 pm UTC
if you have many pmons - you have many instances?
How to findout all the processes associated with a particular instance?
Reji, February 04, 2006 - 9:49 am UTC
Tom:
We are writing an application which will determine the cpu and memory utilization of each and every application running on a server. How can we get the same information about oracle apps, oracle instances on a server?
Thanks
Reji
ORACLE_SID=DB_NAME or ORACLE_SID=DB_UNIQUE_NAME
A reader, May 14, 2008 - 10:50 pm UTC
ORACLE_SID (env setting) = DB_NAME (in the parameter file) or
ORACLE_SID (env setting) = DB_UNIQUE_NAME (in the parameter file)
example: (PRIMARY DB)
DB_NAME = oradg
DB_UNIQUE_NAME = oradg_node1
---------------
export ORACLE_SID = oradg
export ORACLE_SID = oradg_node1
I able to startup using either one of above setting
since we have db_unique_name should we use db_unique_name?
btw this is 11g data guard
May 16, 2008 - 12:27 pm UTC
the oracle_sid is used to find parameter files and create the SGA.
you must have the same parameter files copied with different names.
the sid has nothing really to do with the database name.
A reader, May 14, 2008 - 10:54 pm UTC
my question is should we use ORACLE_SID = DB_UNIQUE_NAME instead of ORACLE_SID = DB_NAME
May 16, 2008 - 12:27 pm UTC
only you can answer this, you can use oracle_sid = fred if you like
Thank for the answer
A reader, May 21, 2008 - 2:51 am UTC
thank you Sir.
attempt to re-link Oracle Software fails
Malay Singh, June 10, 2008 - 12:58 am UTC
Hi I am recieving "An attempt to re-link the Oracle software has failed!" whenever server reboot and restarts.My database is in AIX platform.Please give the solution for this.My database version is 10.2.0.3 and has CPU Jan 2008.What should we do to remove this error?
June 10, 2008 - 7:11 am UTC
please utilize support, the database doesn't try to relink itself during a reboot - not sure what's up with that.
Local connection to a instance (only SID needed ?) - different on window/*nix
Allan, July 27, 2008 - 3:23 am UTC
hi dear tom,
i am reading the earlier thread of this post whereby you mentioned
host+oracle_home+oracle_sid = instance
i also know that instance is just memory and processes
now on windows XP
i have 2 version (2 oracle_homes) installed.
10g version (oracle/product/10.2.0/db_1) sid = orcl
9i version (oracle/product/9.2.0/db_2) sid = shijie
On my environment variables
I have NO oracle_home variables SET.
Neither i have two_Task variable SET
The only environment variable set is PATH and/BUT i have REMOVE the both%oracle_home%/bin value from it.
AND
I have one ORACLE_SID environment variable set to
ORACLE_SID = shijie
In the window services,
i have stop the service for ORCL (meaning now the instance isnt available)
The only service left is shijie (9i)
In window services again
I have stop all LISTENER service. (i just want to make sure its connecting locally (using the IPC/beqeauth) method.
NOW.. here is the experiment..
Since i have no oracle_home set in the environment variable.
I believe the value will be retrieved from the registry itself.
Where it would find in the registry, will be dependant on the oracle.key file in the bin folder whereby i invoke my sqlplus application.
-----------------------------------------------------------
Inside command prompt..
C:\oracle\product\10.2.0\db_1\BIN>echo %ORACLE_HOME%
%ORACLE_HOME% -- no home set
C:\oracle\product\10.2.0\db_1\BIN>echo %ORACLE_SID%
shijie --- 9i SID
C:\oracle\product\10.2.0\db_1\BIN>echo %PATH%
C:\Program Files\Oracle\jre\1.3.1\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\
System32\Wbem;C:\Program Files\ATI Technologies\ATI Control Panel;C:\Program Fil
es\ATI Technologies\ATI.ACE\;C:\Program Files\QuickTime\QTSystem\
-- no path pointing to %ORACLE_HOME%/bin
then......
C:\oracle\product\10.2.0\db_1\BIN>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 27 15:03:45 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: scott/tiger
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>
------------------------------------------------------------
I am connected.
Using a host(same host since locally) + 10g HOME path + 9i SID, i can connect to a 9i instance ..
but how is uniquely identifying the 9i instance possible ?
it was setup using a 9i oracle_home (c:\oracle\product\9.2.0\db_2) + 9i oracle_sid (shijie)
but now with a 10g oracle_home and a 9i SID, i can actually identify it and connect to it.
why is it so ?
need your advice
tom
p.s thanks for all the dedication in keeping this forum active until now.
July 29, 2008 - 10:34 am UTC
windows uses the registry as well, you'd have to look in there to see what the default is. the environment overrides the registry, but the registry is the 'backup'
follow up on Local connection to a instance (only SID needed ?) - different on window/*nix
Allan, July 29, 2008 - 1:05 pm UTC
hi dear tom,
thanks for the reply.
i have check the registry under
hlm\software\oracle\all_homes
there is a
DEFAULT_HOME entry with the value of OraDB9i_home1
which is the name for my oracle_home path
DEFAULT_HOME - OraDB9i_home1
HOME_COUNTER - 1 ( i got 2 home though ) not sure why
LAST_HOME - 0
can i ask if
q1) is it that if
i open a 10g sqlplus and
if its oracle_home is dervied from the 10g/bin oracle.key file and
if with the 10g home and 9i key, instance cannot be located
but NO error will be prompted and the search will be directed/continued to the registry where the default_home value will be look up..
q2) if the above is true..
now i have set the
DEFAULT_HOME to OraDb10g_home1 ( no more OraDb9i_home1),
i reboot my system and setup the same environment once more.
set ORACLE_SID to shijie (9i).
and i am still able to connect to the instance. with a wrong oracle_home and a right oracle_sid.
is it because
some other values need to be set as well
like..
HOME_COUNTER
LAST_HOME
thanks alot tom.
look forward to your always enriching reply.
August 01, 2008 - 9:42 am UTC
q1) as stated above, registry is the default if the environment does not override, it'll look in the environment... then it'll look in the registry.
2) did not follow that.
follow up on Local connection to a instance (only SID needed ?) - different on window/*nix
A reader, August 03, 2008 - 3:48 am UTC
hi tom,
the problem now is.
oracle_home + oracle_sid = instance
1)I have a 9i instance running.
2) I have only 1 environment variable set
that is oracle_sid (which point ot the 9i instance)
3) i do not have any path nor oracle_home environment variable set.
Like what you say, if no environment variable set, registry will be look at for its default value.
Therefore i went to the registry to check its value
under HLM/SOFTWARE/ORACLE/ALL_HOME/
the defaulted home is set to OraDb10g_home1
q1) if my oracle_home default value is OraDb10g_home1(
shouldn't i be not able to connect ?
as my 9i instance is build with a 9i home.
thanks.
August 03, 2008 - 2:14 pm UTC
shouldn't be able to connect to what?
I don't know what you mean here.
You can use 10g software/clients to connect to 9i.
follow up on Local connection to a instance (only SID needed ?) - different on window/*nix
A reader, August 03, 2008 - 3:50 am UTC
sorry to add on..
i am executing sqlplusw.exe from the 10g/bin.
thanks.
August 03, 2008 - 2:14 pm UTC
so, you can connect to multiple versions of Oracle using a given client.
follow up on Local connection to a instance (only SID needed ?) - different on window/*nix
A reader, August 06, 2008 - 3:16 pm UTC
sorry tom if i have not been precise..
as you said.
oracle_sid + oracle_home + host > hash together = instance (memory)
now i am connecting to my own local db on my own machine.
i have 2 db on my system 10g and 9i.
i stop the 10g service, leaving the 9i on.
1)i got oracle_sid pointing to a 9i instance ( set in environment )
2)i got no oracle_home set in the environment variable.
3) as you said, since no envir var is set for oracle_home it will be taken from the default oracle_home in registry.
4) the default oracle_home is set to ora10g_db1
5) i am able to connect to the 9i instance.
but why ?
the 9i is setup with a 9i oracle_home set at = ora9i_homeDB1
and oracle_sid = xxx
now i am using a 10g oracle_home = ora10g_db1 + oracle_sid = xxx
and i am still able to locate the instance and connect to it.
isnt it wrong?
August 06, 2008 - 3:36 pm UTC
hash( oracle_sid+oracle_home ) -> instance for a local connection (and actually, that generates the key for a shared memory segment on unix, the oracle_home isn't as necessary on windows - on windows the sid is sufficient, look at your service names - the sid is there)
OR
tnsconnect string for a remote connection
...
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 7 03:04:26 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: scott
Enter password:
....
i told you before
"so, you can connect to multiple versions of Oracle using a given client. "
follow up on Local connection to a instance (only SID needed ?) - different on window/*nix
A reader, August 06, 2008 - 3:25 pm UTC
sorry for the repost tom,
i am thinking maybe by showing you some exmaple it is clearer.
C:\Documents and Settings\szejie>set ORACLE_SID=shijie
C:\Documents and Settings\szejie>echo %ORACLE_SID%
shijie
* shijie (name for 9i instance)
C:\Documents and Settings\szejie>set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
C:\Documents and Settings\szejie>echo %ORACLE_HOME%
C:\oracle\product\10.2.0\db_1
now i set my ORACLE_HOME to point to the 10 home.
C:\oracle\product\10.2.0\db_1\BIN>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 7 03:04:26 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: scott
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
-----------------------------------------------------------
10g Home + 9i SID i am still able to locate the 9i instance.
why so ?
follow up on Local connection to a instance (only SID needed ?) - different on window/*nix
A reader, August 28, 2008 - 10:28 pm UTC
tom,
sorry to disturb,
but are you still on my question ?
thanks
A reader, August 08, 2009 - 7:33 pm UTC
Hello Tom
1-)If I change the dbname, I always have to open with resetlogs. (Therefore data in redolog may be lost)
2-)Both dbid,dbname are stored in controlfile, datafile and redologs.
Are these correct?
August 11, 2009 - 1:26 pm UTC
it does not really make sense to say "data in redolog may be lost", the data isn't needed, isn't relevant, isn't useful anymore...
when you open resetlogs, you are creating a new database 'chain', you would start backing it up and doing whatever you want as if it were a new database (which it is)
You'll find the identifying information - dbid, dbname - all over the place...
Instance Name & DB Name
Rajeshwaran, Jeyabal, May 07, 2011 - 11:51 pm UTC
Tom:
I was reading about Oracle Database Instance from documentation
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/startup.htm#BABDBBAJ rajesh@ORA11GR2> select instance_name, instance_role
2 from v$instance;
INSTANCE_NAME INSTANCE_ROLE
---------------- ------------------
ora11gr2 PRIMARY_INSTANCE
Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> select name,db_unique_name
2 from v$database;
NAME DB_UNIQUE_NAME
------------------------------ ------------------------------
ORA11GR2 ORA11GR2
Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2>
Is it possible to have Instance name different from Database Name? if yes can you please show me?
May 09, 2011 - 7:15 am UTC
Absolutely it is. Think about real application clusters for example, there would be ONE database (the datafiles) and many instances - each with their own name.
Here is an example of having a different instance name, the instance name is just the oracle_sid, so we'll change it:
ops$ora11gr2%ORA11GR2> connect / as sysdba
Connected.
sys%ORA11GR2> select instance_name, instance_role from v$instance;
INSTANCE_NAME INSTANCE_ROLE
---------------- ------------------
ora11gr2 PRIMARY_INSTANCE
sys%ORA11GR2> select name, db_unique_name from v$database;
NAME DB_UNIQUE_NAME
------------------------------ ------------------------------
ORA11GR2 ora11gr2
sys%ORA11GR2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys%ORA11GR2>
sys%ORA11GR2> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[ora11gr2@localhost dbs]$ cp spfileora11gr2.ora spfileXXX.ora
[ora11gr2@localhost dbs]$ export ORACLE_SID=XXX
[ora11gr2@localhost dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon May 9 08:14:16 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1345376 bytes
Variable Size 163580064 bytes
Database Buffers 364904448 bytes
Redo Buffers 5832704 bytes
Database mounted.
Database opened.
idle> @test
idle> connect / as sysdba
Connected.
sys%ORA11GR2> select instance_name, instance_role from v$instance;
INSTANCE_NAME INSTANCE_ROLE
---------------- ------------------
XXX PRIMARY_INSTANCE
sys%ORA11GR2> select name, db_unique_name from v$database;
NAME DB_UNIQUE_NAME
------------------------------ ------------------------------
ORA11GR2 ora11gr2
Just copy the necessary config files, change the sid and start it up...
db name
A reader, May 09, 2011 - 11:08 am UTC
dbca
A reader, April 30, 2012 - 12:22 pm UTC
Tom:
I need to create a couple of databses/instances on a unix server running 11g standard edition.
Can you run dbca remotely or do you need x windows terminal running on both server and client?
Can the OEM web interfac create those databases too if I dont have x windows?
May 01, 2012 - 3:23 pm UTC
DBCA runs on the server - it does everything by setting environment variables - no network.
OEM can do a remote deploy assuming you have the agents running on the remote machine.
database
A reader, May 01, 2012 - 5:34 pm UTC
Tom:
I know it runs on the server and you can do remote connection to the server and run it.
the question is do you need x windows server running on client or not to see the GUI?
If not, i am thinking of using dbca to create a database on a local machine, then copying the SQL script generated at the end and running that at remote server (CREATE DATABASE command)
May 02, 2012 - 12:39 pm UTC
you would need some sort of remote ability - yes - if you want to do it interactively. You could VNC to the box for example (that is what I usually do).
You could run an X emulator too probably - it has been years and years since I've done that.
You can do that anyway you want - that is entirely up to you. do you need an X emulator? No. Could you use one? Yes.
You can use dbca to create a database in "silent" (non-interactive) mode as well
http://docs.oracle.com/cd/E11882_01/server.112/e25494/create002.htm#ADMIN12479
did you ever hear of vnc ?
Sokrates, May 02, 2012 - 2:37 am UTC
May 02, 2012 - 1:36 pm UTC
thanks
once I got vnc I stopped using those X terminal emulators, it has been years and years and years since I used them.
I love being able to start something on one computer - close the vnc session, drive somewhere else, and reconnect to the vnc session and have everything just be there :)
vnc
A reader, May 02, 2012 - 4:57 pm UTC
May 02, 2012 - 5:46 pm UTC
I just used the stock vnc that came with my linux distribution and the free vnc viewer.
db name
A reader, May 11, 2012 - 3:49 pm UTC
Tom:
Do you recommend using uppercase letters for database and instance name or not?
It looks awkward when you look at the UNIX directory path and the background process names if it is uppercase since everything else is lowercase.
can you change the db and instance name from uppercase to lowercase in linux and oracle?
May 12, 2012 - 12:41 am UTC
most instance names are in lower case - although that is not a requirement. Some people like upper case, some don't. It is a matter of taste.
changing the instance name is easy - just change the ORACLE_SID environment variable - rename or explicitly reference your initialization files and start it up. The instance name isn't really part of the database - it is a very transient thing.
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5004.htm#SQLRF53848 The database name is case insensitive and is stored in uppercase ASCII characters. If you specify the database name as a quoted identifier, then the quotation marks are silently ignored.
Instance vs DB
Saha, May 12, 2012 - 3:16 am UTC
Hello Tom,
I have a doubt in regards to your previous answer. I never know for a normal database even more than one instance is possible. If I have a database naming TEST (say) and an instance with the same name, can I create another instance for TEST Database ? I work in Linux environment mainly.
If this is possible, would you mind giving me an example plesae? This will help to know a new thing for me.
What I was thinking if I create a pfile with name initTEST2.ora and give some parameters with the control_files path and try to start the instance, is it eventually possible?
Another question,
If My database is already opened and I am using an instance and without shutting down that instance(Database in my concept) can I start another instance for that database?
I don't work in RAC.
-Thanks a lot .
May 12, 2012 - 4:43 am UTC
With RAC (real application clusters - known as parallel server prior to Oracle 9i), you have many instances opening and mounting the same database. That is the RAC architecture.
http://docs.oracle.com/cd/E11882_01/rac.112/e16795/toc.htm You would need to have an environment set up in a clustered fashion, that document discusses all of this.
If you do not work in RAC, you cannot do this.