Oracle Essentials

Users do not directly access the information in an Oracle database.

Ref. 8D25-A

Instead, they pass requests for information to an Oracle instance.

Ref. 7493-B

Losing control files is not necessarily fatal — there are ways to rebuild them.

Ref. 5239-C

The recording of the changes in the redo logs can be used to play back the changes lost when the failure occurred, thus protecting transactional consistency.

Ref. 3112-D

In addition, redo log files are used for “undo” operations when a ROLLBACK statement is issued.

Ref. A736-E

Uncommitted changes to the database are rolled back to the database image at

Ref. B464-F

Suppressing Redo Logging By default, Oracle logs all changes made to the database. The generation of redo logs adds a certain amount of overhead. You can suppress redo log generation to speed up specific operations, but doing so means the operation in question won’t be logged in the redo logs and you will not be able to recover that operation in the event of a failure. If you do decide to suppress redo logging for certain operations, you would include the NOLOGGING keyword in the SQL statement for the operation. (Note that prior to Oracle8, the keyword was UNRECOVERABLE.) If a failure occurred during the operation, you would need to repeat the operation. For example, you might build an index on a table without generating redo information. In the event that a database failure occurs and the database is recovered, the index will not be re-created because it wasn’t logged. You’d simply execute the script originally intended to create the index again.

use case

Ref. E0CF-G

To simplify operations in the event of a failure, we recommend that you always take a backup after an unlogged operation if you cannot afford to lose the object created by the operation or you cannot repeat the operation for some reason.

Ref. A1DB-H

A thread of redo is composed of redo log groups, which are composed of one or more redo log members.

Ref. 1F7E-I

Logically, you can think of a redo log group as a single redo log file.

Ref. 3836-J

However, Oracle allows you to specify multiple copies of a redo log to protect the all-important integrity of the redo log. By creating multiple copies of each redo log file, you protect the redo log file from

Ref. 348D-K

Consider what could happen if Oracle were to write multiple redo logs asynchronously, writing to a primary log and then updating the copies later in the background. If a failure occurs that brings the system down and damages the primary log, Oracle might not have completed updating all the logs.

Ref. EC97-L

Once Oracle fills one redo log file, it automatically begins to use the next log file.

Ref. 2304-M

When the server cycles through all the available redo log files, it returns to the first one and reuses it.

Ref. 7098-N

Archived redo logs You may be wondering how to avoid losing the critical information in the redo log when Oracle cycles over a previously used redo log.

Ref. 3B53-O

If a failure occurs that damages the datafiles, you must restore the entire database to the point in time when the last backup occurred.

Ref. 6071-P

Very few Oracle shops make this choice, because the inability to recover to the point of failure is unacceptable — it results in lost work.

Ref. B003-Q

The second and more practical way to address the issue is to archive the redo logs as they fill. To understand archiving redo logs, you must first understand that there are actually two types of redo logs for Oracle:

Ref. 163D-R

Archived redo logs Copies of the filled online redo logs made to avoid losing redo data as the online redo logs are overwritten An Oracle database can run in one of two modes with respect to archiving redo logs:

Ref. 4D2E-S

As the name implies, no redo logs are archived. As Oracle cycles through the logs, the filled logs are reinitialized and overwritten, which erases the history of the changes made to the database. This mode essentially has the disadvantage mentioned above, where a failure could lead to unrecoverable data. Choosing not to archive redo logs significantly reduces your options for database backups, as we’ll discuss in Chapter 11, and is not advised by Oracle.

Ref. DFA8-T

When Oracle rolls over to a new redo log, it archives the previous redo log.

Ref. DB48-U

To prevent gaps in the history, a given redo log cannot be reused until it is successfully archived.

Ref. 2481-V

The archived redo logs, plus the online redo logs, provide a complete history of all

Ref. B3B8-W

Together, they allow Oracle to recover all committed transactions up to the exact time a failure occurred. Operating in this mode enables tablespace and datafile backups. The internal sequence numbers discussed

Ref. 864E-X

Starting with Oracle Database 10g, automatic archiving for an Oracle database is enabled with the following SQL command: ALTER DATABASE ARCHIVELOG

