Oracle physical database structure Overview (data files, redo log files, control files, and other database files)

 

Oracle physical database structure Overview

 

The physical structure of Oracle is actually composed of a variety of files,

Database-related items include:

Data file ):These files are the main files of the database, including data tables, indexes, and all other segments.

Temporary file ):These files are used to complete disk-based sorting and temporary storage.

Control file ):These files tell you where the data files, temporary files, and redo log files are, and indicate other metadata related to the file status.

Redo log file): These are transaction logs.

Password File ):These files are used to authenticate users who have completed management activities over the network. It allows remote sysdba or administrator access to the database.

Change tracking file ):This file facilitates real Incremental backup of ORACLE data. Modifying a trace file does not have to be stored in the Flash recovery area, but it is only related to database backup and recovery.

Flashback log file ):The "front image" of these file storage database blocks to complete the newly added flashback database command.

 

Instance-related files only include:

Parameter file): These files tell the Oracle instance where to find the control file and specify some initialization parameters that define the settings of a memory structure. It has two forms: local and service.

Trace file ):This is usually a diagnostic file created when a server process responds to an exception or error condition.

Warning file ):Similar to the trace file, but it contains information about "expected" events, and warns DBA through a centralized file (including multiple database events.

 

There are also some special files:

Dump File): These files are generated by the export (export) database utility and used by the import database utility.

Data Pump file ):These files are generated by the data pump export process added by Oracle 10g and used by the Data Pump import process. External tables can also be created and used in this file format.

Flat file ):You can view these unformatted files in the text editor. These files are usually used to load data into the database.

 

 

For more information about the database files, see: http://hi.baidu.com/feixianxxx/blog/item/952188f9b91eb89858ee9060.html

For more information about the parameter file, see: http://hi.baidu.com/feixianxxx/blog/item/81420c57daff6b103a293595.html

 

Here are a few points:

1. A data file can store various types of solution objects in the tablespace. Oracle can use one or more data files when allocating space for solution objects in a tablespace.That is to say, the solution object can be cross-data files.

 

2. You can also take a data file offline separately. However, this operation is mainly used for database recovery.

3. Temporary data files are similar to common data files, but there are the following differences: 

A. Temporary files are always set to nologging mode.

B. You cannot set temporary files as read-only files.

C. You cannot use the alter database statement to create a temporary file.

D. Media recovery cannot recognize temporary files:

Backup controlfile does not generate information related to temporary files

Create controlfile cannot set information related to temporary files

E. When a user creates a temporary file or changes its capacity, Oracle does not guarantee that the user-specified file capacity will be allocated disk space. In some file systems (such as UNIX), disk blocks are not allocated when files are created or changed, but are allocated before they are used.

F. The temporary file information can be queried from the dba_temp_files data dictionary table and the V $ tempfile dynamic performance view, but does not exist in the dba_data_files or V $ datafile view.

 

4. Four file system mechanisms can be used to store data in data files.Data here refers to your data dictionary, redo record, undo record, table, index, lob, and other data that you care about every day.

"Cooked" operating system (OS) file system;

Raw partition (also called raw partition );

Automatic Storage Management (ASM );

Cluster File System;

 

5. Do logs are not generated for temporary files, but undo logs can be generated.Since Undo is always protected by Redo, this generates a redo log using a temporary table,

 

6. The control file contains the following content: 

Database Name)

Timestamp)

Name and storage location of the data file and redo log file of this database

Table space information

Offline data files

Log history

Archived log information

Backup set and backup block information

Backup information of data files and redo logs

Data file copy information

Current Log serial number (log sequence number)

Checkpoint Information

 

7. For the control file, we recommend the following:

Use multiple control file in each database)

Store copies of control files on different physical disks

Operating System Mirroring)

Monitoring backup

 

8. Redo the log file:

Instance recovery after system crash

Restore media after data files are restored through backup

Standby (standby) database processing

Input to the stream, which is a redo log mining process for information sharing (this is also a strange replication)

 

9. Some operations may be completed in the mode of generating as few redo as possible.For example, you can use the nologging attribute to create an index. However, all recursive SQL statements completed by Oracle are written into logs.

 

