Introduction to Oracle Databases

Introduction to Oracle Databases

First, introduce

Oracle database systems are a set of software offerings at the core of distributed databases delivered by Oracle (Oracle), the most popular client/server (CLIENT/SERVER,C/S) or browser/server (browser/server,b/ S) One of the databases for the architecture.

Oracle database is the most widely used database management system in the world, as a general database system, it has complete data management function; As a relational database, it is a complete relation product; As a distributed database, it realizes the distributed processing function.

As of June 2015, the latest version of Oracle database is Oracle 12c.

Second, characteristics

1, complete data management functions: Data of a large number of data preservation of persistent data on the reliability of the shared data

2, the complete relationship of products: information standards-Relational DBMS all the information should be in a logical way, that is, the values in the table explicitly represent the criteria view update criteria for access assurance-as long as the data in the table that forms the view changes, the data in the corresponding view changes both the physical and logical independence criteria

3. Distributed processing function:

Oracle database since the 5th edition has provided the distributed processing power, to 7th edition has the relatively perfect distributed database function, an Oracle distributed database by Oracler DBMS, Sql*net, sql* Connect and other non-Oracle relational products constitute.

4, with Oracle can easily realize the operation of the Data Warehouse.

Benefits: Strong scalability, strong scalability, strong data security and strong stability

Iii. Oracle Architecture

The Oracle architecture includes a number of basic components, and a few common components are briefly described below.

1, Oracle server (server)

Oracle servers are the largest concept, with multiple file structures, process structures, and memory structures, but not all of these structures are used when working with SQL statements. Some structures are used to improve database performance, ensure that the database is recoverable when it encounters software or hardware errors, or perform other tasks required to maintain the database. Oracle servers are comprised of two parts: Oracle Instance (instance) and Oracle database (databases).

2, Oracle Instance (example)

Oracle Instance (instance) definition: a means of accessing Oracle database; A Instance corresponds to one and only one database, and a database can have multiple Instance to access it. In other words, instance and database of the relationship, in a typical case, a machine-mounted oracle, they are one-to-one relationship, the corresponding cluster approach (RAK) is more than one Instance is also composed of two parts: memory (memory) and background processes (background process).

The memory is composed of two parts: SGA (System global Area) and the PGA (Program Global Area, and the programming globals); an instance of a SGA,SGA is an essential part of an instance; a server When the process starts, there is a pga;oracle is a crazy to eat the memory of the software, memory mainly consumed in the SGA.

Description: Instance is a living thing, easy to disappear, Oracle did not start, there is no instance.

2.1 SGA Structure

The SGA is a dynamic structure, the size of which is specified by Sga_max_size.

2.1.1 Shared pool

Used to store recently used SQL statements and data dictionaries. It consists of two parts, namely, library cache and data dictionary caching (dictionary cache).

Here is the command to change the shared pool:

Alter system set shared_pool_size=64m;

The library cache is used to store SQL statements that are used, and the data dictionary cache is used to store recently used data dictionaries. Their size needs to be determined by using a shared pool and not set themselves.

2.1.2 Database buffer cache data cache area

It is used to store recently accessed blocks of data, consisting of several small buffers. Db_block_size determines the size of the data block.

Alter system set db_cache_size=96m;

When you perform a select or DML, data is read from the data file to the cache area, where the access and operation of the data is performed in memory.

2.1.3 Redo Log Buffer log buffers

Used to record the changes of routines, and their dimensions are defined by Log_buffer. When DDL or DML is executed, the server process first logs changes to the log buffer before the data cache is modified.

2.1.4 Large pool and Java pool (optional)

If you install or use Java, you need to use a Java pool.

2.2 Background process structure

Process (processes) in Oracle are grouped into three categories: the background process (background processes), the server process (server processes), and the user process. In fact, the server process is also part of the background process, the process is refined in Oracle, where the background process is mainly focused on the core of the database process, and the server process is mainly used to handle the process of client and server connections.

Oracle has a number of required background processes that are required to run Oracle services.

2.2.1 DBWR

Used to write dirty data from the data cache to the data file. The so-called dirty data is the data that has changed.

2.2.2 Pmon

Used to monitor the execution of a server process and to clear the process if it fails.

2.2.3 CKPT

Used to issue checkpoints to synchronize data files, control files, and redo files for a database.

2.2.4 LGWR

Used to write the contents of the log buffer to the redo log.

2.2.5 Smon

System monitoring process. Used to automatically recover instances, merge free space, and free temporary segments.

