Interpretation
The representation of the data structure in the computer (image)
Classification
The physical structure files of the database can be divided into Three categories:
Data files
Log files
Control files
Redo log files
Redo The log file records all modification information to the database. It is the most complex of the three types of files, and it is also a file that is directly related to database security and database backup and recovery.
Log file groups and log members
In every Oracle database, there are at least two redo log file groups. Each group has one or more redo log files, that is, log members. The members in the same group are in a mirror relationship, and their storage contents are exactly the same. When oracle writes a log, it writes in a log group as a logical unit, and the log writing is completed only after the log is written to each member file in the log group.
How the log works
Oracle has multiple log file groups. When all members of a log file group are filled with data at the same time, the system automatically switches to the next one. Log file group, this conversion process is called log switch.
When the log is switched, a number will be assigned to the previous log group for the number of archived logs. This number is called the log sequence number. This number starts from 1, and the serial number is automatically increased by 1 each time it is switched. The maximum value is limited by the parameter MAXLOGHISTORY. The maximum value of this parameter is 65534.
When Oracle fills the last log group, it will automatically switch to the first log group. At this time, when writing logs to the first log group, if the database is running in non-archive mode , The original log information in this log group will be overwritten.
Use the following statement to query log file information:
sql>select * from v$log
The description of the relevant fields is as follows:
GROUP #:Log file group number
THREAD#:Log file thread number, generally 1, and double machine capacity time is 2
SEQUENCE#:Log sequence number
BYTES: log file size
MEMBERS: the number of log members of the group
ARC: whether the log information of the group has been archived
STATUS: the group Status (CURRENT: indicates the group currently in use; NACTIVE: indicates the inactive group; ACTIVE: indicates the archive is not completed)
FIRST_CHANGE#: System change number SCN, also called checkpoint number
< p>FIRST_TIME: System change timeDBA can use the following commands to force log switch
sql>alter system switch logfile
NOARCHIVELOG/ARCHIVELOG
NOARCHIVELOG is a non-archive mode. If the database is running in this mode, when the log is switched, the log information in the newly switched log group will be overwritten. ARCHIVELOG: Archive mode. If the database is running in this mode, the log will be archived and stored, resulting in an archive log, and the log is not allowed to be overwritten and written before it is archived.
To confirm the archiving method of the database, you can query the data dictionary v$database:
sql>select log_mode from v$database
To understand the information of the archive log , You can query the data dictionary v$archived_log.
To change the database to archive mode:
a.alter database archivelog
b. Set the initialization parameter LOG_ARCHIVE_START=TRUE
c .Set the archive file destination storage path LOG_ARCHIVE_DEST=C:\ORA\ARCHIVE
d. Set the archive file naming format parameter LOG_ARCHIVE_FORMAT="ORCK%T%S.ARC". The %S in this format represents the log sequence number, with zeros on the left automatically; %s represents the log sequence number, without zeros on the left automatically; %T represents the log thread number, with zeros on the left; %t represents the log thread number without zeros.
e. Restart the database
CKPT process
The CKPT process ensures that the data in the modified database buffer is written to the data file, log Files, data files, database headers and control files are all written with checkpoint marks. When the database is restored, only the changes made since the last check are provided. When the checkpoint is completed, the system will update the database header and control file.
The parameter LOG_CHECKPOINT_TIMEOUT determines the time interval at which a checkpoint occurs. LOG_CHECKPOINT_INTERVAL determines the number of log file blocks that need to be filled for a check. Checkpoint number, also called system change number (SCN), it identifies a checkpoint. You can query the checkpoint information of the log file through v$log, query the checkpoint information of the data file through v$datafile, and query the checkpoint information of the database header through v$database. The checkpoint numbers in the three places are the same. If they are different, the invention database is out of sync. At this time, the database must not start normally.
Add and delete log file groups and log members
(For detailed syntax, please refer to the oracle document)
alter database [database] add logfile [group integer] filespec [,[group...
alter database [database] add logfile (...)
alter database [database] drop logfile [grout integer]
alter database [database] add logfile member "filespec" [reuse] to group integer
alter database [database] drop logfile member "filename","filename"...
alter database [database] rename file "filename" to "filename"
Clear log file data
alter database [database] clear [unarchived] logfile group integer|filespec
Control file
The control file is a binary file used to describe the physical structure of the database. A database only needs one control file. The content of the control file includes:
Database name And database unique identifier
data file and log file identifier
The synchronization information required for database recovery, namely the checkpoint number
The control file is specified by the parameter control_files, The format is as follows:
control_files=("home/app/.../control01.ctl","home/app/.../control02.ctl")
In the parameter Each file is a mirror image relationship, that is, as long as one of several files is intact, the database can run normally.
The following statement queries the information of the control file:
sql>select * from v$controlfile
If the control file is damaged or missing, the database will be terminated and cannot be started Therefore, to mirror the control file, the manual mirroring steps are as follows: a. Close the database
b. Copy the control file
c. Modify the parameters File, add the new control file location description
d. Restart the database
In addition, note that the control file also contains the settings of several server parameters. If you modify the values of these parameters , Just need to re-create the control file, these parameters are:
MAXLOGFILES:Maximum number of log files
MAXLOGMEMBERS:Maximum number of log members
MAXLOGHISTORY:Maximum Number of historical logs
MAXDATAFILES: Maximum number of data files
MAXINSTANCES: Maximum number of instance files
All commands to modify the database structure will cause the control file Change. At the same time, the meeting will be recorded in the oracle tracking file, the name of the tracking file is alter_SID.log, and the path is as follows:
d:\oracle\product\10.1.0\admin\DB_NAME\bdump\SIDALRT.log( unix is alter_SID.ora)
You can also specify the storage path of the trace file in the parameter file. The background process trace file directory is specified by the parameter background_dump_dest, and the location of the user trace file is specified by the parameter user_bdump_dest, such as:
background_bdump_dest=/u01/app/oracle/oralog/bdump
user_bdump_dest=/u01/app//oralog/udump
Data file
Data files are used to store database data, such as tables and indexes. When reading data, the system first reads the data from the database file and stores it in the data buffer of the SGA. This is to reduce I/O. If there is already data to be read in the buffer when reading data, there is no need to read it from the disk. The same is true when storing data. The data changed when the transaction is committed is first stored in the memory buffer, and then the oracle background process DBWR determines how to write it to the data file.
Query the information of the data file
sql>select * from dba_data_files or
sql>select * from v$datafile (This data dictionary contains the dynamic information of the file )
A data file is only associated with one database. The size of the data file can be changed. You can query the free space of the table space through the following statement
sql>select * from dba_free_space
Modify the size of the data file
sql>alter database datafile "d :\...\df1.dbf" resize 800m
Automatic expansion of database files
Please see the following example:
sql>alter tablespace tbs1 add datafile "d:\...\df2.dbf" size 500m autoextend on next 50m maxsize 1000m
sql>alter database mydb1 datafile "d:\...\df2.dbf","d :\...\df3.dbf" autoexetend off
sql>alter database mydb1 datafile "d:\...\df2.dbf","d:\...\df3.dbf" autoexetend on next 30m maxsize unlimited