Oracle is designed to be a very portable database; it is available on every platform, from Windows to UNIX to mainframes. For this reason, the physical architecture of Oracle looks different on different operating systems. Even though the physical mechanisms used to implement Oracle from platform to platform vary, the architecture is sufficiently generalized so that you can get a good understanding of how Oracle works on all platforms.
3.0 Database Server Architecture
The Oracle database server consists of two entities, an Oracle Instance and an Oracle Database.
Instance is a set of oracle background processes (on UNIX) or single threaded process (on Windows) and shared memory area, which is shared across those processes/threads running on a single computer. Thus an instance can exist without any disk storage at all.
Database is a collection of physical operating system files or disks in case of Automatic Storage Management (ASM) or RAW partitions.
The relationship between database and instance is that a database may be mounted and opened by many instances, while an instance may mount and open a single database in its entire lifetime.
The following figure depicts graphically the previous paragraphs.
Let’s verify it practically; login as oracle user and perform the following steps.
- Use the ps command to see all the processes being run by the user oracle (the Oracle software owner).
There are no Oracle server processes whatsoever at this point.
- Now start up SQL*Plus and connect AS SYSDBA.
- Again check process status our “instance” right now consists solely of the Oracle server process (oracleorcl).
- Start instance in NOMOUNT mode. In NOMOUNT mode, the instance has been started, but no connection has been made to a database.
- Now we have what we would call an “instance”. Through ps we can check the background processes that make up our instance.
- We can also verify the shared memory area allocated to the instance as:
- At this stage we don’t have any database i.e. no database is mounted and opened by our instance. We can verify it by querying the database as:
- To bring life into the database, alter the state of instance to MOUNT and then OPEN.
- We can use a simple query against some Oracle dynamic views, specifically V$DATAFILE, V$LOGFILE, and V$CONTROLFILE, to list the files that make up this database.
- Let’s check the relationship between instance and database. If we close this database and try to open it again, we’ll discover that we can’t. It’s because an instance can mount and open at most one database in its life. We must discard this instance and create a new one in order to open this or any other database.
3.1 Oracle Instance
When an oracle instance is started on a server, the Oracle software allocates a shared memory area called the System Global Area (SGA) and starts several Oracle background processes.
The following figure depicts the overall architecture of the Oracle Instance.
3.1.1 System Global Area
SGA contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance’s SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.
The SGA contains the following memory components:
- Database Buffer Cache
- Redo Log Buffer Cache
- Shared Pool
- Large Pool
- Java Pool
- Streams Pool
- Fixed SGA
The following dynamic views can be used to investigate the information abut SGA.
- V$SGAINFO
- V$SGA_DYNAMIC_COMPONENTS
- V$SGA_RESIZE_OPS
- V$SGA_TARGET_ADVICE
The following figure depicts the internals of SGA.
3.1.1.1 Database Buffer Cache
The database buffer cache is the portion of SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE
You can configure the database buffer cache with separate buffer pools that either keep data in the buffer cache or make the buffers available for new data immediately after using the data blocks.
- The DEFAULT ( DB_BLOCK_SIZE) buffer pool contains data blocks from schema objects that are not assigned to any buffer pool, as well as schema objects that are explicitly assigned to the DEFAULT pool
- The KEEP (DB_KEEP_CACHE_SIZE) buffer pool retains the schema object’s data blocks in memory.
- The RECYCLE (DB_RECYCLE_CACHE_SIZE) buffer pool eliminates data blocks from memory as soon as they are no longer needed.
Note: Multiple buffer pools are only available for the standard block size. Non-standard block size caches have a single DEFAULT pool.
The buffers can be in the form of:
- Free Buffers do not contain any useful data and are available for use.
- Pinned Buffers are currently being accessed.
- Dirty Buffers contain data that has been modified but has not yet been written to disk.
When configuring a new instance, it is impossible to know the correct size for the buffer cache. Typically, a database administrator makes a first estimate for the cache size, then runs a representative workload on the instance and examines the relevant statistics to see whether the cache is under or over configured.
3.1.1.2 Redo Log Buffer Cache
When a server process changes data in the data buffer cache (via an insert, a delete, or an update), it generates redo data, which is recorded in the redo log buffer. The log writer process (LGWR) writes redo information from the redo log buffer in memory to the redo log files on disk.
The initialization parameter LOG_BUFFER determines the size (in bytes) of the redo log buffer. In general, larger values reduce log file I/O, particularly if transactions are long or numerous. The default setting is either 512 kilobytes (KB) or 128 KB times the setting of the CPU_COUNT parameter, whichever is greater.
The redo log buffer is a circular buffer—the log writer process writes the redo entries from the redo log buffer to the redo log files, and server processes write new redo log entries over the entries that have been written to the redo log files. You only need to have a small redo log buffer, about 1MB or so. Large redo log buffers will reduce your log file I/O (especially if you have large or many transactions), but your commits will take longer as well.
3.1.1.3 Shared Pool
The shared pool portion of the SGA contains the library cache, the dictionary cache, buffers for parallel execution messages, and control structures. The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE. The default value of this parameter is 8MB on 32-bit platforms and 64MB on 64-bit platforms.
- Library Cache: The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.
- Dictionary Cache: The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. The data dictionary cache is also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data).
3.1.1.4 Large Pool
An optional memory area to provide large memory allocations for:
- Session memory for the shared server and the Oracle XA interface (used where transactions interact with more than one database).
- I/O server processes.
- Oracle backup and restore operations.
3.1.1.5 Java Pool
Java pool memory is used for all session-specific Java code and data within the JVM. In dedicated server mode, the Java pool includes the shared part of each Java class, which is actually used per session.
The parameter JAVA_POOL_SIZE is used to fix the amount of memory allocated to the Java pool for all session-specific Java code and data.
3.1.1.6 Streams Pool
The Streams pool is a new SGA structure starting in Oracle 10g. The Streams pool is used to buffer queue messages used by the Streams process as it is moving/copying data from one database to another. The Streams pool will only be important in systems using the Streams database feature. In those environments, it should be set in order to avoid “stealing” 10 percent of the Shared pool for this feature.
3.1.1.7 Fixed SGA
Fixed SGA contains a set of variables that point to other components of the SGA. It is like a bootstrap section of the SGA. This part is fixed for each release of oracle and can’t be altered by any parameter settings.
3.1.2 Oracle Processes
A process is a mechanism in an operating system that can run a series of steps. A process normally has its own private memory area in which it runs.
Oracle is a multiple-process (multi-user) database system which uses several processes to run different parts of the Oracle code and additional processes for the users—either one process for each connected user or one or more processes shared by multiple users.
The processes in an Oracle system can be categorized into two major groups:
- User Processes; run the application or oracle tool code.
- Oracle Server Processes; run the oracle database server code. They include Server Processes and Background Processes.
- With dedicated server configuration, Oracle will create a new dedicated process for incoming connection for each session. There is a one-to-one mapping between a connection to the database and a server process or thread.
- With shared server configuration, Oracle uses a pool of “shared processes” for a large community of users. A process (or set of processes) called dispatchers are used by the user process to talk to shared process.
When a user runs an application program (e.g. oracle form) or an Oracle tool (e.g. Enterprise Manager or SQL*Plus), Oracle creates a user process to run the user’s application. Two terms, Connection and Session are closely related to user process.
A connection is a physical path from a client to an Oracle instance. A connection is established either over a network (Oracle Net Services) when different computers run the database application and Oracle, and communicate through a network or over an IPC mechanism when a computer runs both the user process and Oracle.
A session, on the other hand, is a logical entity in the instance, where a user process can execute SQL and so on. Many independent sessions can be associated with a single connection, and these sessions can even exist independently of a connection. A connection may have zero, one, or more sessions established on it.
We can use the v$session dynamic view to see sessions in action.
Oracle server processes run the Oracle database server code. Again it can be categorized into two groups:
- Server Processes
- Background Processes
3.1.2.2.1 Server Processes
The server process is the process that services an individual user process. In some situations when the application and Oracle operate on the same computer, it is possible to combine the user process and corresponding server process into a single process to reduce system overhead. However, when the application and Oracle operate on different computers, a user process always communicates with Oracle through a separate server process.
The most common configuration for the server process is to assign each user a dedicated server process. However, Oracle provides for a more sophisticated means of servicing several users through the same server process, called the shared server architecture.
The following self-explanatory figures depict the dedicated and shared server configuration.
We can query the v$shared_server dynamic view to get information about the shared server process.
For server process the Oracle allocates a memory area called a program global area (PGA). PGA contains data and control information for server process. Unlike the SGA, the PGA is for the exclusive use of each server process and can’t be shared by multiple processes; consequently, the PGA is sometimes called the private global area.
The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance. You use automatic PGA memory management by setting the PGA_AGGREGATE_TARGET parameter.
The content of the PGA memory varies, depending on whether the instance is running the shared server option. But generally it can be:
- Session Memory: Session memory is the memory allocated to hold a session’s variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.
- Private SQL Area: Each session that issues a SQL statement has a private SQL area. For dedicated server, private SQL areas are located in the server process’s PGA. However, for shared server, part of the private SQL area is kept in the SGA. We can query the V$SQL_WORKAREA to get information about SQL area.
The following dynamic views can be used to investigate the information abut PGA.
- V$PGASTAT
- V$PROCESS
3.1.2.2.2 Background Processes
The background processes are the real workhorses of the Oracle instance—they enable large numbers of users to concurrently and efficiently use information stored in database. Each of the Oracle background processes is in charge of a separate task, thus increasing the efficiency of the database instance. These processes are automatically created by Oracle when you start the database instance, and they terminate when the database is shut down.
An Oracle instance can have many background processes; not all are always present. You can query the V$BGPROCESS view for more information on the background processes.
The background processes in an Oracle instance can include the following:
- Database Writer Process (DBWn)
- Log Writer Process (LGWR)
- Checkpoint Process (CKPT)
- System Monitor Process (SMON)
- Process Monitor Process (PMON)
- Recoverer Process (RECO)
- Archiver Processes (ARCn)
- Job Queue Processes
- Queue Monitor Processes (QMNn)
- Other Background Processes
Figure below illustrates how each background process interacts with the different parts of an Oracle database.
DBWn manages the buffer cache so that user processes can always find free buffers. The database
writer process writes dirty buffers to disk under the following conditions:
- When the database issues a checkpoint
- When a server process can’t find a clean reusable buffer
- Every 3 seconds
When a user commits a transaction, the transaction is assigned a system change number (SCN), which Oracle records along with the transaction’s redo entries in the redo log. SCNs are recorded in the redo log so that recovery operations can be synchronized in Real Application Clusters and distributed databases.
The log writer process writes the contents of the redo log buffer to disk under any of the following circumstances:
- Every three seconds
- Whenever someone commits
- When LGWR is asked to switch log files
- When the redo buffer gets one-third full or contains 1MB of cached redo log data
Checkpoint Process (CKPT): The checkpoint (CKPT) process is charged with telling the database writer process when to write the dirty data in the memory buffers to disk. After telling the database writer process to write the changed data, the checkpoint process updates the data file headers and the control file to indicate when the checkpoint was performed. The purpose of the checkpoint process is to synchronize the buffer cache information with the information on the database disks.
A checkpointing process involves the following steps:
A checkpointing process involves the following steps:
- Flushing the contents of the redo log buffers to the redo log files
- Writing a checkpoint record to the redo log file
- Flushing the contents of the database buffer cache to disk
- Updating the data file headers and the control files after the checkpoint completes
With Real Application Clusters, the SMON process of one instance can perform instance recovery for a failed CPU or instance.
Process Monitor Process (PMON): The process monitor (PMON) performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using.
PMON periodically checks the status of dispatcher and server processes, and restarts any that have stopped running (but not any that Oracle has terminated intentionally). PMON also registers information about the instance and dispatcher processes with the network listener. Like SMON, PMON checks regularly to see whether it is needed and can be called if another process detects the need for it.
Recoverer Process (RECO): The recoverer process (RECO) is used with the distributed database configuration that automatically resolves failures involving distributed transactions. The RECO process of a node automatically connects to other databases involved in an in-doubt distributed transaction. When the RECO process reestablishes a connection between involved database servers, it automatically resolves all in-doubt transactions, removing from each database’s pending transaction table any rows that correspond to the resolved in-doubt transactions.
Archiver Processes (ARCn): The archiver process (ARCn) copies redo log files to a designated storage device after a log switch has occurred. ARCn processes are present only when the database is in ARCHIVELOG mode, and automatic archiving is enabled.
An Oracle instance can have up to 10 ARCn processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of ARCn processes is insufficient to handle the workload. The alert log keeps a record of when LGWR starts a new ARCn process.
If you anticipate a heavy workload for archiving, such as during bulk loading of data, you can specify multiple archiver processes with the initialization parameter LOG_ARCHIVE_MAX_PROCESSES.
Job Queue Processes (Jnnn): Job queue processes are used for batch processing. They run user jobs. For each instance, these job queue processes are dynamically spawned by a coordinator job queue (CJQ0) background process.
The initialization parameter JOB_QUEUE_PROCESSES represents the maximum number of job queue processes that can concurrently run on an instance.
Queue Monitor Processes (QMNn): The queue monitor process is an optional background process for Oracle Streams Advanced Queuing, which monitors the message queues. You can configure up to 10 queue monitor processes.
Other Background Processes: There are several other background processes that might be running. These can include the following:
- Manageability Monitor (MMON) process collects several types of statistics to help the database manage itself. For example, MMON collects the Automatic Workload Repository (AWR) snapshot information, which is the basis for the performance diagnostics capability of the Automatic Database Diagnostic Monitor (ADDM).
- Memory Manager (MMAN) process coordinates the sizing of the memory components. It observes the system and workload in order to determine the ideal distribution of memory, and it ensures that the needed memory is available.
- Rebalance Master (RBAL) process coordinates disk rebalancing activity when you use an Automatic Storage Management (ASM) storage system.
- ASM Rebalance (ARBn) processes perform the disk rebalancing activity in an ASM instance.
- ASM Background (ASMB) process is present in all Oracle databases that use an ASM storage system. The ASMB process communicates with the ASM instance by logging into the ASM instance as a foreground process.
- Recovery Writer (RVWR) process writes the flashback data from the flashback buffer to the flashback logs.
The Oracle database has a logical layer and a physical layer. The physical layer consists of the files that reside on the disk; the components of the logical layer map the data to these physical components.
The separation of logical layer from physical layer is a necessary part of the relational database paradigm. The relational paradigm states that programmers should address only logical structures and let the database manage the mapping to physical structures. Thus database administrators see physical components while programmers see logical components.
The physical layer of the database consists of the following types of files:
- Data Files
- Control Files
- Online Redo Log Files
- Archive Log Files
- Parameter Files
- Trace Files
- Alert Files
- Password File
- Backup Files
- Flashback Log Files (Optional)
- Change Tracking Files (Optional)
The logical layer of the database consists of the following types of structures:
- Tablespaces
- Segments
- Extents
- Oracle Blocks
Note: A schema is a collection of database objects that are owned by a particular user. A schema has the same name as that user. Schema objects are the logical structures.
3.2.1 Physical Layer
3.2.1.1 Data Files
Data files are the files where all of the data of the database will be stored. Every database has at least two data file associated with it, and typically it will have many more than two. The first tablespace in any database is always the SYSTEM tablespace, so Oracle automatically allocates the first datafiles of any database for the SYSTEM tablespace during database creation.
A datafile can be associated with only one tablespace and only one database but a tablespace can spawn more than one datafile. If several disk drives are available to store the database, consider placing potentially contending datafiles on separate disks. This way, when users query information, both disk drives can work simultaneously, retrieving data at the same time.
Note: Datafiles should not be stored on the same disk drive that stores the database redo log files.
You can investigate about the data files by querying the following views:
- V$DATAFILE
- DBA_DATA_FILES
- V$TEMPFILE
- DBA_TEMP_FILES
3.2.1.2 Control Files
The database control file is a small binary file necessary for the database to start and operate successfully. The control file is critical to the functioning of the database, and recovery is difficult without access to an up-to-date control file. If all control files of a database are permanently lost during operation, then the instance is aborted and media recovery (type of recovery that takes a backup and applies redo) is required.
The control file contains the names and locations of the data files, redo log files, current log sequence numbers, backup set and backup piece details, check point information and the all-important system change number (SCN), which indicates the most recent version of committed changes in the database.
You specify control file names using the CONTROL_FILES initialization parameter in the database initialization parameter file. Every database has one control file, but due to the file’s importance, multiple identical copies (usually three) are maintained on a different physical disk—when the database writes to the control file, all copies of the file get written to. It is very important that you back up your control files. This is true initially, and every time you change the physical structure of your database.
The following views display information about control files:
- V$CONTROLFILE
- V$PARAMETER
3.2.1.3 Online Redo Log Files
The online redo log files record all the changes made to the database, and they are vital during the recovery of a database. The online redo log consists of groups of online redo log files, each file being known as a member. An Oracle database requires at least two groups of at least one member each to function.
These files are filled with redo records or redo entry. Redo entries record data that you can use to reconstruct all changes made to the database. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.
Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.
LGWR writes to redo log files in a circular fashion. When the current redo log file fills, LGWR begins writing to the next available redo log file. When the last available redo log file is filled, LGWR returns to the first redo log file and writes to it, starting the cycle again.
The point at which the database stops writing to one redo log file and begins writing to another is called log switching. Each online or archived redo log file is uniquely identified by its log sequence number. During crash, instance, or media recovery, the database properly applies redo log files in ascending order by using the log sequence number of the necessary archived and redo log files.
The following views provide information on redo logs.
- V$LOG
- V$LOGFILE
3.2.1.4 Archive Log Files
An archived redo log file is a copy of one of the filled members of an online redo log group. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving.
The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind. The LOG_ARCHIVE_MAX_PROCESSES initialization parameter specifies the num ARCn processes that the database initially invokes. The default is two processes.
If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST initialization parameter. If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n parameters). The two modes of transmitting archived logs to their destination are normal archiving transmission and standby transmission mode. Normal transmission involves transmitting files to a local disk. Standby transmission involves transmitting files through a network to either a local or remote standby database.
You can display information about the archived redo logs using the following sources:
- V$DATABASE
- V$ARCHIVE_DEST
- SQL*Plus command ARCHIVE LOG LIST
3.2.1.5 Parameter Files
There are many different parameter files associated with an Oracle database, from a tnsnames.ora file on a client workstation (used to find a server on the network), to a listener.ora file on the server (for the network listener startup), to the sqlnet.ora, cman.ora, and ldap.ora files, to name a few. The most important parameter file, however, is the database’s parameter file—without this, we cannot even get a database started.
By default the init<SID>.ora can be found in dbs (on Linux) or database folder (on windows) while the spfile is located in dbs directory on both platforms. It is not necessary that a parameter file must be in particular location, you can use the pfile=name option with startup command.
CREATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora';
CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora'
FROM PFILE='/u01/oracle/dbs/test_init.ora';
CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora'
FROM PFILE='/u01/oracle/dbs/test_init.ora';
In the event that the spfile has just “gone missing”, you can also restore the information for your parameter file from the database’s alert log. Every time you start the database, the alert log will contain a section having initialization parameter information.
You can display information about the archived redo logs using the following sources:
- V$PARAMETER
- V$PARAMETER2
- V$SPPARAMETER
- SQL*Plus command SHOW PARAMETERS
3.2.1.6 Trace Files
Each server and background process writes to an associated trace file. When a process detects an internal error, it dumps information about the error to its trace file. Trace files are a source of debugging information. Programmers who wrote the database kernel put the debugging code, and they left it in, on purpose.
All filenames of trace files associated with a process contain the name of the process that generated the trace file. The one exception to this is trace files generated by job queue processes (Jnnn).
Trace file is generated on the database server machine in one of two locations:
- If you are using a dedicated server connection, the trace file will be generated in the directory specified by the USER_DUMP_DEST parameter.
- If you are using a shared server connection, the trace file will be generated in the directory specified by the BACKGROUND_DUMP_DEST parameter.
You can display information about the trace files using the following sources:
- V$PARAMETER
- SQL*Plus command SHOW PARAMETER DUMP_DEST
3.2.1.7 Alert Files
The alert file also known as the alert log is the diary of the database. It is a simple text file written to from the day the database is created to until you erase it. In this file, you will find a chronological history of your database—the log switches; the internal errors that might be raised; when tablespaces were created, taken offline, put back online; and so on.
The alert log can come in handy during troubleshooting—it is usually the first place you should check to get an idea about what was happening inside the database when a problem occurred. In fact, Oracle support may ask you for a copy of the pertinent sections of the alert log during their analysis of database problems.
Oracle puts the alert log (alertdb_name.log) in the location specified for the BACKGROUND_DUMP_DEST initialization parameter.
- V$ALERT_TYPES
- DBA_OUTSTANDING_ALERTS
- DBA_ALERT_HISTORY
- SQL*Plus command SHOW PARAMETER background_dump; to find out where the alert log is located
Too see if there are any Oracle-related errors in your alert log, simply issue the following command:
3.2.1.8 Password File
The password file is a file in which you can specify the names of database users who have been granted the special SYSDBA or SYSOPER administrative privileges.
When you attempt to start up Oracle, there is no database available that can be consulted to verify passwords. When you start up Oracle on the “local” system (i.e., not over the network, but from the machine the database instance will reside on), Oracle will use the OS to perform the authentication. When Oracle was installed, the person performing the installation was asked to specify the “group” for the administrators. Normally on UNIX/Linux, this group will be dba by default and osdba on Windows. It can be any legitimate group name on that platform, however. That group is “special,” in that any user in that group can connect to Oracle “as SYSDBA” without specifying a username or password, for example:
However, suppose you wanted to perform these operations from another machine, over the network. In that case, you would attempt to connect using @tns-connect-string. However, this would fail:
OS authentication won’t work over the network for SYSDBA, even if the very unsafe parameter REMOTE_OS_AUTHENT is set to TRUE. Here comes the password file for rescue. For remote authentication, first, we have set the REMOTE_LOGIN_PASSWORDFILE.
Here EXCLUSIVE means only one database can uses a given password file. This setting cannot be changed dynamically while the instance is up and running, so we’ll have to restart for this to take effect.
The next step is to use the command-line tool named orapwd:
Now we can connect as SYSDBA over the network:
sqlplus sys/oracle@localhost.localdomain/orcl as sysdba
Password file resides in the $ORACLE_HOME/dbs directory on linux (%ORACLE_HOME%\database on windows).
The V$PWFILE_USERS view can be used to see the users who have been granted SYSDBA or SYSOPER system privileges for a database.
3.2.1.9 Backup Files
Backup files are used for database recovery. A backup is a copy of data. This copy can include important parts of the database, such as the control file and datafiles.
3.2.1.10 Flashback Log Files
3.2.1.11 Change Tracking Files
To turn off and remove the block change tracking file, you would use the ALTER DATABASE command once again:
Note: This command will in fact erase the block change tracking file. It does not just disable the feature it removes the file as well.
3.2.2 Logical Layer
3.2.2.1 Tablespaces
A database is divided into logical storage units called tablespaces, which group related logical structures (table, index etc) together. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace.
Tablespaces are divided into logical units of storage called segments, which are further divided into extents. The units of database space allocation are data blocks, extents, and segments.
There is no hard and fast rule regarding the number of tablespaces you can have in a database. The following five tablespaces are generally the default tablespaces that all databases must have, even though it’s possible to create and use a database with just the first two:
There is no hard and fast rule regarding the number of tablespaces you can have in a database. The following five tablespaces are generally the default tablespaces that all databases must have, even though it’s possible to create and use a database with just the first two:
- System Tablespace: It always contains the data dictionary tables for the entire database.
- Sysaux Tablespace: It is an auxiliary tablespace to the SYSTEM tablespace. The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace.
- Undo Tablespace: It is used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. In automatic undo management mode, each Oracle instance is assigned one (and only one) undo tablespace.
- Temporary Tablespace: It contains transient data that persists only for the duration of the session.
- Default Permanent Tablespace: It contains user objects.
- Locally Managed Tablespaces: extent management is done by the tablespace. It maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Changes do not generate rollback information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).
- Dictionary Managed Tablespaces: extent management is done by the data dictionary. Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse. Oracle also stores rollback information about each update of the dictionary tables.
The following data dictionary and dynamic performance views provide useful information about the tablespaces of a database.
- V$TABLESPACE
- DBA_TABLESPACES
- USER_TABLESPACES
3.2.2.2 Segments
A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. There are three types of segments in an Oracle database:
- Data Segments: A single data segment in an Oracle database holds all of the data for one of the following: table that is not partitioned or clustered, partition of a partitioned table, cluster of tables and a materialized view.
- Index Segments: Every non partitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data.
- Temporary Segments: When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle automatically allocates this disk space called a temporary segment. Typically, Oracle requires a temporary segment as a database area for sorting, temporary tables and there indexes.
The following data dictionary and dynamic performance views provide useful information about the segments.
- DBA_SEGMENTS
- USER_SEGMENTS
- V$SORT_SEGMENT
- V$TEMPSEG_USAGE
3.2.2.3 Extents
An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. One or more extents in turn make up a segment.
When you create a table, Oracle allocates to the table’s data segment an initial extent of a specified number of data blocks. Although no rows have been inserted yet, the Oracle data blocks that correspond to the initial extent are reserved for that table’s rows. If the data blocks of a segment’s initial extent become full and more space is required to hold new data, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment.
For maintenance purposes, the header block of each segment contains a directory of the extents in that segment. A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system.
You can display information about the extents using the following views:
- DBA_EXTENTS
- USER_EXTENTS
- DBA_FREE_SPACE
- USER_FREE_SPACE
3.2.2.4 Oracle Blocks
Extents, in turn, consist of Oracle blocks. An Oracle block is the smallest unit of space allocation in Oracle. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.
The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify up to five nonstandard block sizes. The data block sizes should be a multiple of the operating system’s block size within the maximum limit to avoid unnecessary I/O.
The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data. Figure below illustrates the format of a data block.
- Header (Common and Variable): The header contains general block information, such as the block address and the type of segment for example, data or index.
- Table Directory: This portion of the data block contains information about the table having rows in this block.
- Row Directory: This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area). After the space has been allocated in the row directory of a data block’s overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to have 100 bytes allocated in the header for the row directory. Oracle reuses this space only when new rows are inserted in the block.
- Row Data: This portion of the data block contains table or index data. Rows can span blocks.
- Free Space: Free space is allocated for insertion of new rows and for updates to rows that require additional space. In data blocks allocated for the data segment of a table or cluster, or for the index segment of an index, free space can also hold transaction entries. A transaction entry is required in a block for each INSERT, UPDATE, DELETE, and SELECT...FOR UPDATE statement accessing one or more rows in the block.
In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases is unavoidable.
0 comments:
Post a Comment