Busy getting ready for Oracle Open World 2008!

Well, good news-

My Oracle Mix presentation idea has received enough votes to be among the top new sessions to be at Oracle Open World 2008 this year. I am excited and hard at work on the workshop presentation for Oracle 11g tips and database internals. More details on my session can be found here.

I look forward to meeting all the top Oracle gurus at Open World this year- it should promise great fun and wonderful chance to meet new friends and business contacts.

Also, based on the Oracle Open World 2008 conference, besides the amazing array of technical sessions, the entertainment proves to be top notch with Seal and UB40 to perform during the conference week.


I am also at work on my second new book, Oracle 11g Insider Secrets, which will be completed in 2009. My first Oracle book should also be available from Rampant Press next year as well on database migrations to Oracle. Once I have some time, I will continue to discuss more 11g new features and other tidbits on Oracle technology.

Stay tuned for more to come!
-Ben

Oracle 11g Monitoring and Goodbye Alert Log!

Oracle 11g has introduced many new features for database monitoring and administration. This month, we will examine the new interface for monitoring database events within the Oracle 11g database release. In previous releases of Oracle database, there was the alert.log text based file that contains all database activity of use to the vigilant DBA. The alert.log file contains error messages and major database activities and operations which are monitored by the DBA as part of daily maintenance. However, this alert.log file disappears with Oracle 11g and is now replaced by an XML file called the log.xml file.

Oracle 11g also introduces a new command line utility called the ADR Command Interface or ADRCI to review the contents of the log.xml file. By default, the ADR tool is located under the Oracle 11g directory ORACLE_BASE/diag/rdbms/SID/SID/alert
. In order to use the ADR tool, the command adrci is issued with Oracle 11g.


Let's explore an example on how to use the new ADR command tool to view our new Oracle 11g diagnostic log contents.

The default location where the log.xml file is located on the test Oracle 11g Red Hat Linux server in this example:

/u01/app/oracle/diag/rdbms/ora11g/ORA11G/alert


Start a new session of ADR command tool by issue command adrci:







Now let's check out some of the cool new features of the ADR command tool. To display options with the help command from within ADRCI:




For extended help options we can use the help extended command from within the ADR command tool and shown below:




We can get detailed explanations of an option such as the BEGIN BACKUP command which will present a series of screens much like that found in the old UNIX man pages.





What you should finally realize at this point is that Oracle 11g has taken monitoring to a whole new level over 10g release. In fact, think of it as monitoring on steroids. We will next view an example of how to look at the log files for database monitoring with Oracle 11g on Red Hat Linux.




Lets scroll down and see more options for the show alert option with ADRCI:





As you can see, there are now options to review the old style alert.log files as well as the ability to view the log files from the Oracle listener and host. We can also parse the log file to see just a recent activity which is useful for quick monitoring tasks. Lets take a peek at one of the recent log files in the next example.

We choose option 1 and the alert log file is displayed below:




By default the log files are stored under the Linux /tmp directory structure.





To exit and return to the main ADR command screen we use the :q command within the Linux vi editor.


The ADR command tool also lets us view the new health monitor reports and functions.

For instance, if I wish to look at the most recent health monitor execution tasks then I can simply issue the show hm_run command within the ADR command interface.







Also of note with Oracle 11g are several new V$ tables that correspond to the ADR and new health monitoring features:

V$HM_CHECK
V$HM_CHECK_PARAM
V$HM_FINDING
V$HM_INFO
V$HM_RECOMMENDATION
V$HM_RUN


Complete syntax and further details on the ADR command tool is available online in the Oracle 11g Documentation located here

We have barely scratched the surface in how to use this potent new monitoring feature for Oracle 11g. Explore the power of the tool as it promised much needed functionality and useful instrumentation for busy Oracle DBAs in monitoring of large and complex Oracle 11g environments.

Regards,
Ben

Block Size and Oracle Database Performance

