APEX application to display tablespace usage of databases registered in an Oracle Enterprise Manager installation

 

This is an APEX application (created with APEX 23.2) to present tablespace usage data, retrieved from your OEM 13c. It uses spaceUsed, bytesFree and pctUsed metrics to display graphs about Used (GB) space, Free (GB) space and Used (%).

First of all, you will need to create three views under your APEX workspace schema (tbs_usage_objs.sql). If your APEX installation lies in the same database with your OEM installation, you will need to give to your APEX workspace schema/user the necessary SELECT privileges on the appropriate SYSMAN objects (MGMT$TARGET_TYPE, MGMT$METRIC_DAILY, etc). If your APEX installation is in another database than your OEM installation, then create a database link from your APEX workspace schema/user to the OEM's SYSMAN and make the appropriate changes to the views and procedure's definitions.

Finally, import in your APEX workspace the application (f1002.sql).

Upgrade from OEM 11g to 12c: Step DeployAgentNotRequired ignores agent deployment

We created a new OEM 12c [12.1.0.2.2], using the 2-system upgrade approach from our OEM 11g.

I tried to run the "Deploy and Configure Agent" step on 2 servers A and B and the installation proceeded only to server A. For server B, I was getting a "DeployAgentNotRequired" step with output "Ignoring deployment".

The problem was that there is wrong information in the repository and OEM considered some agents as NFS shared, even though they were not.

Execute the following query as SYSMAN in your OEM 11g repository database and identify which of those agents considered as NFS shared, actually are not.

SELECT TARGET_NAME, MASTER_AGENT_GUID, TARGET_GUID, IS_SHARED
FROM SYSMAN.PRE_UPGC_AGT_STAT_MGMT
WHERE IS_SHARED=1;

Keep the TARGET_NAME of those agents (for our example "B:3872") and set IS_SHARED=0 for them:

UPDATE SYSMAN.PRE_UPGC_AGT_STAT_MGMT
SET IS_SHARED = '0', MASTER_AGENT_GUID=TARGET_GUID
WHERE TARGET_NAME='B:3872';

COMMIT;

Also, check their predeployment status [PRE_DEP_STATUS column] and if the value is STATUS_SUCCESS, change it to STATUS_FAILED:

SELECT TARGET_NAME, PRE_DEP_STATUS
FROM SYSMAN.PRE_UPGC_AGT_STAT_MGMT
WHERE TARGET_NAME = 'B:3872';

UPDATE SYSMAN.PRE_UPGC_AGT_STAT_MGMT
SET PRE_DEP_STATUS='STATUS_FAILED'
WHERE TARGET_NAME = 'B:3872';

COMMIT;

Now, retry the "Deploy and Configure Agent" step for those servers.

Upgrade from OEM 11g to 12c: Step unzipAndDeploySoftware fails

We created a new OEM 12c [12.1.0.2.2], using the 2-system upgrade approach from our OEM 11g.

I tried to run the "Deploy and Configure Agent" step on a Linux 64-bit machine and unzipAndDeploySoftware step failed with error:

Check complete: Failed <<<<
Problem: The 32bit software cannot be installed on a 64 bit platform. Similarly the vice-versa is also true where 64bit product cannot be installed on a 32bit machine.
Recommendation: Please install the software on a 32-bit machine if the product is 32bit, otherwise install the product on the 64bit machine if it is a 64bit software.

This is Bug 15995299.
The problem is the OS has been discovered as a Linux 32-bit by the upgrade process.

SELECT TARGET_NAME, PLATFORM_ID, PLATFORM_NAME, CURRENT_VERSION
FROM SYSMAN.PRE_UPGC_AGT_STAT_MGMT
WHERE TARGET_NAME='your_server:3872';

TARGET_NAME	PLATFORM_ID	PLATFORM_NAME	CURRENT_VERSION
your_server:3872	46	Linux x86	11.1.0.1.0

Also, the targets which are monitored by that agent have wrong information:

SELECT EMD_URL, AGENT_NAME, TARGET_NAME, TARGET_TYPE, PLATFORM_ID, PLATFORM_NAME
FROM SYSMAN.PRE_UPGC_TGT_SW
WHERE AGENT_NAME='your_server:3872';

EMD_URL	AGENT_NAME	TARGET_NAME	TARGET_TYPE	PLATFORM_ID	PLATFORM_NAME
https://your_server:3872/emd/main/	your_server:3872	your_server:3872	oracle_emd	46	Linux x86
https://your_server:3872/emd/main/	your_server:3872	your_server	host	46	Linux x86

You have to update these tables with the correct OS information:

UPDATE SYSMAN.PRE_UPGC_AGT_STAT_MGMT
    SET PLATFORM_ID='226', PLATFORM_NAME='Linux x86-64'
WHERE TARGET_NAME ='your_server:3872';

UPDATE SYSMAN.PRE_UPGC_TGT_SW SW
SET (PLATFORM_ID,PLATFORM_NAME,OS_VERSION ) =
(SELECT DISTINCT AGT.PLATFORM_ID,AGT.PLATFORM_NAME,AGT.OS_VERSION 
FROM SYSMAN.PRE_UPGC_AGT_STAT_MGMT AGT, SYSMAN.PRE_UPGC_TGT_SW TGT
WHERE TGT.TARGET_GUID=AGT.TARGET_GUID AND SW.EMD_URL=TGT.EMD_URL);

COMMIT;

 Rerun the "Deploy and Configure Agent" step for the particular agent.

You may also check Oracle Note 1519366.1: EM 12c: PreUpgrade Console shows Linux 64-bit Host As 32-bit for RHEL / OEL 5 64-bit servers.

Upgrade from OEM 11g to 12c: EM Console Service & EM Jobs Service have status Pending, Database System discovered had no members and status Pending.

We created a new OEM 12c [12.1.0.2.2], using the 2-system upgrade approach from our OEM 11g.
The first agent I installed, in the machine where I installed OMS and repository database had every target discovered with status Up, except the Database System, which had status Pending.
Also, EM Console Service & EM Jobs Service had status Pending.

Strangely, both issues had the same solution.
Although, in our old 11g repository database the parameter job_queue_processes had a value of 1000, some where in the upgrade process, in the new 12c repository database this changed to 0.
Probably, that happened during the database upgrade from 11.2.0.2 to 11.2.0.3 using dbua.

So the solution was:
Run as SYS:

SQL> show parameter job_queue_processes

NAME                TYPE     VALUE
------------------- -------- -----
job_queue_processes integer  0

SQL> ALTER SYSTEM SET job_queue_processes=1000 SCOPE=BOTH;
System altered.

SQL> show parameter  job_queue_processes

NAME                TYPE     VALUE
------------------- -------- -----
job_queue_processes integer  1000

Run as SYSMAN:

SQL> exec  emd_maintenance.remove_em_dbms_jobs;
PL/SQL procedure successfully completed.

SQL> exec emd_maintenance.submit_em_dbms_jobs;
PL/SQL procedure successfully completed.

Upgrade from OEM 11g to 12c: ORA-06532 during repository upgrade

In our 2-system upgrade, during the repository upgrade step, we got an ORA-06532 error:

2013-04-08 13:19:07,497 [Thread-514] ERROR - ERROR:ORA-06532: Subscript outside of limit
ORA-06512: at "SYSMAN.NOTIFICATION_MIGRATION_HELPER", line 576
ORA-06512: at "SYSMAN.NOTIFICATION_MIGRATION_HELPER", line 898
ORA-06512: at "SYSMAN.NOTIFICATION_MIGRATION_HELPER", line 1686
ORA-06512: at "SYSMAN.NOTIFICATION_MIGRATION_HELPER", line 2526
ORA-06512: at line 5
File:/oms/sysman/admin/emdrep/sql/core/11.2.0.1/notification/em_notification_data_upgrade.sql
Statement:DECLARE
l_upgrade_type VARCHAR2(64);
BEGIN
-- Migrate notification rules
NOTIFICATION_MIGRATION_HELPER.MIGRATE_RULES(p_batch_size => 50);

-- Find the upgrade type
l_upgrade_type := em_upgsoln_util.get_upgrade_property(EM_UPGSOLN_UTIL.G_UPGRADE_TYPE);

-- Perform pending violation specific upgrade for 1-system upgrade
IF l_upgrade_type IS NOT NULL AND
l_upgrade_type = EM_UPGSOLN_UTIL.G_UPG_ONE_SYSTEM
THEN
-- Migrate pending violations from aq$mgmt_notify_qtable
NOTIFICATION_MIGRATION_HELPER.MIGRATE_DELIVERY_BUS_DATA(p_batch_size => 25);

-- Migrate pending violations from mgmt_notify_requeue table
NOTIFICATION_MIGRATION_HELPER.MIGRATE_REQUEUE_DATA(p_batch_size => 25);

-- Migrate pending rule engine entries from aq$mgmt_notify_input_qtable
NOTIFICATION_MIGRATION_HELPER.MIGRATE_RULE_BUS_DATA(p_batch_size => 25);
END IF;
END;

Bug 15904251 : UPGRADE OF 11G NOTIFICATION RULES FAIL FOR RULE WITH LARGE NUMEBRS OF CONDITIONS was relevant, but applying it, did not solve the issue.
post_config.sh failed, with error: "No OMS is found to be associated with the current Oracle Home"

To continue, first, run these commands as SYSMAN to the new repository database:

ALTER TYPE EM_RULE_EXPRESSION_ARRAY MODIFY LIMIT 100000 CASCADE;
ALTER TYPE EM_RULE_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_SET_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_COND_ACTIONS_OBJ compile;
ALTER TYPE EM_RULE_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_COND_ACTIONS_OBJ compile;
ALTER TYPE EM_RULE_SET_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_COND_ACTIONS_OBJ compile;
ALTER TYPE EM_RULE_COND_ACTIONS_OBJ compile;
ALTER TYPE EM_RULE_SET_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_SET_DETAILS_OBJ compile;
ALTER TYPE EM_RULE_COND_ACTIONS_OBJ compile body;
ALTER TYPE EM_RULE_DETAILS_OBJ compile body;
ALTER TYPE EM_RULE_SET_DETAILS_OBJ compile body;
ALTER PACKAGE EM_RULE_BAM_INTG compile;
ALTER PACKAGE EM_INCIDENT_RULES compile;
ALTER PACKAGE MGMT_RCA compile;
ALTER PACKAGE NOTIFICATION_MIGRATION_HELPER compile;
ALTER PACKAGE EM_RULE_BAM_INTG compile body;
ALTER PACKAGE EM_INCIDENT_RULES compile body;
ALTER PACKAGE MGMT_RCA compile body;
ALTER PACKAGE NOTIFICATION_MIGRATION_HELPER compile body;

After that, run:

$OMS_HOME/oui/bin/runConfig.sh ORACLE_HOME=$OMS_HOME ACTION=configure MODE=perform

At the end, your OMS processes will be running ["emctl status oms" to verify] and you may login to your new 12c OEM.

Upgrade from OEM 11g to 12c: Clustered agents reported as "agents with Invalid Inventory"

We are in the process of upgrading our OEM 11g installation to 12c.
In the "Upgrade Console", agents installed to monitor a RAC were reported as "agents with Invalid Inventory".
These agents were installed using this method, in a custom Oracle inventory.
Additionally, from the "Setup", "Agents" tab, you can see a particular agent's page. There, by pressing the "Oracle Home" link, I was getting the following error:

Could not find Oracle Home <ORACLE_HOME> in the inventory collected for <HOSTNAME> on <TIMESTAMP>

As described in Oracle note 735952.1 - "Error Could not find Oracle Home in the inventory collected for on ", although some changes were necessary, to fix this problem:

• Edit $AGENT_HOME/sysman/config/OUIinventories.add
• Put an entry for your custom oraInst.loc, for example: inventory: /var/opt/oragrid/oraInst.loc
• Restart the agent
• From the OEM, go to the particular host's page, "Configuration" tab and press the "Refresh Host Configuration" button.
• Wait for it to complete and then go to your "Upgrade Console" and press the "Refresh Agents and Targets List" button.

Now, those agents are removed from the "agents with Invalid Inventory" list.

OMS 11g: Copy agent binaries to another server and reconfigure

System admins cloned a server, including OMS agent's binaries, and I did not want to reinstall it.
At start, agent should be down.
Edit $OMS_HOME/sysman/config/emd.properties and change EMD_URL entry to your new server's hostname:

EMD_URL=http://newserver:3872/emd/main/

Delete a few files:

cd $OMS_HOME/sysman/emd
rm -rf state
rm -rf upload
rm lastupld.xml
rm agntstmp.txt
rm protocol.ini
rm blackouts.xml
rm targets.xml

Create a new targets.xml:

<?xml version = "1.0"?>
<Targets>
</Targets>

Start agent:

emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting agent ...... started.

Upload:

emctl upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

Get its status and check "Agent URL" is pointing to new server and if upload was successful:

emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 11.1.0.1.0
OMS Version       : 11.1.0.1.0
Protocol Version  : 11.1.0.0.0
Agent Home        : /oragrid/agent11g
Agent binaries    : /oragrid/agent11g
Agent Process ID  : 8061080
Parent Process ID : 5701676
Agent URL         : http://newserver:3872/emd/main/
Repository URL    : http://OMSserver:4889/em/upload/
Started at        : 2012-12-05 13:01:57
Started by user   : oragrid
Last Reload       : 2012-12-05 13:03:06
Last successful upload                       : 2012-12-05 13:03:09
Total Megabytes of XML files uploaded so far :     3.09
Number of XML files pending upload           :        0
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    44.41%
Last successful heartbeat to OMS             : 2012-12-05 13:03:00
---------------------------------------------------------------
Agent is Running and Ready

Now, if you login to your OMS, you will see your new agent running, but it will not monitor the host installed in. OMS will not even know in which host this agent runs.

Stop agent:

emctl stop agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.

Edit $OMS_HOME/sysman/emd/targets.xml and add the host target:

<Targets AGENT_TOKEN="23e6c87aa252cb56f70800785be11d6071944de7">
        <Target TYPE="oracle_emd" NAME="newserver:3872"/>
        <Target TYPE="host" NAME="newserver"/>
</Targets>

Start, upload and status once more:

emctl start agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting agent ..... started.

emctl upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 11.1.0.1.0
OMS Version       : 11.1.0.1.0
Protocol Version  : 11.1.0.0.0
Agent Home        : /oragrid/agent11g
Agent binaries    : /oragrid/agent11g
Agent Process ID  : 5308612
Parent Process ID : 9633920
Agent URL         : http://newserver:3872/emd/main/
Repository URL    : http://OMSserver:4889/em/upload/
Started at        : 2012-12-05 14:04:03
Started by user   : oragrid
Last Reload       : 2012-12-05 14:04:03
Last successful upload                       : 2012-12-05 14:04:15
Total Megabytes of XML files uploaded so far :     0.51
Number of XML files pending upload           :        1
Size of XML files pending upload(MB)         :     1.15
Available disk space on upload filesystem    :    44.40%
Last successful heartbeat to OMS             : 2012-12-05 14:04:05
---------------------------------------------------------------
Agent is Running and Ready

