As Published In January/February 2004

Concepts and sample questions for OCP upgrade certification

The Oracle Certification Program recently launched Oracle9i DBA New Features for Oracle7.3 and Oracle8 OCPs (Exam #1Z1-035), an exam that allows Oracle7.3 and Oracle8 DBA Certified Professionals to upgrade to the Oracle9i DBA certified professional credential by taking one comprehensive exam. Prior to the release of this exam, Oracle7.3 Certified Professionals had to take three exams and Oracle8 Certified Professionals had to take two exams to upgrade to the Oracle9i DBA certified professional credential.

The Oracle9i DBA certified professional credential continues to be valued among technical professionals. Additionally, the Oracle9i DBA certified professional credential is required for candidacy into Oracle's premier Oracle Certified Master certification; it also positions individuals for the upcoming upgrade to the Oracle Database 10g certification paths.

This is the second of two columns addressing new feature topics that you may encounter on the Oracle9i DBA New Features for Oracle7.3 and Oracle8 OCPs upgrade exam. This column focuses on LogMiner and segment management.

LogMiner Enhancements

The LogMiner utility was initially introduced in Oracle8. The intent of the LogMiner utility is to provide DBAs with the ability to view the contents of the redo log files for purposes of troubleshooting and maintenance. The LogMiner utility has been significantly enhanced, and new LogMiner features in Oracle9i allow DBAs to do the following:

• Skip log corruptions
• Create LogMiner tables in an alternate tablespace
• Generate SQL_REDO and SQL_UNDO with primary key information

Additionally, the LogMiner viewer provides DBAs with a GUI for LogMiner.

What statements about LogMiner are true?

A. Starting with Oracle9i, the DDL issued by the user is logged as part of the DDL transaction.
B. The ability to view committed transactions is available only for Oracle9i and higher.
C. DDL support is available only for Oracle9i and higher generated log files.
D. The ability to query on actual data values in redo log files is available for Oracle8 and higher.
E. LogMiner provides the ability to skip log corruptions.
F. DDL statements are mapped to several DML statements on internal tables.

The correct answers are A, C, D, and E. The statements in answers B and F are not true. The ability to view committed transactions was available as of Oracle8. On the other hand, enhancements to how DDL statements are logged in the redo log files have simplified the task of determining what transactions have occurred. For example, with Oracle9i the actual DDL statement is logged in the redo log files, and LogMiner displays this DDL statement along with the set of DML statements.

Which procedure enables a DBA to extract database dictionary information to either a flat file or the redo logs?

A. DBMS_LOGMNR_D.BUILD
B. DBMS_LOGMNR.START_LOGMNR
C. DBMS_LOGMNR_D.SET_TABLESPACE
D. DBMS_LOGMNR.COMMITTED_DATA_ONLY
E. DBMS_LOGMNR_D.STORE_IN_FLAT_FILE

The correct answer is A. Two options are available when using the DBMS_LOGMNR_D.BUILD procedure: DBMS_LOGMNR_D.STORE_IN_FLAT_FILE and DBMS_LOGMNR_D.STORE_IN_REDO_LOGS. For example, to extract database dictionary information to a flat file, I would execute the following command:

SQL> Execute dbms_logmnr_d.build -
  2 (DICTIONARY_FILENAME => 'dictionary.ora' -
  3 ,DICTIONARY_LOCATION => 
'/oracle/database' -
  4 ,OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE );

The flat file created will have the name dictionary.ora and will be stored in the /oracle/database directory.

The directory in which a flat file created by the DBMS_LOGMNR_D.BUILD procedure will be stored must be the same as that specified by what parameter?

A. USER_DUMP_DEST
B. BACKGROUND_DUMP_DEST
C. CORE_DUMP_DEST
D. UTL_FILE_DIR

The correct answer is D. Therefore, to support the command I previously executed, the init.ora file would contain the following parameter setting:

UTL_FILE_DIR = '/oracle/database'

The actual format of the value is dependent upon the operating system.

An additional consideration when extracting database dictionary information to flat files versus redo logs is that, while extracting flat files does not consume as many resources, there is the possibility that the information will be out of sync with the redo logs to be analyzed. Consequently, having the dictionary in the redo logs prevents the problems of inaccurate correlation with the redo logs.

It is also important that the DBA be aware of some restrictions when using LogMiner, such as visibility to some datatypes and segments.

Select the datatypes or segments that are not supported by LogMiner:

A. Object types
B. Collections
C. Object refs
D. Bitmapped indexes
E. Index organized tables (IOTs)

The correct answers are A, B, C, and E. LogMiner does support bitmapped indexes. Once you have invoked DBMS_LOGMNR.START_LOGMNR, you can query the V$LOGMNR_CONTENTS view to see what datatypes and segments are supported. Be sure to review the LogMiner material in preparation for the Oracle9i DBA New Features for Oracle7.3 and Oracle8 OCPs exam.

Segment Management

Automatic segment management in Oracle9i Database provides a new scheme for managing free space inside database segments. It provides the capability to track in-segment free and used space through bitmaps, as opposed to the previously used free lists.

A set of bitmap blocks (BMBs) is stored within segments that have automatic space management.

Select a valid characteristic of automatic space management:

A. BMBs are organized in a tree hierarchy with a maximum of four levels.
B. Segments using automatic space management can be created only in locally managed tablespaces.
C. The root level of the hierarchy that stores the references to all intermediate BMBs is stored outside of the segment header.
D. When an INSERT is performed, BMBs are acquired in exclusive mode.

The correct answer is B. Remember that in Oracle9i, tablespaces are created as locally managed by default, so this should not pose much of a problem. With regard to the other answers, the tree hierarchy for BMBs is limited to three levels; the root level of the hierarchy that stores the references to all intermediate BMBs is stored in the segment header; and during an INSERT, BMBs are acquired in shared mode.

Which one of the following commands is valid for creating a segment that uses automatic space management?

A. CREATE TABLESPACE examprep EXTENT MANAGEMENT dictionary;
B. CREATE TABLESPACE examprep EXTENT MANAGEMENT local;
C. CREATE TABLESPACE examprep EXTENT MANAGEMENT dictionary SEGMENT SPACE MANAGEMENT auto;
D. CREATE TABLESPACE examprep EXTENT MANAGEMENT local SEGMENT SPACE MANAGEMENT auto;

The correct answer is D. The tablespace must be created as a locally managed tablespace, and SEGMENT SPACE MANAGEMENT must be set to auto.

In order for the correct CREATE TABLESPACE command to work, the database environment must use Oracle Managed Files (OMF). When using OMF, the correct CREATE TABLESPACE command will create the datafile for the tablespace in a prespecified location. OMF is another topic you should be familiar with for this exam.

Next Steps GET certified oracle.com/education/certification The Oracle9i DBA New Features for Oracle7.3 and Oracle8 OCPs exam allows Oracle Certified Professionals to easily upgrade their certification credentials. The Oracle9i DBA Certified Professional credential will be required as part of the Oracle Database 10g upgrade certification path and is one of the requirements for attending the Oracle9i DBA Certified Master practicum. Visit oracle.com/education/certification for more information about the Oracle Certification Program and to download a free exam candidate guide.

Numerous performance improvements to segment usage have been introduced in Oracle9i. Included in these improvements is the index monitoring feature. Although indexes can provide optimized access to data, they also result in performance degradation, since they have to be maintained when changes are made to their associated data segments. In Oracle9i Database, a DBA can monitor index usage and determine whether an index is being used. If the index is not being used, the DBA can drop the index and thereby eliminate unnecessary statement overhead. What view would a DBA query to determine if an index has been used?

A. V$STATISTICS_LEVEL
B. V$OBJECT_USAGE
C. V$SEGMENT_STATISTICS
D. V$SEGSTAT_NAME

The correct answer is B. Before using the V$OBJECT_USAGE table, the DBA must enable monitoring by using the ALTER INDEX <index_name> MONITORING_USAGE command. Within the view, the DBA can query the MONITORING and USED columns; those columns will indicate if monitoring is enabled for the index and, if so, whether the index has been used.

The V$STATISTICS_LEVEL view lists the status of the statistics or advisories controlled by the STATISTICS_LEVEL initialization parameter. The V$SEGMENT_ STATISTICS view has information about segment statistics along with segment owner and tablespace name. The V$SEGSTAT_NAME view lists the segment statistics being collected and the properties of each statistic.

One advantage of monitoring segment-level statistics is that as a DBA you can determine performance problems associated with individual segments. The views V$SEGMENT_STATISTICS and V$SEGSTAT_NAME come in handy by providing an initial set of statistic values for segments. Be sure to set the STATISTICS_LEVEL parameter to at least TYPICAL to start collecting these statistics.

Other segment management topics to study for the exam include creating and using bitmap join indexes, describing cursor-sharing enhancements, and identifying cached execution plans.

Conclusion

This column concludes the discussion about topics associated with the Oracle9i DBA New Features for Oracle7.3 and Oracle8 OCPs exam. The next column will cover topics associated with the Oracle9i DBA Oracle Certified Master (OCM) practicum, which is the highest level of certification an Oracle Certified Professional can obtain.

Jim DiIanni (ocpexam_ww@oracle.com) is the certification director for Oracle Certification Programs.