Previous | Table of Contents |

developer.com - Reference Library

Page 863

APPENDIX D
Oracle Certification Programs

In this appendix

Page 864

Benefiting from Technical Certification

Information technology is evolving at an alarming rate. To stay competitive, corporations must offer more comprehensive and diverse products and services to their customers. For many corporations, this means implementing newer and more complicated technology in areas such as automated customer services, online ordering and account checking, historical data tracking, and so on. In addition, company needs and expectations in regards to the availability of their data are changing. Where once one-quarter of online sales data was sufficient, corporations now need access to a full year. Where one year of financial data was enough, we now need five. We're storing more data online, and more and more data is expected to be instantly available.

To keep up with these changing needs, managers are under pressure to hire and retain top information technology professionals. In many cases, the managers themselves are not able to judge the skill level of applicants in specific technologies. Many companies turn to personnel management firms to supply qualified candidates, hire third-party consulting firms to screen applicants, or buy canned testing packages for specific technologies. The boom in demand for IT consultants can also be partly attributed to the peace of mind their use gives managers. A third-party vendor, who can be easily replaced if the product does not live up to expectation, fills requests for specific skills and abilities.

Technical certification is one way individuals and corporations can ensure that employee's skills and abilities are given the value and recognition they deserve. To a corporation, technical certification is a means of measuring an employee's skill level, or a way to establish a minimum level of qualification for a position. For the employees, it is a way to establish their knowledge and expertise in their chosen field—an unbiased verification of their worth and value.

The Oracle Certified Professional Program

The Oracle Certified Professional (OCP) program offers certification programs based on the different job functions of Oracle professionals. The OCP program is sponsored, administered, and maintained through Oracle corporation, and the material covered throughout the certification closely matches the material in their education courses. The OCP program is similar to the Novell CNA/CNE or the Microsoft MCSE program. Each certification track has a series of tests, each of which must be taken and passed to earn the certification. Each test deals with a general aspect of the certification job role and is further broken down to deal with the various subjects related to the test. With this breakdown of subject matter, certification can take a deliberate and planned approach, concentrating on one subject area and moving on when that area has been mastered. Currently, only the Certified Database Administrator track is available, but look for more certification tracks in the coming year.

Each certification test consists of a series of multiple-choice questions that must be answered within a certain time frame. The test is issued by computer and may use figures and charts as examples or exhibits for a particular question. No two tests are the same—each test draws from a pool of thousands of questions. Each question of the tests falls within one of the categories of the test topic and is given a weight according to the difficulty or complexity of the

Page 865

question. To successfully complete a test, you must correctly answer questions of varying difficulty in each of the testing areas the certification exam covers.

The tests themselves are similar in nature to the Novell or Microsoft certification tests. There is no passing percentage for a test. Rather, it is required that you are able to satisfactorily answer enough questions of different difficulty levels for each test category. Your certification is complete when you have successfully passed all the required certification tests in a certification track. Oracle provides and recommends classroom or computer-based training courses to prepare for certification tests, and the test questions themselves are based on the curricula of their instructor-led training courses. Practical real-world experience, however, is the most valuable test preparation of all. The tests are crafted in such a way that mere book studying is rarely enough to earn a passing grade. Rather, the analytical and judgment skills learned from real-world experience provide the knowledge necessary to pass the tests.

The tests can be taken at any authorized Sylvan Prometric Testing site and each test costs $150.00. Failed tests can be retaken a month from the original testing date. While no limit is placed on the number of times a test can be taken, the test fee must be paid for each test sitting. Test results indicate which subject areas were missed, serving as guides as to what material needs to be revisited and learned for subsequent attempts to pass the test.

Becoming an Oracle Certified Database
Administrator

Currently, the only certification track offered in the OCP program is for the Certified Database Administrator. Database administration is a complex and diverse field, and a successful DBA must possess a wide range of knowledge and skills to perform his or her job. The Certified Database Administrator certification attempts to ensure that its recipients meet a high level of expertise and skill.

Currently, the DBA certification covers Oracle version 7.3. The test will be updated to include Oracle8 material in the first half of next year. It is expected that all persons holding DBA certification at that time will take the necessary steps to upgrade their skills and keep their certification current. Special program offerings will be available to help update Oracle7 certified professionals into the Oracle8 curriculum. The tests are currently platform independent, but certification exams on specific platforms may be released in the future.

Describing the Program

The Certified Database Administrator program consists of the following four tests:

Page 866

To receive certification, you must successfully complete all four of these tests. The subject matter for each test is covered in the Oracle training courses of the same name.

The first test, Introduction to Oracle: SQL and PL/SQL using Procedure Builder, covers the basics of the Oracle database. This test concentrates on topics familiar to Oracle "power users," rather than database administrators, and will pose little problem to the experienced Oracle DBA.

