Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 14, 2005

Database Security and Patches - Part 3

By Steve Callan

Part 3 of this series examines some new functionality found in Oracle10g's Enterprise Manager Console. In prior releases of Oracle (as shown in the first two parts of this series), patching and patch administration were manually performed by the DBA. In 10g, some of that workload has transferred to the software. As an analogy, it is similar to the difference between RMAN (server managed recovery) and user managed recovery. The example database used in this article is Oracle Database 10g Release 2.

Some preliminaries

The first and most obvious preliminary would be to get 10g installed if it not already available. The version used for the examples in this article is Release 2 (sorry, it is Windows again, but I am pretty sure Santa is giving me a Sun box this year).

Click for larger image

Available releases for various platforms

The installation used for examples here does not include the contents of the companion CD. The size on disk for the unzipped 10201_database_win32 folder is 666MB, and the space used for installation under ORACLE_BASE is 1.96GB for the Enterprise Edition.

Click for larger image

Details about the Windows version

Once the software is installed, (create a general-purpose database), you will be using Database Control (still Enterprise Manager, just slightly repackaged). Two things to note about accessing Database Control include:

A new port number of 1158 (or thereabouts)

A different URL, which includes "em" in the path

To access Database Control, the URL is http://localhost:1158/em (from Start>Programs>10g home, the link may appear with the computer name in it). If you forget the "em" extension, you will see a welcome to OC4J page (nice, but not very useful in helping you get to Database Control, or whatever name Oracle intends to refer to Enterprise Manager as).

Click for larger image

How do I get to Database Control?

Add a line to the index.html file in ORACLE_HOME\oc4j\j2ee\home\default-web-app like so (you will have to know the port number, found in the portlist.ini file in the install folder under ORACLE_HOME):

<p>Did you mean to go to Database Control/Oracle Enterprise Manager? <a href="http://localhost:1158/em">Click here</a></p>
<p>This version of Oracle Application Server Containers for J2EE 10g (OC4J) is J2EE 1.3 compatible. </p>

The new line appears on the welcome page.

Click for larger image

Revised page with a helpful link

Navigating through Enterprise Manager

The first time through, you will be prompted to acknowledge some licensing requirements, but after that, once you successfully login, the next page that appears has the look and feel of Application Server's version of Enterprise Manager:

The "home" page for Database Control

Scroll down to the bottom of the page, and click on the Maintenance link in the lower left corner.

On the next page, the area of interest for us is the Software Deployments section.

Using the Database Software Patching Feature

Viewing the patch cache should (hopefully and obviously if you just installed 10g) reveal that no patches are cached at this time.

The cache can be loaded with patches via a connection to MetaLink. As pointed out in a previous article, patch access is generally only available with a support contract, which further means you have a license, which further means you are probably only going to be able to use this part of Database Control at work but not at home as an unlicensed user.

There are two ways to add a patch to the cache: click the "Upload Patch File" button, and perform a setup step that connects you to MetaLink. The manual upload page has several required fields, and some have to be changed to reflect your database environment:

Manually adding a patch to the cache

The setup for MetaLink access is reached by clicking the Setup link just below the main frame, and then selecting "Patching Setup."

Setting up automatic retrieval of available patches

Enter your username/password information and set a size for the cache.

Entering MetaLink credentials and a cache size

Take note of the statement in the Patch Cache section where it says, "The oldest patches are automatically removed from the repository as necessary to keep the cache below the specified maximum cache size." That is generally a good thing in that patches are cumulative in nature, meaning you only need to have the most current one on hand to bring an installation up to date. However, it can be a bad thing in that if you need to revert to an older or prior patch level, you may encounter an unrecoverable combination that the patch "rollback" script cannot undo. If that is the case (like the example in Part 2), you will need to keep the older version stored elsewhere.

Once the setup is complete, go back out to the Maintenance page and click Apply Patch.

At the next page, you can search manually using the "Search by Criteria" section (just like on MetaLink), or take advantage of what has already been done for you.

Search by Criteria section of the "Patch: Select Patch" process

Results from Database Control's automatic search

Let's see what the second patch (#4667809) does. Toggle the radio button and click Next. Note the progress flow diagram at the top of each page (yet another similar feature across the Oracle HTTP Server-based utilities).

Common "Look and feel" across Oracle's Web tools

At the "Set Credentials" step, you will need an OS username and password. If doing this on Windows, and all you do to log on is to select your username, you will need to supply a password because Enterprise Manager requires one. If you do not use one, go out to Control Panel and create a password for your account.

Review the summary information (you can see file system location for the patch) and click Next.

Readme information

Being able to schedule when the patch is applied is a nice touch.

Scheduling when to run the job

The Patch Summary is also pretty neat.

Viewing the patch summary

The next page informs you that you can view the job, and to do that, look to the far right of the page to find the button. Clicking the button shows the status of the job.

Viewing the status of the patch job

You will have to refresh the status page to see changes, and if everything goes well, the end result will be a status of "Succeeded."

Applying the patch was successful

Click a link for one or more of the logs to view its details. I selected checkTarget (below) because this log will show you a "password failed" message if the password set previously is incorrect.

A check for user access to the file system was successful

What is the status of the patch?

So far, so good, but what, exactly, has taken place? Is the patch applied, or is it just staged and ready to be applied? Back at the Stage or Apply step, there is a checkbox you can select to apply the patch after the patch has been staged. In this example, I left the checkbox unchecked, so the status on my computer is staged, not applied.

Run Script to Apply Patch was left unchecked

Oracle's OPatch tool can be used to apply the patch (either manually or via Database Control). That is for the next part of this series.

In Closing

Overall, I think Database Control is a huge improvement to the RDBMS line of products. The automatic patch retrieval/selection process brings Oracle up to speed with respect to something Microsoft has had for a long time (telling you what patches are available and even being able to automatically install them for you). Searching for patches on MetaLink can be a frustrating experience (that "patch" versus "patchset" terminology is misleading). The next and last part of patching covered in this series is the OPatch utility.

» See All Articles by Columnist Steve Callan

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM