Board Deng: The structure of the database

As mentioned earlier, the database server consists of two parts: instance and database.

Where the database is used to store data, and the instance is used to access the data in the database.
The instance includes a set of memory structures and background processes, and the structure of the database needs to be understood from two aspects of logical structure and physical structure .
The logical structure of a database refers to the logical organization of data, which is the mechanism used to manage data within Oracle.
The physical structure of a database is a structure perceived from the user's perspective, and a mechanism for storing and managing data in the operating system.
Logically, a database contains several table spaces, and different types of data are stored in different table spaces, such as system data, user data, temporary data, and rollback data, respectively, in separate table spaces.
A table space contains several segments, and data in the same tablespace can be further stored in different segments depending on the type, such as data segments, index segments, temporary segments, and so on.

A segment can also contain several extents, which are the basic unit for Oracle to allocate storage space and reclaim storage space.
Zones are composed of contiguous blocks of data that are the basic unit of Oracle's read-write database.

table spaces, segments, extents, and data blocks make up the logical structure of the database .
Logical structure refers to the organization of data, and from the physical form, the data is stored in the form of data files on disk.
In the operating system, the user can really feel that the database exists in the data file.
The data file is stored on disk and requires a number of operating system blocks.

Logical structure and physical structure are not irrelevant, but closely related.
The data in the tablespace is stored in a data file, so a tablespace corresponds to one or more data files.
Data blocks are the basic unit of data stored in Oracle, a block of data that corresponds to several operating system blocks, the data is ultimately stored in the operating system block, and Oracle's access to the block of data is ultimately translated into Access to the operating system block.
The relationship between the logical structure of the database and the physical structure is as follows:

Table Space

Table space is the logical organization of data in a database, and a database is logically composed of multiple table spaces.

Table spaces are used to organize different types of data together, just as employees in a company are organized according to different departments.
The data in a tablespace is physically stored in a data file, and one table space corresponds to one or more data files, just as a department corresponds to several offices.
When storage space in a tablespace is tense, you can add data files to a table space , and a data file can belong to only one table space .
If the tablespace contains only one data file, all data is stored in the data file.

If the tablespace corresponds to more than one data file, the data is divided into parts, which are stored in these data files.
The relationship between the tablespace and the data file.

The benefits of introducing table spaces into a database include the following:
• Separating system data from user data helps protect important data.

• You can limit the user's use of disk storage space.
• Separate temporary data from user data to reduce fragmentation of user data stores and improve database performance.
• Ability to store different types of data on separate disks to reduce disk read-write conflicts.

You can store frequently accessed data on relatively fast disks, improving the performance of your database as a whole.
• Individual table spaces can be individually set to online or offline, allowing a single table space to be taken offline and backed up or restored in the event that the database is functioning properly.
There are five types of table spaces in a database, the system tablespace, the Sysaux table space, the undo tablespace, the temporary tablespace, and the normal table space.
The first four table spaces are essential, and you need to create them when you create the database, and the normal tablespace is created as needed.

1. system table Space

The system tablespace is a required table space in the database.

When you create a database, the system table space is automatically created.
In the system table space, information about the database is stored, such as the data dictionary, the definition of the database object, the code of the PL/SQL Store program, the system rollback segment, and so on.

2. Sysaux table Space

The Sysaux table space is also a required tablespace in the database, which is created automatically when the database is created .
The Sysaux table space is a secondary tablespace to the system table space, and data that was previously stored in the system table space is now stored in the Sysaux table space, reducing the burden on the system table space.
In addition, many of the data that previously required a separate tablespace can now be stored in the Sysaux table space, reducing the number of table spaces that need to be maintained.

3. Undo Table Space

The Undo table space is used to store rollback data .

Rollback data is data that is modified by transactions, for example, assuming that the user executes the statement "DELETE from EMP WHERE empno=7902".
The data accessed by the Delete command is the rollback data.
This row of data is stored in the Undo table space when the transaction has not yet been committed.