The second test, Oracle7 Database Administration, is an overview of the general aspects of database administration. The remaining tests concentrate on two of the most important responsibilities of the typical DBA: Database Backup and Recovery, and Database Tuning. An in-depth understanding of how Oracle internally supports database fault tolerance and recoverability is crucial to passing this test.

For many participants, the Tuning exam may prove to be the most difficult test to pass. This is because most of the topics in this test deal with the internal operation of the database—knowledge that is very difficult to internalize through classroom or book training. A comprehensive knowledge of the init.ora parameters and the data dictionary tables and views related to performance tuning is necessary, as well as a solid understanding of how data is read from and written to the database. Because of the sheer amount of material that must be understood to pass this test, expect this exam to be your most intimidating.

Preparing for the Tests

The OCP certification tests have been designed so that book and classroom information provide only a fraction of the total knowledge needed to pass the tests. Just as important to successfully passing these tests is the problem-solving and "real-world" experience gained from working with the Oracle toolset, day in and day out. Each test heavily concentrates on solving problems related to scenarios or situations, rather than filling in the blank. While some people may be able to pass the tests through studying and memorization, most of us won't be so lucky and will have to rely on an equal mix of what we've studied as well as what we know. But for those with a great deal of Oracle experience, preparing for and ultimately passing the tests should be much easier.

The most effective and thorough method to prepare for the DBA Certification tests is to take the associated classes from Oracle. The material in each of the tests is based on the curriculum of the Oracle class of the same name. This method, however, can be quite costly and depending on your experience and expertise with Oracle, unnecessary. Computer-based training is also available from Oracle, as well as classes through the Oracle Channel, and even over the Internet. Contact Oracle Education to tailor a training program that fits your needs and abilities.

Oracle offers a training assessment program to help determine whether you are ready for the real tests and what areas you might need help in. A free version of the program is available from the Oracle Education Web site, and hundreds of sample questions for each test can be purchased for $99 per test. The additional test questions can be ordered from Self Test Software (http://stsware.com) the makers of the assessment test program. These self-tests are

Page 867

excellent resources to test your knowledge as well as to familiarize yourself with the testing environment, because the self-test software program is modeled after the actual Sylvan Prometric tests.

The Oracle Education Web site (http://education.us.oracle.com) has a number of white papers and PDF documents relating to the Oracle Certified Professional program, as well as the DBA Certification. This site has the most up-to-date information on all of the technical and administrative issues relating to certification, including the current test fees, authorized testing centers, and the latest OCP program offerings. Be sure to check out the resources on this site before making any certification plans.

In addition to these resources, other Oracle texts can provide excellent reference material. The online manual set shipped with each Oracle server set is an excellent source to look up specific topics and testing areas. The next section will detail where in this book you can find material dealing with each test area.

The following sections provide details on the topics covered in each Certified DBA Exam and the chapters in this book that can help you with those topics. Although this book has not been specifically written as a study guide for the certification exam, the material contained within will be of great value to you as you prepare for certification.

Introduction to Oracle: SQL and PL/SQL Using Procedure Builder Neither SQL or Procedure Builder is covered in this book—knowledge of basic SQL syntax and usage, such as issuing queries and creating segments, is assumed, and Procedure Builder is outside the scope. Two chapters, however, will be of help in regards to the PL/SQL and SQL*Plus questions in the test: Chapter 10, "PL/SQL Fundamentals," and Chapter 8, "SQL*Plus for Administrators." In addition, Chapter 2, "Logical Database Design and Normalization," covers the data modeling and database design questions that fall within the scope of this test. Finally, this test also includes questions regarding user access to database objects. Chapter 23, "Security Management," covers this material in depth.

According to Oracle's White Paper on the Certified Oracle DBA Track of the OCP program, topics covered by this test include the following:

Page 868

The following are examples of the types of questions you may be asked when taking this test:

Q.) What type of relationship describes the situation in which an employee can have only one department, but a department can have many employees?

A. recursive
B. one-to-one
C. one-to-many
D. many-to-one
E. many-to-many

Q.) In what queries would it be appropriate to use the keyword HAVING?

A. When you need to eliminate duplicate rows in the result set
B. When you need to order the result set by category
C. When you are performing a grouping calculation
D. When you need to restrict the groups of rows returned

Q.) What function would you use to find the position of an arbitrary search string in a string?

A. SUBSTR
B. TRANSLATE
C. INSTR
D. REPLACE
E. FSEEK

Page 869

Q.) Which of the following commands will end a transaction? (Choose one or more answers.)

A. SELECT
B. ROLLBACK
C. UPDATE
D. DELETE
E. CREATE TABLE
F. GRANT

Q.) What command would you enter to remove a primary key constraint?