Recently, on the Oracle OTN Forums, there has been a major debate over the
Oracle block size and database performance. Some top Oracle experts including Jonathan Lewis and Steve Karam have joined the debate. More details can be found here and also here on the issue of whether or not block size for Oracle determines whether or not performance can be improved or not.

In a nutshell, some of the discussion touched on potential bugs in Oracle database code with respect to the use of ASSM and block sizes.

I may or may not create some actual benchmark tests to see how my results differ from the financial services customer I worked a few years ago to resolve the poor performance in the past. I saw major improvement by changing the block size from 8k to 16k. Due to NDA and non-disclosure laws, I was unable to post the actual statistics. Also, one item from the above block size and Oracle performance debate that has been interesting to note is we have two groups of thought at odds with each other: one such as Jonathan Lewis and Tom Kyte advocate extensive test cases and posting results to argue a point on Oracle and the second group represented by Steve Karam and Don Burleson advocate real world cases to prove how Oracle database really works in the real world.

It really depends on how much time I have.

Personally, both camps of thought have some interesting points to consider. I argue this because testing is important and actual results from real world production systems are critical as well. However actual benchmark testing from at the bare minimum a subset of a live production system appears to be more accurate than a simple test case.

My Favorite New Feature in Oracle 11g- Backup and Recovery

Oracle 11g has tons of cool new features that developers and DBAs should be excited about since the first release last summer. For me, as a DBA and consultant, I like the new Data Recovery advisors for Oracle 11g Backup and Recovery with RMAN.

Oracle ACE Arup Nanda has an excellent guide that covers all the details.

The two main features that I particular like about the new Data Recovery Advisor feature new to Oracle 11g release are the automatic repair scripts and the validate database command in RMAN. First lets take a look at an example on how these function in Oracle 11g Release 1(11.1).

Let's say that I have problems with my USERS tablespace and the datafile associated with this tablespace is corrupted or a disk media failure loses the data.

Assuming that I have been a prudent and more likely wise paranoid Oracle DBA, I have good backups tested and available to do the database restore and recovery operation.

Instead of using the normal RMAN commands that I, as the seasoned DBA know by heart to perform the database recovery operation for the lost datafile for the USER tablespace while half asleep at lets say 2am on a Saturday night when the operator pages me, I can make use of the Data Recovery Advisor in RMAN to help guide me to a quick and accurate recovery for my missing or corrupted data file.

First I simulate a data loss by deleting the data file from the operating system.

When I shutdown and startup my test Oracle 11g database, Oracle complains about the missing data files.




And we see the infamous words:



ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/oradata/ora11g/users01.dbf'



OK so how can we quickly diagnose the solution for our database recovery operation with Oracle 11g? Well thanks to the new Data Recovery Advisor, it is a piece of cake if we have a good backup.

First, lets start a new RMAN session and connect to our target or catalog if we are using an RMAN catalog. For sake of simple example, we just use target and no catalog for the test.

rman target /

Once we connect to the target database that requires database recovery, we use the new list failure and list failure detail commands with RMAN to find out the root cause of the database failure.





OK, so now I know what the problem is. Now let's try out the cool new feature of the Oracle 11g Data Recovery Advisor to help us with a repair script and recovery strategy for our lost data files.

We use the advise failure command new to Oracle 11g for RMAN to give us the details on how to recover from the data loss. Below is an example for Oracle 11g on Red Hat Linux.





Notice that the Data Recovery advisor informs us that the data files in question are missing or have been lost as well as recommended actions. As an experienced DBA, we should know these steps fairly well and can verify the steps with the advisor. One nice touch before we implicitly believe the Data Recovery Advisor is to run the repair failure preview command to test out a simulation using the automated repair scripts provided to us by the Data Recovery Advisor.


Let's check out what the Data Recovery Advisor tells us when we run the repair failure preview command from within RMAN for our test Oracle 11g database.




