ORACLE Database File System Analysis

ORACLE Database File System Analysis 1. files associated with the database file type and instances: 1. Parameter files: these files tell Oracle instances where to find control files. For example, the size of a memory structure. Files that make up the database: 2. Data Files: used for databases (these files store information and data that contains tables, indexes, and other parts ). 3. Redo the log file: record the transaction log 4. Control File: It tells us where the data file is stored and other information about its status. 5. Temporary Files: used for disk classification and temporary storage. 6. Password File: used for identity verification by Users performing management activities on the network. 2. parameter file 1. The parameter file of the database is usually called the init file or the init. ora file. This is because the default name is init <ORACLE_SID>. ora. If no parameter file exists, you cannot start the database. This makes it a very important file. However, because it is a plain text file that can be created by any text editor, it is not a file that requires special attention. The parameter file must at least obtain the Database Name and control the file location. The control file tells Oracle the location of each other file. Therefore, it is very important to start database instances. 2. There are many other configuration settings in the common configuration file. The number and name of parameters vary with versions. For example, in Oracle8.1.5, a parameter file is plsql_load_without_compile. Not in previous versions, nor in later versions. In versions 8.1.5, 8.1.6, and 8.1.7, there are 199, 201, and 203 different parameters, which can be configured separately. In the Reference manual of Oracle versions, each recorded parameter is reviewed in detail. 3. Besides the recorded parameters, there are also parameters not recorded in the document. Separate the recorded and unrecorded parameters. Parameters that are not logged start with an underscore. These parameters are not well known and are basically not used. In fact, most of the unrecorded parameters are more annoying. They represent unacceptable features and backward compatibility signs. In the configuration file, there is no reason to use parameters that are not recorded. Many such parameters have bad side effects. Generally, the "_ TRACE_FILES_PUBLIC = TRUE" parameter is used, which allows all developers to read trace files. Instead of being readable only by DBA users. 4. The parameter file must not be in a special location. When starting a database instance, you can use startup pfile = filename. This is most useful when you want to observe the impact of different settings and try different init. ora parameters in the database. 3. Data File 1. Each database must have at least one relevant data file. In fact, apart from the simplest "test" database, there will be only one data file. Any real database has at least two files (one is a SYSTEM data file, one is a "USER" data file ). To understand how ORACLE organizes these files and how data is organized inside them, you must first understand what table space, segments, and disk partitions are) block ). These are the allocation units that Oracle uses to save database objects. 2. A segment is a database object, which consumes storage space (objects), such as tables, indexes, and rollback segments. When you create a table, a table segment is created. When you create a partition table, you create a table segment in each partition. When you create an index, you will create an index segment. Each object that consumes storage space is stored in a single segment. Rollback segments, index segments, temporary segments, and cluster segments are supported. Segments are composed of one or more disk partitions. 3. The Disk Area is a continuous distribution of space in the file. Each segment must start in at least one disk area, and some objects may need at least two disk areas (rollback segments are an example that requires at least two disk areas ). In files, the disk space is always continuous. The disk size range is from one block to 2 GB. The disk area consists of blocks. 4. A block is the minimum unit for space allocation in Oracle. A block is the place where data rows, index items, and temporary sorting results are stored. Block is the object for Oracle to read and write disks. The chunks in Oracle usually have three common sizes (2KB, 4KB, and 8KB ). Of course, a larger size is also allowed. 5. A segment is composed of one or more disk areas, and some continuously allocated blocks constitute a disk area. The size of the database block is fixed when the database is created. In the database, the capacity of each block is the same, and all the blocks have the same common format. The block header contains information about the block type (Table block, index block, and so on), block activity and outdated transaction information, and the address (location) of the block on the disk. Table Directory, which contains information about the tables in each row. The row directory contains information about the descriptive rows found in the block. Block headers, table directories, and row directories are collectively referred to as block overhead (block overhead), which means that the block cannot be provided to the storage space and is used to manage the block itself. The remaining two parts of the block are: Free Space and used space. 6. A tablespace is a container used to store segments. Each segment exactly belongs to a tablespace, and a tablespace can have multiple segments. The tablespace itself has one or more associated data files. The disk areas of any given segment in the tablespace are completely contained in a data file. However, a segment can have disks from many different data files. A tablespace is a logical storage container in oracle. The hierarchical structure stored in Oracle is: (1) the database is composed of one or more tablespaces; (2) the tablespace is composed of one or more data files, a tablespace contains one or more database segments. (3) segments (tables, indexes, etc.) are composed of one or more disk areas. A segment exists in a tablespace and can only belong to one tablespace. However, a segment can be stored in multiple data files to which the tablespace belongs. (4) The Disk Area is a group of consecutive blocks on the disk. A disk partition exists in a segment, and a disk partition is in a segment (tablespace), but a disk partition can only exist in a single data file of the tablespace. (5) blocks are the smallest allocation units in the database, and blocks are the smallest I/O units used by the database. 7. In versions earlier than Oracle8.1.5, there is only one way to manage Disk Partitions In a tablespace. This method is called direary Ary-managed tablespace ). That is, the tablespace space is managed in the data dictionary. When an object needs another disk area, it will request the system to obtain a disk area. Oracle then goes to its data dictionary and runs some requests to discover space (or no space). Then, it updates or deletes all rows in a table ), insert a row into another table. The method class for Oracle space management is very similar to modifying applications by modifying data and moving around. To obtain additional space, SQL statements running in the background are called recursive SQL statements. SQL insert statements allow other recursive SQL statements to run for more space. If recursive SQL is frequently used, it will be very expensive. Therefore, the update of the data dictionary must be serialized. They cannot be processed at the same time. 8. In Versions later than Oracle8.1.5, the concept of local tablespace management is introduced, which is opposite to that of dictionary management. The local tablespace is used to manage the disk partition. The bitmap stored in each data file is used to manage the disk partition. Now, to obtain the disk area, all the system needs to do is set the position in the bitmap to 1. To release the tablespace, restore it to 0. 4. Temporary data files in Oracle are a special data file type. Oracle uses temporary files to store intermediate results or result sets of a large-scale sorting operation in RAM. Permanent data objects, such as tables or an index, are not stored in temporary files, but contents of temporary tables or temporary indexes may be stored in temporary files. Therefore, you cannot create your own application tables in temporary files, but you can use temporary tables to store temporary data. A temporary file never generates a redo log, but generates a cancel log. When used in a global temporary table, if you want to roll back some transactions, you can complete it in your own session. 5. The control file is a fairly small file (up to 64 MB) that contains the directories or paths of other files required by Oracle. The parameter file tells us the location of the control file, while the control file tells the instance the location of the database and the online redo log file. The control file also provides other information for Oracle, such as information about checkpoints that have occurred, database names, timestamps at database creation, and history of archived redo logs. Control files should be taken multiple times by hardware or Oracle (RAID) When RAID or images are unavailable and should be stored on separate disks separately, to avoid the loss of control files in case of disk failure. Vi. redo log files 1. redo log files are crucial to the Oracle database. They are the transaction logs of the database, it is used only for database recovery (they are used only when the instance or media fails, or as a way to maintain the backup database for database recovery ). If the power of the machine where the database is located is turned off and the instance fails, Oracle will use the online redo log to restore the database to the moment before the power is stopped. If a permanent fault occurs on the disk drive that contains the data file, the Oracle database uses the archived redo log to restore the backup of the drive to that point in time just like the online redo log. In addition, if a table is accidentally lost, some key information is deleted, and operations are submitted, you can use these online and backup redo logs to restore the backup, and enables Oracle to immediately restore it to the time before the accident. 2. In fact, each operation executed in Oracle generates a certain number of redo logs, which are written into online redo logs. Some operations may be performed by generating as few redo logs as possible. For example, you can use the NOLOGGING attribute to create an index. This means that the initial creation of the index is not recorded in the log. Online redo logs: 1. Each Oracle database has at least two online redo log files. These online redo log files are fixed in size and used cyclically. Redo logs are first written to log file 1. When the log file is written to the end, switch to log file 2, and so on until it is written to the last log file. When the last log file is written to the end, automatically switch back to log file 1. 2. switching from one log file to another is called Log switching. Log switching may cause temporary "suspension" in a database with poor optimization performance ". Because the redo log is used for Recovery Processing when the operation fails, you must ensure that the log content does not need to be redone when the operation fails before reuse. If Oracle is not sure that it needs the content of a log file, it will temporarily suspend database operations and ensure that the redo data "protected" is safe on the disk itself. Once this problem is confirmed, it will be processed again and the redo log will be reused. 3. Database buffer high-speed cache is the place where the database temporarily stores data. This is the structure of the Oracle SGA. The buffer cache is the first and most important performance optimization device. It exists independently to make very slow I/O processes look faster. The redo log file cannot be used as long as the modified block is cached and not written to the disk. 4. the checkpoint is a refresh of dirty (modified) blocks cached from the buffer to the disk. DBWn is the background process of Oracle. It is responsible for creating space when the buffer cache overflows, and more importantly, executing checkpoints. Oracle does this in the background, and many operations will lead to checkpoints. The most common thing is to redo log switching. When Log File 1 is filled up and switched to log file 2, Oracle initializes a checkpoint. Start DBWn and refresh all the modification blocks protected by Log File 1 to the disk. Before DBWn refreshes all the blocks protected by the log file, Oracle cannot reuse it. If you try to use it before DBWn completes the checkpoint, the following information will be obtained :... thread I cannot allocate new log, sequence 66 Checkpoint not complete Current log #2 seq #65 mem #0: C: \ ORACLE \ ORADATA \ TKYTE816 \ REDO02.LOG... in the database, there is an alarm log (ALARM log is a file on the server, it contains a wealth of messages from the server, such as start and close messages and exception events, such as unfinished checkpoints ). Therefore, when the message appears, the processing in the database is suspended, and DBWn is in a rush to complete its checkpoint. Oracle does its best to provide DBWn with all the processing capabilities, so that it can be done faster at that time. 5. Information that is not displayed in a well-optimized database instance. If you see such a message, it introduces artificial and unnecessary waiting for the end customer. This can be avoided. You only need to allocate enough online redo log files so that you do not attempt to reuse the log files before the checkpoint completes initialization. If this message appears frequently, it means that the DBA has not assigned enough online redo logs to the application, or needs to optimize DBWn more effectively. 6. Different applications will produce different numbers of redo logs. DSS (Decision Support System) will naturally generate important online redo logs, but it is less generated than OLTP (Transaction Processing Transcation Processing) systems. Systems that perform many image operations in Binary Large objects (BLOB) in databases can generate much more redo logs than simple order Input systems. 7. When setting the size and quantity of online redo logs, you must consider the following factors: (1) backup database. If you are using the backup database feature, redo logs here are sent to another machine after they are filled up and applied to a copy of the database. You are most likely to want many small redo log files. This will help ensure that the async between the backup database and the master server is not too obvious. (2) Many users modify the same block. Here you may want to redo a large log file. Because everyone is modifying the same block, we hope to update them as much as possible before writing them to the disk. Each log switch triggers a checkpoint, so you do not want to switch logs too frequently. (3) Average recovery time. If it is necessary to ensure that the recovery takes as little time as possible. Therefore, a small redo log file may be required. In restoration, processing one or two small redo log files takes less time than processing a large redo log file. Archive redo log Oracle databases can be run in either of the two modes-non-archive mode or archive mode. If the archive mode is not used, the system is not a practical system. Non-archive databases will lose data one day sooner or later, which is inevitable. If the archive mode is not used, data will be lost. Only the test system or development system can run in non-archive mode. The difference between the two modes is what Oracle does when reusing and redoing log files. The archive mode must be used for systems that contain valuable data.