Review: uCertify PrepKit 1Z0-047

I was asked by uCertify to review their preparation kit product.

I am currently preparing myself for Oracle 1Z0-047 Oracle Database: SQL Certified Expert Exam and I thought this could be a good chance for me test my current knowledge and chose to review their preparation kit for exactly this exam. (Link)

To be honest, this is my first GUI based prep kit I’ve ever tried. So far I was using the Oracle official documentation as my primary source, and some of the books (Exam Study Guides) that were available.

My study strategy is read the official documentation and practice, practice, practice.

From my experience, the toughest part during preparation for an exam is when it comes to answer the question: “Am I ready enough?”

Usually I go through the questions available in some of the preparation books plus making even more questions based on different scenarios. I also use OTN forums to find discussions with challenging topics where I can test my knowledge.

With the last in mind, the uCertify Prep Kit is just another handy resource that one can use to test his/her knowledge before he/she actually takes the exam.

It is a solid product that provides more than three hundred questions combined into one diagnostic, one final, four practice tests and a quiz. The user also has an ability to create custom tests using those questions he/she finds interesting.

There are also number of questions that are pretty challenging. For all questions, the users have an option to read explanations about the answers including references to the Oracle documentation or other resources.

For each question, there is an option to start a discussion with other users, to send feedback to uCertify and to write personal notes and tags.

The feedback and discussion features are really nice. I found some answers that I did not agree with and used this feature to provide feedback to uCertify. I tested the discussion feature as well and it works nice.

The exam objectives are well covered. There are also questions specific to 11g version of the database.

The software has an update option so the user can download the latest updates and fixes.

The Graphic User Interface is very nice and the navigation is great. Each of the test results can be saved and used for later reference. Also there are features like Flash cards, a Quiz, Study notes and Articles. There are number of notes and articles available.

Finally there is a readiness report that can help the user to find the answer to the question: “Am I ready for the real thing?”

In general, this is a nice product that can be very helpful for one to prepare for an exam (they have various prep kits available (Link)). However, I don’t want anybody to get an impression that I think this is the only resource that one could use to prepare and pass an exam. Whenever I was asked for an advice on what resources should (must) one use to prepare for an exam, my answer was always straight, The Official Documentation, period. (The official trainings are also very valuable, but sometimes they are a must-do regardless of someone’s recommendations :-))

This product and all the other available out there (books, prep kits, study guides) are just another resource for learning and practicing that one may consider to use when preparing for an exam.

Save some disk space - One Reminder and a Cool Windows Command

CPU Reminder

It’s the time of the year when the January CPU is released. I’d like to remind you to think about cleaning up your obsolete backups from $ORACLE_HOME/.patch_storage directory (the ones that are not needed anymore for rollback purposes).

Starting from 10g R2 Oracle backs up the affected libraries along with the rest of the affected files. Some of the libraries can be pretty big and thus after several CPUs the amount of disk space consumed by can be significant.

In order to prevent from unnecessary wasting the disk space, you could use Opatch utility using util cleanup option to remove those backups that are not needed anymore.

Below is the syntax and the options available when using Opatch tool to clean up old backups:

SYNTAX
opatch util cleanup  [-invPtrLoc  ]
                     [-jre  ] [-oh  ]
                     [-silent] [-report]
                     [-ps , this will
                       be located under ORACLE_HOME/.patch_storage/]

OPTIONS
       -invPtrLoc
              Used to locate the oraInst.loc file. Needed when the
              installation used the -invPtrLoc flag. This should be
              the path to the oraInst.loc file.

       -jre
              This option tells OPatch to use JRE (java) from the
              specified location instead of the default location
              under Oracle Home. Both -jdk and -jre options cannot
              be specified together. OPatch will display error in
              that case.

       -oh
              The oracle home to work on. This takes precedence over
              the environment variable ORACLE_HOME.

       -ps
              This option is used to specify the Patch ID with timestamp.
              This Patch ID with timestamp should be the same as in
              .patch_storage directory.

              A directory by this name will be present under
              ORACLE_HOME/.patch_storage. If this directory is specified
              and is valid, then the contents specified in the description
              will be cleaned up only for this patch. Otherwise, all patch
              related directories will be acted upon by this utility.

      -silent
              In silent mode, the cleanup always takes place.

      -report
              Prints the operations without actually executing them.

