Take the Assessment Test immediately following this introduction. (The answers are at the end of the test.) Carefully read over the explanations for any questions you get wrong, and note which chapters the material comes from. This information should help you plan your study strategy.

Study each chapter carefully, making sure that you fully understand the information and the test objectives listed at the beginning of each chapter. Pay extra close attention to any chapter related to questions you missed in the Assessment Test.

Complete all hands-on exercises in the chapter, referring back to the chapter text so that you understand the reason for each step you take. If you do not have an Oracle database available, be sure to study the examples carefully.

Answer the Review Questions related to that chapter. (The answers appear at the end of each chapter, after the "Review Questions" section.) Note the questions that confuse or trick you, and study those sections of the book again.

Take the two Bonus Exams that are included on the accompanying CD. This will give you a complete overview of what you can expect to see on the real test.

Remember to use the products on the CD included with this book. The electronic flashcards and the Sybex Test Engine exam preparation software have been specifically designed to help you study for and pass your exam.

The process of configuring a database to back up the control file with each backup is called?

What is the new Oracle Database 10g feature that makes the channels more resilient?

What command would you use to set a persistent setting in RMAN so that backups are all written to a tape device?

The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter should be set to what value? (Choose all that apply.)

The BACKUP command has the ability to do what? (Choose all that apply.)

Which commands are required to perform a compressed RMAN backup? (Choose all that apply.)

The RATE option performs what function during RMAN backups?

What two values are required to identify a database incarnation within the recovery catalog?

What is the most efficient order in which to create a new default temporary tablespace named TEMP2 and have that tablespace available as the default tablespace for users?

What is the correct command sequence for recovering a missing tempfile named temp?

You are faced with a media failure for a tablespace that is currently read-only. The only backup of the read-only tablespace was made one week ago when the tablespace was read-write. What do you have to do to recover this tablespace? (Choose all that apply.)

User-managed recovery requires which major difference from using RMAN?

Why does control file recovery require special steps when using RMAN without the recovery catalog? (Choose all that apply.)

Logical corruption has been introduced into the database. You need to recover the database to the point-in-time prior to the introduction of the corruption. The logical corruption was introduced at 6:30 P.M. on September 6, 2004.

You have a backup from two days ago. It is discovered that corruption was introduced today at 6:30 P.M. You perform an incomplete recovery of the database to 6:25 P.M. before the corruption. One day later you have a media failure, and you need to recover but you only have the same backup that was used to perform an incomplete recovery prior to the corruption. What will happen to the recovery attempt for the media failure?

Where are the flashback database logs stored? (Choose all that apply.)

Which of the following statements are correct regarding warnings at flash recovery thresholds? (Choose all that apply.)

What files are considered transient in the flash recovery area? (Choose all that apply.)

Which initialization parameter will not create files in the flash recovery area? (Choose all that apply.)

What command is equivalent to the DROP TABLE command of Oracle 9i or earlier versions?

How would you assure that the triggers you need enabled are enabled and the triggers you need disabled are disabled after a Flashback Table recovery? (Choose all that apply.)

How can you identify multiple objects dropped with the same name in the Recycle Bin?

A user accidentally deleted a customer identified by 46435 from the customers table on September 6, 2004 at 3:30 P.M. Which of the following recovery approaches will successfully recover CUSTOMER_ID 46435 with the least impact on database operations?

DB_BLOCK_CHECKING performs checksums on modified blocks only on what database objects?

What is the correct syntax for performing a block media recovery for corrupt blocks in datafile 4 and 5 with blocks 5 and 6, respectively?

What is the name of the DBMS_REPAIR procedure used to identify index entries that point to corrupt data blocks?

What type of backup should not be used to support RMAN block media recovery (BMR)?

You create a new table, populate it, and build several indexes on the table. When you issue a query against the table, the optimizer does not choose to use the indexes. Why might this be?

You are concerned about keeping statistics up-to-date for the NOODLE table, a table that is the target of frequent DML operations. In response, another DBA issues the command ALTER TABLE NOODLE MONITORING. What is the result of this action? (Choose the best answer.)

You have created a new table. How long do you have to wait to be sure that Oracle has automatically gathered optimizer statistics for the table and loaded them into the AWR?

You are concerned about the operating system performance as well as SQL execution plan statistics. Which STATISTICS_LEVEL parameter would be the minimum required to ensure that Oracle will collect these types of statistics?

An ASM disk group can manage database files from how many different databases?

High redundancy disk groups must have how many failure groups? (Choose the best answer.)

Automatic Storage Management (ASM) disk group mirroring is done at which level?