2.2.6 Other processes

Other background processes that are used to run Oracle services.

3, Oracle database (databases)

Let's look at two concepts: Connection (link) and session (sessions). A connection primarily refers to a link between an Oracle client and a backend server, that is, a channel between the client and the server. Session sessions are established only when the link is established.

From the physical structure analysis, Oracle database consists of three types of files: Data files, control files, and redo log files, which are Oracle's core files, In addition, there are parameter file (parameter files), archived log files (archive log file) and password file (password file).

3.1 Data files

Used to store database data, including data dictionaries, user data, undo data, temporary data, and so on. A database is logically composed of one or more tablespace tablespace, while a tablespace is physically composed of one or more data files.

3.2 Control files (controlling file)

Used to record and maintain the physical structure of the database.

3.3 Redo log files (redo logs)

Used to record database changes that can be used to recover a database when a routine fails or media failure occurs. When DDL or DML, transactions are written to the log buffer, which is written to the redo log file by LGWR at a specific point in time. There are at least two log files in the database, which are recycled.

3.4 Archived log files (archive log file)

It is a backup of the redo log, which saves all the redo history.

3.5 Password file (password files) and parameter file (parameter file)

The password file is used primarily to hold the password of the superuser and the user name and password of other privileged users. All two files are saved in the < Installation path >/database folder.

Iv. The logical structure of Oracle database

Oracle database is logically composed of many table spaces, mainly divided into system table space and non system table space. Non-system tablespaces store data, indexes, programs, and other related information for each application.

The logical storage structure consists primarily of data blocks (datablocks), data areas (extents), data segments (segments), tablespace (tablespace), which enable Oracle to control the use of disk space in a granular manner.

1. Table Space

A database is divided into several logical storage units called tablespace (tablespace), each of which holds a set of related logical objects.

Each database is logically divided into one or more tablespace spaces. Users need to create one or more data files for each tablespace to physically store the logical objects that belong to this table space. The size of all data files in a table space is the amount of available data storage for this tablespace. The data in the database is actually stored in the data file that forms the table space.

When we are ready to launch a larger Oracle application system, we should create a table space that is exclusive to it, while defining the location of the physical file and the size of the hard disk.

The relationship between data files and table spaces:

2, paragraph

A segment is a set of data areas (extents) that are assigned to a particular logical structure.

According to the characteristics of the data stored in the paragraph, the segment is divided into four types, namely data segment, index segment, fallback section and temporary segment.

Oracle dynamically allocates new space when the existing data area is filled in one segment. In other words, Oracle assigns a new data area to this segment after the existing data area is filled. Because the data area is allocated on demand, the data area within a segment is not necessarily physically contiguous.

The data in a segment can span multiple data files, but not across multiple table spaces.

3. Data area

A data area is a collection of several contiguous blocks of data obtained by a single storage space allocation for storing information.

When a user creates a data table, Oracle assigns the data segment of this table to an initial data area that contains several blocks of data. Although there is no data in the data table at this time, the block of data in this initial data area is ready for inserting new data.

If the data blocks in the initial data area of a segment are fully loaded and new data is inserted into the space needed, Oracle automatically assigns an incremental data area to the segment. An incremental data area is a subsequent data area that is allocated after a segment relays an existing data area, and its capacity is greater than or equal to the previous data area.

For locally managed tablespace, Oracle first selects a data file that belongs to the table space, and then searches the bitmap of the data file to find contiguous blocks of data, when looking for free space for the new data area. If there is not enough contiguous free space in this block of data, Oracle queries other data files.

4. Data Block

In an Oracle database, the most granular data storage granularity is a block of data that is equivalent to a contiguous physical storage space on a disk.

The minimum physical storage unit of all data at the operating system level is bytes (byte), corresponding to the block of data. Each operating system has a parameter called block size. Each time Oracle acquires data, it always accesses an integer (Oracle) block of data, rather than accessing the data according to the capacity of the operating system block.

The default capacity of the data block allocation is determined by the initialization parameter db_block_size. In addition to this parameter, the administrator can set an additional 5 block capacity parameters. Oracle databases allocate and utilize storage space in a block of data. The block capacity should be set to an integer multiple of the capacity of the operating system block, while less than the maximum block capacity, in order to reduce unnecessary I/O operations. Oracle data blocks are the smallest storage units that Oracle can use and allocate.

In Oracle, the internal structure of the data block is similar regardless of whether it is stored in tables (table), index, or cluster table (clustered data).

Segment, data area, data block relationship: