Oracle System Overview
Oracle has begun to release the version 5 in 86, the system has a distributed database processing function. In 88, the release version 6, Oracle RDBMS (V6.0) can be transaction option (TPO), improve the speed of transaction. In 1992, it launched Version 7, in Oracle RDBMS, Procedural Database Option, Parallel Server Option, called Oracle7 database management system, which releases the true potential of open relational systems. Oracle7's collaborative development environment provides a new generation of software lifecycle development environments that can be used to achieve high productivity, large transaction processing, and client / server structures. Collaborative development environment has portability, supports multiple data sources, multiple graphical user interfaces, multimedia, multi-ethnic languages, and Case and other collaborative application systems.
First, Oracle System
1. Oracle product structure and composition
The Oracle system consists of a batch of software products as the core of RDBMS, and its product structure is shown below:
SQL * Plus
Oracle * Forms
Oracle * Report
SQL * GRAPHIC
SQL * CALC
User-prepared program
Program interface
(SQL executive)
Oracle kernel
(RDBMS)
operating system
Data Dictionary
DD
Database DB
2. Oracle system features
Oracle In 1979, first, first introduced SQL standard relational database products, which can be run on more than 100 hardware platforms (the microcomputer, workstations, small machines, medium and large machines) support many operating systems. The user's Oracle application is convenient to move from a computer configuration to another computer configuration. Oracle's distributed structure can reside data and applications on multiple computers, while communication between each other is transparent. In June 1992, Oracle 7 collaborative server database launched by Oracle, so that the relational database technology has made a new level. According to IDG (International Data Group) 1992 global UNIX database market report, Oracle accounted for 50% of the market sales. It is loved by users because it has the following highlights:
l Supports high-performance transaction processing of large databases, multi-user. Oracle supports the largest database with a size of several hundred gigabies to make full use of hardware devices. Support a large number of users to perform various data applications on the same data, and make data contention to ensure data consistency. System maintenance has high performance, Oracle can work 24 hours a day, normal system operation (backup or individual computer system failure) does not interrupt the database. The availability of database data can be controlled, and can be controlled at the database level or on sub-database level.
l Oracle complies with the data access language, operating system, user interface, and network communication protocol industrial standards. So it is an open system that protects the user's investment. The US Standardization and Technology Institute (NIST) tests Oracle7 Server, 100% compatible with the second level of ANSI / ISO SQL89 standard.
l Implement security control and integrity control. Oracle is a reliable security for limiting each monitoring data access. Oracle implements data integrity, specifying standards for acceptable data.
l Supports distributed databases and distribution processing. Oracle allows you to make full use of computer systems and networks, allow processing to be divided into database servers and client applications, all shared data management of computer processing, and workstations running database applications are set to interpret and display data. Through the computer environment connected to the network, Oracle will be stored in a plurality of computers into a logical database, which can be accessed by all network users. Distributed systems have transparency and data consistency like centralized databases. L has portability, compatible and connectable. Since Oracle software can run on many different operating systems, it is possible to port to any operating system on many different operating systems, and only requires little modification or Oracle software is compatible with industrial standards, including many industrial standard operating systems, which can operate on any operating system. Connectability means that Oralce allows different types of computers and operating systems to share information over the network.
Second, the Architecture of Oracle Database System
Oracle Database System is a computer system with management Oracle database. Each running Oracle database is connected to an Oracle Instance (Instance). An Oracle instance is a software mechanism for accessing and controlling a database. Each time a database is started on a database server, a memory area (SGA) called System Global Area is assigned, with one or more Oracle processes being started. The combination of the SGA and Oracle processes is called an Oracle database instance. An example of the SGA and processes operate to manage database data, work for one or more user services of the database.
In the Oracle system, first is the instance start, and then assemble (MOUNT) a database. In the loose coupling system, a single database can be assembled by multiple instances, ie multiple instances share the same physical database by multiple instances.
1. Process structure and memory structure of Oracle instances
1) Process structure
The process is a mechanism in the operating system that enables a range of steps. Terms of jobs (JOB) or tasks (TASK) are used in some operating systems. A process usually has its own dedicated storage area. The architecture design of the Oracle process makes the largest performance.
Oracle instances have two types: single process instances and multi-process instances.
Single process Oracle (also known as single user oracle) is a database system that performs all Oracle code in a process. Since the Oracle section and the client application cannot be executed in progress, Oracle's code and user database applications are single process execution.
Oracle instances in a single process environment allow only one user can access. For example, run Oracle on MS-DOS.
Multi-process Oracle instance (also known as multi-user Oracle) uses multiple processes to perform different parts of Oracle, have a process for each connected user.
In multi-process systems, processes are divided into two categories: user processes and Oracle processes. When a user runs an application, such as a Pro * C program or an Oracle tool (such as SQL * Plus), the application runs a user process for the user. Oracle processes are divided into two categories: server processes and background processes. The server process is used to process a request for a user process connected to the instance. When the application and ORACELE are running on the same machine, the user process and its corresponding server process are generally combined to reduce system overhead. However, when the application and Oracle run on a different machine, the user process passes through a separation server process with Oracle communication. It performs the following tasks: l for syntax analysis and execution of the SQL statement issued.
l Reads the necessary data blocks into the SGA shared database buffer (the block is not in the buffer) from the disk (data file).
l returns the result to the application process.
The system uses some additional processes in multi-process systems in the multi-process system to make performance and coordinate multiple users. In many operating systems, the background process is automatically established when the instance is started. An Oracle instance can have many background processes, but they are not always existed. The name of the background process is:
DBWR database write program
LGWR log writing program
CKPT checkpoint
SMON system monitoring
PMON process monitoring
Arch archive
RECO recovery
LCKN blockade
DNNN scheduling process
SNNN server
Each background process interacts with different parts of the Oracle database.
The following is a brief introduction to the function of the background process:
DBWR process: This process performs a buffer to write a data file, which is a Oracle background process responsible for buffer storage area management. When a buffer in the buffer is modified, it is marked "Stroke", the main task of DBWR is to write "stain" buffer to disk, keep the buffer "clean". Due to the buffer of the buffer storage area, the number of unused buffers is reduced because the buffer buffer is filled in the database or blurred by the user process. When the unused buffer drops rarely, when the user process is not available from the disk to the memory to the memory storage area, the DBWR will manage the buffer storage area, and the user process can always be unused. Buffer.
Oracle uses the LRU (Least Recently Used) Algorithm (Recent Use Algorithm) Holding the data block in memory, which is recently used, so that I / O is minimized. In the following, the DBWR should be written to the disk to write the blurred buffer:
l When a server process moves a buffer into a "stain" table, the service process will notify DBWR when the critical length is expressed. The critical length is half the value of the parameter DB-block-Write-Batch.
l When a server process finds a DB-Block-Max-Scan-CNT buffer in the LRU table, there is no unused buffer, which stops finding and notifying DBWR to write.
l When timeout (3 seconds each time), DBWR will notify itself.
l When the checkpoint occurs, the LGWR will notify DBWR.
In the first two cases, DBWR writes the blocks in the stain table to disk, and the number of blocks written is specified by the initialization parameter db-block-write-baith. If there is no buffer that specifies the number of blocks in the table, DBWR finds another blurred buffer from the LUR table.
If DBWR is not active in three seconds, it will appear. In this case DBWR looks for the LRU table to find the specified number of buffers, and write the locked buffer to disk. Whenever timeout, DBWR finds a new buffer group. Each time the DBWR lookup buffer is twice as for the value of the bedding parameter db-block-write-ba. If the database is empty, DBWR finally writes all buffer storage area to disk. When a checkpoint occurs, the LGWR specifies that a modified buffer table must be written to the disk. DBWR writes the specified buffer to disk.
On some platforms, one example can have multiple DBWRs. In such an example, some blocks can be written to one disk, and other blocks can be written to other disks. Parameter DB-WRITERS Controls the number of DBWR processes.
LGWR Process: This process writes a log file on disk, which is a Oracle background process responsible for managing log buffers. The LGWR process will output all log items since the last write disk, LGWR output:
l Writes a submission record when the user process is submitted.
l Output log buffers every three seconds.
l Output log buffers when 1/3 of the log buffer is full.
l Out the log buffer when the DBWR writes the modified buffer to disk.
The LGWR process is simultaneously written to the active mirror icon online log file group. If a file in the group is deleted or unavailable, the LGWR can continue to write to other files of the group.
The log buffer is a loop buffer. When LGWR writes a log entry of the log buffer to the log file, the server process can write the new log item to the log buffer. LGWR is usually written quickly, ensuring that the log buffer has a total space to write new log entries.
Note: Sometimes when more log buffers are needed, LWGR writes the log item in front of a transaction, and these log entries are only permanently after submission of transactions.
Oracle uses a fast submission mechanism when the user issues a CommT statement, a commit record immediately places a log buffer, but the corresponding data buffer change is delayed until it is written to the data file when it is more efficient. When a transaction is submitted, it is assigned to a system modification number (SCN), which is recorded in the log entry with the transaction log entry. Since the SCN is recorded in the log, the recovery operation can be synchronized in the parallel server option configuration.
CKPT process: This process modifies the title of all data files when the checkpoint appears to indicate the checkpoint. In general, the task is executed by the LGWR. However, if the checkpoint significantly reduces system performance, the CKPT process can be operated, and the operation of the checkpoint executed by the LGWR process is separated by the CKPT process. For many applications, the CKPT process is unnecessary. Only when the database has many data files, LGWR significantly reduces performance when checkpoints makes the CKPT. The CKPT process does not write blocks to disk, which is done by DBWR.
Initialization Parameters CheckPoint-Process Controls the enablement of the CKPT process or cannot be made. The default is false, that is, it cannot be made.
SMON Process: The executive recovery is performed when the instance is started, and it is also responsible for cleaning up the disabled period. In an environment with parallel server options, SMON is restored by the fault CPU or instance. The SMON process is regularly wake, check whether it is needed, or can be called when other processes are needed.
PMON Process: This process performs process recovery when the user process fails, responsible for cleaning up internal storage and release the resources used by the process. Example: It is to reset the status of the active transaction table, release the block, remove the ID of the fault from the active process table. PMON also periodically checks the status of Dispatcher and server processes. If they are dead, restart (not include intentional delete processes). PMON is regularly wakeful, checking if it is needed, or other process finds that it can be called.
The RECO process: This process is a process that is used when there is a distributed option, which automatically solves the fault in distributed transactions. A node RECO background process is automatically connected to other databases that contain unresolved distributed transactions, and RECO automatically solves all hanging transactions. Any line corresponding to the hanging transaction that has been processed will be deleted from the suspension of each database.
When a database server's RECO background process attempts to establish communication of the same remote server, if the remote server is unavailable or when the network connection cannot be established, the RECO automatically connects again after a time interval.
The Reco daemon is only available in systems that allow distributed transactions, and the Distributed - Transactions parameter is greater than 0.
ARCH process: This process copies the filled online log file to the specified storage device. When the log is used for ArchiveLog, the ARCH process is only exists when the archive is filed.
Lckn Process: It is used in a parallel server option environment, which can be more than 10 processes (LCK0, LCK1 ..., LCK9) for blocking between instances.
DNNN process (scheduling process): This process allows the user process to share a list of limited server processes (SERVER Process). Each user process requires a dedicated Server Process when there is no scheduling process. Multiple user processes can be supported for multi-threaded servers. If there is a large number of users in the system, the multi-clue server can support a large number of users, especially in the client_ server environment.
Multiple scheduling processes can be created in a database instance. At least one scheduling process is established for each network protocol. The database administrator determines the optimum number of start-up schedule according to each process to connect to the number of restrictions in the operating system, which can increase or delete the schedule process at the instance runtime. Multi-clue servers require SQL * NET version 2 or later version. In the configuration of the multi-clue server, a network receiver process waits for the client to apply the connection request and send each one to a scheduling process. If you cannot apply a client to a scheduling process, the network receiver process will start a dedicated server process. The network receiver process is not an integral part of the Oracle instance, which is part of the network process related to Oracle. At the time of the instance, the network receiver is turned on, connects the user to Oracle to establish a communication path, and then each scheduling process gives the address of the scheduling process of the connection request to its receiver. When a user process is a connection request, the network receiver process analyzes the request and determines whether the user can use a scheduled process. If so, the network receiver process returns the address of the scheduling process, and then the user process is directly connected to the scheduling process. Some user processes cannot schedule the process communication (if using SQL * Net previous version users), the network receiver process cannot connect such a user to a scheduling process. In this case, the network receiver establishes a dedicated server process to establish a suitable connection.
2), Oracle Memory Structure
Oracle stores the following information in memory:
l The program code executed.
l Connected session information
l The required data and shared information in the program execution period store buffer information on the outer storage.
Oracle has the following basic memory structure:
l Software Code Area
l System global zone, including database buffer storage, log buffers, and shared pools.
l Program global zone, including the stack area and the data area.
l Sort zone
Software code area
The program code used to store or execute can be executed.
The software area is read-only and can be installed into shared or non-sharing. Oracle system programs are shared, so that multiple Oracle users can access it without having multiple copies in memory. User programs can be shared or not shared.
System global zone
For a set of shared memory structures allocated by Oracle, data or control information of a database instance can be included. If multiple users are connected to the same instance, the data in the SGA of the instance can be shared by multiple users, so it is also known as a shared global zone. When the instance is started, the storage of SGA is automatically assigned; when the instance is turned off, the storage is reclaimed. All users connected to the multi-process database instance can be automatically assigned; when the instance is turned off, the storage is reclaimed. All users connected to multiple process database instances can use information in their SGA, but only have several processes to write information. Storage information in SGA divides memory into several districts: Database buffer storage area, log buffer, shared pool, request, and response queue, data dictionary storage area, and other information.
Program global zone
PGA is a memory area, contains data and control information of a single process, so it is also called Process Global Area.
Sort zone
Sorting requires memory space, Oracle uses the memory sort data, which is called a sort area. The sort zone exists in the memory of the user process requested sort, the size of the space is the size of the sorted data, but is limited by the initialization parameter sort-area-ster.
2. Oracle configuration solution
All users connected to Oracle must perform two code modules to access an Oracle database instance:
l Applications or Oracle Tools: A database user performs a database application or an Oracle tool to send a SQL statement to the Oracle database.
l Oracle Server Program: Responsible for explaining and processing SQL statements in your application.
In multi-process instances, the code to connect the user can be configured according to one of the following three scenarios:
l For each user, its database application and server program combines a single user process
l For each user, its database application is running by the user process and has a dedicated server process. Execute the code of the Oracle server. Such a configuration is called a dedicated server architecture
l The process of executing the database application is different from the process of executing the Oracle server code, and each server process (executing Oracle server code) can serve multiple user processes, such as multi-thread server architecture.
1) The combination of the USER / Server process
Under this configuration, the database application and the Oracle server program run in the same process, which is called a user process.
This Oracle configuration is sometimes referred to as a single task Oracle (Single_Task Oracle), which is suitable for such an operating system, which maintains a quarantine between the database applications and the Oracle code in the same process, which is data security and The integrity is required. The program interface is responsible for the isolation and protection of Oracle server code, transmitting data between database applications and Oracle user programs.
2) System structure using a dedicated server process
Oracle systems using a private server process run on both computers. In this system, the user process executing the database application on a computer, and the server process on another computer performs the corresponding Oracle server code, the two processes are separated. Different server processes established for each user process are called a dedicated server process because the server process works only on the connected user process. This configuration is also known as two tasks Oracle. Each user process that connects to Oracle has a corresponding dedicated service process. This system structure allows client applications to be executed on workstations, communicating with computer running Oracle. This structure is also available when the customer application and Oracle server code is executed on the same computer. 3) System structure of multi-thread servers
Multi-Crew Server Configuration allows many user processes to share rare server processes. In the configuration without multiple clue servers, each user process requires its own dedicated server process. In a configuration with a multi-clue server, many user processes are connected to the scheduling process, sending the client request to a shared server process by the schedule process. The advantage of multi-thread server configuration is to reduce system overhead and increase user number.
The following types of processes are required in this system:
l Network Receiver Process, connect the user process to the scheduled process and a dedicated server process.
l One or more scheduling processes
l One or more shared server processes
The network receiver process waits for the new connection request to determine whether each user process can use the shared server process. If you can use, the receiver process returns the address of the scheduled process to the user process. If the user process requests a dedicated server, the receiver process will create a dedicated server process that connects the user process to the dedicated server process. At least one scheduling process is configured for each network protocol used by the database client and start it.
When the user makes a call, the scheduling process will be placed in the request queue of the SGA and is obtained by the available shared server processes. The shared server process is all the necessary databases to complete each user process. When the server completes the request, return the result to the queue of the scheduling process, and then return the completed request to the user process by the scheduling process.
Shared Server Process: In addition to the shared server process is not the user process specified, the shared server process and the dedicated server process provide the same function, and a shared server process can request any customer requests in the configuration of the multi-cable server. The SGA of a shared server process does not include data related to the user, and its information can be accessed for all shared server process, which only contains stack space, and the process specifies the variable. All information related to session is included in SGA. Each shared server process can access the data space of all sessions, so that any service process can process any sessions. The data space for each session is allocated in the SGA.
Oracle can dynamically adjust the shared server process based on the length of the request queue. The created shared server process will be requested to put the request queue. A user request is called a program interface for a database, for a SQL statement. The entire scheduling process for requesting the queue on the SGA is public. The server process is a new request to check the public request queue. According to the advanced principle, a request is detected from the queue, and then the required call is required to complete the request. The shared server process will respond to the response queue of the scheduled process. Each scheduling process has its own response queue in SGA, and each scheduling process is responsible for returning the completed request to the corresponding user process.
3. Oracle run
1) Oracle's operation using a dedicated service process
Under this configuration, the Oracle running process is as follows:
(1) The database server computer is currently running Oracle (background process).
(2) Run a database application (for user processes) in a customer workstation, such as SQL * Plus. Customer applications use SQL * Net Driver to establish a connection to the server.
(3) The database server computer is currently running the appropriate SQL * NET DRIVER. The receiver process detects the connection request for the customer database application and establishes a private server process for the user process on that machine. (4) The user issues a single SQL statement.
(5) The dedicated server process receives the statement, there are two ways to handle the SQL statement:
l If you include the same SQL statement in a shared pool, the server process can perform the customer's SQL statement using the existing shared SQL area.
l If there is a SQL area in the shared pool contains the same SQL statement, the statement is assigned a new shared SQL area in the shared pool.
In each case, a dedicated SQL area is created in the PGA of the session, and the dedicated server process checks the user's access to the query data.
(6) If necessary, the server process retrieves the data block from the data file, or can use the data block of the buffer storage area stored in the instance SGA.
(7) The server process executes the SQL statement stored in the shared SQL area. The data is first modified in SGA, written by the DBWR process to the disk when most efficient. The LGWR process logs the user's commit request in the online log file.
(8) If the request is successful, the server will send an information through the network. If the request is unsuccessful, the corresponding error message will be sent.
(9) During the whole process, other background processes are running, while paying attention to the conditions that need to intervention. In addition, Oracle manages other matters to prevent competition for the same data between different transactions.
2) Oracle running using multiple clue servers
Under this configuration, the Oracle running process is as follows:
(1) A database server computer runs Oracle configured using multiple clue servers.
(2) Run a database application in a customer workstation (in a user process). The customer applies the appropriate SQL * NET drive attempt to establish the connection to the database server computer.
(3) The database server computer currently runs the appropriate SQL * NET drive, and its network receiver process detects the connection request for the user process and determines how the user process is connected. If the user is using SQL * NET version 2, the network receiver notifies the user process to reconnect using the address of the available scheduling process.
(4) The user issues a single SQL statement
(5) Dispatching Process Places the request queue in the request queue, which is shared for all scheduling processes in SGA.
(6) A available shared server verify the public scheduling process request queue and detects the next SQL statement from the queue. The SQL statement is then processed, with the previous (5), (6), and (7). Note: The dedicated SQL area of the session is built in SGA.
(7) A SQL process is completed when the shared server process is completed, and the process places the result queue that issues the scheduling process of the request.
(8) Scheduling process Check its response queue and send the completed request to the requesting user process.
4. Database Structure and Space Management
An Oracle database is a collection of data and is processed into a unit. An Oracle database has a physical structure and a logical structure.
Physical Database Structure is determined by the operating system file constituting the database. Each Oracle database consists of three types of files: data files, log files, and control files. Database files provide real physical storage for database information. The logical database structure is the database structure involved in the user. The logical structure of an Oracle database is determined by the following factors:
l One or more tablespaces
l Database mode object (ie table, view, index, gathering, sequence, stored procedure)
Logical storage structures such as tablespace, segment (segment) and range will dominate how to use the physical space of a database. The connection between the schema object (Schema Object) has formed a database of a database.
1) Physical structure
(1) Data file
Each Oracle database has one or more physical data files. A database of data files contains all database data. Data logical database structures (such as tables, indexes) are physically stored in a database of data. The data file has the following characteristics:
l A data file is only contact with one database.
l Once established, the data file cannot change the size.
l A table space (logical unit stored in the database) consists of one or more data files.
The data in the data file can be read and stored in the Oracle internal storage area when needed. For example, the user wants to access some of the data of a database, and if the request information is not in the memory store of the database, it is read from the corresponding data file and stored in memory. When modifying and inserting new data, you don't have to write data files immediately. In order to reduce the total number of disk outputs, improve performance, data is stored in memory, and then the Oracle background process DBWR determines how to write it to the appropriate data file.
(2) log file
Each database has two or more groups of log files, each log file group being used to collect database logs. The main function of the log is to record the modifications made to the data, so all modifications for the database are recorded in the log. When a fault occurs, if you cannot write a data file permanently, you can use the log to get the modification, so you will never lose an operational result.
The log file is mainly to protect the database to prevent malfunctions. In order to prevent the fault of the log file itself, Oracle allows the mirrored redo log, so that two or more logs can be maintained on different disks.
The information in the log file is used only when a system failure or a media fault restores the database, which prevents database data from to database data files. However, any lost data is opened when the next database is opened, Oracle automatically applies information in the log file to resume database data files.
(3) Control file
Each Oracle database has a control file (Control file), which records the physical structure of the database, contains the following information type:
l Database name;
l The name and location of database data files and log files;
l Database creation date.
For security, the control file is allowed to be imaged.
Every time the instance of the Oracle database is started, its control file is used to identify the database and log files, which must be turned on when the database is operated. Oracle automatically changes the control file of the database when the physical composition changes of the database. The control file is also used when data is recovered.
2) Logical structure
The database logic structure contains tablespaces, segments, range (extent), blocks, and mode objects.
(1) Table space
A database is divided into one or more logical units, which are called tablespace. A table space can be combined with the relevant logical structure. The DBA can take the following work: l Control the disk assignment of database data.
l Assign the determined spatial share to the database user.
l The availability of data is controlled by causing a single table space online or offline.
l Execute some database backup or recovery operations.
l To improve performance, allocate data storage across the device.
The relationship between database, table space, and data files are shown below:
Database
SYSTEM DATA
TableSpace TableSpace
DTATAFILE1
(2MB) DataFile2
(2MB) DataFile3
(2MB)
Driver1 driver2
. Each database can be logically divided into one or more tablespaces
. Each table space consists of one or more data files, which physically stores data in the table space in the table space. DBA can create new tablespaces, which can add data files for tablespace or delete data files, setting, or change the default segment storage location.
Each Oracle database contains a table space called System, which is created in the database. In this table space, there is always a data dictionary table with the entire database. The smallest database can only need the System tablespace. The table space must always be online. All stored data for tables and stored PL / SQL program units (processes, functions, packages, and flip flops) are stored in the System tablespace. If these PL / SQL objects are built for the database, the DBA needs to plan the space required to plan these objects in the System tablespace.
Table space utilizes increasing data files to expand tablespace, the size of the table space is the size of the data file size that makes up the table.
DBA enables any table space in the Oracle database (except for the SYSTEM tablespace) online or offline. The tablespace is usually online, so that the data it contains is available to the database user. When the table space is offline, its data is not available. In the following cases, DBA can offline it.
. Make some data unavailable, while the remaining parts allow normal access
. Execute offline table space
. To modify or maintain an application, it is temporarily unavailable to a set of tables.
The table space containing a returning segment that is active cannot be offline, which is only when the segment is not in use, the table space is offline.
In the data dictionary, the status of the table space is recorded online. If a table space is offline when the database is closed, then it remains offline after the next database assembly and re-open.
When certain errors appear, a table space can be automatically changed from the online change. Different types of data are separated by using multiple tablespaces, more convenient to manage the database.
A tablespace in the Oracle database consists of one or more physical data files, and a data file can only be contacted with a table space. When establishing a data file for a table space, Oracle establishes this file and assigns the specified disk space capacity. After the data file is established, the assigned disk does not contain any data. The table space can be online or offline. Allow separate data files online or offline in Oracle.
(2) Segment, range and data block
Oracle can control the use of disk space by segment, range, and data block logic data structures.
segment
Segment (segment) contains a logical storage structure of a specified type in the table space, which consists of a set of ranges. There are several types of segments in the Oracle database: data segments, traction segments, rollback segments, and temporal sections.
Data segment: There is a data segment for each non-aggregated table, and all the data of the table is stored in this segment. Each aggregate has a data segment, and the data of each table in the aggregate is stored in this segment. Index section: Each index has an index section, store index data.
Rollback segments: It is created by DBA, which is used to temporarily store information to be revoked, which is used to generate read consistency database information, use when the database is recovered, rollback uncommitted transactions.
Times: When a SQL statement requires a temporary work area, it is created by Oracle. When the statement is executed, the scope of the temporary section is returned to the system.
Oracle assigns space for all segments to range in terms.
range
A range (Extent) is a logical unit assigned by the data storage storage, which is composed of continuous data blocks. Each section consists of one or more scope. Oracle assigns a new range for this section when all spaces are fully used.
For maintenance purposes, each section of the database contains the features of the segment header block, and the range directory in this segment.
data block
Data Block is a unit of storage space in Oracle management data files, minimal units of I / O used in the database, which can be different from the standard I / O block size of the operating system.
Data block format:
Public beam heading
Table catalog
Row catalog
Unused space
Row data
(3) Mode and mode objects
A pattern (Schema) is a collection of mode objects, each of which corresponds to a mode. Mode objects are logical structures that directly reference database data, and the mode objects contain structures such as tables, views, indexes, aggregation, sequences, synonyms, database chains, processes and packages. The mode object is a logical data storage structure, and each mode object does not have a corresponding file on the disk stores its information. A mode object is logically stored in a table space of the database, and each object is physically included in one or more data files of the table space.
table
Table (Table) is the basic unit of data storage in the database, and its data is stored in line. Each table has a table name and column collection. Each column has a column name, data type, width, or precision, proportion. One line is a collection of columns corresponding to a single record.
view
A view (view) is a customized representation of data in one or more tables (or other views). It is defined by a query, so it is considered to be a stored query or a virtual table. Virtual table. The view can be used in many places using the table.
Since the view is exported by the table, the view and tables are much similar, and the view icon table can define the 254 column. The view can be queried, while having certain limits in modification, inserting, or deleting, all operations performed on the view truly affect the data in the basic table of the view, subject to the integrity constraints of the basic table and the limitations of the trigger.
The view is different from the table, and a view does not assign any storage space, and the view does not really contain data. The view defined by the query is corresponding to the data in the view reference table. The view only stores its definitions in the data dictionary.
The introduction view has the following benefits:
. Provide additional security to the table by limiting the access to the table predecessor
. Hide data complexity.
. Simplify command for users
. Provide another view for data for the basic table.
. The modification of the application isolation of the basic table can be used.
. Used for inquiry that cannot be represented without a view.
. Can be used to save complex queries.
Gather
Cluster is an alternative way to store table data. A gather is a set of tables that store rows with the same common column value together, and they are often used together. These public columns constitute aggregation codes. For example, the EMP table share the DEPTNO column, so the EMP table and the DEPT table can be gathered together, and the aggregation code is a deptno column, which gives all the staff of each department to physically store the same line. Data blocks. index
Indexes are a selection structure associated with tables and gatherings. The index is established to improve the performance of the data retrieval, using it quickly determines the specified information. Oracle Index provides a quick access path for table data. The index applies to a range of row queries or queries for specified rows.
An index can be built on a list or multiple columns of a table, once established, automatic maintenance and use by Oracle, is completely transparent to the user. Indexes are logically and physically independent of data, their establishment or deletion has no effect, and the application can continue to process. The retrieval performance of index data is almost maintaining constants, and when there are many indexes on a table, the performance of modification, deletion, and insertion operation will fall.
Indexes have a unique index of each index. The unique index ensures that there is no two lines in the table with repetition values on the column defined index. Oracle automatically defines unique index implementing UNIQUE integrity constraints on a unique code.
The combined index is an index established on a list of the table. Group full index can accelerate the search speed of the SELECT statement, which can reference all or the main part of the combined index in its WHERE clause. Therefore, in the order given in the definition, you will be placed frequently or selecting the most columns in the first place.
When establishing an index, the use of the use of the index segment, the index segment space allocation, and the reserved space will be automatically established in the table.
The memory parameter control of the allocation of the index segment range.
The unused space in its data block can be controlled by the PCTFree parameter setting of this segment.
Sequence builder
Sequence generator generates a serial number. In a multi-user environment, the sequence generator is particularly useful, and each return serial number is generated without the need for disk I / O or transaction blockade.
The serial number is an Oracle integer, up to 38 numbers. One sequence definition points to general information: the name, rising or drop, the spacing between the serial number and other information. All sequence definitions are always available in the data dictionary tables stored in the System tablespace in the SYSTEM table space. Use the serial number using the SQL statement of the reference serial number to generate a new serial number or use the current serial number. Once a serial number is generated in the SQL statement in the user session, the serial number is only available for this session. The serial number generation is independent of the table, so the same sequence generator can be used for one and more tables. The generated serial number can be used to generate a unique master code.
Synonym
A synonym (Synym) is any table, view, snapshot, sequence, process, function or package alias, which defines the stored in the data dictionary. Synonyms are often used by security and convenient reasons, available for:
l The name of the maskable object and its holder.
l Provides location transparency for remote objects of distributed databases.
l Simplify the SQL statement for the user.
There are two synonyms: public and dedicated. A common synonym is held in a special user group named Public Special User Group, which can be accessed by each user in the database. A dedicated synonym is included in the specified user's mode, only for the user and the authorized user.
Mixture
Hashing is a selectable method for storing table data to improve data retrieval. To use a mixture, you will build a mixture and put the table to the gather. The table line in the gathering aggregate is physically stored and retrieved according to the result of the mixture function. The mixed function is used to generate a value of a value, which is called a mixed value, which is based on the specified aggregation code value.
Program unit
Program Unit refers to a stored procedure, function, and package. A process and function is combined by the SQL statement and the PL / SQL statement to perform an executable unit of a certain task. A process or function can be established, store its compilation form in the database, can be executed by the user or database application. The process and function differences are returned to the caller in the function, and the process does not return to the caller. The package provides a related process, functions, variables, and other package structures, one way to store together, allowing managers and application developers to organize such programs (ROUTINE) to provide more features and Improve performance.
Database chain
The database chain is a named object that describes a path (PATH) from a database to another database. In a distributed database, the database chain is implicitly used when referenced to the global object name.
Third, the database and instance startup and close
An Oracle database is not necessary to always be available to all users, and the database administrator can boot the database so that it is opened. In the database open, the user can access the information in the database. When the database is not in use, the DBA can turn off it, the database is closed, and the user cannot access its information.
The database startup and shutdown is a very important management function, protected by the ability to connect to Oracle in INTERNAL. It requires the following prerequisites to connect to Oracle in Internal:
The user's operating system account has an operating system privilege to use an Internal.
There is a password for the Internal database that knows its password.
In addition: When the user is connected in an Internal, it can be connected to a private server and is a secure connection.
1. Database start
Start the database and make it available in three steps:
l Start an instance;
l Assembly database
l Open the database
1) Start an example
The process of launching an instance contains the establishment of an SGA (memory shared area used by database information) and background process. The execution of the instance starts a database before this instance. If you only start an instance, no database is associated with the internal storage structure and process.
2) Assemble a database
Assembly databases are associated with a database with the started instance. When an instance is installed a database, the database remains off, only DBA is accessible.
3) Open a database
Opening a database is to make the database can process the normal database operation. When a database opens all users can connect to the database to access its information. When the database is open, the online data file and the online log file are also opened. If a table space is off when the last database is closed, the table space is not offline when the database is turned on again.
2. Close of database and instance
Close an instance and the database it is connected is also three steps:
1) Turn off the database
The first step in the database stop is to turn off the database. When the database is turned off, all database data and recovery data in SGA are written to the data files and log files. After this, all online data files and online log files are also closed, and the data folder in any offline tablespace is closed. The control file remains open after the database is closed but is also installed.
2) Remove the database
The second step of the stop database is to remove the database from an instance. After the database is removed, only instances are retained in the computer memory. After the database is unloaded, the database control file is also closed.
3) Stop instance
The last step of the stop database is to stop instance. When the example is stopped, the SAG is revoked from memory, and the background process is aborted. 3. Initialization parameter file
When starting an instance, Oracle must read an initialization parameter file (Initialization Parameter File), which is a text file that contains an instance configuration parameter. These parameters set a special value for many memory and process settings for initial Oracle instances, which contains:
l A database name started by an instance
l How much memory is used in the SGA storage structure;
l What is done after filling the online log file;
l The name and location of the database control file;
l The name of the dedicated rollback segment in the database.
Fourth, the use of data dictionary
The data dictionary is one of the most important parts of the Oracle database, which is composed of a list of read-only tables and their views. It provides information about the database, and the information available is as follows:
l The name of the Oracle user;
l A privilege and role of each user;
l The name of the pattern object (table, view, snapshot, index, aggregation, synonym, sequence, process, function, package and trigger, etc.);
l Information about integrity constraints;
L column default;
l Spatial distribution and current usage of objects in the database;
l Auditing information (such as accessing or modifying various objects);
l Other general database information.
Available in SQL access data dictionary, because the data dictionary is read-only, allowing queries.
1. Data dictionary structure
Database data dictionary is composed of a base table and a user accessible view.
Basic Table: The basis of the data dictionary is a set of basic tables consisting, store information about the related database. This information is only read and written by Oracle, which is rarely accessed directly by Oracle users.
User Access View: The Data Dictionary contains information of the user accessible view, which can generally facilitate display information of the basic table of the data dictionary. The view decodes the information in the base table into available information.
2. Data dictionary
When the database is open, the data dictionary is always available, which resides in the System tablespace. The data dictionary contains view sets, in many cases, each view set has three views containing similar information, distinguishes with each other, prefix User, All, and DBA.
l Prefix the view for User, for use, is in the user's mode.
l Prefix the view for all, for extended user views (view accessible for users).
l Prefix the view of the DBA as a view of the DBA (view that can be accessed by all users).
In the database Oracle also maintains a set of virtual tables to record the current database, these tables are called dynamic performance tables. Dynamic performance tables are not true tables, many users cannot access, DBA can query these tables, can establish views to grant other users to take views.
Five, transaction management
1. Transaction
A transaction is a logical unit that consists of one or more SQL statements. A transaction is an atomic unit that makes all SQL statements of the transaction can be all submitted or all rollback. A transaction begins with the first executable SQL statement to submit or rollback, which can be explicit, but also implicit (executable DDL statements).
When an error occurs when an SQL statement occurs, the statement is rolled back, it seems that the statement is not executed, but it does not cause loss of the current work of the current transaction. 2. Oracle's transaction management
One transaction in Oracle starts by an executable SQL statement, one executable SQL statement generates calls to instances. At the beginning of the transaction, it is assigned to a rollback to record the transaction. Anything in the following occurs after any one appears.
l When a commit or rollback (no SavePoint clause) statement is issued.
l A DDL statement is executed. Before the DDL statement is executed, it is implicitly submitted.
l Users to undo their connection to Oracle (current transaction submit).
l User process abnormally (current transaction rollback).
1) Submit a transaction
Submit a transaction, will be permanently permanently executed by the SQL statement in the transaction. Oracle has the following cases before committing:
l The rollback segment buffer in the SGA has generated the rollback segment record, and the rollback information contains the old value of the modified value.
l The log entries are generated in the log buffer in the SGA. These changes can enter the disk before transaction.
l The database buffer for the SGA has been modified, and these modifications can enter the disk before the transaction is actually submitted.
After the transaction is submitted, there is the following situation:
l Submit transactions for internal transactions related to rollback segments, and assign a corresponding unique system modification number (SCN), recorded in the table.
l In the log buffer in the SGA, log items are written to the online log file by the LGWR process, which is an atomic transaction that constitutes a transaction.
l The blockade on the line and the table is released.
l This transaction logo is completed.
Note: Data modifications to the transaction do not have to be written to the data file immediately by the DBWR background process, continue to be stored in the SGA database buffer, and write it to the data file when most efficient.
2) Rollback transaction
The meaning of the rollback transaction is to revoke the data modifications to the SQL statement in the uncommitted transaction. Oralce allows undefused transactions, and the part is allowed to be revoked.
When rolling over the entire transaction (no reference retention point), there is the following situation:
l All modifications for all SQL statements in the transaction, using the corresponding rollback segment being undo.
l The transaction blockade of all data is released.
l The transaction end.
When the transaction rolls back to a reserved point (with savepoint), there is the following cases:
l The statement that only executed after this reservation point is revoked.
l This designated reserved point is still retained, and the reserved point established after this reserved point is deleted.
l The full table blockade and line blockade acquired since the reserved point, but the specified reserved point is previously acquired.
l The transaction can continue.
3) Reserved point
The SavePoint is an intermediate flag within a transaction, often divided into a small part of a long transaction. Retention point can be marked in any point, allowing you to roll back to the work. Reserved points are often used in the application; for example, a process contains several functions, and a reserved point can be created before each function. If the function fails, it is easy to return to the beginning of each function. After returning to a reserve point, the data blocked after the holding point is released.
6. Database trigger
1. Trigger introduction
Database triggers are the procedure stored in the database, which is implicit (execution) when the table is modified. The defined procedures are permitted when INSERT, UPDATE, or DELETE statements are issued in Oracle, which are called database triggers. The trigger is stored in the database and stored with the relevant table. The trigger can only be defined on the table. In many cases, the trigger is used to provide a very advanced dedicated database management system to supplement Oracle's standard features. Triggers are generally used in: l automatically generates the exported column values;
l Prevent invalid transactions;
l Implement a more complex security check
l Implement the reference integrity across the node in the distributed database;
l Implement complex transaction rules;
l Provide transparent event logs;
l Provides advanced audits;
l Maintenance synchronization table replication;
l Collect statistics on access tables.
Note: Differences between database triggers and SQL * Forms triggers. The database trigger is defined on the table and is stored in the database, which is excited when the INSERT, UPDATE or DELETE statement is executed, no matter who is issued. The SQL * Forms trigger is part of the SQL * Form application, which is only excited when a specified trigger point is executed in the specified SQL * Forms application.
Triggers and illustrative integrity constraints can be used to constrain the input of data, but there is a certain difference between them:
Illustrative integrity constraints is about the database that is always "true". An integrity constraint is applied to any statement in the table existing data and operating tables.
The trigger constraint transaction is not applicable to data that has been loaded before the trigger, so it does not guarantee all the rules of the trigger from the trigger in the table. The trigger performs instantaneous constraints, that is, a constraint is implemented when data changes.
2. The consisting of triggers:
A trigger has three basic components: trigger events or statements, limitations, triggers, and triggers.
Trigger events or statements: To cause SQL statements that cause triggers excited, it is the specified table INSERT, UPDATE or DELETE statement.
Trigger Limit: For a bully expression, the condition must be true when the trigger is excited. The trigger limit is specified with the WHEN clause.
The action of the trigger: It consists of a PL / SQL block (process), consists of SQL statements and PL / SQL statements. When the trigger statement is sent, it is executed when the trigger is limited. In the statement of the trigger action, the current line of the current row (new value, old value) of the process of the trigger can be used, and the usage form is:
NEW. Column name reference new value
Ole. Column name references the old value
When defining a trigger, you can specify a trigger action execution number: The affected statement affects each line or executes once for the trigger statement.
There are four types of triggers for each trigger statement:
Row Trigger: Each row affected by the trigger statement, row trigger inspired once.
Statement trigger: This type of trigger executes once the trigger statement, regardless of its affected line.
Defining the trigger can specify the trigger time, specifying the execution of the exciter action to execute or before execution of the trigger statement.
Before trigger: The trigger performs trigger actions before the trigger statement is executed.
AFTER Trigger: The trigger performs trigger actions after the trigger statement is executed.
A trigger can be in two different ways: enable triggers and can't trigger.
Enable trigger: As long as the trigger limit is calculated as true as the trigger statement is issued, this type of trigger performs its trigger.
Make the trigger: This trigger even if its trigger statement is issued, the trigger limit is calculated as TRUE, nor does the trigger action. The source code of the trigger is stored in the database, and when executed, the source code of the flip-flop is compiled, stored in the shared pool. If the trigger squeezes from the shared pool, you must recompile if you reuse.
Seven, distributed processing and distributed database
1. Introduction
A distributed database is a single logical database in front of the user, but it is actually consisting of a set of databases stored on multiple computers. Databases on several computers can be modified and accessed through the network, each database being controlled by its local DBMS. The distribution of each database server in the distributed database maintains the consistency of the global database.
Each computer in the system is called a node. If a node has a management database software, the node is called a database server. If a node is an application of the request server information, the node is called a customer. In Oracle customers, execute database applications, data information and interaction with users. In the server, execute Oracle software, handle concurrently, share data access to the Oracle database. Oracle allows the above two parts to be on the same computer, but when the client part and the server part are more effective, it is more effective.
Distribution processing is a single task to divide a single task by multiple processing machines. Examples of distribution processing in the Oracle database system, such as:
Customers and servers are on different computers located in the network connection.
There are multiple processors on a single computer, and the different processors perform customer application separately.
SQL * NET is an Oracle network interface that allows you to run on the Oracle tools and servers running on the Network Workstation, accessible, modified, shared, and data stored on other servers. SAQL * NET can be considered a program interface for network communication. SQL * NET provides a distributed database and distribution processing for OARCLE using the Communication Protocol and Application Interface (API).
The SQL * NET drive provides an interface between the Oracle process running on the database server and the user process of the Oracle tool.
Each server participating in the distributed database is independently managed separately, so that each database is not a network database. Each database is managed independently, called venue autonomousness. Site autonomous property has the following benefits:
l The node of the system reflects the company's logical organization.
l Control local data by local database administrators so that every database administrator's responsibility domain is small, but
Good management.
l As long as a database and network is available, the global database can be partially available. Will not be due to a database
Stop all operations or cause bottlenecks.
l Troubleshooting is usually performed on a single node.
l Each local data inventory in a data dictionary.
l Node can be upgraded separately.
The mode object can be accessed from all node of the distributed database, so that the local DBMS that is as non-distribution must provide a mechanism, an object can be referenced in the local database. Distributed DBMS must provide a naming mode that a object in the distributed database can be uniquely identified and referenced in the application. The general color is unique in each layer of the hierarchy. Distributed DVMS simply expands hierarchy models, implemented on the network unique database name. Therefore, an object's global object name is guaranteed to be unique in a distributed database.
Oracle allows you to use a mode object in a distributed database in a SQL statement (table, view, and procedure). In Oracle, a global name of a pattern object consists of three parts: including the model name, object name, database name, the form:
Scott.emp@sales.diVision3.acme.com
SCOTT is the model name, EMP is a table name, and the @ symbol is the database name.
A remote query is a query, selecting information from one or more remote tables, which resides in the same remote node.
A distributed query can retrieve data from two or more nodes. A distributed update can modify two or more data. A remote transaction is a transaction, contains one or more remote statements, it All of the references are on the same remote node. One transaction in a distributed transaction contains one or more statements to modify two or more different nodes of the distributed database.
In a distributed database, transactional control must mart municipalities directly under the network to ensure data consistency. Two-stage submission mechanism guarantees that all database servers participating in distributed transactions are all submitted or all rollback transactions.
Oracle Distributed Database System Structure The Oracle Database Administrator provides location transparency for end users and applications, using views, synonyms, and procedures to provide location transparency in the Oracle distributed database system.
Oracle allows remote data in SELECT, INSERT, UPDATE, DELETE, SELECT ... for Update, and Lock Table statements. For queries, including connectivity, aggregate, subquery, and Select ... for Update, you can reference locally, remote tables, and views. For Update, Insert, DELETE, and LOCK TABLE statements, local and remote tables can be referenced. Note You must be on the same node when referenced by the LONG and long RAW columns, sequences, modify the table, and blocking tables. Oracle does not allow remote DDL statements.
All transactions are aborted with a commist or rollback statement in a single-site or distributed database. Oracle provides two mechanisms to achieve transparency of repetition in distributed databases: table snapshot provides asynchronous table duplicate; trigger Repeat of synchronization table. In both cases, transparency of repetition of tables is realized.
2. Distributed database global name and database chain
1) Distributed Database Global Name: Each database has a unique global name, composed of two parts: database name (less than or equal to 8 characters) and network domain. The network domain component of the global database name must obey the standard Internet specification. The hierarchy in the domain name is separated. "Separate, the order of the domain name is from the left to the right.
2) Database Chain: The path defined for the process database. The database chain is transparent to the user of the distributed database, the name of the database chain is the same as the global name of the database pointed to by the chain. It consists of two parts: a remote account and a database string. Cases the form of a database chain:
Creat Public Datebase Link Sale. DiVision3. ACME. COM
Connect to Guest Identified by Password
Using'DB string ';
Where: Sales. Divisin3. ACME. COM-defined chain name; guest / password is a user account and password for a remote database; DB string is used to remotely. The full path is formed by the account and DB string. If only one is partially path.
There are three database chains that can be used to determine the reference to all object names:
Special Database Chain: Established for a designated user. The owner of the dedicated database chain is only available. The SQL statement is used to specify a global object name or in the holder's view process definition.
Public Database Chain: Established for Special User Groups PUBLIC. The public database chain can be used for any user, in the SQL statement to specify a global object name or object definition.
Network Data Chain: Establish and manage the network domain server, use any user of any database in the network to specify the global object name or object definition in the SQL statement. Note: The current network domain server is not available to Oracle, so the network database chain is not available.
3. Table snapshot
Oracle's table snapshot feature allows a primary table to replicate other nodes of the distributed database. Only the primary table is allowed, and only readable. The main expression is called a snapshot. Snapshot asynchronous refresh, reflecting a recent transaction of the primary table.
A snapshot can be a complete copy of the table or a subset of the table, defined by a distributed query that references one or more primary tables, views, or other snapshots. The database containing the primary table is called a primary database. Snapshots have simple snapshots and complex snapshots. Each row of simple snapshots is based on a single line in a single remote table. So define a simple snapshot inquiry, you can't have a Groub by or a Connect By clause, or subquery, connection, or collection operations. If the above clause or operation is included in the query defined in the snapshot, this snapshot is called a complex snapshot.
When the snapshot is established, Oracle creates several internal objects in the snapshot mode:
In the snapshot node, Oracle creates a base table for storing rows that are retrieved by the snapshot definition, then build a read-only view for the table and create a view for the remote home table, which is used for new snapshots.
A snapshot period is refreshed and reflects the current situation of its primary table. In order to refresh a quick photo, the snapshot definition query is issued, and its query is desired to replace the previous snapshot data in the store.
When a snapshot is a simple snapshot, you can refresh by the snapshot log, so you can speed up the refresh process. The snapshot log is a table in the primary table database and is related to the primary table. Oracle uses the snapshot log to track the modified rows in the primary table. When a simple snapshot refresh of the primary table, only the corresponding row of the snapshot log is required to refresh the snapshot, which is called fast refresh.