Identify two valid types of Oracle instances.

A new workstation has been added at your location in Spain. This machine will be used to run an application that connects to your database in the United States. A technician installs the Oracle client and the application. The user on the new machine notices that performance seems to be sluggish compared to the existing workstations. The technician swears that the application is configured identically on all machines, and has ruled out network issues. What is the most likely cause? (Choose the best answer.)

You are setting up a new database instance that needs to support several languages. Conserving storage is a high priority. Point-in-time recovery is absolutely required. How would you go about choosing a character set to use?

Your database uses a single-byte character set and has the following parameters set:

NLS_LENGTH_SEMANTICS = CHAR

You create a table as shown here:

SQL> create table XYZ (
         NAME varchar2(30) );
Table created.

How many characters will you be able to store in the NAME column?

Your client NLS_LANG parameter is set to AMERICAN. You create a session to a server that has an NLS_LANGUAGE setting of FRENCH. You issue the following SQL statement:

ALTER SESSION SET NLS_LANGUAGE=ITALIAN;

Which language setting will govern client-side NLS operations?

You want to set up DRM to allocate CPU resources between seven resource consumer groups. The groups should be allocated only CPU unused by the SYS_GROUP group. Six of the groups will get 15 percent and the remaining group will receive 10 percent. Which of the following would achieve all of these objectives with the least amount of effort?

You are creating a complex plan. You need to limit the degree of parallelism for an existing consumer group. Which DRM element do you need to create?

You have finished building all of your DRM objects in the pending area. Your PC is currently down, so you ask another DBA to validate it from her PC. She sends you back the following screen capture:

SQL> begin
  2     dbms_resource_manager.validate_pending_area;
  3    end;
SQL> /

PL/SQL procedure completed successfully.

What do you know about the status of the pending area? (Select the best answer.)

You want to create the elements for a resource plan that allocates twice as much CPU to one group than to another. Which of the following do you use?

You want to create a scheduler job that is very similar to an existing job. Neither of these jobs requires any arguments. What is the best way to create the new job?

You execute the following PL/SQL block:

SQL> begin
  2      DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
  3      'LOG_HISTORY', '60'),
  4      end;
SQL> /

PL/SQL procedure successfully completed.

What is the result of this action?

You have created a new job by copying an existing job. When you attempt to execute the new job, it doesn't work. Why might this be?

Consider the index HR.IDX_PK_EMP on the table HR.EMPLOYEES and the following ALTER INDEX command:

ALTER INDEX HR.IDX_PK_EMP COALESCE;

Which of the following commands accomplishes the same task as this command? (Choose the best answer.)

Which type of queue is supported by sorted hash clusters?

If you have two redo log groups with four members each, how many disks does Oracle recommend to keep the redo log files?

Which of the following statements is not true about segment shrink operations?

What is the purpose of the overflow area for an index-organized table (IOT)?

You are concerned about ORA-00600 messages that have been appearing in the alert log, and you want to know if any new ones appear. Which Oracle feature might be of use? (Choose the best answer.)

You wish to restrict remote access to your database by using Valid Node Checking. Which file do you need to configure?

Several users call to report that their sessions appear to be hung. They mention that the problem started within the last few minutes. You attempt to connect, but your session hangs as well. What would be the best place to look for the cause of the problem?

A user sends you a SQL script that they say is running very slowly. The explain plan reveals no problems. How might you go about determining the problem?

B. The control file autobackup is the name of the process that automates the backup of the control file with every backup. See Chapter 1 for more information.

C. Automated channel fail over requires that multiple channels be allocated. If one channel fails, the backup or recovery operation will use the other channel to complete the operation. See Chapter 1 for more information.

C. The command that sets the persistent setting that directs RMAN to back up to tape is CONFIGURE DEFAULT DEVICE TYPE TO SBT. See Chapter 1 for more information.

C, E. The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter should never be set to 0 if you are using RMAN. If this value is set to 0, there is a potential to lose backup records. See Chapter 1 for more information.

B, C. The BACKUP command can take advantage of multiplexing datafiles to the same backup set. The BACKUP command can also use the block change tracking capability. See Chapter 2 for more information.

A, C. The correct methods of compressing a backup are to use the command BACKUP AS COMPRESSED BACKUPSET DATABASE and to set persistent settings by using CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET. See Chapter 2 for more information.

C. The RATE option limits the system resource usage that RMAN will consume performing backup operations. See Chapter 2 for more information.

A, C. The DBID and DB_KEY are required to identify the database incarnation when using SQL*Plus to query the recovery catalog tables. See Chapter 2 for more information.

