Unexplained Mysteries

Leave a comment

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

Leave a comment

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

Leave a comment

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

Leave a comment

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.