Make Your Reservation for that Port

Not a port of call, not port wine, but computer ports!

A colleague of mine tracked this down on the microsoft site.

To ensure that your Oracle Listener or SQL Server database starts correctly, you want to avoid other software starting up and grabbing the port needed by Oracle or SQL Server. In my shop, we’ve seen backup software, among others, grab a port and then prevent SQL Server from starting.

Here’s the link on how to do this:
http://support.microsoft.com/kb/812873/en-us

It involves making a registry edit to the key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters and then adding a new ReservedPorts. The port numbers added here will only be used if specifically requested by software.

This will not prevent conflicts if you configure two separate programs to request the same port. But it will save you from Windows automatically assigning the port number in question to software that did not specifically request a port.

OEM: Obscure Tools for Tough Problems

Two aspects of OEM (Oracle Enterprise Manager) that I’m getting more acquainted with…

Scripting
Even though OEM automates tasks and provides a GUI interface to make tasks easier, you still need scripting. You can use the OEM gui to fire off a SQL statement against 50 different databases. But what if you need to change the value OEM stores for the password for the database user with which OEM monitors all those instances? What if you want to change the monitoring threshold on a tablespace in 20 databases?

You need OEM’s command line scripting interface to pull off tasks like those. It’s called, gasp, emcli. You can get it configured quite quickly on your OEM management server. It can also be run from remote laptops, but that seems like a security risk to me. Interestingly, the emcli in OEM version 10.2.0.5.0 seems to have a wider variety of features than does emcli in 10.2.0.4.0 OEM. An example is a bunch of switches in the change password command that make it more secure.

Enterprise Manager Command Line Interface manual provides all the detailed documentation needed.

Diagnosing Problems: EM Diag Kit
Keeping OEM happy involves endless trips to Metalink to identify issues with various Oracle versions and configurations. Lately I’ve been trying to solve why the OEM agent stops and restarts itself on some hosts. My efforts with the folks at Metalink led today to a utility called the EMDiag Kit. This little dynamo scans the OEM repository to help the support folks figure out what’s going on with various OEM components. If you think a little EM diagnosis is needed on your OEM system, hop on to Metalink and check out article EMDiagkit Download and Master Index, Doc ID 421053.1.

The Elusive Oracle ILM Assistant

A few years back, I began seeing Oracle web sites trumpeting the arrival of Oracle Information Lifecycle Management or ILM. ILM was a buzz word in computer trade magazines. The concept seemed to boil down to: you have tons and tons of data, here are some ways to classify and manage it all appropriately, and get rid of older stuff or move it out of the way so your system isn’t bogged down any longer.

Back in the days of Oracle 9i, Oracle ILM appeared to use existing features to enable the Oracle database to put older data on cheaper hard disks, even if the old data and the current data resided in the same table. That was accomplished by using partitions within tables. The web sites also mentioned something about an ILM Assistant, but I never spent enough time to be able to track it down.

Now Oracle 11g database is current and at last I’ve come face to face with the ILM Assistant. It’s still pretty hard to find.

Read a quick overview of ILM here. And this download web page is where you can get hold of the install sql scripts. Here’s a user guide. And Chapter 5 of the VLDB Guide has information as well.

How do you do the install?
1. You need to have Oracle APEX working on your target instance.
2. If you want to run the ILM Assistant with the demonstration data, you need to install the Example schema SH. The example schemas come on a separate download from the Oracle Database installer.
3. Then obtain the ILM Assistant installation zip files mentioned above. You simply follow the steps to run sqlplus to execute sql scripts. Same for the demo data if you want that.
4. Once that is in place, you can access ILM Assistant from a web browser.

I don’t have enough time to figure out just how much value ILM assistant adds to the DBA/IT Cost/Man Hour/Quality equation, but the tool does list out partitions and features, and also provides a methodology for creating an information lifecycle. There are dedicated tools from other vendors that just work on ILM. It’s hard to tell whether Oracle ILM offerings amount to much or provide only a thin veneer on partitioning. I may have an opportunity to find out as we grapple with a large database at work.

Backup and Recovery with 11g Database Control

11G Oracle Enterprise Manager Database Control now has an extensive GUI based interface to RMAN. You can view RMAN backups, run RMAN backups, check on status of jobs, schedule RMAN jobs, all within the OEM db control GUI.