A. ALTER TABLE
B. ALTER INDEX
C. DROP INDEX
D. DROP CONSTRAINT
E. DROP PRIMARY KEY

Q.) What keyword defines the PL/SQL block used for error-handling code?

A. IF ERROR
B. EXCEPTIONS
C. ON ERROR
D. ABEND
E. WHEN OTHERS

Q.) If a DELETE command is specified with no WHERE clause, what will happen?

A. The table will be deleted.
B. The first row will be deleted.
C. All rows will be deleted.
D. An error will be returned.
E. No rows will be deleted.

Q.) What database construct is used to group privileges?

A. System privilege
B. Object privilege
C. Table privilege
D. Role
E. Group

Q.) What objects are found in the USER_TABLES view?

A. Information on all tables in the database
B. Information on all tables the user can select from
C. Information on all tables the user owns
D. Information on all system tables

Page 870

Oracle7 Database Administration The topics covered in the Oracle7 Database Administration test are well represented in this book. The Oracle7 architecture is covered in Chapter 6, "The Oracle Database Architecture." Starting up and shutting down the database, as well as database creation procedures, are covered in Chapter 7, "Exploring the Oracle Environment." Database administration and management topics are covered in Part VI, "Managing the Oracle Database." The test covers all aspects of database administration, and most of the information you will need can be found within the individual chapters of Part VI. Finally, Chapter 14, "SQL*Loader," covers the material you'll need to answer questions on this topic.

According to Oracle's White Paper on the Certified Oracle DBA Track of the OCP program, topics covered by this test include the following:

The following are examples of the types of questions you may be asked when taking this test:

Q.) What background process is updating database datafiles with the information in the data buffer?

A. LGWR
B. DBWR
C. CKPT
D. ARCH
E. SMON

Page 871

Q.) What background process must be running for online redo logs to be automatically copied to disk?

A. ARCH
B. SMON
C. DBWR
D. REDO
E. PMON

Q.) If a database is open, and you want to place it in mount stage, what command(s) must you issue?

A. ALTER DATABASE SHUTDOWN MOUNT;
B. ALTER DATABASE MOUNT;
C. SHUTDOWN; then STARTUP MOUNT;
D. SHUTDOWN MOUNT;
E. STARTUP MOUNT;

Q.) What parameter(s) determine the size of the next extent of a database segment?

A. INITIAL
B. NEXT
C. OPTIMAL
D. PCTINCREASE
E. ADJUST

Q.) In the following SQL statement, what line will generate an error?

1     Create rollback segment rbs01
2    Tablespace rbs
3    Storage ( initial 512k
4        Next 512k
5        Minextents 6
6        Maxextents 119
7        Pctincrease 5
8        Optimal 3M);
A. 2
B. 4
C. 5
D. 6
E. 7
F. 8

Page 872

Q.) In the following series of statements, where all employees start with a salary of 0, what is the final outcome?

update emp set salary = 500 where empid = 23;
savepoint a;
update emp set salary = 1000 where empid = 42;
savepoint b;
rollback to savepoint a;
update emp set salary = 750 where empid = 42;
savepoint c;
update emp set salary = 1250 where empid = 23;
rollback to savepoint c;
A. Employee 23 will have a salary of 500; employee 42 will have a salary of 750.
B. Employee 23 will have a salary of 500; employee 42 will have a salary of 0.
C. Employee 23 will have a salary of 0; employee 42 will have a salary of 0.
D. Employee 23 will have a salary of 1250; employee 42 will have a salary of 750.
E. Employee 23 will have a salary of 1250; employee 42 will have a salary of 1000.

Q.) What construct is used to set database resource limits?

A. Quota
B. Profile
C. Allocation Role
D. Default Role
E. Cost Optimizer

Q.) A developer is granted the connect, resource, and select_any roles. She attempts to create a procedure that utilizes information stored in the dba_tables view. When logged into SQL*Plus, she has no problems selecting from dba_tables, but her procedure fails to compile. What is the most likely cause of the problem?

A. Stored PL/SQL programs cannot access data dictionary views or tables.
B. You must be logged in as SYS to access data dictionary views or tables in stored PL/SQL programs.
C. You cannot create stored PL/SQL programs using privileges that are granted from roles.
D. You must use the fully qualified sys.dba_tables name when accessing dba_tables in a PL/SQL procedure.

Q.) A database has the audit_trail parameter set to DB. Where is the auditing information stored?

A. The background_dump_dest directory.
B. The dba_audit table.
C. The sys.audit$ table.
D. The sys.security$ table.
E. Auditing information is stored in memory when audit_trail is set to DB.

Page 873

Q.) What is the easiest way to move an index from one tablespace to another?