B. The most efficient way to create a new default temporary tablespace named TEMP2 is to create the temporary tablespace TEMP2. Next, perform the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2 command. This will make TEMP2 the default temporary tablespace for all users. You can perform the ALTER USER username TEMPORARY TABLESPACE temp2 command, but this could be more time-consuming than identifying all the users. See Chapter 3 for more information.

C. The correct command sequence for recovering a missing tempfile named temp is as follows:

The database must be mounted, and then the tablespace information needs to be dropped from the data dictionary. Then the tablespace can be created. See Chapter 3 for more information.

B, C. You need to restore and recover the tablespace of the read-only tablespace because the tablespace was read-write when the backup was taken. If the tablespace was backed up read-write, the datafile has changed or has a different SCN in the datafile header. This will require recovery. See Chapter 3 for more information.

D. User-managed recovery requires the DBA to directly manage the recovery process. The DBA must determine which database files to restore and from what backup, as well as which archive logs to apply. See Chapter 4 for more information.

B, C. Control files contain the RMAN repository when not using the recovery catalog that contains metadata about the backups that are used to perform the recovery. This information is not available when recovering a control file. See Chapter 4 for more information.

D. The correct RMAN commands would be as follows:

run
{
set until time '06-SEP-2004 18:25:00';
restore database;
recover database;
}

The closest time to 6:30 P.M. would be 18:25. You need to use both the RESTORE DATABASE and RECOVER DATABASE commands. See Chapter 4 for more information.

B. You can now recover through an incomplete recovery, which uses RESETLOGS to open the database. In previous Oracle versions, you had to take a backup immediately following an incomplete recovery, because the redo log sequences got reset, making the backup unusable. See Chapter 4 for more information.

A, C. The flashback database logs must be stored in the flash recovery area. The actual location in the flash recovery area is determined by the DB_RECOVERY_FILE_DEST parameter. See Chapter 5 for more information.

A, D. At 90 percent full, a warning is sent to the alert log, and obsolete files are removed. At 95 percent full, warnings are set to the alert log. See Chapter 5 for more information.

A, D, E. Flashback logs, datafiles, and RMAN working files are all considered transient by Oracle. These files will be overwritten during certain events. Multiplexed redo logs and control files are considered permanent and will not be overwritten, even when the flash recovery area is backed up to tape. See Chapter 5 for more information.

A, B, D. LOG_ARCHIVE_DEST_DUPLEX is not a valid initialization parameter. LOG_ARCHIVE_DEST_n is the only archive log initialization parameter that writes archive logs to the flash recovery area. See Chapter 5 for more information.

C. The command equivalent to the DROP TABLE command of Oracle 9i or earlier is PURGE TABLE table_name. The PURGE TABLE recycle_bin_object command only purges from the Recycle Bin. See Chapter 6 for more information.

B, C. The Flashback Table command by default disables all triggers unless the ENABLE TRIGGERS option is used. If the ENABLE TRIGGERS option is used, all enabled triggers at the time of the Flashback Table recovery are enabled. All disabled triggers at the time of the Flashback Table recovery are disabled. The last option is to manually enable and disable all triggers after the Flashback Table recovery has been performed. See Chapter 6 for more information.

B. The RECYCLEBIN view OBJECT_NAME column will provide a unique name of a database object, even if the OBJECT_NAME is the same. See Chapter 6 for more information.

B. The Flashback Query will query the deleted customer 46453 from the undo data, and the insert command will add the customer back to the customers table. There will be minimal impact on the database. See Chapter 6 for more information.

B. DB_BLOCK_CHECKING reports on all database objects if DB_BLOCK_CHECKING is TRUE. See Chapter 7 for more information.

C. The correct syntax to perform a block media recovery for corrupt blocks in two datafiles is to execute multiple BLOCKRECOVER commands, one for each datafile and block. See Chapter 7 for more information.

A. The DUMP_ORPHAN_KEYS is designed to identify index entries that point to corrupt data blocks. See Chapter 7 for more information.

A. All incremental backups will not support BMR because they contain only changed blocks. See Chapter 7 for more information.

E. Because you aren't given enough information to determine the availability of statistics, nor are you privy to the query itself, you can't rule out any valid possibilities. All of the choices listed are valid reasons why the optimizer might choose to not use an available index. See Chapter 8 for more information.

B. The MONITORING option of the ALTER TABLE command is deprecated in Oracle Database 10g. While it will not produce an error, it is treated as no operation by Oracle. Automatic DML monitoring is used instead. See Chapter 8 for more information.

D. The AWR does not store optimizer statistics. It stores dynamic performance statistics. Optimizer statistics are stored in the data dictionary. See Chapter 8 for more information.