But wait, there’s more!

There is also something called Data Recovery Advisor (DRA), which will AUTOMATICALLY detect database failures that require recovery, and will suggest multiple recovery approaches. It can then automatically create the needed RMAN script and then run it for you if you wish! Of course, you need to be in archive log mode and you need to have a viable RMAN backup complete.

This tool (DRA) can also be accessed from the RMAN prompt with commands like LIST FAILURE; ADVISE FAILURE, REPAIR DATABASE;

PLEASE NOTE: OEM 11g Database Control is different than OEM grid control. Highest version OEM grid control is 10.2.0.5. Database control is the standalone version of OEM grid control. The features discussed here are only available in the 11G standalone database control, not the 10g grid control. Fortunately, you can have both OEM 10g grid control monitoring your 11g database PLUS run the 11g database control. At least it worked for me. I’d like to double-check this with Oracle.

How do you get OEM 11g database control working? Use the emca tool.
If you don’t have it working already, these 10g instructions work for 11g:
Recreate Database Control Repository, by Jeff Hunter (thanks Jeff!)
You might hit this problem, which I did in my test db:
Meta link doc id (733710.1) EMCA on 11g Db Fails With: Listener is Not Up

How do you stop and start database control?
emctl start dbconsole
emctl stop dbconsole
emctl status dbconsole

How do you access database control once running?
https://host:port/em

How do you see DRA within OEM 11g Database Control?
Once you are in 11g Database Control, go to the “Availability” link to see all the backup and recovery options.
DRA can be accessed via the Availability : Perform Recovery : Oracle Advised Recovery section
It can also be launched from “Advisor Central”, a link found in the “Related Links” section on the bottom of many pages.

More links on DRA
Tim Hall’s great DRA review
Using DRA from 2 Day DBA guide
Repairing Data Corruptions from DBA guide

Deferrable Foreign Keys, Sequences Gone Astray

When the developers for applications have moved on to other things, troubleshooting issues that arise during production support can get interesting. Here are two recent production issues with resolutions that required adjusting Oracle components and not application code.

Issue One: A developer unfamiliar with the app added a new feature so that a new child table got added to a parent table that had several existing child tables. Interestingly the child table foreign keys linked back to a unique constraint, not a primary key. The unique constraint was on a field WIDGET_NAME. Widget names sometimes change, so the app had a procedure to change this value. It invoked a stored procedure that updated the parent and child tables accordingly.

The new child table got added with a foreign key constraint. All appeared well, but it turned out the “change widget name” feature broke. This had not been regression tested. Turns out the foreign key on the new child table was not created with the clause INITIALLY DEFERRABLE. This foreign key clause cannot be changed after the fact, so it needed to be dropped and recreated. And the stored procedure needed an extra line to update the data in the new child table. After that, all worked fine.

Issue Two: Inserts suddenly stopped working on, you guessed it, the widget table. Turns out the primary key on the widget table on a number field usually populated by a sequence generator in a INSERT trigger. I say usually because the trigger would accept an INSERT with a supplied value as well. Well, somebody supplied a value that ended up colliding with values output by the sequence generator. I ended up running a PL/SQL block to move the sequence to a higher setting. I did it the brute force way, like this:

declare
i number;
curr number;
t number;
begin
        select widget_id_seq.nextval into curr from dual;
        for i in curr..200000 loop
                select asset_id_seq.nextval into t from dual;
        end loop;
end;
/

I see Dan Morgan has a nifty reset sequence routine, but I didn't get a chance to try it out.

Bottom Line: Triggers and non-default clauses in contraints can create situations developers might not expect when they encounter for the first time an existing database that nobody has touched for awhile!

Poor Man’s Real Application Testing

Real Application Testing is a new feature of Oracle 11g. It allows users to capture the workload of a running database and then to playback that workload in a test environment. Great for testing the impact of changes to your database environment.

But Real Application Testing is an extra cost option which may be out of reach for many Oracle customers. I’ve been working on a very rudimentary script that facilitates executing many SELECT statements against a database and then generates the explain plans and captures duration for all of them. This is not nearly the functionality of Real Application Testing, but it’s at least a smattering of it.

It works like this. First you must do a significant amount of manual set up work by capturing SELECT statements used in your database. Make sure each SELECT gets into a single text file. I’ve been doing it this way: Use SQL Developer to capture top SQL in a database and save it to XLS format. Then use an Excel macro to write each SELECT statement to a separate file. I would like to automate this process but haven’t had the time.

