Oracle database, instance, service name, SID

Reference: http://www.zhetao.com/content240

In real-world development applications, about Oracle databases, it is often heard that a database is created, a instance is created, and a instance is started.

Actually ask them what is the database, what is instance, most likely they give the answer is the database is instance,instance is the database Ah, no difference.

It can only be said that although they may have some experience with Oracle, the underlying concept is unclear. (I am in this state now)

first, what is a database , in fact, is very simple, the database is a medium for storing data. For example, the commonly used file is one, in oracle10g, the data storage has several kinds.

The first is the file format , which is to create a batch of files on your disk and then store the information in those files.

The second is the disk array form , what does this mean, this means that the database is not stored as a file, but instead of one or more disks formatted as a form of Oracle,

equals the entire disk is stored in an Oracle database and cannot be used for any other purpose. This has the advantage of high storage performance because no other file format is used, but the entire disk becomes

The most adaptable file system format for Oracle.

Of course there may be other forms, such as the Internet or something. However, our most commonly used is the file format , in the file format, the database refers to those data files,

A series of files, such as control files and redo files .

second, what is instance, in fact, refers to the operating system in a series of processes and the allocation of memory blocks for these processes . In Oracle, we can create a new Oracle instance,

This time, although there are processes and the SGA and so on a series of memory fast, but this time did not read the database files in. So just an example, in later, you can manually by command

or automatically load the database file into our database instance, this time the database can let us really start access operation. So, if the application of the database is to be implemented,

Database and database instance is indispensable, if only the database of those files, then, can only represent the data in this file, but we can not directly operate.

And if only the database instance, then we can operate, but also do not know what data to manipulate, operation generated data can not be saved and so on. So, when an oracle Instance

The database can only be used by us after the actual load of an Oracle databases.

One thing to note here is that when the Oracle instance is started, it can only load the database once, if you want to disconnect the database from instance and then re-hang it in a database instance,

So you first end the database instance process, then re-establish the instance process, and load another database. Otherwise you must throw away the ORA-16169 error,

Says the database has been opened. Because a database instance can only load and open a instance in its lifetime.


People who have just come into contact with Oracle are sure to be confused about the instances and databases, what does the instance represent? Why does this concept come into being?

Oracle instance = Memory used by process + process (SGA)

An instance is a temporary thing, and you can think of it as representing the state of the database at some point in time!

Database = Redo File + control file + data file + Temp file

The database is permanent and is a collection of files.

iii. Relationship between Oracle instances and databases

1. Temporary and Permanent
2. Instances can start the startup Nomount independently without a data file, usually with little meaning
3. An instance can only be loaded (ALTER DATABASE mount) and open (ALTER DATABASE open) in its lifetime
4. A database can be loaded and opened by many instances simultaneously (that is, RAC), the role of instances in the RAC environment can be fully reflected!

Four, the next face examples and database to do a detailed interpretation :

There are two words in the Oracle domain that are easy to confuse, which is "instance" (instance) and "database". As Oracle terminology, these two words are defined as follows:

database: A collection of physical operating system files or disks. When using Oracle 10g Automatic storage Management (Automatic Storage management,asm) or raw partitions,

The database may not be a separate file in the operating system, but the definition remains the same.

instance (instance): a set of Oracle background processes/threads and a shared memory area that is shared by threads/processes running on the same computer. This allows for the maintenance of volatile, non-persistent content

(some can flush output to disk). A DB instance can exist even without disk storage. Maybe the example is not the most useful thing in the world, but you can think of it as

Is the most useful thing, which helps to draw a line between an instance and a database.

These two words can sometimes be used interchangeably, but the concepts are completely different.

An instance is a set of operating system processes (or a multithreaded process) and some memory. These processes can operate the database;

A database is simply a collection of files (including data files, temporary files, redo log files, and control files). At any one time, an instance can have only one set of related files (associated with a database).

In most cases, the reverse is true: only one instance of a database operates on it. However, Oracle's real application cluster (real application Clusters,rac) is an exception,