Ref. 3359-Y

Oracle marks the redo logs for archiving as it fills them. The full log files must be archived before they can be reused. The ALTER DATABASE ARCHIVELOG command will by default turn on automatic archiving and the archivers are started.

Ref. EA6E-Z

LOG_ARCHIVE_FORMAT. A setting such as the following: LOG_ARCHIVE_DEST = C:\ORANT\DATABASE\ARCHIVE

Ref. AD37-A

The archived redo logs are critical for database recovery. Just as you can duplex the online redo logs, you can also specify multiple archive log destinations.

Ref. 11CB-B

Oracle will copy filled redo logs to specified destinations. You can also specify whether all copies must succeed or not. The initialization parameters for this functionality are as follows: LOG_ARCHIVE_DUPLEX_DEST Specifies an additional location for redundant redo logs. LOG_ARCHIVE_MIN_SUCCEED_DEST

Ref. B59F-C

An Oracle instance can be defined as an area of shared memory and a collection of background processes.

Ref. B8B1-D

Oracle9i also introduced the concept of the granule, which is the smallest amount of memory that you can add to or subtract from the SGA.

Ref. 2979-E

This notion of waiting to perform I/O until absolutely necessary is common throughout Oracle. Disks are the slowest component of a computer system, so the less I/O performed, the faster the system runs.

Ref. ABC1-F

You can mark a table or index for caching in a specific buffer pool.

Ref. 5CF6-G

You can specify a SHARED_POOL_SIZE initialization parameter, or it will be automatically sized if MEMORY_TARGET or SGA_TARGET is specified. Note that prior to Oracle Database 10g, “out of memory” errors were possible if the shared pool was undersized, but current Oracle database releases now can leverage automatic shared memory tuning.

Ref. 365E-H

disk. This buffer also improves performance. Oracle caches the redo until it can be written to a disk at a more optimal time, which avoids the overhead of constantly writing the redo logs to disk.

Ref. F792-I

Writes the redo information from the log buffer in the SGA to all copies of the current redo log file on disk.

Ref. A845-J

Maintains overall health and safety for an Oracle instance.

Ref. B405-K

Archiver (ARCn) Reads the redo log files once Oracle has filled them and writes a copy of the used redo log files to the specified archive log destination(s). Up to 10 Archiver processes are possible — hence the notation ARCn. LGWR will start additional Archivers as needed, based on the load, up to a limit specified by the initialization parameter

Ref. CFF4-L

You should remember that complete installation of the software includes not only loading the software, but also configuring and starting key services.

Ref. 28A1-M

One of the more important decisions you needed to make before actually installing Oracle in older releases concerned the directory structure and naming conventions you would follow for the files that make up a database. Clear, consistent, and well-planned conventions were crucial for minimizing

Ref. 9B6D-N

human errors in system and database administration.

Ref. 994A-O

An Optimal Flexible Architecture for a Growing Oracle Database or,

Ref. 98A8-P

Anyone who knows the OFA can navigate an Oracle environment to quickly find the software and files used for the database and the instance. This standardization increased productivity and avoided errors.

Ref. 3462-Q

One of the longstanding problems with upgrades has been the requirement to bring down the database, upgrade the database software, and then restart the database.

Ref. 68E5-R

If you are using a Real Application Clusters implementation since Oracle Database 10g, you can perform a rolling upgrade.

Ref. 3348-S

A rolling upgrade allows you to bring down some of the nodes of the cluster, upgrade their software, and then bring them back online as part of the cluster.

Ref. B7ED-T

Oracle might be installed for a variety of workloads. You should take a two-step approach for any new databases you create.

Ref. C638-U

itself. Consider what the database will be used for and how much data it will contain.

Ref. A17F-V

How many users will the database have? How many users will connect simultaneously and how many will concurrently perform transactions or execute queries?

Ref. E1C3-W

Some people employ a so-called CRUD matrix that contains Create, Read, Update, and Delete indicators, or even estimates for how many operations will be performed for each key object used by a business transaction.

Ref. ACAE-X