10. Log switching may be paused.Because it needs to make sure that the contents of the overwritten transaction log have been executed, that is, the data has been written to the disk. that is to say, if the transaction log-protected buffer data is not or is being written to a disk, the switchover operation cannot be performed.

 

11. The smaller the transaction log capacity, the more frequent log switching occurs, affecting Efficiency

12. Why not use archivelog?Performance cannot be used as a reason; an archive with proper configuration will only increase the overhead a little or even no overhead at all. Because of this, add another one: if a system will "lose data", it will be useless again soon.

 

13. The password file stores a list of user names and passwords, which correspond to users who can remotely authenticate sysdba over the network.Oracle must use this file to authenticate users, rather than the list of normal passwords stored in the database.

 

 14. Change the password file status: Alter system set remote_login_passwordfile = exclusive scope = spfile;(Shared (multiple databases can use the same password file) and exclusive (only one database uses a given password file )).

However, note that the changes here do not take effect dynamically and you still need to restart the instance.

The new password file has only one valid sysdba user SYS. To add other users with sysdba to the password file, grant the user sysdba to xxxx again;

 

15. The only purpose of modifying a tracking file is to track which parts have been modified since the last Incremental backup.In this way, the recovery Manager (RMAN) tool can only back up database blocks that actually change without reading the entire database.

Alter database enable block change tracking

Using File

'/Home/ora10gr1/product/10.1.0/oradata/ora10gr1/ora10gr1/changed_blocks.bct ';

 

16. Flashback log file (flashback Log File) is referred to as flashback log (flashback log). This is introduced in Oracle 10g to support the flashback database command.

What if you accidentally delete an important data table?

(1) DBA closes the database.

(2) When DBA starts and loads the database, it can use SCN, Oracle clock or timestamp (wall clock time) to issue the flash back database command, which can be accurate to one or two seconds.

(3) DBA opens the database with the resetlogs option.

 

17. The flash recovery area in Oracle 10 Gb is a new location, including the following files:

A copy of the data file on the disk.

Incremental backup of the database.

Redo log (archive redo log ).

Backup of control files and control files.

Flash Back log.

 

18. Oracle 10g has at least two tools using the Data Pump file format.External tables can load and unload data files in the Data Pump format.

 

19. With spfile, we can eliminate two serious problems in traditional parameter files:

This prevents the reproduction of parameter files. Spfile is always stored on the database server. It must exist on the server host and cannot be stored on the client. For parameter settings, there can be only one "information source ".

You do not need to manually maintain the parameter file outside the database using a text editor (in fact, you cannot manually maintain it more accurately ). The alter system command can be used to directly write values to spfile. The Administrator no longer has to manually search for and maintain all parameter files.

 

20. delete a parameter in spfile?Alter system reset parameter name scope = spfile SID = '*';

 

21. Oracle databases (as well as application servers and Oracle Applications) can be fully measured.This measurement in the database is reflected in the following aspects:

A. V $ view: Most v $ views contain debugging information. V $ waitstat, V $ session_event, and many other V $ views exist to let us know what happened inside the kernel.

B. Audit Command: With this command, you can specify the events to be recorded in the database for future analysis.

C. Resource Manager (dbms_resource_manager): This feature allows you to perform micro-management on database resources (such as CPU and I/O. The resource manager is available only because the database can access all runtime statistics that describe the resource usage.

D. Oracle "Event": based on Oracle events, Oracle can generate required tracking or diagnostic information.

E. dbms_trace: this is a tool in the PL/SQL engine. It fully records the call tree of stored procedures, exceptions, and errors encountered.

F. Database event triggers: these triggers (such as on servererror) allow you to monitor and record what you think is "unexpected" or abnormal. For example, you can record the SQL statements that are running when the "temporary space used up" error occurs.

G. SQL _trace: This SQL tracking tool can also be used in an extended way, that is, the 10046 Oracle E event.

 

22. Whether using SQL _trace or an extended tracking tool, Oracle generates a trace file at the following two locations on the database server host:

If a dedicated server is used for connection, a trace file is generated in the directory specified by the user_dump_dest parameter.

If the Shared Server is used for connection, a trace file is generated in the directory specified by the background_dump_dest parameter"

 

23. The warning file (also known as the warning log) is the log of the database.This is a simple text file. It will be written from the day when the database is "Born" (created) until the database is "finished" (deleted by you.