# ORACLE8i- P6

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:40

0
45
lượt xem
6

## ORACLE8i- P6

Mô tả tài liệu

ORACLE8i- P6: We want information… information...” Possibly you recognize these words as the primary interest of a somewhat clandestine group, and as told by a character called Number 2 to Patrick McGoohan’s character Number 6 (in the old TV show The Prisoner). Indeed, in this day, information is king, and the speedy, accurate, and reliable retrieval of this information is paramount.

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: ORACLE8i- P6

1. 190 CHAPTER 5 • UNDERSTANDING ORACLE8i ARCHITECTURE controllers, not only will you improve the performance of LGWR, but you will also protect your online redo logs from failure or human error. If one member of a redo log group becomes corrupted, that will not cause the rest of the database to fail. Rather, Oracle will simply close the log file, note an error in the LGWR trace file, and continue to write to the remaining log files. If all of the online redo logs become corrupted, LGWR will fail. Then you will need to shut down the data- base, investigate the cause of the problem, and probably recover the database. If Oracle cannot open an existing redo log file group, you will need to shut down the database, determine what the problem with the redo log file is, and then restart the database. Database recovery in the form of media recovery will likely not be required in this case. System Monitor (SMON) The SMON process is responsible for a variety of database operations, including the following: • Cleaning up temporary segments • Recovering from a database crash • Coalescing database free space • Running recovery transactions against unavailable datafiles • Recovering an instance of a failed Oracle Parallel Server (OPS) node • Registering instances with the listener • Cleaning up the OBJ$table • Shrinking rollback segments back to their OPTIMAL size setting • Managing offline rollback segments in PENDING OFFLINE mode • Updating statistics if an object is set to monitoring Process Monitor (PMON) The PMON process is responsible for recovering failed user processes and cleaning up any resources that might have been allocated to that process. If you are running MTS, PMON is responsible for recovery of dispatcher and server processes. PMON also monitors background processes, restarting them if they fail or shutting down instances if required (though PMON shutdowns are not very gracious!). Also, PMON is responsible for registering the database with the Oracle listener. Checkpoint (CKPT) The CKPT process is responsible for updating certain structures in the database datafile headers, as well as in the control files of the database, so that these files are synchronized. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 2. ORACLE PROCESSES 191 Every three seconds, CKPT will wake up and determine if there are checkpoint events to process. Also, CKPT is responsible for notifying DBWn to start checkpoint processing when a checkpoint event is raised. (Prior to Oracle8, if the CKPT process were not run- ning, this job would fall to the DBWn process, which would further burden it.) Optional Processes The following processes are optional, and their use depends on the setup of your database: • ARCH, the archiver process, is normally enabled when the database is in ARCHIVELOG mode. You can have it start automatically via the init.ora param- eter LOG_ARCHIVE_START, or you can start it from SQL via the ALTER SYSTEM command. PA R T • SNPn, the job queue (or Snapshot) processes, are associated with the Oracle Job Scheduler. II • RECO, the Distributed Database Recovery process, is associated with distributed transaction processing in Oracle. It is responsible for recovering transactions that are left in a prepared state after the loss of a connection to a remote database. Oracle Database This process can be disabled by setting the parameter DISTRIBUTED_TRANSAC- Administration TIONS to 0. • LCKn (Lock processes), LMON (Lock Manager), LMD (Lock Manager Daemon), and BSP (Block Server Process) are part of the Oracle Distributed Lock Manager (DLM) processes associated with OPS. • Dnnn, the MTS Dispatcher processes, are dispatcher processes that act as the liaison between the client sessions and the shared server processes in a Net8 configuration. • Snnn, the MTS Shared Server processes, read requests from the request queue, process them, and then forward the result set to the response queue. • Qnnn, the Queue Monitor processes, are associated with Oracle advanced queu- ing. This process is controlled with the init.ora parameter AQ_TM_PROCESS. • LISTENER, the Oracle TNS listener process, facilitates Net8 connections between the database server and the client. These processes are discussed in more detail in the chapters that describe the data- base functions with which they are associated. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 3. 192 CHAPTER 5 • UNDERSTANDING ORACLE8i ARCHITECTURE Oracle Database Control Structures In this section, we will review the concepts and internals of the Oracle database that contribute to maintaining the integrity of the database. First, we will discuss the nature of transactions in Oracle and the structures associated with transactions and data durability: commits, rollbacks, and checkpoints. Next, we will look at Oracle’s internal numbering mechanism: the system change number (SCN). Then we will examine rollback segment generation in Oracle (also known as undo), database datafiles, control files, and Oracle redo logs. Transaction Commits and Rollbacks A transaction is at the heart of the Oracle database. Without transactions, the database data never changes—rows are never added, altered, or deleted—making the database pretty useless for the most part. A transaction is the life of a series of different Oracle DML statements. A transaction begins with an initial connection to the database by a user process or after a previous transaction has been completed. Transactions end successfully (the changes are saved to the database) at the imple- mentation of a commit. Commits can be forced through the use of the COMMIT command. Commits can also be implied through the use of certain DDL commands. Transactions end unsuccessfully (database data changes are not saved) with the issuance of a ROLLBACK command or if the user process encounters some sort of fatal failure. Once a transaction has been committed, it cannot be rolled back. In many cases, such as with the default behavior of SQL*Plus, if you exit the process normally, an implied commit is issued, causing the transactional changes to be committed. Even terminations of certain user processes that might seem abnormal (such as hit- ting Ctrl+C in SQL*Plus and having SQL*Plus terminate, as it does in some cases) will cause an implied commit of the data. Checkpoints Because one of the goals of Oracle is speed, the changes to datafiles are not written out to the datafiles when a commit occurs. Changes are only written to the online redo logs (which we will discuss a bit later in this chapter). Of course, the data is writ- ten out at some point. This occurs when a checkpoint is signaled. A checkpoint is an event that causes the database to become synchronized to a single point in time (this may not be to the current point in time, however). The DBWn process is responsible for processing checkpoint operations. During a checkpoint, the DBWn process fires up and starts writing data out to the database Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 4. ORACLE DATABASE CONTROL STRUCTURES 193 datafiles. In concert with the DBWn process, the CKPT process is responsible for updating certain structures in the database datafile headers, as well as updating the control files of the database so that they are also synchronized. Checkpoints can be caused by a number of events, including the following: • Taking a datafile or tablespace offline • Issuing the ALTER TABLESPACE BEGIN BACKUP command • Issuing the ALTER SYSTEM CHECKPOINT command • Shutting down the database with a SHUTDOWN, SHUTDOWN NORMAL, or SHUTDOWN IMMEDIATE command TI P If you need to abort a shutdown, it’s a good idea to force the system to perform a PA R T checkpoint by issuing an ALTER SYSTEM CHECKPOINT command. This will cause your shut- down to take a little longer, but it will allow your database to come back up much faster. II Checkpoint Processing Oracle Database Oracle8i has changed how it handles checkpoints from previous versions of the data- Administration base. In Oracle8i, you can have multiple checkpoints called and ready to be processed by DBWn at the same time (though checkpoints themselves occur in serial fashion). Oracle creates a checkpoint queue. All dirty buffers in the database buffer caches are linked to this list. The buffers in this queue are ordered according to when the changes occurred by using the low redo value in that block (an SCN value that indi- cates when the buffer was first changed). When a checkpoint is signaled, the range of redo values that the specific check- point request is responsible for is sent. Once the last redo value has been processed, that checkpoint will be complete. Even though that particular checkpoint may be completed, DBWn may continue to process subsequently requested checkpoints. Each checkpoint is handled in the order requested, so the oldest dirty blocks are always written to disk first. Should a block change before it was written during the check- point, that dirty block will still be written by the original checkpoint. There are five different kinds of checkpoints in Oracle: Database All dirty blocks in memory that contain changes prior to the check- point SCN will be written to disk. Datafile All dirty blocks in memory that contain changes that belong to a spe- cific datafile will be written to that datafile. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 5. 194 CHAPTER 5 • UNDERSTANDING ORACLE8i ARCHITECTURE Incremental These checkpoints do not update datafile headers and are used to reduce recovery time. Incremental checkpoints are discussed in more detail in the next section. Mini-checkpoint These checkpoints are caused by certain types of DDL oper- ations, such as dropping a table. This checkpoint impacts only the blocks of those objects. Thread checkpoints These checkpoints are caused when an instance check- points in an OPS environment. Incremental Checkpoints In Oracle8i, the incremental checkpoint process limits the number of dirty buffers in the database buffer cache, in an effort to reduce the time required to restart the database in the event of a system failure. When an amount of redo equivalent to 90 percent of the size of the smallest log file is generated, an incremental checkpoint will be signaled. During normal database operations, the DBWn process constantly writes out small batches of changes to the database datafiles. In doing so, an incremental checkpoint counter is maintained in each datafile, as well as in the database control file. This counter represents the progress of the incremental checkpoints. Because dirty blocks are written to disk in a more frequent fashion (to reduce I/O con- tention), Oracle will need to apply less redo from the online redo logs to recover the database. (This particular requirement has some restrictions associated with the size of the redo log buffer.) You can set several parameters to control incremental checkpoints (each of these can be set in the init.ora database parameter file): FAST_START_IO_TARGET This parameter is available only in the Oracle8i Enterprise Edition of the database. It defines the upper bounds on recovery reads, and it is measured by the maximum number of database buffer cache blocks that you want to recover after a database failure. In other words, you define the maxi- mum number of I/Os that you want Oracle to process during recovery. Oracle will manage the incremental checkpoint process such that this target recovery figure will be maintained as best as possible. The smaller this number, the faster recov- ery will be (but there may be I/O and database performance impacts). LOG_CHECKPOINT_INTERVAL This parameter is measured in redo log blocks (which is generally different from the database block size). It defines the maximum number of dirty redo log buffer blocks that can exist, unwritten, in the database. If this number of blocks is exceeded, an incremental checkpoint is signaled. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 6. ORACLE DATABASE CONTROL STRUCTURES 195 LOG_CHECKPOINT_TIMEOUT This parameter, which is measured in sec- onds, indicates that the tail of the incremental checkpoint should be where the last redo log record was x seconds ago. Thus, this parameter attempts to control the checkpoint process by causing incremental checkpoints no more than a set number of seconds behind in the redo stream. LOG_CHECKPOINTS_TO_ALERT If you want to know how long it takes to complete a checkpoint (which can be a measure of overall database perfor- mance and a good trending tool), you can log the start and stop times of check- points to the alert log by using this parameter. NOTE The logging time of checkpoints in Oracle8i version 8.1.6.3 does not appear to be working correctly. If you set the init.ora parameter LOG_CHECKPOINTS_TO_ALERT to PA R T report checkpoint completions, the times being reported do not seem to match when the checkpoints actually appear to have been completed. This may be due to changes in incre- II mental checkpointing in Oracle8i. You can see how the various parameters impact instance recovery by looking at the Oracle Database Administration V$INSTANCE_RECOVERY view. If you want to know how many checkpoints have occurred on your system since it was started, you can query the V\$SYSSTAT view and look for the statistic called DBWR Checkpoints. This value represents the number of checkpoint requests that have been completed. The System Change Number (SCN) The SCN is simply a counter that represents the state of the database at a given point in time. As an analogy, consider the counter on your VCR. As you record a movie on your videotape, the counter represents individual places on the tape. Thus, if you knew where the counter was when Neo says, “I know Kung-Fu” in The Matrix, you can always go back to that position and watch him strut his stuff. The SCN is some- what like the VCR counter. It is a continuously flowing timeline that identifies when things happened. The SCN is a 6-byte number that increases monotonically; that is, it goes from a small number (starting at 0) and moves up as transactions occur. Each database trans- action is committed at a given SCN. The SCN consists of two parts: the base value and the wrap value. The base value of the SCN is increased first. If the base value rolls over, then the wrap value is increased. A new SCN is assigned every time a transaction is committed. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
8. ORACLE DATABASE CONTROL STRUCTURES 197 database is in a consistent state. If the SCNs do not match, then the database is not in a consistent state and some form of recovery will be required. When a database is shut down normally, the shutdown process will cause a check- point to occur, and all datafiles will be updated. This will cause the SCNs in the datafile headers to be synchronized. The database will then update the SCNs in the control files, so that those SCNs will also be consistent. When the database crashes, or a SHUTDOWN ABORT is executed, these synchronization processes do not occur. Since the database datafiles will be out of synch with each other and the database, they will be considered inconsistent, and Oracle will apply recovery when the data- base is restarted. Database Recovery Services The SCN is used during database recovery to ensure that all database datafiles are recovered to a consistent point in time. If, during the recovery process, a database PA R T datafile is not consistent with the remaining datafiles of the database, Oracle will sig- II nal an error indicating that there is an inconsistent state in the database and that the database will not open. This means that all of your database datafiles must be recov- ered to the same point in time. For example, if you want to recover one tablespace (and the objects in it) as they Oracle Database Administration looked at 3 P.M., and the rest of the database as it looked at 4 P.M, you will need to con- sider some different options from a physical recovery of the data, such as performing a logical recovery or taking advantage of Oracle’s transportable tablespace feature. We will discuss each of these options in more detail in Chapters 11 and 12. Rollback Segments and Undo We have already discussed the role of rollback segments in providing a read-consistent image of changes made to the database. These changes are in the form of undo. Undo is simply the representation of the data that was changed as it existed before the change took place. Undo is what is stored in rollback segments and allows a block change to be undone. Different changes generate different levels of undo. Some changes, such as INSERT statements, generate minimal undo (since the undo for an INSERT operation is basi- cally a deletion of the row). Other changes, such as the results of UPDATE or DELETE statements, can generate a lot of undo. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com