Oracle physical storage structure, oracle physical storage

Oracle physical storage structure, oracle physical storage

The architecture of the Oracle database analyzes the composition, working process, and how the database manages and organizes data from a certain perspective, including the physical storage structure and logical storage structure, memory structure and process structure.

The physical storage structure of Oracle:

The Oracle physical storage structure mainly includes three types of physical files: data files (*. dbf), control files (*. ctl), and redo log files (*. log ).

1. Data Files

Data files are mainly data files. For example, records and indexes of tables stored in data files. In these data files, if users frequently access some data, the data will be stored in the memory buffer. When reading the data, Oracle first finds the relevant data information from the memory de buffer. If no data exists in the memory buffer, the data is read from the database file and stored in the memory data buffer for query. This data access method not only reduces disk I/O operations, but also improves system performance.

If you need information about data files, you can query the data dictionary view DBA_DATA_FILES and V $ DATAFILE. First, use the DESC command to understand the structure of DBA_DATA_FILES and V $ DATAFILE in the data dictionary view. The structure of DBA_DATA_FILES in the data dictionary view is as follows:

SQL> destcba_data_files;

Is the name empty? Type

-------------------------------------------------------------------

FILE_NAME VARCHAR2 (513)

FILE_ID NUMBER

TABLESPACE_NAME VARCHAR2 (30)

BYTES NUMBER

BLOCKS NUMBER

STATUS VARCHAR2 (9)

RELATIVE_FNO NUMBER

AUTOEXTENSIBLE VARCHAR2 (3)

MAXBYTES NUMBER

MAXBLOCKS NUMBER

INCREMENT_BY NUMBER

USER_BYTES NUMBER

USER_BLOCK NUMBER

ONLINE_STATUS VARCHAR2 (7)

In the preceding results, FILE_NAME indicates the name and path of the data file, FILE_ID indicates the ID of the data file in the database, and TABLESPACE_NAME indicates the tablespace name corresponding to the data file; BYTES indicates the size of the data file; BLOCKS indicates the data file

Data Block occupied by the component; STATUS indicates the STATUS of the data file; AUTOEXTENSIBLE indicates whether the data file is scalable. Data Dictionary

View V $ DATAFILE records the dynamic information of the data file. Its structure is as follows:

SQL> DESCV $ DATAFILE;

Is the name empty? Type

-------------------------------------------------------------------

FILE # NUMBER

CREATION_CHANGE # NUMBER

CREATION _ TI DATE

TS # NUMBER

RFILE # NUMBER

STATUS VARCHAR2 (7)

ENABLED VARCHAR2 (10)

CHECKPOINT_CHAN NUMBER

CHECKPOINT_TIME DATE

UNRECOVERABLE_CHANGE # NUMBER

UNRECOVERABLE_TIME DATE

LAST_CHANGE # NUMBER

LAST_TIME DATE

OFFLINE_CHANGE # NUMBER

ONLINE_CHANGE # NUMBER

ONLINE_TIME DATE

BYTES NUMBER

BLOCKS NUMBER

CREATE_BYTES NUMBER

BLOCK_SIZE NUMBER

NAME VARCHAR2 (513)

PLUGGED_IN NUMBER

BLOCK1_OFFSET NUMBER

AUX_NAME VARCHAR2 (513)

FIRST_NONLOGGED_SCN NUMBER

FIRST_NONLOGGED_TIME DATE

FOREIGN_DBID NUMBER

FOREIGN_CREATION_CHANGE # NUMBER

FOREIGN_CREATION_TIME DATE

PLUGGED_READONLY VARCHAR2 (3)

PLUGIN_CHANGE # N MBER

PLUGIN_RESETLOGS_CHANGE # NUMBER

PLUGIN_RESETLOGS_TIME DATE

CON_ID NUMBER

In the above results, FILE # indicates the number of the data FILE to be stored; STATUS indicates the STATUS of the data FILE; CHECKPOINT_CHANGE # indicates the synchronization Number of the data FILE, which is automatically modified as the system runs, to maintain data file synchronization; BYTES indicates the size of the data file; BLOCKS indicates the number of data BLOCKS occupied by the data file; NAME indicates the NAME and storage path of the data file.

2. Control File

A control file is a very small binary file that mainly contains important information about the physical structure of the database and stores the data files and log files in the database.

The control file is critical to the successful startup and normal operation of the database. When loading a database, you must first find the Database Control File. If the control file is normal, the instance can load and open the database. However, if an error message is recorded in the control file or the instance cannot find an available control file, the database cannot be loaded or opened.

An Oracle database usually contains multiple control files. During database operation, the Oracle database must constantly update control files, therefore, the control file must remain available throughout the entire database. If the control file is unavailable for some reason, the database will crash.

You can use the data dictionary view V $ CONTROLFILE to view the control file information. Its structure is as follows:

SQL> DESC V $ CONTROLFILE;

Is the name empty? Type

------------------------------------------------

STATUS VARCHAR2 (7)

NAME VARCHAR2 (513)

IS_RECOVERY_DEST_FILE VARCHAR2 (3)

BLOCK_SIZE NUMBER

FILE_SIZE_BLKS NUMBER

CON_ID NUMBER

In the preceding results, STATUS indicates the STATUS of the control file, and NAME indicates the NAME and path of the control file. For example, query the name and path information of the control file, as follows:

SQL> SELECT NAME FROM V $ CONTROLFILE;

NAME

-------------------------------------------------

D: \ ORACLE \ ORADATA \ ORACLE12C \ CONTROL01.CTL

D: \ ORACLE \ ORADATA \ ORACLE12C \ CONTROL02.CTL

From the above results, we can see that Oracle generally creates two control files containing the same information by default. The purpose is to call another control file to continue working when one of the control files is damaged.

3. redo log files

Redo the log file from the current day to the file, which records the user's operation information on the database. Log files are one of the most important files in the database system. They can be used to ensure database security and backup and recovery. Once the log file is damaged, the database may fail to run normally.

To ensure log security, log files can be mirrored in actual applications. Log Files record the same log information as the image files. They form a log file group, it is best to store log files in the same group in different disks to ensure that when a log file is damaged, other log files provide log information.

Through the data dictionary V $ LOG, you can know which LOG file group the system is currently using, as follows:

SQL> SELECT GROUP #, MEMBERS, STATUS FROM V $ LOG;

GROUP # MEMBERS STATUS

------------------------------------

1 1 INACTIVE

2 1 CURRENT

3 1 INACTIVE

4 2 INACTIVE

In the above structure, if the value of the STATUS field is CURRENT, it indicates that the CURRENT system is using the log file group corresponding to this field.

When the space of one log file group is used up, the Oracle system will automatically switch to another log file group. However, the administrator can use the alter ststem command to wake up and manually switch log files. As follows:

SQL> ALTER SYSTEM SWITCH LOGFILE;

The system has been changed.

Query the data dictionary V $ LOG again as follows:

SQL> SELECT GROUP #, MEMBERS, STATUS FROMV $ LOG;

GROUP # MEMBERS STATUS

------------------------------------

1 1 INACTIVE

2 1 ACTIVE

3 1 CURRENT

4 2 INACTIVE

According to the above results, the log file group currently running by the system has changed.