Oracle Instances and Oracle Databases (Oracle architecture)

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

--oracle Instances and Oracle Databases (Oracle architecture)

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

/*

For the first contact with Oracle database, two concepts that are easily confused are Oracle instances and Oracle databases. These two

The concept differs from the instance and database under SQL Sever, and there are of course some similarities. Just in SQL Server we simply don't need to spend too

A lot of 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, the database, more about

Examples and databases under Oracle.

I. Instances and databases in SQL Server

The 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), if you install SQL Server on the same machine, we can name the instance servername/instancename.

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

Access to databases under different instances, with Servername/instancename:portno for access, default instance

For Servername:portno.

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

3. The accessibility of the instance requires that the corresponding service for that instance be started. It is important to note that the name of the instance and the service name of the instance are not

The same. The default instance service name is MSSQLSERVER, and the service name for the named instance is mssql$instance_name.

4. The relevant functional settings of the instance can be implemented by the configuration of the perimeter application.

5. Once the above is complete, access to the database can be achieved.

Second, Oracle instance

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 Zone 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.

ORACLE 10G introduces ASMM (automatic shared memory management), and DBAs only need to set up sga_target,oracle to

Automatic provisioning of shared pools, Java pools, large pools, data buffers, and streaming pools. Canceling auto-leveling is

Sga_target is set to.

Data buffers (Database buffer cache): Stores the image of a block of data obtained from a data file

Size 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: Is the most critical memory fragment in the SGA, and the shared pool is primarily cached by the library (shared SQL and PL) and data

Dictionary cache composition, its role is to store the frequent use of SQL, under limited capacity, the database system according to a certain algorithm

When to release SQL from the shared pool.

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

Candidates (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

Stream pool: Used by Oracle Streams

2.PGA

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

Process creation time allocation, release at end of process, can only be used by one process

The PGA consists of several structures:

() Sort Area

() Cursor status 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 process

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

Server process

When connecting to an instance and creating a user session, a standalone server or a shared server can generate

Background process

Maintains physical and memory connections to manage database read, write, restore, and monitor tasks.

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

On UNIX machines, the Oracle background process is relative to the operating system process, that is, an Oracle background process initiates an operation

System processes.

On Windows machines, Oracle background processes open Task Manager as compared to operating system threads, and we see only one

ORACLE. EXE process, but with additional tools, you can see the threads that are contained in the process.

Must have a background process

DBWN--Database write process

Pmon---Program monitoring process

Smon--System monitoring process

LGWR---Log write process

CKPT--Checkpoint process

Optional process:

ARCN Archive Process

RECO

snnn

pnnn

DBWN (Database write process)

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

Write Condition:

Checkpoint occurred

Dirty cache reaches limit

There's no free cache.

Timeout occurs

Table Space Offline

Table Space Read-only

Table is deleted or truncated

Start 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, complete disaster recovery when startup fails, etc.

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)

To write changes in the redo log buffer to the online redo log file

Conditions:

When submitted (commit)

Redo Log buffer reaches 1/3 full

Every 3 seconds

There are more than 1MB redo log buffers that were not 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

Iv. Relationship of Oracle instances and Oracle databases

1. An instance can load and open just one database

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

3. The relationship between an instance and a database is one-to-many relationships

Oracle Instances and Oracle Databases (Oracle architecture)