Now, login to your OMS add under Targets tab, All Targets subtab search for type Agent and your new server's hostname.
You will find your new agent with only one monitoring target, your host.

Script to check if AWR snapshot collection has stopped

In one database, after the archive log destination got full, snapshot collection stopped.
There was no way to know this had happened. Only in the MMON trace file I got the following message, for which there is no reference in Metalink:

Unable to schedule a MMON slave at: Auto Flush Main 1
  Slave action has been temporarily suspended
    - Slave action had prior policy violations.
  Unknown return code: 101

So, I had 2 choices. Either to restart the database or to kill MMON and MMNL processes.
I chose the latter.
You will get these in your alert log, when they restart:

Restarting dead background process MMON
Restarting dead background process MMNL
Mon Jul 30 08:18:24 2012
MMON started with pid=18, OS id=10223826 
Mon Jul 30 08:18:24 2012
MMNL started with pid=19, OS id=15204612 

Now snapshot collection is restored.

If you compare the time passed from the last snapshot collected and the interval you have set for snapshot collection, you will find out if the collection is halted.
For instance, if your interval is set to 1 hour (the default) and the last snapshot was collected more than 1 hour ago, then you may assume something is wrong.
The following script will return 0, if [time from last snapshot] < [snapshot interval] and there is no problem and 1 otherwise.

SELECT (CASE WHEN NUMTODSINTERVAL ( (SYSDATE - MAX(CAST(END_INTERVAL_TIME AS DATE))), 'DAY') < SNAP_INTERVAL THEN 0 ELSE 1 END) "RESULT"
FROM DBA_HIST_SNAPSHOT, DBA_HIST_WR_CONTROL
GROUP BY SNAP_INTERVAL;

If you want to create a database user-defined metric to monitor this, you should use a function.
The CASE statement confuses OEM and rejects the query.
First, connect as SYSDBA and grant SELECT on DBA_HIST_SNAPSHOT and DBA_HIST_WR_CONTROL to SYSTEM.
The function is:

CREATE OR REPLACE FUNCTION SYSTEM.CHECKAWR RETURN NUMBER
IS
V_RESULT NUMBER;
BEGIN
SELECT (CASE WHEN NUMTODSINTERVAL ( (SYSDATE - MAX(CAST(END_INTERVAL_TIME AS DATE))), 'DAY') < SNAP_INTERVAL THEN 0 ELSE 1 END) "RESULT" INTO V_RESULT
FROM DBA_HIST_SNAPSHOT, DBA_HIST_WR_CONTROL
GROUP BY SNAP_INTERVAL;

RETURN V_RESULT;
END;
/

Now, in the "SQL Query" box, put:

SELECT SYSTEM.CHECKAWR FROM DUAL; 

ORA-13767 ORA-13780 creating SQL Tuning Task in 11.2.0.3

Tried to create a SQL Tuning Task from Grid Control 11g in a 11.2.0.3 database and I got a ORA-13767 error.

Tried to create it by running DBMS_SQLTUNE.CREATE_TUNING_TASK directly to the target database and got a ORA-13780.

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 L_SQL_TUNE_TASK_ID VARCHAR2(100);
3 BEGIN
4 L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (
5 SQL_ID => 'fcs0af97qvuft',
6 SCOPE => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
7 TIME_LIMIT => 600,
8 TASK_NAME => 'fcs0af97qvuft_task',
9 DESCRIPTION => 'Tuning task for fcs0af97qvuft');
10 DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
11 END;
12 /
DECLARE
*
ERROR AT LINE 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 125
ORA-06512: at "SYS.DBMS_SQLTUNE", line 655
ORA-06512: at line 4

Installed DB Control in the target database and I got a ORA-13780 again.

If I specify in the task 2 snapshots that include an execution of the particular SQL statement, I get no error:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 L_SQL_TUNE_TASK_ID VARCHAR2(100);
3 BEGIN
4 L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (
5 BEGIN_SNAP=>3935,
6 END_SNAP=>3937,
7 SQL_ID => 'fcs0af97qvuft',
8 SCOPE => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
9 TIME_LIMIT => 600,
10 TASK_NAME => 'fcs0af97qvuft_task',
11 DESCRIPTION => 'Tuning task for fcs0af97qvuft');
12 DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
13 END;
14 /
l_sql_tune_task_id: fcs0af97qvuft_task

PL/SQL procedure successfully completed.

You can find in which snapshots your query exists, by running the following query:

SELECT SNAP_ID
FROM DBA_HIST_SQLSTAT
WHERE SQL_ID='fcs0af97qvuft'
ORDER BY SNAP_ID;