Make sure you specify the patch id (ps parameter) of the patch you want to remove.

Cool Windows Command

Few days ago I learned about forfiles command that can be used to select a set of files using some criteria (like date modified) and run command against each of them (like delete). (Something like find –exec in Unix)

I found this very useful for cleaning up the trace and log files that are not needed anymore.

It is very handy and I am really surprised that Microsoft finally came up with something like this. Before I used to write batch scripts to implement the logic that forfiles provides.

It is available on Windows Vista, Windows 2003 Server and Windows 2008 Server.

You should really take a look if you didn’t know about this one already.
Find more (the syntax and few examples) about forfiles from this TechNet Note .

Example:

Task: Delete all the trace files that are older more than 30 days

c:\app\oracle\diag\rdbms\db11g\db11g\trace>forfiles /s /m *.trc /d -30 /c "cmd /c
echo @FILE @FDATE"

"db11g_ora_13200.trc" 12/18/2008
"db11g_ora_18716.trc" 12/18/2008
"db11g_ora_18768.trc" 12/18/2008
"db11g_ora_18892.trc" 12/18/2008
"db11g_ora_3004.trc" 12/18/2008
"db11g_ora_4428.trc" 12/18/2008
"db11g_ora_6256.trc" 12/18/2008
"db11g_ora_6444.trc" 12/18/2008
"db11g_ora_6480.trc" 12/18/2008
"db11g_ora_6504.trc" 12/18/2008
"db11g_ora_6844.trc" 12/18/2008
"db11g_ora_6912.trc" 12/18/2008
"db11g_ora_6928.trc" 12/18/2008
"db11g_ora_7044.trc" 12/18/2008

c:\app\oracle\diag\rdbms\db11g\db11g\trace>forfiles /s /m *.trc /d -30 /c "cmd /c
del @FILE"


c:\app\oracle\diag\rdbms\db11g\db11g\trace>forfiles /s /m *.trc /d -30 /c "cmd /c
echo @FILE @FDATE"
ERROR: No files found with the specified search criteria.

Pretty handy, isn’t it?

ORA-12514 during switchover using Data Guard Broker

I’ve seen, on several occasions, questions being asked about data guard broker being unable to automatically start the databases during a switchover, failing with ORA-12514 - TNS:listener does not currently know of service requested in connect descriptor?

This is most likely because the special service db_unique_name_DGMGRL has not been registered properly with the listener.

This is one of the requirements when configuring Data Guard broker.

From the Oracle documentation (Reference Oracle® Data Guard Broker 10g Release 2 (10.2)):

To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance. The value for the GLOBAL_DBNAME attribute must be set to a concatenation of db_unique_name_DGMGRL.db_domain. For example, in the LISTENER.ORA file:

LISTENER = (DESCRIPTION =
   (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)
   (PO1RT=port_num))))
SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)
   (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
   (ORACLE_HOME=oracle_home)))

I think the main reason for overlooking this prerequisite is because nothing about this is mentioned in Oracle 10g Data Guard Concepts and Administration Guide, the chapters for standby databases configuration.

So once the initial configuration is set up and works fine, the listener prerequisites for setting up Data Guard Broker are probably overlooked.

Of course, this applies only if Data Guard Broker is configured manually. If one uses OEM Grid Control to set up and configure the Data Guard Configuration, OEM will make the necessary changes.

Another point I want to make here is the importance of practicing various switchover and failover scenarios. It is not only that you will gain more experience and fill more comfortable doing the same thing in real situation, but you also will learn about any hidden configuration wholes that you may have overlooked or missed in your configuration steps.