Once each SELECT statement is in a separate file, my two scripts go to work. The scripts assume that all SELECT statements can be run by the same database user. Script number 1 will iterate over each SQL file and create a companion file that produces the explain plan for each SQL statement in a separate file. It also will create a master script that executes the original SQL file and also the newly created companion explain plan file.

At this point, one can run the master file against your target database. This will generate many log files.

The final step is to run script number 2, which parses all the log file output. it extracts the name of each query, the explain plan cost for the query, the duration of the query and the records returned by the query. It places that data into tabular CSV format.

With the above scripts, one can easily run a set of SELECT SQL statements against a database and quickly measure explain plan cost, query duration and records returned.

For example, I can compare the output files from the final step listed above to compare the duration, cost and rows returned from many queries executed on two different systems. This allows me to see if a change in configuration results in a different explain plan or duration. This helps when you are trying to answer questions like “Does the query that runs 500,000 times a day run in 1 second now run in 2 seconds?”

Please see my Download page for the two shell scripts and a sample Excel macro.

Unexplained Mysteries

A big part of what DBAs do is troubleshoot. And a good DBA has a tenacious attitude toward troubleshooting. Also, he or she relentlessly applies cold hard logic to a problem to arrive at a solution. Hoping and wishing for a solution doesn’t cut it.

So when do you choose to let go of a problem, leaving it unsolved? I recently let go of a problem for which I know there is an explanation, but I don’t know just what.

I installed Oracle database 11.1.0.6.0 on a Solaris 10 sparc machine. Worked fine. I applied patch 11.1.0.7.0. The OUI gui said that worked fine. I did not check the inventory after the install. Then I applied several recommended patches for 11g that get installed with the OPatch tool. And OPatch came back and said the patches were not needed! How can this be, I thought. I opened a ticket with Oracle. Then I looked at the inventory and saw that the 11.1.0.7.0 installation did not show up in the inventory. However, 11.1.0.7.0 was definitely installed. The banners for sql*plus, exp, expdp and v$version all said so, as did dba_registry.

Oracle support felt that something had overwritten the comps.xml file in the oraInventory/ContentsXML directory. It didn’t look that way to me. There were some suspicous entries in the 11.1.0.7.0 oui log file, but nothing conclusive. Oracle support suggested that I install into a different home and try again.

So maybe it was a rogue file restore that overwrote comps.xml. Or maybe there is some strange bug in OUI that Oracle hasn’t hit upon yet. But I have filed this one under Unexplained Mysteries. It’s not worth my time to dwell on it further. I’ve got to keep my 11g research going!

Organizing Oracle Enterprise Manager for Monitoring

Oracle Enterprise Manager, aka OEM, aka Grid Control, has lots of parts and lots of screens and lots of log files and lots of features and lots of components and lots of meta-link entries and documentation. Planning, deployment and use of OEM made my head spin for months. I’ve only just now got an almost firm grip on how to organize our OEM deployment to meet the monitoring needs for our DBA team. I’m using OEM 10.2.0.4.0, and have some plans to upgrade to 10.2.0.5.0 over the next few months.

Requirements in my shop were:
1. Allow all DBAs to administer all databases, but be able in the future to limit specific DBAs to specific systems.
2. Allow for two separate on call rotations, and set things up so that On-Call Team A does not get pages at night for databases looked after by On-Call Team B.
3. Distinguish between production and non-production databases so that we can report on them separately and so that an individual DBA can get paged and emailed for a production database at 1AM, but will not get any pages at 1AM for a non-production database.
4. Be able to customize the monitoring threshold settings for individual databases.

I worked with OEM rules, administrators, roles, groups, schedules and metrics and policy settings to meet these requirements.

I created groups classified by business unit, environment and target type. For example: SALES_PROD_LISTENERS, SALES_NONPROD_LISTENERS, HR_PROD_DB, HR_NONPROD_DB, etc. Do this in the targets section.

I created rules also classified by business unit, environment and target type. SALES_PROD_LISTENER_AVAILABILITY, for example. I associated this rule with the matching group. This means that this rule will fire only against specific databases belonging to that group. Go to Preferences – Rules for this.

