Tips and wisdom from 15 years of DBA experience

Friday, December 11, 2009

DB Links and streams not working

I am probably not the only person who has experienced frustration when trying to set up db links between databases on different hosts in order to use streams. Streams requires that global_names be set to true. If you remember this simple rule, your life will be simpler when trying to diagnose errors like:

ERROR at line 1:
ORA-02085: database link SOURCE.SJM.COM connects to SOURCE

Rule: (From Oracle's CREATE DATABASE LINK Reference): " If you specify only the database name, then Oracle Database implicitly appends the database domain of the local database"

This rule is probably where most of our problems stem from. It is why when you create a db link named "SOURCE", you see "SOURCE.DOMAIN.COM" if you do a select db_link from dba_db_links. You will also notice that "DOMAIN.COM" matches the value when you do: "show parameter db_domain"

Secondly, the next line from the Oracle reference states: "If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects." This explains why the db link that you created MUST be the same as the value returned from "select * from global_name" on the database the db link is connecting to.

Friday, December 4, 2009

How to view Grid Control agent "raw" metrics

Sometimes Alerts will not clear properly in OEM, specifically I have seen this happen with regard to invalid objects. In order to find out what is really going on, it is sometimes helpful to look at the actual data that the agent is presenting to oem for debugging. It is simple to do this, as described in Metalink note 276350.1:

First, check whether Metric Browsing is enabled on the agent...the server name below is the server name that you are monitoring - the server where the agent is running, NOT the oem repository server:


If you get a 403 error from this url, then do the following on that server:

$ vi /sysman/config/

Then find the line that reads:

# To enable the metric browser, uncomment the following line
# This is a reloadable parameter

...and un-comment this line:


Then reload the agent:

AGENT_HOME>/bin/emctl reload agent

You should now be able to access the URL that returned a 403 previously. It will list all of various metrics that the agent has, so you can see where any issues are. In my case, it was very helpful, because I was getting invalid object warnings, but through sqlplus I could query dba_objects and see that there were no invalids in the schema it was complaining about. It turns out that the agent was viewing BIN$... objects from the recycle bin as invalid. I cleared the recycle bin, and viola! I was able to click the "reevaluate alert" button and clear the alert. problem solved.

Wednesday, December 2, 2009

Remove Agent from Grid Control

I had a circumstance where the hostname changed on a box, and although there was only one agent on the box, grid control was confused about the original agent, and it's stats was "Agent Unreachable". Oddly, it showed that the last upload time was the same as the reachable agent. The solution is quite simple: perform the following steps as indicated in metalink doc 735062.1:

"After any change on the host configuration, apply the following steps.

In Grid Control navigate to:

Deployment Tab
-> Refresh Host Configuration link
-> Select the host in the "Available Host" menu
-> Click on "Refresh Hosts" button.

Make sure that the submitted job finishes correctly and check the host home page to confirm that the changes have been propagated to the repository"

It worked perfectly for me on GC The old agent is gone from GC now. Yeah!