With Automatic Storage Management (ASM), introduced in Oracle Database 10g, you can add additional disk space or take away disks without interrupting database service.

Ref. 78C7-Y

While starting a database appears to be a single action, it involves an instance and a database and occurs in several distinct phases.

Ref. 93CF-Z

As the name implies, the server process works for the client process — it reads and passes back the requested data, accepts and makes changes on behalf of the client, and so on.

Ref. 94B4-A

For example, when a client wants to read a row of data stored in a particular database block, the server process identifies the desired block and either retrieves it from the database buffer cache or reads it from the correct datafile and loads it into the database buffer cache.

Ref. DD66-B

These actions are performed by the Log Writer (LGWR) and Database Writer (DBWR) processes, respectively.

Ref. F1BC-C

The Listener is started and stopped independently of the instance. If the Listener is down and the instance is up, clients accessing the database over a network cannot find the instance because there is no Listener to guide them. If the Listener is up and the instance is down, there is nowhere to send clients.

Ref. 43CE-D

Shared servers allow the Oracle instance to share a set of server processes across a larger group of users. Instead of each client connecting to and using a dedicated server, the clients use shared servers, which can significantly reduce the overall resource requirements for serving large numbers of users.

Ref. A0AB-E

In many systems there are times when the clients aren’t actively using their server process, such as when users are reading and absorbing data retrieved from the database.

Ref. F6FD-F

exclusive choice between shared server processes and dedicated server processes for an Oracle instance. Oracle can mix and match dedicated and shared servers, and clients can connect to one or the other.

Ref. 1F18-G

connection. Because a shared server process may be servicing another client, the client connects to a dispatcher, which is always ready to receive any client request. There are separate dispatchers for each network protocol being used (e.g., dispatchers for TCP/IP, etc.). The dispatchers serve

Ref. 173A-H

server. The dispatchers accept requests from clients and place them in a request queue, which is a memory structure in the SGA. There is one request queue for each instance. Shared servers

Ref. 6C5F-I

session memory or state. State information is basically data that describes the current status of a session in Oracle. For example, state information contains information about the SQL statements executed by the session. When you use a dedicated server, this state is stored in the private memory used by the dedicated server. This works out well because the dedicated server works with only one client. The term for

Ref. 5ABD-J

this private memory is the Program Global Area (PGA).

Ref. C5A9-K

If you’re using the shared servers, however, any server can work on behalf of a specific client. The session state cannot be stored in the PGA of the shared server process. All servers must be able to access the session state because the session can migrate between different shared servers. For this reason, Oracle places this state information in the System Global Area (SGA).

Ref. 4374-L

All servers can read from the SGA.

Ref. 90E4-M

statements affecting data in multiple tables. The entire set of changes must succeed or fail as a complete unit of work.

Ref. 5928-N

A rollback segment is not the same as a redo log. The redo log is used to log all transactions to the database and to recover the database in the event of a system failure, while the rollback segment provides rollback for transactions and read consistency.

Ref. 0575-O

Blocks of rollback segments are cached in the SGA just like blocks of tables and indexes.

Ref. ACCD-P

Oracle9i, you had the option of specifying automatic management of all rollback segments through the use of an undo tablespace.

Ref. F638-Q

they can be used to speed up database operations.

Ref. DF1C-R

Write all the database blocks the transaction changed to their respective datafiles. Write only the redo information, which typically involves much less I/O than writing the database blocks. This recording of the changes can be replayed to reproduce all the transaction’s changes later, if they are needed due to a failure.

Ref. EFC7-S

To provide maximum performance without risking transactional integrity, Oracle writes out only the redo information. When a user commits a transaction, Oracle guarantees that the redo for those changes writes to the redo logs on disk.

Ref. 3B21-T

If a failure occurs before the changed blocks are flushed from the cache to the datafiles, the redo logs will reproduce the changes in their entirety.

Ref. 83B6-U

system is the physical disk, Oracle’s fast-commit approach minimizes the cost of committing a transaction and provides maximum risk-free performance.

Ref. AEC6-V

