Oracle Database Architecture (Part 04)
Oracle Storage Structures
A-The Physical Structure :
The physical storage structures include three types of files (Critical files that make up a database) :- Control files :Locations of other physical files, database name, database block size, database character set, and recovery information. These files are required to open the database.
- Data files :All application data and internal metadata
- Redo log files : Record of all changes made to the database; used for instance recovery.
Files not technically part of the database :
- Password file : Optional file used to store names of users who have been granted the SYSDBA and SYSOPER privileges
- Parameter file (pfile ,spfile): Configuration parameters for the SGA, optional Oracle features, and background processes.
- Archived redo log files : Copy of the contents of online redo logs, used for database recovery and for change capture.
- Oracle Net files : Entries that configure the database listener and client-todatabase connectivity
- Flashback log file :If the database has flashback logging enabled, files are written to the fast recovery area.
Control Files :
Store important information that is not available anywhere else , Include :
- The name of the database .
- A database-creation timestamp .
- The names, locations, and sizes of the data files and redo log files .
- Tablespace information .
- Redo log information used to recover the database in the case of a disk failure or user error.
- Archived log information .
- RMAN backup information .
- Checkpoint information .
To shows the types of information kept in the control file :
- SELECT type FROM v$controlfile_record_section;
The control files are created when the database is created in the locations specified in the control_files parameter in the parameter file.
Oracle uses the CKPT background process to automatically update each of these files as needed, keeping the contents of all copies of the control synchronized.
To display the names and locations and size of all the database’s control files.
- SELECT name, file_size_blks, block_size FROM v$controlfile;
The control files can be stored in any directory; however, it is better if they are physically stored on different disks.
Data files :
the physical files that actually store the data that has been inserted into each table in the database.
data files are the physical structure behind another database storage area called a tablespace.
A tablespace is a logical storage area within the database.
Tablespaces group logically related segments.
By default, every Oracle Database must have at least three tablespaces (Required):
- SYSTEM : Stores the data dictionary tables and PL/SQL code.
- SYSAUX : Stores segments used for database options such as the Automatic Workload Repository, Online Analytical Processing (OLAP), and Spatial .
- TEMP : Used for performing large sort operations. TEMP is required when the SYSTEM tablespace is created as a locally managed tablespace; otherwise, it is optional.
To shows the data files associated with each tablespace in the database :
- SELECT tablespace_name, file_name FROM dba_data_files ORDER BY tablespace_name;
If the user has performed a committed transaction that modifies that data, the database writer process (DBWn) ultimately writes the modified data back to the data files.
Redo Log Files :
Whenever a user performs a transaction in the database, the information needed to reproduce this transaction in the event of a database failure is written to the redo log files, and the user does not get a confirmation of the commit until the transaction is successfully written to the redo log files.
Because of the important role that redo logs play in Oracle’s recovery mechanism, they are usually multiplexed.
This means that each redo log contains one or more copies of itself in case one of the copies becomes corrupt or is lost because of a hardware failure.
Collectively, these sets of redo logs are referred to as redo log groups.
Each multiplexed file within the group is called a redo log group member.
Oracle automatically writes to all members of the redo log group to keep the files in sync.
Each redo log group must be composed of one or more members.
To shows information on redo logs in the database :
To display which redo log group is currently active and being written to by LGWR :
- SELECT group#, members, status FROM v$log ORDER BY group#;
To view the names of the redo log groups and the names and locations of their members :
- SELECT group#, member FROM v$logfile ORDER BY group# ;
LGWR eventually writes this recovery information to the active redo log group until that log group is filled. LGWR switches to the next redo log until that log group fills with transaction information, and so on, until all available redo logs are used. When the last redo log is used, LGWR wraps around and starts using the first redo log again.(overwritten)
The following are the statuses available for log files :
- UNUSED - Online redo log is new and never been written to.
- CURRENT - The current active redo log.
- ACTIVE - Log is active but is not the current log. It is needed for crash recovery.(FULL)
- CLEARING - A short time status during ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
- CLEARING_CURRENT - Current log is being cleared of a closed thread. The log file may be in this status if there is an I/O error writing the new log information.
- INACTIVE - Log is no longer needed for instance recovery.
Nearly all production databases run in archive-log mode because they need to be able to redo all transactions since the last backup in the event of a hardware failure or user error that damages the database.
If LGWR needs to write to the redo log group that ARCn is trying to copy but cannot because the destination is full, the database hangs until space is cleared on the drive.
B-The Logical Structure
Oracle logically divides the database into smaller units to manage, store, and retrieve data efficiently.
- Tablespaces : The database is logically divided into smaller units at the highest level, called tablespaces. A tablespace has a direct relationship to the physical structure—a data file can belong to one and only one tablespace. A tablespace could have more than one data file associated with it.
- Segments : A segment is a set of extents allocated for logical structures such as tables, indexes, clusters, table partitions, materialized views, and so on. Whenever you create a logical structure that stores data, Oracle allocates a segment, which contains at least one extent, which in turn has at least one block. A segment is created when a table, index, materialized view, or a clustered table is created.
- Extents : An extent is the next level of logical grouping. It is a grouping of contiguous blocks, allocated in one chunk. Because they are allocated in contiguous chunks, extents cannot spawn multiple data files.
- Blocks : A block is the smallest unit of storage in Oracle. A block is usually a multiple of the operating-system block size. A data block corresponds to a specific number of bytes of storage space. The block size is based on the parameter DB_BLOCK_SIZE and is determined when the database is created.
The extents of a segment may belong to more than one datafile.
Exploring the Data Dictionary for Physical and Logical Structures
A-Physical Storage Structures
A-Physical Storage Structures
- Control Files
- V$CONTROLFILE
- Redo Log Files
- V$LOG
- V$LOGFILE
- Data Files—The tablespace number (V$) or name (DBA) links a data file to its logical storage structure
- V$DATAFILE
- V$TEMPFILE
- DBA_DATA_FILES
- DBA_TEMP_FILES
B-Logical Storage Structures
- DBA_TABLESPACES
- DBA_SEGMENTS
- DBA_EXTENTS