The Blog from the DBA Classroom

By: Joel Goodman

Archive for March, 2009

Spring User Group and Seminar Schedule

Posted by Joel Goodman on 25/03/2009

I will be presenting at various user groups this spring and also deliver Oracle University seminars in various EMEA countries. Please visit my events diary for details.

Posted in Oracle | Leave a Comment »

Shared Sessions and Shared Servers

Posted by Joel Goodman on 19/03/2009

One of my roles in Oracle is that of “Ambassador” or “Liaison” to several of the technical SIG meetings held by the UKOUG that are of interest to DBAs. They are the RDBMS, UNIX, RAC & HA and Oracle on Windows SIGs.

At the RDBMS SIG held in Slough on 17/03/2009 a new format for one of the presentations was piloted with good results.  The Session was called “Bits and Pieces” and invited SIG delegates to speak spontaneously for as little as five minutes to share useful tips and experiences with their colleagues. So in effect this was a “Shared Session” of 30-45 minutes.

The session was started with a 10-15 minute presentation by Paul Hancock who discussed options for Installation and covered Interactive Installs, Silent Installs using a responsefile and Cloning of Oracle Homes.

There were few other volunteers prepared to have a go and there was time so I volunteered to share some experiences that I had encountered recently which generated some lively discussion and debate. Here is one of them:

SYSDBA Connections Vs Normal Connections

I am often asked in the DBA Classroom about differences if any between sessions connected as SYS or as another user with the DBA Role. The easy answer is that SYSDBA has additional priveleges and can start up or shut down database instances but there is more. I presented a Master class on Database Links for the US RAC SIG earlier this month and discovered a more complicated distintion whilst writing part of my demo script.  In attempting to show how Shared Database Links work in conjunction with Shared Server Connections I discovered that  SYSDBA sessions do not release the shared server that processes the first network round trip after login.

To demonstrate this I have done the following:

1. Set max_shared_server = 2

2. Connected one normal session using shared server as user SYSTEM

3. Connected one SYSDBA session using shared server as user SYS

4. Connected one normall session using local bequeath  as user SYSTEM

I then did the following query from the local bequeath session:

SQL> select name,circuit,status from v$shared_server;

——–      ———–               ——————–
S000      25D6C85C        WAIT(RECEIVE)
S001       00                   WAIT(COMMON)

Shared Server S001 is in the usual “WAIT(COMMON)” state waiting for input from any dispatcher process on the common inbound queue. But Shared Server S000 is in the “WAIT(RECEIVE)” state waiting for input from the session.

By joining v$session, v$circuit and v$shared_server it was easy to prove that the SYSDBA session was the culprit:

SQL> select,  s.status,  s.circuit, c.status, n.username
2  from v$shared_server s, v$circuit c, v$session n
3  where s.circuit = c.circuit and c.saddr = n.saddr;

——–    ————————         ————–    ————-   —————–
S000    WAIT(RECEIVE)    25D6D1F8   NORMAL   SYS

After discussing this with some support colleagues it was confirmed that the kernel treats both SYSOPER and SYSDBA sessions differently and that these sessions do NOT share their servers when using shared server. This means that several idling sessions connected as SYSOPER or SYSDBA could severely deplete the shared server pool and adversely affect performance.

The recommendation of logging in as another user with the DBA role rather than as SYSDBA or SYSOPER privileged users is therefore a good one for those who use shared server connections but there may be other differences that I have not yet discovered.

In summary use SYSDBA or SYSOPER connections only when required such as for RMAN Channels or when using the Data Guard Broker. For all other normal DBA admin work connect as users with the DBA role or some other role appropriate to the requirement.


Posted in Oracle | 6 Comments »

Are you Planning to Sit the RAC OCE Exam

Posted by Joel Goodman on 06/03/2009

If you are a RAC DBA and wish to gain accreditation for your expertise then the RAC Certified Expert Exam may be of interest to you. Some of my students in the DBA Classroom have sat this exam and gained the accreditation. If you are interested you may find this article co-authored with my friend and colleague for Oracle University Harald van Breederode useful.

>> Become an Oracle Certified Expert in RAC

Posted in Oracle | 2 Comments »

Preparing for Oracle DBA Certification Tests

Posted by Joel Goodman on 03/03/2009

I am often asked for advice on preparing for and sitting the DBA Certification Exams by students at Oracle University Courses and Seminars and by delegates at conferences and Special Interest Group meetings.  Many DBAs who attend these events may know that one of my roles in Oracle University is that of Technical Team Leader for the DBA Certification Test development team and Chief Architect for the DBA Oracle Certified Master exam. I have been doing this work for several years now and have made these recommendations to others based on my experience with the development team and on discussions with successful candidates for any or all of the DBA exams.