D. The STATISTICS_LEVEL parameter must be set to ALL in order to instruct Oracle to automatically collect operating system and SQL execution plan statistics. See Chapter 8 for more information.

C. An ASM disk group can manage database files for essentially an unlimited number of different databases. Creating ASM disk groups is discussed in Chapter 9.

D. High redundancy disk groups require disks in at least three failure groups, but they can contain more if a higher level of redundancy or performance is desired. Controlling disk group redundancy is detailed in Chapter 9.

B. Disk group mirroring for ASM is done at the extent level. To learn about Automatic Storage Management mirroring, see Chapter 9.

C, E. ASM instances manage ASM disk groups, and RDBMS instances are the traditional and only type of instance available before Oracle Database 10g. Configuring ASM and RDBMS instances for Automatic Storage Management is covered in Chapter 9.

B. The most likely cause is that the Oracle client environment is using a character set that does not match the server and is not a strict subset of the server character set. In this situation, Oracle will perform automatic data conversion, which can impact performance. See Chapter 10 for more information.

E. If a single-byte character set can be found that fulfills all of your language requirements, you will save storage space over using a multi-byte character set. See Chapter 10 for more information.

B. A single-byte character set always uses only one byte to store a character, so using byte semantics isn't any different from using character semantics. See Chapter 10 for more information.

A. Client-side NLS operations are governed by the NLS_LANG environment variable settings. The server session was affected by the ALTER SESSION command, but it has no effect on client-side NLS operations because they don't involve the server session. See Chapter 10 for more information.

A. A simple plan can allocate CPU resources for up to eight consumer groups at the same level. By default, SYS_GROUP will be allocated 100 percent of level 1 CPU, and all other CPU allocation is done at level 2. Therefore, a simple plan will meet all of these requirements. See Chapter 11 for more information.

D. A resource plan directive needs to be created, which will define the plan, the consumer group, and resource allocation. See Chapter 11 for more information.

E. There can only be one pending area, so there is no question of whether it was the right one. The problem is that you don't know whether she issued the set serveroutput on statement before executing the procedure. Therefore, you don't know if errors were found, but were not displayed. See Chapter 11 for more information.

C. The resource allocation method is defined at the plan level, not at the plan directive level. The resource allocation method of RATIO indicates that the amounts allocated in subsequent plan directives will represent a weighted amount, rather than a percentage. See Chapter 11 for more information.

A. Scheduler jobs can be copied by using the DBMS_SCHEDULER.COPY_JOB procedure. See Chapter 12 for more information.

E. The SET_SCHEDULER_ATTRIBUTE procedure sets the purge rule for both job and window logs by default. If the WHICH_LOG parameter is specified, the procedure sets the purge rule for one specific log type. See Chapter 12 for more information.

C. By default, copied jobs are created in a disabled state. See Chapter 12 for more information.

A. Using the CASCADE keyword in any segment shrink operation will shrink the free space in any dependent objects such as indexes. Chapter 13 discusses segment shrink functionality.

C. Sorted hash clusters are similar to standard hash clusters, except that they store data sorted by non-primary key columns and make access by applications that use the rows in a first in, first out (FIFO) manner very efficiently; no sorting is required. Chapter 13 describes how sorted hash clusters are created and used.

D. Oracle recommends that you keep each member of a redo log group on a different disk. You must have a minimum of two redo log groups, and it is recommended that you have two members in each group. The maximum number of redo log groups is determined by the MAXLOGFILES database parameter. The MAXLOGMEMBERS database parameter specifies the maximum number of members per group. See Chapter 13 for more information.

B. During the compaction phase, locks are held only on individual rows, causing some minor serialization with concurrent DML operations. For more information about segment shrink, see Chapter 13.

B. If an IOT row's data exceeds the threshold of available space in a block, the row's data will be dynamically and automatically moved to the overflow area. For more information about index-organized tables, see Chapter 13.

C. Server-generated alerts would be the best answer. Oracle has a predefined alert that detects ORA-00600 messages in the alert log and will raise an alert when they are found. See Chapter 14 for more information.

D. The sqlnet.ora file must be manually edited to configure Valid Node Checking. See Chapter 14 for more information.

B. The alert log would be the best place to look for information. EM Database Control does allow alerts to be viewed; however, the metrics are unlikely to have been updated because the problem occurred within the last few minutes. See Chapter 14 for more information.

B. Running the script with SQL tracing enabled will produce a trace file detailing the execution of the SQL. This file can be used to troubleshoot the performance problem. See Chapter 14 for more information.