# ORACLE8i- P9

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

0
36
lượt xem
5

## ORACLE8i- P9

Mô tả tài liệu
Download Vui lòng tải xuống để xem tài liệu đầy đủ

ORACLE8i- P9: 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- P9

1. MANAGING DATABASE OBJECTS THAT SUPPORT TABLES AND INDEXES 311 TABLESPACE data DISABLE STORAGE IN ROW INDEX clob_index); Table created. INSERT INTO clob_test_table VALUES (1, ‘This is another test’); 1 row created. SELECT * FROM clob_test_table; ID CLOB_COLUMN PA R T ---------- ------------------------------ II 1 This is another test UPDATE clob_test_table set clob_column=’This is a changed column’ WHERE id=1; Oracle Database Administration 1 row updated. SELECT * FROM clob_test_table; ID CLOB_COLUMN ---------- ------------------------------ 1 This is a changed column DELETE FROM clob_test_table where clob_column=’This is a changed column’; DELETE FROM clob_test_table where clob_column=’This is a changed column’ * ERROR at line 1: ORA-00932: inconsistent datatypes SQL> SELECT * FROM clob_test_table WHERE clob_column LIKE ‘%This%’; SELECT * FROM clob_test_table WHERE clob_column LIKE ‘%This%’ * ERROR at line 1: ORA-00932: inconsistent datatypes Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
2. 312 CHAPTER 7 • ORACLE DATABASE MAINTENANCE Notice that some SQL operations work just fine, but others don’t work correctly. It’s those problematic operations that the DBMS_LOB package is made for! The Oracle- supplied package provides various routines that can be used to manage and manipu- late Oracle internal and external LOBs. Viewing LOB Information Several data dictionary views provide information about LOBs, such as the column name of the LOB, the LOB segment name, and the chunk size. Table 7.2 describes these data dictionary views. TABLE 7.2: DATA DICTIONARY VIEWS FOR LOBS View Description DBA_LOBS Contains various information on LOBs, such as the name of the table that contains the LOB, the name of the LOB column, and so on DBA_LOB_PARTITIONS Contains partition-specific Information on LOBs in table partitions DBA_LOB_SUBPARTITIONS Contains partition-specific Information on LOBs in table subpartitions DBA_PART_LOBS Contains default partition settings for LOBs in table partitions V$TEMPORARY_LOBS Contains Information on temporary LOBs Understanding Database Startup and Shutdown It is important to understand the process of starting up and shutting down the data- base. This is because you will want the database to be in different modes for different types of recoveries (which are discussed in Chapter 10). In this section, we will discuss the various stages of database startup and shutdown. Starting Up the Database When you issue the STARTUP command on an Oracle database, it goes through three distinct stages: NOMOUNT, MOUNT, and OPEN. Several events occur during these stages as the database prepares to open. Database checks, datafile checks, consistency Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 3. UNDERSTANDING DATABASE STARTUP AND SHUTDOWN 313 checks, and certain types of recoveries occur during the startup process. Another startup mode that is available to the DBA is restricted mode. In executing various tasks, it may (and will likely) be necessary for you to start the database in a mode other than OPEN mode. NOMOUNT Mode NOMOUNT mode, and the NOMOUNT stage of regular startup, is essentially only the startup of the Oracle instance. As Oracle proceeds through the NOMOUNT stage, it performs the following steps: 1. It locates the database parameter file. By default, it will look for the init.ora file in the$ORACLE_HOME/dbs directory on Unix systems or the $ORACLE_HOME\ database directory on Windows NT. PA R T TI P You can use the PFILE parameter in the STARTUP command to designate an alter- II nate location for the parameter file. An example of using the STARTUP command with the PFILE parameter might look something like this: STARTUP NOMOUNT PFILE=d:\oracle\ admin\database\pfile\init(sid).ora. Oracle Database Administration 2. Oracle will open and read the parameter file, and then process and validate the parameters. 3. Oracle will allocate the SGA it will require to crank up the instance. 4. Oracle will proceed to start the processes of the instance, such as DBWR, LGWR, CKPT, SMON, and PMON. Once the instance is started, the NOMOUNT phase of opening the database will have concluded. You will start the database in NOMOUNT mode in just a few cases. First, you will start the instance in this manner when you wish to create a new database (or re-create an old one). Second, you will start the instance in this mode when you need to create a control file using the CREATE CONTROL FILE command (discussed in Chapter 10). To cause the database startup process to proceed only through the NOMOUNT stage and halt, issue the STARTUP NOMOUNT command from the Server Manager or SQL*Plus prompt. You will note that when you start up the instance, the amount of memory allo- cated to the SGA is reported. Generally, the memory reported looks something like this (obviously the sizes of memory allocated will differ): Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 4. 314 CHAPTER 7 • ORACLE DATABASE MAINTENANCE Total System Global Area 92280076 bytes Fixed Size 70924 bytes Variable Size 87937024 bytes Database Buffers 4194304 bytes Redo Buffers 77824 bytes The Fixed Size entry shows the memory allocated to a small portion of the SGA that contains database and instance status information required by background processes. The Variable Size entry shows the overall size of the shared pool area. It is also influenced by the use of the large pool and Java pool sizes. The other entries are self-explanatory. This output can also be retrieved by using the SHOW SGA command or viewed in the V$SGA data dictionary view (which is the source of the output). NOTE The Redo Buffers entry in this output may not match the setting in the init.ora file for your database. This is because there is a minimum setting for the redo log buffer of four times the maximum database block size for the system. MOUNT Mode Once the instance has been started, the next step in opening the database associated with that instance is to mount the database. In MOUNT mode (and during the MOUNT stage), Oracle locates and opens the control file of the database. It is also at the MOUNT stage that database media recovery occurs (if the database needs to be recovered while it is down). To start the database in MOUNT mode, use the STARTUP MOUNT command. You might do this for several reasons: to put the database in ARCHIVELOG mode, to per- form certain recovery operations, or to rename database datafiles. If you wish to open the database in this mode, issue the command ALTER DATABASE OPEN. OPEN Mode During the OPEN stage, Oracle takes the final steps to open the database. The datafiles are opened, and an error will be signaled if a datafile is missing or if Oracle is unable to open it for some other reason. Once the database datafiles are opened, Oracle will check them with the control file to make sure that the database was closed in a con- sistent fashion. If it was, Oracle will open the database for use. If the database was not closed in a normal fashion, Oracle will decide if instance or media recovery is required. If instance recovery is required, Oracle will perform it automatically without any user interaction. During instance recovery, Oracle will apply the redo in the online redo logs to the database, open the database, and roll back any uncommitted transactions Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
6. 316 CHAPTER 7 • ORACLE DATABASE MAINTENANCE time), the database datafiles are checkpointed and then closed. The checkpoint process is very important because it leaves the database datafiles in a consistent state, allowing the database to start up the next time without the database (or the DBA) needing to perform any type of recovery. (See Chapter 5 for more on checkpoints and the consistent state of the database.) The database then proceeds through the dismount phase, in which the control file is closed. Finally, the instance is terminated with the normal shutdown of the Oracle processes and de-allocation of the SGA. Along with the normal SHUTDOWN command, several other commands may be used to shut down the database, as explained in the following sections. SHUTDOWN ABORT A SHUTDOWN ABORT is essentially a crash of the database. At a minimum, this type of shutdown will result in an instance recovery when the database is restarted. The main difference between a SHUTDOWN ABORT and just pulling the plug on the data- base is that the database datafiles are closed normally (although they are not check- pointed in any way) and memory is de-allocated from the system in a somewhat controlled fashion. The normal closing of the database datafiles is perhaps a bit safer than just pulling the plug on a database. SHUTDOWN IMMEDIATE The SHUTDOWN IMMEDIATE command will cause all existing user processes to be terminated, following which a normal shutdown will ensue. All existing uncommit- ted transactions will be rolled back before the database is shut down, which can take some time to complete. Another thing that can slow down a SHUTDOWN IMMEDI- ATE is an ongoing instance recovery process. You can get an idea of how long a SHUTDOWN IMMEDIATE will take by looking at the V$TRANSACTION table’s USED_UBLK or USED_UREC column, which repre- sents the total amount of undo that will need to be reapplied to the database datafiles before the database can shut down. Ongoing instance recovery processes will not show up in V$TRANSACTION. To see those, you can try looking at the V$SESSION_ LONGOPS or V$RECOVERY_PROGRESS view, although neither of these may provide any information, depending on how much recovery Oracle is expecting to do. SHUTDOWN TRANSACTIONAL The SHUTDOWN TRANSACTIONAL command allows the user transaction to com- plete before signing off the user. Thus, if a user application is in the middle of making some change, the database will wait for a commit to occur before disconnecting the Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com
11. MANAGING DATABASE FILES 321 TO ’e:\oracle\oradata\recover\recover_my_extending_tablespace_01.dbf’; -- Bring the datafile online. ALTER DATABASE DATAFILE ’d:\oracle\oradata\recover\recover_my_extending_tablespace_01.dbf’ ONLINE; If you are not running your database in ARCHIVELOG mode, you will need to shut down the database, physically move the datafile again, mount the database, and issue the ALTER DATABASE RENAME command to rename the datafile’s location on the disk. Resizing Datafiles As explained in Chapter 5 (in the discussion of creating tablespaces), the AUTO- PA R T EXTEND clause lets you define how large a datafile is allowed to grow and how large it can grow at any given time. You can also use the ALTER DATABASE DATAFILE com- II mand with the RESIZE clause to resize database datafiles, as in this example: ALTER DATABASE DATAFILE ’d:\oracle\oradata\recover\recover_my_extending_tablespace_01.dbf’ Oracle Database RESIZE 10000k; Administration If you are making a datafile smaller, you can only remove free blocks from the end of the datafile. If the datafile has any used blocks, or any free space contained between used blocks, you will not be able to free that space. Viewing Datafile Information Oracle provides many different views for managing database datafiles. Table 7.3 lists the most commonly used views for managing datafiles. TABLE 7.3: COMMON VIEWS FOR DATABASE DATAFILES View Description DBA_DATA_FILES Lists datafiles information, such as the datafile number and asso- ciated tablespace names. DBA_FREE_SPACE Provides free space information for datafiles. Can be used to determine how much free space can be de-allocated from a data- base datafile. V$BACKUP Indicates the backup status of a datafile. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 12. 322 CHAPTER 7 • ORACLE DATABASE MAINTENANCE TABLE 7.3: COMMON VIEWS FOR DATABASE DATAFILES (CONTINUED) View Description V$DATAFILE Provides a large amount of information on database datafiles from the control file. This view is available after the database is mounted. V$RECOVER_FILE Provides information on database datafiles that require media recovery. This view is available after the database is mounted. FET$ Lists datafiles and free blocks (or extents) within the datafiles. UETLists all used extents in a database datafile. DBA_EXTENTS Allows you to locate the datafile in which a specific extent is located. Adding Control Files You should have at least two control file copies at a minimum. If necessary, you can add a control file to your database. Simply follow these steps: 1. Shut down the system. 2. Copy an existing control file for the system to the new name and location of the control file you wish to add. 3. Modify the database parameter file to reflect the addition of the control file. 4. Restart the database. Moving Online Redo Logs Even as a seasoned DBA, it always makes me a bit queasy to even think about touch- ing the online redo logs. This is because the redo logs are critical for database recovery operations, so if something should happen to them, you could end up with an unre- coverable database. To move an online redo log (you might want to do this for I/O distribution pur- poses), you can choose one of two paths. One method is to just delete and re-create the online redo log group, using the ALTER DATABASE DROP LOGFILE and ALTER DATABASE ADD LOGFILE commands (see the “Sizing the Redo Logs” section in Chapter 3 for examples of using these commands). Alternatively, you can manually move the online redo logs and then rename them in the database using the ALTER DATABASE FILE RENAME command. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 13. ANALYZING DATABASE OBJECTS 323 WARN I NG Remember how important the online redo logs are to Oracle. Act care- fully when manipulating them. To move a database redo log file, follow these steps: 1. Shut down the database and then mount it. 2. Rename the old log file. 3. Physically move the log file to its new location (for example, using the cp or copy command). Create it at the new location with its proper name. 4. To rename the redo log within the database itself, use the ALTER DATABASE RENAME FILE command. 5. Open the database. PA R T Review Listing 7.4 for an example of moving a file in Oracle (redo logs are moved II in the same manner as datafiles). Analyzing Database Objects Oracle Database Administration The ANALYZE command collects a great deal of useful information about tables, indexes, and clusters. The ANALYZE command has the following main purposes: • To collect or delete statistics about tables, indexes, clusters, and scalar object attributes • To validate the structure of tables, indexes, clusters, and REFs • To identify migrated and chained rows of a table or cluster • To create histograms to analyze data distribution Here, we will discuss the basics of gathering statistics and validating objects using the ANALYZE command. The topics of analyzing objects in conjunction with using cost-based optimization (CBO), identifying chained rows, and creating histograms are covered in Chapter 16. Also, analyzing database objects is a prerequisite for some Oracle maintenance and tuning activities, which are discussed in Chapters 15 through 17. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 14. 324 CHAPTER 7 • ORACLE DATABASE MAINTENANCE Gathering Statistics The ANALYZE command provides statistics on tables, such as the number of rows in the table, the number of blocks above the high-water mark of the table, average free space in each block, and a great deal of additional information. When analyzing indexes, you gather statistics on the depth of the index from its root block to its leaf blocks, the number of leaf blocks in the index, the number of distinct index values, and other information. You can either compute the statistics of the object, by using the COMPUTE STATIS- TICS clause of the ANALYZE command, or estimate the statistics of the object, by using the ESTIMATE STATISTICS clause. You can analyze a table, index, or cluster. If you analyze the table, all associated indexes are analyzed by default. However, you can specify that you do not want to analyze the indexes along with the table. You can also choose to analyze only specific columns in a table (all columns is the default), only specific partitions or subpartitions of a partitioned object (Chapter 24 discusses partitioned objects), or only local indexes. WARNING Never analyze an object owned by SYS. These are data dictionary objects and should not be analyzed. Computing Statistics The COMPUTE STATISTICS clause gives you the most accurate statistics possible on the object you are analyzing (short of building histograms, which we’ll discuss in Chapter 16). Here are examples of computing statistics for a table and an index: ANALYZE TABLE employee COMPUTE STATISTICS; ANALYZE INDEX ix_employee COMPUTE STATISTICS; The downside to computing statistics is that you need a significant amount of tem- porary space—enough to hold and sort the entire table. As a benchmark, figure that you need about three times the size of the table as workspace for an analysis using the COMPUTE STATISTICS method. (Note that if indexes are being analyzed, no addi- tional space is required.) Also, a complete analysis of a table takes significantly more processing time than for an analysis using the ESTIMATE STATISTICS clause. This can be particularly noticeable in a warehousing or Decision Support System (DSS) envi- ronment, where you are dealing with very large objects. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 15. ANALYZING DATABASE OBJECTS 325 Estimating Statistics Computing statistics may not be a realistic option because of the demand on resources. As an alternative, you can use ANALYZE with the ESTIMATE STATISTICS clause. When you estimate a given object’s statistics, Oracle reads through a sample set of rows or blocks, and based on that sample set, generates estimated object statis- tics. You can estimate based on either a specific number of rows or on a percentage of the total number of rows in the object. NOTE If you choose to have the CBO estimate more than 50 percent of the table, it will actually compute the statistics instead. (Of course, Oracle doesn’t bother to tell you that it’s doing so.) PA R T Let’s look at a few examples of the ANALYZE command using the ESTIMATE STA- II TISTICS clause: • To estimate statistics for a table based on the first 2000 rows: ANALYZE TABLE employee ESTIMATE STATISTICS SAMPLE 2000 ROWS; Oracle Database • To estimate statistics for a table based on a sample of 20 percent of the rows: Administration ANALYZE TABLE employee ESTIMATE STATISTICS SAMPLE 20 PERCENT; • To estimate statistics for an index using a sample size of 2000 rows: ANALYZE INDEX ix_employee ESTIMATE STATISTICS SAMPLE 2000 ROWS; If you issue the ANALYZE TABLE ESTIMATE STATISTICS command without defin- ing how many or what percentage of rows to sample, Oracle will, by default, use a 1064-row sample to estimate the statistics. Is there any difference between doing an ANALYZE ESTIMATE using ROWS or an ANALYZE ESTIMATE using PERCENT? Some DBAs have found that using PERCENT generates statistics that more closely align with the actual statistics of the object. We have seen consistently better performance of queries against objects where PERCENT was used, as opposed to ROWS. Thus, we strongly suggest that you use PERCENT when analyzing an Oracle object. NOTE If you attempt to analyze an index that has been marked UNUSABLE (such as a partitioned table and a partition of a global index), the ANALYZE operation will fail. You will need to rebuild the UNUSABLE index before you can analyze it. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 16. 326 CHAPTER 7 • ORACLE DATABASE MAINTENANCE Viewing Statistics The ANALYZE command populates many columns in the DBA_INDEXES, DBA_TABLES, _TAB_COL_STATISTICS, and DBA_TAB_COLUMNS tables. This also applies to the USER and ALL versions of these data dictionary tables. Table 7.4 lists all of the columns populated by the ANALYZE command, and Table 7.5 describes the commonly referenced columns. TABLE 7.4: TABLES POPULATED BY THE ANALYZE COMMAND Table (DBA, USER, and ALL) Columns with Analyze Results _INDEXES BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_ PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR, NUM_ROWS, SAMPLE_SIZE, LAST_ANALYZED _TABLES NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_ CNT, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS, SAMPLE_SIZE, LAST_ANALYZED _TAB_COL_STATISTICS NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE, GLOBAL_STATS, USER_STATS, AVG_COL_LEN _TAB_COLUMNS NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE TABLE 7.5: COMMONLY REFERENCED ANALYZE STATISTICS Column Table, Index, Description or Column LAST_ANALYZED All Date the object was last analyzed. SAMPLE_SIZE All Size of the last analyze sample. AVG_COL_LENGTH Column Average length of data in the table column. LOW_VALUE/HIGH_VALUE Column High value and low value for the data points contained in the column. NUM_NULLS Column Number of NULL values currently in the column. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 17. ANALYZING DATABASE OBJECTS 327 TABLE 7.5: COMMONLY REFERENCED ANALYZE STATISTICS (CONTINUED) Column Table, Index, Description or Column AVG_DATA_BLOCKS_PER_KEY Index Average of how many index blocks a single key resides in. AVG_LEAF_BLOCKS_PER_KEY Index Average of how many index leaf blocks a single key resides in. BLEVEL Index Height of an index—indicates the num- ber of levels from the root block to the node blocks. DISTINCT_KEYS Index Number of distinct keys present in the index. The closer this number is to NUM_ROWS, the more selective the PA R T index. LEAF_BLOCKS Index Number of leaf blocks contained in the II index. AVG_ROW_LEN Table Average length of a row in a table. You can multiply this by NUM_ROWS to dis- Oracle Database cover the total amount of data you are Administration actually storing in a table. AVG_SPACE Table Average amount of free space, in bytes, in a block in the table. BLOCKS Table Number of blocks in the table. CHAIN_CNT Table Number of chained rows in the table. CLUSTERING_FACTOR Table Clustering factor—indicates the ordering of the rows in the table as compared to the order in the index. A lower number is generally better. (See Chapter 16 for details.) EMPTY_BLOCKS Table Number of unused blocks allocated to the table above the high-water mark. NUM_ROWS Table/Index Number of rows in the table. Use this number to keep track of table growth. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 18. 328 CHAPTER 7 • ORACLE DATABASE MAINTENANCE Removing Statistics from an Object Using the DELETE STATISTICS clause of the ANALYZE command, you can remove sta- tistics that have been generated with the ANALYZE command. You can remove statis- tics on a table, cluster, or index. You can also delete statistics for a specific partition of a partitioned table. If you remove statistics from a table, the statistics of any associated index are also removed. Here’s an example of using the ANALYZE DELETE command: ANALYZE TABLE employee DELETE STATISTICS; So, why would you want to use the DELETE STATISTICS command? Perhaps you have decided not to keep existing statistics on a given object because you wish the queries using that table to use rules-based optimization (RBO), rather than CBO. Or, you might have objects that you don’t keep statistics on because you wish them to use RBO, yet you want to have the statistics available for management purposes. In this case, you would analyze the object, copy the statistics somewhere else, and then delete the statistics. For example, Chapter 15 describes how to generate average block usage reports for a table. If you are using Oracle in an RBO environment and you want to actually run these reports, you can analyze the table, run the report, and remove the statistics using the ANALYZE TABLE DELETE STATISTICS command. Validating the Structure of an Object The ANALYZE VALIDATE command allows you to validate the structure of tables, table partitions, indexes, and clusters. You might validate an object if you suspect all or part of it has become corrupted. Also, validating an index is a way to collect statis- tics that are not otherwise collected. Here is a summary of the effects of validating each type of object: Table validation Each row and each data block in the table is validated. If you wish to validate that all of the table’s rows are properly stored in all associ- ated indexes, use the CASCADE clause of the ANALYZE VALIDATE command. When you validate a partitioned table, in addition to the validation routines exe- cuted for normal tables, each row is checked to ensure that it is in the correct par- tition. If an invalid row is discovered, an entry is made in a table called INVALID_ROWS. Index validation Each data block in an index is validated and checked for corruption. However, no cross-checking is done against the table on which the index is built to make sure that all entries in the index are valid. Oracle also col- lects useful statistics that are loaded in tables called INDEX_STATS and INDEX_ HISTOGRAM, which store statistics from only one index at a time. For example, Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 19. DIAGNOSING DATABASE PROBLEMS 329 the INDEX_STATS table includes information about the number of distinct keys in the index, number and total length of deleted leaf rows in the index, how often the most repeated key is repeated, total space allocated and used in the B*Tree, and the height of the B*Tree. Cluster validation The structure of the cluster’s tables is validated. You can use the CASCADE keyword with the ANALYZE VALIDATE command to force vali- dation of the cluster table’s associated indexes. WARN I NG Use caution when validating an object. During the period of the valida- tion, you will not be able to make any changes to the object, and all DML activity on that object will wait for the validation to be completed. PA R T II Diagnosing Database Problems As a DBA, you know that databases occasionally have problems. Perhaps a process Oracle Database Administration keeps failing, or maybe the database keeps shutting down for what appears to be no good reason. Of course, you need to identify the cause of a problem before you can fix it. Oracle provides several tools that you can use for diagnosing and troubleshooting database problems. These include trace files, the alert log, the DBV utility, and the DBMS_REPAIR packages. Using Trace Files and the Alert Log Oracle trace files provides varying levels of information about the database. In partic- ular, they can provide some insightful information about why the database or an associated process has failed. The alert log is created by the database and can be used to monitor the database for ongoing events. Reviewing Process Trace Files Database processes generally create process trace files when they encounter some situation that causes process failure or could indicate some problem condition (for example, encountering a bad block that could be repaired). Sometimes, processes will create informational messages in their trace files. The location of process trace files is controlled by the init.ora parameter BACKGROUND_DUMP_DEST. The file- naming convention differs on different operating systems, but it generally includes Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com 20. 330 CHAPTER 7 • ORACLE DATABASE MAINTENANCE the process name in the name of the file. An example of a process trace filename on a Windows NT system is ora816ARC0.log, containing the trace file for the ARC0 archive logging process. Networking processes also create trace files. For example, the listener process cre- ates a listener trace file and stores it inORACLE_HOME/network/trace by default. If you have a database crash, be sure to look for trace files written about the time of the crash. Oracle will probably be interested in these as a part of the diagnosis process. N OT E Each ARCH process will create a trace file by default. The parameter LOG_ARCHIVE_TRACE is supposed to prohibit this action. There is a bug (1307166) that causes a trace file to be created, regardless of the setting of LOG_ARCHIVE_TRACE. This bug is corrected in Oracle8i version 8.1.7.0. Reviewing User Trace Files User trace files are generally created at the request of a user session. The trace files generated are created in the directory location specified by the init.ora parameter USER_DUMP_DEST. These trace files generally contain run information from specific sessions that can be analyzed for performance-tuning purposes. User trace files are also used by the system when the DBA issues the ALTER DATA- BASE BACKUP CONTROLFILE TO TRACE command at the SQL prompt. When issued, this command will create a trace file that contains the required SQL commands to re- create the control file of the database. We will discuss this command in more detail in Chapter 10. Finally, the system processes themselves will create trace files as they operate. These trace files can be helpful in cases where the database processes have crashed. Using the Alert Log You can use the alert log to monitor database events such as log switches, startup and shutdown, tablespace coalesce operations, and certain database error conditions. Like the process trace file location, the alert log location is controlled by the init.ora parameter BACKGROUND_DUMP_DEST. In terms of monitoring a production database, you should keep an eye on the alert log. Database problems that will be reported in the alert log include segments that could not extend and certain Oracle database errors. You will also find other impor- tant day-to-day operating messages in the alert log. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com