This is an option provided by Oracle that allows operations on multiple computers in a clustered environment so that multiple instances can mount and open a database (on a shared set of physical disks) at the same time.

As a result, we can access this database from several different computers at the same time. Oracle RAC supports highly available systems that can be used to build a solution that is very scalable.

An instance is the name of the memory structure that governs the associated library (the SGA, PGA, server process, user process, background process, and so on)
The database is the actual disk files (data files, log files, control files, etc.), is responsible for saving the data, but by the corresponding instance to manipulate its data
Service Name is the name of the publicity, for the Network monitoring services
In fact, in our traditional concept, the database is a generic name, in Oracle, you can understand the "database" as a big concept, but also to understand it as a small concept

1, an Oracle database system can install several databases at the same time, each database corresponding to a unique instance, but the OPS system, can be multiple instances of a database operation, called parallel server
2, just a name, SID is Instance_name,service_names mainly used in the listener, in order to facilitate it, some are for the continuation of traditional habits, some for more convenient use
3, NET Easy config should be the host string, is for the client service,
A database can advertise multiple service names (Service_names)
A client can also connect to the same database server with multiple host strings
4, an OS can be installed multiple Oracle database (small concept), each library can publish multiple service names, all through Init.ora and Listener.ora to achieve

Host string, database service name, database alias, not quite the same.

Service_names is the external service name , is used by the server side, a library can set up a number of external service name, for different purposes

The term " host string " is used primarily in Sql*plus, which is used on the client,
" database aliases " are used primarily when some development tools connect to an Oracle server.
There is also the service Name of the data source, like when you configure ODBC,
They are all the same, pointing to a small piece of text in the client Tnsnames.ora file

1, in the Sql/plus in the landing, with User/[email protected]***, then, this * * * is the host string.
2, the user and password when setting up ODBC must be a legitimate user and password in Oracle, but do not lose, it is also possible to refer to the ODBC name.
3. To purchase Oracle from an Oracle agent, the user counts the money. So, this "user" is with us in Oracle.

The user created by the Create user USERNAME identified by password is a different concept, "number of users" refers to the number of concurrent access users,

(I understand that the number of processes accessing the same memory address at the same time). If I buy 8 users, can build dozens of, hundreds of users also no problem, as long as the peak can not reach the number of concurrent.


For example, your name is Xiao Ming, but you have a lot of nicknames. Your parents call you xiaoming, but your friends call you a nickname.

Here your parents are Oracle instances, Xiaoming is Sid,service name is your nickname.

sid is used for instance to differentiate between individual databases, service name for external links .
for the first contact with Oracle database, the two concepts that are easily confused are Oracle instances and Oracle databases. These two
concepts are different from the instance and database under SQL Sever, and of course there are similarities. Just in SQL Server we don't have to spend too much
effort to figure out SQL instances and databases, because it's simple and easy to understand. The following is a brief description of the SQL instance, database, and more about the
of Oracle under the instance and database.

=====================================================

one, SQL Instances in server and database

(or IP), and if you install SQL Server on the same machine, we can name the instance servername/ InstanceName.


Span style= "COLOR: #ccffff" to Servername:portno.
2. Configure IP addresses for different instances, associated access protocols, ports, and so on.
3. The accessibility of the instance requires that the corresponding service for that instance be started. It is important to note that the service name of the instance name and instance is not the same as the

4. The relevant functional settings of the instance can be implemented by a perimeter application configuration.
=======5. Once the above is complete, access to the database can be achieved.

=======================================================

An Oracle server consists of an Oracle instance and an Oracle database.

That is: Oracle Server = Oracle Instance + Oracle Database

Oracle Instances
Includes a memory structure (SGA) and a series of background processes (Background process), which together are called an Oracle instance
That is: Oracle Instance = SGA + Background Process
Oracle Memory Architecture
Includes system global Area (SGA) and program Global (PGA)
Oracle Memory structures = SGA + PGA