Remember that rollback segments are used to provide a consistent image of the data in your Oracle database at a previous point in time. With Flashback Query, you can direct Oracle to return the results for a SQL query at a specific point in time. For instance, you could ask for a set of results from the database as of two hours ago. Flashback provided extra functionality by leveraging the rollback feature that was already a core part

Ref. E4CA-W

Since Flashback uses rollback segments, you can only flash back as far as the information in the current rollback segment. This requirement typically limits the span of flashback to a relatively short period of time

Ref. C527-X

Despite this limitation, there are scenarios in which you might be able to use a Flashback Query effectively, such as going back to a point in time before a user made an error that resulted in a loss of data.

Ref. 437D-Y

Flashback Database, to roll back the entire database to a consistent state Flashback Table, to roll back a specific table Flashback Drop to roll back a DROP operation Flashback Versions Query, to retrieve changes to one or more rows

Ref. F698-Z

information stored in a column, and can limit the operations that you can perform on columns.

Ref. 8755-A

The ROWID is a special type of column known as a pseudocolumn. The ROWID pseudocolumn can be accessed just like a column in a SQL SELECT statement. There is a ROWID pseudocolumn for every row in an Oracle database. The ROWID represents the specific address of a particular row. The ROWID pseudocolumn is defined with a ROWID datatype.

Ref. B277-B

The ROWID relates to a specific location on a disk drive. Because of this, the ROWID is the fastest way to retrieve an individual row.

Ref. 4222-C

Oracle Database 10g and later releases support a pseudocolumn ORA_ROWSCN, which holds the System Change Number (SCN) of the last transaction that modified the row.

Ref. 63B7-D

You can use this pseudocolumn to check easily for changes in the row since a transaction started. For more information on SCNs, see the discussion of concurrency

Ref. 7278-E

When you create a column for a table that must have a value, you specify it as NOT NULL, meaning that it cannot contain a NULL

Ref. F91E-F

value. If you try to write a row to a database table that doesn’t assign a value to a NOT NULL column, Oracle will return an error.

Ref. 0053-G

nonrelational database. As of Oracle9i, you can define external tables. As the name implies, the data for an external table is stored outside the database, typically in a flat file. The external table is read-only; you cannot update the data it contains. The external table is good for loading and unloading data to files from a database, among other purposes.

Ref. 44CA-H

Oracle Database 11g introduces the ability to create virtual columns for a table. These columns are defined by an expression and, although the results of the expression are not stored, the columns can be accessed by applications at runtime.

Ref. 8195-I

To simplify access to data stored in multiple tables. To implement specific security for the data in a table (e.g., by creating a view that includes a WHERE clause that limits the data you can access through the view). Starting with Oracle9i, you can use fine-grained access control to accomplish the same purpose. Fine-grained access control gives you the ability to automatically limit data access based on the value of data in a row. To isolate an application from the specific structure of the underlying tables.

Ref. EDA1-J

The ROWID is the fastest way to retrieve any row in a database, so the subsequent retrieval of a database row is performed in the most optimal way.

Ref. 8430-K

An index in Oracle refers to the physical structure used within the database.

Ref. 80CE-L

A cluster is a way of storing related data values together on disk. Oracle reads data a block at a time, so storing related values together reduces the number of I/O operations needed to retrieve related values, since a single data block will contain only related rows.

Ref. 6A89-M

The concept behind the Rules Manager is simple. A rule is stored in the database and is called and evaluated by applications. If business conditions or requirements change, the rule covering those scenarios can be changed without having to touch the application code. Rules can be shared across multiple application systems, bringing standardization along with reduced maintenance across the set of applications. You can also create granular rules that can be used in different combinations to implement a variety of conditions.

Ref. 2CB2-N

Using rules can be a very powerful tool for implementing complex logic, but the use of rules can affect your application design. For more information on the Rules Manager, please refer to the Oracle

Ref. 1898-O

Because you could join data from different tables together in a query, there was no need to keep all the information associated with a particular object together in a single record. You could decompose the information into associated units and simply join the appropriate units together when you needed information that crossed table boundaries.

Ref. 2DEA-P

A constraint enforces certain aspects of

Ref. 6392-Q

data integrity within a database.

Ref. D552-R