The only relevant Oracle Note is 1445188.1, but it states that Bug 9957725 is fixed in DB Control 11.2.0.3 and 12g.

Oracle filed Bug 14342949 for this issue.

20/11/2012: From Oracle Development:

It looks like the error message is expected. Once the SQL is selected and you are on the SQL Details page : If the Statistics tab / panel shows "Snapshot Time : date/time to date/time : No data is available for this statement in this snapshot duration".

Selecting the "Schedule SQL Tuning Advisor" will run the dbms_sqltune.create_tuning_task with that begin_snap and end_snap, which correctly results in the ORA-13780 error. The screen will default to the current date/time, so customers need to select a snapshot on the statistics tab/pane with data prior to calling the "Schedule SQL Tuning Advisor".

It seems to be expected behaviour.

Custom scripts to monitor Siebel using OMS user defined metrics

In this post, I describe how to create user defined metrics in hosts.
The following scripts will raise alerts, whenever something's wrong.

IBM HTTP Server:

#!/bin/sh
PS=`ps -ef|grep "HTTPServer -k start"|grep -v grep|wc -l`

echo em_result=$PS
if test $PS -lt 4
then
echo em_message='Apache is down. Processes='$PS
else
echo em_message='Apache is up. Processes='$PS
fi

Oracle BI Publisher:

#!/bin/sh
PS=`ps -ef|grep oc4j_bi|grep java|grep -v grep|wc -l`

echo em_result=$PS
if test $PS -ne 1
then
echo em_message='BI Publisher is down. Processes='$PS
else
echo em_message='BI Publisher is up. Processes='$PS
fi

Gateway Server:

#!/bin/sh
PS=`ps -ef|grep gtwyns|grep -v grep|wc -l`

echo em_result=$PS
if test $PS -ne 1
then
echo em_message='Gateway is down. Processes='$PS
else
echo em_message='Gateway is up. Processes='$PS
fi

Siebel Server:
server.cfg:

spool server.lst
list server show SBLSRVR_NAME,HOST_NAME,SBLSRVR_STATE,START_TIME,END_TIME
exit

