Oracle Storage Structure-Oracle physical storage structure

The Oracle storage structure includes the physical storage structure and logical storage structure.

1. the physical storage structure of Oracle is composed of data files, online redo log files, and control files, of course, it also includes some other files, such as archive log files, parameter files, alarm files, tracking files, and backup files.

2. The Oracle logical storage structure is composed of data blocks, partitions, segments, and table spaces.

Oracle physical storage structure:

① Data file: a database may consist of multiple data files, which truly store database data. A data file is an operating system file. database objects (tables and indexes) are physically stored in data files.

Data Files have the following features:
A: A data file can belong to only one database.
B: data files can be set to automatically increase.
C: one or more data files form tablespaces.
D: a data file can belong to only one tablespace.
When we want to query the data of a table, if the data of the table is not in the memory, oracle will read the data file of the table and store the data in the memory.

② Control file: a database must have at least one control file. The "physical structure information" of the database stored in the control file must be ", it is precisely because he stores the physical structure information of the database, so he is particularly important. the physical structure information includes:
A: Database Name.
B: name and location of the data file and On-line log file.
C: The timestamp when the database is created.
To better protect the database, we can use images to control files. the content in each control file is the same. mirroring the control file does not affect database corruption or data loss even if a control file is faulty. when starting the database, oracle will open the database based on the data files in the control file and the information in the online log files.

③ Online redo log file: a database can have multiple online log files, and the online log file contains the redo records ). online log files record database changes. For example, if an unexpected data change is not written to a data file in time, oracle will obtain the changes based on the information in the online log file, write these changes to the data file. this is also the significance of the existence of online log files. the only function used to record online log files is to restore instances. for example, if a system powers down unexpectedly, the data in the memory is not written to the data file. oralce will restore the database to the status before failure according to the redo record function package in the online log file.

④ Archive log file: a copy of the online log file, which records the database change history.

⑤ Parameter file: It usually refers to the initialization parameter file ). the parameter file includes the initialization parameter file and the server-side parameter file ). when the database starts, it reads the parameter file, allocates the SGA according to the parameters in the parameter file, and starts a series of background processes. parameter files store database and instance parameters.

⑥ Alert log file: it is an alarm log file that records major activities and errors in the database. the alert file records the major events and errors by time. the format of the alert file name is alertSID. log. the location of the alert file is specified by the initialization parameter background_dump_desc.

7. trace log file: A trace log file. Each server process and background process writes a trace file. for example, if an error occurs in the background process, oracle will write the error information to the trace file. DBA can view errors in the process based on the trace file information. the trace file is written to two directories. information related to server processes is written to the directory specified by the initialization parameter user_dump_desc, and information related to background processes is written to the directory specified by the initialization parameter background_dump_desc. the time trace file will be filled up. DBA can manually delete the trace file or limit the size of the trace file. the initialization parameter MAX_DUMP_FILE_SIZE can limit the size of the trace file.

Recover backup file: Used to restore (restore) data in the database in case of medium damage.

1. Solution for optimizing Oracle physical storage structure
2. Basic syntax used in Oracle Structured Query
3. Introduction to Oracle Stored Procedure usage
4. Summary of Oracle Stored Procedure writing experience
5. Application of looping arrays during Oracle Storage