Oracle instances and Oracle databases (Oracle Architecture)

Original: http://blog.csdn.net/robinson_0612/archive/2010/04/26/5529239.aspx

 

-- ===================================================== ===

-- Oracle instance and Oracle Database (Oracle Architecture)

-- ===================================================== ===

/*

For those who are new to Oracle databases, the two concepts that are easily obfuscated are Oracle instances and Oracle databases. These two

The concept is different from that of SQL Server instances and databases. We don't need to spend too much on SQL Server.

It is easy to understand and understand SQL instances and databases. The following is a brief description of SQL instances and databases.

The instance and database under oracle.

 

I. instances and databases in SQL Server

1. an instance in SQL indicates that there is only one default instance on an SQL Server server. The default Instance name is servername.

(Or IP address). If SQL Server is installed on the same machine, we can name the instance servername/InstanceName.

That is, multiple different instances can exist on an SQL Server server. A single instance can have multiple different databases.

For access to databases under different instances, use servername/InstanceName: portno to implement access. The default instance

Servername: portno.

2. Configure IP addresses, related access protocols, and ports for different instances.

3. For instance accessibility, you need to start the corresponding services of the instance. Note that the Instance name and service name of the instance are not the same

. The service name of the default instance is MSSQLServer, and the service name of the instance is MSSQL $ instance_name.

4. You can configure the related functions of the instance through the peripheral application.

5. After completing the preceding steps, you can access the database.

Ii. Oracle instances

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

That is, Oracle Server = Oracle instance + Oracle Database

Oracle instances

Including the memory structure (SGA) and a series of background processes, both of which are called an oracle instance

That is, Oracle instance = SGA + background process

Oracle Memory Structure

Including system global zone (SGA) and program global zone (PGA)

Oracle memory structures = SGA + PGA

SGA is shared by servers and background processes.

PGA contains the data and control information of a single server process or a single background process. Unlike the SGA shared by several processes, PGA is

In a region used by only one process, PGA is allocated when a process is created and recycled when the process is terminated. It is generated by the server process.

1. SGA

System global SGA, SGA = data buffer + redo log buffer + Shared Pool + large pool + Java pool + stream pool

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

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

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

Because the instance memory is allocated when the database is started, you need to restart the database for the modification to take effect.

Oracle 10g introduces asmm (automatic shared memory management), DBA only needs to set sga_target, Oracle will

Automatically allocates the Shared Pool, Java pool, large pool, data buffer, and stream pool. To Cancel automatic configuration

Set sga_target.

Database buffer cache: The image used to store data blocks obtained from data files.

The size is determined by db_cache_size.

View: Show parameter db_cache_size;

Set: Alter system set db_cache_size = 800 m;

Redo log Buffer: any changes to the database are recorded in the buffer in order, and then the lgwr Process

It is written to the disk, and the size is determined by log_buffer.

Shared Pool: is the most critical memory segment in SGA. The shared pool is mainly composed of database cache (shared SQL zone and PL/SQL zone) and data.

Dictionary cache, which stores frequently used SQL statements. with limited capacity, the database system determines based on certain algorithms.

Determine when to release the SQL statement in the shared pool.

The database cache size is determined by shared_pool_size.

View: Show parameter shared_pool_size

Modify: Alter system set shared_pool_size = 120 m;

Data Dictionary cache:

Stores the definitions and permissions of data files, tables, indexes, columns, users, and other data objects in the database.

The size is determined by shared_pool_size and cannot be specified separately.

Large pool: it is an optional area for some large processes, such as Oracle backup and recovery operations, Io server processes, etc.

Java pool: The program buffer is reserved for the Java program. If you do not need a Java program, you do not need to change the default size of the buffer.

Stream pool: used by Oracle streams

2. PGA

Memory reserved for connecting each user process to the Oracle database

Allocated when a process is created, released when the process ends, and can only be used by one process

PGA includes the following structures:

() Sorting area

() Cursor status Zone

() Session information area

() Stack Zone

Determined by the parameter pga_aggregate_target.

3. Several processes: User process, server process, background process, and other optional Processes

User Process

When a user connects to a database and requests a connection from the Oracle server, a connection must be established first and will not be directly connected to the Oracle server.

Server process

An independent server or Shared Server can be used to connect to an instance and establish a user session.

Background Process

Maintain the connection between the physical and memory to manage the read/write, recovery, and monitoring of the database.

Server process communicates with user process and exchanges data with user process.

On Unix machines, the Oracle background process is relative to the operating system process, that is, an oracle background process starts an operation

System process.

On a Windows machine, compared to the operating system thread, the Oracle background process opens the task manager and we can only see one

Oracle. EXE process, but through another tool, you can see the threads included in the process here.

 

Required background processes

Dbwn --> database write process

Pmon --> program monitoring process

SMON --> system monitoring process

Lgwr --> log Writing Process

Ckpt --> Checkpoint Process

 

Optional process:

Arcn archiving process

Reco

Snnn

Pnnn

 

Dbwn (Database write process)

Writes modified data blocks from the database buffer cache to the data files on the disk.

Write condition:

Check Point

Dirty cache reached limit

No free Cache

Timeout occurred

Tablespace offline

Tablespace read-only

Table deleted or truncated

Start to back up tablespace

Number of data write processes that can be modified

Alter system set db_writer_processes = 3 Scope = spfile;

Pmon (Program monitoring process)

Clear invalid user processes and release resources used by user processes.

For example, pmon rolls back unsubmitted jobs, releases locks, and releases the SGA resources allocated to failed processes.

Clear failed processes

Roll back a transaction

Release lock

Release other resources

SMON (system monitoring process)

Check Database Consistency and complete disaster recovery when startup fails.

When the real column is restored, roll back all the files in the redo log, open the database for user access, roll back uncommitted transactions, and release the temporary tablespace.

Clears temporary space, aggregates idle space, restores transaction activities from available files, and recovers instances of failed nodes in Ops.

Clear OBJ $ table

Reduce rollback segments

Offline rollback segments

Lgwr (log writing process)

Write changes in the redo log buffer to the online redo log file.

Condition:

Commit)

Reached/full

Every second

There are more than m redo log buffer not written to disk

The SCN Number of the data to be written by dbwr is greater than the scn number of the lgwr record. dbwr triggers lgwr writing.

Timeout

Write logs before the dbwr Process

Ckpt (Checkpoint Process)

The working principle of dbwr/lgwr has resulted in inconsistent data files, log files, and control files. The ckpt process is responsible for synchronizing data files,

Log Files and Control Files

Ckpt updates the header information of the data file/control file.

Condition:

When switching logs

When the database is shut down with the immediate, transaction, and normal options

The values are determined based on the values set in the initial file log_checkpoint_interval, log_checkpoint_timeout, and fast_start_io_target.

User-triggered

Arcn (archiving process)

Back up or archive a full log group during each log Switch

Condition:

When the database is running in archive Mode

 

Reco

Resolves faults in distributed transactions. Oracle can connect to multiple remote databases. Due to network problems, some things are pending.

The reco process tries to establish communication with the remote server. When the fault is eliminated, the reco process automatically resolves all pending sessions.

Server process)

It can be divided into two types: dedicated server process and multitreaded server process)

Dedicated service process: a service process corresponds to multiple user processes and serves user processes in turn.

Start of user process, server process, and background processes

User process: the database user is started when requesting the oralce Server session

Service Process: when a user session is started and connected to an oracle instance, the process is started.

Background process: When the Oracle instance is started, start the background process.

Iii. Oracle Database

A collection of physical files

Including control files, data files, online log files, parameter files, and password files

That is: Oracle Database = controlfile + datafile + logfiel + spfile + ..

1. controlfile)

Database Name, checkpoint information, and timestamp of database creation

All data files, online log files, and archived Log File Information

Backup Information

2. Data File)

Contains all data of users and Applications

-- View data file information

3. Online log files

Records all user operations on the database. A database must contain at least two log group files and each log group must contain at least one log member.

Multiple log members in the log group are mirror-related.

4. archiving log files

Oracle can run in two modes: Archive mode and non-archive mode. In archive mode, to save all user modifications,

After switching online log files and overwriting, the system saves them into a continuous file series, which is an archive log file.

Recover unexpected data loss and exceptions.

5. Parameter files (pfile and spfile)

Initsid. ora or init. ora file, usually at: $ oracle_base/admin/<Sid>/pfile

The initialization file records the startup parameters of many databases, such as memory, control files, and number of processes. It is loaded when the database is started (nomount is loaded)

6. other files

Password File: used to authenticate oracle users with sysdba permissions.

Alarm Log File: The alarm log file (alert. log or alrt. ora), which records the database startup, shutdown, and important error information.

View path: Select value from V $ parameter where name = 'background _ dump_dest ';

7. logical structure of the database

Tablespace, segment, partition, Block

A database consists of one or more tablespaces. A tablespace can belong to only one database.

A tablespace consists of one or more data files. A data file can only belong to one tablespace.

A data file consists of one or more operating system blocks. Each operating system block can contain only one data file.

One tablespace can contain one or more segments, and one segment can only belong to one tablespace.

A cidr block consists of one or more zones. Each region can belong to only one segment.

A zone consists of one or more Oracle blocks. Each Oracle block can belong to only one zone.

A zone can belong to only one data file. The space of the data file can be allocated to one or more zones.

An Oracle block consists of one or more operating system blocks. An operating system block is part of an Oracle block.

Iv. Relationship between Oracle instances and Oracle databases

1. One instance can load and open only one database

2. A database can be loaded and opened by multiple instances.

3. The ing between instances and databases is one-to-one or multiple-to-one.