The SGA is shared by the server and background processes
The PGA contains data and control information for a single server process or a single background process, contrary to the SGA shared by several processes, the PGA is
Zones that are used only by one process, the PGA is allocated when the process is created and is reclaimed when the process is terminated. This is generated by the server process.

1.SGA
System Global Area SGA,SGA = data buffer + Redo log buffer + shared pool + large pool + Java pool + streaming pool

The system global zone is dynamic and is determined by the parameter sga_max_size.

View the SGA size of the current system: Show parameter sga_max_size;

To modify: Alter system set sga_max_size=1200m Scope=spfile;

Because the allocation of instance memory occurs when the database is started, you need to restart the database for the changes to take effect. The

ORACLE 10G introduces ASMM (automatic shared memory management), and the DBA simply sets up sga_target,oracle to automatically provision the shared pool, Java pool, large pool, data buffer, stream pool automatically. Canceling auto-leveling is
Sga_target is set to.

data buffer (database buffer cache): The mirrored
size that stores the data blocks obtained from the data file is determined by Db_cache_size
View: Show parameter db_cache_size;
settings: Alter system set DB_CACHE_ size=800m;

redo log buffers (Redo log buffer): Any modifications to the database are recorded sequentially in that buffer, and then by the LGWR process
It is written to disk. Size determined by Log_buffer

shared pool : Yes , the shared pool is primarily made up of library cache (shared SQL and PL) and data The dictionary cache consists of

Its role is to store frequently used SQL, and under limited capacity, the database system determines when to release SQL from the shared pool based on certain algorithms.
Library cache size determined by shared_pool_size
View: Show Parameter Shared_pool_size
Modify: Alter system set shared_pool_size=120m;

Data Dictionary Cache :
Store definitions and permissions information for data files, tables, indexes, columns, users, and other data objects in the database
Size determined by shared_pool_size, cannot be specified individually

Big Pool (Large Pool): is an optional area for some large processes such as Oracle Backup recovery operations, IO server processes, etc.

Java Pool : This program buffer is reserved for Java programs. If no Java program is necessary to change the default size of the buffer

Flow Pool (Stream pool): used by Oracle Stream

2.PGA
Is the memory process creation time allocation that is reserved for each user process connection to the Oracle database, released at the end of the process, and can only be used by one process.
The PGA consists of several structures:

sort area, cursor state area, session information area, stack area

By parameter: Pga_aggregate_target decision

3. Several types of processes:

User processes, server processes, background processes, other optional processes

User processes: When a user connects to a database, requests an Oracle server connection, a connection must first be established and not directly connected to the Oracle server.

Server processes: generated when connecting to an instance and establishing a user session, a standalone server or a shared server.

background process: maintains a connection between physical and memory to manage the reading, writing, recovery, and monitoring of the database. The server process is primarily through it and the user process

Communicate and exchange data between it and the user process. On UNIX machines, the Oracle daemon process is relative to the operating system process, i.e.,

An Oracle background process will start an operating system process. On a Windows machine, the Oracle background process opens Task Manager, relative to the operating system thread,

We can only see a ORACLE.EXE process, but with another tool, we can see the threads that are contained in the process.

Optional Process: ARCN archive process, RECO, snnn, pnnn

Background processes that must be available:
dbwn Database Write process, Pmon program monitoring process, Smon system monitoring process, LGWR log write process, CKPT checkpoint process



dbwn (Database write process), once used the name DBWR (datebase Writer), later allow multiple processes to write the data file is changed to DBWN. Be responsible for changing the

Data blocks are written to data files on disk from the database buffer cache.

Write condition: Checkpoint occurs, dirty cache reaches limit, no free cache, timeout occurs, table space is offline, tablespace is read-only, table is deleted or truncated,
Start the backup table space. The number of data write processes can be modified
Alter system set DB_WRITER_PROCESSES=3 Scope=spfile;


Pmon (Program monitoring process):Clears the failed user process and frees the resources used by the user process.
If Pmon will roll back uncommitted work, release the lock and release the SGA resource allocated to the failed process.
Clear the failed process
Rolling back a transaction
Release lock
Releasing additional resources