When you add a constraint to a particular column, Oracle automatically ensures that data violating that constraint is never accepted. If a user attempts to write data that violates a constraint, Oracle returns an error for the offending SQL statement. Constraints may be associated with columns when you create or add the table containing the column (via a number of keywords) or after the table has been created with the SQL command ALTER TABLE. Since Oracle8, the following constraint types are supported:

Ref. 1B26-S

Primary key Each table can have, at most, a single primary key constraint. The primary key may consist of more than one column in a table. The primary key constraint forces each primary key to have a unique value.

Ref. 4F1E-T

The foreign key constraint is defined for a table (known as the child) that has a relationship with another table in the database (known as the parent). The

Ref. 2CE1-U

Foreign keys not only provide a way to join related tables but also ensure that the relationship between the two tables will have the required data integrity.

Ref. E913-V

Normally, you cannot delete a row in a parent table if it causes a row in the child table to violate a foreign key constraint. However, you can specify that a foreign key constraint causes a cascade delete, which means that deleting a referenced row in the parent table automatically deletes all rows in the child table that reference the primary key value in the deleted row in the parent table.

Ref. 2632-W

Although you can write triggers to perform the work of a constraint, Oracle has optimized the operation of constraints, so it’s best to always use a constraint instead of a trigger if possible. A trigger is a block of code that is fired whenever a particular type of database event occurs to a table. There are three types of events that can cause a trigger to fire: A database UPDATE A database INSERT A database DELETE You can, for instance, define a trigger to write a customized audit record whenever a user changes a row.

Ref. 9113-X

data. The process of making this decision is called query optimization, because Oracle looks for the optimal way to retrieve the data.

Ref. FD8A-Y

This retrieval is known as the execution path.

Ref. E14D-Z

The trick behind query optimization is to choose the most efficient way to get the data, since there

Ref. 7C55-A

For instance, even with a query that involves only a single table, Oracle can take either of these approaches: Use an index to find the ROWIDs of the requested rows and then retrieve those rows from the table. Scan the table to find and retrieve the rows; this is referred to as a full table scan.

Ref. 11D4-B

Prior to Oracle Database 10g, you could choose between two different Oracle query optimizers, a rule-based optimizer and a cost-based optimizer; these are described in the following sections.

Ref. 2005-C

View-based security You can think of views as virtual tables defined by queries that extract or derive data from physical base tables.

Ref. 930E-D

You can use views to present only the rows or columns that a certain group of users should be able to access.

Ref. 2DEB-E

in the company, you’ll want to hide more personal information by providing a view that shows only the basic information. Creating a virtual private database or leveraging the Label Security Option, described in subsequent sections of this chapter provide a more secure means of restricting access to certain data.

Ref. 6352-F

Oracle offers a type of security that you can use to grant this type of fine-grained access control (FGAC).

Ref. 44F8-G

Security policies implemented as PL/SQL functions can be associated with tables or views enabling creation of a virtual private database (VPD).

Ref. B3CE-H

The collections of labels, label authorizations, and security enforcement options can be applied to entire schemas or to specific tables.

Ref. 45F1-I

Sensitivity labels are defined based on a user’s need to see and/or update data.

Ref. CDBE-J

Application roles have all the privileges necessary to run the applications, and users of the applications are granted the roles necessary to execute them.

Ref. 5100-K

you can create stored procedures that provide access to the tables and grant access to the stored procedures instead of the tables. For example, instead of granting INSERT privileges for the EMPLOYEE table, you might create and grant access to a stored procedure called HIRE_EMPLOYEE that accepts as parameters all the data for a new employee.

Ref. B7DD-L

One of the curious aspects of performance is that “good performance” is defined by its absence rather than by its presence. You can recognize bad performance easily, but good performance is usually defined as simply the absence of bad performance.

Ref. 1ED1-M

There are three basic steps to understanding how to address performance issues with your Oracle database: Define performance and performance problems. Check the performance of the Oracle server software. Check the overall performance of the server machine.

Ref. F218-N

Database Diagnostic Monitor (ADDM) can automatically identify SQL statements that are using the most resources or are less than optimal — the SQL Tuning Advisor component can even suggest solutions for the identified performance problems. (These tools are described later in this chapter.)

