Oracle instance and Oracle Database overview

The two concepts that are easily confusing for first-contact Oracle databases are Oracle instances and Oracle databases. These two concepts are different from instances and databases under SQL Sever, and of course there are similarities. Just in SQL Server we don't need to spend too much effort figuring out SQL instances and databases because it's easy to understand. The following is a brief description of the SQL instance, the database, and more of the examples and databases under Oracle.

An instance and database in SQL Server

An instance in 1.SQL refers to a single default instance on a SQL Server server. The default instance name is the machine name servername (or IP), and if you install SQL Server on the same machine, we can name the instance servername/instancename.

That is, there can be several different instances on a single SQL Server server. There can be multiple different databases under one instance.

For access to databases under different instances, access can be achieved using SERVERNAME/INSTANCENAME:PORTNO, and the default instance is Servername:portno.

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

3. The accessibility of an instance requires that the corresponding service for the instance be started. Notice here that the instance name and the instance's service name are not the same. The default instance's service name is MSSQLSERVER, and the named instance's service name is Mssql$instance_name.

4. The settings for the relevant functionality of the instance can be implemented through the perimeter application configuration.

5. After the completion of the above, access to the database can be achieved.

Second, Oracle example

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

That is: Oracle Server = Oracle Instance + Oracle Database

Oracle Instances

Includes the memory structure (SGA) and a series of background processes (Background process), which together are called an Oracle instance

namely: Oracle Instance = SGA + Background Process

Oracle Memory Architecture

Contains the system global area (SGA) and program Global Area (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 an area that is used by only one process, and the PGA is reclaimed when the process is created to terminate the process. is generated by the server process.

1.SGA

System global Zone SGA,SGA = data buffer + Redo log buffer + shared pool + large pool + Java pool + stream Pool

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

View the current system's SGA size: 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.

ORACLE 10G introduces ASMM (automatic shared memory management), and the DBA automatically deploys the shared pool, Java pool, large pool, data buffer, and stream pool with just the sga_target,oracle. Canceling auto leveling is sga_target set to.

Data buffers (Database buffer cache): Storing mirrors of blocks of data obtained from data files

Size determined by db_cache_size

View: Show parameter db_cache_size;

SETTING: Alter system set db_cache_size=800m;

Redo Log buffers (Redo log buffer): Any modifications to the database are recorded sequentially in the buffer and then written to disk by the LGWR process, up to the size of the Log_buffer

Shared pool: Is the most critical memory fragment in the SGA, where the shared pool is primarily cached by the library (shared SQL and Pl/sql areas) and data

Dictionary cache composition, its role is to store frequently used SQL, in limited capacity, the database system according to a certain algorithm

Decide when to release the SQL from the shared pool.

Library cache size determined by shared_pool_size

View: Show Parameter Shared_pool_size

Modification: Alter system set shared_pool_size=120m;

Data Dictionary caching:

Store definition and permission information for data files, tables, indexes, columns, users, and other data objects in a database

Size is determined by shared_pool_size and cannot be specified individually

Dachi (Large Pool): 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 it is not necessary to change the default size of the buffer without Java programs

Stream pool: Used by Oracle Streams

2.PGA

is the memory reserved for each user process to connect to the Oracle database

When a process is created, it is released at the end of the session and can only be used by a process

The PGA includes the following 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, and other optional processes

User process

When a user connects to a database and requests an Oracle server connection, you must first establish a connection and not connect directly to the Oracle server

Server process

When connecting to an instance and creating a user session, a stand-alone server or providing a shared server can generate

Background process

Maintain physical and memory connections to manage database reading, writing, recovery, and monitoring.

The Server process is primarily connected and communicated with the user process, and is exchanged for data between him and the user process.

On UNIX machines, the Oracle background process is relative to the operating system process, that is, an Oracle background process starts an operating system process.

On the Windows machine, the Oracle background process, in relation to the operating system thread, opens Task Manager, and we can only see a ORACLE.EXE process, but with another tool, we can see the threads that are included in the process.

There must be a background process

DBWN--> Database Write process

Pmon--> Program monitoring process

Smon--> System Monitoring process

LGWR--> Log Write process

CKPT--> Checkpoint Process

Optional processes:

ARCN Archiving Process

Reco

snnn

pnnn

DBWN (Database write process)

Responsible for writing modified blocks of data from the database buffer cache to the data file on disk

Write Condition:

Checkpoint occurred

Dirty cache reaches limit

There's no free cache

Timeout occurred

Table Space Offline

Table Space Read Only

Table is deleted or truncated

Start Backup table Space

You can modify the number of data write processes

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 assigned to the failed process.

To purge failed processes

Rolling back a transaction

Release lock

Freeing other resources

Smon (System monitoring process)

Check database consistency, complete disaster recovery when startup fails, etc.

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

Clears temporary space, gathers free space, recovers transaction activity from files that are never available, recovery of instances of failed nodes in OPS

Clear obj$ Table

Shrink rollback Segment

To make a rollback segment go offline

LGWR (log write process)

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Write changes in redo log buffers to the online redo log file

Conditions:

Time of submission (commit)

Redo Log buffer reaches 1/3 full

Every 3 seconds

There are more than 1MB redo log buffers not written to disk

The DBWR of the data to be written is greater than the SCN number of the LGWR record, DBWR triggers the LGWR write

Timeout

Write a log before DBWR process

CKPT (Checkpoint process)

DBWR/LGWR working principle, 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:

When the log is switched

Database with immediate, transaction, normal option shutdown database

Based on the values of the Log_checkpoint_interval, Log_checkpoint_timeout, and fast_start_io_target settings of the initial conversation file, determine

User triggers

ARCN (archive process)

To back up or archive a full log group for each log switch

Conditions:

When the database runs in an archived way

Reco

Responsible for solving the problems in distributing things. Oracle can connect to multiple remote databases, and when due to network problems, some things are in the open.

The RECO process attempts to establish communication with the remote server, and the RECO process resolves all pending sessions automatically after the failure is resolved.

Server process (Service processes)

Divided into dedicated service processes (dedicated server process) and shared services processes (multitreaded server process)