Assuming another user executes the statement "SELECT * from emp WHERE empno=7902", then he will get the row of data returned from the Undo table space .
If the transaction is rolled back, the data in the undo Tablespace is written back to the original storage space as if the original DML operation was not executed.
This shows that theundo table space is designed to roll back the transaction .

In previous versions of the database, rollback data could only be stored in a rollback segment.
The rollback field is in a table space, or in some particular table space.

The management of rollback segments is complex and requires the database administrator to manually execute cumbersome commands.
The current version of the database uses the Undo table space to manage rollback data.
Only rollback segments can be stored in the Undo table space, not other types of segments, such as data segments, index segments, and so on.
The advantage of using the undo tablespace is that the rollback data is automatically managed , reducing the burden on the database administrator.

Temporary table space

Temporary table spaces are used to hold temporary data that is generated when a user accesses a database.

For example, when the user executes the statement "SELECT * from emp ORDER by Empno;" , the data in the table is sorted and the sort results are produced.
The sort operation is typically done in the PGA's sort area.

If the sorting area is not large enough to hold the data, the temporary table space is used.
Only temporary segments can be created in a temporary tablespace.

Temporary segments are not permanent, and when the user first performs a sort operation in the database, the temporary segment is automatically generated, and the space of the temporary segment is freed when the database is closed.
It is for this reason that the temporary tablespace does not allow the creation of persistent database objects , such as tables, indexes, and so on.

You can create more than one temporary table space in a database.

Without a temporary tablespace, users may need to use the system table space to store temporary data when performing operations such as sorting.
If temporary data is stored frequently in the system table space, a large amount of storage fragmentation is generated, reducing the performance of the database.
When creating a user using the Create User command, you can specify a temporary tablespace for that user through the temporary tablespace clause.
After the user has created it, you can also specify a temporary tablespace for it by using the ALTER USER command.
The temporary data that is generated when the user accesses the database is stored in the specified temporary table space.

Normal table Space

The normal tablespace is a table space that the user really cares about and can create more than one normal table space in the database.

The normal table space is used to hold the user's data.

Paragraph

A tablespace separates data by type logically , such as organizing user data and system data in a single table space.
in the same table space, there may be different types of database objects , such as tables, indexes.

Oracle organizes the data in different database objects together as segments.
A table space contains multiple segments, but a segment can belong to only one table space.
When a user creates a database object in the database, a segment is automatically created in the Tablespace to store the object's data.
For example, by default, a table corresponds to a table segment and an index segment for one index.

The allocation of storage space in a segment is in the area.
Contains a number of extents in a segment.

You need to specify the initial number of extents when assigning segments.
As the data in the segment increases, the data server expands the segment to allocate the required extents for the segment, and when the data in the segment is deleted, the free area can be reclaimed.
There are four main types of segments that can be created in a table space, which are data segments, index segments, temporary segments, and rollback segments.
Store different database objects in each segment.
Data segments are used to hold data in a table, and by default one table corresponds to a table segment.

you can store data in only one table in a table segment .
When a user creates a table in a tablespace, the database server automatically creates a segment in the table space for the table with the same name as the table.
The index segment is used to store the data in the index, and when the user creates an index for a table, the data server automatically creates an index segment for the index that is the same as the index, and that the index segments are one by one corresponding to each other.
When you create a PRIMARY KEY constraint or a uniqueness constraint on a table, the corresponding index segment is also generated.
Temporary segments are used to hold temporary data, and when users perform operations such as sorting, a large amount of temporary data is generated that is stored in temporary segments.
Of course, temporary data is preferentially stored in the PGA's sort area, which can increase the speed of sorting .
If the sorting area is not large enough to hold the temporary data, the temporary segment is used.

Temporary segments are not required, and if no dedicated temporary segments are created, the user's sort operation uses the temporary segments in the system table space.
Because the system table space stores important systems data, the frequent use of systematic tablespace yields a lot of fragmentation, which reduces the performance of the database, so Oracle recommends creating a dedicated temporary segment as much as possible and storing it in a dedicated temporary table space.

