The representation (image) of a data structure on a computer is called the physical (storage) structure of the data, which includes the representation of data elements and the representation of relationships

The physical structure is the structure of the operating system files used by the oracle database. For the physical structure of the database files, the storage directory structure varies from oracle version to oracle version and from platform to platform

The physical structure of a database is the structure of the storage directory. p> winnt | d:oracleproduct oradataDB_NAME * *(oracle g);d:orantdatabase* *(oracle oracle ) Unix | /home/app/oracle/product/ /oradata/DB_NAME/* * ( g);/home/app/oradata/db_name/* *( i i)

The physical structure of the database files can be divided into three categories according to their roles

Data Files

Log Files

Control Files

I Data Files

Data files are used to store the data in the database such as tables, indexes, and so on. Data files are used to store database data, such as tables, indexes, and so on. When reading data, the system first reads the data from the database file and stores it in the SGA data buffer. This is to reduce I/O. If the data to be read is already in the buffer when the data is read, it doesn't need to be read from disk again. The same applies to storing data. DBWR decides how to write it to the datafile

Querying datafile information

sql>select * from dba_data_files or sql>select * from v$datafile (this data dictionary contains dynamic information about files)

A datafile is associated with only one database. datafile is linked to only one database The size of the datafile can be changed The amount of free space in the tablespace can be queried with the following statement

sql>select * from dba_free_space

Modify the size of the datafile

sql>alter database datafile "d: df dbf" resize m

Autoextend feature for database files See the following example:

sql>alter tablespace tbs add datafile "d: df dbf" size m autoextend on next m maxsize m sql>alter database mydb datafile "d: df dbf" "d: df dbf" autoexetend off sql>alter database mydb datafile "d: df dbf" "d: df dbf" autoexetend on next m maxsize unlimited

Two Redo Log Files

The redo log file records all changes made to the database. It is the most complex of the three types of files, and is also the one that is directly related to database security and database backups and restores.

Log File Groups and Logs

The log file group and logs are the most complex of the three.

In every oracle database, there are at least two redo log file groups, each of which has one or more redo log files, the log members. The members of the same group are mirrored, and they store exactly the same content. When Oracle writes logs, it writes them in logical units of a log group.

How logging works

Oracle has multiple log file groups. When all the members of a log file group are filled with data at the same time, the system automatically switches to the next log file group. This switching process is called log switching.

When the logs are switched over, the previous log group is assigned a number, which is used to archive the logs. This number is called the log sequence number, which starts at the beginning of the switch, and is automatically incremented with each switch. The maximum value is limited by the MAXLOGHISTORY parameter, which has a maximum value of

When oracle fills up the last log group, it automatically switches to the first log group, and then writes logs to the first log group.

Use the following statement to query the log file information

sql>select * from v$log

The relevant fields are described below

GROUP#:Log file group number

THREAD#:Log file thread number Typically, this is the number of the log file thread for a dual-unit machine

SEQUENCE#:Log file group number

The log file group is the first group to be written to, and the log file is the first group to be written to. SEQUENCE#:Log sequence number

BYTES:Log file size

MEMBERS:Number of log members in the group

ARC:Whether or not archiving of log messages in the group has been completed

STATUS:The status of the group (CURRENT:Indicates the group currently in use NACTIVE:Indicates the group is not active ACTIVE:Indicates the group is not in use)

ACTIVE:Indicates that a group is not active. ACTIVE: indicates that archiving is not complete)

FIRST_CHANGE#:System Change Number SCN also known as the checkpoint number

FIRST_TIME:System Change Time

DBAs can force logfile switching with the following command

sql>alter system switch logfile

NOARCHIVELOG/ARCHIVELOG

NOARCHIVELOG is non-archival mode If the database is running in this mode, when the logs are switched, the logs in the newly switched log group are overwritten ARCHIVELOG:archival mode If the database is running in this mode, the logs are archived. mode if the database is running in this mode, logs are archived and stored, archived logs are generated, and logs are not allowed to be overwritten until they are archived.

To confirm the archiving mode of a database, you can query the data dictionary v$database:

sql>select log_mode from v$database

To find out about archived logs, you can query the data dictionary v$database. 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:ORAARCHIVE

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 Automatic left zeroing %s represents the log sequence number Automatic left non-zeroing %T represents the log thread number Left zeroing %t represents the log thread number Left zeroing %t represents the log thread number Left zeroing %t represents the log thread number Left zeroing %T means log thread number left %T means log thread number not zeroed

e Restart the database

The CKPT process (checkpoint process)

The CKPT process ensures that any modified data in the database buffer is written to the datafiles, the logfiles, the datafiles, the database header, and the control file, and that there are checkpoints flagged for writes to the datafiles. The database header and control file are updated when the checkpoint completes

The parameter LOG_CHECKPOINT_TIMEOUT determines the time interval at which a checkpoint occurs LOG_CHECKPOINT_INTERVAL determines the number of logfile blocks that need to be populated for a checkpoint Checkpoint number, also known as the system change number (SCN), identifies a checkpoint that has occurred. (SCN) It identifies a checkpoint You can query the checkpoint information of the log file through v$log, the checkpoint information of the datafile through v$datafile, and the checkpoint information of the database header through v$database. If the checkpoint numbers are the same in all three places, and if they are different, it means that the invented databases are not in sync, and the databases will not be able to start up correctly. Delete logfile groups Log members (refer to oracle documentation for detailed syntax)

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 logfile data

alter database [database] clear [unarchived] logfile group integer|filespec

Three Control Files

A control file is a binary file that is used to describe the physical structure of a database A database requires only one A database requires only one control file The contents of a control file include

Database name and unique database identifier

Data file and log file identifiers

Synchronization information required for database recovery, i.e., checkpoint numbers

The control file is specified by the parameter control_files, in the following format

control_files=(" home/app/ /control ctl" "home/app/ /control ctl")

The files in the parameter are mirrored, which means that only one of the files needs to be intact in order for the database to function properly

The following query looks up information about the control files

sql>. select * from v$controlfile

If the control file is corrupted or lost, the database is terminated and cannot be started, so you need to mirror the control file manually as follows

a Shut down the database

b Copy the control file

c Modify the parameter file to include a description of the location of the newly added control file

d Restart the database

Also note that the control file contains settings for several server parameters, and if you change the values of these parameters, you will just need to recreate the control file

MAXLOGFILES:Maximum number of log files

MAXLOGMEMBERS:Maximum number of log members

MAXLOGHISTORY:Maximum number of history logs

MAXDATAFILES:Maximum number of data files

MAXINSTANCES:Maximum number of instance files

All commands that modify the structure of the database cause changes to the control file, which are also recorded in the oracle trace file. The name of the file is alter_SID log and the path is as follows

d:oracleproduct adminDB_NAMEdumpSIDALRT log (alter_SID ora for unix)

You can also specify the path where the trace file is stored in a parameter file The directory of the trace file in the background is specified by the parameter Background process trace file directory is specified by the parameter background_dump_dest User trace file location is specified by the parameter user_bdump_dest e.g.

lishixinzhi/Article/program/SQL/201405/30847