Main script [Insert your env. variables' values]:

export SIEBEL_HOME=

export SIEBGATE=
export SIEBENT=
export SADMINPWD=
export WORKDIR=
export HOST_NAME=

. $SIEBEL_HOME/siebenv.sh

cd $WORKDIR

cat /dev/null>server.lst

srvrmgr /g $SIEBGATE /e $SIEBENT /u sadmin /p $SADMINPWD /s $HOST_NAME /i server.cfg 1>/dev/null

GREP=`grep Running server.lst |wc -l`
echo em_result=$GREP

if test $GREP -lt 1
    then
                echo em_message='Siebel Server is down.'
        else
                echo em_message='Siebel Server is up.'
fi

Siebel Components:

comp.cfg:

spool comp.lst
list comp show SV_NAME,CC_ALIAS,CC_NAME,CP_DISP_RUN_STATE,CP_START_TIME,CP_END_TIME
exit

Main script [Insert your env. variables' values]:

export SIEBEL_HOME=
export SIEBGATE=
export SIEBENT=
export SADMINPWD=
export WORKDIR=
export HOST_NAME=

. $SIEBEL_HOME/siebenv.sh

cd $WORKDIR

cat /dev/null>comp.lst

srvrmgr /g $SIEBGATE /e $SIEBENT /u sadmin /p $SADMINPWD /s $HOST_NAME /i comp.cfg 1>/dev/null

GREP=`egrep '(Unavailable|Shutdown)' comp.lst |wc -l`
echo em_result=$GREP

if test $GREP -gt 0
    then
                echo em_message=$GREP' Components down.'
        else
                echo em_message='Components are OK.'
fi

You may use OMS 11g preinstalled Siebel plugin, instead, although I haven't seen anything about components' status. You only get informed, if gateway or Siebel servers are up or down.
If you choose to use this plugin, make sure agent's OS user has the same primary group as Siebel's OS user. Oracle had to open Bug 13849748 for me, until I discovered that this was needed, since it is not mentioned in any documentation.
Also agent messes with /tmp/regss* files, taking their ownership and crashing, sometimes, the gateway server. Remove "${MWHOME}/bin/regautobackup -off" from your siebenv.sh files to correct this.

Grid 11g:"Siebel Discovery Agent is not installed at one or more hosts" trying to discover Siebel topology

We installed agents in all our 4 Siebel servers, let's say named sappl1, sappl2, sappl3 and sappl4.
Under Hosts Tab we see they are up.
When we tried to discover Siebel topology, we got errors:

Discovery Summary
Add Enterprise operation is not successful.  Resolve the error messages and then try again

Error Messages
     Siebel Discovery Agent is not installed at one or more hosts
     Discovery From Gateway Server Failed

Trace Messages
     Discovered the Siebel topology from Gateway Server

In our OMS server, in ../gc_inst/em/EMGC_OMS1/sysman/log/emoms.trc, there were these entries:

2012-02-23 09:12:26,891 [Thread-114] ERROR enterprise.TDProcConfig log.625 - doConfiguration: There are some issues with respect to Agents.
2012-02-23 09:12:26,891 [Thread-114] ERROR enterprise.TDProcConfig log.625 - doConfiguration: Agent erros out for host  sapp3 with message : .Siebel Discovery Agent is not installed at one or more hosts
2012-02-23 09:12:26,891 [Thread-114] ERROR enterprise.TDProcConfig log.625 - doConfiguration: Agent erros out for host  sapp2 with message : .Siebel Discovery Agent is not installed at one or more hosts
2012-02-23 09:12:26,891 [Thread-114] ERROR enterprise.TDProcConfig log.625 - doConfiguration: Agent erros out for host  sapp4 with message : .Siebel Discovery Agent is not installed at one or more hosts

Names sapp2, sapp3, sapp4 were not correct.
OMS had discovered the servers as "sapplX".
In Siebel servers' /etc/hosts file the entries were:

10.101.1.17    sapp1        sappl1
10.101.1.18    sapp2        sappl2
10.101.1.19    sapp3        sappl3
10.101.1.20    sapp4        sappl4

We changed the order:

10.101.1.17    sappl1        sblapp1
10.101.1.18    sappl2        sblapp2
10.101.1.19    sappl3        sblapp3
10.101.1.20    sappl4        sblapp4

Discovery will succeed this time.

Backend WLS or EM application seems to be down

A few days ago, the server, where OMS and its DB reside, crushed.
After we restarted it, whenever we tried to access the grid console, we got error "Backend WLS or EM application seems to be down".
Agents failed to upload XML files to OMS and "emctl pingOMS" was giving an error "EMD pingOMS error: No response header from OMS".
We checked WebLogic and OMS .trc, .log and .out files, but there was no error recorded, neither before nor after the crush.

To correct this issue:

    1. Stop OMS

     emctl stop oms -all

    2. Kill -9 all WebLogic and OMS processes still running after the stop. You can find these processes, using ps.

     ps -ef | grep EMGC_ADMINSERVER
     ps -ef | grep EMGC_OMS1
     ps -ef | grep oms

    3. Delete every .lok file you find under WebLogic Domain

     find . -name "*.lok"

    These files were:

    ../gc_inst/user_projects/domains/GCDomain/config/config.lok
    ../gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/data/ldap/ldapfiles/EmbeddedLDAP.lok
    ../gc_inst/user_projects/domains/GCDomain/servers/EMGC_OMS1/tmp/EMGC_OMS1.lok
    ../gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/data/ldap/ldapfiles/EmbeddedLDAP.lok
    ../gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/tmp/EMGC_ADMINSERVER.lok

    4. Start OMS

     emctl start oms

The best matching Oracle Documents about this incident are:

• ID 943790.1: What are the .lok Files Used For in a WebLogic Server (WLS) Domain? In general, these files are a mechanism to ensure file and server locks and to prevent a server from being booted twice.
• ID 957377.1: Weblogic Fails To Start With Error "Unable To Obtain Lock"
• ID 1235753.1: 11g Grid Control: OMS Startup Shows "AdminServer Could Not Be Started" but OMS is able to Startup

Another cause for this error could be incorrect entries in your /etc/hosts file.
Your 127.0.0.1 entry should be exactly like:

127.0.0.1    localhost.localdomain    localhost

And you should remove any entry about IPv6, so delete or comment:

::1    localhost6.localdomain6    localhost6

OEM: "Tablespace Used (%)" alert is not triggered

There was an incident, where a tablespace filled completely, but I did not get any alert from OEM.

There is Oracle Note 403264.1:"Troubleshooting a Database Tablespace Used(%) Alert problem", where several issues about this alert are discussed.
In my case, there was no entry in DBA_OUTSTANDING_ALERTS,
although in DBA_TABLESPACE_USAGE_METRICS the particular tablespace had exceeded the thresholds I have set.

So, according to Section 4 in this note, this is probably caused by database bugs 7462407, 5245039.
Additionally, I have a "gap" in my v$tablespace.

Unfortunately, there are no patches for these bugs for Windows platforms, so I created a user defined metric to get these alerts.
The new metric was based on DBA_TABLESPACE_USAGE_METRICS, which is very nice, because it queries v$filespace_usage, which keeps track of any MAXSIZE value you may have set in your autoextended datafiles.
If it is set to UNLIMITED, then this value is equal to 32 GB for 8 KB block size (2^22-1 * 8 KB).

Keep in mind, columns referencing sizes in these 2 views are blocks.
So, if your tablespace has block size of 8 KB, multiply these values by 8 to get its size in KB.

Of course, there is no way for your database to know, if your filesystems will fill, e.g. if in your filesystem you have 10 GB of free space and an autoextended datafile in it with MAXSIZE 15 GB, you will not get any alert, even from the default OEM metric, when your filesystem's free space depletes.
You should use "Filesystem Space Available (%)" alert at host level, to check your filesystem's free space.

Oracle Enterprise Manager 11g (11.1.0.1.0) agent installation using response file

Download and extract agent's binaries in a directory in your server, e.g. /var/opt/oragrid/binaries.
Open file /var/opt/oragrid/binaries/hpi/response/additional_agent.rsp and do the following changes:

SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE
DECLINE_SECURITY_UPDATES=TRUE
#MYORACLESUPPORT_USERNAME=
#MYORACLESUPPORT_PASSWORD=
#COLLECTOR_SUPPORTHUB_URL=
ORACLE_AGENT_HOME_LOCATION=[directory where agent software will be installed, e.g. /var/opt/oragrid]
OMS_HOST=[server's name where OMS is installed]
OMS_PORT=[server's port, default 4889]
AGENT_REGISTRATION_PASSWORD=[sysman's password]

If you are installing an agent in a RAC, change also:

CLUSTER_NODES="[node1's name],[node2's name],..."

If you don't want to mess with server's oracle central inventory, use a custom inventory, by creating an oraInst.loc file, e.g. /var/opt/oragrid/oraInst.loc.
Open it and add:

inventory_loc=[path where this inventory will be placed, e.g. /var/opt/oragrid/oraInventory]
inst_group=[agent's user group, dba or oinstall]

Go to /var/opt/oragrid/binaries/hpi/agent/install and run:

runInstaller -invPtrLoc /var/opt/oragrid/oraInst.loc -silent -responseFile /var/opt/oragrid/binaries/hpi/response/additional_agent.rsp

If you are installing in a RAC, find cluster name:

cd $CRS_HOME/bin
./cemutlo -n

Now, run:

runInstaller -invPtrLoc /var/opt/oragrid/oraInst.loc -silent -responseFile /var/opt/oragrid/binaries/hpi/response/additional_agent.rsp -CLUSTER_NAME=[cluster name] CLUSTER_NODES="{[node1's name],[node2's name],...}"

There is a bug that fills emagent.trc pretty fast.
So, at the end, install patch 9756966.

Finally, run [agent's home]/root.sh as root.

In the RAC installation, all onoff patches that were supposed to be installed after agent's installation, failed, because although I had specified my custom oraInst.loc in the runInstaller command, OPatch was using oraInst.loc under agent's home directory.
Probably, you will not face this issue, if you don't specify a custom inventory.
If that happens also to you, go to [agent's home]/install/oneoffs/111010 and install them manually.

If you try to monitor a 9i database not in 9.2.0.8.0 patchset, you will probably get many ORA-07445 in the alert log and core dumps in your database's cdump directory.
To fix this, edit [agent's home]/sysman/config/emd.properties and add the parameter AvoidOCIPing=1.
Then, restart the agent.

Oracle Enterprise Manager 11g: Install & configure EM CLI in agents for failovers in Cold Failover Cluster

First of all, install agents in all cluster nodes, discover  and configure the targets running in them.
If a node fails and you move your oracle service to a working node, you would want to inform OMS for this relocation.
This is accomplished, using Enterprise Manager Command Line Interface (EM CLI).

Download emclikit.jar from your grid installation:

http(s)://[host]:[port]/em/console/emcli/download

The following must be executed for every agent deployed in cluster nodes.

Set your JAVA_HOME enviroment (Java 1.6.0 or greater is needed):

export JAVA_HOME=[java directory]
export PATH=$JAVA_HOME/bin:$PATH

Under agent's home create a "emcli" directory and put emclikit.jar in it.
Run:

java -jar emclikit.jar client -install_dir=[directory where emclikit.jar is placed]

Setup EM CLI:

emcli setup -url="http(s)://[host]:[port]/em" -username=sysman -password=[sysman password] -licans=YES -dir=[directory where emclikit.jar is placed] -trustall -localdirans=YES

You can check targets discovered by your OMS, running the following query:

SELECT * FROM SYSMAN.MGMT_TARGETS ORDER BY TARGET_TYPE;

Or by giving the following command:

emctl config agent listtargets

Now, lets say you relocate a target e.g. a database called TESTDB from node1 to node2.
If node1's agent is still running, you can leave everything as it is. An agent can monitor targets running in other nodes.
If node1 completely fails and its agent stops running, you should instruct OMS to monitor this target using another agent, so execute:

emcli relocate_targets -src_agent=node1:[agent's port, default 3872] -dest_agent=node2:[agent's port, default 3872] -target_name=TESTDB -target_type=oracle_database -copy_from_src -force=YES

If you get a "target [database instance, oracle_database] is broken: cannot compute dynamic properties in time" error:

emctl stop agent

Edit [agent's home]/sysman/config/emd.properties and change value of dynamicPropsComputeTimeout to 120.

emctl start agent
emctl upload agent

Grid Control: Create user-defined metrics at OS level

Using a scripting language of your choice, create a script that contains logic to check for the condition being monitored.
For example, scripts that check for disk space or memory usage.
All scripts to be run with User-Defined Metrics should be placed in a directory to which the Management Agent has full access privileges.
Scripts themselves must have the requisite permissions set so that they can be executed by the Management Agent.
The script runtime environment must also be configured:
If your script requires an interpreter, such as a Perl interpreter, this must be installed on that host as well.

Let's say that we want to create a metric to alert us if something is wrong with a critical OS process running to our server.
This could be the e-BS Internal Manager.
First of all, we need to create the script "/appl/oragrid/checkInternal.sh":

#!/bin/sh
PS=`ps -ef|grep FNDCPMBR|grep -v grep|wc -l`

echo em_result=$PS
if test $PS -eq 1
then
echo em_message='Internal Manager is OK.'
else
if test $PS -gt 1
then
echo em_message='Stucked Internal Manager process found.'
else
echo em_message='Internal Manager is down.'
fi
fi

Enterprise Manager needs two parameters to be set, in order to be able to process the script's output, em_result and em_message.
em_result is the value that will be compared against the warning and critical threshold you will set in the metric's creation page (see image) and raise the appropriate alert.
The default message for this alert will be: "The value is [em_result]".
For our example, "=1" means we have one Internal Manager process, which is the expected behavior.
">1" means that more than one processes are running and "<1" that there is no process running.
Both these conditions are not acceptable and an alert should be risen about them.
If we want to receive a more clear message than the default for our alert, we set the em_message parameter.
So, if "em_result=1" then "Internal Manager is OK", if "em_result>1" then "Stucked Internal Manager process found" and if "em_result<1" then "Internal Manager is down".

The next step is to create the alert from the Enterprise Manager.
Login and go to the Targets tab.
In the Hosts tab, choose the host where you created your script and to the Related Links section, click the User-defined Metrics link.
Press the Create button and you will be transferred to the page shown in the image attached.
In the Command Line field enter the full command path and the full path name of your script, for our case: "/usr/bin/sh /appl/oragrid/checkInternal.sh".
Choose the appropriate Comparison Operator (for our example this is: "!=") and the Warning and Critical Thresholds.
For our example we set "1" in the Critical field.
Scheduling is obvious.
You define how frequently your metric will be monitored by the Enterprise Manager.
Now, whenever something is wrong with our Internal Manager process on our server, an alert will rise and will be displayed with the message of our choice on on the main host page under the Alerts section.
To be notified via e-mail for these types of alerts, just add the User Defined Numeric Metric in the notification rule you already have for your host or create a new one.
Be sure to be subscribed to this notification rule.

Grid Control: Create user-defined metrics at database level

User-Defined Metrics allow you to extend the reach of Enterprise Manager’s monitoring to conditions specific to particular environments via custom scripts or SQL queries and function calls. Once defined, User-Defined Metrics will be monitored, aggregated in the repository and trigger alerts, like regular metrics.

Login to Enterprise Manager and go to the Databases tab. Enter the database you choose and at the Related Link section click the User-defined Metrics link.
Press Create button and you will be transferred to the page shown at the image posted, where you can create your custom metric.

I will not explain every detail you see in this page, since a lot of them are obvious.
You should understand the meaning of thresholds. Your query should return a single value or a two-column set of values. In the first case, you set the Comparison Operator and the Warning and Critical Values according to what you want. In the second case, in the Warning Thresholds by Key and Critical Thresholds by Key fields you define for individual keys specific values that trigger an alert. An example:

SELECT ename FROM emp
Threshold settings for this example are shown.
Use the format key:value . Keys are case-sensitive.
■ Warning:500
■ Critical:300
■ Comparison Operator: < ■ Warning threshold by key: SMITH:250;JONES:400;CLARK:900 The warning threshold is set to 250 for SMITH, 400 for JONES, and 900 for CLARK. ■ Critical threshold by key: SMITH:100;JONES:200;CLARK:500 The critical threshold is set to 100 for SMITH, 200 for JONES, and 500 for CLARK. All other keys will use the threshold values specified in the Warning and Critical fields. When these conditions are met, a warning or critical alert will be risen on the main database page under the Alerts section.

Now, an example for a String metric type.
Supposedly, you want to raise an alert when a job in your database becomes broken (stops running). An appropriate query could be:

select broken from dba_jobs where what = 'KEEP_SIZE_AUD_LOG';

This will return either 'Y' or 'N' (a string value). So, I choose String Metric Type and Single Value SQL Query Output. For Comparison Operator we choose MATCH and and we enter Y in the field Critical. Warning alert has no meaning in our case, so this field will be left blank.
Now, when this particular job stops running a Critical Alert will be risen on the main database page under the Alerts section.

If you do not want to get as an Alert name something vogue, such as "[Metric Name]=Y", you may enter something else in the Alert Message field, like "Job KEEP_SIZE_AUD_LOG is broken." For the "SELECT ename FROM emp" example you could use "Underpaid Employee: %Key% has salary of %value%", so e.g. you will get a warning alert if Smith's [key] salary drops below 250 [value].

Scheduling is obvious. You define how frequently your metric will be monitored by the Enterprise Manager.

To be notified via e-mail for these types of alerts, just add the User Defined Numeric Metric and/or User-Defined String Metric in the notification rule you already have for your database or create a new one. Be sure to be subscribed to this notification rule.