Here is an example of this problem and its solution:

For this example I used two Oracle 10.2.0.1 databases, dg1db running as a primary database and dg2db running as a physical standby database.

[oracle@dg1 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/***
Connected.
DGMGRL> show configuration

Configuration
  Name:                dg-test
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    dg1db - Primary database
    dg2db - Physical standby database

Current status for "dg-test":
SUCCESS

Now lets try the switchover. As you can see the role transition was done successfully, but the database startup that should happened at the end failed with ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

DGMGRL> switchover to dg2db;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "dg1db" on database "dg1db"
Shutting down instance "dg1db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "dg2db" on database "dg2db"
Shutting down instance "dg2db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dg1db" on database "dg1db"
Starting instance "dg1db"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
You are no longer connected to ORACLE
Please connect again.
Unable to start instance "dg1db"
You must start instance "dg1db" manually
Operation requires startup of instance "dg2db" on database "dg2db"
You must start instance "dg2db" manually
Switchover succeeded, new primary is "dg2db"
DGMGRL>

After starting the databases manually, I checked the status of the Data Guard configuration and it was SUCCESS.

DGMGRL> show configuration

Configuration
Name:                dg-test
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
  dg1db - Physical standby database
  dg2db - Primary database

Current status for "dg-test":
SUCCESS

DGMGRL>

Now I will make the necessary changes to the listeners and try the switchover in the opposite direction. After the change listener.ora should look like (pay attention to GLOBAL_NAME=dg1db_DGMGRL.localdomain).

[oracle@dg1 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/ora10g/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = dg1db_DGMGRL.localdomain)
    (ORACLE_HOME = /u01/app/oracle/product/ora10g)
    (SID_NAME= dg1db)
  )
)

LISTENER =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dg1.localdomain)(PORT = 1521))
)

[oracle@dg1 admin]$ lsnrctl reload
[oracle@dg1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-JAN-2009 17:02:35

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                13-JAN-2009 08:08:17
Uptime                    0 days 8 hr. 54 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/ora10g/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/ora10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1.localdomain)(PORT=1521)))
Services Summary...
Service "dg1db.localdomain" has 1 instance(s).
Instance "dg1db", status READY, has 1 handler(s) for this service...
Service "dg1db_DGB.localdomain" has 1 instance(s).
Instance "dg1db", status READY, has 1 handler(s) for this service...
Service "dg1db_DGMGRL.localdomain" has 1 instance(s).
Instance "dg1db", status UNKNOWN, has 1 handler(s) for this service...
Service "dg1db_XPT.localdomain" has 1 instance(s).
Instance "dg1db", status READY, has 1 handler(s) for this service...
The command completed successfully

I did the same thing with the second listener.
Now the switchover will complete without any problems.
DGMGRL> show configuration

Configuration
Name:                dg-test
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
  dg1db - Physical standby database
  dg2db - Primary database

Current status for "dg-test":
SUCCESS

DGMGRL> switchover to dg1db
Performing switchover NOW, please wait...
Operation requires shutdown of instance "dg2db" on database "dg2db"
Shutting down instance "dg2db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "dg1db" on database "dg1db"
Shutting down instance "dg1db"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "dg2db" on database "dg2db"
Starting instance "dg2db"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "dg1db" on database "dg1db"
Starting instance "dg1db"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "dg1db"
DGMGRL>

DGMGRL> show configuration

Configuration
Name:                dg-test
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
  dg1db - Primary database
  dg2db - Physical standby database

Current status for "dg-test":
SUCCESS

DGMGRL>

Cheers,
Mihajlo Tekic

HAPPY NEW YEAR !!!

HAPPY NEW YEAR TO EVERYONE !!!

I WISH YOU AND YOUR FAMILIES HEALTHY, HAPPY AND SUCCESSFUL NEW YEAR !!!



The Picture above is of my daughter Monika.

She is four months old and is the best thing that happened to me in 2008.