The rollback segment is used to store rollback data.

When a user executes a DML statement, the database server stores the modified data in the table segment, and the pre-modified data is stored in the rollback segment as the rollback data.
When the user rolls back the transaction, the database server re-writes the data in the rollback segment to the table segment, and the changes made by the firm are canceled .
When a user commits a transaction, the data in the rollback segment becomes invalid, and the user cannot roll back the transaction .

Multiple rollback segments can be created in the database.

When a user performs a DML operation, the database server automatically assigns a rollback segment to the current transaction, and the user can also specify a rollback segment from the command.
When you create a database, a system rollback segment is automatically created in the systems table space, and the systematic rollback segment is used to maintain internal oracle transactions.
The database administrator can create additional rollback segments from the command.
It is important to note that Oracle 11g provides two ways to manage rollback data, one of which is manual management, which side using rollback segments to maintain transactions.
Another side note, called Automatic Management, uses dedicated undo tablespace management to roll back data.
Because the management of the rollback segment is too complex, Oracle recommends that you use automatic management, which may be removed from manual management in later versions of Oracle.

Area

Area is the smallest unit of Oracle's allocated storage space, one segment consists of several contiguous blocks of data, and the size of the extents is an integer multiple of the block size .
When you create a database object, the database server allocates several extents to the object to store the object's data.
Database objects occupy at least one zone, and as data increases, the database server will continuously allocate the required extents for that object, which may or may not be equal in size .

Data block

The data block is the smallest unit of storage in Oracle and the smallest logical unit of data read and written by the database server.

When a database server allocates space for a segment or reclaims storage space, it is in a zone, and when it reads and writes data, it is in chunks.
The data in the database is finally stored on the hard disk, so the data block must be closely related to the block in the operating system.
A block of data consists of a number of operating system blocks, which are an integer multiple of the operating system block.

The database server reads and writes data in chunks, which ultimately translates to read and write to the operating system block.
In Oracle 11g, there are two forms of data blocks, one is a standard block and the other is a non-standard block.

The size of the standard block is specified by the initialization parameter db_block_size, and all standard blocks are of the same size.

The size of non-standard blocks can be in a variety of situations, such as 2KB, 4KB, 8KB, 16KB, 32KB (but not the same size as the standard block), and so on.
A series of initialization parameter db_nk_cache_size can be used in the database, where n is 2, 4, 8, 16, 32, and so on.
These initialization parameter families are used to specify the database cache size for non-standard blocks.
If a non-standard block is defined in the database, the appropriate database cache must be defined for it in the SGA.
Regardless of the data block, its size cannot be modified after the database is created.
When the database server reads and writes data, the data in the data block is first called into the database cache of the SGA, and buffers must be defined for each size of data block in the cache, and the buffer size is the same as the data block.
When a user accesses a database, the contents of the data block are read and written to the same size buffer.

The physical structure of the database

The data in the database is logically structured in the form of table space, segments, extents, blocks of data, and is physically represented as files stored on disk .
The physical structure of the database refers to how the data is stored in the operating system and is a visible form of organization to the user.
The physical structure of the database includes data files, control files, and redo log files, which are required for the database to function properly. three files.

In addition, the database includes password files, parameter files, warning files, and tracking text .

Data files

As the name implies, data files are used to store data .

Stores the structure and data of all database objects in a data file, including database objects such as tables, views, indexes, triggers, stored programs, and so on.
User access to the database is essentially access to the data file, except that the files cannot be accessed directly by the operating system and must be accessed through the database server.
The data file differs from the normal file.

The initial size of the general operating system file may be smaller, and as the content in the file increases, so does its size, and its size will decrease as the content decreases, and at any time its size is consistent with its content.
The data file is more like an empty container, and when the data file is created, the storage space is allocated according to the specified size.
The size of a file is always fixed, no matter how much data it contains, before it is fully written.
Since the data file is an operating system file, it must occupy a certain number of operating system blocks on disk.

