| Oracle7 Server Concepts Manual |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Server Concepts Manual | Library |
Product |
Contents |
Index |
Shakespeare: The Merchant of Venice
This chapter is an overview of the Oracle Server. It presents information that will help you use the rest of this book. It includes:
client/server (distributed processing) environments
To take full advantage of a given computer system or network, Oracle allows processing to be split between the database server and the client application programs. The computer running the database management system handles all of the database server responsibilities while the workstations running the database application concentrate on the interpretation and display of data.
large databases and space management
Oracle supports the largest of databases, potentially terabytes in size. To make efficient use of expensive hardware devices, it allows full control of space usage.
many concurrent database users
Oracle supports large numbers of concurrent users executing a variety of database applications operating on the same data. It minimizes data contention and guarantees data concurrency.
high transaction processing performance
Oracle maintains the preceding features with a high degree of overall system performance. Database users do not suffer from slow processing performance.
high availability
At some sites, Oracle works 24 hours per day with no down time to limit database throughput. Normal system operations such as database backup and partial computer system failures do not interrupt database use.
controlled availability
Oracle can selectively control the availability of data, at the database level and sub-database level. For example, an administrator can disallow use of a specific application so that the application's data can be reloaded, without affecting other applications.
openness, industry standards
Oracle adheres to industry accepted standards for the data access language, operating systems, user interfaces, and network communication protocols. It is an "open" system that protects a customer's investment.
Release 7.3 of the Oracle Server has been certified by the U.S. National Institute of Standards and Technology as 100% compliant with Entry Level of the ANSI/ISO SQL92 (Structured Query Language) standard. Oracle fully satisfies the requirements of the U.S. Government's FIPS127-2 standard and includes a "flagger" to highlight non-standard SQL usage.
Also, Oracle7 has been evaluated by the U.S. Government's National Computer Security Center (NCSC) as compliant with the Orange Book security criteria; the Oracle7 Server and Trusted Oracle7 comply with the C2 and B1 Orange Book levels, respectively, as well as with comparable European ITSEC security criteria.
Oracle also supports the Simple Network Management Protocol (SNMP) standard for system management. This protocol allows administrators to manage heterogeneous systems with a single administration interface.
manageable security
To protect against unauthorized database access and use, Oracle provides fail-safe security features to limit and monitor data access. These features make it easy to manage even the most complex design for data access.
database enforced integrity
Oracle enforces data integrity, "business rules" that dictate the standards for acceptable data. As a result, the costs of coding and managing checks in many database applications are eliminated.
distributed systems
For networked, distributed environments, Oracle combines the data physically located on different computers into one logical database that can be accessed by all network users. Distributed systems have the same degree of user transparency and data consistency as non-distributed systems, yet receive the advantages of local database management.
Oracle also offers the heterogeneous option that allows users to access data on some non-Oracle databases transparently.
portability
Oracle software is ported to work under different operating systems. Applications developed for Oracle can be ported to any operating system with little or no modification.
compatibility
Oracle software is compatible with industry standards, including most industry standard operating systems. Applications developed for Oracle can be used on virtually any system with little or no modification.
connectibility
Oracle software allows different types of computers and operating systems to share information across networks.
replicated environments
Oracle software lets you replicate groups of tables and their supporting objects to multiple sites. Oracle supports replication of both data- and schema-level changes to these sites. Oracle's flexible replication technology supports basic primary site replication as well as advanced dynamic and shared-ownership models.
The following sections provide a comprehensive overview of the Oracle architecture. Each major section describes a different part of the overall architecture.
You can define and manipulate data in a table with SQL commands. You use data definition language (DDL) commands to set up the data. DDL commands include commands to creating and altering databases and tables.
You can update, delete, or retrieve data in a table with data manipulation commands (DML). DML commands include commands to alter and fetch dat. The most common SQL command is the SELECT command, which allows you to retrieve data from the database.
In addition to SQL commands, the Oracle Server has a procedural language called PL/SQL. PL/SQL enables the programmer to program SQL statements. It allows you to control the flow of a SQL program, to use variables, and to write error-handling procedures.
Physical Database Structure An Oracle database's physical structure is determined by the operating system files that constitute the database. Each Oracle database is made of three types of files: one or more datafiles, two or more redo log files, and one or more control files. The files of an Oracle database provide the actual physical storage for database information.
Logical Database Structure An Oracle database's logical structure is determined by
Figure 1 - 1. An Oracle Instance
An Oracle instance has two types of processes: user processes and Oracle processes.
A user process executes the code of an application program (such as an Oracle Forms application) or an Oracle Tool (such as Server Manager).
Oracle processes are server processes that perform work for user processes and background processes that perform maintenance work for the Oracle Server.
![[*]](jump.gif)
.
Structures
Structures are well-defined objects (such as tables, views, indexes, and so on) that store or access the data of a database. Structures and the data contained within them can be manipulated by operations.
Operations
Integrity Rules
Integrity rules are the laws that govern which operations are allowed on the data and structures of a database. Integrity rules protect the data and the structures of a database.
Relational database management systems offer benefits such as
Databases, Tablespaces, and Datafiles The relationship among databases, tablespaces, and datafiles (datafiles are described in the next section) is illustrated in Figure 1 - 2.
Figure 1 - 2. Databases, Tablespaces, and Datafiles
This figure illustrates the following:
Tables A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user-accessible data.
Table data is stored in rows and columns. Every table is defined with a table name and set of columns. Each column is given a column name, a datatype (such as CHAR, DATE, or NUMBER), and a width (which may be predetermined by the datatype, as in DATE) or scale and precision (for the NUMBER datatype only). Once a table is created, valid rows of data can be inserted into it. The table's rows can then be queried, deleted, or updated.
To enforce defined business rules on a table's data, integrity constraints and triggers can also be defined for a table. For more information, see "Data Integrity" .
Views A view is a custom-tailored presentation of the data in one or more tables. A view can also be thought of as a "stored query".
Views do not actually contain or store data; rather, they derive their data from the tables on which they are based, referred to as the base tables of the views. Base tables can in turn be tables or can themselves be views.
Like tables, views can be queried, updated, inserted into, and deleted from, with restrictions. All operations performed on a view actually affect the base tables of the view.
Views are often used to do the following:
Sequences A sequence generates a serial list of unique numbers for numeric columns of a database's tables. Sequences simplify application programming by automatically generating unique numerical values for the rows of a single table or multiple tables.
For example, assume two users are simultaneously inserting new employee rows into the EMP table. By using a sequence to generate unique employee numbers for the EMPNO column, neither user has to wait for the other to input the next available employee number. The sequence automatically generates the correct values for each user.
Sequence numbers are independent of tables, so the same sequence can be used for one or more tables. After creation, a sequence can be accessed by various users to generate actual sequence numbers.
Program Units The term "program unit" is used in this manual to refer to stored procedures, functions, and packages.
Note: The information in this section applies only to Oracle with the procedural option installed (PL/SQL.).
A procedure or function is a set of SQL and PL/SQL (Oracle's procedural language extension to SQL) statements grouped together as an executable unit to perform a specific task. For more information about SQL and PL/SQL, see "Data Access" .
Procedures and functions allow you to combine the ease and flexibility of SQL with the procedural functionality of a structured programming language. Using PL/SQL, such procedures and functions can be defined and stored in the database for continued use. Procedures and functions are identical, except that functions always return a single value to the caller, while procedures do not return a value to the caller.
Packages provide a method of encapsulating and storing related procedures, functions, and other package constructs together as a unit in the database. While packages provide the database administrator or application developer organizational benefits, they also offer increased functionality and database performance.
Synonyms A synonym is an alias for a table, view, sequence, or program unit. A synonym is not actually an object itself, but instead is a direct reference to an object. Synonyms are used to
Indexes, Clusters, and Hash Clusters Indexes, clusters, and hash clusters are optional structures associated with tables, which can be created to increase the performance of data retrieval.
Indexes are created to increase the performance of data retrieval. Just as the index in this manual helps you locate specific information faster than if there were no index, an Oracle index provides a faster access path to table data. When processing a request, Oracle can use some or all of the available indexes to locate the requested rows efficiently. Indexes are useful when applications often query a table for a range of rows (for example, all employees with a salary greater than 1000 dollars) or a specific row.
Indexes are created on one or more columns of a table. Once created, an index is automatically maintained and used by Oracle. Changes to table data (such as adding new rows, updating rows, or deleting rows) are automatically incorporated into all relevant indexes with complete transparency to the users.
Indexes are logically and physically independent of the data. They can be dropped and created any time with no effect on the tables or other indexes. If an index is dropped, all applications continue to function; however, access to previously indexed data may be slower.
Clusters are an optional method of storing table data. Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.
The related columns of the tables in a cluster are called the cluster key. The cluster key is indexed so that rows of the cluster can be retrieved with a minimum amount of I/O. Because the data in a cluster key of an index cluster (a non-hash cluster) is stored only once for multiple tables, clusters may store a set of tables more efficiently than if the tables were stored individually (not clustered). Figure 1 - 3 illustrates how clustered and non-clustered data is physically stored.
Clusters also can improve performance of data retrieval, depending on data distribution and what SQL operations are most often performed on the clustered data. In particular, clustered tables that are queried in joins benefit from the use of clusters because the rows common to the joined tables are retrieved with the same I/O operation.
Like indexes, clusters do not affect application design. Whether or not a table is part of a cluster is transparent to users and to applications. Data stored in a clustered table is accessed via SQL in the same way as data stored in a non-clustered table.
Hash clusters also cluster table data in a manner similar to normal, index clusters (clusters keyed with an index rather than a hash function). However, a row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stored together on disk.
Hash clusters are a better choice than using an indexed table or index cluster when a table is often queried with equality queries (for example, return all rows for department 10). For such queries, the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.
Database Links A database link is a named object that describes a "path" from one database to another. Database links are implicitly used when a reference is made to a global object name in a distributed database. Also see "Distributed Databases" .
Figure 1 - 3. Clustered and Unclustered Tables
Oracle Data Blocks At the finest level of granularity, an Oracle database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. A data block size is specified for each Oracle database when the database is created. A database uses and allocates free database space in Oracle data blocks.
Extents The next level of logical database space is called an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.
Segments The level of logical database storage above an extent is called a segment. A segment is a set of extents allocated for a certain logical structure. For example, the different types of segments include the following:
Data Segment
Index Segment
Each index has an index segment that stores all of its data.
Rollback Segment
One or more rollback segments are created by the database administrator for a database to temporarily store "undo" information. This information is used:
.
.
Temporary Segment
Oracle dynamically allocates space when the existing extents of a segment become full. Therefore, when the existing extents of a segment are full, Oracle allocates another extent for that segment as needed. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.
The following are characteristics of datafiles:
Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and increase performance, data is pooled in memory and written to the appropriate datafiles all at once, as determined by the DBWR background process of Oracle. (For more information about Oracle's memory and process structures and the algorithm for writing database data to the datafiles, see "Oracle Server Architecture" .)
Redo log files are critical in protecting a database against failures. To protect against a failure involving the redo log itself, Oracle allows a multiplexed redo log so that two or more copies of the redo log can be maintained on different disks.
The Use of Redo Log Files The information in a redo log file is used only to recover the database from a system or media failure that prevents database data from being written to a database's datafiles.
For example, if an unexpected power outage abruptly terminates database operation, data in memory cannot be written to the datafiles and the data is lost. However, any lost data can be recovered when the database is opened, after power is restored. By applying the information in the most recent redo log files to the database's datafiles, Oracle restores the database to the time at which the power failure occurred.
The process of applying the redo log during a recovery operation is called rolling forward. See "Database Backup and Recovery" .
The Use of Control Files Every time an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. If the physical makeup of the database is altered (for example, a new datafile or redo log file is created), the database's control file is automatically modified by Oracle to reflect the change.
A database's control file is also used if database recovery is necessary. See "Database Backup and Recovery" .
Figure 1 - 4 shows a typical variation of the Oracle Server memory and process structures.
Figure 1 - 4. Memory Structures and Processes of Oracle
for information about the Oracle background processes and "An Oracle Instance" for information about Oracle instances.)Oracle allocates the system global area when an instance starts and deallocates it when the instance shuts down. Each instance has its own system global area.
Users currently connected to an Oracle Server share the data in the system global area. For optimal performance, the entire system global area should be as large as possible (while still fitting in real memory) to store as much data in memory as possible and minimize disk I/O. The information stored within the system global area is divided into several types of memory structures, including the database buffers, redo log buffer, and the shared pool. These areas have fixed sizes and are created during instance startup.
Database Buffer Cache Database buffers of the system global area store the most recently used blocks of database data; the set of database buffers in an instance is the database buffer cache. These buffers can contain modified data that has not yet been permanently written to disk. Because the most recently (and often the most frequently) used data is kept in memory, less disk I/O is necessary and performance is increased.
Redo Log Buffer The redo log buffer of the system global area stores redo entries -- a log of changes made to the database. The redo entries stored in the redo log buffers are written to an online redo log file, which is used if database recovery is necessary. Its size is static.
Shared Pool The shared pool is a portion of the system global area that contains shared memory constructs such as shared SQL areas. A shared SQL area is required to process every unique SQL statement submitted to a database (see "SQL Statements" 1-24). A shared SQL area contains information such as the parse tree and execution plan for the corresponding statement. A single shared SQL area is used by multiple applications that issue the same statement, leaving more shared memory for other uses.
Cursors A cursor is a handle (a name or pointer) for the memory associated with a specific statement. Although most Oracle users rely on the automatic cursor handling of the Oracle utilities, the programmatic interfaces offer application designers more control over cursors. For example, in precompiler application development, a cursor is a named resource available to a program and can be specifically used for the parsing of SQL statements embedded within the application. The application developer can code an application so that it controls the phases of SQL statement execution and thus improve application performance.
An Oracle Server has two general types of processes: user processes and Oracle processes.
Server Processes Oracle creates server processes to handle requests from connected user processes. A server process is in charge of communicating with the user process and interacting with Oracle to carry out requests of the associated user process. For example, if a user queries some data that is not already in the database buffers of the system global area, the associated server process reads the proper data blocks from the datafiles into the system global area.
Oracle can be configured to vary the number of user processes per server process. In a dedicated server configuration, a server process handles requests for a single user process. A multi-threaded server configuration allows many user processes to share a small number of server processes, minimizing the number of server processes and maximizing the utilization of available system resources.
On some systems, the user and server processes are separate, while on others they are combined into a single process. If a system uses the multi-threaded server or if the user and server processes run on different machines, the user and server processes must be separate. Client/server systems separate the user and server processes and execute them on different machines.
Background Processes Oracle creates a set of background processes for each instance. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user process. The background processes asynchronously perform I/O and monitor other Oracle processes to provide increased parallelism for better performance and reliability.
An SGA and the Oracle background processes constitute an Oracle instance. (See "System Global Area (SGA)" for information about the system global area and "An Oracle Instance" for information about Oracle instances.)
Each Oracle instance may use several background processes. The names of these processes are DBWR, LGWR, CKPT, SMON, PMON, ARCH, RECO, Dnnn and LCKn. Each background process is described in the following sections.
Database Writer (DBWR) The Database Writer writes modified blocks from the database buffer cache to the datafiles. Because of the way Oracle performs logging, DBWR does not need to write blocks when a transaction commits (see "Transactions" ). Instead, DBWR is optimized to minimize disk writes. In general, DBWR writes only when more data needs to be read into the system global area and too few database buffers are free. The least recently used data is written to the datafiles first.
Log Writer (LGWR) The Log Writer writes redo log entries to disk. Redo log data is generated in the redo log buffer of the system global area. As transactions commit and the log buffer fills, LGWR writes redo log entries into an online redo log file.
Checkpoint (CKPT) At specific times, all modified database buffers in the system global area are written to the datafiles by DBWR; this event is called a checkpoint. The Checkpoint process is responsible for signalling DBWR at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint. CKPT is optional; if CKPT is not present, LGWR assumes the responsibilities of CKPT.
System Monitor (SMON) The system monitor performs instance recovery at instance startup. In a multiple instance system (one that uses the Parallel Server), SMON of one instance can also perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online. SMON also coalesces free extents within the database to make free space contiguous and easier to allocate.
Process Monitor (PMON) The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the cache and freeing resources that the process was using. PMON also checks on dispatcher (see below) and server processes and restarts them if they have failed.
Archiver (ARCH) The archiver copies the online redo log files to archival storage when they are full. ARCH is active only when a database's redo log is used in ARCHIVELOG mode. (See "The Redo Log" ).
Recoverer (RECO) The recoverer is used to resolve distributed transactions that are pending due to a network or system failure in a distributed database. At timed intervals, the local RECO attempts to connect to remote databases and automatically complete the commit or rollback of the local portion of any pending distributed transactions.
Dispatcher (Dnnn) Dispatchers are optional background processes, present only when a multi-threaded server configuration is used. At least one dispatcher process is created for every communication protocol in use (D000, . . ., Dnnn). Each dispatcher process is responsible for routing requests from connected user processes to available shared server processes and returning the responses back to the appropriate user processes.
Lock (LCKn) Up to ten lock processes (LCK0, . . ., LCK9) are used for inter-instance locking when the Oracle Parallel Server is used; see "The Oracle Parallel Server: Multiple Instance Systems" for more information about this configuration.
SELECT ename, deptno FROM emp;
Only a complete SQL statement can be executed, whereas a sentence fragment, such as the following, generates an error indicating that more text is required before a SQL statement can execute:
SELECT ename
A SQL statement can be thought of as a very simple, but powerful, computer program or instruction.
SQL statements are divided into the following categories:
.)Data Manipulation Statements (DML) DML statements manipulate the database's data. For example, querying, inserting, updating, and deleting rows of a table are all DML operations; locking a table or view and examining the execution plan of an SQL statement are also DML operations.
Transaction Control Statements Transaction control statements manage the changes made by DML statements. They allow the user or application developer to group changes into logical transactions. (See "Transactions" ) Examples include COMMIT, ROLLBACK, and SAVEPOINT.
Session Control Statements Session control statements allow a user to control the properties of his current session, including enabling and disabling roles and changing language settings. The two session control statements are ALTER SESSION and SET ROLE.
Embedded SQL Statements Embedded SQL statements incorporate DDL, DML, and transaction control statements in a procedural language program (such as those used with the Oracle Precompilers). Examples include OPEN, CLOSE, FETCH, and EXECUTE.
Consider a banking database. When a bank customer transfers money from a savings account to a checking account, the transaction might consist of three separate operations: decrease the savings account, increase the checking account, and record the transaction in the transaction journal.
Figure 1 - 5 illustrates the banking transaction example.
Figure 1 - 5. A Banking Transaction
Committing a transaction makes permanent the changes resulting from all SQL statements in the transaction. The changes made by the SQL statements of a transaction become visible to other user sessions' transactions that start only after the transaction is committed.
Rolling back a transaction retracts any of the changes resulting from the SQL statements in the transaction. After a transaction is rolled back, the affected data is left unchanged as if the SQL statements in the transaction were never executed.
By using savepoints, you can arbitrarily mark your work at any point within a long transaction. This allows you the option of later rolling back all work performed from the current point in the transaction to a declared savepoint within the transaction. For example, you can use savepoints throughout a long complex series of updates, so if you make an error, you do not need to resubmit every statement.
For example, recall the banking example. A transfer of funds between two accounts (the transaction) should include increasing one account (one SQL statement), decreasing another account (one SQL statement), and the record in the transaction journal (one SQL statement). All actions should either fail or succeed together; the credit should not be committed without the debit. Other non-related actions, such as a new deposit to one account, should not be included in the transfer of funds transaction; such statements should be in other transactions.
When designing a database application, a developer should consider the advantages of using stored PL/SQL:
The following sections describe the different program units that can be defined and stored centrally in a database.
Database triggers can be used in a variety of ways for the information management of your database. For example, they can be used to automate data generation, audit data modifications, enforce complex integrity constraints, and customize complex security authorizations.
The following integrity constraints are supported by Oracle:
NOT NULL
Disallows nulls (empty entries) in a table's column.
UNIQUE
Disallows duplicate values in a column or set of columns.
PRIMARY KEY
Disallows duplicate values and nulls in a column or set of columns.
FOREIGN KEY
Requires each value in a column or set of columns match a value in a related table's UNIQUE or PRIMARY KEY (FOREIGN KEY integrity constraints also define referential integrity actions that dictate what Oracle should do with dependent data if the data it references is altered).
CHECK
Disallows values that do not satisfy the logical expression of the constraint.
Keys The term "key" is used in the definitions of several types of integrity constraints. A key is the column or set of columns included in the definition of certain types of integrity constraints. Keys describe the relationships between the different tables and columns of a relational database. The different types of keys include
primary key
The column or set of columns included in the definition of a table's PRIMARY KEY constraint. A primary key's values uniquely identify the rows in a table. Only one primary key may be defined per table.
unique key
The column or set of columns included in the definition of a UNIQUE constraint.
foreign key
The column or set of columns included in the definition of a referential integrity constraint.
referenced key
The unique key or primary key of the same or different table that is referenced by a foreign key.
Individual values in a key are called key values.
Note: While database triggers allow you to define and enforce integrity rules, a database trigger is not the same as an integrity constraint. Among other things, a database trigger defined to enforce an integrity rule does not check data already loaded into a table. Therefore, it is strongly recommended that you use database triggers only when the integrity rule cannot be enforced by integrity constraints.
If many people are accessing the same data, one way of managing data concurrency is to make each user wait his or her turn. The goal of a database management system is to reduce that wait so it is either non-existent or negligible to each user. All DML statements should proceed with as little interference as possible and destructive interactions between concurrent transactions must be prevented. Destructive interaction is any interaction that incorrectly updates data or incorrectly alters underlying data structures. Neither performance nor data integrity can be sacrificed.
Oracle resolves such issues by using various types of locks and a multiversion consistency model. Both features are discussed later in this section. These features are based on the concept of a transaction. As discussed in "Data Consistency Using Transactions" , it is the application designer's responsibility to ensure that transactions fully exploit these concurrency and consistency features.
Note that a transaction is key to Oracle's strategy for providing read consistency. This unit of committed (or uncommitted) SQL statements
If you want to run a number of queries against multiple tables and if you are doing no updating, you may prefer a read-only transaction. After indicating that your transaction is read-only, you can execute as many queries as you like against any table, knowing that the results of each query are consistent with respect to the same point in time.
Locks are used to achieve two important database goals:
consistency
Ensures that the data a user is viewing or changing is not changed (by other users) until the user is finished with the data.
integrity
Ensures that the database's data and structures reflect all changes made to them in the correct sequence.
Locks guarantee data integrity while allowing maximum concurrent access to the data by unlimited users.
Oracle's sophisticated lock manager automatically locks table data at the row level. By locking table data at the row level, contention for the same data is minimized.
Oracle's lock manager maintains several different types of row locks, depending on what type of operation established the lock. In general, there are two types of locks: exclusive locks and share locks. Only one exclusive lock can be obtained on a resource (such as a row or a table); however, many share locks can be obtained on a single resource. Both exclusive and share locks always allow queries on the locked resource, but prohibit other activity on the resource (such as updates and deletes).
Database security can be classified into two distinct categories: system security and data security.
System security includes the mechanisms that control the access and use of the database at the system level. For example, system security includes:
Oracle manages database security using several different facilities:
Figure 1 - 6. Oracle Security Features
Security Domain Each user has a security domain -- a set of properties that determine such things as the
System Privileges System privileges allow users to perform a particular systemwide action or a particular action on a particular type of object. For example, the privileges to create a tablespace or to delete the rows of any table in the database are system privileges. Many system privileges are available only to administrators and application developers because the privileges are very powerful.
Object Privileges Object privileges allow users to perform a particular action on a specific object. For example, the privilege to delete rows of a specific table is an object privilege. Object privileges are granted (assigned) to end-users so that they can use a database application to accomplish specific tasks.
Granting Privileges Privileges are granted to users so that users can access and modify data in the database. A user can receive a privilege two different ways:
The DBA can create a role with a password to prevent unauthorized use of the privileges granted to the role. Typically, an application is designed so that when it starts, it enables the proper role. As a result, an application user does not need to know the password for an application's role.
Default Tablespace Each user is associated with a default tablespace. When a user creates a table, index, or cluster and no tablespace is specified to physically contain the object, the user's default tablespace is used if the user has the privilege to create the object and a quota in the specified default tablespace. The default tablespace feature provides Oracle with information to direct space usage in situations where object location is not specified.
Temporary Tablespace Each user has a temporary tablespace. When a user executes a SQL statement that requires the creation of temporary segments (such as the creation of an index), the user's temporary tablespace is used. By directing all users' temporary segments to a separate tablespace, the temporary tablespace feature can reduce I/O contention among temporary segments and other types of segments.
Tablespace Quotas Oracle can limit the collective amount of disk space available to the objects in a schema. Quotas (space limits) can be set for each tablespace available to a user. The tablespace quota security feature permits selective control over the amount of disk space that can be consumed by the objects of specific schemas.
statement auditing
Statement auditing is the auditing of specific SQL statements without regard to specifically named objects. (In addition, database triggers allow a DBA to extend and customize Oracle's built-in auditing features.)
Statement auditing can be broad and audit all users of the system or can be focused to audit only selected users of the system. For example, statement auditing by user can audit connections to and disconnections from the database by the users SCOTT and LORI.
privilege auditing
Privilege auditing is the auditing of the use of powerful system privileges without regard to specifically named objects. Privilege auditing can be broad and audit all users or can be focused to audit only selected users.
object auditing
Object auditing is the auditing of accesses to specific schema objects without regard to user. Object auditing monitors the statements permitted by object privileges, such as SELECT or DELETE statements on a given table.
For all types of auditing, Oracle allows the selective auditing of successful statement executions, unsuccessful statement executions, or both. This allows monitoring of suspicious statements, regardless of whether the user issuing a statement has the appropriate privileges to issue the statement.
The results of audited operations are recorded in a table referred to as the audit trail. Predefined views of the audit trail are available so that you can easily retrieve audit records.
In addition, Trusted Oracle enforces mandatory access control (also called MAC) across a wide range of multilevel secure operating system environments. Mandatory access control is a means of restricting access to information based on labels. A user's label indicates what information a user is permitted to access and the type of access (read or write) that the user is allowed to perform. An object's label indicates the sensitivity of the information that the object contains. A user's label must meet certain criteria, determined by MAC policy, in order for him/her to be allowed to access a labeled object. Because this type of access control is always enforced above any discretionary controls implemented by users, this type of security is called "mandatory".
See the Trusted Oracle7 Server Administrator's Guide for more information.
user error
User errors can require a database to be recovered to a point in time before the error occurred. For example, a user might accidentally drop a table. To allow recovery from user errors and accommodate other unique recovery requirements, Oracle provides for exact point-in-time recovery. For example, if a user accidentally drops a table, the database can be recovered to the instant in time before the table was dropped.
statement and process failure
Statement failure occurs when there is a logical failure in the handling of a statement in an Oracle program (for example, the statement is not a valid SQL construction). When statement failure occurs, the effects (if any) of the statement are automatically undone by Oracle and control is returned to the user.
A process failure is a failure in a user process accessing Oracle, such as an abnormal disconnection or process termination. The failed user process cannot continue work, although Oracle and other user processes can. The Oracle background process PMON automatically detects the failed user process or is informed of it by SQL*Net. PMON resolves the problem by rolling back the uncommitted transaction of the user process and releasing any resources that the process was using.
Common problems such as erroneous SQL statement constructions and aborted user processes should never halt the database system as a whole. Furthermore, Oracle automatically performs necessary recovery from uncommitted transaction changes and locked resources with minimal impact on the system or other users.
instance failure
Instance failure occurs when a problem arises that prevents an instance (system global area and background processes) from continuing work. Instance failure may result from a hardware problem such as a power outage, or a software problem such as an operating system crash. When an instance failure occurs, the data in the buffers of the system global area is not written to the datafiles.
Instance failure requires instance recovery. Instance recovery is automatically performed by Oracle when the instance is restarted. The redo log is used to recover the committed data in the SGA's database buffers that was lost due to the instance failure.
media (disk) failure
An error can arise when trying to write or read a file that is required to operate the database. This is called disk failure because there is a physical problem reading or writing physical files on disk. A common example is a disk head crash, which causes the loss of all files on a disk drive. Different files may be affected by this type of disk failure, including the datafiles, the redo log files, and the control files. Also, because the database instance cannot continue to function properly, the data in the database buffers of the system global area cannot be permanently written to the datafiles.
A disk failure requires media recovery. Media recovery restores a database's datafiles so that the information in them corresponds to the most recent time point before the disk failure, including the committed data in memory that was lost because of the failure. To complete a recovery from a disk failure, the following is required: backups of the database's datafiles, and all online and necessary archived redo log files.
Oracle provides for complete and quick recovery from all possible types of hardware failures including disk crashes. Options are provided so that a database can be completely recovered or partially recovered to a specific point in time.
If some datafiles are damaged in a disk failure but most of the database is intact and operational, the database can remain open while the required tablespaces are individually recovered. Therefore, undamaged portions of a database are available for normal use while damaged portions are being recovered.
, the redo log is a set of files that protect altered database data in memory that has not been written to the datafiles. The redo log can be comprised of two parts: the online redo log and the archived redo log. The Online Redo Log The online redo log is a set of two or more online redo log files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stored in redo log buffers of the system global area are written to an online redo log file by the background process LGWR.
The online redo log files are used in a cyclical fashion; for example, if two files constitute the online redo log, the first file is filled, the second file is filled, the first file is reused and filled, the second file is reused and filled, and so on. Each time a file is filled, it is assigned a log sequence number to identify the set of redo entries.
To avoid losing the database due to a single point of failure, Oracle can maintain multiple sets of online redo log files. A multiplexed online redo log consists of copies of online redo log files physically located on separate disks; changes made to one member of the group are made to all members.
If a disk that contains an online redo log file fails, other copies are still intact and available to Oracle. System operation is not interrupted and the lost online redo log files can be easily recovered using an intact copy.
The Archived Redo Log Optionally, filled online redo files can be archived before being reused, creating an archived redo log. Archived (offline) redo log files constitute the archived redo log.
ARCHIVELOG
The filled online redo log files are archived before they are reused in the cycle.
NOARCHIVELOG
The filled online redo log files are not archived.
In ARCHIVELOG mode, the database can be completely recovered from both instance and disk failure. The database can also be backed up while it is open and available for use. However, additional administrative operations are required to maintain the archived redo log.
If the database's redo log is operated in NOARCHIVELOG mode, the database can be completely recovered from instance failure, but not from a disk failure. Additionally, the database can be backed up only while it is completely closed. Because no archived redo log is created, no extra work is required by the database administrator.
Multiplexed Control Files This feature is similar to the multiplexed redo log feature: a number of identical control files may be maintained by Oracle, which updates all of them simultaneously.
, rollback segments record rollback information used by several functions of Oracle. During database recovery, after all changes recorded in the redo log have been applied, Oracle uses rollback segment information to undo any uncommitted transactions. Because rollback segments are stored in the database buffers, this important recovery information is automatically protected by the redo log.
Full Backups A full backup is an operating system backup of all datafiles, online redo log files, and the control file that constitutes an Oracle database. Full backups are performed when the database is closed and unavailable for use.
Partial Backups A partial backup is an operating system backup of part of a database. The backup of an individual tablespace's datafiles or the backup of a control file are examples of partial backups. Partial backups are useful only when the database's redo log is operated in ARCHIVELOG mode.
A variety of partial backups can be taken to accommodate any backup strategy. For example, you can back up datafiles and control files when the database is open or closed, or when a specific tablespace is online or offline. Because the redo log is operated in ARCHIVELOG mode, additional backups of the redo log are not necessary; the archived redo log is a backup of filled online redo log files.
If all needed redo information is online, Oracle performs this recovery step automatically when the database starts. After roll forward, the datafiles contain all committed changes as well as any uncommitted changes that were recorded in the redo log.
An Oracle database system can easily take advantage of distributed processing by using its client/server architecture. In this architecture, the database system is divided into two parts: a front-end or a client portion and a back-end or a server portion.
Server The server portion runs Oracle software and handles the functions required for concurrent, shared data access. The server portion receives and processes SQL and PL/SQL statements originating from client applications. The computer that manages the server portion can be optimized for its duties. For example, it can have large disk capacity and fast processors.
Note: The information in this section regarding distributed updates and two-phase commit applies only for those systems using Oracle with the distributed option.
A distributed database is a network of databases managed by multiple database servers that appears to a user as a single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified. The primary benefit of a distributed database is that the data of physically separate databases can be logically combined and potentially made accessible to all users on a network.
Each computer that manages a database in the distributed database is called a node. The database to which a user is directly connected is called the local database. Any additional databases accessed by this user are called remote databases. When a local database accesses a remote database for information, the local database is a client of the remote server (client/server architecture, discussed previously).
While a distributed database allows increased access to a large amount of data across a network, it must also provide the ability to hide the location of the data and the complexity of accessing it across the network. The distributed DBMS must also preserve the advantages of administrating each local database as though it were non-distributed.
SELECT * FROM emp@sales;
Complete Transparency to Database Users The Oracle two-phase commit mechanism is completely transparent to users that issue distributed transactions. A simple COMMIT statement denoting the end of a transaction automatically triggers the two-phase commit mechanism to commit the transaction; no coding or complex statement syntax is required to include distributed transactions within the body of a database application.
Automatic Recovery from System or Network Failures The RECO (recoverer) background process automatically resolves the outcome of in-doubt distributed transactions -- distributed transactions in which the commit was interrupted by any type of system or network failure. After the failure is repaired and communication is reestablished, the RECO of each local Oracle Server automatically commits or rolls back any in-doubt distributed transactions consistently on all involved nodes.
Optional Manual Override Capability In the event of a long-term failure, Oracle allows each local administrator to manually commit or roll back any distributed transactions that are in doubt as a result of the failure. This option allows the local database administrator to free up any locked resources that may be held indefinitely as a result of the long-term failure.
Facilities for Distributed Recovery If a database must be recovered to a point in the past, Oracle's recovery facilities allow database administrators at other sites to return their databases to the earlier point in time also. This ensures that the global database remains consistent.
Note: The information in this section applies only to Oracle with the distributed or advanced replication options.
Distributed database systems often locally replicate remote tables that are frequently queried by local users. By having copies of heavily accessed data on several nodes, the distributed database does not need to send information across a network repeatedly, thus helping to maximize the performance of the database application.
Data can be replicated using snapshots or replicated master tables. Replicated master tables require the replication option. For more information about replicating data, see Oracle7 Server Distributed Systems, Volume II.
Using SQL*Net, the application developer does not have to be concerned with supporting network communications in a database application. If a new protocol is used, the database administrator makes some minor changes, while the application requires no modifications and continues to function.
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |