AskTom

Hi,

in my grid control still left old alerts on the first page. This alerts are obsolete and should be deleted automatically but isnt so I looked for any solution. Oracle forum
And there is.

1) connect as user SYSMAN to repository database and select you alert.

-you can use ths general query
SELECT * from MGMT_CURRENT_SEVERITY;

or specific for one target

select t.target_name,t.target_type,collection_timestamp,message,
‘exec em_severity.delete_current_severity(”’ ||t.target_guid || ”’,”’ ||metric_guid || ”’,”’ ||key_value || ”’)’ em_severity
from mgmt_targets t inner join mgmt_current_severity s on t.target_guid = s.target_guid
where target_name like ‘&target’;

TARGET_NAME
——————————————————————————–
TARGET_TYPE
—————————————————————-
COLLECTION_TIMESTA
——————
MESSAGE
——————————————————————————–
EM_SEVERITY
——————————————————————————–
dottk.taloha.tk
oracle_database
05-OCT-09

TARGET_NAME
——————————————————————————–
TARGET_TYPE
—————————————————————-
COLLECTION_TIMESTA
——————
MESSAGE
——————————————————————————–
EM_SEVERITY
——————————————————————————–
Snapshot Too Old Error detected: SQL ID 7nsy3bq9wp2hv, Snapshot SCN 0×0002.3d5b1
87c, Recent SCN 0×0002.3d5e6b21, Undo Tablespace UNDOTBS1, Current Undo Retentio
n 6093.

TARGET_NAME
——————————————————————————–
TARGET_TYPE
—————————————————————-
COLLECTION_TIMESTA
——————
MESSAGE
——————————————————————————–
EM_SEVERITY
——————————————————————————–
exec em_severity.delete_current_severity(’7EF58D39E522C52FE009EECEB83B2AED’,'109
613CA182F59DA38A43D8C1E79E7AB’,'UNDOTBS1′)

and now you can delete the messages (example)

exec em_severity.delete_current_severity(’7EF58D39E522C52FE009EECEB83B2AED’,'109613CA182F59DA38A43D8C1E79E7AB’,'UNDOTBS1′)

Regards,

Tom

Hi,

sametimes you can have trouble with agent. The best choice is to delete this files and start agent again.

Delete all files at $AGENT_HOME/sysman/emd/upload and $AGENT_HOME/sysman/emd/state
export AGENT_HOME=/opt/oracle/product/agent10g/
echo $AGENT_HOME
rm –Rf $AGENT_HOME/sysman/emd/upload/*.*
rm –Rf $AGENT_HOME/sysman/emd/state/*.*

$ ./emctl clearstate agent
$ ./emctl unsecure agent
$ ./emctl start agent
$ ./emctl upload agent
$ ./emctl secure agent

Check 731692.1 and 808318.1 documents on metalink

Regards,

Tom

We have corrupted datafile sysaux:

Mon May 04 06:00:20 2009
Errors in file /opt/app/oracle/diag/rdbms/sprod/sprod/trace/sprod_j000_9704.trc (incident=120442):
ORA-01578: ORACLE data block corrupted (file # 3, block # 34676)
ORA-01110: data file 3: ‘+DATA/sprod/datafile/sysaux.270.685013947′
Incident details in: /opt/app/oracle/diag/rdbms/sprod/sprod/incident/incdir_120442/sprod_j000_9704_i120442.trc

1) Verify the identity of the corrupt object
SQL> SELECT segment_name , segment_type , owner , tablespace_name
FROM sys.dba_extents
WHERE file_id = 3
AND 34676 BETWEEN block_id and block_id + blocks -1; 2 3 4

SEGMENT_NAME
——————————————————————————–
SEGMENT_TYPE OWNER TABLESPACE_NAME
—————— —————————— ——————————
MGMT_METRICS_1HOUR_PK
INDEX SYSMAN SYSAUX

2) Analyze the parent table
SQL> analyze table sysman.mgmt_metrics_1hour validate structure cascade;
analyze table sysman.mgmt_metrics_1hour validate structure cascade
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 3, block # 76185)
ORA-01110: data file 3: ‘+DATA/sprod/datafile/sysaux.270.685013947′

SQL> analyze index sysman.mgmt_metrics_1hour_pk validate structure;
analyze index sysman.mgmt_metrics_1hour_pk validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 3, block # 76185)
ORA-01110: data file 3: ‘+DATA/sprod/datafile/sysaux.270.685013947′

3) unable to rebuild online
SQL> alter index sysman.MGMT_METRICS_1HOUR_PK rebuild online;
alter index sysman.MGMT_METRICS_1HOUR_PK rebuild online
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

SQL> alter table sysman.MGMT_METRICS_1HOUR move online;
alter table sysman.MGMT_METRICS_1HOUR move online
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 3, block # 34676)
ORA-01110: data file 3: ‘+DATA/sprod/datafile/sysaux.270.685013947′

Solution: Recreate SYSMAN schema

oracle@antarctic:/opt/app/oracle/product/11/db_1/bin> sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Wed May 6 16:29:17 2009

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop user sysman cascade;

User dropped.

SQL> drop role MGMT_USER;

Role dropped.

SQL> drop user MGMT_VIEW cascade;

User dropped.

SQL> drop public synonym MGMT_TARGET_BLACKOUTS;

Synonym dropped.

SQL> drop public synonym SETEMVIEWUSERCONTEXT;

Synonym dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@antarctic:/opt/app/oracle/product/11/db_1/bin> $ORACLE_HOME/sysman/admin/emdrep/bin/RepManager antarctic 2483 sprod -action drop
Enter SYS user’s password :
Enter repository user name : sysman
Getting temporary tablespace from database…
Found temporary tablespace: TEMP
Checking SYS Credentials … rem error switch
OK.
rem error switch
Dropping the repository..
Quiescing DB … Done.
Checking for Repos User … Does not Exist.
Repos User does not exist …  Continuing with cleanup …
Dropping Roles/Synonymns/Tablespaces … Done.
Unquiescing DB … Done.
Dropped Repository Successfully.
oracle@antarctic:/opt/app/oracle/product/11/db_1/bin> ./emca -repos create

STARTED EMCA at May 6, 2009 4:41:05 PM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: sprod
Listener port number: 2483
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
May 6, 2009 4:41:38 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /opt/oracle/cfgtoollogs/emca/sprod/emca_2009_05_06_16_41_05.log.
May 6, 2009 4:41:39 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
May 6, 2009 4:54:44 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at May 6, 2009 4:54:44 PM

CHECK
SQL> analyze table sysman.mgmt_metrics_1hour validate structure cascade;

Table analyzed.

SQL> analyze index sysman.mgmt_metrics_1hour_pk validate structure;

Index analyzed.

Regards,

Tom