Database Storage Structures
Introduction
An Oracle database is made up of physical and logical structures.Physical structures can be seen and operated on from the operating system, such as the physical files that store data on a disk.
Logical structures are created and recognized by Oracle Database and are not known to the operating system.
The applications developer or user may be aware of the logical structure, but is not usually aware of this physical structure.
This section provides background information about the various database storage structures. It contains the following topics:
- Control Files
- Online Redo Log Files
- Archived Redo Log Files
- Rollback Segments
- Data Files
- Tablespaces
- Other Storage Structures
Control Files:
- tracks the physical components of the database.
- the root file that the database uses to find all the other files used by the database.
- have multiple identical copies.
- At the time of Installation using DBCA, three copies of the control file are automatically created and kept synchronized with each other.
- CONTROL_FILES INIT parameter contains all the location of control files. Here, any path is deleted means that control file is deleted.
- the root file that the database uses to find all the other files used by the database.
- have multiple identical copies.
- At the time of Installation using DBCA, three copies of the control file are automatically created and kept synchronized with each other.
- CONTROL_FILES INIT parameter contains all the location of control files. Here, any path is deleted means that control file is deleted.
Online Redo Log Files
- a set of two or more online redo log files, collectively known as the redo log.- a redo log is made up of redo entries, which are also called redo records.
- stores a copy of the changes made to data.
- two or more identical copies of the online redo log file can be maintained on different disks to protect against any failure.
- Log writer process (LGWR) writes redo log from memory buffer (Database Buffer Cache) until that group reach to the stiorage size limit or until manual log switch occur.
- The LGWR process then writes to the next log group.
- The LGWR process works in a circular fashion so that the oldest group is overwritten by the most recent redo records.
Archived Redo Log Files
- When archiving of the online redo logs is enable, Oracle Database copies the online redo log files to another location before they are overwritten.- Archived redo log files are required to recover a backup of the database from the time of the backup to the current time.
- configure the database to write archived redo log files to the fast recovery area.
Archive log Details:
SQL> show parameter log_archive;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string
log_archive_dest string
log_archive_dest_1 string
...
..
.
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_local_first boolean TRUE
log_archive_max_processes integer 4
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 8
How to Enable Archiving:
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
If now log switch command is executed then log switch will not occur.
i.e. SQL> alter system switch logfile;
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1770434560 bytes
Fixed Size 2229184 bytes
Variable Size 469765184 bytes
Database Buffers 1291845632 bytes
Redo Buffers 6594560 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
Now, execute -
SQL> alter system switch logfile;
Log switch will occur.
Rollback Segments
- used to track undo information for the database in earlier releases of Oracle Database.- Now, the preferred way of managing undo information is with the undo tablespace.
Note:
Oracle Database uses a SYSTEM rollback segment for performing system transactions. It is created automatically when the database is created, and is always brought online at instance startup. It is located in the SYSTEM tablespace. Not required to perform any operations to manage the SYSTEM rollback segment.
Data Files
- Data files are the operating system files that store the data within the database.- Tempfiles are a special class of data files that are associated only with temporary tablespaces.
- Data file components:
i. Segment: A segment contains a specific type of database object. E.g. a table is stored in a table segment, and an index is stored in an index segment. A data file can contain many segments.
ii. Extent: An extent is a contiguous set of data blocks within a segment.
iii. Data block: A data block, also called a database block, is the smallest unit of I/O to database storage. An extent consists of several contiguous data blocks. The database uses a default block size at database creation.
After the database has been created, it is not possible to change the default block size without re-creating the database.
Segments, extents, and data blocks are all logical structures. Only Oracle Database can determine how many data blocks are in a file. The operating system recognizes only files and operating system blocks, not the number of data blocks in an Oracle Database file. Each data block maps to one or more operating system blocks.
Tablespaces
- A database is divided into logical storage units called tablespaces, which group related logical structures (such as tables, views, and other database objects).- A tablespace consists of one or more physical data files.
- When you create an Oracle database, some tablespaces already exist, such as SYSTEM and SYSAUX.
Tablespaces included in the database:
EXAMPLE - contains the sample schemas that are included with Oracle Database.
SYSTEM - automatically created. Oracle Database uses it to manage the database. It contains the data dictionary, which is the central set of tables and views used as a read-only reference for a particular database. It also contains various tables and views that contain administrative information about the database.
These are all contained in the SYS schema, and can be accessed only by the SYS user or other administrative users with the required privilege.
SYSAUX - auxiliary tablespace to the SYSTEM tablespace. It includes Automatic Workload Repository, Oracle Streams, Oracle Text, and Database Control Repository.
TEMP - tablespace stores temporary data generated when processing SQL statements.
UNDOTBS1 - used by the database to store undo information.
USERS - tablespace is used to store permanent user objects and data.
You can create new tablespaces to support your user and application data requirements. During tablespace creation, you set the following parameters:
- Locally Managed Tablespaces
- Tablespace Types
- Tablespace Status
- Autoextend Tablespace
- Encrypted Tablespaces
Tablespace Types:
i. Permanent
ii. Undo
iii. Temporary
Tablespace Status:
i. Read Write
ii. Read Only
iii. Offline
Other Storage Structures
- can exist in an Oracle database include the initialization parameter file, the password file, and backup files.Initialization Parameter File (INIT File):
used by the Oracle instance at startup to determine the run-time properties and resources for the database.
Type of INIT Files
i. PFILE
ii.SPFILE
Password File:
- A database can use a password file to authenticate administrative users with SYSDBA or SYSOPER connection privileges.
- Required for remote connections to the database as SYSDBA or SYSOPER.
- These privileges enable a DBA to start and shut down the database and perform other high-level administrative tasks.
- Granting SYSDBA to a user adds that user to the password file automatically.
Backup Files:
Backup files are not technically database files, but are copies of the database in some form that can be used to recover the database if a failure causes loss of data.
....Continue.
No comments:
Post a Comment