A. Export and Import
B. ALTER TABLE REBUILD INDEX
C. ALTER INDEX REBUILD
D. RENAME INDEX
E. SQL*Loader

Oracle7 Performance Tuning The tuning exam will test not only your knowledge of database and application tuning, but also your understanding of the internal mechanisms of the Oracle database. Chapters 5 and 6, which cover the workings of the database and instance, also cover the background internal information you need to know. Part VIII, "Performance Tuning," is devoted entirely to tuning topics and covers most of the pure application tuning issues that are covered in the exam. Chapter 22, "Identifying Heavy Resource Users," provides information on tracking down database bottlenecks and performance issues. You should also reference Chapter 25, "Integrity Management," for information on locks and latching, both of which appear on the tuning exam.

According to Oracle's White Paper on the Certified Oracle DBA Track of the OCP program, topics covered by this test include the following:

Oracle7 Backup and Recovery Workshop Finally, the DBA's nemesis: Backup and Recovery. For this test, you will want to understand the use of rollback segments, redo logs, and the background processes and memory areas that help support the recoverability of the Oracle database. This information can be found in Chapters 5 and 6, but for your focus on this topic, look to Chapter 24, "Backup and Recovery," for coverage of Oracle backup and recovery principles and procedures. This chapter covers the different types of backup strategies, methods for implementing strategies, as well as presenting the technical explanation of the backup and recovery procedures.

Page 874

According to Oracle's White Paper on the Certified Oracle DBA Track of the OCP program, topics covered by this test include the following:

The Certified Database Administrator Program

Another source of Oracle certification comes from the Certified Database Administrator (DBA) exam, which is administered by the Chauncy group. This test differs from the OCP program in several key areas: One, it was designed and developed by a committee of expert database administrators from the "real world," two, it was developed in cooperation with and is an offering from the International Oracle Users Group _ Americas, and three, it is not under the jurisdiction of the Oracle Corporation.

In practice, the difference you will find between these two programs is the subject matter and purpose of the certifications. In the OCP program, the goal of certification is to prove that you have mastered the content of the applicable Oracle Education courses and are prepared to implement or use the knowledge within to solve real-world problems. The Certified DBA program, however, was developed without a specific series of courses or topics in mind, and instead focuses on the actual day-to-day knowledge and skills necessary for a DBA to perform his or her job well. The questions are therefore targeted at a broader knowledge base, and the test cannot be passed with mastery of a set number of topics or chapters from a book. The Certified DBA program is truly a certificate of real-world mastery over the DBA job role.

Description of the Program

The following chapters are taken from the Candidate Bulletin of Information, provided by the Chauncey group. You can order a copy of this bulletin by calling the Chauncey group at 800-258-4914, or by visiting their Web site at www.chauncey.com.

Page 875

The Oracle7 Database Administrator (DBA) Examination measures an individual's mastery of the knowledge, skills, and abilities necessary to perform the job of Oracle7 DBA proficiently. The Certified Oracle7 DBA designation documents the attainment of this level of competence. It is useful to candidates in assessing their qualifications and abilities and to employers in making hiring decisions and evaluating the applicable technical skills of existing staff for diverse purposes.

The Oracle7 DBA Examination consists of 60 computer-delivered, multiple-choice or free-response questions in a variety of formats. Candidates are asked to choose the best answer for each question. Each question is scored separately and only correct responses will contribute to a candidate's final score. Although no candidate is expected to obtain a perfect score, candidates are expected to have a high degree of familiarity with Oracle7. Oracle7 DBA Examination candidates will be given 1 hour and 45 minutes to complete the examination and a brief biographical questionnaire. An additional 15 minutes is allotted for a pretest tutorial and exit evaluation. The Oracle7 DBA Examination is available year-round at participating Sylvan Prometric Centers. The fee is $195 each time you take the examination.

The Oracle7 DBA Examination offers multiple-choice questions in which a candidate selects one of several displayed choices. It also features scenario questions in which a candidate is provided with a variety of on-line exhibits as part of the testing process. There are also questions in which a candidate is asked to identify incorrect information in a command or process. All the questions are designed to measure knowledge of Oracle7, breadth of preparation, and both analytical and problem-solving skills.

The examination covers seven major content areas. The major emphasis is placed on the comprehension and application of concepts, principles, and practices rather than on the recall of isolated facts. Some questions, however, are based on knowledge of specific terms, tools, and techniques.

The content areas and a representative description of topics covered in each category are provided below:

I. Oracle Architecture and Options (11_13%)

II. Security (13_15%)

Page 876

III. Data Administration (11_13%)

IV. Backup and Recovery (16_18%)

V. Software Maintenance and Operation (13_15%)

VI. Resource Management (13_15%)

VII. Tuning and Troubleshooting (13_15%)

Page 877

Page 878

Previous | Table of Contents |