An Oracle server consists of both a database and an instance. In Oracle terminology, database refers to only the physical files on disk. These are the files thatstore the data itself, the database state information in the control file, and the changes made to the data in the redo log files. The term instance refers to the Oracle processes and memory structures that reside in the server's memory and access an Oracle database on disk. One of the reasons for separating the concepts of a database and an instance is that a database may be shared by two or more different Oracle instances as part of an Oracle configuration that enhances the scalability, performance, and reliability of the Oracle server.
Database The collection of all physical files on disk that are associated with a single Oracle instance.
It's also important to differentiate between the logical and physical structures of the database. The logical structures represent components such as tables—what you normally see from a user's point of view. The physical structures are the underlying storage methods on disk—the physical files that compose the database.
Instance The collection of memory structures and Oracle background processes that operates against an Oracle database.
Logical Storage Structures
The Oracle database is divided into increasingly smaller logical units to manage, store, and retrieve data efficiently and quickly. The illustration below shows the relationships between the logical structures of the database: tablespaces, segments, extents, and blocks.
Logical Structures Structures in an Oracle database that a database user would see, such as atable, as opposed to the underlying physical structures at the datafile level.
The logical storage management of the database's data is independent of the physical storage of the database's physical files on disk. This makes it possible for changes to the physical structures to be transparent to the database user at the logical level.
A tablespace is the highest level of logical objects in the database. A database consists of one or more tablespaces. A tablespace will frequently group together similar objects, such as tables, for a specific business area or a specific function. A particular tablespace can be reorganized, backed up, and so forth with minimal impact to other users whose data may be in other tablespaces.
Tablespace A logical grouping of database objects, usually to facilitate security, performance, or the availability of data base objects such as tables and indexes. A table space is composed of one or more data files on disk.
All Oracle databases must have at least two tablespaces: the SYSTEM tablespace and the SYSAUX tablespace. Having more than just the SYSTEM and SYSAUX tablespaces is highly recommended when creating a database. In the illustration of logical structures, you can see the SYSTEM tablespace, the SYSAUX tablespace, and two others. Oracle's Database Configuration Assistant, discussed later in this chapter, creates a total of six tablespaces for a default installation of Oracle 10g.
A tablespace is further broken down into segments. A database segment is a type of object that a user typically sees, such as a table. Tablespace 1 in the logical structure illustration consists of three segments, which could be tables, indexes, and so forth. It's important to note that this is the logical representation of these objects; the physical representation of these objects in the operating system files will most likely not resemble the logical representation.
segment A set of extents allocated for a single type of object, such as a table.
The next-lowest logical grouping in a database is the extent. A segment groups one or more extents allocated for a specific type of object in the database. Segment 2 in the logical structure illustration consists of two extents. Note that an extent cannot be shared between two segments. Also, a segment, and subsequently an extent, cannot cross a tablespace boundary.
extent A contiguous group of blocks allocated foruse as part of a table, index, and so forth.
At the other end of the spectrum of logical objects is the database block (also known as an Oracle block), the smallest unit of storage in an Oracle database. Every database block in a tablespace has the same number of bytes. Starting with Oracle9i, different tablespaces within a database can have database blocks with different sizes. Typically, one or more rows of a table will reside in a database block, although very long rows may span several database blocks.
Database Block The smallest unit of allocation in an Oracle database. One or more database blocks compose a database extent.
Extents group together logically contiguous database blocks in a tablespace. All database blocks within a single extent will store the same kind of information.
A database block can have a size of 2KB, 4KB, 8KB, 16KB, or 32KB. Once any tablespace, including the SYSTEM and SYSAUX tablespaces, is created with a given block size, it cannot be changed. If you want the tablespace to have a larger or smaller block size, you need to create a new tablespace with the new block size, move the objects from the old tablespace to the new tablespace, and then drop the old tablespace.
A schema is another logical structure that can classify or group database objects. A schema has a one-to-one correspondence with a user account in the Oracle database, although some schemas may be designed to hold only objects that may be referenced by other database users. For instance, in the logical structure illustration, Segments 1 and 3 may be owned by the HR schema, while Segment 2 may be owned by the SCOTT schema.
schema A named group of objects associated with a particular user account, such as tables, indexes, functions, and so forth.
A schema is not directly related to a tablespace or any other logical storage structure; the objects that belong to a schema may be in many different tablespaces. Conversely, a tablespace may hold objects for many different schemas. A schema is a good way to group objects in the database for purposes ofsecurity and access control.
Physical Storage Structures
From the perspective of building queries and running reports, regular users don't need to know much about the underlying physical structure of the database on disk. However, DBAs do need to understand these database components.
The physical structure of the Oracle database consists of datafiles, redo log files, and control files. On a day-to-day basis, the DBA will deal most often with the datafiles, since this is where all of the user and system objects, such as tables and indexes, are stored. The illustration below shows the physical structure and its relationship to the Oracle memory structures and logical storage structures.
Physical Structures Structures of an Oracle database, such as datafiles on disk, that are not directly manipulated by users of the database. Physical structures exist at the operating system level.
The datafiles in a database contain all of the database data that the users of the database save and retrieve. A single datafile is an operating system file on the server's disk. Each datafile belongs to only one tablespace; a tablespace can have many datafiles associated with it.
datafiles Files that contain all of the database data that the users of the database save and retrieve using SELECT and other DML statements. A tablespace comprises one or more datafiles.
There are five physical datafiles in the database in the physical structure illustration: one is used for the SYSTEM tablespace, one is used for the SYSAUX tablespace, two datafiles are assigned to Tablespace 1, and the fifth datafile isassigned to Tablespace 2.
Redo Log Files
The redo log files facilitate the Oracle mechanism to recover from an instance failure or a media failure. When any changes are made to the database, such as updates to data or creating or dropping database objects, the changes are recorded to the redo log files first. A database has at least two redo log files, and it is recommended that multiple copies of the redo log files be stored on different disks. (Oracle automatically keeps the multiple copies in synch.) If the instance fails, any changed database blocks that were not yet written to the datafiles are retrieved from the redo log files and written to the datafiles when the instance is started again.
redo log files Files that contain a record of all changes made to the data in both tables and indexes as well as changes to the database structures themselves. These files are used to recover changed data that was in memory at the time of a crash.
The control file maintains information about the physical structure of the entire database. It stores the name of the database, the names and locations of the tablespaces in the database, the locations of the redo log files, information about the last backup of each tablespace in the database, and much more. Because of the importance of this file, it is recommended that a copy of the control file reside on at least three different physical disks. As with the redo log files, Oracle keeps all copies of the control file in synch automatically.
control file A file that records the physical structure of a database, the database name, and the names and locations of datafiles and redo log files.
The control file and redo log file contents do not map directly to any database objects, but their contents and status are available to the DBA by accessing virtual tables called data dictionary views, which are owned by the SYS schema.
Oracle Memory Structures
The memory allocated to Oracle includes the following types of data:
This information is stored in three major areas: the System Global Area (SGA), the Program Global Area (PGA), and the Software Code Area.
The overall memory allocated to Oracle can be divided into two categories: shared memory and nonshared memory. The SGA and the Software Code Area are shared among all database users. The PGA is considered nonshared. There is one dedicated PGA allocated for each user connected to the database.
System Global Area
The System Global Area (SGA) is the memory area that is shared by all connected users of the database. The SGA is broken down into many areas. We will discuss the areas that hold cached data blocks from database tables, recently executed SQL statements, and information on recent structural and data changes in the database. These areas are known as the database buffer cache, the shared pool, and the redo log buffer, respectively.
System Global Area (SGA) A group of shared memory structures for a single Oracle instance.
Database Buffer Cache
The database buffer cache holds copies of database blocks that have been recently read from or written to the database datafiles. The data cached here primarily includes table and index data, along with data that supports ROLLBACK statements.
Database Buffer Cache The memory structure in the SGA that holds the most recently used or written blocks of data.
Any database block can be in one of three states: dirty, free, or pinned.
LRU (least recently used) algorithm An algorithm used to determine when to reuse buffers in the database buffer cache that are not dirty or pinned. The less frequently a block is used, the more likely it is to be replaced with a new database block read from disk.
The shared pool contains recently used SQL and PL/SQL statements (stored procedures and functions). It also contains data from system tables (the data dictionary tables), such as character set information and security information. Because objects such as PL/SQL stored functions can be cached in the shared pool, another user or process that needs the same stored functions can benefit from the performance improvement because of the stored function already being in memory.
shared PoolAn area in the SGA that contains cached SQL and PL/SQL statements and cached tables owned by SYS.
Redo Log Buffer A buffer in the SGA that contains information pertaining to changes in the database.
Redo Log Buffer
The redo log buffer keeps the most recent information regarding changes to the database resulting from SQL statements. The blocks in this buffer are eventually written to the online redo log files, which are used to recover, or redo, all recent changes to the database after a failure.
Program Global Area
The Program Global Area (PGA) belongs to one user process or connection to the database and is therefore considered nonsharable. It contains information specific to the session, and it can include sort space and information on the state of any SQL or PL/SQL statements that are currently active by the connection.
Software Code Area
The Software Code Area is a shared area containing the Oracle program code or executables against the database. It can be shared by multiple database instances running against the same or different databases, and as a result, it saves a significant amount of memory on the server.
Software Code Area A location in memory where the Oracle application software resides. The Software Code Area can be shared among several Oracle instances.
A process on a server is a section of a computer program in memory that performs a specific task. When the Oracle server starts, multiple processes are started on the server to perform various functions as part of the Oracle instance. While a detailed discussion of all Oracle background processes is beyond the scope of this book, we will discuss a few of the key processes: Database Writer (DBWn), Log Writer (LGWR), and System Monitor (SMON). These processes communicate with various areas of the SGA, such as the database buffer cache and the redo log buffer, as indicated in the earlier illustration.
Process An executing computer program in memory that performs a specific task.
Database Writer (DBWn)
There may be anywhere from one to 20 copies (DBW0 through DBW9 and DBWa through DBWj) of the Database Writer process running in an Oracle instance. As noted earlier in the section on the SGA, new and modified data is stored in buffers in the database buffer cache, which are marked as dirty buffers. At some point (for example, when the number of free buffers is low), these buffers need to be written out to disk, which is what the DBWn process does, allowing subsequent SELECT statements and other DML statements access to those buffers in the buffer cache.
Note: Program Global Area (PGA)
A nonshared area of memory used for storing all connection information, including SQL statement information, in a dedicated server configuration for a user who is connected to the database. In a shared server configuration, a large portion of the memory for each connection is stored in the SGA instead of the PGA.
If there is enough memory and the demand on the system is high, having more than one copy of this process may dramatically improve the performance and reduce the response time when a query or DML statement is run.
Log Writer (LGWR)
The Log Writer process writes the buffers in the SGA's redo log buffer out to disk to the redo log files. The Log Writer process must be able to write redo log buffers fast enough to make sure that there is room in the redo log buffer for entries from new transactions. By writing all changes to the database to the redo logfiles, the changes made to the database can be recovered by reissuing the commands in the logs if an instance failure occurs.
Log Writer writes under a variety of conditions: when a user issues a COMMIT, when the redo log buffer is one-third full, when DBWn writes dirty buffers, or every three seconds.
System Monitor (SMON)
SMON performs a number of different functions in the database. If there is a system crash, the SMON process will apply the changes in the redo log files (saved to disk previously by the LGWR process) to the datafiles the next time the instance is started. This ensures that no committed transactions are lost because of the system crash. (SMON also performs a number of other tasks that are beyond the scope of this book.)
Oracle DBA Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Oracle 11g Interview Questions|
|Oracle apps Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle ADF Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps Functional Interview Questions|
|Oracle Apps DBA Interview Questions||Oracle Workflow Interview Questions|
Oracle Dba Tutorial
Relational Database Concepts
Sql*plus And Isql*plus Basics
Oracle Database Functions
Restricting, Sorting, And Grouping Data
Using Multiple Tables
Advanced Sql Queries
Installing Oracle And Creating A Database
Creating And Maintaining Database Objects
Users And Security
Making Things Run Fast (enough)
Saving Your Stuff (backups)
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.