Smon (System monitoring process):Check database consistency and complete disaster recovery when startup fails.
When a real column is restored, roll forward all the files in the Redo log, open the database in order for the user to access, roll back uncommitted transactions, release the temporary tablespace
Purge temporary space, gather free space, recover transaction activity from files that are never available, instance recovery for failed nodes in OPS
Clear the obj$ table
Reduce rollback segments
Take the rollback segment offline

LGWR (log write process):Writes changes in the redo log buffer to the online redo log file.
Conditions:
When submitted (commit)
Reach/Full
Every second
There are more than m redo log buffers that have not been written to disk
DBWR the SCN number of the data to be written is greater than the SCN number of the LGWR record, DBWR triggers LGWR write
Timeout
Write the log before the DBWR process

CKPT (Checkpoint process):DBWR/LGWR works, resulting in data files, log files, control file inconsistencies, CKPT process is responsible for synchronizing data files,
Log files and control files, ckpt updates the header information of the data file/control file
Conditions:
At the time of log switching
Database with immediate, transaction, normal option when shutdown the database
Based on the values set by the initial session file Log_checkpoint_interval, Log_checkpoint_timeout, and Fast_start_io_target, determine
User triggered

ARCN (archive process)
Back up or archive a full log group at each log switch
Conditions:
When the database is running in archive mode

RECO
Responsible for solving problems in distributed things. Oracle can connect to multiple databases remotely, and some things are in an outstanding state due to network problems.
The RECO process attempts to establish communication with the remote server, and when the failure is eliminated, the reco process automatically resolves all pending sessions.

Server process (Service processes)
Divided into private service processes (dedicated server process) and shared services processes (multitreaded server process)
Private service process: A service process that corresponds to multiple user processes and rotates the service for the user process.

User processes, service processes (Server process), background processes (Background Processes) Start-up
User process: Database user requests Oralce Server session is started
Service process: When a user session is started, the process is started when connected to an Oracle instance
Background process: When an Oracle instance is started, the associated background process is started

Third, Oracle database
Collection of a series of physical files
including control files, data files, online log files, parameter files, password files, etc.
That is: Oracle Database = controlfile + datafile + Logfiel + spfile +.
1. Control files (controlfile)
Database name, checkpoint information, time stamp for database creation
All data files, online log files, archived log file information
Backup information, etc.

2. Data file (datafile)
Contains all the data for users and applications
--View Data file information


3. Online log files
Records all user operations on the database, with at least two log group files in one database and at least one log member in each log group
Multiple log members in a log group are mirror-to-phase relationships

4. Archive log files
Oracle can run in two modes, archive mode and non-archive mode. In archive mode, in order to save all changes to the user,
After the online log file is switched and overwritten, the system saves them as a contiguous set of file series, which is the archive log file.
The user recovers unexpected data loss, exceptions, and so on.

5. parameter files (pfile and SPFile)
Initsid.ora or Init.ora files, usually located in: $ORACLE _base/admin/<sid>/pfile
Initialization files record the startup parameters of many databases, such as memory, control files, number of processes, etc., when the database is started (Nomount loading)

6. Other documents
Password file: Authentication for Oracle with SYSDBA rights users.
Alert log file: Alarm log file (Alert.log or Alrt.ora), record database startup, shutdown, and some important error messages
View path: Select value from v$parameter where name = ' Background_dump_dest ';

7. Logical organizational structure of the database
Table space, segment, area, block
A database consists of one or more table spaces, and a table space can belong to only one database
A table space consists of one or more data files, and one data file can belong to only one table space
A data file consists of one or more operating system blocks, each operating system block can only be counted as one data file
A table space can contain one or more segments, one segment can belong to only one table space
A segment consists of one or more zones, each of which can belong to only one segment
A zone consists of one or more Oracle blocks, and each Oracle block can belong to only one zone
A zone can belong to only one data file, the space of the data file may be assigned to one or more zones
An Oracle block consists of one or more operating system blocks, and an operating system block is part of an Oracle block

Oracle database, instance, service name, SID