Within the database server, the basic unit of data read and write is a block of data that corresponds to multiple operating system blocks.
When a data block is read and written in the database, the operating system corresponds to read and write to multiple operating system blocks.
A data file is logically a tablespace, and a table space can contain one or more data files, and a data file can belong to only one table space.
When a user creates a database object, it can specify only the table space to which it belongs, not the data file to which it is stored.
Once a database object is created, its structure and data are stored in one or more data files.
As the database runs, the data in the data file may become more and more, and eventually the storage space of the data file is exhausted.
In order to store more data, the storage space of the data file must be extensible.

Oracle provides three ways to extend the data file storage space.
The first method is to add a new data file to the current tablespace, and the new data will be stored in the new data file.
The second method is to manually extend the data files in the table space, on the basis of the original storage space to increase a certain amount of storage space.
The third method is to activate the automatic extension of the data file, and the database server will automatically allocate new storage space for the data file.

Control files

The control file is another important file in the database, its function is to record the structure and state of the database .

This is a binary file that the user cannot view and modify the contents of the file.
The database needs to look up data files and open them according to the contents of the control files at startup.
Any modifications made to the database structure are recorded in the control file during the database run.
When you start the database server, you start the instance before you can open the database.

The database server establishes a correspondence between the instance and the database by controlling the file.
The path of the data file, the path of the redo log file, the current log sequence number, the SCN, and so on are recorded in the control file.
If the control file is missing or corrupted, the database server will not function correctly.

Due to the special importance of the control file, there are special requirements for its storage.
At least one control file is required in a database, Oracle recommends creating at least two control files and storing them on two disks, two files mirroring each other , and if one file is corrupted, the database server can use a different file.

Under normal circumstances, the contents of these two files are completely-like, the database server only needs to read the information from one of the files, but any modifications made to the database structure must be written to both control files.

Redo log Files

Redo log files is an important means to ensure the security of the database.

Records in the Redo log file are changes made by the user to the database, which are the DML and DDL commands.
When a database server fails, the database administrator can recover the data based on the contents of the redo log, ensuring that the data is not lost due to a failure.
When a user performs a DML or DDL operation, the actual data processing is performed in the SGA.

The server process first generates a redo log and stores it in the redo log buffer, and then modifies the corresponding buffer in the database cache.
At some time, the DBWR process writes the contents of the dirty buffers in the database cache to the data file, LGWR writes the contents of the redo log buffer to the redo log file.
In general, LGWR always writes redo logs to the redo log file before the DBWR process.
Not only redo logs are recorded in the Redo log file, but the SCN is also logged.

If a user commits a transaction, the SCN is written to the redo log file along with the redo log along with the LGWR process.
This way, if the database server fails, the database administrator can restore the database to the last SCN.
If the user does not commit the transaction, the redo log may also be written to the redo log file, but if the transaction is rolled back, it will have no effect on the database.
At least two redo log files are required in the database, and the database server writes the redo logs to these files in a circular manner.
When the first file is full, the database server automatically logs the switch and writes the redo log to the next file.
When the last file is full, the database server re-writes the redo log to the first file.

In this cycle, the previous contents of the Redo log file are overwritten.
Because of the special importance of redo log files, Oracle recommends that at least one image file be created for each redo file .
The redo log files that are mirrored are categorized as one log group , and the contents and sizes of all the files in the same log group are identical .
These files should be distributed as far as possible on different disks, so that all redo logs are lost when the disk fails.

When the database server is writing redo logs, the redo log must be written to all files of the same log group at the same time.
In order to ensure the security of redo logs, the redo log files should be archived in time to generate archived log files .

Archived log files can be backed up to storage media such as disks.
The archive operation must be done in the archive mode of the database, and one or more arch processes need to be started.
Before the LGWR process writes a redo file to a redo log file, the file must be archived, otherwise the LGWR process will be suspended and all transactions stopped.

