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.