Purging SYSAUX tablespace (Purging AWR reports)

Dump below is of a session clearing the sysaux tablespace. It turned out that something went wrong with the automatic AWR gathering/purging and the sysaux tablespace became very large. …

Thanks to this blog I found script below wich displays the various sysaux components and their sizes. …

SQL> col "Space (M)" for 999,999.99  
SQL> 
SQL> SELECT 
  2    occupant_name,  
  3    round( space_usage_kbytes/1024) "Space (M)",  
  4    schema_name, 
  5    move_procedure
  6  FROM 
  7    v$sysaux_occupants  
  8  ORDER BY 
  9    1  
 10  /
OCCUPANT_NAME          Space (M) SCHEMA_NAME          MOVE_PROCEDURE
-------------------- ----------- -------------------- ----------------------------------------
AO                          1.00 SYS                  DBMS_AW.MOVE_AWMETA
EM                           .00 SYSMAN               emd_maintenance.move_em_tblspc
JOB_SCHEDULER               1.00 SYS
LOGMNR  
Read more →

Manually triggering Metric Collection (clearing -tablespace- Alerts)

Manually triggering metric collection in Oracle Enterprise Manager (GRID control) …

Here’s the scenario: …

You have an Alert that your tablespace is ‘full’,
You enlarge the tablespace,
The ‘Tablespace Full Alert’ is still in your dashbord for the next 24 (x) hours since you’ve configured this metric to be collected once every 24 (x) hours,
You want however to ‘remove’ (clear) this Alert. In order to achive this you will have to to re-evaluate this metric collection … …

Here is how you do it (for anything storage related on your database server): …

$ emctl control agent runCollection <hostname>:host host_storage

See the Oracle docs, 2.7.6 Reevaluating Metric Collections. It shows you how to retrieve the correct collection name for any metric. …

You will find yourself grep-ing through ‘$ORACLE_HOME/sysman/admin/metadata/*xml’ and ‘$ORACLE_HOME/sysman/admin/default_collection/*xml’ … …

Read more →

Increase idle time for OMS frontend sessions

Some additional configuration directives. …

To increase the idle time for the OMS frontend sessions edit the ‘oracle.sysman.eml.maxInactiveTime’ configuration parameter your Oracle Management Server’s emoms.properties file. The parameter value unit is in minutes and the default is 15 minutes which is a bit short I suppose. I have set it to 4 hours as you can see in the example below. …

$ cat $ORACLE_HOME/sysman/config/emoms.properties | grep maxInactiveTime
oracle.sysman.eml.maxInactiveTime=240
$

For more info see the ‘Enterprise Manager Advanced Configuration‘ book …

Read more →

Really delete (‘corrupt’) Agent from OMS repository

Here’s how to overcome ORA-20206 in Oracle GRID environment, agent status unclear… …

Scenario

Removed (Windows) Agent from Grid. (With SQL Server Management plugin)
Tried to restart this SAME agent, errors below occur. …

# $ORACLE_HOME/sysman/log/emagent.log contains

2008-01-17 14:13:52 Thread-4032 EMAgent started successfully (00702)
2008-01-17 14:14:21 Thread-2720 <Upload Manager> Unsuccessful Upload attempts for XML file exceeds specified limit=3, 
      Agent will shutdown (00851)
2008-01-17 14:14:21 Thread-4032 EMAgent abnormal terminating (00704)
# $ORACLE_HOME/sysman/log/emdctl.trc contains

2008-01-17 14:13:32 Thread-752 WARN  http: snmehl_connect: connect failed to (<agent_hostname>:3872): 
      No connection could be made because the target machine actively refused it.
 (error = 10061)
2008-01-17 14:13:32 Thread-752 ERROR main: nmectla_agentctl: Error connecting to 
      https://<agent_hostname>:3872/emd/main/. Returning status code 1
2008-01-17 14:14:23 Thread-172 WARN  http: snmehl_connect: connect failed to 
      (<agent_hostname>:3872): 
      No connection could be made because the target machine actively refused it. (error = 10061)
2008-01-17 14:14:23 Thread-172 ERROR main: nmectla_agentctl: Error connecting to 
      https://<agent_hostname>:3872/emd/main/. Returning status code 1

# $ORACLE_HOME/sysman/log/emagent.trc contains

2008-01-17 14:14:18 Thread-192 WARN  TargetManager: Regenerating all Metadata
2008-01-17 14:14:18 Thread-192 DEBUG TargetManager: In getTypeAndNameForHostTarget
2008-01-17 14:14:18 Thread-192 DEBUG TargetManager: getTypeAndNameForHostTarget found host, <agent_hostname>
2008-01-17 14:14:18 Thread-192 DEBUG TargetManager: In getTypeAndNameForHostTarget
2008-01-17 14:14:18 Thread-192 DEBUG TargetManager: getTypeAndNameForHostTarget found host, <agent_hostname>
2008-01-17 14:14:18 Thread-192 WARN  collector: Regenerating all DefaultColls
2008-01-17 14:14:18 Thread-192 ERROR upload: Failed to upload file A0000001.xml, ret = -6
2008-01-17 14:14:19 Thread-192 WARN  upload: FxferSend: received fatal error in
Read more →

Clearing Alerts in Oracle GRID Control

Here’s how to clear alerts from the Oracle GRID manually. …

For some obscure reason Oracle is very reluctant in providing the information that is described here below. Search Metalink and you will find no entries… The information below is from dbasupport.com and helps me to clear alerts that the Agent does NOT clear for me. …

sysman.em_severity
[REMI@DB01.REMIDIAN.COM]
SQL>  @desc sysman.em_severity
.
.
PROCEDURE DELETE_CURRENT_SEVERITY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TARGET_GUID                  RAW                     IN
 P_METRIC_GUID                  RAW                     IN
 P_KEY_VALUE                    VARCHAR2                IN
.
.

Generate SQL to remove alerts from specific targets
select t.target_name
,      t.target_type
,      collection_timestamp
,      message
,
Read more →