[Reading Notes] Database physical structure

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/72/79/wKiom1XkU1qioxj0AAKMBSzJbyI252.jpg "title=" Clipboard.png "alt=" wkiom1xku1qioxj0aakmbszjbyi252.jpg "/> Note:

1) database refers to data files;

2) The process and memory make up an instance.

1. Data files:

A data file is a file that is really used to record user data, a data file can belong to only one database, and a data file can belong to only one table space.

View table Space physical file name, path and size:

Select Tablespace_name,

FILE_ID,

file_name,

Round (Bytes/(1024x768), 0) Total_space

From Dba_data_files

Order BY Tablespace_name;

How data files are created:

Create a data file when creating a new table space:

Create tablespace test datafile '/u01/app/oracle/oradata/orcl/test_01.dbf ' size 32m autoextend on next 32m maxsize 2048m Extent management Local;

To add a tablespace data file:

Altertablespace users add datafile '/u01/app/oracle/oradata/orcl/users02.dbf ';

Delete data file mode: Delete the table space while deleting the data file, directly delete the data file will be an error.

Drop tablespace test_data includingcontents and datafiles;--Delete data file when table space is deleted

Alter tablespace users drop datafile '/u01/app/oracle/oradata/orcl/users02.dbf ';-- Delete a data file in the Tablespace

2. Control Files

The name of the database is recorded in the control file, and the physical layout of the database includes the location of the data file, the online log file, the backup file, and the current database SCN and other important information, in the second phase of database startup will be read into the control file.

Requires multiple copies of the control file to prevent damage to the database after a single file is damaged. When the database modifies the control file, all copies are modified at the same time.

How to view:

SELECT * from V$controlfile;

Show parameter control_files;

To create a copy of a control file:

1) Modify the parameter file

alter system set Control_files = '/u01/app/oracle/oradata/orcl/control01.ctl ', '/u01/app/oracle/flash_recovery_area/ Orcl/control02.ctl ' Scope=spfile;

2) Stop the database

sql> shutdown immediate;

3) Copy control file

[Email protected] yoon]$ cp/u01/app/oracle/oradata/orcl/control01.ctl/u01/app/oracle/flash_recovery_area/orcl/ Control02.ctl

4) Start the database

Sql> Statup

5) Verify , view v$controlfile

Sql> select name from V$controlfile;

The steps to delete a copy of the control file are one less step than the steps you created to copy the control file.

3. Log Files

A log file is a "journal" that records all changes occurring in the database in chronological order.

The log file is logged Redo Records, each Redo Records represents a change in the data block. A transaction involves multiple data block content changes, so it contains multiple Redo Records. These Redo Records Record the following contents of the transaction:

The start time of the transaction;

The name of the transaction;

Object name;

The state before the data is changed, also called the pre-image;

The state after the data has been modified, also called after mirroring;

Commit tag;

Time to trigger the write operation:

Commit operation; the cumulative Redo Log reaches 1M;Redo Logbuffer uses 1/3 that exceed the total capacity .

Log files are usually in the form of a group, and the contents of each group are exactly the same; the log will switch to the next log group after a log group has been written.

View of log files:

SELECT * from V$logfile;

log file Contents view: Using Logminer.

Log file groups increased:

ALTER DATABASE add logfile Group N (' path ') size100m;

log file group deletion ; :

ALTER DATABASE drop logfile Group 4;--only log groups with INACTIVE status can be deleted

Log file group membership increased:

ALTER DATABASE add logfile member ' path ' to Groupt;

Log file group member removal:

ALTER DATABASE drop logfile member ' path '

Archived log files are log files that are generated in archive mode, and archived log files are generated before the log files are overwritten to preserve the historical "journal".

4. parameter file

The parameter file is divided into static parameter file (pfile) and dynamic parameter file (Spile). pfile is a text document,SPFile is a binary file.

Parameter modification:

Alter system set Parameter=value[scope=spfile|memory|both]

Scope ,SPFile represents the modification in the spile file, does not affect the current settings , memory is immediately modify the current settings, will not modify the spile;both The SPFile and current settings are also modified .

for static parameters, use only scope=spfile

Oracle Boot Process load file order: spfilesid.oraàspfile.oraàInitsid.ora

Location of the parameter file:

Show parameter SPFile;

Show parameter SPFile;

Creation of the parameter file:

Create pfile= ' ... ';

Create spile= ' ... ';

How to start the database with the specified parameter file:

Startup pfile= ' ... ';

Startup spfile= ' ... ';

5. Alarm Log file

Record important events that occur during database operation, such as startup, shutdown, log switching, adding data files, and so on during startup, this log records all parameter values.

How to view:

Show parameter background_dump_dest;--find the location of the alarm log

Enter the directory under the Linux system and input the tail-f alarm log.

exception View:grep ora-alertsid.log

6.trace files: There are 3 types of trace files, which are kernel trace files, background process trace files, user trace files, respectively, with core_dump_dest,background_dump_dest ,user_dump_dest parameter definition

7.Change Tracking File

Data block change tracking file, which records all changed blocks since the last full-volume backup, saves time on incremental backups.

[Reading Notes] Database physical structure