Before examining preparation strategies, it is always useful to know for what one is preparing. The DBA exams fall into two categories:

1. Theory Style Exams

The Oracle Certified Associate (OCA), Oracle Certified Professional (OCP) and the various Oracle Certified Expert (OCE) exams are all short answer style exams consisting mostly of multiple choice and some simulation style items.

2. Performance Style Exams

The Oracle Certified Master two day exams are Practical exams where candidates must perform various tasks in various skill sets on real systems which are then scored.

The exams all test candidates skill and knowledge in different ways which broadly speaking may classified in two categories

1. Learner Level

Learner Style exam items are generally based on “What is done” or “How is something done”. These items are restricted to skills and knowledge learned at Oracle University Courses or from related Oracle Documentation.  OCA Exams will contain a greater percentage of these items than will an OCP or OCE Exam. I like to refer to these as “Knowledge” style items.

Examples of  the types of content within  “Learner Level” style items are:

1. Knowledge of  which tool or package or view to use in a specific situation.

2. Knowledge of  which memory structure or background process is used in a specific situation.

3. In which order are certain actions performed

4. What effects are caused by doing or by not doing certain things such as setting parameters, invoking packages, using some Enterprise Manager component etc..

5. For OCM exams, “Learner Level” skills are tested by asking candidates to perform tasks without saying how. But it will be  clear what is to be done IE Create a Tablespace, set a parameter, create a certain partitioned table with certain attributes etc..


2. Practitioner Level

Practitioner Style items test whether candidates are able to use what they know to satisfy requirements. So part of what is being assessed by the test is the ability to convert the stated requirements into the correct technical solution. OCP and OCE exams have a larger percentage of these than do OCA exams. I like to refer to these as “Meta-Knowledge” style items.

Examples of  the types of content within  “Practitioner Level” style items are:

1. What is the best way to do something

2. Why is it important to do or not to do certain things

3. Diagnosing a problem based on a display or exhibit

4. Conversion of a stated set of requirements into specific recommendations

5. For OCM exams, “Practitioner Level” skills are tested by asking candidates to convert stated business requirements into actions and then to implement those actions thereby proving that the candidate both understands the issue and knows which tool to use to solve the problem. In some cases candidates are also asked to implement a strategy to meet objectives. So there is no bluffers guide to OCM. You must know not only the “what” and the “how” but the “when” the “why” and even the “if” of Oracle DBA technology and show that you can apply your skills to these situations.

Now that it is clearer what is required for these exams we can discuss how best to prepare for them. Below is a list of different ways to prepare but there is no “one size fits all” strategy. People differ in their favourite approach to learning or revision but the common theme for all these methods is to “play with the system” and have hands on experience. None of these methods is better; use what works best for you.

1. Preparing for OCA,OCP,OCE Theory Style Exams

1.1 Oracle University ILT courses

1.2 Self Study CDs where available for a specific skill set

1.3 Oracle University Self Study eStudies

1.4 Mentoring provided by a Subject Matter Expert at work

1.5 Oracle Technology Network Articles and Oracle by Example (OBE) downloads.

1.6 Certification prep books from a variety of  sources

1.7 Practice Exams from “Self Test” or “Transcender” or other vendors

1.8 Play with the system. To use it is to know it.

2. Preparing for the DBA OCM Exam

All the preparation methods for “Theory Style” exams apply to OCM but the most important preparation method in my experience requires the following:

2.1 Perform a Gap Analysis between your skills and the exam requirements listed here.

2.2 Make time available to practice those skills that are rarely used or with which you are unfamiliar. This reuires that the gap analysis categorise your current skillset according to your degree of familiarity and use.

2.3 If necessary create a “sandbox “system at work or at home to play around with the technology. For RAC you may not have a cluster with which to play, so perhaps you will take this opportunity to set up a virtual cluster.

2.4 Whilst practicing your skills set time limits on yourself to learn how to work under time pressure. The OCM exam skill sets are all done under time constraints even though in a live system you might choose to work more slowly and for longer to do the same thing that someone else does more quickly. If you can do something correctly 50% faster than you did before then it demonstrates that you know that skill better that you did before.

I hope that this has both helped and encouraged those Oracle DBAs who are thinking about or planning to get certified and that you are successful in your plans.

Posted in Oracle | 9 Comments »