I created Roles for each target type and assigned all targets of that type to that role. ALL_LISTENERS, for example. If needed in the future I can change this to SALES_LISTENERS and MARKETING_LISTENERS if I need to. Go to Setup – Roles for this.

Each real life person has two accounts, one for production systems and one for non-production systems. These administrator accounts subscribe to the appropriate rules for the systems that belong to their on-call rotation. For example, the production account subscribes to the production rules, e.g. SALES_PROD_LISTENER_AVAILABILITY, but not SALES_NONPROD_LISTENER_AVAILABILITY. They need two accounts because schedules are linked to accounts. In that way, the production account gets pages and emails 24/7 for production systems, and the non-production account only gets notifications during the day for non-production systems, provided the schedules for each account are set up correctly and the accounts are subscribed to the correct rules. Manage Administrators on the Setup page. Each administrator must log in as themselves and then manage Schedules and Rule subscriptions using the links on the Preferences page. Lastly, individual DBAs can customize metric and policy settings for individual systems so that threshold settings are in line for that system. Change metric and policy settings on the target home page for the specific database. Link is at the bottom of the page.

Excel Data Getter – Query Oracle, SQL Server and Access from Excel

People frequently want to put database data into Excel for analysis and manipulation. Here’s an Excel macro file that I wrote which simplifies getting data into Excel. Called Data Getter, it allows you to query Access, Oracle and SQL Server databases. You can save your queries so they can be reused. You can run more than one query at a time and can direct the output to one or more output files. You can save your configuration files so that you can change from config file to another easily. If you know VBA, you can customize the macro further to work with other database products. Get it here, or on the Downloads page here at Database Bulletin.

On my to do list for improvements are:
1. Enabling password protecting for configuration files to make it less dangerous to have passwords sitting in files. Data Getter supports SQL Server trusted connections, so there are no password problems if you are using it solely to connect to SQL Server via trusted connections.
2. Customize it for more databases. Filemaker, Sybase, mySQL, PostGres, DB2 are all on my list.
3. Create a “Scheduled Task” mode or command line mode so that you can configure it to run a set of reports from the command line for use in scripting or with Windows scheduled tasks.

Oracle Internet Directory administration cheatsheet

What tools and files help one administer Oracle Internet Directory (OID)? Here’s a rundown on command line, GUI, OEM and log files offerings. This post applies to OID installed with Oracle Identity Manager 10.1.0.4.0 and 10.1.4.2.0. I haven’t used earlier versions.

opmnctl found in $ORACLE_HOME/opmn/bin will stop and start all services that are part of the Oracle application server (OAS) installation, of which OID is part. I will use $OH rather than $ORACLE_HOME for the rest of this post.

Just want to use OID and don’t want the over head of OAS? Use oidmon to start the monitoring component of oid services and then use oidctl to stop and start specific OID components. Both are found in $OH/bin.

Need to update OID entries from the command line? $OH/bin has several ldap compliant ldap tools: ldapsearch, ldapmodify, ldapadd, ldapdelete, ldapbind, ldapcompare and more.

You can configure OID replication with the OID gui installer. But after installing, you can administer OID replication with remtool, found in $OH/ldap/bin. The remtool utility allows you to configure OID replication done with LDAP and OID replication done with trigger based Advanced Replication.

If you have multiple OID directories, you may want to compare the contents to see whether the entries match, or whether subsets of OID directories match. Located in $OH/ldap/bin, oidcmprec allows one to do these comparisons and even reconcile two different directories by updating a target to match the source!

You can quickly generate LDIF (LDAP data interchange format or lightweight directory interchange format) text files containing the using the ldifwrite utility in $OH/ldap/bin. Such files can be used with any LDAP compliant server, not just Oracle’s.

$OH/ldap/bin has a number of other utilities, some which are shell scripts, some executables. There are password changing utilities, bulk updating utilities and configuration utilities.

For X-windows style administration, try $OH/bin/oidadmin.

Naturally, Oracle Enterprise Manager (OEM) has an interface for OID, but you’ll have to run OID with Oracle Application server to be able to do this. Running OID just with oidctl is not sufficient. When running just with oidctl, you can use OEM to monitor whether OID is up or down, but there are no screens for additional administration.

Lastly, you may wonder where OID specific log files are located. Most can be found in $OH/ldap/log. oidmon, the ldap server, replication components and remtool all output their log files here.