Notice that the Data Recovery Advisor provides us with both the list of steps to perform the database recovery option as well as the impact to the database for the recovery operation as well as location of the repair scripts. One nice feature of the Data Recovery Advisor is that these tasks can be scripted in the Unix shell scripting language of your choice and then scheduled to run via a daily cron job for detection and repair of database failures.

Now that we are satisfied with the output for the recovery scripts from the Data Recovery Advisor, let's go ahead and use the repair scripts above to complete our database restore and recovery.

The repair failure command will use the default repair scripts created by the Data Recovery Advisor to execute these tasks as shown in the following example.




First, the Data Recovery Advisor for Oracle 11g in RMAN shows us the tasks to be executed and then prompts us whether or not we really really want to do this. Since we like the choices given, we choose yes and the advisor quickly carries out the database recovery operation for us right away.




Once the restore and recovery operation has completed successfully, Oracle 11g RMAN asks us if we would like to open the database. Since we were unable to open the database last time due to the missing or lost data files, we choose yes to test whether the restore has been successful.

Since it takes a while to open my test Oracle 11g database, I get a cup of coffee and come back.




Now that RMAN has completed the database recovery, lets test to see if we can access our missing data from our USERS and EXAMPLE tablespaces that were lost earlier.

But first, I would like to illustrate the other way cool feature of Oracle 11g for Backup and Recovery with RMAN: validate database

In the past when an Oracle DBA has to check for corrupt blocks and repair corrupt blocks in earlier releases before 11g, the dbverify utility was the tool of choice along with the block recover feature in Oracle RMAN.
Not only was this time consuming tracking down which corrupt blocks belong to which data file and associated tablespaces, but it was a pain in the rear to deal with.

Well now with 11g for Oracle RMAN, we have the validate command. It is fantastic!

Let's view a few examples.

To check the entire database for block corruption- we have the validate database command.

RMAN will cycle through the entire database as shown in the example below:





My advice for Oracle DBAs out there is to run a validate command after each database recovery operation to verify that no block corruption exists. It also helps to do this on a regular basis just as part of good preventive maintenance and sanity checks.

Now lets run the validate tablespace command to check for block corruption with the recently restored EXAMPLE and USERS tablespaces:



And for our EXAMPLE tablespace:




We also have the validate datafile command to check for block corruption with a specific data file. For example, to check for corruption with data file 1 which happens to belong to the SYSTEM tablespace:





I like this a lot better than the old dbverify utility which frankly to be honest was a royal pain to deal with.

Now that we have covered the new features for Oracle 11g RMAN with backup and recovery for the new Data Recovery Advisor, lets verify that our data has been restored by logging on to our database and checking to see that our data has been restored for these tables in the USERS and EXAMPLE tablespaces.




Now that our data has been recovered we are satisfied with the power and ease that the new Oracle 11g Data Recovery Advisor affords us busy Oracle DBAs.

Cheers,
Ben

How to Secure Oracle 10g/11g Enterprise Manager for SSL

Recently a poster on the Oracle Forums (forums.oracle.com) asked a question on how to secure Oracle 10g Database Control for Enterprise Manager (OEM) with SSL. I had to do this for some military customers when we migrated to secured networks for Oracle 10g.


According to the Oracle documentation and various tips from Oracle Metalink, Oracle uses wallets to provide the ability to secure OEM via SSL layer.

The emctl secure commands provide the tools to setup the wallet based security with SSL to lock down the Oracle 10g Database or Grid Control environment.

Reference
Oracle® Enterprise Manager Advanced Configuration
10g Release 2 (10.2)

The emctl secure agent utility performs the following actions:

* Obtains an Oracle Wallet from the Management Service that contains a unique
digital certificate for the Management Agent. This certificate is required in order for
the Management Agent to conduct SSL communication with the secure
Management Service.

* Obtains an Agent Key for the Management Agent that is registered with the
Management Service.

* Configures the Management Agent so it is available on your network over HTTPS
and so it uses the Management Service HTTPS upload URL for all its
communication with the Management Service.

To enable Enterprise Manager Framework Security for the Management Agent:

1. Ensure that your Management Service and the Management Repository are up
and running.
2. Change directory to the following directory:


AGENT_HOME/bin (UNIX)
AGENT_HOME\bin (Windows)


3. Stop the Management Agent:


PROMPT> ./emctl stop agent


4. Enter the following command:


PROMPT> ./emctl secure agent (UNIX)
PROMPT> emctl secure agent (Windows)


The emctl secure agent utility prompts you for the Agent Registration Password, authenticates the password against the Management Service, and reconfigures the Management Agent to use Enterprise Manager Framework Security.

Note:
Alternatively, you can enter the command all on one line, but if you enter the
command on one line, the password you enter will be displayed on the screen as
you type:


PROMPT> ./emctl secure agent agent_registration_pwd (UNIX)
PROMPT> emctl secure agent agent_registration_pwd (Windows)


Here is sample output for the various options for emctl secure command:


C:\>emctl secure
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0

Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

Usage :
secure oms -sysman_pwd -reg_pwd [-hos
t ] [-reset] [-secure_port ]
secure agent
secure em
secure dbconsole []
secure setpwd
secure status [oms url]
secure lock | unlock

In this case, I have Oracle 10g on Windows XP with Database Control for EM.
Here is an example:

C:\>emctl secure dbconsole oracle oracle karma

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.

http://karma:5500/em/console/aboutApplication

Checking Repository... Done.
Checking Repository for an existing Enterprise Manager Root Key... Done.
Generating Enterprise Manager Root Key (this takes a minute)... Done.
Fetching Root Certificate from the Repository... Done.
Generating Registration Password Verifier in the Repository... Done.
Updating HTTPS port in emoms.properties file... Done.
Generating Java Keystore...Certificate was added to keystore
Certificate reply was installed in keystore
Done.
Securing OMS ... Done.
Generating Oracle Wallet Password for Agent.... Done.
Generating wallet for Agent ... Done.
Copying the wallet for agent use... Done.
Storing agent key in repository... Done.
Storing agent key for agent ... Done.
Configuring Agent...
Configuring Agent for HTTPS... Done.
EMD_URL set in C:\oracle\product\10.2.0\db_1/karma_orcl/sysman/config/emd.prop
ties
Configuring Agent ... Done.
Configuring Key store.. Done.


Virtualization with Oracle 11gR1 and Oracle VM

I am excited about the Oracle focus on virtualization for database servers.

As a consultant, I like using virtual servers to use for test servers on my laptop which comes in handy for demos and testing.

Here are details from the Oracle Virtual Site:



Installing and Configuring Oracle Enterprise Linux 5 with Oracle Database 11g Release 1 as a Paravirtualized Machine (PVM) on an Oracle VM Server

I encourage fellow Oracle professionals to explore the future with the Oracle virtual server technology!

Support for third party database migrations to Oracle Using SQL Developer

As you all may or may not be aware of, in the past year or so, I have been busy writing a book on database migrations to Oracle 10g/11g using the freely available migration tools from Oracle. Originally these tools were in the form of what is called the Oracle Migration Workbench or OMWB for short. In 2006, Oracle released a development environment called SQL Developer.

Oracle has added migration tools to their flagship development product called SQL Developer which used to be called Raptor in the first beta version that will be the focus for migration and development tasks.

However, there are a few gotchas with SQL Developer tool in terms of third party migrations. Oracle as of yet after discussions with product management at Oracle on the SQL Developer team has no support in SQL Developer yet for IBM DB2 UDB and Informix migrations to Oracle using SQL Developer. SO, in spite of what Oracle may say, you still need to use the Oracle Migration Workbench if you wish to migrate off of IBM DB2 UDB or Informix to Oracle. After a brief chat with Sue Harper whom is the product manager at Oracle for SQL Developer, she was kind enough to let me know that support is coming eventually within SQL Developer in a future release to support Informix and IBM DB2 migrations to Oracle.