Trace files and Warning files

When the database server is unhealthy, some useful information is generated in the warning and trace files , and the database administrator can view the contents of the files and determine the cause of the failure, based on the information recorded in it.
Trace files are used to record internal error messages that occur with server processes and background processes, and each server process and background process has its own trace files.
the trace file name for the background process is , where the SID is the instance name and process is the background process name .
For example, the trace file for the LGWR process in instance test is named "TESTLGWR." TRC ".

The trace file name of the server process is called _ora. TRC , where the SID is the instance name, and the PID is the process number of the server process .
Depending on the file name, the database administrator can determine which process the trace file was generated from.

warning files are used to record error messages inside an instance and the database administrator's maintenance of the database, and non-default initialization parameters are also logged in the warning file .
the name of the warning file is Alert_ <sid>. LOG , where the SID is the instance name.
Trace files and warning files have default storage paths, and their paths are specified by the initialization parameters.

The initialization parameter user_dump_dest specifies the storage path of the server process trace file, and the initialization parameter background_dump_dest specifies the storage path of the background process trace file and the warning file.

Privileged user and password files

When the database was created, a special user Sys was created automatically, which is what we normally call a database administrator, with two special system permissions SYSDBA and Sy soper , with all the permissions for the entire database .
The management of the database is basically done with this user's identity.
SYS users need to specify SYSDBA or Sysoper permissions when they log on to an instance.

For example, log in by Sql*plus as follows:

Sqlplus sys/123 as Sysdba

Or

Sqlplus sys/123 as Sysoper

where "1234" is the user's password for s y S.

Before you log in, you need to set an environment variable ORACLE_SID in your system.
In Windows, you need to set this variable only if you are running multiple instances in a system.

The command to set environment variables in Unix/linux is (where ORCL is the name of the instance):

Oracle_sid=orcl

EXPORT Oracle_sid

The commands for setting environment variables in Windows are:

SET ORACLE_SID=ORCL

Before installing Oracle software in Unix/linux, you need to create an Oracle user in your system that belongs to the DBA user group (which is typically a system administrator administrator in Windows, belonging to the ORA_DBA user group ), all subsequent administrative tasks to the database are performed with the Oracle user logged on to the system.
As long as the Oracle user logs on to the system, it is possible to log in to the DB instance in the following way without supplying the SYS user's password:

Sqlplus/as SYSDBA

This is the fact that the Oracle user in the system is mapped to the SYS user in the database.

This type of authentication is referred to as operating system validation in Oracle .
In fact, any user in the operating system, as long as they belong to the DBA user group, can log on to the DB instance in the above way.
Similarly, any system user who belongs to the Oper user group (or the DBA user group) can log in to the DB instance as follows:

Sqlplus/as Sysoper

If the SYS user logs on to the instance remotely, such as through EM, a password is required.

SYS user's password on the one hand , like ordinary users, stored in the database, on the other hand , the SYS user's password is stored in an operating system file , this file is the password file .
In Unix/linux, the password file is stored in the DBS subdirectory of the Oracle installation directory, and the file naming rules are: orapw<sid>. Ora, where refers to the instance name.

In windows, the password file is stored in the database subdirectory, and the file naming convention is: pwd<sid>. Ora.
SYS user's remote login is verified by a password file.

If the password file is missing or the password is entered incorrectly, the validation fails.
In this case, you may need to re-create the password file and specify a new password.

Oracle provides a command, named Orapwd, to manage the password file with this command.
This command is performed by an Oracle user in the operating system.
For example:
$ orapwd File=$0racle_home/dbs/orapworcl.ora password= "1234" force=y
The parameters of this command have the following meanings:
File: Used to specify the path and name of the password file, and if no path is specified, it will be generated in the current directory.
Password: The new password used to specify the SYS user.
Force: Overwrite if the password file already exists.

Board Deng: The structure of the database