SID, Instance, Database name??? 3

[oaklanders]

A SID, database instance in Oracle 9i is really the name of the database? If I have one SID (or database instance) on my Unix server called ABC then I can say I have one database called ABC on my Unix server?

Is it possible to have two databases on my Unix Server.
So in my case I would have one SID called ABC and if we had another SID called XYZ then I would have two databases (two database instances or two SIDs) on my Unix server?

 

[SantaMufasa]

Oak,

Yours are all excellent questions. I'll be glad to offer some insight into the definitions and distinctions for the terms SID, Instance, and Database insofar as they relate to the Oracle World.

First of all, Oracle uses the term, "Database", very differently from how other database vendors use the term.

An easy way to distinguish an Oracle database from an Oracle Instance is that an Oracle database is what you have left when someone turns off the power to the computer. Specifically, under power-off condition, you have files left that Oracle divides into three major groups:[ul][li]Control files, which record the physical file names of all other database files and high-level characteristics that define the database.[/li][li]Database data files, which provide the storage space for Oracle's tablespaces, which, in turn, hold tables, indexes, et cetera.[/li][li]On-line Redo Log files, which store a sequential list of all changes to the database. These redo log files are what we use when we must recover a physically or logically damaged database.[/li][/ul]An Oracle Instance [which always has a System IDentifier (SID)] exists only when the computer is running. The Instance is made up of memory structures and executing programs that read and change the contents of the database. The main memory structures of an Oracle instance is the System Global Area (SGA). The SGA is divided into three main sub-sections:[ul][li]Shared Pool. This memory structure contains the SQL Area in which Oracle stores both human-readable copies of each SQL statement and binary, executable interpretations of the SQL statements. This memory structure also houses the program/work areas for each connection to the Oracle database.[/li][li]Database Buffer Cache. This chunk of memory holds database data that we read from and update to the database data files. When one SELECTs data from a table, if it is not already in the database buffer cache, the server that is handling our SQL requests must go out to the database data files and read approriate blocks of data into open blocks in the database buffer cache that satisfy our SELECT. If we INSERT, UPDATE, or DELETE rows, those changes take place in database buffer cache blocks, then, at appropriate times, a program called the Database Write (DBWR) physically writes blocks from the database buffer cache back out to the database data files on disk.


Oracle also writes (sequentially) the Data Manipulation Language (DML) changes (that result from INSERT, UPDATE, and DELETE statements) to the next Instance memory structure, the Log Buffer.

[/li][li]Log Buffer. This memory structure is an area that Oracle sets aside to gather up changes as they occur against the database. At appropriate times, a program called the Log Writer (LGWR) copies all of the information that is in the Log Buffer out to the On-Line Redo Log files. This process writes the changes sequentially. The log-buffer entries exist mainly for "reconstructing" the database in the event of a power failure, a SHUTDOWN ABORT, or some sort of damage or disruption to a database data file.[/li][/ul]In addition to the memory structures (SGA) of an Oracle Instance, there are the background processes that do the actual work of the database. There are many categories of background processes. Oracle adds more background processes as Oracle versions become more sophisticated. Among the background processes that exist in Oracle instances (at minimum) all the way back to Oracle Version 7 are (including those I have already mentioned):[ul][li]Database Writer (DBWR)[/li][li]Log Writer (LGWR)[/li][li]Archiver (ARCH)[/li][li]Checkpoint (CKPT)[/li][li]System Monitor (SMON)[/li][li]Process Monitor (PMON)[/li][/ul]So, the memory structures and background processes compose an Oracle Instance.



Now, the name of an Oracle Instance and the name of a/the database that the instance manages can be two entirely different names. But since the names can be the same for those two components, most organizations name the Instance and the Database by the same name.

Persons who are authorized to connect to, and manipulate, databases connect to an Oracle Instance with a Oracle User Name, which a Database Administrator creates using the "CREATE USER..." SQL command. Once an Oracle User exists, the User can create (and thus own) tables, indexes, and many other types of database objects. Other names by which you may hear reference to an Oracle USER are: "SCHEMA" and "LOGIN".

All of the Oracle USERs and the objects that they own (e.g., tables, indexes, et cetera) that are managed by a single Oracle Instance compose one Oracle database. (Other database servers, e.g., SQL Server, MySQL, EnterpriseDB, DB2, Informix, et cetera, refer to each schema as a separate database. So each installation would contain, in their terminology, many databases. This is an important terminological distinction when speaking with Oracle professionals.)

Now, to bring all of these concepts together: When an Oracle Database Administrator does a "startup" command, there are three "levels" (statuses) to which the administrator can bring the database:[ul][li]STARTUP NOMOUNT. This means that Oracle has created the memory structures and started the background processes of the instance only. The database itself is not yet accessible.[/li][li]STARTUP MOUNT. All of the work of STARTUP NOMOUNT has occurred, with the addition that the database's CONTROL FILES have been opened, but the database is still not yet accessible.[/li][li]STARTUP OPEN. All of the work of the previous two steps has occurred, but now Oracle has read the contents of the CONTROL FILES and has opened the files to which the CONTROL FILES point. If Oracle has been able to successfully open all of the database data files and on-line redo log files that appear in the CONTROL FILES, then the database is now OPEN and ready to read from and update.[/li][/ul]It is absolutely reasonable to have multiple Oracle Instances and their associated databases on a single *nix or Windows environment. In fact, on the *nix boxes that I manage, there are usually from 1 to 5 Oracle Instances and Databases that reside on a single machine.


I hope that all of this is understandable. If you have follow-up questions, we welcome them.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[oaklanders]

Thanks for all the writing and time you put into this!

If I understand you right then I am working on a (turned on) Unix server that has one instance (ABC) running. Not sure if the (turned off) Database name is actually called ABC because I have only been told what the instance (SID) is called. Also, if the DBA added another SID (called XYZ) to our Unix server we would have another database to connect to. I assume Oracle charges money for each instance.

Now in future if we had clusters (I think Oracle uses RAC as the name of cluster workstations?) then I would have multiple servers that could have the same database name but each server in the cluster would then have its own instance (SID)? So for a cluster environment I could have several SIDs (instance names) for same Database name?

 

[SantaMufasa]

Oak,

In the case you describe, above (RAC), I recommend that you name the Database differently from any of the Instances that interact with the Database. For example, you can name the Database "ABC", and the Instances that connect to (and interact with) the "ABC" database could have names like "DEF", "GHI", "JKL", and "XYZ".

The bottome line: you can have these configurations:

[ul][li]1 Instance controlling 1 Database (neither name has to be the same as the other), and[/li][li]Multiple Instances interacting/controlling 1 Database[/li][/ul]

But you cannot have 1 Instance controlling Multiple Databases.

I assume Oracle charges money for each instance.

Not necessarily. There are many different costing schemes that Oracle offers for its licensing. With the license-costing scheme that I worked under recently (development license), Oracle allowed us to create as many Instances and Databases on our server machine as we wanted, at no additional charge.


BTW, if you want to confirm the name of a database that an Instance is managing, you can run this query:

select name from v$database;

To see all of the other information that relates to the database specifically, you can query on any of the other 47 columns that appear in "v$database". To see those column names, you can issue the SQL*Plus command:

desc v$database

Let us know if you have additional questions/concerns.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[oaklanders]

Thanks for all your time and info!

It seems I dont have privileges to confirm the name of the database that my Instance ABC is managing when I try the SQL: select name from v$database;