Shulou(Shulou.com)06/01 Report--

The storage structure of Oracle is divided into physical storage structure and logical storage structure. Physical storage structure: refers to the files that exist on the hard disk

Data file (data file)

A database can be composed of multiple data files, which really store database data. A data file is an operating system file. The objects (tables and indexes) of the database are physically stored in the data file. When we query the data of a table, if the data of the table is not in memory, then oracle reads the data file where the table is located and stores the data in memory. You can view the currently existing data files and the corresponding tablespaces through the following statement:

1select file_name,tablespace_name from dba_data_files

Online log file (online redo log file)

A database can have multiple online log files that contain redo records (undo records). The online log file records the changes in the database. For example, when an accident causes changes to the data not written to the data file in time, then oracle will obtain these changes according to the information in the online log file, and then write these changes to the data file. This is also the meaning of the existence of online log files. The only function of redoing records in online log files is to restore instances. For example, an unexpected power loss of the system, resulting in the data in memory is not written to the data file. Then oralce will restore the database of the redo logging package in the online log file to the state it was before the failure. You can view the current log files and the corresponding log group information through the following statement:

1select member,group# from v$logfile

Note: Oracle uses log groups to manage log files. There are three log groups by default, with at least two members in each group. As shown in the image above.

In addition, we already know what data files and online log files are, and use the following diagram to illustrate the relationship between them.

Control file (control file)

A database must have at least one control file to control the "physical structure information" of the database stored in the file. It is precisely because it stores the physical structure information of the database that it is particularly important. This physical structure information includes:

The name of the database.

The name and location of the data file and online log file.

The timestamp when the database was created.

Meta information of RMAN backup

In order to better protect the database, we can mirror the control file. The content in each control file is the same. If the control files are mirrored, even if there is a problem with one of the control files, it will not affect the corruption of the database and the loss of data. When starting the database, oracle will open the database according to the information of the data file and online log file in the control file.

You can view the currently existing control file through the following statement.

1select name from v$controlfile

Note: there are two control files by default, which is called "multiplexing".

Archive log file

Is a copy of the online log file, which records the history of changes to the database. Note: Oracle defaults to non-archive mode, which can be viewed by the following statement

1archive log list

Switch the database to archive mode with the following statement.

1234shutdown immediatestartup mountalter database archivelog;alter database open

Parameter file

Usually refers to the initialization parameter file (initialization parameter file). The parameter file includes initialization parameter file and server-side parameter file (server parameter file). The parameter file is read when the database is started, and then SGA is allocated and a series of background processes are started according to the parameters in the parameter file. The parameters of the database and instance are stored in the parameter file.

There are two types of parameter files for Oracle:

Spfile: binary form, after 9i

Pfile: text form, before 9i

You can view the current parameter file information through the following statement:

1show parameter spfile

You can convert spfile to pfile in the following ways:

1create pfile='/home/oracle/pfile.ora' from spfile

View the parameter values in pfile:

You can modify the value of the parameter through the following statement.

12alter system set open_cursors=400 scope=both; Note: there are three values for scope: memory, spfile, and both.

Alarm log file

The major activities and errors of the database are recorded. Alarm files record major activities and errors in chronological order. The name of the alert file is in the format alert_SID.log. The location of the alert file can be obtained by querying v$diag_info, as follows:

1select * from v$diag_info

Note: in versions prior to 12c, the location of the alarm log can be obtained by querying the parameter background_dump_dest.

The information recorded in the alarm log file, including:

Information about database start and stop

Structural change of database

Information about compulsory audit

Deadlock information

Tracking file

Is to trace log files, each server process and background process write trace files. For example, when an error occurs in the background process, oracle will write the error information to the trace file. DBA can view the error in the process based on the information of the trace file.

The location of the trace file is in the same directory as the alarm log file:

1select * from v$diag_info

Backup file

It is used to restore (restore) the database and recover (recover) data when the database is damaged.

Second, logical storage structure

Logically,

A database consists of one or more tablespaces and so on.

A tablespace (tablespace) consists of a set of segments

A segment (segment) consists of a group of areas

An extent consists of a batch of database blocks

A database block corresponds to one or more physical blocks

Database (database)

A database is a warehouse that organizes, stores and manages data according to the data structure.

Tablespaces (tablespace)

A table space is a logical partition of a database, and a table space can only belong to one database. A table space (tablespace) is the largest logical unit, corresponding to one or more data files, usually made up of related segments. The size of a tablespace is the sum of the size of its corresponding data file. All database objects are stored in the specified tablespace. But the main storage object is the table, so it is called the tablespace.

Tablespaces that must exist

System

Sysaux

Temp

Undo

Optional tablespaces: generally refer to user-created tablespaces, such as users

You can view the tablespace information for the current database through the following statement:

1select tablespace_name from dba_tablespaces

Segments (paragraph)

A segment is a set of areas assigned to a logical structure (a table, an index, or other object) and a collection of space used by database objects; a segment can have table segments, index segments, rollback segments, temporary periods, cache segments, and so on.

Extents (Zone)

Is a logical unit of database storage space allocation, which consists of contiguous data blocks. The first segment consists of one or more extents. When all the space in the middle of a segment is fully used, oracle assigns a new range to the segment.

Data Block (block)

It is the unit of storage space in the data file managed by oralce. It is the smallest unit and the smallest logical unit used by the database. Its size can be different from the standard Imax O block size of the operating system. The size of the data block is determined by the DB_BLOCK_SIZE parameter. Block size is the smallest unit that handles Oracle updates, selections, or insert data transactions. When the user selects data from the table, the selection operation reads or extracts data in blocks from the database file. For example, if the size of the Oracle block is 8kb, even if you only want to retrieve the name of the character of the 4kb, you must read the block of the entire 8kb containing these four characters.

View the current block setting size with the following statement:

1show parameter db_block_size

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.