Ref. AFAC-O

Whenever you attempt to address performance problems, you must make sure to carefully monitor the areas that you are attempting to improve, both before and after your changes.

Ref. 3C95-P

Important baseline data gathered by the AWR includes application, database, operating system, disk I/O, and network statistics.

Ref. 047C-Q

Use disk-striping technologies to spread I/O evenly across multiple spindles

Ref. 3899-R

Use tablespaces to clearly segregate and target different types of I/O

Ref. 9487-S

Separate table I/O from index I/O by placing these structures in different tablespaces. You can then place the datafiles for these tablespaces on various disks to provide better performance for concurrent access.

Ref. 8D15-T

Placing multiple large tables in a single tablespace makes it difficult to determine which table is causing the I/O to the underlying datafiles. Segregating the objects allows you to directly monitor the I/O associated with each object. Your Oracle documentation details the other factors to consider in mapping objects to tablespaces.

Ref. 61B6-U

transactional systems. Oracle writes to all copies of the redo log file, and this I/O is not completed until all copies have been successfully written to. If you have two copies of the redo log file, one on a slow device and the other on a fast device, your redo log I/O performance will be constrained by the slower device.

Ref. 8B64-V

To avoid archiving performance issues due to I/O contention, make sure that the archive log destination uses different devices from those used for the redo logs and redo log mirrors.

Ref. 5612-W

Make sure the device used for the archive log destination doesn’t contain any database components or database backups If the failure of a single device results in the loss of both database components and archived redo logs, or backup components and archived redo logs, recovery will be endangered.

Ref. 7400-X

Instead, suppose you placed the same datafile on a “disk” that was actually an array of five physical disks. Each physical disk in the array can perform I/O operations independently on different data blocks of the index, automatically increasing the amount of I/O Oracle can perform without causing contention.

Ref. 80CA-Y

Simply using disk arrays won’t, by itself, give you optimal I/O performance.

Ref. 7276-Z

This type of disk-volume management is completely transparent to the database server and offers many benefits:

Ref. 8568-A

Write I/O is completed as soon as the data has been written to the subsystem’s cache. The I/O subsystem will destage the data from cache to actual disk later. Read I/O can be satisfied from the cache. The subsystem can employ some type of algorithm to sense I/O patterns and preload the cache in anticipation of pending read activity.

Ref. 0A0C-B

At the other extreme, you can group all disks into one single array, seen by the operating system and Oracle as a single “disk.” This makes for extremely simple planning and administration; no effort is required to analyze where you should place the various files, as there is only one “disk.”

Ref. 3BAC-C

In almost all large databases, disk striping increases disk I/O rates without adding too heavy an administrative burden

Ref. F0EC-D

session waited for space in the redo log buffer. An example of the query you may use to obtain the statistic is: SELECT name, value FROM V$SYSSTAT    WHERE name = ‘redo buffer allocation retries’;

Ref. 774F-E

The larger the number of users, the higher the number of server processes and the larger the amount of memory used for their associated PGAs.

Ref. 21A3-F

This area should not be confused with the shared SQL area within the shared pool. The shared SQL area contains shareable details for the SQL statement, such as the optimization plan. Optimizers and optimization plans are

Ref. 57E2-G

The private SQL area contains the session-specific information about the execution of the SQL statement within the session, such as the number of rows retrieved so far.

Ref. A694-H

A significant portion of the data in a data warehouse is often read-only, with infrequent updates

Ref. D65D-I

The data in OLTP systems is not “clean” or consistent across systems Data input to OLTP systems, if not carefully controlled, is likely to contain errors and duplication. Often, a key portion of the data warehouse loading process involves elimination of these errors.

Ref. B255-J

resources. Even if the underlying server platform is fine, the Oracle instance itself can fail. Whatever the cause of the crash, the effect on Oracle is the same — the instance cannot deliver its promised functionality.

Ref. BA9F-K

All active queries and transactions will be abruptly terminated. The process of cleaning up the resulting mess is called instance recovery or crash recovery.

Ref. FA05-L